Oracle sql online test for Certification

Oracle SQL is the widely used in various Products. Having a knowledge in Oracle SQL is plus point.

This Oracle sql online test check the understanding on various topics like Oracle table, Oracle view ,constraints, type of index, sequences, subqueries , oracle joins ,materialized view, primary key constraints , single row function ,sql queries and many more

The below test is randomized sql test and select 15 questions from the set of many questions,so each time you try to attempt it, you will get different questions in different order and option would also be in different order, So it will be fun practicing with this tool. You can try as many times as you want till you feel comfortable about it

Instructions for online test

1.This quiz consists of 15 questions .It is recommended to complete the test in 30 min
2. It consists both single choice and more than one choice questions to test your knowledge
3. You can press the submit button at the end of your test and get the final score
4.You will also get the know the correct choice after you finished the test.

Show paginator

Hide paginator

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?

TRUNCATE TABLE sales_data KEEP STRUCTURE;

TRUNCATE TABLE sales_data

DROP TABLE sales_data

DELETE FROM sales_data

Question 1 of 15

2. How would you add a foreign key constraint on the dept_no column in the EMP table. Referring to the ID column in the DEPT table?

Use the ALTER TABLE command with the MODIFY clause on the EMP table.

Use the ALTER TABLE command with the MODIFY clause on the DEPT table.

This task cannot be accomplished.

Use the ALTER TABLE command with the ADD clause on the EMP table.

Use the ALTER TABLE command with the ADD clause in the DEPT table.

Question 2 of 15

3. Examine the following command:

CREATE TABLE pay_master
(employee-id Number(9)
CONSTRAINT pay-pk PRIMARY KEY,
2000-pay NUMBER(8,2)
manager-name VARCHAR2(25)
CONSTRAINT mgr-name-nn. NOT NULL,
pay-96 NUMBER(8,2));

Which two lines of this command will return an error?

Fourth

Fifth

First

Second

Sixth

Third

Question 3 of 15

4. A group function produces ______.

One result from each row in a table.

Many results from many rows per group.

A group of results from one row.

One result from many rows per group.

Question 4 of 15

5. The emp table contains these columns:
Last_name Varchar2 (25)
First_name Varchar2 (25)
Salary Number7, 2
You need to display the names of employees on more than an average salary of all
employees. Evaluate the SQL statement.
SELECT, LAST_NAME, FIRST_NAME from emp where salary< avg(salary);

Which change should you make to achieve the desired results?

Move the function to the select clause and add a group clause.

Use a sub query in the where clause to compare the average salary value.

Change the function in the Where clause.

Move the function to the select clause and add a group by clause and a having clause.

Question 5 of 15

6. The emp table contains these columns:
FIRST-NAME VARCHER2(25)
COMISSION NUMBER(3,2)
Evaluate this SQL statement
SELECT first-name,commission FROM emp WHERE commission= (SELECT comission FROM emp WHERE UPPER(first-name)= ‘smith’)
Which statement will cause this statement to fail?

There is no employee with the first name smith.

The first name values in the data base are in the lower case.

smith has a null commission resolution.

smith has a zero commission resolution.

Question 6 of 15

7. You want of display the details of all employees whose last names is john. But you are not sure in which case last names are stored. Which statement will list all the employees whose last name is john?

Select last name, first name FROM emp WHERE last name=UPPER (‘john’);

Select last name, first name FROM emp WHERE UPPER (last name)= ‘john’;

Select last name, first name. FROM emp WHERE LOWER (last name)= ‘john’;

Select last name, first nam FROM emp WHERE last name= ‘john’;

Question 7 of 15

8. You need to create a report to display the ship date and order totals of your order table. If the order has not been shipped your report must display not shipped. If the total is not available your report must say not available. In the order table the ship date column has a data type of date the total column has a data type of number. Which statement do you use to create this report?

Select order, shipdate “Not shipped”,total “Not available” FROM order;

Select order, NVL (shipdate ‘Not shipped’),NVL (total, “Not available”) FROM order;

Select order, TO_CHAR (shipdate, ‘Not shipped’) TO_CHAR (total, ‘Not available’) FROM order;

Select order, NVL (TO_CHAR (shipdate), ‘Not shipped’),NVL (TO_CHAR (total), ‘Not available’) FROM order;

Question 8 of 15

9. 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?

An arithmetic expression must be added that invokes the salary increment in the SELECT clause of the SELECT statement.

A column must be added to the worker table.

An arithmetic expression must be added that invokes the salary increment in the UPDATE clause of the SELECT statement.

An arithmetic expression must be added that invokes the salary increment in the SET section of the upgrade clause.

Question 9 of 15

10. You need to test if the current fetch within a PL/SQL loop was successful. Which cursor attribute is needed to accomplish this task?

SQL%ISOPEN

SQLROWCOUNT

SQL%FOUND

A cursor attribute cannot be used within a PL/SQL loop.

Question 10 of 15

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?

SELECT divisionno, job FROM worker;

SELECT distinct divisionno, job FROM worker;

CREATE report DISPLAY divisionno, job

SELECT no duplicate divisionno, job FROM worker;

Question 11 of 15

12. What should be done after each fetch statement in a PL/SQL block?

Open the cursor.

Initialize the loop.

Close the cursor.

Test for rows using the cursor attribute.

Question 12 of 15

13. What command should be used to execute a script file named create_table.sql from the SQL Plus environment?

START create_table

GET create_table

EXECUTE create_table

RUN create_table

Question 13 of 15

14. 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?

An ORDER BY clause cannot contain a group function.

A group function cannot contain a single row function.

A WHERE clause cannot be used to restrict groups.

A SELECT clause cannot contain a group function.

Question 14 of 15

15. You need to store currency data and you know that data will always have two digits to the right of the decimal points. However the number of digits to the left of the decimal place will vary greatly. Which data type would be most appropriate to store the data?

NUMBER(T)

CHAR

NUMBER

VARCHAR

Question 15 of 15

I hope you like this Oracle sql online test and it helps in your examination preparation. Best of Luck for the Oracle certification Examination. Please do provide feedback on it. We will also be adding more quizes and questions in future.

Happy Learning!!!!

Related Articles
How to prepare for OCI Associate Certified Examination
Oracle Sql Tutorials
Tips for Passing OCI examination
Oracle Performance & Tuning Quiz

Instructions for online test

Reader Interactions

Leave a Reply Cancel reply