Master these to crack any SQL interview!

Here are Top SQL Interview Questions for Beginners with Clear Example

1️) What is the difference between WHERE and HAVING?

• WHERE filters rows before grouping.

• HAVING filters after aggregation.

SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5;

2) How to find the second highest salary?

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

3) What is a NULL? How do you handle it?

• NULL = unknown/missing value.
• Use IS NULL or COALESCE().
SELECT name, COALESCE(phone, 'N/A') FROM users;

4) Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?
Example:
SELECT *
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

5) What is a subquery? Types?

• Single-row
• Multi-row
• Correlated
SELECT name
FROM employees
WHERE dept_id = (SELECT id FROM departments WHERE name = 'IT');

6) What are window functions?

SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

7) Difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

RANK() – Skips ranks
DENSE_RANK() – No gaps
ROW_NUMBER() – Unique order

8) How to retrieve duplicate records?

SELECT name, COUNT(*)
FROM students
GROUP BY name
HAVING COUNT(*) > 1;

9️) What is a primary key vs unique key?

• Primary Key: Unique + Not Null
• Unique Key: Just Unique

10) What is a foreign key?

• A key that references a primary key in another table to maintain referential integrity.

11) Difference between DELETE, TRUNCATE, and DROP?

• DELETE: Row-wise delete, rollback possible

• TRUNCATE: Faster, can't rollback

• DROP: Removes structure

12) How to select top N records?

SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
13) How do you update data with a JOIN?
UPDATE e
SET e.salary = e.salary * 1.1
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'IT';

14) What’s a CTE (Common Table Expression)?

WITH high_salary AS (
SELECT * FROM employees WHERE salary > 80000
)
SELECT * FROM high_salary WHERE dept_id = 2;

15) Find employees earning more than their manager.

SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

1️6️) What is normalization?

• Process to reduce redundancy.
• 1NF, 2NF, 3NF, BCNF...

1️7️) What is denormalization?

• Adding redundancy for performance.

1️8️) What is indexing?

• Boosts read performance.
• Use on frequently filtered/sorted columns.
CREATE INDEX idx_salary ON employees(salary);

1️9) What’s the use of GROUP BY?

• Aggregation by categories.
SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id;
2️0️) What’s the difference between COUNT(*), COUNT(1), COUNT(column)?
• COUNT(*): All rows

• COUNT(1): Similar to COUNT(*)

• COUNT(column): Non-null values only

2️1️) What’s a correlated subquery?

SELECT name
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE dept_id = e.dept_id
);

2️2️) What is EXISTS vs IN?

• EXISTS – Faster for correlated subqueries

• IN – Works well for static lists

2️3️) What is the difference between UNION and UNION ALL?

• UNION: Removes duplicates

• UNION ALL: Includes all rows

2️4️) What is a stored procedure?

• A saved SQL logic block
CREATE PROCEDURE GetEmployees()
BEGIN
SELECT * FROM employees;
END;

2️5️) What is ACID in DB?

• Atomicity
• Consistency
• Isolation
• Durability
2️6️) How to get the nth highest salary?
SELECT DISTINCT salary
FROM employees e1
WHERE N-1 = (
SELECT COUNT(DISTINCT salary)
FROM employees e2
WHERE e2.salary > e1.salary
);

2️7️) What is a composite key?

• A primary key made up of 2+ columns.
PRIMARY KEY (order_id, product_id)

2️8️) What is a self join?

• Joining a table with itself.
SELECT A.name, B.name AS manager_name
FROM employees A
JOIN employees B ON A.manager_id = B.id;

2️9️) What are aggregate functions?

• SUM(), COUNT(), AVG(), MIN(), MAX()

3️0️) Difference between CHAR and VARCHAR?

• CHAR(n) – Fixed length

• VARCHAR(n) – Variable length

3️1️) How to remove duplicate rows?

DELETE FROM employees
WHERE id NOT IN (
SELECT MIN(id)
FROM employees
GROUP BY name, salary
);

3️2️) What’s the purpose of COALESCE()?

• Returns the first non-null value.
SELECT COALESCE(mobile, phone, 'No Contact') FROM users;
3️3️) What is the use of CASE statement?
SELECT name,
CASE
WHEN salary > 100000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM employees;

3️4️) What is the default sorting order in ORDER BY?

• Ascending (ASC)

3️5️) Difference between BETWEEN and IN?

• BETWEEN – Continuous range

• IN – Specific values

3️6️) How to calculate cumulative sum?

SELECT name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;

3️7️) What’s the purpose of LIMIT and OFFSET?

