SQL – Top 25 interview questions

Certainly! Here are the top 25 interview questions for SQL, along with detailed answers and relevant examples.

1. What is SQL and what are its main components?

Answer:
SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. The main components of SQL are:

• DDL (Data Definition Language): Commands that define the database schema, such as CREATE, ALTER, and DROP.

  CREATE TABLE employees (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      position VARCHAR(50)
  );

• DML (Data Manipulation Language): Commands that manipulate data, such as INSERT, UPDATE, and DELETE.

  INSERT INTO employees (id, name, position) VALUES (1, 'John Doe', 'Manager');
  UPDATE employees SET position = 'Senior Manager' WHERE id = 1;
  DELETE FROM employees WHERE id = 1;

• DQL (Data Query Language): Commands that query data, primarily SELECT.

  SELECT * FROM employees;

• DCL (Data Control Language): Commands that control access to data, such as GRANT and REVOKE.

  GRANT SELECT ON employees TO user_name;

• TCL (Transaction Control Language): Commands that manage transactions, such as COMMIT, ROLLBACK, and SAVEPOINT.

  BEGIN;
  UPDATE employees SET position = 'Manager' WHERE id = 2;
  COMMIT;

2. What are the different types of joins in SQL?

Answer:
Joins in SQL are used to combine rows from two or more tables based on a related column. The different types of joins are:

• INNER JOIN: Returns only the rows that have matching values in both tables.

  SELECT employees.name, departments.name
  FROM employees
  INNER JOIN departments ON employees.department_id = departments.id;

• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table. If no match, NULL values are returned for columns from the right table.

  SELECT employees.name, departments.name
  FROM employees
  LEFT JOIN departments ON employees.department_id = departments.id;

• RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and the matched rows from the left table. If no match, NULL values are returned for columns from the left table.

  SELECT employees.name, departments.name
  FROM employees
  RIGHT JOIN departments ON employees.department_id = departments.id;

• FULL JOIN (FULL OUTER JOIN): Returns rows when there is a match in one of the tables. If there is no match, NULL values are returned for columns from the table without a match.

  SELECT employees.name, departments.name
  FROM employees
  FULL JOIN departments ON employees.department_id = departments.id;

• CROSS JOIN: Returns the Cartesian product of both tables.

  SELECT employees.name, departments.name
  FROM employees
  CROSS JOIN departments;

3. What is a primary key in SQL?

Answer:
A primary key is a field (or combination of fields) that uniquely identifies each record in a table. Primary keys must contain unique values and cannot contain NULLs.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

4. What is a foreign key in SQL?

Answer:
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. It is used to establish and enforce a link between the data in the two tables.

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

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

Answer:

• DELETE: Removes rows from a table based on a condition. It can be rolled back if wrapped in a transaction.

  DELETE FROM employees WHERE id = 1;

• TRUNCATE: Removes all rows from a table without logging individual row deletions. It cannot be rolled back.

  TRUNCATE TABLE employees;

• DROP: Deletes the table and its structure from the database.

  DROP TABLE employees;

6. What is normalization, and what are the normal forms?

Answer:
Normalization is the process of organizing data to reduce redundancy and improve data integrity. The normal forms are:

• First Normal Form (1NF): Ensures each column contains atomic (indivisible) values, and each column contains values of a single type.
• Second Normal Form (2NF): Achieves 1NF and ensures that all non-key attributes are fully functionally dependent on the primary key.
• Third Normal Form (3NF): Achieves 2NF and ensures that all the attributes are not only fully functionally dependent on the primary key but also non-transitively dependent (i.e., no transitive dependencies).

7. What is an index in SQL, and what are the types of indexes?

Answer:
An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage and slower write operations. Types of indexes include:

• Unique Index: Ensures all values in the index are unique.

  CREATE UNIQUE INDEX idx_employee_id ON employees(id);

• Composite Index: An index on multiple columns.

  CREATE INDEX idx_name_position ON employees(name, position);

• Full-text Index: Used for full-text searches.

  CREATE FULLTEXT INDEX idx_fulltext_name ON employees(name);

• Clustered Index: Sorts the data rows in the table based on the index key. There can be only one clustered index per table.

  CREATE CLUSTERED INDEX idx_id ON employees(id);

