SQL Questions and Answers

SQL Interview Questions and Answers

1. Basic SQL Questions

Q1: What is SQL?

Answer:

SQL (Structured Query Language) is a standard language used to interact with relational databases. It allows you to:

• Retrieve data (SELECT)

• Insert, update, and delete data (INSERT, UPDATE, DELETE)

• Define and modify database structures (CREATE, ALTER, DROP)

• Control access and permissions (GRANT, REVOKE)

Q2: What are the different types of SQL commands?

Answer:

SQL commands are categorized into:

1. DDL (Data Definition Language) – CREATE, ALTER, DROP

2. DML (Data Manipulation Language) – SELECT, INSERT, UPDATE, DELETE

3. DCL (Data Control Language) – GRANT, REVOKE

4. TCL (Transaction Control Language) – COMMIT, ROLLBACK, SAVEPOINT

Q3: What is the difference between WHERE and HAVING?

Answer:

• WHERE filters rows before aggregation.

• HAVING filters groups after aggregation.

-- WHERE example
SELECT * FROM employees
WHERE salary > 50000;

-- HAVING example
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

Q4: What are the types of joins in SQL?
Answer:
INNER JOIN – Returns matching rows only
LEFT JOIN (LEFT OUTER JOIN) – Returns all rows from the left table + matching rows from the right
RIGHT JOIN (RIGHT OUTER JOIN) – Returns all rows from the right table + matching rows from the left
FULL OUTER JOIN – Returns all rows from both tables
CROSS JOIN – Returns Cartesian product of both tables

Q5: What is a primary key vs foreign key?

Answer:

• Primary Key – Uniquely identifies each row in a table; cannot be NULL.

• Foreign Key – A column referencing the primary key in another table, enforcing referential integrity.

2. Intermediate SQL Questions

Q6: What are aggregate functions?

Answer:

Aggregate functions perform calculations across multiple rows:

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

• Often used with GROUP BY and HAVING.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

Q7: What are subqueries? Give an example.
Answer:
    A subquery is a query nested inside another query.
Example:
SELECT first_name, last_name
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);
Returns employees earning more than the average salary.

Q8: What is a CTE (Common Table Expression)?
Answer:
CTE is a temporary named result set defined using WITH to improve readability.
WITH TopEmployees AS (
    SELECT first_name, salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 5
)
SELECT * FROM TopEmployees;

Q9: What are indexes?
Answer:
An index improves query speed by allowing the database to search quickly instead of scanning the entire table.
Example:
CREATE INDEX idx_employee_lastname
ON employees (last_name);
Indexes are useful for WHERE, JOIN, and ORDER BY.
Q10: What is the difference between UNION and UNION ALL?
Answer:
UNION – Combines results from two queries and removes duplicates.
UNION ALL – Combines results including duplicates.
SELECT first_name FROM employees
UNION
SELECT first_name FROM managers;

3. Advanced SQL Questions
Q11: Explain window functions. Give an example.
Answer:
Window functions perform calculations over a set of rows related to the current row, 
without collapsing results like GROUP BY.
SELECT first_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
Assigns ranks based on salary.

Q12: How do you optimize SQL queries?

Answer:

• Use indexes on frequently searched columns.

• Select only necessary columns, avoid SELECT *.

• Filter rows early with WHERE.

• Avoid unnecessary joins or nested subqueries.

• Use EXPLAIN or execution plans to identify slow queries.

Q13: Difference between DELETE, TRUNCATE, and DROP

Command	Purpose	Notes
DELETE	Removes rows based on condition	Can use WHERE; slower for large tables
TRUNCATE	Removes all rows	Faster, cannot use WHERE, resets identity
DROP	Deletes entire table	Structure and data removed

Q14: What is normalization?

Answer:

Normalization organizes database tables to reduce redundancy and improve data integrity.

• 1NF – Atomic values, no repeating groups

• 2NF – 1NF + all non-key attributes fully dependent on primary key

• 3NF – 2NF + no transitive dependency

Q15: How do you handle NULL values in SQL?

Answer:

• Use IS NULL or IS NOT NULL in filters.

• Use functions like COALESCE() to replace NULL with a default value.

SELECT first_name, COALESCE(phone_number, 'N/A') AS phone
FROM employees;