Here are some of good Oracle sql examination questions with answer
1. You must permanently remove all data from the sales_data table, but will need the table structure in the future. What single command should be issued?
a. DROP TABLE sales_data
b. TRUNCATE TABLE sales_data
c. DELETE FROM sales_data
d. TRNCATE TABLE sales_data KEEP STRUCTURE;
Solution :(b) (c)
2. Examine the following command:
CREATE TABLE pay_master
CONSTRAINT pay-pk PRIMARY KEY,
CONSTRAINT mgr-name-nn. NOT NULL,
Which two lines of this command will return an error?
Solution :(a) and (d)
3. You must store currency data. All data will have two digits to the fight of the decimal point (ie, 25.xx). The numbers to the left of the decimal point will vary. Which data type is most appropriate?
4. Examine the WORKER table below:
NAME NULL TYPE
WORKER ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
PHONE NOT NULL VARCHAR2(9)
There are hundreds of records in the EMPLOYEE TABLE. You need to modify the phone column to hold only number values. Which statement will modify the data type appropriately?
a. ALTER TABLE worker MODIFY phone NUMBER(9)
b. ALTER WORKER table MODIFY COLUMN phone NUMBER(9)
c. A VARCHAR2 data type cannot be changed to a NUMBER data type for a column.
d. The data type of a column cannot be modified if there is data in the column.
5. What should be done after each fetch statement in a PL/SQL block?
a. Open the cursor.
b. Close the cursor.
c. Initialize the loop.
d. Test for rows using the cursor attribute.
6. A group function produces ______.
a. A group of results from one row.
b. One result from each row in a table.
c. Many results from many rows per group.
d. One result from many rows per group.
7. Your company will be granting workers a $150 salary increase. You need to evaluate results of the increase from the worker table prior to the actual modification. You do not want to store in the results in the current database. Which of the following is untrue?
a. A column must be added to the worker table.
b. An arithmetic expression must be added that invokes the salary increment in the SET section of the upgrade clause.
c. An arithmetic expression must be added that invokes the salary increment in the SELECT clause of the SELECT statement.
d. An arithmetic expression must be added that invokes the salary increment in the UPDATE clause of the SELECT statement.
8. What command should be used to execute a script file named create_table.sql from the SQL Plus environment?
a. START create_table
b. EXECUTE create_table
c. RUN create_table
d. GET create_table
9. You need to test if the current fetch within a PL/SQL loop was successful. Which cursor attribute is needed to accomplish this task?
d. A cursor attribute cannot be used within a PL/SQL loop.
10) You query a database with the following command:
SELECT dept_no, AVG (MONTHS_BETWEEN (SYSDATE, hire_date)) FROM worker_company
WHERE AVG (MONTHS_BETWEEN (SYSDATE, hire_date)) > 60
GROUP BY dept_no
ORDER BY AVG (MONTHS_BETWEEN (SYSDATE, hire_date) )
Where does the statement cause an error?
a. A SELECT clause cannot contain a group function.
b. A WHERE clause cannot be used to restrict groups.
c. An ORDER BY clause cannot contain a group function.
d. A group function cannot contain a single row function.
11. You have been instructed to create a report that shows different jobs in each division within your company. No duplicate roles can be displayed. Which of the following SELECT statements should be used?
a. SELECT divisionno, job FROM worker;
b. SELECT no duplicate divisionno, job FROM worker;
c. SELECT distinct divisionno, job FROM worker;
d. CREATE report DISPLAY divisionno, job