Oracle Multiple Choice Questions

Test your Oracle knowledge with these comprehensive multiple-choice questions. Hover over any question to reveal the correct answer.

Jump to Section:

1. Introduction 2. Setup 3. DB Operations 4. Basic Queries 5. DML 6. Advanced Filtering 7. Multiple Tables 8. Aggregation 9. Subqueries 10. Views/Indexes 11. Functions 12. Transactions 13. PL/SQL

1. Introduction to Oracle

1. What company developed Oracle Database? Easy
A. Microsoft
B. Oracle Corporation
C. IBM
D. SAP
Answer: B. Oracle Corporation
2. Which of these is NOT a feature of Oracle Database? Easy
A. PL/SQL
B. RAC (Real Application Clusters)
C. Materialized Views
D. Stored Functions
Answer: D. Stored Functions (All are Oracle features)
3. What is the proprietary procedural language for Oracle? Medium
A. T-SQL
B. PL/SQL
C. SQL*Plus
D. OracleScript
Answer: B. PL/SQL
4. When was the first version of Oracle Database released? Medium
A. 1977
B. 1985
C. 1990
D. 1995
Answer: A. 1977
5. What is Oracle's flagship database product called? Hard
A. Oracle Standard Edition
B. Oracle Enterprise Edition
C. Oracle Database
D. Oracle RDBMS
Answer: C. Oracle Database

2. Setting Up Oracle Environment

6. Which command-line tool is commonly used to interact with Oracle Database? Easy
A. SQL*Plus
B. Oracle CLI
C. SQL Developer
D. Database Explorer
Answer: A. SQL*Plus
7. Which of these is NOT an Oracle GUI tool? Easy
A. SQL Developer
B. Oracle Enterprise Manager
C. Toad for Oracle
D. MySQL Workbench
Answer: D. MySQL Workbench
8. What is the default port for Oracle Database listener? Medium
A. 1433
B. 1521
C. 3306
D. 5432
Answer: B. 1521
9. Which command displays version information in Oracle? Medium
A. SHOW VERSION;
B. SELECT * FROM v$version;
C. DISPLAY VERSION;
D. GET VERSION();
Answer: B. SELECT * FROM v$version;
10. What is the name of Oracle's free database version? Hard
A. Oracle Lite
B. Oracle XE (Express Edition)
C. Oracle Free
D. Oracle Community Edition
Answer: B. Oracle XE (Express Edition)

3. Database Operations

11. Which command creates a new tablespace in Oracle? Easy
A. ADD TABLESPACE
B. CREATE TABLESPACE
C. NEW TABLESPACE
D. MAKE TABLESPACE
Answer: B. CREATE TABLESPACE
12. How do you list all tables in the current schema in Oracle? Easy
A. LIST TABLES;
B. SELECT table_name FROM user_tables;
C. SHOW TABLES;
D. GET TABLES;
Answer: B. SELECT table_name FROM user_tables;
13. Which command is used to create a new user in Oracle? Medium
A. ADD USER
B. CREATE USER
C. NEW USER
D. MAKE USER
Answer: B. CREATE USER
14. What does the DROP USER command do in Oracle? Medium
A. Backs up a user's objects
B. Deletes a user account
C. Disables a user temporarily
D. Changes user password
Answer: B. Deletes a user account
15. Which view contains information about all tables in Oracle? Hard
A. ALL_TABLES
B. DBA_TABLES
C. USER_TABLES
D. All of the above
Answer: D. All of the above

4. Basic SQL Queries

16. Which SQL keyword retrieves data from an Oracle database? Easy
A. GET
B. SELECT
C. EXTRACT
D. FETCH
Answer: B. SELECT
17. Which Oracle-specific function returns the current date? Easy
A. CURRENT_DATE()
B. SYSDATE
C. NOW()
D. GETDATE()
Answer: B. SYSDATE
18. What does the DUAL table in Oracle represent? Medium
A. A system table with one row and one column
B. A temporary table
C. A view of all tables
D. A metadata table
Answer: A. A system table with one row and one column
19. Which Oracle operator concatenates strings? Medium
A. +
B. ||
C. CONCAT()
D. Both B and C
Answer: D. Both B and C (|| and CONCAT())
20. What is the purpose of the ROWNUM pseudocolumn in Oracle? Hard
A. To filter records
B. To assign a unique number to each row returned
C. To sort the result set
D. To count rows in a table
Answer: B. To assign a unique number to each row returned

5. Modifying Data (DML)

