Oracle Online Test - MCQ Questions

📘 Premium Read: Access my best content on Medium member-only articles — deep dives into Java, Spring Boot, Microservices, backend architecture, interview preparation, career advice, and industry-standard best practices.

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (176K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

Welcome to the Oracle Online Test! We will present 25 MCQs (Multiple-Choice Questions) to test your knowledge of the Oracle database.

You can select the correct answer for each question and submit the test. You will get your online test score after finishing the complete test.

1. What does the following Oracle SQL query return?

SELECT SYSDATE FROM DUAL;

a) The current system date and time

b) A list of all dates in the database

c) The date the database was created

d) The user's system date and time settings

2. How does Oracle handle the operation shown in the following query?

SELECT last_name, job_id, salary * 0.10 FROM employees;

a) It multiplies the salary by 10 for each employee

b) It adds 10 to the salary for each employee

c) It multiplies the salary by 0.10 for each employee

d) It reduces the salary by 10% for each employee

3. What is the function of the ROWNUM pseudocolumn in Oracle?

SELECT * FROM employees WHERE ROWNUM <= 5;

a) It generates a unique identifier for each row returned

b) It limits the results to the first 5 rows of the table

c) It counts the number of rows in the result set

d) It sorts the data in ascending order

4. How do you create a new user in Oracle?

CREATE USER new_user IDENTIFIED BY password;

a) The given query

b) CREATE NEW_USER WITH PASSWORD password;

c) ADD USER new_user SET PASSWORD = password;

d) INSERT USER new_user IDENTIFIED BY password;

5. What does the TRUNCATE TABLE statement do in Oracle?

TRUNCATE TABLE employees;

a) Deletes all rows from the employees table irreversibly

b) Removes the employees table from the database

c) Reduces the size of the employees table by deleting some rows

d) Deletes the structure of the employees table

6. How do you alter the data type of a column in an existing table in Oracle?

ALTER TABLE employees MODIFY (job_id VARCHAR2(15));

a) The given query

b) ALTER TABLE employees CHANGE job_id VARCHAR2(15);

c) UPDATE TABLE employees SET DATA_TYPE job_id VARCHAR2(15);

d) ALTER TABLE employees SET job_id TYPE VARCHAR2(15);

7. What is the output of the following SQL statement in Oracle?

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;

a) The system date in DD-MM-YYYY format

b) The system date in YYYY-MM-DD format

c) The system date in DD-MON-YYYY format

d) The system date in MON-DD-YYYY format

8. How do you add a NOT NULL constraint to an existing column in Oracle?

ALTER TABLE employees MODIFY last_name NOT NULL;

a) The given query

b) ALTER TABLE employees SET last_name NOT NULL;

c) UPDATE TABLE employees SET last_name REQUIRE;

d) MODIFY TABLE employees last_name NOT NULL;

9. Which SQL statement is used to remove a stored procedure in Oracle?

DROP PROCEDURE my_procedure;

a) The given query

b) DELETE PROCEDURE my_procedure;

c) REMOVE PROCEDURE my_procedure;

d) ERASE PROCEDURE my_procedure;

10. What is the purpose of the CONNECT BY clause in Oracle?

SELECT last_name FROM employees CONNECT BY PRIOR employee_id = manager_id START WITH employee_id = 100;

a) It is used to perform recursive queries

b) It creates a join between two tables

c) It connects to the database

d) It filters the results of a query

11. How do you find the total number of rows in the employees table in Oracle?

SELECT COUNT(*) FROM employees;

a) The given query

b) SELECT ROWS() FROM employees;

c) SELECT TOTAL_ROWS() FROM employees;

d) SELECT ALL(*) FROM employees;

12. What does the following command do in Oracle?

CREATE SYNONYM emp FOR hr.employees;

a) Creates a synonym named emp for the employees table in the hr schema

b) Creates a new table called emp that duplicates hr.employees

c) Renames the hr.employees table to emp

d) Drops the hr.employees table and creates a new one called emp

13. How do you retrieve the 10th to 20th rows from a result set in Oracle?

SELECT * FROM (
SELECT a.*, ROWNUM rnum FROM (
SELECT * FROM employees ORDER BY employee_id
) a WHERE ROWNUM <= 20
) WHERE rnum >= 10;

a) The given query

b) SELECT * FROM employees LIMIT 10, 20;

