SQL Interview Questions and Answers

CTA

SQL is the most popular computer language for creating, modifying, retrieving, and manipulating data in a database management system. It is widely used across industries for efficient database creation, storage, retrieval, query processing, and robust security features. There are 22,000 job openings around the world, SQL remains an important skill for data analysts, database administrators, developers, and other IT professionals. Whether you are a beginner or an experienced professional, it is important to be well-prepared for SQL queries-based interview questions. In this article we will see top 70+ SQL interview questions and answers which include basic, advanced, technical, and scenario based questions asked by interviewers and help you to crack your next SQL job interviews.

Table of Contents

1. Basic SQL Interview Questions for Freshers
2. Advanced SQL Interview Questions and Answers
3. SQL Advanced Interview Questions for Experienced Professionals ( 3 – 5 years)

1. What is the difference between a primary key and a unique key?

• Primary Key: The primary key is used to uniquely identify every record in a table. It ensures that NULL values are not present in the table.
• Unique Key: Unique key also identifies each record uniquely but it can accept NULL values.

2. What are ACID properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure the reliable processing of database transactions.

3. What is a constraint, and why use constraints?

SQL constraints are a set of rules applied to a column or table to maintain data integrity. SQL consists of many constraints which are as follows:

• DEFAULT: It sets a default value for a column.
• UNIQUE: It ensures all values are unique.
• NOT NULL: It prevents NULL values.
• PRIMARY KEY: It enables to uniquely identify each record in a table. We can say that it combines NOT NULL and UNIQUE.
• FOREIGN KEY: Links records in two tables.

How many questions can you answer in SQL Server Interview?

Take a quick Quiz to check it out

Take a Quiz

4. What is the difference between WHERE clause and Having clause?

5. Write a query to select specific columns, say name and age, from a table called Employees.

SELECT name, age 
FROM Intellipaat_Emp;

6. What is an alias?

An alias is a temporary name assigned to a table or column for the duration of a query. Aliases improve readability.

SELECT col_1 AS column_name 
FROM table_name;

7. What do you mean by foreign key?

A foreign key is a table column or a set of columns in a relational database system that establishes a link between data in the two tables. It is used to implement referential integrity by ensuring that the value in the foreign key column matches a primary key value in another table.

8. Write a query to get employees older than 35 and working in the operation department.

SELECT * 
FROM Intellipaat_Emp 
WHERE age > 35 
  AND department = 'operation';

9. Write a query to find the average salary for each department.

SELECT 
    department, 
    AVG(salary) AS avg_salary
FROM 
    Intellipaat_Emp
GROUP BY 
    department;

10. What are tables and fields in SQL?

Tables and fields are fundamental building blocks of a database structure in SQL.

Table: A table is a collection of related data organized in rows and columns. Each table is used to store information about a specific entity. A table consists of multiple fields (columns) and records (rows).

Field: A field is a specific attribute or property of the entity that the table represents. It is also known as a column; each field holds data of a specific type, such as text, numbers, or dates.

11. Write a query to find employees whose names start with 'Int'.

Query: SQL> SELECT *
FROM employees
WHERE employee_name LIKE 'Int%';

12. Write a query to add a new employee record.

INSERT INTO Intellipaat_Emp (name, age, department, salary)
VALUES ('John Doe', 28, 'Marketing', 50000);

13. State the difference between the RIGHT JOIN and the LEFT JOIN.

Both  RIGHT JOIN and LEFT join do the same work, they return the result of a query that contains all the records in the table. The only difference is that the left view shows all the records in the left table of the query, and the right view shows all the records in the right table.

14. Write a query to retrieve the last five records from the Employees table based on the id column.

SELECT * 
FROM Intellipaat_Emp 
ORDER BY id DESC 
LIMIT 5;

15. Write a query to label employees with salaries above 5000 as

SELECT name, 
       salary, 
       CASE 
           WHEN salary > 5000 THEN 'High Salary'
           ELSE 'Low Salary' 
       END AS salary_category 
FROM Intellipaat_Emp;

16. Write a query to get all employees and their project names, showing NULL if an employee is not assigned a project.