21. Which statement inserts new data into an Oracle table? Easy
A. ADD RECORD
B. INSERT INTO
C. CREATE ROW
D. NEW ENTRY
Answer: B. INSERT INTO
22. Which Oracle statement modifies existing records? Easy
A. MODIFY
B. ALTER
C. UPDATE
D. CHANGE
Answer: C. UPDATE
23. Which statement removes records from an Oracle table? Medium
A. REMOVE
B. DELETE
C. DROP
D. ERASE
Answer: B. DELETE
24. What is the difference between DELETE and TRUNCATE in Oracle? Medium
A. DELETE is faster than TRUNCATE
B. TRUNCATE can be rolled back
C. DELETE can use a WHERE clause
D. TRUNCATE is a DDL command
Answer: C. DELETE can use a WHERE clause
25. Which Oracle statement is used to merge data into a table? Hard
A. COMBINE
B. MERGE
C. INTEGRATE
D. UPDATE OR INSERT
Answer: B. MERGE

6. Advanced Filtering

26. Which Oracle operator searches for a specified pattern in a column? Easy
A. LIKE
B. MATCH
C. SIMILAR TO
D. CONTAINS
Answer: A. LIKE
27. Which Oracle function performs case-insensitive comparisons? Medium
A. UPPER()
B. LOWER()
C. NLS_UPPER()
D. All of the above
Answer: D. All of the above
28. Which Oracle operator is used to combine multiple conditions in a WHERE clause? Easy
A. AND
B. PLUS
C. ALSO
D. WITH
Answer: A. AND
29. What does the Oracle BETWEEN operator do? Medium
A. Checks if a value is within a range
B. Compares two tables
C. Joins multiple conditions
D. Creates a temporary table
Answer: A. Checks if a value is within a range
30. Which Oracle clause is used to filter groups after GROUP BY is applied? Hard
A. WHERE
B. FILTER
C. HAVING
D. GROUP FILTER
Answer: C. HAVING

7. Working with Multiple Tables

31. Which Oracle join returns all rows when there is a match in either table? Medium
A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. FULL OUTER JOIN
Answer: D. FULL OUTER JOIN
32. What is the purpose of a foreign key in Oracle? Medium
A. To uniquely identify each record
B. To create a relationship between tables
C. To speed up queries
D. To encrypt data
Answer: B. To create a relationship between tables
33. Which Oracle join returns only matching rows from both tables? Easy
A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. FULL JOIN
Answer: A. INNER JOIN
34. What is a self-join in Oracle? Hard
A. Joining a table to itself
B. Joining without any condition
C. Joining multiple tables at once
D. Joining temporary tables
Answer: A. Joining a table to itself
35. Which Oracle join returns all rows from the left table and matched rows from the right table? Medium
A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. CROSS JOIN
Answer: B. LEFT JOIN

8. Aggregation and Grouping

36. Which Oracle function returns the average value of a numeric column? Easy
A. MEAN()
B. AVG()
C. AVERAGE()
D. MEDIAN()
Answer: B. AVG()
37. What is the difference between WHERE and HAVING clauses in Oracle? Hard
A. WHERE filters rows before grouping, HAVING filters after
B. HAVING works with aggregates, WHERE doesn't
C. WHERE can be used in any SELECT, HAVING only with GROUP BY
D. All of the above
Answer: D. All of the above
38. Which Oracle function counts the number of rows in a result set? Easy
A. SUM()
B. COUNT()
C. TOTAL()
D. NUMBER()
Answer: B. COUNT()
39. What does GROUP BY do in Oracle? Medium
A. Filters rows before aggregation
B. Groups rows by column values
C. Sorts the result set
D. Joins multiple tables
Answer: B. Groups rows by column values
40. Which Oracle function returns the highest value in a column? Easy
A. TOP()
B. MAX()
C. HIGHEST()
D. PEAK()
Answer: B. MAX()

9. Subqueries

41. What is a subquery in Oracle? Medium
A. A query that runs after the main query
B. A query nested inside another query
C. A backup query
D. A query that modifies tables
Answer: B. A query nested inside another query
42. Which Oracle operator compares a value to a list from a subquery? Medium
A. EQUAL
B. IN
C. LIKE
D. MATCH
Answer: B. IN
43. What is a correlated subquery in Oracle? Hard
A. A subquery that references the outer query
B. A subquery that joins multiple tables
C. A subquery with aggregate functions
D. A subquery that runs in parallel
Answer: A. A subquery that references the outer query
44. Which Oracle clause can contain a subquery in a SELECT statement? Medium
A. SELECT
B. FROM
C. WHERE
D. All of the above
Answer: D. All of the above
45. What is the purpose of EXISTS with Oracle subqueries? Hard
A. To check if a subquery returns any rows
B. To count rows in a subquery
C. To join subquery results
D. To sort subquery results
Answer: A. To check if a subquery returns any rows

