Top 100 Interview Questions

 

🔹 Top 100 SQL Interview Questions

1. SQL Basics (1–20)

  1. What is SQL? Difference between SQL and PL/SQL.

  2. Different types of SQL statements (DDL, DML, DCL, TCL).

  3. Difference between Primary Key, Foreign Key, Unique Key.

  4. What is a Constraint? Types of constraints.

  5. Difference between TRUNCATE, DELETE, DROP.

  6. What is a Schema in SQL?

  7. What are Views? Types (Simple, Complex, Materialized).

  8. Difference between View and Materialized View.

  9. Difference between Cluster and Non-Clustered Index.

  10. What is a Stored Procedure? Advantages.

  11. Difference between Procedure and Function.

  12. What are Cursors? Types of Cursors.

  13. Difference between Strongly Typed and Weakly Typed Cursor.

  14. What are Sequences? How are they used?

  15. Difference between CHAR and VARCHAR2.

  16. What is a Synonym in SQL?

  17. What is Normalization? Types (1NF to 5NF).

  18. Difference between Normalization and Denormalization.

  19. What is a Check Constraint?

  20. Difference between Relational Database and Non-Relational Database.

2. Joins & Queries (21–40)

  1. Different types of Joins.

  2. Self Join example.

  3. Cross Join vs Cartesian Product.

  4. Equi Join vs Non-Equi Join.

  5. Write query: Second highest salary.

  6. Query: Nth highest salary (ROW_NUMBER vs Correlated Subquery).

  7. Query: Find duplicate rows.

  8. Query: Find employees joined in last 6 months.

  9. Query: Display department-wise highest salary.

  10. Query: Find employees without managers.

  11. Query: Retrieve common records between two tables.

  12. Query: Retrieve employees not present in another table.

  13. Query: Display 5th to 10th highest salaries.

  14. Query: Display manager → employee hierarchy (connect by / recursive CTE).

  15. Query: Display employees earning more than their manager.

  16. Query: Display top 3 salaries per department.

  17. Query: Transpose rows to columns (Pivot).

  18. Query: Transpose columns to rows (Unpivot).

  19. Query: Find missing numbers in a sequence.

  20. Query: Display employees with same salary.

3. Functions & Expressions (41–60)

  1. NVL vs NVL2 vs COALESCE vs NULLIF.

  2. Difference between Aggregate vs Analytic functions.

  3. Explain RANK vs DENSE_RANK vs ROW_NUMBER.

  4. Explain LAG and LEAD functions.

  5. Explain FIRST_VALUE and LAST_VALUE.

  6. Difference between IN and EXISTS.

  7. Difference between ANY and ALL.

  8. Difference between UNION and UNION ALL.

  9. Difference between INTERSECT and MINUS.

  10. What is CASE expression? Example.

  11. Difference between DECODE and CASE.

  12. Explain GROUPING SETS, ROLLUP, CUBE.

  13. What is a Function-Based Index? Example.

  14. Difference between Deterministic and Non-Deterministic function.

  15. What is SYS_CONNECT_BY_PATH?

  16. What is LISTAGG function?

  17. Difference between COUNT(*), COUNT(1), COUNT(column).

  18. Difference between CURRENT_DATE, SYSDATE, SYSTIMESTAMP.

  19. Difference between DATE, TIMESTAMP, INTERVAL.

  20. Difference between CHAR, VARCHAR2, NCHAR, NVARCHAR2.

4. Indexing & Performance (61–80)

  1. What are different index types? (B-Tree, Bitmap, Reverse Key, Function-Based).

  2. Difference between B-Tree vs Bitmap index.

  3. What is Index Selectivity?

  4. What is a Composite Index?

  5. What is a Unique Index?

  6. What is a Reverse Key Index?

  7. When should you use a Bitmap Index?

  8. How to detect full table scans unnecessarily?

  9. How to check query execution plan? (EXPLAIN PLAN).

  10. Difference between Cost-Based Optimizer and Rule-Based Optimizer.

  11. Nested Loops Join vs Merge Join vs Hash Join.

  12. How to tune a slow-running SQL query? Steps.

  13. How to identify Top SQL queries consuming most resources?

  14. Difference between Hard Parse and Soft Parse.

  15. What are Bind Variables? Why important?

  16. What are SQL Hints? Examples (INDEX, PARALLEL, FIRST_ROWS).

  17. Difference between Global and Local Index.

  18. What is Partitioning? Types (Range, List, Hash, Composite).

  19. Partition Pruning in Oracle — how it works?

  20. Star Schema vs Snowflake Schema.

5. Transactions & Concurrency (81–90)

  1. What is ACID property?

  2. Difference between COMMIT, ROLLBACK, SAVEPOINT.

  3. What is Transaction Control Language (TCL)?

  4. What is a Deadlock? How to detect and resolve?

  5. What is a Lock? Types of locks.

  6. Difference between Row-level vs Table-level lock.

  7. What are Isolation Levels? Examples.

  8. Explain Dirty Read, Non-Repeatable Read, Phantom Read.

  9. Difference between Optimistic and Pessimistic Locking.

  10. What is MVCC (Multi-Version Concurrency Control)?

6. Advanced SQL & Real Scenarios (91–100)

  1. What is a Common Table Expression (CTE)? Recursive CTE example.

  2. Difference between Global Temporary Table and Local Temporary Table.

  3. Difference between Inline View and Subquery Factoring (WITH clause).

  4. What is Materialized View Refresh? Types (Complete, Fast, Force).

  5. Difference between OLTP and OLAP queries.

  6. What is Sharding in databases?

  7. Difference between Star Schema and Snowflake Schema in Data Warehousing.

  8. How to implement Slowly Changing Dimensions (SCD) in SQL?

  9. Explain ETL Testing SQL queries (Source vs Target count check).

  10. Real-time scenario: How do you migrate millions of records with minimum downtime?

No comments:

Post a Comment

Subscribe to: Comments (Atom)

About This Blog

SQL and PL/SQL are essential for database management, enabling efficient data retrieval, manipulation, and transaction control. SQL (Structu...

  • About This Blog
    SQL and PL/SQL are essential for database management, enabling efficient data retrieval, manipulation, and transaction control. SQL (Structu...