SELECT * FROM employees
ORDER BY id
LIMIT 10 OFFSET 20;

3️8️) What is schema in SQL?

• Logical structure grouping tables, views, procedures.

3️9️) How to convert rows into columns (pivot)?

Depends on RDBMS. Example (PostgreSQL):
SELECT
department,
COUNT(*) FILTER (WHERE gender = 'M') AS male_count,
COUNT(*) FILTER (WHERE gender = 'F') AS female_count
FROM employees
GROUP BY department;
4️0️) What is the use of DISTINCT?
SELECT DISTINCT department FROM employees;

4️1️) What is a trigger?

• Auto-executed code when events occur.
CREATE TRIGGER log_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO audit_log(emp_id, old_salary, new_salary)
VALUES (OLD.id, OLD.salary, NEW.salary);

4️2️) Difference between OLTP and OLAP?

OLTP OLAP
Fast read/write Fast analysis
Normalized Denormalized
e.g., ATM e.g., Reporting tool

4️3️) How to find top 3 salaries per department?

SELECT *
FROM (
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
) sub
WHERE rnk <= 3;

4️4️) What is data type precision vs scale?

• DECIMAL(p, s)
• p = precision (total digits)
• s = scale (digits after decimal)

4️5️) What is ISNULL() vs IFNULL()?

• RDBMS specific null handling functions.
SELECT ISNULL(col, 0); -- SQL Server
SELECT IFNULL(col, 0); -- MySQL
46) What is TRANSACTION?
• Group of SQL steps executed as a unit.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

47) What’s the difference between CHARINDEX() and SUBSTRING()?

• CHARINDEX() – Position of substring

• SUBSTRING() – Extract part of string

48) What is a cross join?

• Cartesian product of two tables.
SELECT * FROM products CROSS JOIN colors;

49) What is SET vs SELECT INTO?

• SET assigns variables

• SELECT INTO creates new table

50) How to check for referential integrity violations?

SELECT *
FROM orders
WHERE customer_id NOT IN (SELECT id FROM customers);

51) What is the difference between TEMP and GLOBAL TEMP tables?
• TEMP: Session-specific
• GLOBAL TEMP: Visible to all sessions (until last session disconnects)
CREATE TEMP TABLE temp_sales (...);

52) What is the difference between IN, ANY, ALL, and EXISTS?
-- IN: list match
-- ANY: at least one match
-- ALL: all must match
-- EXISTS: subquery returns at least one row
53) How to find employees with no department?
SELECT *
FROM employees
WHERE dept_id IS NULL;

54) How to calculate age from date of birth?

SELECT name,
DATEDIFF(YEAR, dob, GETDATE()) AS age
FROM employees; -- SQL Server

-- MySQL
SELECT name, TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age FROM employees;

55) How to find all weekends in a given month?

(Example in PostgreSQL)
SELECT generate_series('2025-07-01'::date, '2025-07-31', '1 day')::date AS day
WHERE EXTRACT(DOW FROM day) IN (0, 6);

56) How to find customers who never placed an order?

SELECT *
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

57) How to reverse a string in SQL?

-- SQL Server
SELECT REVERSE('LinkedIn');

-- MySQL
SELECT REVERSE('LinkedIn');

58) What is a surrogate key?

• A system-generated unique key (like ID) not derived from data.

59) What is an alternate key?

• A candidate key not chosen as the primary key.
60) How to avoid divide-by-zero errors?
SELECT amount / NULLIF(quantity, 0) AS unit_price FROM orders;

61) What is the difference between TRIGGER and PROCEDURE?

• Trigger: Auto-executed
• Procedure: Manually invoked

62) How to calculate median salary?

SELECT AVG(salary) AS median_salary
FROM (
SELECT salary
FROM employees
ORDER BY salary
LIMIT 2 - (SELECT COUNT(*) % 2) OFFSET (SELECT (COUNT(*) - 1) / 2)
);

63) What is the difference between SCHEMA and DATABASE?

• Database: Collection of schemas
• Schema: Collection of tables, views, procedures inside DB

64) What is a materialized view?

• A view that stores query results physically (refreshed periodically).

65) How to compare two tables for mismatched data?

SELECT * FROM tableA
EXCEPT
SELECT * FROM tableB;

66) How to find common records between two tables?

SELECT * FROM tableA
INTERSECT
SELECT * FROM tableB;

67) What is a recursive CTE?

• A CTE that refers to itself (e.g., hierarchy or tree structure)
WITH RECURSIVE hierarchy AS (
SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy;

68) What are the different types of indexes?

