SQL Interview Questions and Answers

Preparing for a SQL interview? This article covers the top SQL interview questions and answers to help freshers and experienced professionals crack interviews at leading tech companies. SQL (Structured Query Language) is a key skill for managing and querying databases.

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

A Primary Key uniquely identifies each record in a table and cannot be NULL, whereas a Unique Key ensures uniqueness but allows one NULL value.

2. What is the difference Between WHERE Clause and the HAVING Clause

The WHERE clause filters rows before grouping, while HAVING filters grouped data after aggregation.

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

1. DDL: Data Definition Language is used to create, modify, and drop the schema of database objects. CREATE, ALTER TABLE, DROP, TRUNCATE, and ADD COLUMN are DDL commands.
2. DML: Data Manipulation Language allows for changing or manipulating the existing data of the tables. UPDATE, DELETE, and INSERT are DML commands.
3. DCL: Data Control Language allows the administrator of the database to manage the rights and permissions of the users in the database. GRANT and REVOKE are DCL commands.
4. 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, and SAVEPOINT are examples of TCL commands.
5. DQL: Data Query Language is used to retrieve data from databases using the SELECT statement.

SQL Difference-Based Interview Questions

4. What is the difference between the Drop vs Delete commands?

DELETE removes rows from a table, while DROP removes the entire table structure permanently.

5. What is the difference between BETWEEN and IN Operators

BETWEEN selects values within a range, while IN matches values from a specified list.

6. What is the difference between UNION and UNION ALL

UNION removes duplicate records, whereas UNION ALL includes all records, including duplicates.

7. What is the difference between SQL and NoSQL Databases

SQL databases are relational, table-based, and schema-driven, while NoSQL databases are non-relational, schema-flexible, and designed for high scalability.

8. What is the difference between SQL and PL/SQL

SQL is used to query and manage data, whereas PL/SQL adds procedural capabilities like loops, conditions, and exception handling.

9. What is the difference between On-Premise SQL and Cloud SQL

On-premise SQL runs on local infrastructure, while Cloud SQL is managed, scalable, and hosted on cloud platforms.

10. What is the difference between INNER JOIN and SELF JOIN

INNER JOIN combines rows from two tables based on a condition, while SELF JOIN joins a table with itself.

11. What is the difference between LEFT JOIN and RIGHT JOIN

A LEFT JOIN returns all records from the left table and matching records from the right, whereas a RIGHT JOIN returns all records from the right table and matching records from the left.

12. What is the difference between a FULL JOIN and a CARTESIAN JOIN

A FULL JOIN returns all matching and non-matching rows from both tables, while a CARTESIAN JOIN returns every possible row combination.

13. What is the difference between JOINs and the UNION Clause

JOINs combine columns from multiple tables, while UNION combines rows from multiple SELECT queries.

14. What is the difference between NULL, 0, and Empty String (“”)?

NULL means no value, 0 is a numeric value, and an empty string represents a value with no characters.

15. Difference between CHAR, VARCHAR, and VARCHAR2

CHAR is a fixed-length datatype, VARCHAR is a variable-length datatype, and VARCHAR2 is an Oracle-specific datatype recommended for safe and efficient string storage.

16. Difference between Clustered and Non-Clustered Indexes

A clustered index defines the physical order of data in a table, while a non-clustered index maintains a separate structure with pointers to data rows.

17. Difference between OLTP and OLAP

OLTP systems handle real-time transactional operations, whereas OLAP systems are designed for complex analytical queries and reporting.

18. Difference between RDBMS and DBMS

A DBMS stores and manages data, while an RDBMS organizes data into relational tables with keys, constraints, and relationships.

SQL Basics & Core Concepts Interview Questions

19. 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 the unique identification of each record in a table. We can say that it combines NOT NULL and UNIQUE.
• FOREIGN KEY: Links records in two tables.

20. What is the COALESCE function?

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

Syntax:

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

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

In SQL, UNION, MINUS, and INTERSECT are set operators used to combine or compare results from multiple SELECT queries:

1. UNION:
   Combines the results of two SELECT statements and removes duplicate rows.
   Use when you want to merge datasets without duplicates.
2. MINUS:
   Returns rows from the first SELECT that are not found in the second.
   Used to identify differences between two datasets.
3. INTERSECT:
   Returns only the rows that are common to both SELECT statements.
   Use when you need data that appears in both datasets.

22. What is a view in SQL, and what are its types?

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

There are four types of views in SQL:

1. Simple View: It is a view based on a single table and does not have a GROUP BY clause or other SQL features.
2. Complex View: This is a view built from several tables and includes a GROUP BY clause as well as functions.
3. Inline View: It is a view built on a subquery in the FROM clause, which provides a temporary table and simplifies a complicated query.
4. Materialised View: This is a view that saves both the definition and the details. It builds data replicas by physically preserving them.

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

A temporary table is a table that is created to store data temporarily during a session. It exists only while the session is active or until it is dropped manually.

