Landing a job that requires SQL skills often means preparing for a challenging interview. SQL is a foundational technology for database management, so employers test candidates on various concepts ranging from basic to advanced-level queries. This blog covers common SQL interview questions and their answers to help you succeed in your next interview.
What is SQL?
SQL or Structured Query Language, is a programming language designed for managing and querying relational databases. It allows you to create, read, update, and delete data in databases, making it essential for working with data-driven systems. It forms the backbone of most modern applications, making it a must-have skill for developers, analysts, and data professionals alike.
Basic SQL Questions
1. What are the types of SQL commands?
SQL commands are grouped into the following categories:
- DDL (Data Definition Language): Commands like CREATE, ALTER and DROP that define or modify database structure. For example, creating a new table in a database involves a CREATE command.
- DML (Data Manipulation Language): Commands like INSERT, UPDATE and DELETE for modifying data. These commands interact directly with the rows of a table.
- DQL (Data Query Language): The SELECT statement used to query data. This command helps retrieve specific information from tables.
- DCL (Data Control Language): Commands like GRANT, REVOKE to control access to the database. They ensure data security.
- TCL (Transaction Control Language): Commands like COMMIT, ROLLBACK for managing transactions, ensuring data consistency.
2. What is a primary key?
A primary key is a unique identifier for a record in a table. It ensures that each row in the table is unique and cannot contain NULL values. It is the cornerstone of relational integrity. For example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(50)
);
3. What is the difference between WHERE and HAVING?
- WHERE: Filters rows before grouping data. It is used for raw data filtration.
- HAVING: Filters groups after the GROUP BY clause, making it useful for aggregated data. Example:
SELECT Department, COUNT(*)
FROM Employees
WHERE Department IS NOT NULL
GROUP BY Department
HAVING COUNT(*) > 5;
4. What is a foreign key?
A foreign key is a field in one table that links to the primary key in another table. It enforces referential integrity between two tables, ensuring valid relationships. Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
5. What are indexes in SQL?
Indexes are special lookup tables used to speed up data retrieval. They significantly improve query performance by allowing the database to find data without scanning every row in a table. However, they may slow down INSERT or UPDATE operations due to the maintenance of index data. Example:
CREATE INDEX idx_customer_name
ON Customers (CustomerName);
6. Explain the difference between DELETE and TRUNCATE.
- DELETE: Removes specific rows based on a condition and can use a WHERE clause. It logs each row deleted.
- TRUNCATE: Removes all rows from a table without logging individual row deletions, making it faster but less flexible. It resets table identity values.
7. What is the use of the GROUP BY clause?
The GROUP BY clause is used to group rows with the same values in specified columns and perform aggregate functions like COUNT, SUM, or AVG on them. Example:
SELECT Department, AVG(Salary)
FROM Employees
GROUP BY Department;
8. How do you fetch unique records?
Use the DISTINCT keyword to fetch unique records. This ensures no duplicate rows are returned. Example:
SELECT DISTINCT Department
FROM Employees;
9. What is a default constraint in SQL?
A default constraint sets a default value for a column if no value is specified during insertion. Example:
CREATE TABLE Products (
ProductID INT,
Price DECIMAL(10, 2) DEFAULT 0.00
);
10. Explain LIKE in SQL.
The LIKE operator is used for pattern matching in a WHERE clause. It is particularly useful for flexible searches. Example:
SELECT *
FROM Employees
WHERE Name LIKE 'A%';
This query retrieves all employees whose names start with ‘A’.
Intermediate SQL interview questions with detailed answers:
11. Find Customer Order Counts
Question:
Write a query to list customers who have placed more than 5 orders. Use the customers and orders tables.
Answer:
SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) > 5;
- Explanation: This query joins the customers and orders tables on customer_id. It groups by customer_id and customer_name, then counts the number of orders for each customer. The HAVING clause filters the results to show customers with more than 5 orders.
12. Aggregate Functions with GROUP BY
Question:
Calculate the total revenue, average revenue, and the count of orders grouped by product category in the sales table.
Answer:
SELECT product_category,
SUM(revenue) AS total_revenue,
AVG(revenue) AS average_revenue,
COUNT(order_id) AS order_count
FROM sales
GROUP BY product_category;
- Explanation: This query uses SUM() to calculate total revenue, AVG() to find the average revenue, and COUNT() to count the number of orders. The GROUP BY clause groups the result by product category.
13. INNER JOIN Practice
Question:
Join the employees and departments tables. Return the employee name and department name for all employees.
Answer:
SELECT e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
- Explanation: An INNER JOIN is used to combine rows from employees and departments where the department_id matches in both tables. This returns the employee names along with their respective department names.
14. Subquery for Average Salary
Question:
Write a query to find the names of employees who earn more than the average salary in the company.
Answer:
SELECT employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
- Explanation: The subquery (SELECT AVG(salary) FROM employees) calculates the average salary. The outer query returns employees whose salary is greater than this average.
15. LEFT JOIN to Include Nulls
Question:
Write a query to list all employees and their department names, including employees who don’t belong to any department.
Answer:
SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
- Explanation: A LEFT JOIN is used here to include all employees. If an employee doesn’t belong to a department, the result will show NULL for the department name.
16. Self Join to Find Manager-Employee Relationship
Question:
Write a query to list employees along with their manager’s name. Assume there’s a column manager_id in the employees table that points to another employee’s employee_id.
Answer:
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
- Explanation: This is a self join on the employees table, where the manager_id in the employee record matches the employee_id in the manager’s record. It returns the employee names along with their manager’s names.
17. Using UNION to Combine Results
Question:
Write a query to list all employees and all customers (from the employees and customers tables) who have made orders. Ensure no duplicate records are returned.
Answer:
SELECT employee_name AS name FROM employees
UNION
SELECT customer_name AS name FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);
- Explanation: The UNION operator combines results from the employees and customers tables. The WHERE clause filters customers who have placed orders, ensuring unique names across both datasets.
18. Using CASE for Conditional Logic
Question:
Write a query to categorize employees based on their salary into “High”, “Medium”, and “Low” categories.
Answer:
SELECT employee_name,
CASE
WHEN salary > 100000 THEN 'High'
WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;
- Explanation: The CASE statement is used to categorize employees based on their salary. The result categorizes them as “High”, “Medium”, or “Low”.
19. Find Duplicate Records
Question:
Write a query to find duplicate email addresses from the users table. Assume the users table has a column email.
Answer:
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
- Explanation: This query groups records by email and counts how many times each email appears. The HAVING clause filters for emails that appear more than once.
20. Using Window Functions (ROW_NUMBER)
Question:
Write a query to assign a row number to each employee, ordered by their salary in descending order.
Answer:
SELECT employee_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;
- Explanation: The ROW_NUMBER() window function assigns a unique row number to each row, ordered by salary in descending order. It doesn’t require a GROUP BY clause because it is a window function operating over the entire result set.
Advanced SQL interview questions
Here are 15 advanced SQL interview questions or concepts with detailed answers, designed to test in-depth SQL knowledge and problem-solving ability:
21. What are Window Functions? Explain with an example.
Answer:
Window functions perform calculations across a set of table rows that are related to the current row. Unlike regular aggregate functions, which return a single value for a group of rows, window functions return a value for each row in the result set.
Example:
SELECT employee_name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
- Explanation:
- RANK() is a window function that assigns a unique rank to each row within a result set, based on the salary column. The window is defined by the ORDER BY clause, and the rank restarts when there are ties in salary.
22. What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN?
Answer:
- INNER JOIN returns only the rows that have matching values in both tables.
- LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, the result will contain NULL for columns from the right table.
- RIGHT JOIN is similar to LEFT JOIN, but it returns all rows from the right table, and the matching rows from the left table. If there is no match, the result will contain NULL for columns from the left table.
23. How would you find the Nth highest salary in a table?
Answer:
To find the Nth highest salary, you can use ROW_NUMBER() or RANK() along with a subquery or a CTE.
Example for Nth highest salary:
WITH RankedSalaries AS (
SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
)
SELECT salary
FROM RankedSalaries
WHERE rank = 3; -- Nth value, here it is the 3rd highest salary
- Explanation:
The RANK() function ranks salaries in descending order. By filtering for rank = 3, you get the 3rd highest salary.
24. Explain the concept of Normalization and its different normal forms.
Answer:
Normalization is the process of organizing a database to reduce redundancy and dependency by splitting large tables into smaller, related tables. The goal is to eliminate undesirable characteristics like insertion, update, and deletion anomalies.
The common normal forms (NF) are:
- 1NF (First Normal Form): Ensures that each column contains atomic (indivisible) values and each row is unique.
- 2NF (Second Normal Form): Meets 1NF requirements and ensures that all non-key columns are fully dependent on the primary key.
- 3NF (Third Normal Form): Meets 2NF requirements and ensures that there are no transitive dependencies (i.e., non-key columns depend on other non-key columns).
- BCNF (Boyce-Codd Normal Form): A stricter version of 3NF, where every determinant is a candidate key.
Normalization helps in reducing data redundancy but might increase the complexity of queries, so in some cases, denormalization is used for performance reasons.
25. What is a Common Table Expression (CTE), and how is it different from a subquery?
Answer:
A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH keyword before the main query.
Example:
WITH EmployeeCTE AS (
SELECT employee_id, salary
FROM employees
WHERE department_id = 10
)
SELECT * FROM EmployeeCTE;
- Explanation:
A CTE is similar to a subquery but can be more readable and reusable. It allows recursion and can be referenced multiple times in the main query.
26. What are Indexes and how do they improve query performance?
Answer:
An index is a database object that speeds up data retrieval operations on a table. Indexes are created on one or more columns and help the database engine quickly locate rows without scanning the entire table.
- How they improve performance:
Indexes reduce query execution time by allowing faster access to rows, especially for queries with WHERE, JOIN, or ORDER BY clauses. However, indexes can slow down INSERT, UPDATE, and DELETE operations because the index also needs to be updated. - Types of indexes:
- B-tree index: Default index type, useful for range queries and equality searches.
- Hash index: Used for equality comparisons.
- Full-text index: Used for searching large text fields.
27. What is the difference between TRUNCATE and DELETE?
Answer:
- DELETE:
- Removes rows from a table based on a condition.
- Slower because it logs each row deletion and maintains referential integrity (with foreign keys).
- Can be rolled back if wrapped in a transaction.
- TRUNCATE:
- Removes all rows from a table, without logging each row individually.
- Faster than DELETE because it doesn’t maintain transaction logs for each row.
- Cannot be rolled back (in most cases) and does not fire triggers.
28. What are Triggers in SQL?
Answer:
A trigger is a set of SQL statements that automatically execute or “fire” when certain events (INSERT, UPDATE, DELETE) occur on a table. Triggers are used for enforcing business rules, data validation, and auditing.
- Example: A trigger can be set to track whenever a row is inserted into the employees table, automatically logging the action in an audit table.
29. What are Transaction and ACID properties?
Answer:
A transaction is a sequence of one or more SQL operations that are executed as a single unit of work. Transactions ensure data integrity, even in the event of a failure.
ACID Properties:
- Atomicity: Ensures that all operations in a transaction are completed successfully or none are.
- Consistency: Ensures the database moves from one valid state to another.
- Isolation: Ensures that operations of one transaction are not visible to others until the transaction is complete.
- Durability: Ensures that once a transaction is committed, its changes are permanent.
30. How would you write a query to find employees who worked in multiple departments?
Answer:
To find employees who worked in multiple departments, you can group by employee_id and count distinct department_id.
SELECT employee_id, COUNT(DISTINCT department_id) AS department_count
FROM employee_department
GROUP BY employee_id
HAVING COUNT(DISTINCT department_id) > 1;
- Explanation:
This query counts the number of distinct departments an employee has worked in, and filters those employees who have worked in more than one department.
31. What is a Subquery and how does it differ from a Join?
Answer:
A subquery is a query embedded within another query. It can be used to filter results or calculate values for comparison. A subquery is executed first, and its results are used by the outer query.
- Difference from a JOIN:
- Subquery: Runs a separate query and provides results to the main query.
- JOIN: Combines rows from multiple tables based on a related column and executes in one step.
32. Explain the difference between GROUP BY and DISTINCT.
Answer:
- GROUP BY:
- Used to aggregate rows into groups based on one or more columns. It is often used with aggregate functions (like COUNT(), SUM(), AVG(), etc.).
- DISTINCT:
- Used to remove duplicate rows from the result set, returning only unique values.
Example for GROUP BY:
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
Example for DISTINCT:
SELECT DISTINCT department_id FROM employees;
33. What is Denormalization?
Answer:
Denormalization is the process of combining tables or adding redundant data to a database to improve read performance. It reduces the need for complex joins by storing the same data in multiple places, which can speed up queries at the cost of additional storage and potential data inconsistency.
34. How can you improve the performance of a slow query?
Answer:
To improve query performance:
- Use indexes on frequently queried columns.
- Optimize joins: Avoid unnecessary joins and subqueries.
- Rewrite subqueries as joins where possible.
- Limit data: Only select the necessary columns, not SELECT *.
- Use EXPLAIN to analyze query plans and identify bottlenecks.
- Avoid complex operations in WHERE clauses (e.g., functions on columns).
35. What is Cascading in Foreign Keys?
Answer:
Cascading is a set of rules that define what happens to related rows when a row in a referenced table is updated or deleted.
- CASCADE: Automatically updates or deletes rows in the child table when the corresponding row in the parent table is updated or deleted.
- SET NULL: Sets the foreign key values to NULL when the parent row is deleted.
- NO ACTION: Prevents the update or delete operation if there are any related rows in the child table.
These questions and concepts cover a broad range of advanced SQL topics such as window functions, joins, normalization, subqueries, and transaction management. Mastery of these areas will demonstrate a solid understanding of relational database systems and SQL performance optimization.
Tips for SQL Interviews
Here are 5-7 quick tips for succeeding in an SQL interview:
- Master SQL Basics: Understand core concepts like SELECT, JOIN, GROUP BY, and aggregation functions.
- Practice Regularly: Solve SQL problems on platforms like LeetCode, HackerRank, or SQLZoo to sharpen your skills.
- Optimize Queries: Focus on writing efficient queries by using proper indexing and understanding query plans.
- Know Advanced Concepts: Be prepared to discuss window functions, CTEs, normalization, and transaction management.
- Think Aloud: During the interview, explain your thought process clearly when solving a problem.
- Consider Edge Cases: Handle NULL values, empty sets, and duplicates in your queries to ensure robustness.
- Stay Calm: Don’t panic if you don’t know the answer immediately—focus on problem-solving steps and communicating your approach.
These tips will help you prepare effectively and confidently tackle SQL interview challenges!
Enhance Your SQL Skills with Free Courses
Ready to take your SQL skills to the next level?
If you’re looking for structured learning to boost your SQL knowledge, check out free SQL courses to kickstart your learning.
Conclusion
In SQL interviews, success comes from a combination of mastering core concepts, consistent practice, and a calm, structured approach to solving problems. By understanding key SQL operations, optimizing your queries, and being prepared to tackle complex scenarios, you’ll be able to impress interviewers with both your technical skills and your problem-solving mindset.
As you continue to refine your skills and knowledge, remember that consistent learning and practice are essential to staying ahead in the field. If you’re ready to dive deeper and enhance your SQL expertise, consider exploring free SQL courses by Great Learning. These courses are designed to help you understand both basic and advanced concepts with hands-on experience, perfect for boosting your confidence in SQL interviews.
Take the first step towards mastering SQL and ace your interview today!
Explore Free SQL Courses by Great Learning Academy.