• Clustered
• Non-clustered
• Composite
• Unique
• Bitmap (Oracle)

69) How to find gaps in a sequence?

SELECT id + 1 AS missing
FROM employees e1
WHERE NOT EXISTS (
SELECT 1 FROM employees e2 WHERE e2.id = e1.id + 1
);

70) How to find 3rd occurrence of a character in a string?

-- SQL Server
SELECT CHARINDEX('-', column, CHARINDEX('-', column, CHARINDEX('-', column) + 1)
+ 1)
FROM table;

71) How to rank within groups?

SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

72) What is the difference between VARCHAR, TEXT, and NVARCHAR?

Type Use Case
VARCHAR ASCII text
NVARCHAR Unicode (multi-language)
TEXT Large strings (deprecated in some DBs)
73) How to export query results to a CSV?
• MySQL:
SELECT * INTO OUTFILE '/tmp/output.csv'
FIELDS TERMINATED BY ','
FROM employees;

74) How to find duplicates based on 2 columns?

SELECT col1, col2, COUNT(*)
FROM table
GROUP BY col1, col2
HAVING COUNT(*) > 1;

75) How to use MERGE statement (UPSERT)?

MERGE target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (source.id, source.name);

76) What are the ACID properties of a transaction?

• Atomicity – All or nothing
• Consistency – DB stays valid
• Isolation – Concurrent-safe
• Durability – Once committed, changes persist

77) How to generate row numbers without ROW_NUMBER()?

SELECT a.id, COUNT(*) AS row_num
FROM employees a
JOIN employees b ON a.id >= b.id
GROUP BY a.id;

78) What is database sharding?

• Breaking large databases into smaller, faster, independent chunks across multiple machines.

79) How to get table metadata?

-- MySQL
DESCRIBE employees;

-- SQL Server
EXEC sp_help 'employees';

80) How to get column names from a table?

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'employees';

81) How to remove duplicate rows but keep one?

DELETE FROM employees
WHERE id NOT IN (
SELECT MIN(id)
FROM employees
GROUP BY name, salary
);

82) What is the difference between HAVING and WHERE with example?
• WHERE filters rows

• HAVING filters aggregates

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

83) What is the fastest way to count rows?

SELECT COUNT(*) FROM employees;
-- Indexing helps performance.

84) How to get max value per group?

SELECT department, MAX(salary)
FROM employees
GROUP BY department;

85) Difference between LEFT OUTER JOIN and RIGHT OUTER JOIN?
• LEFT returns all from left + matches from right
• RIGHT does the opposite
86) How to join 3 or more tables?
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;

87) What is a NULL-safe comparison?

-- MySQL specific
SELECT * FROM employees WHERE NULL <=> NULL;

88) What is a VIEW and how is it different from a table?

• View = virtual table (no data stored)
• Table = stores data physically
CREATE VIEW high_earners AS
SELECT * FROM employees WHERE salary > 100000;

89) What is the use of WITH TIES in TOP clause?

SELECT TOP 3 WITH TIES *
FROM employees
ORDER BY salary DESC;

✅ Returns all records tied with the 3rd highest salary.

90) How to handle NULLs in ORDER BY?
-- PostgreSQL
ORDER BY salary NULLS LAST;

91) Difference between COUNT(*) and COUNT(column)?

• COUNT(*): All rows

• COUNT(column): Non-null rows only

92) How to convert string to date?

-- MySQL
STR_TO_DATE('2025-07-08', '%Y-%m-%d');

-- SQL Server
CAST('2025-07-08' AS DATE);
93) How to create a running total using CTE?
WITH salary_cte AS (
SELECT id, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees
)
SELECT * FROM salary_cte;

94) How to delete rows with a join?

DELETE e
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'HR';

95) What are dirty reads?

• Reading uncommitted data from another transaction — occurs under READ
UNCOMMITTED isolation level.

96) What is a phantom read?

• A transaction reads new rows added by another transaction before it ends.

97) What is the difference between clustered and non-clustered index?

Clustered Index Non-Clustered Index
Physically sorts table Separate structure
1 per table Many per table

98) How to find table size in SQL?

-- SQL Server
sp_spaceused 'employees';

-- PostgreSQL
SELECT pg_size_pretty(pg_total_relation_size('employees'));

99) How to select every nth row?

-- MySQL
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER () AS rn
FROM employees
) AS temp
WHERE MOD(rn, 5) = 0;

100) What is the difference between delete and truncate with foreign keys?
• DELETE: Allowed with FK (if CASCADE or FK exists)

• TRUNCATE: Not allowed if foreign key exists referencing the table