It’s useful for storing intermediate results when working with complex queries or large data.

Example:

-- Create a temporary table named 'TempEmployees'
CREATE TEMPORARY TABLE TempEmployees (
-- Define a column 'ID' of integer type
ID INT,
-- Define a column 'Name' that can store up to 50 characters
Name VARCHAR(50) );
 copy

24. What are ACID properties?

ACID stands for atomicity, consistency, isolation, and durability. These properties ensure the reliable processing of database transactions.

25. When will you use the DISTINCT keyword?

• DISTINCT is used to eliminate duplicate rows.
• SELECT COUNT (DISTINCT Table_name). This can be used to count the unique records.
• However, using it multiple times can impact the table’s performance.

26. 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 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.

27. How will you prevent SQL injection?

SQL injection can be prevented very efficiently if we use parameterized queries or prepared statements instead of dynamic SQL, where we concatenate strings.

So instead of writing:

"SELECT * FROM users WHERE id = " + user_input; copy

You should write:

"SELECT * FROM users WHERE id = %s" copy

Now, user input is only data, no matter how the code is executed. This method makes sure that even if the user decides to insert some illegal SQL commands, they will not be executed. Prepared statements are available in most SQL libraries of today, and they are regarded as the most suitable method for implementing application security against SQL injection attacks.

28. 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. SQL functions are used to increase the readability and reusability of code.

29. What is normalization? Explain its types.

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

Types of normalization:

• 1NF: Ensures that each column contains only atomic (indivisible) values.
• 2NF: Remove partial dependencies.
• 3NF: Remove transitive dependencies.
• BCNF: Ensures that every determinant is a candidate key.

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 mainly used in scenarios where individual columns or groups of columns cannot be used as primary keys.

For Example:

-- Create a table named 'Employee'
CREATE TABLE Employee (
-- Use AUTO_INCREMENT to automatically generate a unique ID for each new record
Employee_id INT NOT NULL AUTO_INCREMENT,
Employee_name VARCHAR(255) NOT NULL,
Employee_designation VARCHAR(255),
Age INT,
-- Set 'Employee_id' as the AUTO_INCREMENT Primary Key of the table
PRIMARY KEY (Employee_id) ); copy

31. What are aggregate functions in SQL?

Aggregate functions in SQL carry out a computation over a collection of values and produce a single outcome. Typical instances encompass:

• COUNT(): Returns the number of rows.
• SUM(): Returns the total sum.
• AVG(): Returns the average value.
• MIN() / MAX(): Return the minimum or maximum value, respectively.

32. What are SQL dialects?

SQL dialects are generally the variations of standard SQL syntax and features that are implemented by different database vendors. Examples include:

• T-SQL for Microsoft SQL Server
• PL/SQL for Oracle
• pgSQL for PostgreSQL
• MySQL’s extended SQL

Though the main SQL syntax is still fairly the same, these dialects bring in proprietary functions, procedural abilities, and performance features that are designed specifically for particular systems.

33. What are the different types of SQL operators?

SQL supports several operator categories:

• Arithmetic Operators: +, -, *, / (perform calculations)
• Comparison Operators: =, <, >, <=, >=, <> (used in WHERE clauses)
• Logical Operators: AND, OR, NOT (combine multiple conditions)
• Set Operators: UNION, INTERSECT, EXCEPT (combine result sets)

Understanding how to use these operators is crucial for writing efficient and readable SQL queries.

34. How to select all columns from a table?

In SQL, to get all the columns from a table, you can use the wildcard character asterisk (*)

SELECT * FROM table_name; copy

The above query retrieves all the columns of a table. Although it is a good practice during development, in production, it is advisable to explicitly mention the columns to be retrieved so as to increase performance and readability.

35. What are scalar functions in SQL?

Scalar functions in SQL are built-in functions that take one or more inputs and return a single value. Some of the common Scalar functions.

• UPPER() / LOWER() – Converts text to uppercase or lowercase, respectively
• LEN() – Returns the number of characters in a string
• ROUND() – Rounds a numeric value to the specified number of decimal places
• GETDATE() – Retrieves the current system date and time

These functions are useful for data formatting, transformation, and validation within SQL queries.

36. What are indexed views?

An indexed view is a view in SQL that has a unique clustered index created on it, thus only the data of the view result is physically stored on the disk. In comparison to traditional views, indexed views improve performance by calculating the joins and aggregations before execution. Indexed views are most beneficial in SQL Server to improve performance in OLAP scenarios.

37. What are the different isolation levels in SQL?

SQL supports five isolation levels to handle concurrency in transactions:

1. Read Uncommitted: Allows dirty reads.
2. Read Committed: Default in most databases, and it avoids dirty reads.
3. Repeatable Read: Prevents dirty and non-repeatable reads.
4. Serializable: Highest isolation in order to prevent phantom reads.
5. Snapshot: Maintains versioned data to avoid locking.

These levels balance between data consistency and performance in SQL