SELECT Employees.name, 
       Projects.project_name 
FROM Intellipaat_Emp AS Employees
LEFT JOIN Projects 
ON Employees.project_id = Projects.id;

17. Write an SQL query to display each department along with the name of any employee who works in that department. If a department has no employees, show the department with NULL for the employee’s name.

SELECT dept.DepartmentName, 
       int_emp.Name 
FROM Employees AS int_emp
RIGHT JOIN Departments AS dept 
ON int_emp.DepartmentID = dept.DepartmentID;

18. Can you join a table to itself in SQL?

Yes, in SQL, it is possible to join a table to itself, which is known as a self-join. By using table aliases, you can treat the same table as two separate entities and perform a join operation on them based on specified conditions. Self-joins are used when you need to retrieve information by comparing rows within the same table.

19. Difference between Drop vs Delete command?

The DROP and DELETE commands in SQL are used for removing data, but they are differ significantly: DROP: Drop command is a DDL command. It completely removes a table or database, including its structure and all data from the database.

DELETE: Delete is a DML command. It removes specific rows of data from a table based on a given condition.

20. Write a query to increase the salary of all employees in the 'HR' department by 10%.

UPDATE Intellipaat_Emp 
SET salary = salary * 1.1 
WHERE department = 'HR';

21. What is the COALESCE function?

The COALESCE function is used to take a set of inputs values and returns the first non-null value from the record.

Syntax:

COALESCE(val1,val2,val3,……,nth val)

22. What is a trigger?

The trigger is used to do an automatic process when a particular event happens in the database or table. It helps in maintaining the integrity of the table and associated tables. The trigger can be activated when the commands like insert, update, and delete are fired. The syntax used to generate the trigger function is as follows:

CREATE TRIGGER trigger_name

23. Write a query to fetch unique employee names where duplicate names exist in the Employees table.

SELECT name 
FROM Intellipaat_Emp 
GROUP BY name 
HAVING COUNT(*) = 1;

24. Find all duplicate rows in a table Employees, considering all columns.

SELECT * 
FROM Intellipaat_Emp 
GROUP BY name, age, department, salary 
HAVING COUNT(*) > 1;

25. How will you calculate the total sales in each category of a product sales table?

To calculate the total sales in each category of a product sales table, we can use the aggregate function (SUM) with the sales amount column and group it by the category column.

SELECT category, 
       SUM(sales_amt) AS Total_Sales 
FROM sales 
GROUP BY category;

26. How can you copy data from one table to another table?

we can use the INSERT INTO SELECT operator.

INSERT INTO employee_duplicate 
SELECT * 
FROM employees;

27. What is Normalization?

Normalization is used to reduce data redundancy and improve data integrity. Normalization, splits the big table into multiple sub tables and ensure that database integrity constraints are intact with their relationship with each other. It is a process of decomposing tables to eliminate data redundancy.

28. What is the difference between BETWEEN and IN operators in SQL?

The BETWEEN operator is employed to identify rows that fall within a specified range of values, encompassing numerical, textual, or date values. It returns the count of values that exist between the two defined boundaries.

On the other hand, the IN operator serves as a condition operator utilized for searching values within a predetermined range. When multiple values are available for selection, the IN operator is utilized.

29. What are DDL, DML, DCL, TCL, and DQL in SQL?

DDL: Data Definition Language is used to create, modify, and drop the schema of database objects. CREATE, ALTER TABLE, DROP, TRUNCATE, ADD COLUMN are DDL commands.

DML: Data Manipulation Language allows to change or manipulate the existing data of the tables. UPDATE, DELETE, INSERT are DML commands.

DCL: Data Control Language allows the administrator of the database to manage the rights and permissions of the users in the database. GRANT, REVOKE are DCL commands.

TCL: Transaction Control Language is used to maintain the SQL operations within the database. It also allows the changes to be saved, which are made by the DML commands. COMMIT, SET TRANSACTION, ROLLBACK, SAVEPOINT are examples of TCL commands.

DQL: Data Query Language is used to retrieve data from databases using the SELECT statement.

30. What is AUTO_INCREMENT?

AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a new record is inserted into a table. This is majorly used in the scenario where individual columns or groups of columns cannot be used as primary keys.

For Example,:

CREATE TABLE Employee (
    Employee_id INT NOT NULL AUTO_INCREMENT,
    Employee_name VARCHAR(255) NOT NULL,
    Employee_designation VARCHAR(255),
    Age INT,
    PRIMARY KEY (Employee_id)
);

31. Write an SQL syntax for joining 3 tables.

select tab1.col1, tab2.col2,tab3.col3 (columns to display) from table1
Join    ///Any type of join
table2 on tab1.col1=tab2.col1    //any matching columns
Join  ///Any type of join
table3 on tab 2.col1=tab 3.col1     //any matching columns

32. What are UNION, MINUS, and INTERSECT in SQL?

• UNION: Union operation combines results from two queries, and removes duplicates.
• MINUS: Minus operation returns rows from the first query that are not in the second query.
• INTERSECT: Intersect returns rows that are common to both queries.

33. What is a subquery?

A subquery is a query nested within another query, enabling more complex data retrieval.

34. Explain the difference between a correlated subquery and a nested subquery.

Correlated Subquery: References data from the outer query in its WHERE clause.
Nested Subquery: This can be placed anywhere in the outer query and does not directly reference the outer table.

35. Write a query to fetch employees who earn more than the average salary.

SELECT * 
FROM Intellipaat_Emp 
WHERE salary > (SELECT AVG(salary) 
                FROM Intellipaat_Emp);

36. How would you find the 2nd highest salary from a table called Employees?

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

For the Nth highest salary, replace MAX with LIMIT:

SELECT DISTINCT salary 
FROM Intellipaat_Emp 
ORDER BY salary DESC 
LIMIT N-1, 1;

37. Write a query to select only even or odd rows from a table based on an id field.

— Even rows

SELECT * 
FROM Intellipaat_Emp 
WHERE id % 2 = 0;

 — Odd rows

SELECT * 
FROM Intellipaat_Emp 
WHERE id % 2 = 1;

38. Write a query to select the top 2 salaries from each department in the Employees table.

SELECT * 
FROM (
    SELECT name, department, salary, 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank 
    FROM Intellipaat_Emp
) AS ranked 
WHERE rank <= 2;

39. If you have an id column with sequential numbers but some values are missing, write a query to find the missing numbers.

SELECT id + 1 AS missing_id 
FROM Intellipaat_Emp 
WHERE (id + 1) NOT IN (SELECT id FROM Intellipaat_Emp);

40. Write a query to swap the values in a column, for example, changing all 'Male' to 'Female' and vice versa in a column gender.

UPDATE Intellipaat_Emp
SET gender = CASE 
                WHEN gender = 'Male' THEN 'Female'
                ELSE 'Male' 
             END;

41. Write a query to find pairs of employees who have the same salary.

SELECT A.name AS employee1, B.name AS employee2, A.salary
FROM Intellipaat_Emp A 
JOIN Employees B ON A.salary = B.salary
AND A.name < B.name;

42. Write a query to find the number of days an employee has been with the company.

SELECT name, DATEDIFF(CURDATE(), joining_date) AS days_with_company 
FROM Intellipaat_Emp;

43. Find pairs of employees who were hired on the same day.

SELECT A.name AS employee1, B.name AS employee2, A.joining_date
FROM Intellipaat_Emp A, Employees B
WHERE A.joining_date = B.joining_date
AND A.name < B.name;

44. Write a query to find the median salary in each department from an Employee table.

Hint: You may use ROW_NUMBER() or PERCENT_RANK() to determine median values.

WITH RankedSalaries AS (
    SELECT department, salary, 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) AS rn_asc,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn_desc
    FROM Intellipaat_Emp
)
SELECT department, AVG(salary) AS median_salary
FROM RankedSalaries 
WHERE rn_asc = rn_desc OR rn_asc + 1 = rn_desc
GROUP BY department;

45. Write a query to get the top 10% of employees by salary.

 Hint: Use PERCENT_RANK() to filter out top percentages.