• Non-clustered Index: Maintains a separate structure from the data rows. Multiple non-clustered indexes can be created on a table.

  CREATE INDEX idx_name ON employees(name);

8. What are SQL constraints?

Answer:
Constraints enforce rules on data columns in a table to ensure data integrity. Common constraints include:

• NOT NULL: Ensures a column cannot have a NULL value.

  CREATE TABLE employees (
      id INT PRIMARY KEY,
      name VARCHAR(50) NOT NULL
  );

• UNIQUE: Ensures all values in a column are unique.

  CREATE TABLE employees (
      id INT PRIMARY KEY,
      email VARCHAR(50) UNIQUE
  );

• PRIMARY KEY: Uniquely identifies each record in a table.

  CREATE TABLE employees (
      id INT PRIMARY KEY,
      name VARCHAR(50)
  );

• FOREIGN KEY: Ensures the referential integrity of the data in one table to match values in another table.

  CREATE TABLE departments (
      id INT PRIMARY KEY,
      name VARCHAR(50)
  );

  CREATE TABLE employees (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      department_id INT,
      FOREIGN KEY (department_id) REFERENCES departments(id)
  );

• CHECK: Ensures that all values in a column satisfy a specific condition.

  CREATE TABLE employees (
      id INT PRIMARY KEY,
      age INT CHECK (age >= 18)
  );

9. What is a view in SQL?

Answer:
A view is a virtual table based on the result set of a SQL query. It encapsulates complex queries and provides an abstraction layer.

CREATE VIEW employee_view AS
SELECT id, name, position
FROM employees
WHERE position = 'Manager';

To query a view:

SELECT * FROM employee_view;

10. What is a stored procedure in SQL?

Answer:
A stored procedure is a set of SQL statements that can be stored in the database and executed as a single unit. It helps in encapsulating logic and improving performance.

CREATE PROCEDURE AddEmployee (
    IN emp_name VARCHAR(50),
    IN emp_position VARCHAR(50)
)
BEGIN
    INSERT INTO employees (name, position) VALUES (emp_name, emp_position);
END;

To call a stored procedure:

CALL AddEmployee('Jane Doe', 'Developer');

11. What is a trigger in SQL?

Answer:
A trigger is a set of SQL statements that automatically executes in response to certain events on a particular table or view. Triggers can be used to enforce business rules, validate data, and maintain audit trails.

CREATE TRIGGER trg_before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END;

12. How do you perform a transaction in SQL?

Answer:
A transaction in SQL is a sequence of operations performed as a single logical unit of work. Transactions ensure data integrity and support the properties of ACID (Atomicity, Consistency, Isolation, Durability).

BEGIN;

UPDATE employees SET salary = salary + 500 WHERE department_id = 1;
INSERT INTO salary_logs (employee_id, amount, change_date) VALUES (1, 500, NOW());

COMMIT;

If something goes wrong and you want to undo the changes, you can use ROLLBACK:

BEGIN;

UPDATE employees SET salary = salary + 500 WHERE department_id = 1;
INSERT INTO salary_logs (employee_id, amount, change_date) VALUES (1, 500, NOW());

ROLLBACK;

13. What is the difference between HAVING and WHERE?

Answer:

• WHERE: Filters rows before the grouping operation. It cannot be used with aggregate functions.

  SELECT department_id, COUNT(*)
  FROM employees
  WHERE salary > 50000
  GROUP BY department_id;

• HAVING: Filters rows after the grouping operation. It can be used with aggregate functions.

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

14. What is a subquery, and what are its types?

Answer:
A subquery is a query nested inside another query. The types of subqueries include:

• Single-row subquery: Returns a single row.

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

• Multi-row subquery: Returns multiple rows.

  SELECT name
  FROM employees
  WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

• Correlated subquery: A subquery that references columns from the outer query.

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

15. How do you use the CASE statement in SQL?

Answer:
The CASE statement allows conditional logic in SQL queries.

SELECT name,
       salary,
       CASE
           WHEN salary < 50000 THEN 'Low'
           WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
           ELSE 'High'
       END AS salary_level
FROM employees;

16. What are window functions in SQL?

Answer:
Window functions perform calculations across a set of table rows related to the current row. Unlike aggregate functions, they do not cause rows to become grouped into a single output row.

