sql important ques for interview
- User
sql important ques for interview
Assistant
Here are important SQL interview questions categorized by difficulty level and topic. These are commonly asked in interviews for roles like Data Analyst, Data Scientist, Software Developer, Database Administrator, etc.
🟢 Basic SQL Interview Questions
1. What is SQL?
- Answer: SQL (Structured Query Language) is a standard language used to manage and manipulate relational databases.
2. What is the difference between
DELETEandTRUNCATEcommands?Feature DELETE TRUNCATE DML/DLL DML command DDL command WHERE clause Can be used Cannot be used Rollback Possible Not possible in most RDBMS Auto-increment reset No Yes (in some DBs like MySQL) 3. Explain
JOINtypes.- INNER JOIN: Returns matching rows from both tables.
- LEFT JOIN (LEFT OUTER JOIN): All rows from left table + matched rows from right.
- RIGHT JOIN (RIGHT OUTER JOIN): All rows from right table + matched rows from left.
- FULL JOIN (FULL OUTER JOIN): All rows when there's a match in either table.
- CROSS JOIN: Cartesian product of two tables.
4. What is a primary key vs. unique key?
Feature Primary Key Unique Key Null values Not allowed One null allowed (in some DBs) Number per table One Multiple Indexing Automatically indexed Automatically indexed 5. What is normalization? Name different normal forms.
- Normalization: Process of organizing data to reduce redundancy.
- Normal Forms:
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
🟠 Intermediate SQL Interview Questions
6. What is a subquery?
- A query inside another query.
- Types:
- Scalar
- Row
- Table
- Correlated
Example:
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);7. Explain
GROUP BYandHAVING.GROUP BY: Groups rows sharing same value.HAVING: Filters grouped result (used withGROUP BY, unlikeWHERE).
Example:
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;8. Difference between
WHEREandHAVING?Clause Applies on Used with WHERE Individual rows SELECT, UPDATE, DELETE HAVING Aggregated groups GROUP BY in SELECT 9. What is an index?
- An index is a performance optimization structure that allows faster retrieval of records.
- Types: Clustered, Non-clustered, Unique, Composite, Full-text
10. What are constraints?
Constraints enforce rules on data in a table.
NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECKDEFAULT
🔴 Advanced SQL Interview Questions
11. What is a view?
- A virtual table based on the result-set of an SQL query.
- Syntax:
CREATE VIEW employee_view AS SELECT name, department FROM employees;12. What is a stored procedure?
- A precompiled collection of SQL statements and control flow logic.
- Example:
CREATE PROCEDURE GetEmployeeCount AS BEGIN SELECT COUNT(*) FROM employees; END13. What is a trigger?
- A special type of stored procedure that runs automatically when a specific event occurs (e.g., INSERT, UPDATE, DELETE).
- Used for auditing, logging, enforcing business rules.
14. Explain ACID properties.
- Atomicity: Transaction is all or nothing.
- Consistency: Only valid data is written.
- Isolation: Transactions are isolated from each other.
- Durability: Once committed, changes are permanent.
15. What is a transaction?
- A sequence of operations performed as a single logical unit of work.
- Commands:
BEGIN TRANSACTION,COMMIT,ROLLBACK
16. What are Common Table Expressions (CTEs)?
- Temporary named result set used within a SELECT, INSERT, UPDATE, or DELETE statement.
- Example:
WITH CTE_Employee AS ( SELECT * FROM employees WHERE salary > 50000 ) SELECT * FROM CTE_Employee;17. Explain
RANK(),DENSE_RANK(), andROW_NUMBER().All are window functions:
ROW_NUMBER()– Assigns unique numbers.RANK()– Same rank for ties, skips next number.DENSE_RANK()– Same rank for ties, doesn’t skip next number.
18. Write a query to find the second highest salary.
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);Or using subquery:
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
🧪 Practical / Scenario-Based SQL Questions
19. Find duplicate records in a table.
SELECT emp_id, COUNT(*) FROM employees GROUP BY emp_id HAVING COUNT(*) > 1;20. How to delete duplicate records?
DELETE FROM employees WHERE id NOT IN ( SELECT MIN(id) FROM employees GROUP BY emp_id );21. Find Nth highest salary using a subquery or window function.
Using window function:
SELECT name, salary FROM ( SELECT name, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS rnk FROM employees ) AS ranked WHERE rnk = N;
📚 Bonus: SQL for Data Analysis
22. What is a pivot table in SQL?
- It transforms rows into columns.
- Often done using
CASE WHENorPIVOT(in some SQL dialects like SQL Server).
23. Time-based analysis queries
Example: Monthly sales report
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_sales FROM orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY month;
🧠 Tips for SQL Interviews
- Practice writing queries manually – don't rely only on tools.
- Understand execution plans – helps in optimizing queries.
- Know your SQL dialect – MySQL, PostgreSQL, SQL Server, Oracle, etc.
- Be comfortable with joins, subqueries, window functions, and CTEs.
- Use real datasets like those on LeetCode, HackerRank, or Kaggle.
Would you like me to provide a PDF version, or a list of SQL practice problems, or sample answers for these in a specific SQL dialect like MySQL, PostgreSQL, or SQL Server?
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.