SELECT * 
FROM (
    SELECT name, salary, 
           PERCENT_RANK() OVER (ORDER BY salary DESC) AS pct_rank 
    FROM Intellipaat_Emp
) AS Ranked
WHERE pct_rank <= 0.1;

46. Write a query to calculate the cumulative salary (running total) within each department.

SELECT department, name, salary, 
SUM(salary) OVER (PARTITION BY department ORDER BY name) AS cumulative_salary
FROM Intellipaat_Emp;

47. Write a query to calculate the time gap (in hours) between consecutive logins for each user.

SELECT user_id, login_time, 
       TIMESTAMPDIFF(HOUR, 
           LAG(login_time) OVER (PARTITION BY user_id ORDER BY login_time), 
           login_time) AS hours_since_last_login
FROM Logins;

48. Write a query to get a full list of products, including products that have no sales, by performing a full outer join between product_dim and sales_fact.

SELECT p.product_id, p.product_name, SUM(s.sale_amount) AS total_sales
FROM product_dim p
LEFT JOIN sales_fact s ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name

UNION

SELECT p.product_id, p.product_name, 0 AS total_sales
FROM product_dim p
WHERE NOT EXISTS (
    SELECT 1
    FROM sales_fact s
    WHERE p.product_id = s.product_id
);

49. Write a query to calculate the year-to-date (YTD) sales for each product up to the current date in the sales_fact table.

