SQL Interview Questions

1. What is SQL and what are its key features?

Structured Query Language (SQL) is used to manage and manipulate relational databases.

• Data Definition: Create, alter, and delete tables using DDL commands.
• Data Manipulation: Insert, update, delete, and retrieve data using DML and SELECT statements.
• Data Control: Manage access using DCL commands like GRANT and REVOKE.
• Transaction Control: Ensure data integrity with COMMIT, ROLLBACK, SAVEPOINT.
• Standardized Language: Supported by major RDBMS (MySQL, SQL Server, Oracle, etc.).
• Declarative: Focuses on what data to retrieve, not how.

2. What are the different types of SQL statements?

• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE
• DQL (Data Query Language): SELECT
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

3. What is the difference between WHERE and HAVING clause?

4. What are SQL joins and what types exist?

Joins combine rows from two or more tables based on a related column.

• INNER JOIN: Returns matching rows from both tables.
• LEFT JOIN: All rows from the left, and matched rows from the right.
• RIGHT JOIN: All rows from the right, and matched rows from the left.
• FULL JOIN: All matching and non-matching rows from both.
• SELF JOIN: Join a table to itself.
• CROSS JOIN: Returns the Cartesian product of rows.

5. What is normalization? What are its benefits?

Normalization is the process of organizing data to reduce redundancy.

Normal Forms:

• 1NF: Atomic values
• 2NF: Remove partial dependencies
• 3NF: Remove transitive dependencies
• BCNF: Higher version of 3NF

Benefits:

• Reduces data redundancy
• Ensures data integrity
• Easier maintenance

6. What is the difference between PRIMARY KEY and UNIQUE KEY?

7. What is a foreign key?

• A foreign key enforces referential integrity between two tables.
• Points to a primary key in another table.
• Prevents invalid data in child table.
• Used to link records across tables.

8. What are indexes and why are they important?

• Indexes speed up the retrieval of rows based on column values.
• Types: Single-column, composite, unique, full-text
• Trade-off: Faster reads but slower writes
• Use Case: Large datasets where search efficiency is critical

9. What is a subquery and what types exist?

A subquery is a query nested inside another SQL query.

Types:

• Scalar Subquery: Returns a single value
• Correlated Subquery: Depends on outer query
• Nested Subquery: Independent inner query

SELECT name FROM employees 
WHERE dept_id = (SELECT id FROM departments WHERE name = 'HR');

10. What are aggregate functions in SQL?

Used to perform calculations on a set of values.

• COUNT(): Number of records
• SUM(): Total sum
• AVG(): Average value
• MAX(): Highest value
• MIN(): Lowest value

Intermediate Level

11. What is a stored procedure?

• A prepared SQL code that can be saved and reused.
• Improves performance by reducing network traffic.
• Enhances security by limiting direct table access.

CREATE PROCEDURE GetEmployees()
AS
BEGIN
    SELECT * FROM employees;
END;

12. What are SQL views?

• Virtual tables based on the result-set of a SELECT statement.
• Simplify complex queries by encapsulating them.
• Provide an additional security layer by restricting access.

CREATE VIEW ActiveCustomers AS
SELECT * FROM customers WHERE status = 'active';

13. What is the difference between DELETE, TRUNCATE and DROP?

14. What are SQL triggers?

• Automated procedures that execute when specific events occur.
• Types: BEFORE/AFTER INSERT, UPDATE, DELETE.
• Used for auditing, logging, or enforcing business rules.

CREATE TRIGGER log_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log VALUES(NEW.id, NOW());
END;

15. What is a transaction and its properties (ACID)?

A transaction is a sequence of operations performed as a single logical unit of work.

ACID Properties:

• Atomicity: All operations succeed or none do.
• Consistency: Database remains in a valid state.
• Isolation: Concurrent transactions don't interfere.
• Durability: Committed changes persist.

16. What are SQL constraints?

Rules enforced on data columns to maintain data integrity.

• NOT NULL: Column cannot have NULL values.
• UNIQUE: All values must be different.
• PRIMARY KEY: Uniquely identifies each record.
• FOREIGN KEY: Maintains referential integrity.
• CHECK: Ensures values meet specific conditions.
• DEFAULT: Sets default value when none specified.

17. What is the difference between UNION and UNION ALL?

18. What are window functions in SQL?

• Perform calculations across a set of table rows related to the current row.
• Common functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG().
• Use OVER() clause to define the window frame.

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

19. What is the difference between CHAR and VARCHAR?

20. What is a CTE (Common Table Expression)?

• Temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
• Defined using WITH clause.
• Improves readability of complex queries.

WITH DeptSalaries AS (
    SELECT dept_id, AVG(salary) as avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT * FROM DeptSalaries WHERE avg_salary > 50000;