c) SELECT * FROM employees OFFSET 9 ROWS FETCH NEXT 11 ROWS ONLY;

d) SELECT * FROM employees WHERE ROWNUM BETWEEN 10 AND 20;

14. What is the Oracle SQL function to return the number of characters in a string?

SELECT LENGTH('Hello World') FROM DUAL;

a) The given query

b) SELECT COUNT('Hello World') FROM DUAL;

c) SELECT CHAR_LENGTH('Hello World') FROM DUAL;

d) SELECT STRING_LENGTH('Hello World') FROM DUAL;

15. How do you perform a case-insensitive search for all employees whose name starts with 'Jo' in Oracle?

SELECT * FROM employees WHERE LOWER(first_name) LIKE 'jo%';

a) The given query

b) SELECT * FROM employees WHERE first_name LIKE 'jo%' COLLATE CI;

c) SELECT * FROM employees WHERE first_name ILIKE 'Jo%';

d) SELECT * FROM employees WHERE first_name = 'Jo' IGNORE CASE;

16. What is the result of executing the following SQL statement in Oracle?

SELECT TO_DATE('20210115', 'YYYYMMDD') FROM DUAL;

a) January 15, 2021

b) An error because the format is incorrect

c) January 1, 2021

d) The string '20210115'

17. How do you delete duplicate rows in a table, retaining only the row with the lowest id in Oracle?

DELETE FROM table_name WHERE rowid NOT IN (
SELECT MIN(rowid) FROM table_name GROUP BY column_name
);

a) The given query

b) DELETE FROM table_name WHERE id NOT IN (SELECT MIN(id) FROM table_name GROUP BY column_name);

c) DROP DUPLICATES FROM table_name KEEP MIN(id);

d) REMOVE DUPLICATES FROM table_name WHERE MIN(id);

18. What is the function of the NVL function in Oracle?

SELECT NVL(salary, 0) FROM employees;

a) It replaces NULL values in the salary column with 0

b) It calculates the total salary including nulls as 0

c) It sets all non-null salary values to 0

d) It creates a new column that shows whether salary is null

19. How do you add a column to an existing table in Oracle?

ALTER TABLE employees ADD (email VARCHAR2(100));

a) The given query

b) MODIFY TABLE employees ADD COLUMN email VARCHAR2(100);

c) UPDATE TABLE employees SET email VARCHAR2(100);

d) INSERT INTO employees (email) VALUES (VARCHAR2(100));

20. What is the purpose of the DECODE function in Oracle?

SELECT DECODE(job_id, 'IT_PROG', 'Programmer', 'IT_MGR', 'Manager', 'Staff') FROM employees;

a) It encrypts job_id values

b) It decodes encrypted data in the job_id column

c) It provides conditional querying similar to a CASE statement

d) It converts job_id codes into readable job titles

21. How do you copy data from one table to another in Oracle?

INSERT INTO new_table SELECT * FROM old_table;

a) The given query

b) COPY new_table FROM old_table;

c) CLONE new_table FROM old_table;

d) DUPLICATE new_table FROM old_table;

22. How do you list all indexes on a table in Oracle?

SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = 'EMPLOYEES';

a) The given query

b) SHOW INDEXES FROM employees;

c) LIST INDEXES ON employees;

d) DISPLAY INDEXES FOR employees;

23. How do you retrieve only the unique values from a column in Oracle?

SELECT DISTINCT column_name FROM table_name;

a) The given query

b) SELECT UNIQUE column_name FROM table_name;

c) GET DISTINCT column_name FROM table_name;

d) FETCH DISTINCT column_name FROM table_name;

24. What does the following Oracle SQL command do?

CREATE OR REPLACE VIEW emp_view AS SELECT * FROM employees WHERE department_id = 10;

a) Creates a new view or replaces an existing view named emp_view

b) Renames the employees table to emp_view

c) Updates all employees in department 10 to be visible only

d) Deletes the employees where department_id equals 10

25. How do you ensure that a column in a table always contains positive values using Oracle SQL?

ALTER TABLE employees ADD CONSTRAINT check_salary CHECK (salary > 0);

a) The given query

b) ALTER TABLE employees SET salary > 0;

c) UPDATE TABLE employees SET salary > 0;

d) INSERT INTO employees (salary) VALUES (>0);