38. What is SQL Server Integration Services (SSIS)?

SQL Server Integration Services (SSIS) is a platform developed by Microsoft to carry out ETL (Extract, Transform, Load) functions. It generally allows data migration, transformation, and workflow automation across various data sources. Typically, SSIS is very useful for building data warehouses, which makes it essential for Business Intelligence(BI) projects.

39. What are the applications of SQL?

SQL is a popular language in various sectors and career roles. Its most important applications are:

• Handling and searching in relational databases
• Carrying out data analysis and generating reports
• Creating backend logic in apps
• Helping ETL pipelines in data warehousing
• Maintaining data integrity and setting up access control

SQL flexibility turns it into a fundamental skill that data analysts, software developers, DBAs, and BI professionals all have in common.

40. What is DESC in SQL?

In SQL, DESC stands for descending. It is used to sort records in descending order, i.e., highest to lowest. It is usually clubbed with the ORDER BY clause to sort records. Here is an example of the same:

SELECT * FROM employees ORDER BY salary DESC; copy

41. What is a schema in SQL?

In SQL, a schema can be termed as a structure that groups tables, views, databases, and stored procedures. Using a schema prevents conflict and allows two names to exist in parallel, divided by the schema. Here is an example of the same:

CREATE SCHEMA sales;
CREATE TABLE sales.orders (
order_id INT,
order_date DATE );
 copy

42. Can we rollback DELETE?

Yes, after using the DELETE command, you can roll back if you are using the TRANSACTION command. DELETE is a DML command, so when you rollback, all the transactions are undone and the records are restored. Here is an example of the same:

BEGIN TRANSACTION; -- Start a transaction
DELETE FROM employees
WHERE employee_id = 101; -- This delete happens temporarily
ROLLBACK; -- Undo the delete
 copy

43. Explain how CASE statements work in SQL.

CASE provides if-then-else logic in SQL like this:

SELECT product_name, CASE
WHEN price > 100 THEN 'Premium'
WHEN price > 50 THEN 'Standard'
ELSE 'Budget' END AS tier FROM products copy

44. What is a pivot in SQL?

The PIVOT command is used to summarise the data. It converts rows into columns, which helps in better analysis. Let’s understand this using an example:

Input Data:

Question: Find the sales of Product A and Product B in 2022 and 2023

Output Data:

Query Used:

SELECT
product, [2022] AS sales_2022, [2023]
AS sales_2023 FROM
( SELECT product, year, sales FROM sales )
AS SourceTable PIVOT ( SUM(sales) FOR year IN ([2022], [2023]) )
AS PivotTable; copy

45. What is a dynamic SQL query?

A Dynamic SQL query is a technique in SQL wherein the query is built during runtime, giving dynamic inputs in the query. This makes the query flexible enough to create multiple use cases using the same query. Here is an example of the same:

