This comprehensive guide presents 50 foundational SQL interview questions tailored for 2025 candidates. Covering essential topics such as basic queries, joins, and subqueries, it includes practical SQL code examples to facilitate effective learning and preparation.
1. Basics
1.1 What is SQL?
SQL serves as the primary language for interacting with relational databases, facilitating essential operations such as querying data, inserting new records, updating existing information, and deleting entries. In essence, SQL provides the necessary tools to efficiently organize and manipulate structured data stored in tables.
1.2 How do you select all columns from a table?
Use SELECT * to retrieve all columns from a table.
SELECT * FROM employees;1.3 How do you select distinct values from a column?
Use DISTINCT to eliminate duplicate rows.
SELECT DISTINCT department FROM employees;1.4 How do you filter rows using a WHERE clause?
Use WHERE to filter rows based on a condition.
SELECT * FROM employees WHERE salary > 50000;1.5 How do you sort results using ORDER BY?
Use ORDER BY to sort results in ascending or descending order.
SELECT name, salary FROM employees ORDER BY salary DESC;2. Joins
2.1 What is a JOIN in SQL?
A JOIN in SQL serves to associate rows from two or more tables by matching them through a shared column. Essentially, it enables the integration of data sets, allowing for meaningful connections and analysis by aligning related information across different tables.
2.2 How do you perform an INNER JOIN?
INNER JOIN returns rows with matching values in both tables.
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;2.3 How do you perform a LEFT JOIN?
LEFT JOIN returns all rows from the left table and matching rows from the right.
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;2.4 How do you perform a RIGHT JOIN?
RIGHT JOIN returns all rows from the right table and matching rows from the left.
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;2.5 How do you perform a FULL JOIN?
FULL JOIN returns all rows when there’s a match in either table.
SELECT e.name, d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id;3. Aggregate Functions
3.1 How do you count rows in a table?
Use COUNT to get the number of rows.
SELECT COUNT(*) FROM employees;3.2 How do you calculate the average of a column?
Use AVG to compute the average of a column.
SELECT AVG(salary) FROM employees;3.3 How do you find the sum of a column?
Use SUM to calculate the total of a column.
SELECT SUM(salary) FROM employees;3.4 How do you find the maximum value in a column?
Use MAX to get the highest value.
SELECT MAX(salary) FROM employees;3.5 How do you find the minimum value in a column?
Use MIN to get the lowest value.
SELECT MIN(salary) FROM employees;4. Grouping and Filtering
4.1 How do you group rows by a column?
Use GROUP BY to group rows with the same values.
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;4.2 How do you filter grouped results using HAVING?
Use HAVING to filter groups based on a condition.
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;4.3 How do you use LIMIT to restrict the number of rows?
Use LIMIT to restrict the number of returned rows.
SELECT * FROM employees LIMIT 10;5. Subqueries
5.1 What is a subquery?
A subquery, functions as a query embedded within another query. This inner query executes first, producing a set of results that the main, or outer, query then utilizes. Subqueries are essential tools in SQL, allowing for more complex data retrieval by enabling one query to reference the output of another within the same statement.
5.2 How do you use a subquery in a WHERE clause?
Use a subquery to filter rows based on a condition.
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);5.3 How do you use a correlated subquery?
A correlated subquery references columns from the outer query.
SELECT name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);6. Indexes
6.1 What is an index in SQL?
An index is a data structure that improves the speed of data retrieval operations.
6.2 How do you create an index?
Use CREATE INDEX to create an index on a column.
CREATE INDEX idx_employee_name ON employees(name);6.3 How do you drop an index?
Use DROP INDEX to remove an index.
DROP INDEX idx_employee_name;7. Normalization
7.1 What is normalization?
Normalization is essentially the process of structuring data in a database to reduce redundancy and maintain data integrity. It’s a systematic way to ensure that information is not unnecessarily repeated and that the relationships between data elements are logically sound.
7.2 What is the first normal form (1NF)?
First normal form, or 1NF, dictates that each column in a table must hold only atomic (indivisible) values. In practical terms, this means that each field contains just one piece of information, and there are no repeating groups or arrays within a single column.
7.3 What is the second normal form (2NF)?
Second normal form, or 2NF, builds upon 1NF. For a table to satisfy 2NF, all non-key attributes must be fully functionally dependent on the entire primary key, not merely a part of it. This requirement eliminates partial dependencies that can lead to inconsistencies.
7.4 What is the third normal form (3NF)?
Third normal form, or 3NF, takes things a step further. It requires that the table already meet all the conditions of 2NF, and, in addition, that there are no transitive dependencies for non-key attributes. In other words, non-key attributes should not depend on other non-key attributes—only on the primary key. This further streamlines the data and ensures logical coherence.
8. Constraints
8.1 What is a primary key?
A primary key uniquely identifies each row in a table.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50)
);8.2 What is a foreign key?
A foreign key links two tables by referencing a primary key.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
employee_id INT,
FOREIGN KEY (employee_id) REFERENCES employees(id)
);8.3 What is a unique constraint?
A UNIQUE constraint ensures all values in a column are unique.
CREATE TABLE employees (
id INT PRIMARY KEY,
email VARCHAR(50) UNIQUE
);9. Transactions
9.1 What is a transaction?
A transaction is a sequence of operations performed as a single unit.
9.2 How do you start and commit a transaction?
Use BEGIN and COMMIT to start and save a transaction.
BEGIN;
UPDATE employees SET salary = salary + 1000;
COMMIT;9.3 How do you rollback a transaction?
Use ROLLBACK to undo changes if an error occurs.
BEGIN;
UPDATE employees SET salary = salary + 1000;
ROLLBACK;10. Advanced Queries
10.1 How do you find the nth highest salary?
Use a subquery with LIMIT and OFFSET to find the nth highest salary.
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;10.2 How do you delete duplicate rows?
Use a CTE to identify and delete duplicates based on a unique column.
WITH duplicates AS (
SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM employees
)
DELETE FROM duplicates WHERE rn > 1;10.3 How do you find employees with salaries above their department average?
Use a subquery to compare salaries with department averages.
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);10.4 How do you use a CASE statement?
Use CASE to apply conditional logic in queries.
SELECT name, salary,
CASE
WHEN salary > 70000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM employees;10.5 How do you pivot data in SQL?
Use PIVOT to transform rows into columns (database-specific).
SELECT *
FROM (
SELECT department_id, salary
FROM employees
) AS SourceTable
PIVOT (
AVG(salary)
FOR department_id IN (1, 2, 3)
) AS PivotTable;Conclusion
If you’re aiming to succeed in SQL interviews in 2025, these 50 questions are essential. Seriously, take the time to work through each query and grasp the underlying concepts. With thorough practice, you’ll approach any database challenge in your interviews with genuine confidence.
