SQL | Interview Questions and Answers

Note
You can check other posts on my personal website: https://hbolajraf.net

1. What is SQL?

SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. It is used for tasks such as querying data, updating data, and defining the structure of a database.

2. What is the difference between SQL and NoSQL databases?

SQL databases are relational databases that use structured query language for defining and manipulating the data. NoSQL databases, on the other hand, are non-relational databases that can handle unstructured or semi-structured data and do not require a fixed schema.

3. What is a primary key?

A primary key is a unique identifier for a record in a database table. It ensures that each record in a table can be uniquely identified.

4. Explain the difference between INNER JOIN and LEFT JOIN.

• INNER JOIN returns only the matching rows from both tables.
• LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

5. What is normalization?

Normalization is the process of organizing data in a database to eliminate redundancy and dependency. It involves dividing large tables into smaller tables and defining relationships between them.

6. Write a SQL query to retrieve all employees from the "employees" table.

SELECT * FROM employees;

7. How do you add a new column to an existing table in SQL?

ALTER TABLE table_name
ADD COLUMN new_column_name data_type;

8. Explain the difference between UNION and UNION ALL.

• UNION combines the result sets of two or more SELECT statements and removes duplicate rows.
• UNION ALL also combines result sets but includes all rows, including duplicates.

9. What is a foreign key?

A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a link between the two tables.

10. Write a SQL query to calculate the average salary from the "salaries" table.

SELECT AVG(salary) FROM salaries;

11. How do you delete a record from a table in SQL?

DELETE FROM table_name
WHERE condition;

12. Explain the ACID properties in the context of database transactions.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably.

• Atomicity: Ensures that a transaction is treated as a single, indivisible unit.
• Consistency: Ensures that a transaction brings the database from one valid state to another.
• Isolation: Ensures that the concurrent execution of transactions does not result in data inconsistencies.
• Durability: Ensures that once a transaction is committed, its changes are permanent.

13. What is the purpose of the GROUP BY clause?

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions like COUNT, SUM, AVG, etc.

14. Write a SQL query to find the second-highest salary from the "employees" table.

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

15. How do you create an index in SQL?

CREATE INDEX index_name
ON table_name (column1, column2, ...);

16. Explain the difference between a clustered index and a non-clustered index.

• A clustered index determines the physical order of data in a table. There can be only one clustered index per table.
• A non-clustered index does not affect the physical order of the table. Tables can have multiple non-clustered indexes.

17. What is a stored procedure?

A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. It is stored in the database and can be called from applications or other stored procedures.

18. Write a SQL query to find the total number of rows in a table.

SELECT COUNT(*) FROM table_name;

19. Explain the difference between a view and a table.

• A table is a physical storage structure that holds data.
• A view is a virtual table based on the result of a SELECT query. It does not store data itself but provides a way to represent the result of a query.

20. How do you update data in a table using SQL?

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

21. What is the purpose of the HAVING clause?

The HAVING clause is used in conjunction with the GROUP BY clause to filter the results of a grouped query based on a specified condition.

22. Write a SQL query to find the top N records from a table.

SELECT * FROM table_name
LIMIT N;

23. How do you perform a self-join in SQL?

A self-join is a regular join, but the table is joined with itself. It is typically used when a table has a foreign key that references its own primary key.

SELECT t1.column1, t2.column2
FROM table_name t1
JOIN table_name t2 ON t1.common_column = t2.common_column;

24. Explain the purpose of the CASE statement in SQL.

The CASE statement is used to perform conditional logic within a SQL query. It allows you to perform different actions based on different conditions.

SELECT 
  column1,
  CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result3
  END AS new_column
FROM table_name;

25. What is the difference between CHAR and VARCHAR data types?

• CHAR is a fixed-length character data type.
• VARCHAR is a variable-length character data type.

26. Write a SQL query to find the third-highest salary from the "employees" table.

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

27. What is a trigger in SQL?

A trigger is a set of instructions that are automatically executed (or "triggered") in response to specific events on a particular table or view. These events can include INSERT, UPDATE, DELETE operations.

28. Explain the difference between a database and a schema.

• A database is a container for tables and related objects.
• A schema is a collection of database objects (tables, views, procedures) that are logically grouped together.

29. How do you find duplicate rows in a table?

SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

30. What is a subquery?

A subquery is a query nested inside another query. It can be used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.

SELECT column1
FROM

 table_name
WHERE column2 IN (SELECT column2 FROM another_table WHERE condition);

This concludes the list of SQL interview questions and answers. Feel free to use these questions as a study guide or reference for your SQL interviews.

What Next?

These are just a few examples of SQL interview questions. Depending on the level of expertise required, interview questions may vary from basic to advanced topics. Good luck with your interviews!