SELECT product_id, 
       SUM(sale_amount) OVER (PARTITION BY product_id 
              ORDER BY sale_date 
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ytd_sales
FROM sales_fact
WHERE sale_date <= CURRENT_DATE
ORDER BY product_id;

50. What is a function in SQL, and why do we use functions?

A function is a database object that encapsulates a set of SQL statements that perform operations and return a specific result. To increase readability and reusability of code functions are used.

51. What is view and its type in SQL?

A view is a virtual table representing data from one or more tables without physically storing it. It can simplify complex queries.

Simple View: It is a view based on a single table and does not have a GROUP BY clause or other SQL features.

Complex View: It is a view built from several tables and includes a GROUP BY clause as well as functions.

Inline View: It is a view built on a subquery in the FROM clause, which provides a temporary table and simplifies a complicated query.

Materialized View: It is a view that saves both the definition and the details. It builds data replicas by physically preserving them.

52. What is the difference between Union and Union All operators?

The UNION and UNION ALL operators both are used for combine the output of two or more SELECT queries, but they differ in handling duplicate rows:

UNION: UNION operators combine the results of multiple SELECT queries and remove duplicate rows. It returns only distinct values across all queries.

UNION ALL: UNION ALL operator combines the output of multiple SELECT queries, including duplicates

53. What is a stored procedure?

A stored procedure is a set of SQL statements stored in the database that can be reused, promoting modular programming.

54. What do you understand about a temporary table? Write a query to create it.

Temporary table allow us to store and process the data just like a normal table; however, the benefit of using temporary tables is that it will created on the fly and will be deleted automatically. It is primarily used for complex data transformation or data selection from a large number of tables. This enables writing simple SQL queries and data manipulation activities on intermediate datasets.

Syntax:
CREATE TABLE #table_name();
The below query will create a temporary table:
create table #book(b_id int, b_cost int)
Now, we will insert the records
insert into #book values(1,100)
insert into #book values(2,232)
select * from #book

Output:

55. What do you understand by Self Join? Explain using an example.

Self Join in SQL is used for joining a table with itself. Here, depending on some conditions, each row of the table is joined with itself and with other rows of the table.

56. How would you find the second-highest salary from a table?

Code:

SELECT * FROM employee;
SELECT MAX(e_salary) 
FROM employee 
WHERE e_salary NOT IN (SELECT MAX(e_salary) FROM employee);

57. How would you optimize a slow-moving SQL query? List the SQL optimization techniques.

We can optimize a slow-moving SQL query by using indexing in the DBMS to find the specific rows in a table very quickly.

There are several optimization techniques listed below

1. Using Indexes
2. Using Distinct Clause
3. Using Having and Where clauses
4. Avoiding correlated subqueries
5. Using Limit to restrict the rows as output
6. Using Column statistics

58. How can you secure your SQL queries against SQL injection attacks?

Use prepared statements with parameterized queries to separate code from data, preventing the execution of malicious code.

59. Explain the concept of database partitioning and its benefits.

Database partitioning divides a large table into smaller segments based on a chosen key. This improves performance of the sql queries by allowing queries to run on the specific partitions and reducing I/O operations.

60. What are the third-party tools that are used in SQL Server?

The following is the list of third-party tools that are used in SQL Server:

• SQL CHECK
• SQL DOC 2
• SQL Backup 5
• SQL Prompt
• Litespeed 5.0

61. How will you fetch the most recent entries in a database?

We can fetch the most recent entries in a database by using the ORDER BY clause along with the timestamp column in descending order.

SELECT *
FROM table_name
ORDER BY timestamp_column DESC;

62. How will you find the IDs or details where there have been no entries in terms of sales?

To find the IDs or details where there have been no entries in terms of sales, we can use the LEFT JOIN or NOT EXISTS clause.

Assume we have two tables: ‘product’ with product details and ‘sales’ with sales data.

Left Joins:

SELECT p.product_id, p.product_name 
FROM product p
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE s.product_id IS NULL;

Here, the WHERE s.product_id is NULL condition helps us filter out the rows where a match in the sales table is not found.

Not Exists:

SELECT p.product_id, p.product_name 
FROM products p
WHERE NOT EXISTS (
    SELECT 1 
    FROM sales s 
    WHERE s.product_id = p.product_id
);

63. Suppose there is a database where information about the employees in various verticals is stored. Your task is to find the average salary of each vertical and the highest salary among the lot.

To find the average salary of each vertical and the highest salary among the employees, we can use the group by clause along with the aggregate functions (AVG and MAX).

SELECT vertical, 
       AVG(salary) AS average_salary, 
       MAX(salary) AS highest_salary
FROM employees
GROUP BY vertical;

Where,

vertical: column that you want to group
salary: column in the table
employees: table name

64. Given data where store inventory is stored, your task is to find the top 3 products in each category in terms of prices.

To find the top 3 products in each category in terms of price, we can group by clause along with the aggregate function (MAX) with the price column and set the limit as 3 in descending order.

SELECT category, 
       product_name, 
       MAX(price) AS max_price
FROM inventory
GROUP BY category, product_name
ORDER BY category, max_price DESC
LIMIT 3;

65. Write an SQL query to find the month-on-month sales of a specific product in a store.

To calculate the month-on-month sales of a specific product in a store, we can use a combination of date functions and aggregate functions.

SELECT EXTRACT(YEAR_MONTH FROM sale_date) AS year_month, 
       SUM(quantity_sold) AS total_sales
FROM sales
WHERE product_id = 'your_product_id'
GROUP BY year_month
ORDER BY year_month;

66. Suppose in an organization, employees are mapped under managers. Write a SQL query that will fetch you the managers and employees working under them.

To fetch the managers and employees working under them, we can use a self-join to fetch the managers and the employees working under them.

SELECT M.manager_id AS manager_id, 
       M.manager_name AS manager_name, 
       E.employee_id AS employee_id, 
       E.employee_name AS employee_name
FROM employees E
JOIN employees M ON E.manager_id = M.employee_id
ORDER BY M.manager_id, E.employee_id;

67. In a store inventory, your task is to fetch the total quantity of the top product purchased by the customers.

To fetch the total quantity of the top product purchased by the customers, we can use a group by clause along with the limit in descending order.

SELECT product_id, 
       SUM(quantity_purchased) AS total_quantity_purchased
FROM purchases
GROUP BY product_id
ORDER BY total_quantity_purchased DESC
LIMIT 1;

68. Given a Supervision table with employee_id and manager_id, write a query to detect if there is a cycle in the reporting hierarchy.

WITH RECURSIVE Hierarchy
WITH RECURSIVE Hierarchy AS (
    SELECT employee_id, manager_id, employee_id AS root 
    FROM Supervision
    UNION ALL
    SELECT H.employee_id, S.manager_id, H.root
    FROM Hierarchy H
    JOIN Supervision S ON H.manager_id = S.employee_id
    WHERE H.root <> S.manager_id
)
SELECT root 
FROM Hierarchy
GROUP BY root
HAVING COUNT(DISTINCT manager_id) <> COUNT(manager_id);

69. You need to create a materialized view to store the monthly total sales by product for faster reporting. Write the SQL to create this view.

CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
    product_id,
    YEAR(sale_date) AS year,
    MONTH(sale_date) AS month,
    SUM(sale_amount) AS total_sales
FROM sales_fact
GROUP BY product_id, YEAR(sale_date), MONTH(sale_date);

70. Write a query that detects missing dates in a sequence from a sales table. The sales table contains sale_date and sale_amount, and you need to find any missing dates between the earliest and latest sales dates.

WITH DateSeries AS (
    SELECT MIN(sale_date) AS start_date, MAX(sale_date) AS end_date
    FROM sales
)
SELECT DATE_ADD(start_date, INTERVAL seq DAY) AS missing_date
FROM DateSeries,
     (SELECT @rownum := @rownum + 1 AS seq 
      FROM sales, 
           (SELECT @rownum := 0) AS r) AS seq_numbers
WHERE DATE_ADD(start_date, INTERVAL seq DAY) <= end_date
AND DATE_ADD(start_date, INTERVAL seq DAY) NOT IN (SELECT sale_date FROM sales)
ORDER BY missing_date;

71. You have an order table with millions of rows and you frequently run a query that filters it by customer_id, order_date, and status. What indexes would you create to optimize this query, and why?

We need to create a composite index on columns that are frequently used for filtering

CREATE INDEX idx_orders_customer_date_status
ON orders (customer_id, order_date, status);

72. Write a query using a Common Table Expression (CTE) to rank customers by total purchase amount and return the top 10 customers.

WITH RankedCustomers AS (
    SELECT customer_id, SUM(purchase_amount) AS total_spent,
           RANK() OVER (ORDER BY SUM(purchase_amount) DESC) AS rank
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM RankedCustomers
WHERE rank <= 10;

73. Write an UPDATE query to set the total_sales to the sum of individual sales amounts for each employee in the employee table.

UPDATE Intellipaat_Emp e
SET total_sales = (
    SELECT SUM(sale_amount)
    FROM sales s
    WHERE s.employee_id = e.employee_id
)
WHERE EXISTS (
    SELECT 1
    FROM sales s
    WHERE s.employee_id = e.employee_id
);

74. Can you identify the employee who has the third-highest salary from the given employee table (with salary-related data)?

Consider the following employee table. In the table, Sabid has the third-highest salary (60,000).

Below is a simple query to find out which employee has the third-highest salary. The functions RANK, DENSE RANK, and ROW NUMBER are used to obtain the increasing integer value by imposing the ORDER BY clause in the SELECT statement, based on the ordering of the rows. The ORDER BY clause is necessary when the RANK, DENSE RANK, or ROW NUMBER functions are used. On the other hand, the PARTITION BY clause is optional.

WITH CTE AS
(
    SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RN
    FROM EMPLOYEE
)
SELECT Name, Salary 
FROM CTE 
WHERE RN = 3;

CTA

Conclusion:

In conclusion, we covered the top 70+ SQL interview questions and answers in this article that help you prepare and crack SQL jobs. We focused on covering essential concepts of SQL like  basics of SQL queries, joins, subqueries, indexing, and performance optimization. Whether you are a beginner or expert, understanding these topics will help you confidently handle SQL  interviews.

SQL Cheat Sheets

Go through the following SQL Cheat Sheets and download their PDF:

• Download the PDF of the SQL Basics Cheat Sheet
• Download the PDF for SQL Constraints, Joins, Set Operators Cheat Sheet
• Download the PDF of SQL Grouping, inbuilt, subquery, views, and temp table Cheat Sheet
• Download the PDF for SQL User Define Function Cheat Sheet
• Download the PDF for SQL Exception Handling Index, Pivot, Transactions Cheat Sheet