SELECT name,
       department_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM employees;

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

Answer:

• UNION: Combines the results of two queries and removes duplicate rows.

  SELECT name FROM employees WHERE department_id = 1
  UNION
  SELECT name FROM employees WHERE department_id = 2;

• UNION ALL: Combines the results of two queries and includes all duplicate rows.

  SELECT name FROM employees WHERE department_id = 1
  UNION ALL
  SELECT name FROM employees WHERE department_id = 2;

18. How do you handle NULL values in SQL?

Answer:
NULL values represent missing or unknown data. Handling NULL values involves using functions like IS NULL, IS NOT NULL, COALESCE, and NULLIF.

• IS NULL and IS NOT NULL: Check for NULL values.

  SELECT * FROM employees WHERE manager_id IS NULL;

• COALESCE: Returns the first non-NULL value from a list of arguments.

  SELECT name, COALESCE(manager_id, 'No Manager') AS manager
  FROM employees;

• NULLIF: Returns NULL if the two arguments are equal.

  SELECT name, NULLIF(department_id, 0) AS department_id
  FROM employees;

19. What are common table expressions (CTEs) in SQL?

Answer:
A CTE is a temporary result set defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It improves readability and simplifies complex queries.

WITH department_salaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, ds.avg_salary
FROM employees e
JOIN department_salaries ds ON e.department_id = ds.department_id
WHERE e.salary > ds.avg_salary;

20. What is the purpose of the GROUP BY clause in SQL?

Answer:
The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to perform operations on each group.

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

21. How do you optimize SQL queries?

Answer:
Optimizing SQL queries involves various strategies to improve performance:

• Indexes: Create indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.

  CREATE INDEX idx_department_id ON employees(department_id);

• Query rewriting: Simplify complex queries and avoid subqueries where possible.

  SELECT e.name, d.name AS department
  FROM employees e
  JOIN departments d ON e.department_id = d.id;

• Avoiding SELECT *: Select only the columns needed.

  SELECT name, position FROM employees;

• Using proper joins: Choose the most efficient type of join based on the data.
• Partitioning: Split large tables into smaller, more manageable pieces.
• Analyze execution plan: Use tools to analyze and optimize the query execution plan.

22. What are the differences between SQL and NoSQL databases?

Answer:

• SQL (Relational Databases):
• Schema-based with predefined structure.
• Use SQL for queries.
• Supports ACID properties for transactions.
• Examples: MySQL, PostgreSQL, Oracle.
• NoSQL (Non-relational Databases):
• Schema-less, allowing dynamic data structures.
• Use various query languages.
• Support eventual consistency (BASE properties).
• Suitable for unstructured and semi-structured data.
• Examples: MongoDB, Cassandra, Redis.

23. What are stored functions in SQL, and how do they differ from stored procedures?

Answer:

• Stored Functions: Return a single value and can be used in SQL expressions. They must contain a RETURN statement.

  CREATE FUNCTION get_employee_count() RETURNS INT
  BEGIN
      RETURN (SELECT COUNT(*) FROM employees);
  END;

To call a stored function:

  SELECT get_employee_count();

• Stored Procedures: Do not return values directly but can return multiple values through output parameters or result sets. They are used for performing a series of operations.

  CREATE PROCEDURE get_employee_details()
  BEGIN
      SELECT * FROM employees;
  END;

To call a stored procedure:

  CALL get_employee_details();

24. Explain the concept of ACID properties in SQL.

Answer:
ACID properties ensure reliable processing of database transactions:

• Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction is aborted.
• Consistency: Ensures that the database remains in a consistent state before and after the transaction.
• Isolation: Ensures that the operations within a transaction are isolated from other transactions.
• Durability: Ensures that the results of a transaction are permanently stored in the database, even in the event of a system failure.

25. How do you implement pagination in SQL?

Answer:
Pagination allows you to split query results into manageable chunks. Common techniques include:

• Using LIMIT and OFFSET:

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

• Using ROW_NUMBER() (SQL Server example):

  SELECT *
  FROM (
      SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
      FROM employees
  ) AS temp
  WHERE row_num BETWEEN 21 AND 30;

These questions cover a broad range of SQL topics, providing a solid foundation for anyone preparing for a SQL interview. If you need more detailed explanations or additional questions, feel free to ask!