50 Important Oracle SQL Questions and Answers
1. What is SQL?
SQL (Structured Query Language) is used to manage and manipulate relational databases.
2. What is the difference between SQL and PL/SQL?
SQL is a query language for database interaction, while PL/SQL is Oracle's procedural extension for
writing complex scripts.
3. What are the types of SQL commands?
SQL commands are categorized into DDL (Data Definition Language), DML (Data Manipulation
Language), DCL (Data Control Language), and TCL (Transaction Control Language).
4. What is a Primary Key?
A Primary Key uniquely identifies each row in a table and cannot contain NULL values.
5. What is a Foreign Key?
A Foreign Key establishes a relationship between two tables by referencing a Primary Key in
another table.
6. What is the difference between CHAR and VARCHAR?
CHAR is a fixed-length string, while VARCHAR is a variable-length string, allowing storage
efficiency.
7. What is a Constraint in SQL?
Constraints enforce rules on table data, such as NOT NULL, UNIQUE, PRIMARY KEY, and
CHECK.
8. What is Normalization?
Normalization is the process of organizing data to reduce redundancy and improve integrity.
9. What is the difference between WHERE and HAVING?
WHERE filters records before aggregation, while HAVING filters after aggregation (used with
GROUP BY).
10. What is the use of GROUP BY?
GROUP BY is used to aggregate data based on specific columns, often with aggregate functions
like SUM, COUNT, etc.
11. What is the difference between DELETE and TRUNCATE?
DELETE removes rows based on conditions and logs transactions, while TRUNCATE removes all
rows without logging, making it faster.
12. What is an Index in SQL?
An Index improves query performance by enabling faster data retrieval.
13. What are different types of Indexes?
Common types are B-tree, Bitmap, Unique, and Composite indexes.
14. What is the difference between UNION and UNION ALL?
UNION removes duplicates, while UNION ALL retains all records, including duplicates.
15. What is a Subquery?
A Subquery is a nested query used to retrieve data for the main query.
16. What are Joins in SQL?
Joins retrieve data from multiple tables based on related columns. Types include INNER, LEFT,
RIGHT, and FULL JOINs.
17. What is the difference between INNER JOIN and OUTER JOIN?
INNER JOIN returns matching records, while OUTER JOIN returns matching and unmatched
records from one or both tables.
18. What is a Self Join?
A Self Join joins a table to itself using aliases.
19. What is the difference between ROWNUM and ROW_NUMBER()?
ROWNUM is assigned before sorting, while ROW_NUMBER() assigns a unique number based on a
specified order.
20. What is the NVL function?
NVL replaces NULL values with a specified value. Example: NVL(salary, 0) replaces NULL salary
values with 0.
21. What is the COALESCE function?
COALESCE returns the first non-NULL value from a list of expressions.
22. What is a View in SQL?
A View is a virtual table based on a SQL query.
23. What is the difference between View and Materialized View?
Views do not store data physically, whereas Materialized Views store query results and refresh
periodically.
24. What is the DECODE function?
DECODE works like a conditional statement, similar to CASE.
25. What is the CASE statement?
CASE is used for conditional logic within SQL queries.
26. What is a Sequence?
A Sequence generates unique numeric values, often used for primary keys.
27. What are Triggers in SQL?
Triggers are automatic actions executed before/after INSERT, UPDATE, or DELETE operations.
28. What is the difference between a Function and a Procedure?
Functions return a value and can be used in SELECT queries, whereas Procedures perform actions
but do not return values.
29. What is a Cursor in SQL?
A Cursor allows row-by-row processing of query results.
30. What are Explicit and Implicit Cursors?
Implicit Cursors are automatically created by Oracle, while Explicit Cursors are defined by the user.
31. What is the difference between COMMIT and ROLLBACK?
COMMIT saves changes to the database, while ROLLBACK undoes them.
32. What is a Savepoint?
A Savepoint allows partial rollback of transactions.
33. What is the difference between DROP and DELETE?
DELETE removes rows while keeping the table structure, whereas DROP removes the entire table
and its structure.
34. What is the purpose of the EXISTS clause?
EXISTS checks whether a subquery returns any results.
35. What is the purpose of the IN clause?
IN checks whether a value exists in a given list or subquery result.
36. What are Analytical Functions in SQL?
Analytical Functions perform calculations over a set of rows related to the current row.
37. What is the difference between LEAD and LAG functions?
LEAD fetches the next row's value, while LAG fetches the previous row's value.
38. What is Partitioning in SQL?
Partitioning divides tables into smaller, manageable pieces to improve performance.
39. What is the difference between SGA and PGA?
SGA (System Global Area) is shared memory, while PGA (Program Global Area) is private memory
for a session.
40. What is a Deadlock in SQL?
A Deadlock occurs when two transactions wait for each other, causing a halt.
41. What is Query Optimization?
Query Optimization improves query performance using indexes, joins, and execution plans.
42. What is an Execution Plan?
An Execution Plan shows how a SQL query is executed by the database optimizer.
43. What is the difference between a Clustered and Non-Clustered Index?
Clustered Index determines the table's physical order, while Non-Clustered Index stores pointers to
data.
44. What is Data Warehousing?
Data Warehousing stores and processes large volumes of historical data for analysis.
45. What is a Star Schema?
A Star Schema is a database schema used in data warehousing with a central fact table connected
to dimension tables.
46. What is the difference between OLTP and OLAP?
OLTP handles transactional queries, while OLAP is used for analytical processing.
47. What is a Synonym in SQL?
A Synonym is an alias for a database object, used to simplify queries.
48. What is the LISTAGG function?
LISTAGG aggregates column values into a single string with a specified delimiter.
49. What is Flashback Query?
Flashback Query allows retrieval of past data without restoring backups.
50. What is the difference between Soft and Hard Parsing?
Soft Parsing reuses execution plans, while Hard Parsing creates a new execution plan from scratch.
