SQL Interview Questions and Answers
1. Explain order of execution of SQL.
- SQL executes in the order: FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT ->
ORDER BY -> LIMIT.
2. What is difference between WHERE and HAVING?
- WHERE filters before aggregation, HAVING filters after aggregation.
3. What is the use of GROUP BY?
- Used to group records and apply aggregate functions.
4. Explain all types of joins in SQL.
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN, CROSS JOIN.
5. What are triggers in SQL?
- Automated actions on INSERT, UPDATE, DELETE.
6. What is stored procedure in SQL?
- A precompiled SQL script that can be executed multiple times.
7. Explain all types of window functions?
- RANK, DENSE_RANK, ROW_NUMBER, LEAD, LAG.
8. What is difference between DELETE and TRUNCATE?
- DELETE removes specific rows, TRUNCATE removes all rows and cannot be rolled back.
9. What is difference between DML, DDL and DCL?
- DML (INSERT, UPDATE, DELETE), DDL (CREATE, ALTER, DROP), DCL (GRANT, REVOKE).
10. What are aggregate functions? Explain with examples.
- COUNT, SUM, AVG, MAX, MIN.
11. Which is faster between CTE and Subquery?
- CTE is faster when reused multiple times.
12. What are constraints and types of Constraints?
- PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK.
13. Types of Keys?
- Primary Key, Foreign Key, Candidate Key, Composite Key, Surrogate Key.
14. Different types of Operators?
- Arithmetic, Comparison, Logical, Bitwise.
15. Difference between Group By and Where?
- WHERE filters before grouping, GROUP BY groups records.
16. What are Views?
- A virtual table based on a SQL query.
17. What are different types of constraints?
- Primary Key, Foreign Key, Unique, Check, Not Null.
18. Difference between VARCHAR and NVARCHAR?
- VARCHAR stores non-Unicode, NVARCHAR stores Unicode.
19. Difference between CHAR and NCHAR?
- CHAR is fixed-length, NCHAR supports Unicode.
20. What are indexes and their types?
- Clustered Index, Non-Clustered Index.
21. What is an index? Explain its different types.
- Indexes improve query speed, types include Clustered and Non-Clustered.
22. List the different types of relationships in SQL.
- One-to-One, One-to-Many, Many-to-Many.
23. Differentiate between UNION and UNION ALL.
- UNION removes duplicates, UNION ALL keeps duplicates.
24. Retention Query in SQL?
- SELECT COUNT(DISTINCT user_id) / COUNT(*) * 100 AS retention_rate FROM user_activity;
25. Year-on-Year Growth Query?
- SELECT year, (revenue - LAG(revenue) OVER (ORDER BY year)) / LAG(revenue) OVER (ORDER BY year) * 100
AS yoy_growth FROM sales;
26. Cumulative Sum Query?
- SELECT name, salary, SUM(salary) OVER (ORDER BY id) AS cumulative_salary FROM employees;
27. Difference between Function and Stored Procedure?
- Functions return a value, Stored Procedures perform multiple operations.
28. Do we use variables in views?
- No, views cannot store variables.
29. What are the limitations of views?
- Cannot accept parameters, some modifications on base tables are restricted.