10. Views and Indexes

46. What is a materialized view in Oracle? Medium
A. A physical table that stores query results
B. A virtual table based on a SELECT
C. A graphical representation
D. A stored procedure
Answer: A. A physical table that stores query results
47. What is the main purpose of an index in Oracle? Medium
A. To enforce integrity
B. To improve query performance
C. To provide backup
D. To encrypt data
Answer: B. To improve query performance
48. Which statement creates a view in Oracle? Easy
A. MAKE VIEW
B. CREATE VIEW
C. NEW VIEW
D. DEFINE VIEW
Answer: B. CREATE VIEW
49. What is a function-based index in Oracle? Hard
A. An index on the result of a function
B. An index on multiple columns
C. An index that covers all columns
D. An index with duplicate values
Answer: A. An index on the result of a function
50. Which statement creates an index in Oracle? Medium
A. MAKE INDEX
B. CREATE INDEX
C. NEW INDEX
D. ADD INDEX
Answer: B. CREATE INDEX

11. Oracle Functions

51. Which Oracle function returns the current date and time? Easy
A. CURRENT_TIMESTAMP
B. SYSDATE
C. NOW()
D. Both A and B
Answer: D. Both A and B (CURRENT_TIMESTAMP and SYSDATE both work in Oracle)
52. Which function is used to concatenate strings in Oracle? Easy
A. CONCAT()
B. || operator
C. MERGE()
D. Both A and B
Answer: D. Both A and B (CONCAT() and || both work in Oracle)
53. Which Oracle function converts a string to uppercase? Easy
A. UPPER()
B. UCASE()
C. TO_UPPER()
D. CAPITALIZE()
Answer: A. UPPER()
54. What does the NVL() function do in Oracle? Medium
A. Combines multiple strings
B. Returns the first non-NULL argument
C. Counts the number of NULL values
D. Converts data types
Answer: B. Returns the first non-NULL argument
55. Which Oracle function extracts a portion of a string? Medium
A. EXTRACT()
B. SUBSTR()
C. SLICE()
D. PART()
Answer: B. SUBSTR()

12. Transactions and ACID

56. Which SQL statement starts a transaction in Oracle? Medium
A. BEGIN TRANSACTION
B. SET TRANSACTION
C. BEGIN WORK
D. START TRANS
Answer: B. SET TRANSACTION
57. What does the "A" in ACID stand for? Medium
A. Availability
B. Atomicity
C. Accuracy
D. Authentication
Answer: B. Atomicity
58. Which statement permanently saves a transaction's changes in Oracle? Easy
A. SAVE
B. COMMIT
C. END
D. FINISH
Answer: B. COMMIT
59. What does the "I" in ACID represent? Medium
A. Integration
B. Isolation
C. Inclusion
D. Inspection
Answer: B. Isolation
60. Which statement undoes a transaction's changes in Oracle? Easy
A. UNDO
B. ROLLBACK
C. REVERT
D. CANCEL
Answer: B. ROLLBACK

13. PL/SQL

61. What is PL/SQL? Medium
A. Oracle's procedural extension to SQL
B. A backup of database procedures
C. A special type of table
D. A graphical interface
Answer: A. Oracle's procedural extension to SQL
62. Which statement creates a PL/SQL stored procedure? Hard
A. CREATE PROCEDURE
B. MAKE PROCEDURE
C. NEW PROCEDURE
D. DEFINE PROCEDURE
Answer: A. CREATE PROCEDURE
63. How do you execute a PL/SQL stored procedure? Easy
A. EXECUTE
B. CALL
C. RUN
D. EXEC
Answer: D. EXEC (or EXECUTE in SQL*Plus)
64. What is an advantage of PL/SQL? Medium
A. Improved performance
B. Enhanced security
C. Code reusability
D. All of the above
Answer: D. All of the above
65. Which PL/SQL construct is used for error handling? Hard
A. IF-THEN-ELSE
B. EXCEPTION
C. LOOP
D. CASE
Answer: B. EXCEPTION

More Oracle Resources

Oracle Basics

Learn fundamental Oracle concepts and commands

View Notes
Oracle PL/SQL

Advanced PL/SQL techniques and interview tips

View Tricks