DECLARE @tableName NVARCHAR(50) = 'employees';
DECLARE @query NVARCHAR(MAX);
SET @query = 'SELECT * FROM ' + @tableName + '
WHERE department = ''HR'''; EXEC(@query); copy

46. What is RDBMS? How is it different from a standard DBMS?

An RDBMS (Relational Database Management System) stores data in structured tables with rows and columns and maintains relationships using keys, whereas a DBMS stores data without enforcing relationships.

• RDBMS supports primary keys, foreign keys, and normalization
• DBMS does not enforce relationships between data
• RDBMS ensures better data integrity and consistency
• RDBMS supports multi-user access efficiently

Examples of RDBMS include MySQL, PostgreSQL, and Oracle.

47. What is an Alias in SQL?

An Alias in SQL is a temporary name assigned to a table or column to make SQL queries more readable and easier to understand.

• Improves query readability
• Helps simplify complex column names
• Useful in joins and calculated columns
• Exists only during query execution

Aliases do not change the actual table or column names in the database.

48. What is the order of execution in an SQL query?

The order of execution in SQL defines how a query is processed internally by the database engine, which is different from how the query is written.

1. FROM – Identifies the source tables
2. WHERE – Filters rows
3. GROUP BY – Groups rows
4. HAVING – Filters grouped data
5. SELECT – Selects columns
6. ORDER BY – Sorts the final result

Understanding this order helps write correct and optimized SQL queries.

49. What is the NULL value? Is it the same as zero or a blank space?

A NULL value represents the absence of data and is not the same as zero or a blank space.

• NULL means unknown or missing value
• Zero (0) is a numeric value
• Blank space ("") is an empty string
• NULL cannot be compared using = and requires IS NULL

NULL behaves differently in calculations and comparisons and does not represent any actual value.

SQL Joins & Table Relationships Interview Questions

50. What is SQL JOIN?

The SQL JOIN component joins rows from one or more tables in a relational database. Create sets that can be stored in tabular form or used routinely. JOIN is to combine columns from one table or multiple tables using the same value.

51. What are the different types of JOINs in SQL?

There are six different types of JOINs in SQL, which are:

• INNER JOIN: An INNER JOIN is used to return records of the same value in two tables.
• LEFT JOIN: LEFT JOIN is used to join all the rows in the left table with matching rows in the right table.
• RIGHT JOIN: RIGHT JOIN is used to join all the rows in the right table with the corresponding rows in the left table.
• FULL JOIN: A FULL JOIN is used to return all records from two tables, if there are matching records in each table.
• SELF JOIN: A SELF JOIN is a join used to join a table to itself. SELF JOINS treats one table as two tables.
• CARTESIAN JOIN: A CARTESIAN Integral is used to multiply the number of rows in the first table by the number of rows in the second table. It is also called a CROSS JOIN.

52. Is a SELF JOIN an INNER JOIN or an OUTER JOIN?

The SELF JOIN can be an INNER JOIN, OUTER JOIN, or a CROSS JOIN. SELF JOIN is used to link tables automatically based on columns that contain duplicate data in multiple rows.

53. What is NATURAL JOIN?

A NATURAL JOIN in SQL is a kind of join that automatically combines two tables only by the common columns that have the same name and compatible data types. Unlike INNER JOIN or EQUI JOIN, a NATURAL JOIN doesn’t require an explicit join condition using the ON clause.

It implicitly matches columns with identical names in both tables and returns rows where the values in those columns are equal.

54. What is an EQUI JOIN?

An EQUI JOIN is a type of join operation in a database that combines rows from two or more tables based on a matching condition using the equality operator (=). It is used to retrieve data where values in specified columns are equal.

Here is an example of the syntax for an EQUI JOIN:

SELECT column_name(s) FROM table1 JOIN table2
-- Matches rows
where values in the specified columns are equal ON
table1.column_name = table2.column_name. copy

55. 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.

56. What is a HASH JOIN?

A HASH JOIN requires two inputs, an INNER table and an OUTER table. HASH JOINS involve using a HASH table to identify matching rows between two tables. HASH JOINS are an option when other joins are not recommended. When joining large data sets that are unsorted or non-indexed, HASH JOINS are better.

57. What is MERGE JOIN?

MERGE JOIN is one of the most important join types in SQL Server. In MERGE JOIN, your query plan is effective, and you don’t need to make many changes to improve query performance because the MERGE JOIN operator uses ordered data entry, it can use two large data sets.

58. Can you explain NESTED JOIN in SQL?

In SQL, a Nested Join (also referred to as a Nested Loop Join) is the basic method that most relational databases employ for connecting two tables. It is the default approach that database engines frequently use for joining small or medium datasets.

In a Nested Join, one table is treated as the outer table, and for each row in this outer table, the system searches for matching rows in the inner table. This process repeats until all matching combinations are found.

59. How do you map a Many-to-Many relationship?

A Many-to-Many relationship is mapped using a Junction Table (also called a Bridge Table) that stores the relationships between two tables.

In a many-to-many relationship, multiple records in one table can be related to multiple records in another table. Since relational databases do not support many-to-many relationships directly, a junction table is used to resolve this.

• The junction table contains foreign keys from both related tables
• Each foreign key references the primary key of a parent table
• The combination of foreign keys usually forms a composite primary key
• Ensures data integrity and avoids redundancy

60. What is a Cross-Join (Cartesian Product)?

A Cross Join produces a Cartesian Product by combining every row from one table with every row from another table.

If the first table has N rows and the second table has M rows, the result of a cross join will contain N × M rows.

• No join condition is required
• Produces all possible row combinations
• Can generate very large result sets
• Mostly used for testing or generating combinations

61. Explain Common Table Expressions in SQL.

In general, a Common Table Expressions (CTEs) is a temporary, named result set that can be used to refer to an UPDATE, INSERT, SELECT, or DELETE statement. A CTE can be specified by adding WITH before an UPDATE, INSERT, DELETE, SELECT, or MERGE statement. Multiple CTEs can be used in the WITH clause by separating them with commas.

62. What is a recursive CTE?

A recursive CTE (Common Table Expression) is a SQL construct that enables a query to refer to itself in order to carry out iterative tasks. Generally, it is used to handle hierarchical or tree-like data, such as employee-management hierarchies or category-subcategory structure

Example syntax:

WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id FROM employees
WHERE manager_id IS NULL UNION ALL
SELECT e.id, e.name, e.manager_id FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id )
SELECT * FROM employee_hierarchy;  copy

Understand how hierarchical recursive queries work in MySQL through this blog. 

63. How will you structure data to perform a JOIN Operation in a one-to-many relationship situation?

To create a one-to-many relationship, you need to add the primary key from one side to many sides as a column. To create a many-to-many relationship, you need a middle table that contains the primary keys from the many-to-many tables.

SQL Query-Based & Scenario-Driven Interview Questions

64. 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 AS (
-- Base case SELECT employee_id, manager_id,
CAST(employee_id AS VARCHAR(200))
AS path FROM Supervision UNION ALL
-- Recursive step
SELECT s.employee_id, s.manager_id,
CONCAT(h.path, '->', s.employee_id)
AS path FROM Supervision s INNER JOIN Hierarchy h ON s.manager_id = h.employee_id
-- Stop recursion if cycle detected
WHERE h.path NOT LIKE CONCAT('%', s.employee_id, '%') )
-- Detect cycles where employee appears again in path
SELECT DISTINCT employee_id, path FROM Hierarchy
WHERE path LIKE CONCAT('%', employee_id, '%'); copy

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

-- Update total_sales column in the employee table
UPDATE Intellipaat_Emp e SET total_sales = (
-- Calculate total sales per employee
SELECT COALESCE(SUM(s.sale_amount), 0)
FROM sales s WHERE s.employee_id = e.employee_id )
-- Update only employees who have sales records
WHERE EXISTS ( SELECT 1 FROM sales s
WHERE s.employee_id = e.employee_id ); copy

66. Write an SQL syntax for joining 3 tables.

SELECT tab1.col1, tab2.col2, tab3.col3
-- Columns to display FROM table1 tab1
JOIN table2 tab2 ON tab1.col1 = tab2.col1
-- Matching condition
JOIN table3 tab3 ON tab2.col1 = tab3.col1;
-- Matching condition copy

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

SELECT name, age
-- Select specific columns FROM Employees;
-- Table name copy

68. Write a query to get employees older than 35 and working in the operations department.

SELECT * FROM Intellipaat_Emp
-- Table name WHERE age > 35
-- Age greater than 35 AND department = 'operations';
 copy

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

SELECT department, AVG(salary) AS avg_salary
-- Calculate the average salary FROM Intellipaat_Emp GROUP BY department;
-- Group results by department
 copy

70. Write a query to find employees whose names start with ‘Int’.

SELECT * FROM Intellipaat_Emp
WHERE employee_name LIKE 'Int%';
-- Names starting with 'Int' copy

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

INSERT INTO Intellipaat_Emp (
Employee_name, Age, Department, Salary)
VALUES ('John Doe', 28, 'Marketing', 50000); copy

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

SELECT * FROM Intellipaat_Emp ORDER BY Employee_id DESC
-- Sort by ID in descending order LIMIT 5;
-- Return last 5 records copy

73. Write a query to label employees with salaries above 5000 as “High Salary.”

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

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

SELECT e.Employee_name, p.project_name FROM Intellipaat_Emp
e LEFT JOIN Projects p ON e.project_id = p.id;
-- LEFT JOIN ensures NULL if no project copy

75. 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, emp.Employee_name
FROM Departments dept
LEFT JOIN Intellipaat_Emp emp ON emp.DepartmentID
= dept.DepartmentID; copy

76. 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'; copy

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

SELECT Employee_name FROM Intellipaat_Emp
GROUP BY Employee_name HAVING COUNT(*) > 1; copy

78. Find all duplicate rows in the table Employees, considering all columns.

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER
( PARTITION BY Employee_name, Age, Department, Salary )
AS rn FROM Intellipaat_Emp ) t WHERE rn > 1; copy

79. 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
-- Calculate total sales per category FROM sales
GROUP BY category; -- Group totals by category copy

80. 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; copy

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

SELECT * FROM Intellipaat_Emp
-- Compare each employee's salary to the average salary
WHERE salary > (SELECT AVG(salary) FROM Intellipaat_Emp);  copy

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

SELECT MAX(salary)
-- Filter out the top salary FROM Intellipaat
_Emp WHERE salary < (SELECT MAX(salary)
-- Filter out the top salary FROM Intellipaat_Emp);  copy

For the Nth highest salary, replace MAX with LIMIT:

SELECT DISTINCT salary FROM Intellipaat
_Emp ORDER BY salary DESC
-- Sort salaries from highest to lowest LIMIT N-1, 1;
-- Skip the top (N-1) salaries and return the Nth highest one copy

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

-- Select employees with EVEN Employee_id
SELECT * FROM Intellipaat_Emp WHERE Employee_id % 2 = 0;
-- IDs divisible by 2 are even
-- Select employees with ODD Employee_id SELECT * FROM
Intellipaat_Emp WHERE Employee_id % 2 = 1;
-- IDs not divisible by 2 are odd  copy

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

-- Select top 2 highest-paid employees per department
SELECT Employee_name, Department, Salary FROM
( SELECT Employee_name, Department, Salary, ROW_NUMBER() OVER
( PARTITION BY Department ORDER BY Salary DESC )
AS salary_rank FROM Intellipaat_Emp ) ranked WHERE salary_rank <= 2;  copy

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

-- Find missing Employee_id values in sequence
SELECT (e.Employee_id + 1) AS missing_id FROM Intellipaat
_Emp e WHERE (e.Employee_id + 1) NOT IN
( SELECT Employee_id FROM Intellipaat_Emp );  copy

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

-- Swap Male to Female and Female to Male
UPDATE Intellipaat_Emp SET Gender = CASE
WHEN Gender = 'Male' THEN 'Female' WHEN Gender = 'Female'
THEN 'Male' ELSE Gender -- Keep other values unchanged END;  copy

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

-- Find employee pairs who earn the same salary
SELECT A.Employee_name AS employee1,
B.Employee_name AS employee2, A.Salary FROM Intellipaat
_Emp A JOIN Intellipaat_Emp B ON A.Salary = B.Salary
AND A.Employee_name < B.Employee_name;
-- Avoid self-match & duplicate pairs copy

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

-- Calculate number of days since employee joined SELECT Employee_name, DATEDIFF(CURDATE(), Joining_date) AS days_with_company FROM Intellipaat_Emp; copy

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

-- Find employee pairs hired on the same date
SELECT A.Employee_name AS employee1, B.
Employee_name AS employee2, A.Joining_date
FROM Intellipaat_Emp A JOIN Intellipaat_Emp B
ON A.Joining_date = B.Joining_date AND A.
Employee_name < B.Employee_name; -- Avoid duplicate pairs copy

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

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

-- Calculate median salary per department WITH RankedSalaries AS ( SELECT Department, Salary, -- Rank salaries in ascending order ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary) AS rn_asc, -- Rank salaries in descending order ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn_desc FROM Intellipaat_Emp ) -- Select middle salary (median) SELECT Department, AVG(Salary) AS median_salary FROM RankedSalaries WHERE rn_asc = rn_desc OR rn_asc + 1 = rn_desc GROUP BY Department; copy

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

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

-- Select top 10% highest-paid employees SELECT * FROM ( SELECT Employee_name, Salary, -- Rank employees by descending salary percentage PERCENT_RANK() OVER (ORDER BY Salary DESC) AS pct_rank FROM Intellipaat_Emp ) Ranked WHERE pct_rank <= 0.1; -- Top 10% earners copy

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

-- Calculate running total salary within each department SELECT Department, Employee_name, Salary, -- Running total ordered by salary SUM(Salary) OVER ( PARTITION BY Department ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_salary FROM Intellipaat_Emp; copy

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

-- Calculate time difference in hours between consecutive logins per user
SELECT user_id, login_time, TIMESTAMPDIFF( HOUR, LAG(login_time)
OVER ( PARTITION BY user_id -- Restart for each user
ORDER BY login_time -- Compare chronologically ),
login_time -- Current login time )
AS hours_since_last_login FROM Logins;  copy

94. 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.

-- Return all products, including those with no sales SELECT p.product_id, p.product_name, COALESCE(SUM(s.sale_amount), 0) 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;  copy

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

-- Calculate Year-To-Date sales per product
SELECT product_id, SUM(sale_amount) AS ytd_sales
FROM sales_fact WHERE sale_date >=
DATE_FORMAT(CURRENT_DATE, '%Y-01-01')
AND sale_date <= CURRENT_DATE
GROUP BY product_id;  copy

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

-- Second-highest salary using ranking
SELECT e_salary FROM ( SELECT e_salary, DENSE_RANK() OVER (
ORDER BY e_salary DESC) AS rnk FROM employee ) ranked
WHERE rnk = 2;  copy

97. 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;
-- Sort by timestamp in descending order copy

98. 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;  copy

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 );  copy

99. 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,    -- Grouping by vertical AVG(salary) AS average_salary,    -- Calculate average salary for each vertical MAX(salary) AS highest_salary    -- Find the highest salary in each vertical FROM employees GROUP BY vertical;    -- Group the results by vertical  copy

Where,

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

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

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.

-- Get top 3 highest-priced products in each category SELECT category, product_name, price FROM ( SELECT category, product_name, price, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY price DESC ) AS price_rank FROM inventory ) ranked WHERE price_rank <= 3;  copy

101. 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.

— Extract year and month from the sale date

-- Calculate month-on-month sales for a specific product SELECT DATE_FORMAT(sale_date, '%Y-%m') 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;  copy

102. Suppose in an organisation, employees are mapped under managers. Write an 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.

-- Fetch managers and their reporting employees SELECT M.employee_id AS manager_id, M.employee_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.employee_id, E.employee_id;  copy

103. 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.

-- Find the product with the highest total quantity purchased
SELECT product_id, SUM(quantity_purchased)
AS total_quantity_purchased FROM purchases
GROUP BY product_id
ORDER BY total_quantity_purchased
DESC LIMIT 1;  copy

104. You need to create a materialised 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);  copy

105. 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.

-- Generate all dates between min and max sale_date
WITH RECURSIVE DateSeries AS ( SELECT MIN(sale_date)
AS date_value, MAX(sale_date) AS max_date
FROM sales UNION ALL SELECT date_value + INTERVAL 1
DAY, max_date FROM DateSeries
WHERE date_value < max_date ) -- Find missing dates
SELECT date_value AS missing_date FROM DateSeries
WHERE date_value NOT IN ( SELECT sale_date FROM sales )
ORDER BY missing_date; copy

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

-- Rank customers by total purchase amount and return
top 10 WITH RankedCustomers AS (
SELECT customer_id, SUM(purchase_amount) AS total_spent,
-- Rank customers by spending (highest first) DENSE_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; copy

107. 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; copy

108. Get the customer name and product name ordered by first name from the customer

SELECT first_name, b.Product_name
FROM [customer] A LEFT OUTER JOIN [Product] B
-- Left join to include customers without orders ON A.
customer_id = B.customer_id
-- Join condition on customer_id
ORDER BY a.first_name
-- Order the result by customer's first name<span style="font-weight: 400"> </span> copy

109. Get the customer’s name and the product name ordered by first name

SELECT a.First_Name, ISNULL(b.
Product_name,'-No Project Assigned')
<span style="font-weight: 400">AS Product_Name</span>
FROM customer A LEFT OUTER JOIN product
B ON A.customer_id = B.customer_id
ORDER BY a.first_name copy

110. Get all product names, even if they have not matched any customer ID, in the left table, order by first name

SELECT a.first_name,b.
Product_name FROM [customer]
A RIGHT OUTER JOIN [product] B ON a.
customer_id = b.customer_id
ORDER BY a.first_name copy

111. Get the complete record (customer name, product name) from both tables ([CustomerDetail], [ProductDetail]); if no match is found in any table, then show NULL.

SELECT a.first_name,b.Product_name
FROM [customer] A FULL OUTER JOIN [product]
B ON a.customer_id = b.customer_id
ORDER BY a.first_name copy

112. Write a query to find out the product name that is not assigned to any employee( tables: [CustomerDetail],[ProductDetail]).

SELECT b.Product_name FROM [customer]
A RIGHT OUTER JOIN [product] B ON a.
customer_id = b.customer_id
WHERE a.first_name IS NULL copy

113. Write down the query to fetch the ProductName on which more than one customer is working, along with the CustomerName.

Select P.Product_name, c.first_name from product P
INNER JOIN customer c on p.customer_id = c.
customer_id where P.Product_name in
(select Product_name from product
group by Product_name having COUNT(1)>1)  copy

*The output will not come as there is no duplicate record in the product table.

114. Write a query to find the Nth highest salary from a table

To find the Nth highest salary, the DENSE_RANK() window function is commonly used because it handles duplicate salaries correctly.

SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (
ORDER BY salary DESC) AS rank_num FROM employees )
ranked_salaries WHERE rank_num = N;  copy

Replace N with the required position (for example, 2 for second highest salary).

115. Write a query to find and remove duplicate rows from a table

Duplicate rows can be identified using ROW_NUMBER() and removed by keeping only the first occurrence.

DELETE FROM employees WHERE id NOT IN
( SELECT MIN(id) FROM employees
GROUP BY name, salary );  copy

This query keeps one unique record and removes all duplicates based on the selected columns.

116. How do you pattern match for names starting with “A” and ending with “n”?

The LIKE operator is used for pattern matching. The pattern 'A%n' matches names that start with A and end with n.

SELECT * FROM employees
WHERE name LIKE 'A%n';  copy

Here, % represents zero or more characters between A and n.

117. Write a query to show the current date and time

Different databases use different functions to return the current date and time.

SQL Server:

SELECT GETDATE();  copy

MySQL:

SELECT NOW();  copy

Both queries return the current system date and time of the database server.

118. How do you create an empty table with the same structure as another table?

An empty table with the same structure can be created using SELECT INTO with a condition that always evaluates to false.

SELECT * INTO new_table
FROM existing_table WHERE 1 = 0;  copy

This copies only the table structure, not the data.

119. How do you check if a table exists before creating it?

Checking for table existence helps prevent errors when creating tables.

SQL Server:

IF NOT EXISTS ( SELECT * FROM sys.tables
WHERE name = 'employees' ) BEGIN
CREATE TABLE employees ( id INT, name VARCHAR(50) ); END  copy

MySQL:

CREATE TABLE IF NOT EXISTS
employees ( id INT, name VARCHAR(50) );  copy

These approaches ensure the table is created only if it does not already exist.

Advanced SQL & Database Architecture Interview Questions

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

You can optimize a slow-moving SQL query by using specific performance tuning techniques.

There are several SQL query optimization techniques listed below:

1. Use indexes on frequently filtered or joined columns.
2. Avoid SELECT *; select only the required columns.
3. Filter early with proper WHERE clauses.
4. Analyse the query with EXPLAIN to find bottlenecks.
5. Replace correlated subqueries with joins or CTEs.
6. Limit results using LIMIT or pagination.
7. Ensure efficient joins and avoid unnecessary sorting.

These steps help improve performance by reducing data scanned and optimising execution.

121. 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 the performance of the SQL queries by allowing queries to run on the specific partitions and reducing I/O operations.

122. Explain the difference between horizontal and vertical partitioning.

Horizontal: Splits a table by rows (e.g., by date ranges)

Vertical: Splits a table by columns (e.g., separating frequently vs rarely accessed fields)
Use horizontal for distributed querying, vertical for security/performance optimization.

123. What third-party tools 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

124. How would you track changes to sensitive data for auditing?

-- Create a table named 'audit_log' to track changes in data
CREATE TABLE audit_log (   change_time TIMESTAMP,
-- Timestamp of when the change occurred
user_id INT,
-- Id of the user who made the change
old_value JSONB,
-- Previous state of the data stored as JSONB
new_value JSONB
-- Updated state of the data stored as JSONB );
Use triggers or CDC (Change Data Capture) to populate this table. copy

125. 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); copy

126. What is Denormalization, and when is it used?

Denormalization is the process of intentionally introducing redundancy into a database by combining tables to improve query performance.

While normalization focuses on reducing data duplication, denormalization is used when read performance is more important than storage efficiency.

• Reduces the need for complex joins
• Improves read and reporting performance
• Commonly used in data warehouses and OLAP systems
• May increase data redundancy and update complexity

127. What is a View? How is it different from a Materialized View?

A View is a virtual table created from a SQL query, whereas a Materialized View stores the query result physically on disk.

128. What is a Cursor? Why should you avoid using them?

A Cursor is a database object used to retrieve and process rows one at a time instead of processing them as a set.

Cursors should generally be avoided because they significantly impact performance compared to set-based operations.

• Processes rows sequentially (row-by-row)
• Consumes more memory and CPU
• Slower than set-based SQL operations
• Used only when row-level processing is unavoidable

129. Explain Window Functions (RANK vs DENSE_RANK vs ROW_NUMBER)

Window functions perform calculations across a set of rows related to the current row without collapsing the result set.

130. How does an Index work internally?

An Index improves query performance by allowing the database to locate rows quickly without scanning the entire table.

Most relational databases implement indexes using B-Tree data structures.

• B-Trees keep data sorted and balanced
• Search, insert, and delete operations run in logarithmic time
• Indexes store key values and pointers to actual rows
• Excessive indexing can slow down write operations

Indexes significantly speed up SELECT queries but should be used carefully to balance performance.

131. Explain the CAP Theorem.

CAP Theorem, also known as Brewer’s Theorem, which is a key concept in distributed systems that states all three properties cannot be guaranteed at the same time:

• Consistency
• Availability
• Partition Tolerance

132. 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.

133. Explain the GROUP BY and HAVING clauses

GROUP BY: GROUP BY aggregates data into groups based on specified columns (like department or date).

HAVING: HAVING filters these groups after aggregation, similar to WHERE for individual rows.

Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 80000 shows only departments with an average salary above 60k.

134. What are the different types of SQL commands?

SQL commands are broadly classified into five categories: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), TCL (Transaction Control Language), and DQL (Data Query Language).

• DDL consists of commands such as CREATE, ALTER, and DROP.
• DML consists of INSERT, UPDATE, and DELETE.
• DCL consists of GRANT and REVOKE.
• TCL consists of COMMIT, ROLLBACK, and SAVEPOINT.
• DQL is mainly the SELECT command.

The SQL command types are various ways to handle the database structure, perform data operations, and regulate access.

135. What is a subquery?

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

136. 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.

137. How do window functions work?

Window functions perform calculations across related rows without collapsing them like GROUP BY.

Example: RANK() OVER (PARTITION BY department ORDER BY salary DESC) ranks salaries within each department. They’re powerful for running totals, rankings, and moving averages while preserving original rows.

138. 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 SQL commands like insert, update, and delete are fired. The syntax used to generate the trigger function is as follows:

CREATE TRIGGER trigger_name copy

139. How would you track changes to sensitive data for auditing?

-- Create a table named 'audit_log' to track changes in data
CREATE TABLE audit_log (   change_time TIMESTAMP,
-- Timestamp of when the change occurred   user_id INT,
-- Id of the user who made the change   old_value JSONB,
-- Previous state of the data stored as JSONB
new_value JSONB
-- Updated state of the data stored as JSONB );
Use triggers or CDC (Change Data Capture)
to populate this table. copy

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

How to Prepare for an SQL Interview?

To prepare for your next SQL Interview, follow these steps:

1. Prepare the Basics: Learn basic SQL commands like SELECT, JOIN, GROUP BY etc, data types, constraints and basic SQL interview questions.
2. Practice Queries: Solve real world questions on SQL queries on different platforms like LeetCode and HackerRank.

Please take a moment to watch the video on SQL interview questions:

Conclusion

These SQL interview questions cover essential concepts to help you crack SQL interviews with confidence, whether you are a beginner or an experienced professional.

If you want to know how data analysis helps in real-world applications and advance your skills further, consider exploring an SQL course to gain comprehensive knowledge and hands-on experience.