Structured Query Language (SQL) is the most important skill for data analysts, software developers, and database administrators. If you’re preparing for an SQL interview then reviewing common SQL interview questions and their answers can give you a better edge in job interviews . In this article, we will try to cover fundamental to advanced SQL questions with detailed answers which is frequently asked in interviews .
1. What is SQL?
SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows users to create, read, update, and delete (CRUD) data from databases.
2. What are the different types of SQL commands?
SQL commands are categorized into five types:
- DDL (Data Definition Language) – CREATE, ALTER, DROP, TRUNCATE
- DML (Data Manipulation Language) – SELECT, INSERT, UPDATE, DELETE
- DCL (Data Control Language) – GRANT, REVOKE
- TCL (Transaction Control Language) – COMMIT, ROLLBACK, SAVEPOINT
- DQL (Data Query Language) – SELECT (Some classify this separately)
3. What is the difference between DELETE, TRUNCATE, and DROP?
| Command | Function |
| DELETE | Removes specific rows from a table based on a condition. Can be rolled back. |
| TRUNCATE | Removes all rows from a table but keeps its structure. Cannot be rolled back. |
| DROP | Deletes the entire table, including its structure. Cannot be rolled back. |
4. What is the difference between WHERE and HAVING?
- WHERE is used to filter records before aggregation.
- HAVING is used to filter records after aggregation.
Example:
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000 — Applied before aggregation
GROUP BY department
HAVING COUNT(*) > 10; — Applied after aggregation
5. What is a primary key?
A primary key is a column or a set of columns that uniquely identify each row in a table. It must be unique and cannot contain NULL values.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50)
);
6. What is the difference between INNER JOIN and LEFT JOIN?
- INNER JOIN returns only the matching records from both tables.
- LEFT JOIN returns all records from the left table and matching records from the right table. If there is no match, NULL values are returned.
Example:
— INNER JOIN
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
— LEFT JOIN
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
7. What is an index in SQL?
An index is a database object that improves the speed of data retrieval operations on a table.
Example:
CREATE INDEX idx_employee_name ON employees(name);
8. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
| Function | Description |
| ROW_NUMBER() | Assigns a unique number to each row, without gaps. |
| RANK() | Assigns a rank with gaps if there are duplicates. |
| DENSE_RANK() | Assigns a rank without gaps, even if there are duplicates. |
Example:
SELECT name, salary,
ROW_NUMBER() OVER(ORDER BY salary DESC) AS row_number,
RANK() OVER(ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER(ORDER BY salary DESC) AS dense_rank
FROM employees;
9. What is normalization? Explain its types.
Normalization is the process of organizing data in a database to reduce redundancy and improve efficiency. Common normal forms include:
- 1NF (First Normal Form): Ensures atomicity (each column has unique values).
- 2NF (Second Normal Form): Removes partial dependencies.
- 3NF (Third Normal Form): Removes transitive dependencies.
10. What is a stored procedure?
A stored procedure is a set of SQL statements that can be executed as a single unit, improving performance and reusability.
Example:
CREATE PROCEDURE GetEmployeeByDepartment(IN dept_id INT)
BEGIN
SELECT * FROM employees WHERE department_id = dept_id;
END;
Conclusion
SQL is a powerful tool, and mastering it is crucial for database-related roles. Practicing these common SQL interview questions will help you ace your next SQL interview. Keep learning and exploring more complex queries and optimization techniques:)
Do you have any favorite SQL interview questions? Share them in the comments below!
Leave a Reply