1

SQL Interview Questions Guide | © Crack Interview 2025

 2
SQL Interview Questions Guide
Comprehensive 500+ SQL Interview Questions & Answers
Basics to Advanced
Flow :- Easy Concept to Hard Practice

SQL Interview Questions Guide | © Crack Interview 2025

 3
1. What is SQL, and why is it used??

Answer:

SQL (Structured Query Language) is a standard programming language designed for managing and querying
relational databases. It is used to:

• Create database structures (tables, indexes).

• Read data (SELECT queries).

• Modify data (INSERT, UPDATE, DELETE).

• Control access and transactions.

Why use SQL?

• It provides a declarative way to say what you want (e.g., “give me all customers in Mumbai”) rather than how
to get it.

• It’s standardized (ANSI SQL) so skills and queries transfer across many systems.

• It’s powerful for data manipulation, reporting, and enforcing data integrity.

2. Difference between SQL and MySQL.

Answer:

• SQL is a language — a set of commands and syntax used to interact with relational databases (SELECT,
INSERT, CREATE TABLE, etc.).

• MySQL is a relational database management system (RDBMS) — a software product that implements
SQL and stores/manages data (others: PostgreSQL, Oracle Database, SQL Server).

Short analogy: SQL : language :: MySQL : one brand of database software that speaks that language.

3. What is a database?

Answer:

A database is an organized collection of data stored so it can be accessed, managed, and updated efficiently. In
RDBMS systems a database usually contains:

• Tables (the main data containers)

• Views, indexes, stored procedures, triggers

• User accounts, permissions

• Metadata (information about the structure)

SQL Interview Questions Guide | © Crack Interview 2025
 4
4. What are the main types of SQL commands?

Answer:

SQL commands are commonly grouped by functionality. The main groups:

1. DDL — Data Definition Language

Commands that define or change database structure (tables, schemas, indexes).
Examples: CREATE, ALTER, DROP, TRUNCATE, RENAME.

2. DML — Data Manipulation Language

Commands to insert, update, delete or retrieve data.
Examples: SELECT, INSERT, UPDATE, DELETE, MERGE.

3. DCL — Data Control Language

Commands to control access/permissions.
Examples: GRANT, REVOKE.

4. TCL — Transaction Control Language

Commands to manage transactions (commit or rollback changes).
Examples: BEGIN / START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT.

5. Differentiate between DDL, DML, DCL, and TCL commands.

Answer:

DDL (Data Definition Language)

• Purpose: change database structure.

• Effects: usually auto-committed (structure changes are immediate).

• Examples & short meaning:

o CREATE TABLE customers (...) — creates a table.

o ALTER TABLE orders ADD COLUMN shipped_date DATE; — changes a table.

o DROP TABLE tmp; — removes a table and its data.

DML (Data Manipulation Language)

• Purpose: operate on the data stored in tables.

• Often runs within transactions (can be rolled back).

• Examples:

o INSERT INTO products (id,name,price) VALUES (1,'Pen',10.0);

o UPDATE products SET price = price * 1.1 WHERE category='office';

o DELETE FROM customers WHERE id = 42;

SQL Interview Questions Guide | © Crack Interview 2025
 5
o SELECT * FROM customers WHERE city='Bengaluru'; (SELECT is read-only DML)

DCL (Data Control Language)

• Purpose: manage permissions and security.

• Examples:

o GRANT SELECT, INSERT ON [Link] TO 'analyst'@'%';

o REVOKE INSERT ON [Link] FROM 'temp_user'@'localhost';

TCL (Transaction Control Language)

• Purpose: group operations into atomic units and control commit/rollback.

• Examples:

o START TRANSACTION; — start a new transaction (some DBs use BEGIN).

o COMMIT; — make all changes in the transaction permanent.

o ROLLBACK; — undo changes since transaction start or savepoint.

o SAVEPOINT sp1; — create a point to roll back to within a transaction.

6. What is the difference between a database and a schema?

Answer:

Definitions vary slightly between database products, but general meanings:

• Database: a top-level container that holds data and objects (tables, views, procedures). In many
systems (MySQL, SQL Server), a database is the main storage unit.

• Schema: a namespace inside a database used to group related objects and to manage permissions. Think
of it as a folder inside the database.

Examples by RDBMS:

• PostgreSQL: one server can host many databases; each database can contain multiple schemas
(common pattern: public schema).

• MySQL: the terms “database” and “schema” are often used interchangeably — one database = one
schema namespace.

• Oracle: a schema is essentially a user account; objects owned by a user form that user's schema. Oracle
databases contain many schemas (users).

In short: schema = logical organization/namespace inside a database (but exact meaning depends on the
DBMS).

7. Explain what a table is in SQL.

SQL Interview Questions Guide | © Crack Interview 2025
 6
Answer:

A table is a structured collection of related data organized in rows and columns. It resembles a spreadsheet:

• Columns define the attributes (fields) and their data types (e.g., INT, VARCHAR(255), DATE).

• Rows (records) hold specific instances of data.

For Example:

CREATE TABLE employees (

emp_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
hire_date DATE,
salary DECIMAL(10,2)
);
This query retrieves only the first and last names of employees working in the HR department. The WHERE
clause acts as a filter, ensuring only relevant data is displayed.

8. What are constraints in SQL?

Answer:

Constraints are rules applied to table columns that enforce data integrity. Common types:

1. PRIMARY KEY

o Uniquely identifies each row in a table.

o Implies UNIQUE + NOT NULL.

o Example: emp_id INT PRIMARY KEY.

2. UNIQUE

o Ensures all values in a column (or group of columns) are distinct.

o Example: UNIQUE (email).

3. NOT NULL

o Column must have a value; cannot be NULL.

o Example: name VARCHAR(100) NOT NULL.

4. FOREIGN KEY

o Enforces referential integrity between tables.

o Points to a primary/unique key in another table.

SQL Interview Questions Guide | © Crack Interview 2025
 7
o Example:

o CREATE TABLE orders (

o order_id INT PRIMARY KEY,

o customer_id INT,

o FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

o );

5. CHECK

o Ensures column values satisfy a boolean condition.

o Example: CHECK (age >= 18).

6. DEFAULT

o Supplies a default value when none is provided.

o Example: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP.

7. INDEX (not strictly a constraint but often discussed alongside)

o Improves query performance; can be unique as well (UNIQUE INDEX).

Constraints can be declared inline with a column or as table-level constraints. They help keep data valid and
consistent (e.g., no negative ages, no orphan foreign keys, unique emails).

9. What are the different types of data types in SQL?

Answer:

SQL supports multiple data types to define what type of value a column can store. Common categories:

Category Examples Purpose

Numeric INT, BIGINT, SMALLINT, DECIMAL(p,s), FLOAT, To store numbers
DOUBLE
Character/String CHAR(n), VARCHAR(n), TEXT To store text/strings
Date/Time DATE, TIME, DATETIME, TIMESTAMP To store date and time values
Binary BLOB, BINARY, VARBINARY To store binary data like
images/files
Boolean BOOLEAN / BIT True/False values
Misc JSON, ENUM, UUID (specific to some DBMS) Special purpose data types

Different DBMS products might have additional types.

SQL Interview Questions Guide | © Crack Interview 2025

 8
10. What is the difference between CHAR and VARCHAR data types?

Answer:

CHAR VARCHAR
Fixed-length string Variable-length string
If you declare CHAR(10) and store “Hey”, SQL still stores 10 VARCHAR(10) stores only actual
characters (pads with spaces) characters
Good for fixed-size fields (e.g. country codes ISO “IND”) Good for variable-length text (names,
addresses, emails)
Slightly faster for fixed length More space-efficient

11. Explain the purpose of a PRIMARY KEY.

Answer:

A PRIMARY KEY uniquely identifies each record (row) in a table.

Rules:

• Must be unique

• Cannot be NULL

• Only one primary key per table (but can be made of multiple columns = composite key)

It enforces entity integrity.

12. What is a FOREIGN KEY, and why is it used?

Answer:

A FOREIGN KEY links two tables together.

It enforces referential integrity.

Purpose:

• Makes sure that a value in one table must exist in another table.

13. What is the difference between PRIMARY KEY and UNIQUE KEY?

Answer:

PRIMARY KEY UNIQUE KEY

Identifies a record uniquely Ensures values are unique but NOT for identification purpose
Only one allowed per table Can have multiple UNIQUE keys in a table
Cannot contain NULL Can contain NULL (depends on DB, but generally allowed)
Combination of UNIQUE + NOT NULL Only enforces uniqueness

SQL Interview Questions Guide | © Crack Interview 2025

 9
14. What is a DEFAULT constraint?

Answer:

DEFAULT provides a default value to a column if no value is supplied during INSERT.

Example:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

If user doesn’t give any value → DB fills current time automatically.

15. What is a NOT NULL constraint?

Answer:

NOT NULL ensures a column cannot store NULL values.

Example:

name VARCHAR(100) NOT NULL

Means every row must have a name.

16. Explain the CHECK constraint with an example.

Answer:

CHECK is used to restrict the values a column can store.

Example:

age INT CHECK (age >= 18)

This means:

• Only age values 18 or above are allowed.

• If someone tries to insert age 10 → SQL rejects it.

17. What is a composite key?

Answer:

A composite key is a primary key made up of multiple columns together to uniquely identify a record.

Example:
SQL Interview Questions Guide | © Crack Interview 2025
 10
PRIMARY KEY (student_id, course_id)

Meaning → each student can take multiple courses, but the combination of student + course must be unique.

18. Can a table have multiple primary keys? Why or why not?

Answer:

No.
A table can have only one primary key constraint.

Reason:

• A primary key defines one unique identity per record.

• You can combine multiple columns into one primary key → but it is still a single composite key, not
multiple separate primary keys.

However, a table CAN have many UNIQUE keys.

19. What is the purpose of the CREATE statement in SQL?

Answer:

CREATE is a DDL command used to create new database objects such as:

• databases

• tables

• views

• indexes

• schemas

• procedures / functions (depending on DBMS)

20. What is the syntax for creating a new database in SQL?

Answer:

CREATE DATABASE database_name;

SQL Interview Questions Guide | © Crack Interview 2025

 11
21. How do you create a new table in SQL?

Answer:

For Example:

CREATE TABLE table_name (

column1 datatype constraints,
column2 datatype constraints,
...
);

------
CREATE TABLE customers (
cust_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
city VARCHAR(50)
);

22. How do you delete a table in SQL?

Answer:

Use the DROP TABLE command.

Example:

DROP TABLE customers;

This removes the table structure + all data permanently.

23. What is the difference between DELETE, TRUNCATE, and DROP?

Answer:

Command Purpose Removes Removes Transaction Rollback?

Data? Structure?
DELETE Removes selected rows YES NO Can rollback
TRUNCATE Removes all rows (faster YES (all rows) NO Usually cannot rollback
than DELETE) (depends DB)
DROP Removes entire table YES YES Cannot rollback

For Example:
SQL Interview Questions Guide | © Crack Interview 2025
 12
DELETE FROM students WHERE id=5;
TRUNCATE TABLE students;
DROP TABLE students;

24. What is the use of the ALTER TABLE command?

Answer:

ALTER TABLE is used to modify an existing table.

Common uses:

• add a column

• drop a column

• rename columns

• modify datatype

• add or remove constraints

25. How do you rename a column in SQL?

Answer:

MySQL syntax:

ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

PostgreSQL syntax:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Example (PostgreSQL style):

ALTER TABLE employees RENAME COLUMN name TO full_name;

26. How do you add a new column to an existing table?

Answer:

ALTER TABLE table_name ADD column_name datatype constraints;

SQL Interview Questions Guide | © Crack Interview 2025

 13
27. What is the use of the DESC command?

Answer:

DESC stands for describe.

It shows the structure/definition of a table.

Example:

DESC employees;

It displays columns, datatypes, nullability, keys, etc.

28. How can you view the structure of a table in SQL?

Answer:

DBMS Command
MySQL DESC table_name; or SHOW COLUMNS FROM table_name;
PostgreSQL \d table_name; in psql or SELECT column_name, data_type FROM information_schema.columns
WHERE table_name='table_name';
SQL Server sp_help table_name;
Oracle DESC table_name;

29. What is the purpose of the SELECT statement?

Answer:

SELECT is a DML command used to retrieve data from a table.

It is the most commonly used SQL command.

Example:

SELECT name, age FROM employees;

This will fetch data from the table and display it as output.

30. What is the difference between SELECT * and selecting specific columns?

Answer:

SELECT * SELECT specific columns

selects all columns from a table selects only required columns
slower when table has many columns faster, less data transfer
not good for production code recommended for professional usage

SQL Interview Questions Guide | © Crack Interview 2025

 14
31. What does the WHERE clause do?

Answer:

WHERE is used to filter rows based on a condition.

Example:

SELECT * FROM employees WHERE department='Sales';

This returns only employees who work in Sales department.

32. What is the difference between WHERE and HAVING clauses?

Answer:

WHERE HAVING
filters rows before grouping filters groups after grouping
cannot use aggregate functions (SUM, COUNT) can use aggregate functions
used with SELECT, UPDATE, DELETE used with GROUP BY

33. What is the use of the DISTINCT keyword?

Answer:

DISTINCT removes duplicate values from result.

Example:

SELECT DISTINCT city FROM customers;

This returns unique list of cities only once.

34. How can you sort query results in SQL?

Answer:

Use the ORDER BY clause.

Example:

SELECT * FROM products ORDER BY price ASC; -- ascending

SELECT * FROM products ORDER BY price DESC; -- descending

SQL Interview Questions Guide | © Crack Interview 2025

 15
35. What is the purpose of the LIMIT or TOP clause?

Answer:

Used to restrict (limit) how many rows are returned.

MySQL / PostgreSQL SQL Server

LIMIT n TOP n
Example (MySQL):

SELECT * FROM employees LIMIT 5;

Example (SQL Server):

SELECT TOP 5 * FROM employees;

36. Explain how to use the BETWEEN operator.

Answer:

BETWEEN checks if a value lies within a range (inclusive).

Example:

SELECT * FROM students WHERE marks BETWEEN 70 AND 90;

This returns rows where marks >= 70 AND marks <= 90.

37. What does the LIKE operator do in SQL?

Answer:

LIKE is used for pattern matching in text strings.

Example:

SELECT * FROM customers WHERE name LIKE 'A%';

This returns names starting with letter ‘A’.

SQL Interview Questions Guide | © Crack Interview 2025

 16
38. What are wildcard characters in SQL?

Answer:

Wildcard Meaning
% matches zero or more characters
_ matches exactly one character
Example usages:

name LIKE '%son' -- ends with "son"

name LIKE 'A%' -- starts with A

name LIKE 'A__ ' -- starts with A and exactly 3 letters total

39. What is NULL in SQL?

Answer:

NULL represents unknown, missing or not applicable data in SQL.

It does NOT mean:

• zero

• empty string

• space

• default value

NULL means value is not present.

40. How is NULL different from 0 or an empty string?

Answer:

Value Meaning Example

NULL no value / unknown age = NULL → we don’t know the age
0 numeric value zero age = 0 → age is zero
'' (empty string) string with length zero name = '' → name exists but blank

SQL Interview Questions Guide | © Crack Interview 2025

 17
41. How do you check for NULL values in SQL?

Answer:

We cannot use = or != with NULL.

Correct way:

WHERE column_name IS NULL

Example:

SELECT * FROM employees WHERE manager_id IS NULL;

42. What is the use of the IS NULL and IS NOT NULL operators?

Answer:

Operator Purpose
IS NULL returns rows where column has NULL value
IS NOT NULL returns rows where column has non-null value

43. Can a primary key column contain NULL values? Why or why not?

Answer:

No.
A primary key column cannot contain NULL.

Reason:

• Primary key uniquely identifies each row

• If NULL is allowed → uniqueness cannot be guaranteed because NULL means unknown value

Therefore:

• PRIMARY KEY = automatically NOT NULL + UNIQUE

Example:

emp_id INT PRIMARY KEY -- cannot be NULL

SQL Interview Questions Guide | © Crack Interview 2025

 18
44. What are the advantages of using a database?

Answer:

Main advantages:

1. Data Security — access control, permissions, backups protect data.

2. Data Integrity — database ensures correctness and consistency.

3. Data Sharing — many users/programs can access same data safely.

4. Data Independence — you can change structure without changing applications.

5. Reduces Data Redundancy — same data is not repeated everywhere.

6. Efficient Data Retrieval — optimized queries, indexing, caching.

7. Concurrent Access — multiple users can read/write at the same time.

8. Backup + Recovery — recovery in case of failures.

45. What is the difference between a relational and non-relational database?

Answer:

Relational DB (RDBMS) Non-Relational DB (NoSQL)

Stores data in tables (rows & columns) Stores data in formats like documents, key-value, graph, columns
Uses SQL Uses various query methods (Mongo Query Language, CQL, etc.)
Ideal for structured data Ideal for semi-structured or unstructured data
Strong ACID compliance Often prioritizes performance & scalability
Joins are common Data often denormalized (embedded data)

46. What are the ACID properties in a database?

Answer:

Property Meaning
A – Atomicity all operations in a transaction must complete or none do
C – Consistency transaction must take DB from one valid state to another valid state
I – Isolation transactions should not affect other running transactions
D – Durability once committed, data must be permanent even after failures

SQL Interview Questions Guide | © Crack Interview 2025

 19
47. Explain the concept of data integrity.

Answer:

Data integrity = correctness, accuracy, and consistency of data in database.

Maintained through:

• constraints (PK, FK, UNIQUE, CHECK)

• rules

• validations

• ACID properties

Goal: data should remain valid, reliable, and trustworthy throughout its lifecycle.

Example:
Employee age cannot be negative → CHECK constraint enforces integrity.

48. What are the most common SQL databases used in the industry today?

Answer:

DBMS Used for

MySQL / MariaDB Web applications, LAMP stack
PostgreSQL Advanced SQL features, enterprise use
Microsoft SQL Server Windows based enterprise environments
Oracle Database Large corporations, banking, finance
SQLite Mobile apps, embedded systems

49. How can you rename a column in the output using SQL?

Answer:

SELECT salary AS monthly_salary FROM employees;

SELECT salary AS monthly_salary, name AS employee_name FROM employees;

SQL Interview Questions Guide | © Crack Interview 2025

 20
50. What is the purpose of using aliases in SQL?

Answer:

Aliases are temporary names given to columns or tables just for output or query readability.

Uses:

• shorter names in complex queries

• understandable output

• prevent naming conflicts

51. How can you use aliases for both table and column names?

Answer:

Column alias:

SELECT name AS employee_name FROM employees;

Table alias:

SELECT [Link], d.department_name

FROM employees AS e

JOIN departments AS d

ON e.dept_id = d.dept_id;

52. How do you use comparison operators like =, >, <, >=, <=, != in SQL?

Answer:

You use them inside the WHERE clause to compare values.

Examples:

WHERE salary > 50000

WHERE age <= 30

WHERE city != 'Mumbai'

WHERE department = 'Sales'

SQL Interview Questions Guide | © Crack Interview 2025

 21
53. How do you write a query to find employees with salary greater than 50,000?

Answer:

SELECT * FROM employees

WHERE salary > 50000;

54. How can you filter data based on multiple conditions using AND and OR?

Answer:

Example with AND:

SELECT * FROM employees

WHERE department='IT' AND salary > 60000;

Example with OR:

SELECT * FROM employees

WHERE city='Delhi' OR city='Mumbai';

55. What is the difference between AND and OR conditions?

Answer:

AND OR
All conditions must be TRUE At least one condition must be TRUE
More restrictive (fewer rows) Less restrictive (more rows)

SQL Interview Questions Guide | © Crack Interview 2025

 22
56. What is the use of the IN operator in SQL?

Answer:

IN checks if a value matches any value in a list.

Example:

SELECT * FROM employees

WHERE department IN ('IT','HR','Finance');

This is same as:

department='IT' OR department='HR' OR department='Finance'

57. What is the difference between IS NULL and = NULL?

Answer:

IS NULL = NULL
correct way to check NULL does NOT work
Standard SQL operator will not return correct result

58. What is the use of the LIKE operator in SQL?

Answer:

LIKE is used to perform pattern matching in text fields.

It helps to search text based on partial / wildcard patterns.

Example: Show all names which starting from A letter ?

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

59. What does the % wildcard represent in SQL pattern matching?

Answer:

% means zero or more characters.

Example:

'A%' → starts with A (A, Amit, Anusha, etc.)

'%ed' → ends with "ed"

'%kumar%' → contains "kumar" anywhere

SQL Interview Questions Guide | © Crack Interview 2025

 23
60. What does the _ wildcard represent in SQL?

Answer:

_ (underscore) means exactly one character.

Example:

'name LIKE 'A__' → names with A + 2 more characters (Abe, Amy)

if want 2nd letter is “a” and length can be any number of character?
Ans :- LIKE ‘_a%’

61. Write a query to find all records that contain the word “Manager” in the job title.

Answer:

SELECT * FROM employees

WHERE job_title LIKE '%Manager%';

62. What is the difference between LIKE and ILIKE?

Answer:

LIKE ILIKE
Case-sensitive (by default) Case-insensitive matching
Standard SQL command used in most DBMS Available mainly in PostgreSQL only

63. How can you limit the number of rows returned by a query?

Answer:

DB Clause
MySQL / PostgreSQL / MariaDB LIMIT
SQL Server TOP
Oracle ROWNUM or FETCH FIRST

SQL Interview Questions Guide | © Crack Interview 2025

 24
64. Row Count if we Join Table A with Table B

Table A
Table B
1
1 1
1 1
1 1
1 1
1

Answer:

LEFT JOIN -24

RIGHT JOIN- 24

INNER-24

OUTER-24

65. How do you fetch the first 5 highest salaries from an Employee table?

Answer:

MySQL / PostgreSQL:

SELECT name, salary

FROM employees

ORDER BY salary DESC

LIMIT 5;

SQL Server:

SELECT TOP 5 name, salary

FROM employees

ORDER BY salary DESC;

SQL Interview Questions Guide | © Crack Interview 2025

 25
66. How do you skip the first N rows and return the next M rows?

Answer:

Use LIMIT offset, count in MySQL:

Example: skip 10 rows return next 5

SELECT * FROM employees

LIMIT 10, 5;

PostgreSQL / SQL Server (new syntax):

SELECT * FROM employees

OFFSET 10 ROWS

FETCH NEXT 5 ROWS ONLY;

67. How do you find duplicate records in a table using SQL?

Answer:

SELECT name, COUNT(*)

FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

68. What is the difference between DISTINCT and GROUP BY when removing duplicates?

Answer:

Feature DISTINCT GROUP BY

Purpose removes duplicate rows groups rows to apply aggregations
Usage simple more powerful (sum, avg, count needed)

69. What are aggregate functions in SQL?

Answer:

Aggregate functions are built-in SQL functions that perform a calculation on a set of rows and return a single
summarized value.
They are mostly used with the GROUP BY clause to generate summary reports from large datasets.

SQL Interview Questions Guide | © Crack Interview 2025

 26
Function Description
COUNT() Counts rows
SUM() Adds numeric values
AVG() Calculates average
MIN() Returns minimum value
MAX() Returns maximum value

Key point:
Aggregate functions ignore NULL values (except COUNT(*), which counts rows including NULLs).

70. How does COUNT(*) differ from COUNT(column_name)?

Answer:

COUNT(*)

• Counts all rows, regardless of NULL or not.

• Includes duplicate values.

• Fastest because it does not check column values.

Example:
If table has 10 rows, COUNT(*) = 10.

COUNT(column_name)

• Counts only non-NULL values in that specific column.

• Ignores NULL entries.

Example:
If a column has 10 rows but 2 are NULL → COUNT(column) = 8.

Summary:

• COUNT(*) → counts rows

• COUNT(col) → counts non-null values of that column

71. How do you calculate the average salary of employees?

Answer:

Use the AVG() function:

SELECT AVG(salary) AS average_salary

FROM employees;

SQL Interview Questions Guide | © Crack Interview 2025

 27
How it works:

• Adds all salary values.

• Divides by number of non-NULL salary values.

• Ignores NULL salary rows automatically.

72. What is the difference between SUM() and COUNT()?

Answer:

SUM()

• Adds numeric values.

• Works only on numeric columns.

• Ignores NULLs.

Example:
SUM(salary) → total salary payout.

COUNT()

• Counts rows or non-null values.

• Can be used on any datatype.

• COUNT(*) counts all rows.

In simple terms:

• SUM() calculates totals.

• COUNT() calculates quantity.

73. How do MIN() and MAX() functions handle NULL values?

Answer:

Both MIN() and MAX():

• Ignore NULL values entirely.

• Evaluate only rows that contain actual values.

• Return the minimum or maximum from the set of non-NULL values.

Example:

SQL Interview Questions Guide | © Crack Interview 2025

 28
Salary
50000
NULL
70000
45000
MIN(salary) = 45000
MAX(salary) = 70000

NULL is not considered for comparison.

74. Write a query to count distinct departments in a table.

Answer:

SELECT COUNT(DISTINCT department) AS total_departments

FROM employees;

75. What happens when you use an aggregate function without GROUP BY?

Answer:

When no GROUP BY is used:

• The entire table is treated as one single group.

• SQL returns one row with the aggregated value.

Example:

SELECT SUM(salary) FROM employees;

This calculates the total salary for all employees.

Important:
Using aggregate functions without GROUP BY still works, but only returns one record.

76. Can aggregate functions be used in the WHERE clause? Why or why not?

Answer:

No, aggregate functions cannot be used in the WHERE clause. (Inside)

Reason:

• WHERE filters rows before aggregation.

• Aggregate functions like SUM, AVG, MAX are computed after rows are filtered.

SQL Interview Questions Guide | © Crack Interview 2025

 29
• SQL cannot compare a value that doesn’t exist yet.

This causes an error:

-- Invalid

SELECT department, SUM(salary)

FROM employees

WHERE SUM(salary) > 50000;

77. How do you filter aggregated results using the HAVING clause?

Answer: (Q76 to Q77)- Connected

You use HAVING instead of WHERE.

HAVING filters the result after the aggregation.

Example: show departments with total salary > 1,00,000:

SELECT department, SUM(salary) AS total_salary

FROM employees

GROUP BY department

HAVING SUM(salary) > 100000;

• GROUP BY creates groups.

• HAVING filters based on aggregate calculations.

78. What is the difference between HAVING and WHERE with aggregates?

Answer:

Feature WHERE HAVING

Filters rows before grouping ✔️ ❌
Filters rows after grouping ❌ ✔️
Can use aggregate functions ❌ ✔️
Used with GROUP BY Optional but usually before GROUP BY Always after GROUP BY

SQL Interview Questions Guide | © Crack Interview 2025

 30
79. What are scalar functions in SQL?

Answer:

Scalar functions are SQL functions that operate on a single value and return one value as output.
Unlike aggregate functions (which work on groups of rows), scalar functions act on individual rows.

Scalar functions categories include:

• String functions: UPPER(), LOWER(), SUBSTR()

• Numeric functions: ROUND(), POWER(), ABS()

• Date functions: NOW(), DATEDIFF(), DATEADD()

• Conversion functions: CAST(), CONVERT()

Key point:
Scalar functions return a value for every row in the result set.

80. Explain the purpose of the ROUND() function.

Answer:

The ROUND() function rounds a numeric value to a specified number of decimal places.

Syntax:

ROUND(number, decimal_places)

Examples:

SELECT ROUND(123.456, 2); -- Output: 123.46

SELECT ROUND(123.456, 0); -- Output: 123

SELECT ROUND(123.456, -1); -- Output: 120

• Positive decimals = round to right of decimal

• Zero = round to nearest whole number

• Negative decimals = round to tens, hundreds, etc.

81. What is the difference between CEIL() and FLOOR()?

Answer:

CEIL() / CEILING()

• Rounds a number upwards to the next highest integer.

SQL Interview Questions Guide | © Crack Interview 2025

 31
• Example:
CEIL(5.1) → 6
CEIL(-5.1) → -5

FLOOR()

• Rounds a number downwards to the next lowest integer.

• Example:
FLOOR(5.9) → 5
FLOOR(-5.9) → -6

Summary:

• CEIL: round UP

• FLOOR: round DOWN

• Both work even with negative numbers.

82. How does the ABS() function work?

Answer:

The ABS() function returns the absolute value of a number.

Example:

SELECT ABS(-15); -- Output: 15

SELECT ABS(20); -- Output: 20

It removes the sign but keeps the magnitude.

Use case:
To measure difference, distance, or deviation without negative signs.

83. How do you generate random numbers in SQL?

Answer:

Different SQL systems have different functions:

Database Random Function

MySQL RAND()

PostgreSQL RANDOM()

SQL Interview Questions Guide | © Crack Interview 2025

 32
Database Random Function

SQL Server RAND()

Oracle DBMS_RANDOM.VALUE

84. What is the use of the POWER() function?

Answer:

The POWER() function raises a number to the power of another number.

Syntax:

POWER(base, exponent)

Example:

SELECT POWER(2, 3); -- Output: 8

SELECT POWER(10, 2); -- Output: 100

Useful for:

• Mathematical calculations

• Growth/decay formulas

• Financial computations (compound interest)

85. How does the MOD() function work?

Answer:

The MOD() function returns the remainder after dividing two numbers.

Syntax:

MOD(a, b)

Example:

SELECT MOD(10, 3); -- Output: 1

SELECT MOD(25, 4); -- Output: 1

Common uses:
SQL Interview Questions Guide | © Crack Interview 2025
 33
• Finding even/odd numbers

• Scheduling/rotation cycles

• Partitioning data

86. Write a query to calculate the percentage of total sales for each category.

Answer:

Assume a table sales(category, amount).

SELECT

category,

SUM(amount) AS category_sales,

(SUM(amount) * 100.0 / (SELECT SUM(amount) FROM sales)) AS percentage_of_total

FROM sales

GROUP BY category;

Explanation:

• SUM(amount) → total sales per category

• Subquery → total sales of all categories

• Formula calculates percentage

87. How does SQL handle numeric overflow in expressions?

Numeric overflow occurs when a calculation exceeds the maximum value allowed by the datatype.

SQL handles overflow in these ways (depends on database):

1. Error / Exception

Some systems (SQL Server, Oracle) throw an error:

Arithmetic overflow error

2. Automatic type promotion

Some engines (PostgreSQL) auto-convert if possible.

3. Truncation / Loss of precision

SQL Interview Questions Guide | © Crack Interview 2025

 34
For FLOAT or DOUBLE types, overflow may result in:

• Infinity

• NaN (Not a Number)

Example of overflow:

SELECT 9999999999 * 9999999999;

If stored in INT → overflow

If stored in BIGINT or DECIMAL → works

Solution:- More Useful in real industry projects

Use appropriate types like DECIMAL(20,2) or BIGINT.

88. What is the difference between arithmetic expressions and numeric functions?

Answer:

Arithmetic expressions

• Use basic math operators: +, -, *, /, %

• Work directly on numeric values

• Example:

SELECT price * quantity AS total;

Numeric functions

• Predefined SQL functions that manipulate numeric values

• Examples: ROUND(), POWER(), ABS(), MOD()

Key Differences:

Aspect Arithmetic Expressions Numeric Functions

Type of operation Basic math Advanced/complex math
Flexibility Limited Very flexible
Handles rounding? No Yes (ROUND())
Handles powers, logs? No Yes (POWER())
Error handling Depends Depends on function
Simple rule:

• Use expressions for direct calculations

• Use numeric functions for mathematical transformations

89. What is the use of the UPPER() and LOWER() functions?

SQL Interview Questions Guide | © Crack Interview 2025
 35
Answer:

UPPER()

Converts all characters in a string to uppercase.

SELECT UPPER('hello world'); -- Output: HELLO WORLD

LOWER()

Converts all characters in a string to lowercase.

SELECT LOWER('HELLO'); -- Output: hello

Use cases:

• Standardizing text for searches

• Case-insensitive comparisons

• Formatting output

Example comparison:

WHERE LOWER(email) = 'alex@[Link]'

90. How do you remove leading and trailing spaces from a string?

Answer:

Use the TRIM() family of functions:

Standard TRIM:

SELECT TRIM(' SQL Guide ');

-- Output: 'SQL Guide'

Remove only leading spaces:

SELECT LTRIM(' Text');

Remove only trailing spaces:

SELECT RTRIM('Text ');

Use case:
Cleaning messy text from user inputs or imported files

91. What is the difference between SUBSTRING() and LEFT()/RIGHT()?

SQL Interview Questions Guide | © Crack Interview 2025

 36
Answer:

SUBSTRING()

Extracts part of a string from any position.

SUBSTRING(string, start_position, length)

Example:

SELECT SUBSTRING('Database', 2, 3);

-- Output: 'ata'

LEFT()

Extracts characters from the beginning.

SELECT LEFT('Database', 4);

-- Output: 'Data'

RIGHT()

Extracts characters from the end.

SELECT RIGHT('Database', 4);

-- Output: 'base'

Summary:

• SUBSTRING() → flexible, can extract from anywhere

• LEFT(), RIGHT() → quick extraction from edges

92. How does the CONCAT() function work?

Answer:

CONCAT() joins multiple strings into one.

Example:

SELECT CONCAT('Hello', ' ', 'World');

-- Output: Hello World

Key features:

• Accepts 2 or more arguments

• Automatically treats NULL as an empty string (in MySQL)

SQL Interview Questions Guide | © Crack Interview 2025

 37
SELECT CONCAT('SQL', NULL, 'Guide');

-- Output: SQLGuide

93. What is the difference between CONCAT() and using || for concatenation?

Answer:

There are two methods of joining strings depending on the SQL engine.

CONCAT()

• Works in MySQL, SQL Server (as CONCAT), PostgreSQL

• Treats NULL as empty string (MySQL)

|| (Concatenation operator)

• Used in PostgreSQL and Oracle as standard string concatenation

• Does NOT ignore NULL

• SELECT 'SQL' || NULL; -- Output: NULL

94. Write a query to extract the first 3 characters from an employee name.

Answer:

SELECT LEFT(employee_name, 3) AS first_three

FROM employees;

OR using substring:

SELECT SUBSTRING(employee_name, 1, 3)

FROM employees

SQL Interview Questions Guide | © Crack Interview 2025

 38
95. How do you find the length of a string in SQL?

Answer:

Different SQL engines use different functions:

Database Function
MySQL LENGTH(), CHAR_LENGTH()
SQL Server LEN()
PostgreSQL LENGTH()
Oracle LENGTH()
Example:

SELECT LENGTH('SQL Guide'); -- Output: 9

CHAR_LENGTH() counts characters;

LENGTH() may count bytes depending on charset.

96. What is the use of the REPLACE() function?

Answer:

Replaces part of a string with another substring.

Syntax:

REPLACE(original_string, find_string, replace_with)

Example:

SELECT REPLACE('Learn SQL Fast', 'Fast', 'Easily');

-- Output: Learn SQL Easily

Use cases:

• Clean data

• Standardize text

• Remove unwanted characters

97. How do you reverse a string in SQL?

Answer:

Some SQL engines support REVERSE():

MySQL / SQL Server:

SELECT REVERSE('SQL');
SQL Interview Questions Guide | © Crack Interview 2025
 39
-- Output: LQS

PostgreSQL workaround:

SELECT STRING_AGG(c, '')

FROM regexp_split_to_table('SQL', '') AS t(c)

ORDER BY generate_series DESC;

Oracle:

SELECT REVERSE('SQL') FROM dual;

(if not available, use custom function)

98. How do you check if a string contains a specific substring?

Answer:

Using LIKE, INSTR(), or POSITION().

Using LIKE (most common)

WHERE column_name LIKE '%substring%'

Example:

SELECT *

FROM employees

WHERE name LIKE '%son%';

Using INSTR() (MySQL, Oracle)

SELECT INSTR('Database', 'base');

-- Output: 5 (position found)

Check condition:

WHERE INSTR(description, 'SQL') > 0

Using POSITION() (PostgreSQL)

SELECT POSITION('SQL' IN 'Learn SQL');

-- Output: 7

SQL Interview Questions Guide | © Crack Interview 2025

 40
99. How do you get the current date in SQL?

Answer:

Database Function Returns

MySQL CURDATE() Current date
SQL Server GETDATE() Date + time
PostgreSQL CURRENT_DATE Current date
Oracle SYSDATE Current date + time
Example (MySQL):

SELECT CURDATE();

-- Output: 2025-12-01

Example (PostgreSQL):

SELECT CURRENT_DATE;

100. What is the difference between NOW(), CURRENT_DATE, and

CURRENT_TIMESTAMP?

Answer:

NOW()

• Returns current date + current time

• Example: 2025-12-01 03:12:45

CURRENT_DATE

• Returns only date

• Example: 2025-12-01

CURRENT_TIMESTAMP

• Returns date + time + timezone info (in many SQL engines)

• More precise than NOW()

Summary:

Function Returns Includes Time? Includes Timezone?

NOW() Date + time ✔️ ❌ (depends on database)
CURRENT_DATE Date only ❌ ❌
CURRENT_TIMESTAMP Date + time + timezone ✔️ ✔️

SQL Interview Questions Guide | © Crack Interview 2025

 41
101. How do you extract the year, month, or day from a date?

Answer:

MySQL:

SELECT YEAR(order_date);

SELECT MONTH(order_date);

SELECT DAY(order_date);

PostgreSQL:

SELECT EXTRACT(YEAR FROM order_date);

SELECT EXTRACT(MONTH FROM order_date);

SELECT EXTRACT(DAY FROM order_date);

SQL Server:

SELECT YEAR(order_date);

SELECT MONTH(order_date);

SELECT DAY(order_date);

Example Output:

2025-12-01 → Year = 2025, Month = 12, Day = 01

102. How do you add days, months, or years to a date?

Answer:

MySQL:

SELECT DATE_ADD('2025-12-01', INTERVAL 10 DAY);

SELECT DATE_ADD('2025-12-01', INTERVAL 2 MONTH);

SELECT DATE_ADD('2025-12-01', INTERVAL 1 YEAR);

PostgreSQL:

SELECT '2025-12-01'::date + INTERVAL '10 days';

SELECT '2025-12-01'::date + INTERVAL '2 months';

SELECT '2025-12-01'::date + INTERVAL '1 year';

SQL Server:

SQL Interview Questions Guide | © Crack Interview 2025

 42
SELECT DATEADD(DAY, 10, '2025-12-01');

SELECT DATEADD(MONTH, 2, '2025-12-01');

SELECT DATEADD(YEAR, 1, '2025-12-01');

103. What is the use of the DATEDIFF() function?

Answer:

Calculates the difference between two dates in a specific unit (days, months, years).

Example (MySQL):

SELECT DATEDIFF('2025-12-10', '2025-12-01');

-- Output: 9 days

SQL Server:

SELECT DATEDIFF(DAY, '2025-12-01', '2025-12-10');

PostgreSQL Equivalent:

SELECT '2025-12-10'::date - '2025-12-01'::date;

Outputs number of days.

104. How do you calculate age from a date of birth?

Answer:

MySQL:

SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age;

PostgreSQL:

SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, date_of_birth));

SQL Server:

SELECT DATEDIFF(YEAR, date_of_birth, GETDATE())

- CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, date_of_birth, GETDATE()), date_of_birth) > GETDATE()

THEN 1 ELSE 0 END AS age;

This ensures precise age calculation (avoids counting partial birthdays).

SQL Interview Questions Guide | © Crack Interview 2025

 43
105. How does SQL handle invalid dates?

Answer:

SQL behavior varies by database:

MySQL

• Strict mode ON → raises an error

• Strict mode OFF → returns 0000-00-00 or adjusts value

SQL Server

Always raises an error:

Conversion failed when converting date

PostgreSQL

Raises error:

DATE/TIME field value out of range

Summary:
Most databases reject invalid dates with an error unless MySQL is in non-strict mode.

106. Write a query to find records created in the last 7 days.

Answer:

Assume column name: created_at

MySQL:

SELECT *

FROM orders

WHERE created_at >= NOW() - INTERVAL 7 DAY;

PostgreSQL:

WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

SQL Server:

WHERE created_at >= DATEADD(DAY, -7, GETDATE());

SQL Interview Questions Guide | © Crack Interview 2025

 44
107. How do you round a date to the nearest month or year?

Answer:

Round to start of month

MySQL:

SELECT DATE_FORMAT(order_date, '%Y-%m-01');

PostgreSQL:

SELECT date_trunc('month', order_date);

Round to start of year

PostgreSQL:

SELECT date_trunc('year', order_date);

SQL Server:

SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1);

Note:
Most SQL engines round down to the month/year start, not to nearest.
PostgreSQL supports true "truncation" via date_trunc().

108. What is the difference between date datatype and timestamp datatype?

Answer:

DATE

• Stores only year, month, day

• No time component

• No timezone

Example:

2025-12-01

TIMESTAMP / DATETIME

• Stores date + time

• Some databases also store timezone info (e.g., PostgreSQL)

• Higher precision (seconds, milliseconds, microseconds)

SQL Interview Questions Guide | © Crack Interview 2025

 45
Example:

2025-12-01 03:22:11

109. What are expression-based computations in SQL?

Answer:

Expression-based computations are operations performed directly inside SQL queries using:

• Arithmetic expressions (+, -, *, /)

• Logical expressions (AND, OR, NOT)

• String expressions (||, CONCAT)

• Function calls (ROUND(), UPPER(), DATE_ADD())

• Conditional expressions (CASE)

Example:

SELECT price * quantity AS total_amount

FROM orders;

Here, price * quantity is an expression-based computation.

Purpose:

• Perform calculations without storing values physically

• Transform data dynamically

• Build computed columns for reporting

110. How do you create a calculated column in a SELECT query?

Answer:

Use any expression inside the SELECT statement and assign an alias with AS.

Example:

SELECT

product_name,

price,

quantity,

SQL Interview Questions Guide | © Crack Interview 2025

 46
price * quantity AS total_value

FROM sales;

This creates a calculated column named total_value.

You can create computed columns using:

• Arithmetic

• String manipulation

• Date functions

• Conditional logic (CASE)

111. What is the use of the CASE expression?

Answer:

The CASE expression allows you to implement IF/ELSE logic inside SQL.

Syntax (Simple):

CASE value

WHEN condition THEN result

ELSE default

END

Use cases:

• Categorizing data

• Conditional formatting

• Handling nulls

• Creating buckets or segments (age groups, salary groups)

112. Write a query to categorize employees as “High Salary,” “Medium Salary,” or “Low
Salary.”

Answer:

Assume salary column exists.

SQL Interview Questions Guide | © Crack Interview 2025

 47
SELECT

employee_name,

salary,

CASE

WHEN salary >= 80000 THEN 'High Salary'

WHEN salary >= 40000 THEN 'Medium Salary'

ELSE 'Low Salary'

END AS salary_category

FROM employees;

113. What is the purpose of the COALESCE() function?

Answer:

COALESCE() returns the first non-NULL value from a list of arguments.

Example:

SELECT COALESCE(middle_name, first_name, 'N/A');

If middle_name is NULL → returns first_name

If both are NULL → returns 'N/A'

Use cases:

• Replace NULLs in reports

• Provide default values

• Combine multiple possible sources of data

114. How is NULLIF() different from COALESCE()?

Answer:

COALESCE(a, b, c)

• Returns the first non-NULL value

NULLIF(a, b)

• Returns NULL if a = b
SQL Interview Questions Guide | © Crack Interview 2025
 48
• Otherwise returns a

Example:

SELECT NULLIF(5, 5); -- Returns NULL

SELECT NULLIF(5, 3); -- Returns 5

Key Difference:

Function Purpose
COALESCE() Replace NULL with something else
NULLIF() Convert equal values into NULL

115. What are deterministic vs. non-deterministic functions?

Answer:

Deterministic function

Returns the same output every time for the same input.

Examples:

• ABS(10)

• ROUND(5.67, 1)

• UPPER('sql')

Non-deterministic function

May return different results even for the same input.

Examples:

• NOW() → time keeps changing

• RAND() → random value

• CURRENT_TIMESTAMP

• NEWID() (SQL Server)

Importance:

• Deterministic functions can be indexed

• Used in materialized views

• Improve query optimization

SQL Interview Questions Guide | © Crack Interview 2025
 49

116. Can functions be used inside the ORDER BY clause? Explain with example.

Answer:

Yes, SQL allows functions inside ORDER BY.

Example 1: Ordering names by length

SELECT name

FROM employees

ORDER BY LENGTH(name);

Example 2: Ordering dates by month number

SELECT order_date

FROM orders

ORDER BY MONTH(order_date);

Example 3: Ordering by a computed salary category

ORDER BY

CASE

WHEN salary >= 80000 THEN 1

WHEN salary >= 40000 THEN 2

ELSE 3

END;

Using functions in ORDER BY helps achieve advanced custom sorting.

117. How do database engines optimize queries with heavy function usage?

Answer:

Database engines use several strategies:

1. Expression simplification

SQL engine precomputes static expressions.

Example:

SELECT price * (100 + 18) / 100

SQL Interview Questions Guide | © Crack Interview 2025

 50
Constant expression (100 + 18) is computed once.

2. Use of function indexes

For deterministic expressions:

• MySQL: Functional indexes

• PostgreSQL: Index on expression

• Oracle: Function-based index

Example:

CREATE INDEX idx_upper_name ON employees(UPPER(name));

Improves queries such as:

WHERE UPPER(name) = 'ROHAN'

3. Predicate pushdown

Database applies filters early to reduce data scanned.

4. Caching deterministic results

Repeated function calls may be cached internally.

5. Avoiding functions on indexed columns

Because it prevents index usage.

Example (bad):

WHERE UPPER(name) = 'ROHAN' -- index on name is ignored

Better:

WHERE name = 'ROHAN'

(using functional index if needed)

SQL Interview Questions Guide | © Crack Interview 2025

 51
118. What are the limitations of using functions in SQL queries?

Answer:

1. Functions on indexed columns break index usage

Results in full table scans → slower queries.

2. Non-deterministic functions cannot be indexed

Functions like NOW() or RAND() reduce optimization.

3. Heavy computations slow down queries

Complex functions increase CPU usage (e.g., REGEXP, date parsing).

4. Multi-row functions aren’t allowed

Expressions operate on a single row, not on a group (unless aggregate).

5. Not portable across databases

Different SQL dialects have different functions:

• DATEADD() → SQL Server

• INTERVAL → MySQL

• AGE() → PostgreSQL

6. Cannot always be used in WHERE

Aggregate functions require HAVING, not WHERE.

7. May return NULL unexpectedly

Functions like NULLIF() or SUBSTRING() can return NULL or errors if misuse occurs.

119. What is a subquery in SQL?

Answer:

A subquery (also called an inner query or nested query) is a SQL query written inside another SQL query.
The result of the subquery is used by the outer (main) query to perform further filtering, comparison, or
calculations.

Example:

SELECT name

FROM employees

WHERE salary > (

SQL Interview Questions Guide | © Crack Interview 2025

 52
SELECT AVG(salary)

FROM employees

);

Here:

• Inner query calculates average salary

• Outer query uses that value to filter employees

120. What is the difference between a subquery and a join?

Answer:

Aspect Subquery Join

Purpose Filter or compute using a nested query Combine rows from tables

Execution Often executed first Executed as part of query plan

Readability Simple for logical conditions Better for relational data

Performance Can be slower for large data Usually faster and optimized

Output Single or multiple values Multiple combined columns

121. What are the types of subqueries in SQL?

Answer:

Based on result:

• Single-row subquery

• Multi-row subquery

• Multi-column subquery

Based on usage:

• Scalar subquery

• Correlated subquery

• Non-correlated subquery

Based on location:

• In SELECT

SQL Interview Questions Guide | © Crack Interview 2025

 53
• In FROM

• In WHERE

• In HAVING

122. What is a single-row subquery?

Answer:
A single-row subquery returns exactly one row and one column.

Operators used:

=, <, >, <=, >=, <>

Example:

SELECT name

FROM employees

WHERE salary = (

SELECT MAX(salary)

FROM employees

);

If the subquery returns more than one row, SQL throws an error.

123. What is a multi-row subquery?

Answer:

A multi-row subquery returns multiple rows but usually one column.

Operators used:

• IN

• ANY

• ALL

• EXISTS

Example:

SELECT name
SQL Interview Questions Guide | © Crack Interview 2025
 54
FROM employees

WHERE department_id IN (

SELECT department_id

FROM departments

WHERE location = 'Delhi'

);

124. Where can a subquery be used in a SQL statement?

Answer:

Subqueries can appear in multiple clauses:

WHERE

WHERE salary > (SELECT AVG(salary) FROM employees)

SELECT

SELECT name, (SELECT COUNT(*) FROM employees) AS total_employees

FROM employees;

FROM (derived table)

FROM (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id) t

HAVING

HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)

125. Can a subquery return multiple columns?

Answer:

Yes, but only in specific cases.

Allowed scenarios:

• Subquery in FROM clause

• Multi-column comparison

Example (multi-column):

SELECT *

SQL Interview Questions Guide | © Crack Interview 2025

 55
FROM employees e

WHERE (department_id, salary) IN (

SELECT department_id, MAX(salary)

FROM employees

GROUP BY department_id

);

Not allowed:

Single-value comparison using = with multiple columns.

126. What happens if a subquery returns no rows?

Answer:

Behavior depends on the operator used:

Using IN

WHERE column IN (subquery returning no rows)

→ Condition evaluates to FALSE → no records returned

Using EXISTS

WHERE EXISTS (subquery returning no rows)

→ Condition evaluates to FALSE

Using scalar subquery

→ Returns NULL

Example:

WHERE salary > (SELECT salary FROM employees WHERE id = 999)

If no row → salary > NULL → condition fails

127. What happens if a subquery returns more rows than expected?

Answer:

Error occurs when:

• A single-row operator (=, >, <) is used

SQL Interview Questions Guide | © Crack Interview 2025

 56
• Subquery returns multiple rows

Example (Error):

WHERE salary = (SELECT salary FROM employees);

Correct approach:

WHERE salary IN (SELECT salary FROM employees);

Or:

WHERE salary > ANY (SELECT salary FROM employees);

128. Can subqueries be nested inside other subqueries?

Answer:

Yes. SQL allows multiple levels of nesting.

Example:

SELECT name

FROM employees

WHERE department_id IN (

SELECT department_id

FROM departments

WHERE location_id IN (

SELECT location_id

FROM locations

WHERE country = 'India'

);

129. What is a non-correlated subquery?

Answer:

A non-correlated subquery is a subquery that does not depend on the outer query for its execution.

SQL Interview Questions Guide | © Crack Interview 2025

 57
• It can run independently

• Executed once

• Result is passed to the outer query

Example:

SELECT name

FROM employees

WHERE salary > (

SELECT AVG(salary)

FROM employees

);

Here:

• Inner query calculates average salary once

• Outer query uses that value

Key characteristics:

• No reference to outer query columns

• Faster and easier to optimize

130. What is a correlated subquery?

Answer:

A correlated subquery is a subquery that depends on the outer query.

• References columns from the outer query

• Executed once for each row processed by the outer query

• Cannot run independently

Example:

SELECT [Link], [Link]

FROM employees e

WHERE salary > (

SELECT AVG(salary)

SQL Interview Questions Guide | © Crack Interview 2025

 58
FROM employees

WHERE department_id = e.department_id

);

Here:

• Subquery uses e.department_id from outer query

• Executes separately for each employee row

131. How does a correlated subquery execute internally?

Answer:

Execution flow:

1. Outer query selects one row

2. Subquery runs using values from that row

3. Result is returned to outer query

4. Condition is evaluated

5. Process repeats for every row

Visual flow:

Outer Row 1 → Subquery → Result

Outer Row 2 → Subquery → Result

Outer Row 3 → Subquery → Result

...

Performance implication:

If outer query returns N rows, subquery executes N times.

132. Why are correlated subqueries slower than non-correlated ones?

Answer:

Main reasons:

1. Repeated execution

• Non-correlated → executed once

SQL Interview Questions Guide | © Crack Interview 2025

 59
• Correlated → executed per row

2. Higher CPU & I/O cost

• More scans

• More comparisons

3. Limited optimization

• Harder for optimizer to cache results

4. Large datasets amplify cost

• Performance degrades sharply with scale

Summary:

Correlated subqueries trade clarity and correctness for performance cost.

133. Can correlated subqueries be rewritten using joins?

Answer:

Yes, in most cases.

Joins are often faster and more optimized.

Correlated subquery:

SELECT [Link]

FROM employees e

WHERE salary > (

SELECT AVG(salary)

FROM employees

WHERE department_id = e.department_id

);

Equivalent JOIN version:

SELECT [Link]

FROM employees e

JOIN (

SELECT department_id, AVG(salary) avg_sal

SQL Interview Questions Guide | © Crack Interview 2025

 60
FROM employees

GROUP BY department_id

)d

ON e.department_id = d.department_id

WHERE [Link] > d.avg_sal;

Benefits of join:

• Single scan

• Better index usage

• Easier to optimize

134. Write a scenario where correlated subquery is required.

Answer:

Some problems are naturally expressed using correlated subqueries.

Scenario:

Find employees who earn the highest salary in their department

SELECT [Link], [Link]

FROM employees e

WHERE salary = (

SELECT MAX(salary)

FROM employees

WHERE department_id = e.department_id

);

Why correlated?

• Comparison must be done per department

• Depends on outer row’s department

Although join alternatives exist, correlated subquery is clear and intuitive here.

135. What is the scope of columns in a correlated subquery?

SQL Interview Questions Guide | © Crack Interview 2025

 61
Answer:

Column visibility:

Query Level Accessible Columns

Outer query Own columns only

Subquery Own columns + outer query columns

Outer query ❌ Cannot access subquery columns

Example:

SELECT [Link]

FROM employees e

WHERE EXISTS (

SELECT 1

FROM projects p

WHERE p.emp_id = [Link]

);

• Subquery accesses [Link]

• Outer query cannot access p.emp_id

136. Can a correlated subquery run independently? Why or why not?

Answer:

No, it cannot.

Reason:

• It references columns from the outer query

• Without outer query values, subquery is incomplete

Example:

SELECT *

FROM employees

WHERE department_id = e.department_id;

SQL Interview Questions Guide | © Crack Interview 2025

 62
e.department_id is undefined outside the outer query.

Conclusion:

Correlated subqueries depend on outer query context and cannot be executed alone.

137. What is the difference between correlated subquery and self join?

Answer:

Aspect Correlated Subquery Self Join

Execution Row-by-row Set-based

Performance Slower Faster

Readability Often clearer More complex

Index usage Limited Better

Optimization Harder Easier

Example (Self Join):

SELECT [Link]

FROM employees e1

JOIN employees e2

ON e1.department_id = e2.department_id

GROUP BY [Link]

HAVING [Link] = MAX([Link]);

Rule of thumb:

• Use correlated subqueries for clarity

• Use self joins for performance

138. How does indexing impact correlated subquery performance?

Answer:

Indexes significantly affect performance.

Helpful indexes:

• Index on join/filter columns

SQL Interview Questions Guide | © Crack Interview 2025

 63
• Index on columns referenced by outer query

Example:

WHERE department_id = e.department_id

Add index:

CREATE INDEX idx_emp_dept ON employees(department_id);

Benefits:

• Faster row lookups

• Reduced full table scans

• Lower execution time per subquery run

Limitation:

Even with indexes:

• Correlated subquery still executes multiple times

• Joins remain faster for large datasets

139. What is the difference between IN and EXISTS?

Answer:

Aspect IN EXISTS

Comparison Compares a value to a list of values Checks for row existence

Subquery result Returns values Returns TRUE/FALSE

Execution Subquery often evaluated fully Stops when first match found

NULL behavior Affected by NULLs Not affected by NULLs

Performance Slower for large datasets Faster for large datasets

Example:

-- IN

SELECT name

FROM employees

WHERE department_id IN (

SELECT department_id FROM departments

SQL Interview Questions Guide | © Crack Interview 2025

 64
);

-- EXISTS

SELECT name

FROM employees e

WHERE EXISTS (

SELECT 1

FROM departments d

WHERE d.department_id = e.department_id

);

140. When should you use EXISTS instead of IN?

Answer:

Use EXISTS when:

✔ Subquery returns large result sets

✔ Subquery is correlated
✔ Checking only existence, not values
✔ NULLs may exist in subquery
✔ Performance matters on big tables

Rule of thumb:

IN → small static lists

EXISTS → large or correlated datasets

141. What happens when NULL values exist in an IN subquery?

Answer:

If an IN subquery contains NULL, SQL may return no rows, even when matches exist.

Example:

SELECT *

FROM employees

WHERE department_id IN (1, 2, NULL);

• Comparison with NULL → UNKNOWN

SQL Interview Questions Guide | © Crack Interview 2025
 65
• UNKNOWN → treated as FALSE

• Result → No rows returned

This is dangerous and common in interviews.

Solution:

• Use EXISTS

• Or filter NULL explicitly

WHERE department_id IN (

SELECT department_id

FROM departments

WHERE department_id IS NOT NULL

);

142. Explain the use of ANY and ALL operators.

Answer:

ANY

Condition must be true for at least one value.

salary > ANY (subquery)

Means:

Salary is greater than minimum value returned.

ALL

Condition must be true for all values.

salary > ALL (subquery)

Means:

Salary is greater than maximum value returned.

Comparison:

Operator Meaning

SQL Interview Questions Guide | © Crack Interview 2025

 66
Operator Meaning

ANY At least one match

ALL All matches

143. Write a query using ALL operator.

Answer:

Find employees earning more than the highest salary in department 10

SELECT name, salary

FROM employees

WHERE salary > ALL (

SELECT salary

FROM employees

WHERE department_id = 10

);

Equivalent to:

WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 10);

144. What is the difference between = ANY and IN?

Answer:

= ANY

• Compares a value against each result

• Returns TRUE if any match found

salary = ANY (subquery)

IN

• Shortcut syntax for = ANY

salary IN (subquery)

Difference:
SQL Interview Questions Guide | © Crack Interview 2025
 67
There is no logical difference
IN is simpler and more readable.

145. How does EXISTS improve performance in large datasets?

Answer:

Performance reasons:

✔ Stops scanning once a match is found

✔ Does not return or store full result set
✔ Uses index lookups efficiently
✔ Works well with correlated subqueries

Example:

WHERE EXISTS (

SELECT 1

FROM orders o

WHERE o.customer_id = [Link]

);

Once one order is found, SQL stops scanning further rows.

146. Can NOT EXISTS replace LEFT JOIN IS NULL?

Answer:

Yes.
NOT EXISTS is often better and safer.

LEFT JOIN approach:

SELECT [Link]

FROM customers c

LEFT JOIN orders o

ON [Link] = o.customer_id

WHERE o.customer_id IS NULL;

NOT EXISTS approach:

SELECT [Link]
SQL Interview Questions Guide | © Crack Interview 2025
 68
FROM customers c

WHERE NOT EXISTS (

SELECT 1

FROM orders o

WHERE o.customer_id = [Link]

);

Why NOT EXISTS is better:

✔ Cleaner logic
✔ Avoids NULL confusion
✔ Faster on large datasets

147. What is a semi-join and anti-join?

Answer:

Semi-Join

Returns rows from left table only when a match exists.

Implemented using:

• EXISTS

• IN

SELECT *

FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o WHERE o.customer_id = [Link]

);

Anti-Join

Returns rows from left table only when no match exists.

Implemented using:

• NOT EXISTS

• LEFT JOIN ... IS NULL

SQL Interview Questions Guide | © Crack Interview 2025
 69
SELECT *

FROM customers c

WHERE NOT EXISTS (

SELECT 1 FROM orders o WHERE o.customer_id = [Link]

);

148. How does SQL engine stop execution in EXISTS subqueries?

Answer:

Execution behavior:

1. Outer query processes one row

2. Subquery begins execution

3. As soon as one matching row is found:

o EXISTS returns TRUE

o SQL stops scanning further rows

4. Control returns to outer query

Why this is powerful:

• Minimal scanning

• Reduced CPU & I/O

• Huge performance gains on large tables

Key insight:

EXISTS is a short-circuit operator

149. What is a scalar subquery?

Answer:

A scalar subquery is a subquery that returns exactly one row and one column (a single value).

• Used where a single value is expected

• Commonly placed in SELECT, WHERE, or HAVING

Example:

SQL Interview Questions Guide | © Crack Interview 2025

 70
SELECT name,

(SELECT COUNT(*) FROM employees) AS total_employees

FROM employees;

Here, the subquery returns one value → total number of employees.

Important rule:

If a scalar subquery returns more than one row, SQL throws an error.

150. Can a subquery be used in the SELECT clause?

Answer:

Yes.
A subquery in the SELECT clause is usually a scalar subquery.

Example:

SELECT

[Link],

[Link],

(SELECT AVG(salary) FROM employees) AS avg_salary

FROM employees e;

This adds a calculated column based on another query.

Use cases:

• Display global aggregates

• Compute derived values per row

• Compare row values against overall metrics

151. What is a derived table?

Answer:

A derived table is a subquery placed in the FROM clause that acts like a temporary table for the outer query.

• Exists only during query execution

• Must have an alias

• Used for intermediate aggregations or transformations

SQL Interview Questions Guide | © Crack Interview 2025
 71
Example:

SELECT d.department_id, d.avg_salary

FROM (

SELECT department_id, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id

) d;

Here, d is a derived table named d.

152. Can a subquery be used in the FROM clause?

Answer:

Yes.
When used in the FROM clause, the subquery becomes a derived table.

Example:

SELECT t.department_id, t.total_salary

FROM (

SELECT department_id, SUM(salary) AS total_salary

FROM employees

GROUP BY department_id

) t;

The outer query treats t like a regular table.

153. How do you assign an alias to a subquery?

Answer:

An alias is mandatory for subqueries in the FROM clause.

Syntax:

FROM (subquery) alias_name

Example:

FROM (
SQL Interview Questions Guide | © Crack Interview 2025
 72
SELECT department_id, COUNT(*) cnt

FROM employees

GROUP BY department_id

) dept_count;

Without an alias, SQL raises an error

154. What is the difference between a derived table and a CTE?

Answer:

Aspect Derived Table CTE

Defined Inside FROM clause Using WITH clause

Readability Lower for complex queries Higher

Reusability One-time use Can be reused

Scope Single query block Entire query

Recursion ❌ Not allowed ✔️ Allowed

Example CTE:

WITH dept_avg AS (

SELECT department_id, AVG(salary) avg_sal

FROM employees

GROUP BY department_id

SELECT *

FROM dept_avg

WHERE avg_sal > 50000;

Rule:

Use CTEs for clarity and complex logic.

SQL Interview Questions Guide | © Crack Interview 2025

 73
155. Can you use ORDER BY inside a subquery?

Answer:

Generally No — with exceptions.

Not allowed:

ORDER BY inside subquery without LIMIT/TOP has no meaning.

Allowed when:

• Used with LIMIT / OFFSET (MySQL, PostgreSQL)

• Used with TOP (SQL Server)

Example:

SELECT *

FROM (

SELECT *

FROM employees

ORDER BY salary DESC

LIMIT 5

) top_employees;

Important:

Only the outermost ORDER BY guarantees final result order.

156. What are the restrictions on subqueries in the SELECT clause?

Answer:

Subqueries in SELECT must:

✔ Return one column

✔ Return one row per outer row
✔ Be scalar (single value)
✔ Not return multiple rows

Invalid example:

SELECT name,

(SELECT salary FROM employees)

SQL Interview Questions Guide | © Crack Interview 2025
 74
FROM employees;

Error: subquery returns multiple rows

157. Can aggregate functions be used inside subqueries?

Answer:

Yes.
Aggregate functions are very common inside subqueries.

Example:

SELECT name

FROM employees

WHERE salary > (

SELECT AVG(salary)

FROM employees

);

Aggregates inside derived tables:

FROM (

SELECT department_id, COUNT(*) cnt

FROM employees

GROUP BY department_id

) t;

Aggregates work normally inside subqueries.

SQL Interview Questions Guide | © Crack Interview 2025

 75
158. How does GROUP BY behave inside subqueries?

Answer:

GROUP BY inside a subquery behaves exactly like it does in a normal query.

Key points:

✔ Groups data before passing results to outer query

✔ Can reduce rows for comparison
✔ Often used with aggregate functions

Example:

SELECT [Link]

FROM employees e

WHERE salary = (

SELECT MAX(salary)

FROM employees

WHERE department_id = e.department_id

);

Here:

• Subquery groups implicitly by department via correlation

• Returns one value per department

Derived table example:

SELECT *

FROM (

SELECT department_id, AVG(salary) avg_sal

FROM employees

GROUP BY department_id

)t

WHERE avg_sal > 60000;

SQL Interview Questions Guide | © Crack Interview 2025

 76
159. Why are subqueries sometimes slower than joins?

Answer:

Subqueries can be slower mainly because of how often they are executed and how much data they process.

Key reasons:

1. Repeated execution (correlated subqueries)

• Correlated subqueries run once per outer row

• Large tables → thousands of executions

2. Limited index usage

• Functions or expressions inside subqueries can prevent index scans

3. Poor optimization

• Some subqueries cannot be flattened or merged into the main query

4. Larger intermediate results

• IN subqueries may build full result sets before comparison

Summary:

Joins are set-based and optimized, subqueries may behave row-by-row.

160. How does the query optimizer handle subqueries?

Answer:

Modern SQL optimizers try to rewrite subqueries for better performance.

Optimizer strategies:

✔ Convert subqueries into joins

✔ Perform subquery unnesting
✔ Apply predicate pushdown
✔ Use short-circuit evaluation (EXISTS)
✔ Cache deterministic subquery results

Important:

Optimizer decisions depend on statistics, indexes, and query structure.

SQL Interview Questions Guide | © Crack Interview 2025

 77
161. What is subquery unnesting?

Answer:

Subquery unnesting is an optimization where the database:

Converts a subquery into an equivalent join internally.

Example:

SELECT *

FROM employees

WHERE department_id IN (

SELECT department_id FROM departments

);

Optimizer rewrites internally as:

SELECT e.*

FROM employees e

JOIN departments d

ON e.department_id = d.department_id;

Benefits:

✔ Single scan
✔ Better index usage
✔ Faster execution

162. When should you avoid subqueries?

Answer:

Avoid subqueries when:

Processing large datasets

Subquery is correlated and runs per row
Subquery can be rewritten as a join or window function
Performance is critical
Query plan shows repeated scans

Preferred alternatives:

• JOINs

SQL Interview Questions Guide | © Crack Interview 2025

 78
• CTEs

• Window functions

163. How do you debug slow subqueries?

Answer:

Step-by-step approach:

1. Use EXPLAIN / EXPLAIN ANALYZE

Shows execution plan and cost.

EXPLAIN ANALYZE SELECT ...

2. Identify repeated subquery execution

Look for nested loops or subquery scans.

3. Check index usage

Add indexes on:

• Join columns

• Filter columns

4. Rewrite subquery as JOIN

Test performance difference.

5. Remove unnecessary functions

Avoid applying functions on indexed columns.

164. What is the impact of correlated subqueries on large tables?

Answer:

Impact:

Execution count = outer rows × subquery cost

High CPU & I/O usage
Slow response time
Poor scalability

Example:

If outer table has 1M rows → subquery runs 1M times

SQL Interview Questions Guide | © Crack Interview 2025
 79
Best practice:

Avoid correlated subqueries on large datasets unless absolutely necessary.

165. How do databases cache subquery results?

Answer:

Caching behavior depends on type:

✔ Non-correlated & deterministic

• Executed once

• Result cached and reused

Correlated

• Cannot be cached

• Executed repeatedly

Non-deterministic

• Functions like NOW(), RAND() disable caching

Example (cached):

WHERE salary > (SELECT AVG(salary) FROM employees)

166. Can window functions replace subqueries?

Answer:

Yes—in many cases, window functions are better.

Subquery example:

SELECT *

FROM employees e

WHERE salary > (

SELECT AVG(salary)

FROM employees

WHERE department_id = e.department_id

);

SQL Interview Questions Guide | © Crack Interview 2025

 80
Window function alternative:

SELECT *

FROM (

SELECT *,

AVG(salary) OVER (PARTITION BY department_id) AS dept_avg

FROM employees

)t

WHERE salary > dept_avg;

Benefits:

✔ One scan
✔ Better performance
✔ Cleaner logic

167. What are common interview traps related to subqueries?

Answer:

Common traps:

Using = instead of IN
Ignoring NULLs in IN subqueries
Using correlated subqueries on large tables
Assuming subquery executes once
Using ORDER BY inside subqueries incorrectly
Forgetting alias for derived tables
Misusing ANY vs ALL
Confusing EXISTS with IN
Expecting index usage inside function-wrapped columns

168. How would you explain subqueries to a beginner in simple terms?

Answer:

Simple explanation:

A subquery is a query inside another query that helps answer a question step-by-step.

SQL Interview Questions Guide | © Crack Interview 2025

 81
Example in plain English:

“Find employees who earn more than the average salary.”

1. First query: What is the average salary?

2. Second query: Who earns more than that?

SQL version:

SELECT name

FROM employees

WHERE salary > (

SELECT AVG(salary)

FROM employees

);

169. What is the purpose of the GROUP BY clause in SQL?

Answer:

The GROUP BY clause is used to group rows that have the same values in specified columns so that
aggregate functions (like SUM, COUNT, AVG, MIN, MAX) can be applied to each group.

Purpose:

• Convert row-level data into summary-level data

• Perform calculations per category, department, date, etc.

Example:

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id;

This calculates average salary per department, not for the entire table.

170. How does GROUP BY work internally?

Answer:

SQL Interview Questions Guide | © Crack Interview 2025

 82
Internally, SQL engines follow these steps:

Scan the table

Apply WHERE filters
Sort or hash rows based on GROUP BY columns
Create groups of identical values
Apply aggregate functions to each group
Apply HAVING filters
Return final result

Internals:

• Small datasets → Sort-based grouping

• Large datasets → Hash-based grouping

• Indexes can improve grouping performance

171. Which columns must be included in the GROUP BY clause?

Answer:

Rule:

All non-aggregated columns in the SELECT clause must appear in GROUP BY.

Valid:

SELECT department_id, COUNT(*)

FROM employees

GROUP BY department_id;

Invalid:

SELECT department_id, salary, COUNT(*)

FROM employees

GROUP BY department_id;

salary is neither grouped nor aggregated.

SQL Interview Questions Guide | © Crack Interview 2025

 83
172. Can you use expressions in the GROUP BY clause?

Answer:

Yes, expressions are allowed.

Example:

SELECT YEAR(order_date), COUNT(*)

FROM orders

GROUP BY YEAR(order_date);

Other examples:

• GROUP BY salary * 12

• GROUP BY UPPER(city)

• GROUP BY CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END

Using functions may disable index usage and impact performance.

173. What happens if a non-grouped column is used in the SELECT clause?

Answer:

Standard SQL:

Error is thrown

SELECT department_id, salary

FROM employees

GROUP BY department_id;

MySQL (non-strict mode):

Returns unpredictable values

Best practice:

Always follow standard SQL rules to avoid incorrect results.

SQL Interview Questions Guide | © Crack Interview 2025

 84
174. Can GROUP BY be used without aggregate functions?

Answer:

Yes, but it behaves like DISTINCT.

Example:

SELECT department_id

FROM employees

GROUP BY department_id;

Equivalent to:

SELECT DISTINCT department_id FROM employees;

Usage:

• Rare

• Answer for how to select distinct values from a column without using distinct

• Mostly for compatibility or readability

175. What is the default grouping behavior when GROUP BY is omitted?

Answer:

When GROUP BY is omitted:

• The entire table is treated as one single group

• Aggregate functions return one row

Example:

SELECT AVG(salary) FROM employees;

Average salary of all employees, not per department.

176. Can you group by multiple columns?

Answer:

Yes, grouping happens on the combined values.

SQL Interview Questions Guide | © Crack Interview 2025
 85
Example:

SELECT department_id, job_title, COUNT(*)

FROM employees

GROUP BY department_id, job_title;

Creates groups like:

• (Dept 10, Manager)

• (Dept 10, Analyst)

• (Dept 20, Analyst)

Important:

Order of columns matters logically, not syntactically.

SQL Interview Questions Guide | © Crack Interview 2025

 86
177. Provide result table from joining Table A and Table B

Table A

Null

Null

Table B

Null

Null

Null

Answer:

INNER JOIN

Condition: [Link] = [Link]

Since NULL = NULL → NOT TRUE

No rows match

Result:

0 rows

LEFT JOIN

Query:

SELECT *

FROM A

LEFT JOIN B

ON [Link] = [Link];

Logic:

• Take ALL rows from A

SQL Interview Questions Guide | © Crack Interview 2025

 87
• Match from B if possible

• If no match → B columns become NULL

Since no NULL matches NULL:

Result:

[Link] [Link]

NULL NULL

NULL NULL

2 rows (because A has 2 rows)

RIGHT JOIN

Query:

SELECT *

FROM A

RIGHT JOIN B

ON [Link] = [Link];

Logic:

• Take ALL rows from B

• Match from A if possible

• If no match → A columns become NULL

Since no match:

Result:

[Link] [Link]

NULL NULL

NULL NULL

NULL NULL

3 rows (because B has 3 rows)

SQL Interview Questions Guide | © Crack Interview 2025

 88

FULL OUTER JOIN

Query:

SELECT *

FROM A

FULL OUTER JOIN B

ON [Link] = [Link];

Logic:

• Take ALL rows from A

• Take ALL rows from B

• Match if possible

• No matches here

So:

• 2 rows from A (unmatched)

• 3 rows from B (unmatched)

Result:

[Link] [Link]

NULL NULL

NULL NULL

NULL NULL

NULL NULL

NULL NULL

Total = 5 rows

Final Answer Summary

Join Type Output Rows

SQL Interview Questions Guide | © Crack Interview 2025

 89
INNER JOIN 0

LEFT JOIN 2

RIGHT JOIN 3

FULL OUTER JOIN 5

178. What is the difference between WHERE and HAVING?

Answer:

Aspect WHERE HAVING

Filters Individual rows Groups of rows

Works on Raw data Aggregated data

Aggregates allowed ❌ No ✅ Yes

Execution stage Before GROUP BY After GROUP BY

Typical use Row-level filtering Group-level filtering

Example:

-- WHERE filters rows

SELECT *

FROM employees

WHERE salary > 50000;

-- HAVING filters groups

SELECT department_id, COUNT(*)

FROM employees

GROUP BY department_id

HAVING COUNT(*) > 5;

SQL Interview Questions Guide | © Crack Interview 2025

 90
179. Why can’t aggregate functions be used in the WHERE clause?

Answer:

Reason:

WHERE is evaluated before GROUP BY, so aggregate values do not exist yet.

Execution logic:

1. WHERE filters rows

2. GROUP BY forms groups

3. Aggregates are calculated

Invalid:

SELECT department_id

FROM employees

WHERE COUNT(*) > 5; -- Error

Correct:

SELECT department_id

FROM employees

GROUP BY department_id

HAVING COUNT(*) > 5;

180. How does HAVING filter grouped data?

Answer:

HAVING works after groups are formed and after aggregate functions are computed.

Example:

SELECT department_id, SUM(salary) AS total_salary

FROM employees

GROUP BY department_id

HAVING SUM(salary) > 1000000;

SQL Interview Questions Guide | © Crack Interview 2025
 91
Process:

• Rows grouped by department

• Salaries summed per department

• Groups with total salary > 1,000,000 retained

181. Can you use both WHERE and HAVING in the same query?

Answer:

Yes—and it is best practice.

Rule:

• WHERE → filters rows before grouping

• HAVING → filters groups after aggregation

Example:

SELECT department_id, AVG(salary)

FROM employees

WHERE status = 'ACTIVE'

GROUP BY department_id

HAVING AVG(salary) > 60000;

✔ WHERE reduces data early

✔ HAVING applies business rules on aggregates

182. Write a query to find departments with total salary greater than 1,000,000.

Answer:

SELECT department_id, SUM(salary) AS total_salary

FROM employees

GROUP BY department_id

HAVING SUM(salary) > 1000000;

✔ Correct use of HAVING

✔ Aggregate condition applied properly

SQL Interview Questions Guide | © Crack Interview 2025

 92

183. How do NULL values behave in GROUP BY?

Answer:

Behavior:

• All NULL values are treated as one group

• Aggregate functions ignore NULLs (except COUNT(*))

Example:

SELECT department_id, COUNT(*)

FROM employees

GROUP BY department_id;

If department_id is NULL:
All NULL rows form one group

Aggregate behavior:

• COUNT(column) → ignores NULL

• COUNT(*) → counts NULL rows

184. Can HAVING be used without GROUP BY?

Answer:

Yes, but it applies to the entire result set.

Example:

SELECT COUNT(*)

FROM employees

HAVING COUNT(*) > 100;

Entire table treated as one group

Use case:

• Rare

• Validation or threshold checks

SQL Interview Questions Guide | © Crack Interview 2025

 93
185. What is the difference between HAVING COUNT() > 1 and WHERE COUNT() > 1?

Answer:

WHERE version:

WHERE COUNT(*) > 1 -- Invalid

HAVING version:

HAVING COUNT(*) > 1 -- Valid

Reason:

• WHERE cannot access aggregate results

• HAVING is designed specifically for aggregates

186. How does HAVING impact query performance?

Answer:

Performance considerations:

HAVING filters after aggregation, so:

• All rows must be grouped first

• Aggregates must be computed before filtering

Best practice:

✔ Use WHERE to reduce rows early

✔ Use HAVING only when aggregate filtering is required

Optimization example:

-- Slower

SELECT department_id, SUM(salary)

FROM employees

GROUP BY department_id

HAVING department_id = 10;

-- Faster

SELECT department_id, SUM(salary)

FROM employees

SQL Interview Questions Guide | © Crack Interview 2025

 94
WHERE department_id = 10

GROUP BY department_id;

187. How do aggregate functions behave with NULL values?

Answer:

General rules:

• Most aggregate functions ignore NULL values

• COUNT(*) counts rows, including NULLs

Function NULL Handling

COUNT(*) Counts all rows

COUNT(column) Ignores NULLs

SUM() Ignores NULLs

AVG() Ignores NULLs

MIN() / MAX() Ignore NULLs

Example:

SELECT COUNT(salary), COUNT(*), AVG(salary)

FROM employees;

If salary has NULLs:

• COUNT(salary) → excludes NULLs

• COUNT(*) → includes all rows

188. What is the difference between COUNT(*), COUNT(1), and COUNT(column)?

Answer:

Function Behavior

COUNT(*) Counts all rows

COUNT(1) Counts all rows

COUNT(column) Counts non-NULL values only

Key points:

• COUNT(*) and COUNT(1) are functionally identical

SQL Interview Questions Guide | © Crack Interview 2025
 95
• Modern DBs optimize them the same

• COUNT(column) excludes NULLs

Example:

SELECT COUNT(*), COUNT(1), COUNT(manager_id)

FROM employees;

189. How does SUM() work with negative values?

Answer:

SUM() adds all numeric values, including negatives.

Example:

SELECT SUM(amount)

FROM transactions;

If values are:

100, -20, 50

Result:

130

Use cases:

• Accounting balances

• Profit/Loss calculations

• Adjustments & refunds

190. How do you calculate average per group?

Answer:

Use AVG() with GROUP BY.

Example:

SELECT department_id, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id;

Explanation:

SQL Interview Questions Guide | © Crack Interview 2025

 96
• Rows grouped by department

• Average calculated within each group

191. How do you count distinct values within a group?

Answer:

Use COUNT(DISTINCT column).

Example:

SELECT department_id, COUNT(DISTINCT job_title) AS job_count

FROM employees

GROUP BY department_id;

Notes:

• DISTINCT is applied inside each group

• Can be expensive on large datasets

192. How do you find the maximum value per category?

Answer:

Use MAX() with GROUP BY.

Example:

SELECT department_id, MAX(salary) AS max_salary

FROM employees

GROUP BY department_id;

Important:

• Returns maximum value, not the row itself

• To fetch full row → use subquery or window function

193. How do you calculate running totals without window functions?

Answer:

Use a correlated subquery.

SQL Interview Questions Guide | © Crack Interview 2025

 97
Example:

SELECT t1.order_date,

(SELECT SUM([Link])

FROM sales t2

WHERE t2.order_date <= t1.order_date) AS running_total

FROM sales t1

ORDER BY t1.order_date;

Limitations:

Slow for large tables

Executes subquery per row

Window functions are preferred if available.

194. How do you perform conditional aggregation?

Answer:

Use CASE inside aggregate functions.

Example:

SELECT

department_id,

SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,

SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count

FROM employees

GROUP BY department_id;

Key idea:

Conditions are applied inside aggregates, not in WHERE.

195. How do you count records based on a condition?

Answer:

Method 1: CASE + COUNT

SQL Interview Questions Guide | © Crack Interview 2025

 98
COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END)

Method 2: CASE + SUM

SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END)

Full example:

SELECT

COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END) AS active_users

FROM users;

196. What is the difference between conditional aggregation and filtered aggregation?

Answer:

Aspect Conditional Aggregation Filtered Aggregation

Method CASE inside aggregate WHERE / FILTER

Rows processed All rows Subset of rows

Flexibility Multiple conditions in one query One condition per filter

SQL support Universal DB-specific

Conditional aggregation:

SUM(CASE WHEN status = 'ACTIVE' THEN amount ELSE 0 END)

Filtered aggregation (PostgreSQL):

SUM(amount) FILTER (WHERE status = 'ACTIVE')

Key difference:

• Conditional aggregation keeps all data in scope

• Filtered aggregation removes rows before aggregation

197. What is GROUPING SETS in SQL?

Answer:

GROUPING SETS allow you to define multiple GROUP BY combinations in a single query.

Instead of writing multiple queries with UNION ALL, you can generate multiple aggregation levels at once.

Example:

SQL Interview Questions Guide | © Crack Interview 2025

 99
SELECT department_id, job_title, SUM(salary)

FROM employees

GROUP BY GROUPING SETS (

(department_id, job_title),

(department_id),

()

);

Result includes:

• Salary per department + job

• Salary per department

• Grand total

198. What is the use of ROLLUP?

Answer:

ROLLUP generates hierarchical subtotals from left to right.

Example:

SELECT department_id, job_title, SUM(salary)

FROM employees

GROUP BY ROLLUP (department_id, job_title);

Output levels:

1. (department_id, job_title)

2. (department_id)

3. (Grand Total)

Best for:

• Reports with hierarchical structure

• Drill-down reports

SQL Interview Questions Guide | © Crack Interview 2025

 100
199. What is the use of CUBE?

Answer:

CUBE generates all possible combinations of the grouped columns.

Example:

SELECT department_id, job_title, SUM(salary)

FROM employees

GROUP BY CUBE (department_id, job_title);

Output includes:

• (department_id, job_title)

• (department_id)

• (job_title)

• (Grand Total)

Best for:

• Multi-dimensional analytics

• BI & OLAP systems

200. What is the difference between ROLLUP and CUBE?

Answer:

Feature ROLLUP CUBE

Subtotals Hierarchical All combinations

Output size Smaller Larger

Complexity Simple Complex

Performance Faster Slower

Use case Reports Analytics

SQL Interview Questions Guide | © Crack Interview 2025

 101
201. How do GROUPING SETS improve query readability?

Answer:

Without GROUPING SETS:

SELECT department_id, SUM(salary) FROM employees GROUP BY department_id

UNION ALL

SELECT NULL, SUM(salary) FROM employees;

With GROUPING SETS:

SELECT department_id, SUM(salary)

FROM employees

GROUP BY GROUPING SETS ((department_id), ());

Benefits:

✔ Cleaner SQL
✔ Single table scan
✔ Easier maintenance

202. How do you identify subtotal rows in ROLLUP output?

Answer:

Subtotal rows contain NULLs in rolled-up columns.

Example:

SELECT

department_id,

job_title,

SUM(salary)

FROM employees

GROUP BY ROLLUP (department_id, job_title);

SQL Interview Questions Guide | © Crack Interview 2025

 102
• job_title IS NULL → Department subtotal

• department_id IS NULL AND job_title IS NULL → Grand total

But NULLs may also exist in data → use GROUPING() function.

203. What is the GROUPING() function?

Answer:

GROUPING(column) returns:

• 1 → Column is aggregated (subtotal)

• 0 → Column is actual data

Example:

SELECT

department_id,

job_title,

SUM(salary),

GROUPING(department_id) AS grp_dept,

GROUPING(job_title) AS grp_job

FROM employees

GROUP BY ROLLUP (department_id, job_title);

Usage:

✔ Distinguish real NULLs vs subtotal NULLs

✔ Label subtotal rows

204. How do NULLs behave in ROLLUP and CUBE?

Answer:

Behavior:

• Actual NULLs in data are grouped normally

• ROLLUP/CUBE introduce NULLs for subtotals

Best practice:

SQL Interview Questions Guide | © Crack Interview 2025

 103
Always use GROUPING() to differentiate NULL sources.

205. Are GROUPING SETS supported in all SQL databases?

Answer:

Database Support

PostgreSQL ✅

Oracle ✅

SQL Server ✅

MySQL ❌ (partial via UNION)

SQLite ❌

MySQL workaround:

Use UNION ALL.

206. What are real-world use cases for ROLLUP and CUBE?

Answer:

✔ Business reports:

• Sales by region → country → city

• Department → team → employee

✔ Financial reports:

• Monthly → quarterly → yearly totals

• Expense category breakdowns

✔ BI dashboards:

• Cross-dimensional metrics

• Pivot-style aggregations

✔ Performance analytics:

• Product × region × time analysis

SQL Interview Questions Guide | © Crack Interview 2025

 104
207. What is a Common Table Expression (CTE)?

Answer:

A Common Table Expression (CTE) is a temporary named result set defined within a SQL query using the
WITH keyword.
It exists only for the duration of the query and helps make complex queries more readable and
maintainable.

Simple example:

WITH dept_avg AS (

SELECT department_id, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id

SELECT *

FROM dept_avg

WHERE avg_salary > 60000;

208. How is a CTE different from a subquery?

Answer:

Aspect CTE Subquery

Readability High Lower

Reusability Can be referenced multiple times Rewritten each time

Structure Named logical block Inline

Complexity Easier for complex logic Harder to manage

Debugging Easy Difficult

Key idea:
SQL Interview Questions Guide | © Crack Interview 2025
 105
CTEs improve clarity and structure, not always performance.

209. What are the advantages of using CTEs?

Answer:

Major benefits:

✔ Improves readability
✔ Simplifies complex joins & aggregations
✔ Allows recursive queries
✔ Reduces query duplication
✔ Easier debugging & maintenance
✔ Logical separation of steps

210. What is the syntax of a CTE?

Answer:

Basic syntax:

WITH cte_name AS (

SELECT columns

FROM table

WHERE conditions

SELECT *

FROM cte_name;

Multiple CTEs:

WITH cte1 AS (...),

cte2 AS (...)

SELECT *

FROM cte1

JOIN cte2 ON ...;

211. Can a CTE be referenced multiple times in the same query?

SQL Interview Questions Guide | © Crack Interview 2025
 106
Answer:

Yes.

Example:

WITH sales_summary AS (

SELECT department_id, SUM(amount) AS total_sales

FROM sales

GROUP BY department_id

SELECT *

FROM sales_summary

WHERE total_sales > 100000

UNION ALL

SELECT *

FROM sales_summary

WHERE total_sales < 50000;

Note:

• Some databases re-execute the CTE

• Others may inline it

212. Are CTEs stored physically in the database?

Answer:

No.

CTEs are:

• Logical, not physical

• Exist only during query execution

• Not stored like tables or views

Exception:

• Some DBs may materialize CTEs internally for optimization

SQL Interview Questions Guide | © Crack Interview 2025

 107

213. What is a recursive CTE?

Answer:

A recursive CTE is a CTE that references itself, allowing traversal of hierarchical or graph-like data.

Used for:

• Organizational hierarchies

• Folder structures

• Bill of materials

• Tree traversal

214. What are the components of a recursive CTE?

Answer:

Three mandatory parts:

Anchor query – base case

Recursive query – references CTE itself
UNION ALL – combines results

Structure:

WITH RECURSIVE cte_name AS (

-- Anchor

SELECT ...

FROM table

WHERE condition

UNION ALL

-- Recursive

SELECT ...

FROM table

JOIN cte_name ON condition

SQL Interview Questions Guide | © Crack Interview 2025
 108
)

SELECT * FROM cte_name;

215. Write a use case where recursive CTEs are useful.

Answer:

Example: Employee hierarchy

WITH RECURSIVE emp_tree AS (

SELECT emp_id, name, manager_id, 1 AS level

FROM employees

WHERE manager_id IS NULL

UNION ALL

SELECT e.emp_id, [Link], e.manager_id, [Link] + 1

FROM employees e

JOIN emp_tree et ON e.manager_id = et.emp_id

SELECT * FROM emp_tree;

Output:

• CEO → Managers → Employees

• Hierarchical levels

216. What are the limitations of recursive CTEs?

Answer:

Limitations:

Slower than iterative logic

Risk of infinite recursion
Limited recursion depth (DB-specific)

SQL Interview Questions Guide | © Crack Interview 2025

 109
Harder to optimize
Poor performance on very large trees

Safety features:

• Max recursion limit

• Termination condition required

217. What are window (analytic) functions in SQL?

Answer:

Window functions perform calculations across a set of related rows (a window) without collapsing rows
into a single result.

Unlike aggregate functions, they:

• Return a value for every row

• Preserve the original row count

Examples of window functions:

• ROW_NUMBER()

• RANK()

• DENSE_RANK()

• NTILE()

• SUM() OVER()

• AVG() OVER()

218. How are window functions different from aggregate functions?

Answer:

Aspect Aggregate Functions Window Functions

Row count Reduced Preserved

GROUP BY Required Not required

Result One row per group One value per row

Use case Summary Analytics & ranking

Example:
SQL Interview Questions Guide | © Crack Interview 2025
 110
-- Aggregate

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id;

-- Window

SELECT name, department_id,

AVG(salary) OVER (PARTITION BY department_id) AS dept_avg

FROM employees;

219. What is the purpose of the OVER() clause?

Answer:

The OVER() clause defines:

• Partitioning (how rows are grouped)

• Ordering (row sequence)

• Frame (subset of rows)

Syntax:

function() OVER (

PARTITION BY column

ORDER BY column

ROWS | RANGE frame

Example:

SUM(salary) OVER (PARTITION BY department_id)

220. What is the difference between PARTITION BY and GROUP BY?

Answer:

PARTITION BY GROUP BY

Logical window Physical grouping

SQL Interview Questions Guide | © Crack Interview 2025

 111
Rows preserved Rows collapsed

Used with OVER Used with aggregates

Analytical Summary

Key takeaway:

PARTITION BY groups data within the window, not the result set.

221. Can window functions be used in the WHERE clause? Why?

Answer:

No, window functions cannot be used in WHERE.

Reason:

• WHERE executes before window functions

• Window values do not exist yet

Correct approach:

Use a subquery or CTE.

SELECT *

FROM (

SELECT *,

RANK() OVER (ORDER BY salary DESC) AS rnk

FROM employees

)t

WHERE rnk <= 5;

222. What is the default window frame in SQL?

Answer:

If ORDER BY is used:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

If ORDER BY is omitted:

• Entire partition is used

SQL Interview Questions Guide | © Crack Interview 2025

 112
Important:

• RANGE works on value-based ranges

• Can produce unexpected results with duplicates

223. Explain ROW_NUMBER() with an example.

Answer:

ROW_NUMBER() assigns a unique sequential number to rows.

Example:

SELECT name, salary,

ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn

FROM employees;

Output:

• Highest salary → rn = 1

• No ties (always unique)

224. What is the difference between RANK() and DENSE_RANK()?

Answer:

Function Tie Handling Gaps

RANK() Same rank for ties Gaps exist
DENSE_RANK() Same rank for ties No gaps
Example:

Salaries: 100, 100, 90

Salary RANK DENSE_RANK

100 1 1

100 1 1

90 3 2

SQL Interview Questions Guide | © Crack Interview 2025

 113

225. When should you use ROW_NUMBER() vs RANK()?

Answer:

Use ROW_NUMBER() when:

✔ Unique ordering required

✔ Pagination
✔ De-duplication

Use RANK() when:

✔ Handling ties matters

✔ Competitive rankings
✔ Top-N queries with ties

226. What is the use of NTILE() function?

Answer:

NTILE(n) divides rows into n equal-sized buckets.

Example:

SELECT name, salary,

NTILE(4) OVER (ORDER BY salary DESC) AS quartile

FROM employees;

Use cases:

• Quartiles

• Deciles

• Performance banding

• Distribution analysis

227. How do you calculate a running total using window functions?

Answer:

Use SUM() with OVER() and ORDER BY.

Example:

SQL Interview Questions Guide | © Crack Interview 2025

 114
SELECT

order_date,

amount,

SUM(amount) OVER (

ORDER BY order_date

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) AS running_total

FROM sales;

Explanation:

• Orders rows by date

• Adds all previous and current values

• Produces a cumulative sum per row

228. How do you calculate moving averages in SQL?

Answer:

Use AVG() with a sliding window frame.

Example: 3-day moving average

SELECT

order_date,

amount,

AVG(amount) OVER ( ORDER BY order_date

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

) AS moving_avg_3

FROM sales;

Use cases:

• Trend analysis

• Smoothing volatile data

• Financial indicators

SQL Interview Questions Guide | © Crack Interview 2025

 115

229. What is the difference between ROWS and RANGE in window frames?

Answer:

Aspect ROWS RANGE

Frame type Physical rows Logical value range

Precision Exact row count Value-based

Duplicate values Counted individually Treated as one

Predictability High Can be confusing

Example difference:

-- ROWS: exactly 1 row before

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

-- RANGE: all rows with same ORDER BY value

RANGE BETWEEN 1 PRECEDING AND CURRENT ROW

Best practice: Prefer ROWS for accurate analytics.

230. How do you find the second highest salary using window functions?

Answer:

Method using DENSE_RANK():

SELECT name, salary

FROM (

SELECT name, salary,

DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk

FROM employees

)t

WHERE rnk = 2;

SQL Interview Questions Guide | © Crack Interview 2025

 116
Why DENSE_RANK?

• Handles duplicate salaries correctly

• No rank gaps

[Link] is the use of LAG() and LEAD() functions?

Answer:

Function Purpose

LAG() Access previous row

LEAD() Access next row

Example:

SELECT

order_date,

amount,

amount - LAG(amount) OVER (ORDER BY order_date) AS diff

FROM sales;

Use cases:

• Growth comparison

• Trend change detection

• Time-series analysis

232. How do you compare current row values with previous rows?

Answer:

Use LAG() or LEAD().

Example:

SELECT

order_date,

amount,

CASE

SQL Interview Questions Guide | © Crack Interview 2025

 117
WHEN amount > LAG(amount) OVER (ORDER BY order_date)

THEN 'Increase'

ELSE 'Decrease'

END AS trend

FROM sales;

233. How do window functions handle NULL values?

Answer:

Behavior:

• Aggregates ignore NULLs (SUM, AVG)

• Ranking functions include NULL rows

• LAG/LEAD return NULL when no row exists

Example:

LAG(amount, 1, 0) OVER (ORDER BY order_date)

✔ Default value avoids NULLs

234. Can window functions be nested?

Answer:

Direct nesting is not allowed.

Invalid:

SUM(AVG(amount) OVER (...)) OVER (...)

Correct approach:

Use subqueries or CTEs.

WITH t AS (

SELECT AVG(amount) OVER (PARTITION BY category) AS avg_amt

FROM sales

SQL Interview Questions Guide | © Crack Interview 2025

 118
)

SELECT SUM(avg_amt) FROM t;

235. How do window functions impact performance?

Answer:

Performance characteristics:

• Require sorting (ORDER BY)

• Memory intensive

• Large partitions increase cost

Factors affecting performance:

• Partition size

• Ordering columns

• Frame type

• Data volume

236. Can indexes improve window function performance?

Answer:

Yes, indirectly.

How indexes help:

✔ Speed up ORDER BY
✔ Reduce sorting cost
✔ Improve partition scans

Example:

CREATE INDEX idx_sales_date ON sales(order_date);

Limitations:

• Indexes do not eliminate window computation

• Useful mainly for ORDER BY and PARTITION BY

SQL Interview Questions Guide | © Crack Interview 2025

 119
237. What is conditional logic in SQL?

Answer:

Conditional logic in SQL allows you to execute different expressions or return different values based on
specific conditions—similar to if–else logic in programming languages.

Used for:

• Categorization

• Data transformation

• Handling NULLs

• Conditional calculations

• Business rules implementation

Core tools:

• CASE

• COALESCE()

• NULLIF()

238. How does the CASE statement work internally?

Answer:

Internally, SQL evaluates a CASE expression top to bottom:

Each WHEN condition is checked in order

The first matching condition is returned
Remaining conditions are skipped
If no match → ELSE is returned
If no ELSE → returns NULL

Important:

CASE short-circuits like if–else.

SQL Interview Questions Guide | © Crack Interview 2025

 120
239. What is the difference between simple CASE and searched CASE?

Answer:

Feature Simple CASE Searched CASE

Comparison Equals only Any condition

Syntax CASE column WHEN value CASE WHEN condition

Flexibility Limited High

Usage Discrete values Ranges & complex logic

Simple CASE:

CASE status

WHEN 'A' THEN 'Active'

WHEN 'I' THEN 'Inactive'

END

Searched CASE:

CASE

WHEN salary > 80000 THEN 'High'

WHEN salary > 40000 THEN 'Medium'

ELSE 'Low'

END

240. How do you implement IF-ELSE logic in SQL?

Answer:

SQL uses CASE to implement IF-ELSE.

Example:

CASE

WHEN score >= 60 THEN 'Pass'

ELSE 'Fail'

END

Some databases also support:

SQL Interview Questions Guide | © Crack Interview 2025

 121
• IF() (MySQL)

• IIF() (SQL Server)

But CASE is standard and portable.

241. What is the use of COALESCE() in advanced queries?

Answer:

COALESCE() returns the first non-NULL value from a list.

Syntax:

COALESCE(expr1, expr2, ..., exprN)

Advanced uses:

✔ Provide default values

✔ Handle missing data
✔ Prevent NULL propagation
✔ Simplify CASE expressions

Example:

SELECT COALESCE(bonus, 0) FROM employees;

242. How do you avoid division-by-zero errors in SQL?

Answer:

Best approach: NULLIF()

SELECT sales / NULLIF(quantity, 0)

FROM orders;

Why it works:

• NULLIF(quantity, 0) → NULL if quantity = 0

• Division by NULL → NULL (safe)

Alternative:

CASE WHEN quantity = 0 THEN NULL ELSE sales / quantity END

SQL Interview Questions Guide | © Crack Interview 2025

 122
243. What is the difference between NULLIF() and COALESCE()?

Answer:

Function Purpose

NULLIF(a, b) Returns NULL if a = b

COALESCE() Returns first non-NULL value

Example:

NULLIF(10, 10) -- returns NULL

COALESCE(NULL, 5) -- returns 5

Combined usage:

COALESCE(sales / NULLIF(quantity, 0), 0)

244. How do you create dynamic categories using CASE?

Answer:

Example: Salary bands

SELECT name,

CASE

WHEN salary >= 80000 THEN 'High'

WHEN salary >= 40000 THEN 'Medium'

ELSE 'Low'

END AS salary_category

FROM employees;

Dynamic use cases:

• Risk levels

• Performance ratings

• Customer segmentation

245. How do you write queries with multiple conditional rules?

Answer:
SQL Interview Questions Guide | © Crack Interview 2025
 123
Use nested CASE or multiple WHEN clauses.

Example:

CASE

WHEN department = 'IT' AND salary > 70000 THEN 'Senior IT'

WHEN department = 'IT' THEN 'Junior IT'

WHEN department = 'HR' THEN 'HR Staff'

ELSE 'Other'

END

Best practice:

✔ Place most specific conditions first

✔ Avoid deep nesting when possible

246. What are common mistakes with CASE expressions?

Answer:

Common errors:

Forgetting ELSE → unexpected NULLs

Incorrect condition order
Mixing data types in THEN/ELSE
Using CASE in WHERE instead of logical expressions
Overusing nested CASE (hard to maintain)
Assuming CASE stops evaluating all WHENs (only first match matters)

247. How do CTEs affect query optimization?

Answer:

CTEs mainly affect optimization in how the optimizer chooses to execute them.

Key behaviors:

• Inlining: Optimizer treats CTE like a subquery (most modern DBs)

• Materialization: CTE is executed once and stored temporarily

Impact:

SQL Interview Questions Guide | © Crack Interview 2025

 124
✔ Improves readability
May block optimization if forced to materialize

Example risk:

WITH cte AS (

SELECT * FROM large_table

SELECT *

FROM cte

WHERE col = 10;

If materialized → entire table scanned before filtering.

248. When should you avoid using CTEs?

Answer:

Avoid CTEs when:

Query is performance-critical
CTE references very large datasets
DB engine always materializes CTEs (older PostgreSQL, SQL Server)
CTE is referenced only once and can be inlined
Filtering should happen earlier

Prefer:

• Inline subqueries

• Derived tables

• Joins

249. Can a CTE degrade performance compared to subqueries?

Answer:

Yes.

Why:

• Subqueries are often optimized and merged

• CTEs may prevent predicate pushdown

SQL Interview Questions Guide | © Crack Interview 2025
 125
• Materialized CTEs require extra memory and I/O

Example:

-- Slower in some engines

WITH cte AS (

SELECT * FROM sales

SELECT * FROM cte WHERE amount > 1000;

Better:

SELECT * FROM sales WHERE amount > 1000;

250. How does the query optimizer treat window functions?

Answer:

Window functions:

• Are evaluated after WHERE, GROUP BY, HAVING

• Require sorting or partitioning

• Cannot be short-circuited like EXISTS

Optimizer actions:

✔ Sort elimination (if index exists)

✔ Shared computation across multiple window functions
Cannot push filters inside window frames

251. How would you optimize a query with multiple window functions?

Answer:

Best practices:

✔ Use same PARTITION BY and ORDER BY

✔ Avoid repeating window definitions
✔ Use named windows (PostgreSQL)

WINDOW w AS (PARTITION BY dept ORDER BY salary)

SQL Interview Questions Guide | © Crack Interview 2025

 126
✔ Filter rows before window calculation
✔ Reduce partition size
✔ Index ORDER BY columns

252. Can window functions replace GROUP BY in some scenarios?

Answer:

Yes, partially

Example:

-- GROUP BY

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id;

-- Window function

SELECT DISTINCT department_id,

AVG(salary) OVER (PARTITION BY department_id)

FROM employees;

Difference:

• Window → row-level analytics

• GROUP BY → summary reports

253. What is the difference between analytical and reporting queries?

Answer:

Reporting Queries Analytical Queries

Aggregated Row-level

GROUP BY heavy Window functions

Static reports Trend analysis

Summaries Comparisons

Example:

• Reporting → Total sales per month

SQL Interview Questions Guide | © Crack Interview 2025

 127
• Analytical → Sales growth per day

254. How do advanced SQL features vary across databases?

Answer:

Feature PostgreSQL SQL Server MySQL Oracle

Window Functions ✅ Full ✅ Full ⚠ Partial ✅ Full

Recursive CTE ✅ ✅ ⚠ Limited ✅

FILTER clause ✅ ❌ ❌ ❌

GROUPING SETS ✅ ✅ ❌ ✅

255. What is database design?

Answer:
Database design is the process of structuring data in a database so it can be stored, managed, and retrieved
efficiently and accurately.

It defines:

• What data will be stored

• How data is organized (tables, columns)

• How tables relate to each other

• Rules and constraints for data integrity

In short:

Database design is the blueprint of a database.

256. Why is database design important before writing queries?

Answer:

Because bad design leads to bad performance and incorrect data, no matter how good the queries are.

Importance:

SQL Interview Questions Guide | © Crack Interview 2025

 128
✔ Prevents data duplication
✔ Ensures data consistency
✔ Improves query performance
✔ Makes maintenance easier
✔ Supports scalability
✔ Reduces bugs and anomalies

Example:

Poor design:

OrderID | CustomerName | CustomerPhone | ProductName

Good design:

Customers → Orders → OrderItems

Queries depend on structure. Fixing design later is costly.

257. What are the main steps involved in database design?

Answer:

Step-by-step process:

Requirement analysis
– Understand business rules and data needs

Conceptual design
– Identify entities and relationships (ER diagram)

Logical design
– Convert ER diagram into normalized tables

Physical design
– Decide data types, indexes, storage

Implementation & testing

– Create database and validate queries

258. What is the difference between logical design and physical design?

Answer:

Logical Design Physical Design

DBMS independent DBMS specific

SQL Interview Questions Guide | © Crack Interview 2025

 129
Tables, columns, relations Indexes, partitions

Normalization rules Performance tuning

Business-focused System-focused

Logical = What data & relations

Physical = How data is stored

259. What is an entity in database design?

Answer:

An entity is a real-world object or concept that can be uniquely identified and stored in the database.

Examples:

• Employee

• Customer

• Order

• Product

Each entity usually becomes a table.

260. What is an attribute?

Answer:

An attribute is a property or characteristic of an entity.

Example:

Entity: Employee
Attributes:

• employee_id

• name

• salary

• department_id

Attributes become columns in a table.

SQL Interview Questions Guide | © Crack Interview 2025

 130
261. What is a relationship between entities?

Answer:

A relationship defines how two or more entities are connected.

Example:

• One Customer places many Orders

• One Order contains many Products

Relationships are usually implemented using foreign keys.

262. What is cardinality in database relationships?

Answer:

Cardinality specifies how many instances of one entity relate to another.

Types:

• One-to-One (1:1)

• One-to-Many (1:N)

• Many-to-Many (M:N)

Example:

• One Department → Many Employees (1:N)

263. What is an ER (Entity-Relationship) diagram?

Answer:

An ER diagram is a visual representation of:

• Entities

• Attributes

• Relationships

• Cardinality and constraints

Symbols:

• Rectangle → Entity

• Oval → Attribute
SQL Interview Questions Guide | © Crack Interview 2025
 131
• Diamond → Relationship

• Lines → Connections

Purpose:

✔ Understand system structure

✔ Communicate with stakeholders
✔ Reduce design errors
✔ Guide table creation

264. What is a primary key and why is it important?

Answer:

A primary key (PK) is a column (or set of columns) that uniquely identifies each row in a table.

Characteristics:

✔ Unique
✔ NOT NULL
✔ One per table
✔ Immutable (should not change)

Example:

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

name VARCHAR(100),

salary INT

);

Importance:

• Prevents duplicate records

• Enables fast data access (indexed)

• Used to create relationships with other tables

• Ensures entity integrity

SQL Interview Questions Guide | © Crack Interview 2025

 132
265. What is a candidate key?

Answer:

A candidate key is any column (or set of columns) that can uniquely identify a row.

A table can have multiple candidate keys, but only one becomes the primary key.

Example:

Table: Users

• user_id

• email

• username

All three can uniquely identify users → candidate keys

266. What is a super key?

Answer:

A super key is any combination of columns that uniquely identifies a row.

Key idea:

All candidate keys are super keys, but not all super keys are candidate keys.

Example:

{employee_id}

{employee_id, name}

{employee_id, department}

Only the minimal ones become candidate keys.

267. What is a foreign key?

Answer:

A foreign key (FK) is a column in one table that references the primary key of another table.

Purpose:

✔ Establish relationships
✔ Enforce referential integrity

SQL Interview Questions Guide | © Crack Interview 2025

 133
Example:

CREATE TABLE orders (

order_id INT PRIMARY KEY,

customer_id INT,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

268. What is the difference between natural key and surrogate key?

Answer:

Natural Key Surrogate Key

Real-world meaning System-generated

Example: Email, Aadhaar Example: Auto-increment ID

Can change Stable

Business-dependent Business-independent

Best practice:

✔ Use surrogate keys as primary keys

✔ Enforce natural keys using UNIQUE constraints

269. What is a composite key?

Answer:

A composite key is a primary key made of two or more columns.

Example:

CREATE TABLE enrollments (

student_id INT,

course_id INT,

PRIMARY KEY (student_id, course_id)

);

Used when no single column is unique.

SQL Interview Questions Guide | © Crack Interview 2025
 134

270. Can a table have multiple candidate keys?

Answer:

Yes

A table can have multiple candidate keys, but:

• Only one is chosen as the primary key

• Others are enforced using UNIQUE constraints

Example:

UNIQUE (email)

UNIQUE (username)

271. Why should primary keys be immutable?

Answer:

Primary keys should never change because:

✔ They are referenced by foreign keys

✔ Changing them breaks relationships
✔ Causes cascading updates
✔ Impacts indexing and performance

Example:

Changing employee_id would affect:

• Payroll

• Attendance

• Performance tables

Best practice: Use surrogate, meaningless IDs.

272. What is referential integrity?

Answer:
SQL Interview Questions Guide | © Crack Interview 2025
 135
Referential integrity ensures that:

Every foreign key value must exist in the referenced primary key table (or be NULL).

Example:

orders.customer_id → customers.customer_id

You cannot:

• Insert an order for a non-existent customer

• Delete a customer who has orders (without rules)

273. What happens when referential integrity is violated?

Answer:

When violated, the database:

Rejects INSERT
Rejects UPDATE
Rejects DELETE

Example:

INSERT INTO orders (order_id, customer_id)

VALUES (101, 999); -- ERROR: customer does not exist

Referential actions:

Action Behaviour
RESTRICT Prevent operation
CASCADE Propagate change
SET NULL Set FK to NULL
SET DEFAULT Set default value

274. What is normalization in databases?

Answer:

Normalization is the process of organizing data into well-structured tables to:

• Reduce data redundancy

• Avoid data anomalies

• Ensure data consistency and integrity

SQL Interview Questions Guide | © Crack Interview 2025

 136
It divides large tables into smaller related tables and connects them using keys.

Goal:

Store each piece of data in only one place.

275. What problems does normalization solve?

Answer:

Normalization solves three major problems:

✔ Data redundancy

– Same data stored multiple times

✔ Data inconsistency

– Same data having different values

✔ Data anomalies

– Insert, update, and delete problems

Example problem:

StudentID | StudentName | Course | Instructor | InstructorPhone

InstructorPhone repeats many times → redundancy

276. What is denormalization and why is it used?

Answer:

Denormalization is the intentional introduction of redundancy into a normalized database.

Why use it?

✔ Faster read performance

✔ Fewer joins
✔ Better for reporting/analytics

Trade-off:

Normalization Denormalization
Less redundancy More redundancy
Slower reads Faster reads
Better consistency Risk of inconsistency
Used in data warehouses, dashboards, and high-read systems.

SQL Interview Questions Guide | © Crack Interview 2025

 137
277. What are functional dependencies?

Answer:

A functional dependency (FD) describes a relationship between attributes.

Definition:

A → B means A uniquely determines B

Example:

EmployeeID → EmployeeName

If EmployeeID is known, EmployeeName is fixed.

Functional dependencies guide normal form decisions.

278. What is partial dependency?

Answer:

A partial dependency occurs when:

• A table has a composite primary key

• A non-key attribute depends on only part of the key

Example:

Primary Key: (StudentID, CourseID)

StudentID → StudentName partial dependency

Violates Second Normal Form (2NF).

279. What is transitive dependency?

Answer:

A transitive dependency occurs when:

A → B and B → C
therefore A → C

Example:

EmployeeID → DepartmentID

SQL Interview Questions Guide | © Crack Interview 2025

 138
DepartmentID → DepartmentName

So:

EmployeeID → DepartmentName

Violates Third Normal Form (3NF).

280. What is insertion anomaly?

Answer:

An insertion anomaly occurs when:

• You cannot insert data without having other unrelated data

Example:

You cannot add a new department unless at least one employee exists.

Department data depends on employee existence.

281. What is deletion anomaly?

Answer:

A deletion anomaly occurs when:

• Deleting one record removes unintended data

Example:

Deleting the last employee of a department deletes department details.

282. Explain database normalization up to BCNF (1NF, 2NF, 3NF, BCNF) with proper
definitions and table examples.

Answer:

Normalization is a step-by-step process used to organize data efficiently, reduce redundancy, and eliminate
anomalies. It progresses through normal forms, each addressing specific problems.

First Normal Form (1NF)

SQL Interview Questions Guide | © Crack Interview 2025

 139
Definition

A table is in 1NF if:

• Each field contains atomic (indivisible) values

• No repeating groups or multi-valued attributes

• Each record can be uniquely identified

Table NOT in 1NF

StudentID StudentName Courses

1 Rahul SQL, Java

2 Neha Python

Problem:

• Courses contains multiple values

Table in 1NF

StudentID StudentName Course

1 Rahul SQL

1 Rahul Java

2 Neha Python

✔ Each column has atomic values

✔ Repeating groups removed

Second Normal Form (2NF)

Definition

A table is in 2NF if:

• It is already in 1NF

• There is no partial dependency

• All non-key attributes depend on the entire primary key

SQL Interview Questions Guide | © Crack Interview 2025

 140
Table in 1NF but NOT in 2NF

Primary Key: (StudentID, CourseID)

StudentID CourseID StudentName CourseName

1 C1 Rahul SQL

1 C2 Rahul Java

Problems:

• StudentName depends only on StudentID

• CourseName depends only on CourseID

Partial dependency exists

Convert to 2NF

Students

StudentID StudentName

1 Rahul

Courses

CourseID CourseName

C1 SQL

C2 Java

Enrollments

StudentID CourseID

✔ Partial dependencies removed

✔ All non-key attributes depend on full key

Third Normal Form (3NF)

Definition

A table is in 3NF if:

• It is in 2NF

• There is no transitive dependency

SQL Interview Questions Guide | © Crack Interview 2025

 141
• Non-key attributes depend only on the primary key

Table in 2NF but NOT in 3NF

EmpID EmpName DeptID DeptName

101 Amit D1 HR

102 Riya D2 IT

Problem:

• EmpID → DeptID

• DeptID → DeptName
Transitive dependency

Convert to 3NF

Employees

EmpID EmpName DeptID

Departments

DeptID DeptName

✔ Transitive dependency removed

✔ Each fact stored once

Boyce-Codd Normal Form (BCNF)

Definition

A table is in BCNF if:

For every functional dependency A → B,

A must be a super key

BCNF is stronger than 3NF.

Table in 3NF but NOT in BCNF

Student Course Instructor

SQL Interview Questions Guide | © Crack Interview 2025

 142
Rahul SQL Mohan

Neha Java Suresh

Functional Dependencies:

• (Student, Course) → Instructor

• Instructor → Course

Instructor is not a super key

Convert to BCNF

Instructor_Course

Instructor Course

Student_Instructor

Student Instructor

✔ Every determinant is a super key

✔ BCNF achieved

Normal Forms Summary Table

Normal Form Eliminates

1NF Repeating groups

2NF Partial dependency

3NF Transitive dependency

BCNF Non-superkey dependencies

Interview-Ready Conclusion

Normalization is a progressive refinement process.

1NF ensures atomicity, 2NF removes partial dependency,
3NF removes transitive dependency, and BCNF ensures
that every determinant is a super key.

SQL Interview Questions Guide | © Crack Interview 2025

 143
283. When should you choose denormalization over normalization?

Answer:

You should choose denormalization when:

✔ Read performance is more important than write performance

✔ Queries require many joins and are slow
✔ Data is mostly read-only
✔ System is analytics or reporting heavy

Common scenarios:

• Data warehouses

• Dashboards

• Reporting systems

• OLAP systems

Trade-off:

• Faster reads

• More storage

• Risk of data inconsistency

Rule of thumb:
Normalize for OLTP, denormalize for OLAP

284. How do OLTP and OLAP systems differ in design?

Answer:

Feature OLTP OLAP

Purpose Daily transactions Analytics & reporting

Queries Short, frequent Long, complex

Writes Heavy Rare

Design Highly normalized Denormalized

Example Banking, ecommerce BI, dashboards

SQL Interview Questions Guide | © Crack Interview 2025

 144
285. What is star schema and where is it used?

Answer:

A star schema is a data warehouse design where:

• One central fact table

• Connected to multiple dimension tables

• Structure looks like a star

Example:

Time

Product— Sales — Customer

Location

Usage:

✔ OLAP systems
✔ Reporting & BI tools
✔ Fast aggregations

286. What is snowflake schema?

Answer:

A snowflake schema is an extension of star schema where:

• Dimension tables are further normalized

• Structure looks like a snowflake ❄

Comparison:

Star Schema Snowflake Schema

Simple Complex

Faster queries More joins

More storage Less redundancy

SQL Interview Questions Guide | © Crack Interview 2025

 145
287. What are fact and dimension tables?

Answer:

Fact table:

• Stores numeric, measurable data

• Large table

• Contains foreign keys

Example:

sales_fact (date_id, product_id, revenue)

Dimension table:

• Stores descriptive attributes

• Smaller tables

Example:

product_dim (product_id, category, brand)

288. How do you design tables for scalability?

Answer:

Scalable design principles:

✔ Use surrogate keys

✔ Proper indexing strategy
✔ Partition large tables
✔ Avoid over-normalization
✔ Separate transactional and analytical workloads
✔ Plan for growth (billions of rows)

Design for future data volume, not current size.

289. What are common database design mistakes?

Answer:

Over-normalization
No indexing strategy
Using business data as primary keys

SQL Interview Questions Guide | © Crack Interview 2025

 146
Ignoring query patterns
Storing derived data unnecessarily
Mixing OLTP and OLAP workloads

290. How do you handle slowly changing dimensions (SCD)?

Answer:

Slowly Changing Dimensions track changes in dimension attributes over time.

Types:

• Type 1: Overwrite old data

• Type 2: Add new row with history

• Type 3: Store limited history in columns

Example (Type 2):

CustomerID | City | StartDate | EndDate | IsActive

Used heavily in data warehouses.

291. What is an index and why is it used?

Answer:

An index is a data structure (usually a B-Tree) that allows the database to find rows quickly without
scanning the entire table.

Without index:

Full Table Scan → Slow

With index:

Index Lookup → Fast

Why indexes are used:

✔ Faster SELECT queries

✔ Faster JOINs
✔ Faster WHERE, ORDER BY, GROUP BY
✔ Improves query scalability

Indexes trade storage + write cost for read performance.

SQL Interview Questions Guide | © Crack Interview 2025

 147
292. What are the different types of indexes?

Answer:

Common index types:

Type Description

Clustered Defines physical order of data

Non-clustered Separate structure with pointers

Unique Prevents duplicate values

Composite Index on multiple columns

Covering Query satisfied entirely by index

Full-Text Text search

Bitmap Low-cardinality columns

Hash Equality searches

293. What is a clustered index?

Answer:

A clustered index:

• Determines the physical order of rows in a table

• Data is stored in the same order as the index

Example:

PRIMARY KEY (employee_id)

The table itself is the index.

Characteristics:

✔ Fast range queries

✔ Efficient sorting
Expensive inserts/updates

294. What is a non-clustered index?

SQL Interview Questions Guide | © Crack Interview 2025

 148
Answer:

A non-clustered index:

• Stored separately from table data

• Contains index key + pointer to actual row

Example:

CREATE INDEX idx_name ON employees(name);

Characteristics:

✔ Multiple allowed per table

✔ Flexible
Extra lookup (bookmark lookup)

295. What is the difference between clustered and non-clustered indexes?

Answer:

Feature Clustered Non-clustered

Physical order Yes No

Storage Table itself Separate

Number allowed One Many

Range queries Faster Slower

Lookup Direct Pointer-based

296. How many clustered indexes can a table have and why?

Answer:

Only ONE clustered index per table

Why?

• A table can have only one physical order

• Multiple clustered indexes would require multiple physical layouts (impossible)

Typically defined on Primary Key.

SQL Interview Questions Guide | © Crack Interview 2025

 149

297. What is a composite index?

Answer:

A composite index is an index on multiple columns.

Example:

CREATE INDEX idx_order ON orders(customer_id, order_date);

Important:

• Column order matters

• (A, B) ≠ (B, A)

Used for queries filtering on both columns.

298. What is index selectivity?

Answer:

Index selectivity measures how unique indexed values are.

Formula:

Selectivity = Distinct values / Total rows

Examples:

• High selectivity → employee_id ✔

• Low selectivity → gender

High selectivity = better index performance.

299. What is a covering index?

Answer:

A covering index:

• Contains all columns required by a query

• Query is answered without accessing table data

Example:

CREATE INDEX idx_cover

ON orders(customer_id, order_date, total_amount);

SQL Interview Questions Guide | © Crack Interview 2025
 150
✔ Eliminates table lookups
✔ Very fast queries

300. When should you avoid creating indexes?

Answer:

Avoid indexes when:

Table is small
Columns are frequently updated
Low-selectivity columns
Write-heavy systems (many INSERT/UPDATE)
Temporary or staging tables
Indexes not used by queries

Why?

Indexes:

• Slow down writes

• Consume disk space

• Increase maintenance cost

301. Why might an index not be used by the optimizer?

Answer:

The optimizer avoids indexes when:

✔ Table is small (full scan is cheaper)

✔ Query returns a large percentage of rows
✔ Index has low selectivity
✔ Statistics are outdated
✔ Function is applied to indexed column
✔ Data distribution is skewed

Optimizer always chooses the lowest cost plan, not “use index at all costs”.

302. How do functions in WHERE clause affect index usage?

SQL Interview Questions Guide | © Crack Interview 2025

 151
Answer:

Applying a function to an indexed column breaks index usage.

Index NOT used:

SELECT * FROM employees

WHERE UPPER(name) = 'RAHUL';

Index used:

SELECT * FROM employees

WHERE name = 'Rahul';

Reason:
Index stores raw values, not computed results.

✔ Solution:

• Avoid functions on indexed columns

• Use function-based indexes (if supported)

303. How does using LIKE with wildcards impact index performance?

Answer:

Index NOT used:

WHERE name LIKE '%rahul'

Index USED:

WHERE name LIKE 'rahul%'

Why?

• Leading wildcard (%value) prevents index traversal

• Trailing wildcard allows range scan

Rule:

Index works only when left side of pattern is fixed

SQL Interview Questions Guide | © Crack Interview 2025

 152
304. What is index fragmentation?

Answer:

Index fragmentation occurs when index pages become logically or physically disordered due to frequent
data modifications.

Causes:

• Frequent INSERTs

• UPDATEs changing indexed columns

• DELETEs

Effects:

Slower reads
More I/O
Inefficient index scans

305. How do you rebuild or reorganize indexes?

Answer:

Rebuild:

• Drops and recreates index

• Removes fragmentation completely

• Expensive but effective

ALTER INDEX idx_name REBUILD;

Reorganize:

• Light defragmentation

• Less resource usage

ALTER INDEX idx_name REORGANIZE;

Use rebuild for heavy fragmentation, reorganize for moderate fragmentation.

SQL Interview Questions Guide | © Crack Interview 2025

 153
306. What is the impact of indexes on INSERT, UPDATE, and DELETE?

Answer:

Indexes slow down write operations because:

Operation Impact

INSERT Index entries must be added

UPDATE Index may need rebalancing

DELETE Index entries must be removed

More indexes = slower writes

Balance read vs write workload

307. What happens when indexes are created on low-cardinality columns?

Answer:

Low-cardinality column = few distinct values

Example: gender, status (Y/N)

Problems:

Index returns too many rows

Optimizer prefers full table scan
Extra storage, no benefit

✔ Better alternatives:

• Bitmap index (OLAP)

• Composite index

• Filtered index

308. How does data distribution affect index efficiency?

Answer:

If data is unevenly distributed, index usefulness drops.

Example:

status = 'ACTIVE' (95%)

SQL Interview Questions Guide | © Crack Interview 2025

 154
status = 'INACTIVE' (5%)

Querying ACTIVE → index useless

Querying INACTIVE → index useful

Optimizer uses statistics & histograms to decide.

309. Why is SELECT * considered bad for performance?

Answer:

Problems with SELECT *:

Reads unnecessary columns

More I/O and memory usage
Prevents covering index usage
Breaks queries when schema changes

Better approach:

SELECT employee_id, name FROM employees;

✔ Faster
✔ Uses covering indexes
✔ Cleaner, safer queries

310. Find the 2nd highest salary from the Employee table.

Answer:

Query

SELECT MAX(salary) AS SecondHighestSalary

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

emp_id name salary

1 A 80000

2 B 90000

3 C 90000

4 D 70000

SQL Interview Questions Guide | © Crack Interview 2025

 155

Explanation

1. Inner query finds highest salary

2. Outer query finds maximum salary less than highest

3. Handles duplicates automatically

Step-by-Step Execution

1. Inner query executes first

2. SELECT MAX(salary) FROM Employee;

Returns 90000

3. Outer query filters

4. salary < 90000

Remaining salaries → 80000, 70000

5. MAX() on remaining values

Returns 80000

Why This Works

• Automatically handles duplicate highest salaries

• Avoids LIMIT, which some databases don’t support

311. Find Employees Who Earn More Than Their Manager

Answer:

Return employees whose salary is greater than their manager.

Query

SELECT [Link] FROM Employee e

JOIN Employee m
ON e.manager_id = m.emp_id
WHERE [Link] > [Link];

SQL Interview Questions Guide | © Crack Interview 2025

 156
emp_id name salary manager_id

1 A 50000 NULL

2 B 70000 1

3 C 60000 1

Explanation

• Self join used

• e → employee

• m → manager

• Compare salaries

Step-by-Step Execution

1. Self Join

o Same table joined twice

o e = employee

o m = manager

2. Join Condition

3. e.manager_id = m.emp_id

4. Salary Comparison

5. [Link] > [Link]

Why Self Join Is Needed

Manager data exists in the same table, not a separate one.

312. Find Duplicate Records in a Table

Answer:

Query

SELECT email, COUNT(*) AS cnt

FROM Users

SQL Interview Questions Guide | © Crack Interview 2025

 157
GROUP BY email
HAVING COUNT(*) > 1;

Explanation

• GROUP BY groups rows

• HAVING filters aggregated data

Step-by-Step Execution

1. GROUP BY email
→ Groups same emails

2. COUNT(*)
→ Counts rows per email

3. HAVING COUNT(*) > 1

→ Filters duplicate groups

WHERE vs HAVING (IMPORTANT)

WHERE HAVING

Filters rows Filters groups

Used before GROUP BY Used after GROUP BY

Cannot use aggregates Can use aggregates

313. Delete Duplicate Records (Keep One)

Answer:

Query

DELETE FROM Employee

WHERE id NOT IN (
SELECT MIN(id)
FROM Employee
GROUP BY email
);

SQL Interview Questions Guide | © Crack Interview 2025

 158
Explanation

• Subquery keeps one record

• Others are deleted

Step-by-Step Execution

1. Subquery:

2. SELECT MIN(id) FROM Employee GROUP BY email;

→ Keeps one row per email

3. Outer DELETE:
→ Deletes all rows not in this list

Interview Warning

Some DBs (MySQL) need:

DELETE e

FROM Employee e

JOIN (...)

314. Top 3 Salaries in Each Department

Answer:

Query

SELECT *

FROM (

SELECT *,

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk

FROM Employee

)t

WHERE rnk <= 3;

SQL Interview Questions Guide | © Crack Interview 2025

 159
Step-by-Step Execution

1. PARTITION BY department_id
→ Resets ranking per department

2. ORDER BY salary DESC

→ Highest salary ranked first

3. DENSE_RANK()
→ Handles duplicate salaries correctly

RANK vs DENSE_RANK

Function Skips Numbers

RANK Yes
DENSE_RANK No

315. Find Departments with No Employees

Answer:

Query

SELECT d.department_name

FROM Department d

LEFT JOIN Employee e

ON d.department_id = e.department_id

WHERE e.department_id IS NULL;

Step-by-Step Execution

1. LEFT JOIN keeps all departments

2. No matching employee → NULL

3. Filter NULL rows

SQL Interview Questions Guide | © Crack Interview 2025

 160
316. User which have Consecutive Login Days

Answer:

Find users logged in for 3 consecutive days.

Query

SELECT DISTINCT a.user_id

FROM Logins a

JOIN Logins b

ON a.user_id = b.user_id

AND a.login_date = DATE_ADD(b.login_date, INTERVAL 1 DAY)

JOIN Logins c

ON a.user_id = c.user_id

AND a.login_date = DATE_ADD(c.login_date, INTERVAL 2 DAY);

Step-by-Step Execution

• b → day 1

• a → day 2

• c → day 3

Each join checks date continuity.

Interviewer Is Testing

✔ Date logic
✔ Multi self-join

317. Swap Values Using CASE

Answer:

Swap M ↔ F values.

SQL Interview Questions Guide | © Crack Interview 2025

 161
Query

UPDATE Employee

SET gender = CASE

WHEN gender = 'M' THEN 'F'

WHEN gender = 'F' THEN 'M'

END;

Why CASE Works

• Evaluates row-by-row

• No temp variable required

318. Find the Running Total (Cumulative Sum) of employee salary

Answer:

Calculate cumulative salary.

Query

SELECT emp_id,

salary,

SUM(salary) OVER (ORDER BY emp_id) AS running_total

FROM Employee;

Execution

• Orders rows

• Adds previous salaries to current row

Subquery vs Window

Subquery Window

SQL Interview Questions Guide | © Crack Interview 2025

 162
Subquery Window

Slow Fast

Complex Clean

319. Find Second Latest Record Per coustomer

Answer:

Second latest order per customer.

Query

SELECT *

FROM (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn

FROM Orders

)t

WHERE rn = 2;

Step-by-Step Execution

1. Partition per customer

2. Sort orders by date

3. Pick second row

SQL Interview Questions Guide | © Crack Interview 2025

 163
320. Identify Customers Who Placed Orders in Consecutive Months

Answer:

You are given an Orders table containing customer_id and order_date.

Write a SQL query to identify customers who placed at least one order in two consecutive months.

Query (MySQL / Standard SQL)

SELECT DISTINCT o1.customer_id

FROM Orders o1

JOIN Orders o2

ON o1.customer_id = o2.customer_id

AND PERIOD_DIFF(

EXTRACT(YEAR_MONTH FROM o1.order_date),

EXTRACT(YEAR_MONTH FROM o2.order_date)

) = 1;

Step-by-Step Explanation

1. EXTRACT(YEAR_MONTH FROM order_date)

o Converts date into YYYYMM format

2. PERIOD_DIFF(month1, month2)

o Returns number of months between two dates

3. Condition = 1

o Ensures consecutive months

4. DISTINCT

o Removes duplicate customer IDs

Edge Cases

• Multiple orders in same month → handled

• Year transition (Dec → Jan) → handled automatically

SQL Interview Questions Guide | © Crack Interview 2025

 164
321. Find the Most Frequently Ordered Product for Each Customer

Answer:

Given an Orders table with customer_id and product_id,

write a SQL query to find the product ordered the maximum number of times by each customer.

Query

SELECT customer_id, product_id

FROM (

SELECT customer_id,

product_id,

COUNT(*) AS order_count,

RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(*) DESC) AS rnk

FROM Orders

GROUP BY customer_id, product_id

)t

WHERE rnk = 1;

Step-by-Step Explanation

1. GROUP BY customer_id, product_id

o Counts how many times each product is ordered per customer

2. RANK() OVER

o Ranks products per customer by order count

3. rnk = 1

o Returns top ordered product(s)

Edge Case

• If multiple products have same max count → all returned

SQL Interview Questions Guide | © Crack Interview 2025

 165
322. Find Employees Whose Salary Is Greater Than the Average Salary of Their
Department

Answer:

Write a SQL query to retrieve employees whose salary is greater than the average salary of the department
they belong to.

Query

SELECT e.emp_id, [Link], [Link]

FROM Employee e

JOIN (

SELECT department_id, AVG(salary) AS avg_salary

FROM Employee

GROUP BY department_id

)d

ON e.department_id = d.department_id

WHERE [Link] > d.avg_salary;

Step-by-Step Explanation

1. Subquery calculates average salary per department

2. Join employee table with department average

3. Filter employees earning more than department average

SQL Interview Questions Guide | © Crack Interview 2025

 166
323. Retrieve Records That Exist in One Table but Not in Another

Answer:

You are given two tables TableA and TableB with identical structure.
Write a SQL query to find records present in TableA but missing in TableB.

Query

SELECT a.*

FROM TableA a

LEFT JOIN TableB b

ON [Link] = [Link]

WHERE [Link] IS NULL;

Step-by-Step Explanation

• LEFT JOIN keeps all records from TableA

• Missing match → NULL

• Filter NULL values

324. Find the Median Salary from Employee Table

Answer:

Write a SQL query to calculate the median salary of employees.

Query (Using Window Functions)

SELECT AVG(salary) AS median_salary

FROM (

SELECT salary,

ROW_NUMBER() OVER (ORDER BY salary) AS rn,

COUNT(*) OVER () AS cnt

FROM Employee

SQL Interview Questions Guide | © Crack Interview 2025

 167
)t

WHERE rn IN ((cnt + 1)/2, (cnt + 2)/2);

Step-by-Step Explanation

1. Assign row numbers by salary

2. Count total rows

3. Select middle value(s)

4. Average handles odd/even counts

325. Identify Users Who Performed an Action on Every Day of a Given Period

Answer:

Given a UserActivity table with user_id and activity_date,

find users who were active every day for the last 7 days.

Query

SELECT user_id

FROM UserActivity

WHERE activity_date >= CURDATE() - INTERVAL 6 DAY

GROUP BY user_id

HAVING COUNT(DISTINCT activity_date) = 7;

Step-by-Step Explanation

• Filters last 7 days

• Counts distinct days

• Only users with all 7 days returned

SQL Interview Questions Guide | © Crack Interview 2025

 168
326. Find the First and Last Transaction Date for Each User

Answer:
Write a SQL query to retrieve the first and last transaction date for each customer.

Query

SELECT customer_id,

MIN(transaction_date) AS first_txn,

MAX(transaction_date) AS last_txn

FROM Transactions

GROUP BY customer_id;

Step-by-Step Explanation

• MIN → first occurrence

• MAX → latest occurrence

327. Find Employees Who Never Received a Bonus

Answer:

Given Employee and Bonus tables, find employees who never received a bonus.

Query

SELECT e.emp_id, [Link]

FROM Employee e

LEFT JOIN Bonus b

ON e.emp_id = b.emp_id

WHERE b.emp_id IS NULL;

Step-by-Step Explanation

• LEFT JOIN keeps all employees

SQL Interview Questions Guide | © Crack Interview 2025

 169
• Missing bonus rows → NULL

• Filter NULL values

328. Calculate Percentage Contribution of Each Product to Total Sales

Answer:

Write a SQL query to calculate each product’s contribution percentage to total sales amount.

Query
SELECT product_id,
SUM(amount) AS product_sales,
ROUND(
SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (),
2
) AS percentage_contribution
FROM Sales
GROUP BY product_id;

Step-by-Step Explanation

• Inner SUM → product sales

• Window SUM → total sales

• Percentage calculation

329. Detect Gaps in Sequential IDs

Answer:

Given a table containing sequential IDs,

write a SQL query to identify missing ID values.

Query

SELECT [Link] + 1 AS missing_id

FROM TableX t1
LEFT JOIN TableX t2
ON [Link] + 1 = [Link]
WHERE [Link] IS NULL;

SQL Interview Questions Guide | © Crack Interview 2025

 170
Step-by-Step Explanation

• Checks if next ID exists

• Missing match indicates gap

330. Find Employees Who Have the Same Salary Within the Same Department

Answer:

Given an Employee table containing emp_id, name, salary, and department_id,

write a SQL query to identify employees who share the same salary with at least one other employee in
the same department.

Query

SELECT emp_id, name, salary, department_id

FROM Employee

WHERE (salary, department_id) IN (

SELECT salary, department_id

FROM Employee

GROUP BY salary, department_id

HAVING COUNT(*) > 1

);

Step-by-Step Explanation

1. Subquery groups employees by:

o salary

o department_id

2. HAVING COUNT(*) > 1

o Finds duplicate salaries within a department

3. Outer query retrieves full employee details

Edge Case
SQL Interview Questions Guide | © Crack Interview 2025
 171
• Same salary in different departments → NOT considered duplicate

331. Retrieve the Latest Record for Each Group Without Using MAX()

Answer:

From an Orders table, retrieve the latest order for each customer, without using MAX(order_date).

Query

SELECT *

FROM (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn

FROM Orders

)t

WHERE rn = 1;

Step-by-Step Explanation

1. PARTITION BY customer_id

o Groups orders per customer

2. ORDER BY order_date DESC

o Latest order ranked first

3. ROW_NUMBER() = 1

o Picks latest record

SQL Interview Questions Guide | © Crack Interview 2025

 172
332. Identify Customers Who Stopped Ordering (Churn Detection)

Answer:

Given an Orders table, find customers who have not placed any order in the last 6 months.

Query

SELECT DISTINCT customer_id

FROM Orders

GROUP BY customer_id

HAVING MAX(order_date) < CURDATE() - INTERVAL 6 MONTH;

Step-by-Step Explanation

1. Group orders by customer

2. MAX(order_date) finds last order

3. Compare with current date

333. Find Employees Who Have Never Changed Their Salary

Answer:

Given a SalaryHistory table, write a SQL query to find employees whose salary has never changed.

Query

SELECT emp_id

FROM SalaryHistory

GROUP BY emp_id

HAVING COUNT(DISTINCT salary) = 1;

Step-by-Step Explanation

1. Group salary records per employee

2. Count distinct salary values

SQL Interview Questions Guide | © Crack Interview 2025
 173
3. If count = 1 → no change

334. Find the 3rd Highest Salary Per Department (Exact Rank)

Answer:

Write a SQL query to retrieve employees who earn the 3rd highest salary in each department.

Query

SELECT *

FROM (

SELECT *,

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk

FROM Employee

)t

WHERE rnk = 3;

Step-by-Step Explanation

• Salary ranking resets per department

• DENSE_RANK avoids skipping ranks

• Filters exactly rank 3

Interviewer Is Testing

✔ Ranking precision
✔ Partitioned analytics

SQL Interview Questions Guide | © Crack Interview 2025

 174
335. Calculate Month-over-Month Sales Growth Percentage

Answer:

Given a Sales table with sale_date and amount,

calculate the month-over-month percentage growth in total sales.

Query

SELECT month,
total_sales,
ROUND(
(total_sales - LAG(total_sales) OVER (ORDER BY month)) * 100.0 /
LAG(total_sales) OVER (ORDER BY month),
2
) AS growth_percentage
FROM (
SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month,
SUM(amount) AS total_sales
FROM Sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m')
) t;

Step-by-Step Explanation

1. Aggregate sales per month

2. LAG() gets previous month sales

3. Formula calculates growth %

336. Find Users Who Performed Exactly One Transaction Per Day

Answer:

Identify users who performed exactly one transaction per day, across all their activity days.

Query

SELECT user_id

FROM Transactions

GROUP BY user_id, transaction_date

HAVING COUNT(*) = 1;

SQL Interview Questions Guide | © Crack Interview 2025

 175
Step-by-Step Explanation

• Groups by user and date

• Filters days with only one transaction

337. Find Records Where Value Increased Compared to Previous Row

Answer:

Given a Metrics table with daily values,

find dates where the value increased compared to the previous day.

Query

SELECT date, value

FROM (

SELECT date,

value,

LAG(value) OVER (ORDER BY date) AS prev_value

FROM Metrics

)t

WHERE value > prev_value;

Step-by-Step Explanation

• LAG() retrieves previous row value

• Compare current vs previous

SQL Interview Questions Guide | © Crack Interview 2025

 176
338. Identify Products That Were Never Sold

Answer:

Given Products and Sales tables,

write a SQL query to find products that were never sold.

Query

SELECT p.product_id, p.product_name

FROM Products p

LEFT JOIN Sales s

ON p.product_id = s.product_id

WHERE s.product_id IS NULL;

Step-by-Step Explanation

• LEFT JOIN keeps all products

• Missing sales → NULL

339. Find Employees With Longest Tenure in Each Department

Answer:

Write a SQL query to find employees who have the longest tenure (earliest joining date) in each
department.

Query

SELECT *

FROM (

SELECT *,

RANK() OVER (PARTITION BY department_id ORDER BY join_date) AS rnk

FROM Employee

)t

WHERE rnk = 1;
SQL Interview Questions Guide | © Crack Interview 2025
 177

Step-by-Step Explanation

• Earliest joining date ranked first

• Handles multiple employees joining same day

340. Identify Continuous Date Ranges (Gaps & Islands Problem)

Answer:

You are given a table containing dates when a system was active.
Write a SQL query to identify continuous ranges of dates (islands) where activity happened without any
gaps.

Query

SELECT MIN(activity_date) AS start_date,

MAX(activity_date) AS end_date

FROM (

SELECT activity_date,

DATE_SUB(activity_date,

INTERVAL ROW_NUMBER() OVER (ORDER BY activity_date) DAY) AS grp

FROM Activity

)t

GROUP BY grp;

Step-by-Step Explanation

1. ROW_NUMBER() assigns sequential numbers to dates

2. Subtracting row number from date:

o Continuous dates result in same computed value

3. Grouping by that value creates date islands

SQL Interview Questions Guide | © Crack Interview 2025

 178
341. Find Customers Who Bought All Products

Answer:

Given a Purchases table containing customer_id and product_id,

write a SQL query to find customers who have purchased every product available in the Products table.

Query

SELECT customer_id
FROM Purchases
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) =
(SELECT COUNT(*) FROM Products);

Step-by-Step Explanation

1. Count distinct products purchased per customer

2. Compare with total products available

3. If equal → customer bought all products

342. Find the Longest Streak of Consecutive Activity per User

Answer:

Given a UserLogins table, write a SQL query to find the longest consecutive login streak (in days) for each
user.

Query

SELECT user_id,
COUNT(*) AS streak_length
FROM (
SELECT user_id,
login_date,
DATE_SUB(login_date,
INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
FROM UserLogins
)t
GROUP BY user_id, grp;

Step-by-Step Explanation

• Same gap-island logic applied per user

SQL Interview Questions Guide | © Crack Interview 2025
 179
• Grouped dates represent consecutive streaks

• COUNT gives streak length

343. Find Employees Who Earn More Than 80% of Employees

Answer:

Write a SQL query to find employees whose salary is greater than 80% of all employees’ salaries.

Query

SELECT *
FROM (
SELECT *,
PERCENT_RANK() OVER (ORDER BY salary) AS pr
FROM Employee
)t
WHERE pr >= 0.8;

Step-by-Step Explanation

1. PERCENT_RANK() assigns relative rank (0 to 1)

2. Values ≥ 0.8 → top 20% earners

Interviewer Is Testing

✔ Statistical window functions

✔ Percentile logic

SQL Interview Questions Guide | © Crack Interview 2025

 180
344. Detect Duplicate Transactions Made Within 5 Minutes

Answer:

Given a Transactions table, identify transactions made by the same user for the same amount within 5
minutes.

Query

SELECT t1.*
FROM Transactions t1
JOIN Transactions t2
ON t1.user_id = t2.user_id
AND [Link] = [Link]
AND t1.txn_id <> t2.txn_id
AND ABS(TIMESTAMPDIFF(MINUTE, t1.txn_time, t2.txn_time)) <= 5;

Step-by-Step Explanation

• Self join on user and amount

• Time difference ≤ 5 minutes

• Excludes same record comparison

345. Find the First Non-Repeating Value in a Column

Answer:

Given a table with values that may repeat,

write a SQL query to find the first value that appears only once.

Query

SELECT value
FROM Data
GROUP BY value
HAVING COUNT(*) = 1
ORDER BY MIN(id)
LIMIT 1;

Step-by-Step Explanation

1. Group values and count occurrences

2. Filter non-repeating values

SQL Interview Questions Guide | © Crack Interview 2025
 181
3. Order by original insertion order

346. Calculate Retention Rate (Day-1 Retention)

Answer:

Given a UserActivity table, calculate Day-1 retention rate,

i.e., users who returned the next day after signup.

Query

SELECT
COUNT(DISTINCT a.user_id) * 100.0 /
COUNT(DISTINCT s.user_id) AS retention_rate
FROM Signups s
LEFT JOIN UserActivity a
ON s.user_id = a.user_id
AND a.activity_date = DATE_ADD(s.signup_date, INTERVAL 1 DAY);

Step-by-Step Explanation

• Join signup day with next-day activity

• Numerator → retained users

• Denominator → total signups

347. Find Orders That Contain Exactly the Same Products

Answer:

Identify orders that contain exactly the same set of products, regardless of order.

Query

SELECT o1.order_id, o2.order_id

FROM OrderItems o1
JOIN OrderItems o2
ON o1.product_id = o2.product_id
AND o1.order_id < o2.order_id
GROUP BY o1.order_id, o2.order_id
HAVING COUNT(*) =
(SELECT COUNT(*) FROM OrderItems oi WHERE oi.order_id = o1.order_id)
AND COUNT(*) =
(SELECT COUNT(*) FROM OrderItems oi WHERE oi.order_id = o2.order_id);

SQL Interview Questions Guide | © Crack Interview 2025

 182

Step-by-Step Explanation

• Match same products

• Ensure product count matches both orders

• Guarantees exact match

348. Identify Employees Promoted Every Year

Answer:

Given a JobHistory table, find employees who were promoted every single year without skipping.

Query

SELECT emp_id
FROM JobHistory
GROUP BY emp_id
HAVING COUNT(DISTINCT YEAR(promotion_date)) =
MAX(YEAR(promotion_date)) - MIN(YEAR(promotion_date)) + 1;

Step-by-Step Explanation

• Continuous year logic

• Compares span vs count

349. Find the Most Recent Record Before a Given Date

Answer:

For each customer, retrieve the most recent order placed before a given date (2024-01-01).

Query

SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM Orders
SQL Interview Questions Guide | © Crack Interview 2025
 183
WHERE order_date < '2024-01-01'
)t
WHERE rn = 1;

Step-by-Step Explanation

• Filters orders before cutoff

• Picks latest among those

• Partition ensures per customer logic

350. Find Users Whose Activity Strictly Increased Every Day

Answer:

Write a SQL query to identify users whose activity count strictly increased every day (no equal or decreasing
values).

Query

SELECT user_id
FROM (
SELECT user_id,
activity_date,
activity_count,
LAG(activity_count) OVER (PARTITION BY user_id ORDER BY activity_date) AS prev_count
FROM UserMetrics
)t
GROUP BY user_id
HAVING SUM(
CASE
WHEN prev_count IS NOT NULL AND activity_count <= prev_count THEN 1
ELSE 0
END
) = 0;

Step-by-Step Explanation

1. LAG(activity_count)

o Retrieves previous day’s activity for same user

2. Compare current vs previous:

o If activity_count <= prev_count → violation

3. Sum all violations

SQL Interview Questions Guide | © Crack Interview 2025

 184
4. Keep users with zero violations

351. Find Employees Who Have Worked Under More Than One Manager

Answer:

Given an EmployeeHistory table that tracks emp_id, manager_id, and from_date,

write a SQL query to find employees who have reported to more than one manager over time.

Query

SELECT emp_id
FROM EmployeeHistory
GROUP BY emp_id
HAVING COUNT(DISTINCT manager_id) > 1;

Step-by-Step Explanation

1. Group records per employee

2. Count distinct managers

3. More than one → employee changed manager

352. Find Customers Whose Spending Is Always Above the Customer Average

Answer:

Given a Transactions table, identify customers whose every transaction amount is greater than their own
average transaction amount.

Query

SELECT customer_id
FROM Transactions
GROUP BY customer_id
HAVING MIN(amount) > AVG(amount);

Step-by-Step Explanation

• AVG(amount) → customer’s average

• MIN(amount) → smallest transaction

SQL Interview Questions Guide | © Crack Interview 2025

 185
• If smallest > average → all are above average

353. Identify the Peak Concurrent Users (Max Overlap)

Answer:

Given a Sessions table with login_time and logout_time,

write a SQL query to find the maximum number of concurrent users at any time.

Query

SELECT MAX(concurrent_users) AS peak_users

FROM (
SELECT time,
SUM(change) OVER (ORDER BY time) AS concurrent_users
FROM (
SELECT login_time AS time, 1 AS change FROM Sessions
UNION ALL
SELECT logout_time AS time, -1 AS change FROM Sessions
)t
) x;

Step-by-Step Explanation

1. Login → +1

2. Logout → −1

3. Sort all events chronologically

4. Running sum = active users

5. MAX gives peak load

354. Find Products With Consistently Increasing Monthly Sales

Answer:

Write a SQL query to identify products whose monthly sales increased every month without decline.

Query

SELECT product_id
FROM (
SELECT product_id,
month,
SQL Interview Questions Guide | © Crack Interview 2025
 186
total_sales,
LAG(total_sales) OVER (PARTITION BY product_id ORDER BY month) AS prev_sales
FROM MonthlySales
)t
GROUP BY product_id
HAVING SUM(
CASE
WHEN prev_sales IS NOT NULL AND total_sales <= prev_sales THEN 1
ELSE 0
END
) = 0;

Step-by-Step Explanation

• LAG compares month-to-month sales

• Any decline → violation

• Only perfect growth products remain

355. Find Employees Who Joined Before Their Manager

Answer:

Given an Employee table with emp_id, manager_id, and join_date,

find employees who joined the company before their manager.

Query

SELECT e.emp_id, [Link]

FROM Employee e
JOIN Employee m
ON e.manager_id = m.emp_id
WHERE e.join_date < m.join_date;

Step-by-Step Explanation

• Self join employee with manager

• Compare join dates

• Identifies organizational anomalies

SQL Interview Questions Guide | © Crack Interview 2025

 187
356. Find the First Purchase Date After Signup for Each User

Answer:

Given Users and Orders tables,

retrieve the first purchase date after signup for each user.

Query

SELECT u.user_id,
MIN(o.order_date) AS first_purchase_date
FROM Users u
LEFT JOIN Orders o
ON u.user_id = o.user_id
AND o.order_date > u.signup_date
GROUP BY u.user_id;

Step-by-Step Explanation

• Join with condition on date

• MIN selects earliest valid order

• LEFT JOIN keeps users without purchases

357. Find Customers Who Reduced Spending Month Over Month

Answer:

Identify customers whose total monthly spending decreased compared to the previous month.

Query

SELECT DISTINCT customer_id

FROM (
SELECT customer_id,
month,
total_spent,
LAG(total_spent) OVER (PARTITION BY customer_id ORDER BY month) AS prev_spent
FROM MonthlySpending
)t
WHERE total_spent < prev_spent;

SQL Interview Questions Guide | © Crack Interview 2025

 188
Step-by-Step Explanation

• LAG compares current vs previous month

• Filters only decreasing patterns

358. Find Orders Where Order Value Exceeds Customer’s Average Order Value

Answer:

Write a SQL query to retrieve orders whose value is greater than the customer’s average order value.

Query

SELECT o.*
FROM Orders o
JOIN (
SELECT customer_id, AVG(order_amount) AS avg_amt
FROM Orders
GROUP BY customer_id
)a
ON o.customer_id = a.customer_id
WHERE o.order_amount > a.avg_amt;

Step-by-Step Explanation

• Subquery computes customer averages

• Join allows row-level comparison

• Filters above-average orders

359. What is the difference between INNER JOIN and LEFT JOIN in real scenarios?

Answer:

INNER JOIN

• Returns only matching records

• Used when both sides must exist

SELECT *

FROM Orders o

INNER JOIN Customers c

SQL Interview Questions Guide | © Crack Interview 2025

 189
ON o.customer_id = c.customer_id;

LEFT JOIN

• Returns all records from left table

• Missing matches appear as NULL

SELECT *

FROM Customers c

LEFT JOIN Orders o

ON c.customer_id = o.customer_id;

Real Use Case

• INNER JOIN → completed transactions

• LEFT JOIN → customers with or without orders

360. Provide result for the join of Table1 & Table2 :- A) LEFT JOIN , B) RIGHT JOIN

Table1

ID Name

1001 A

1002 B

1003 C

1004 D

Table2

ID Subject

1004 Math

1005 Math

1006 History

1007 Physics
1008 CS

SQL Interview Questions Guide | © Crack Interview 2025

 190

Answer:

Left Join :-

ID Name [Link] Subject

1001 A NULL NULL

1002 B NULL NULL

1003 C NULL NULL

1004 D 1004 Maths

Right Join:-

ID Name [Link] Subject

1004 D 1004 Math

NULL NULL 1005 Math

NULL NULL 1006 History

NULL NULL 1007 Physics

NULL NULL 1008 CS

361. How does an index improve query performance, and when can it hurt
performance?

Answer:

How Index Helps

• Reduces full table scans

• Enables fast seek operations

When Index Hurts

• Frequent INSERT/UPDATE/DELETE

• Low-cardinality columns (gender, status)

Example

SQL Interview Questions Guide | © Crack Interview 2025

 191
Index on user_id → good
Index on is_active → often useless

362. What is the difference between UNION and UNION ALL? When should each be used?

Answer:
Both UNION and UNION ALL are used to combine result sets of multiple SELECT queries, but they behave
differently.

Key Differences

UNION UNION ALL

Removes duplicate rows Keeps duplicates

Slower (sorting + deduplication) Faster

Uses DISTINCT internally No duplicate check

Example

SELECT city FROM Customers

UNION

SELECT city FROM Suppliers;

When to Use

• UNION → when duplicates must be removed

• UNION ALL → when performance matters and duplicates are acceptable

Interview Insight

Always say: “UNION ALL is preferred unless deduplication is required.”

363. What is the difference between EXISTS and IN? Which is better and why?

Answer:

Both are used to filter rows based on a subquery, but they work differently internally.

IN Example

SELECT *

FROM Orders

WHERE customer_id IN (SELECT customer_id FROM Customers);

SQL Interview Questions Guide | © Crack Interview 2025
 192
EXISTS Example

SELECT *

FROM Orders o

WHERE EXISTS (

SELECT 1

FROM Customers c

WHERE c.customer_id = o.customer_id

);

Key Differences

• IN → compares values

• EXISTS → checks existence (true/false)

• EXISTS stops as soon as a match is found

Important Edge Case

• IN behaves unexpectedly with NULL

• EXISTS handles NULL safely

Interview Tip

Say: “EXISTS is generally better for large subqueries.”

364. What is a CROSS JOIN and where is it practically used?

Answer:

A CROSS JOIN produces a Cartesian product of two tables.

Example

SELECT *

FROM Colors

CROSS JOIN Sizes;

If Colors = 3 rows, Sizes = 4 rows → result = 12 rows.

Real-World Uses

• Generating combinations
SQL Interview Questions Guide | © Crack Interview 2025
 193
• Calendar tables

• Test data generation

Warning

• Can create huge result sets

• Should be used intentionally

Interview Insight

If candidate understands CROSS JOIN, interviewer assumes good join clarity.

365. How does ORDER BY behave inside subqueries or views?

Answer:

• ORDER BY inside a subquery does not guarantee final ordering

• Only the outermost ORDER BY matters

Misconception

SELECT *

FROM (

SELECT * FROM Employee ORDER BY salary DESC

) t;

Result order is not guaranteed.

Correct

SELECT *

FROM (

SELECT * FROM Employee

)t

ORDER BY salary DESC;

SQL Interview Questions Guide | © Crack Interview 2025

 194
366. What is an index, and how does it improve query performance internally?

Answer:

An index is a separate data structure (usually B-Tree) that allows the database to locate rows quickly
without scanning the entire table.

How It Works Internally

• Without index → Full Table Scan

• With index → Index Seek

o Database finds pointer to row directly

o Fewer disk reads (I/O)

Example

CREATE INDEX idx_emp_salary ON Employee(salary);

Important Trade-off

• Faster SELECT

• Slower INSERT / UPDATE / DELETE

367. Provide the result for the outer join

Table : A

Population CountryID Units

1000 1 40
2000 1 25
3000 3 30
4000 2 3

Table : B

CountryID Country
1 USA
2 Canada
3 Panama
4 Spain

SQL Interview Questions Guide | © Crack Interview 2025

 195
Answer:

Population CountryID Units [Link] [Link]

1000 1 40 1 USA
2000 1 25 1 USA
4000 2 35 2 Canada
3000 3 30 3 Panama
Null NULL Null 4 Spain

368. Why might the optimizer choose a full table scan even when an index exists?

Answer:

The optimizer is cost-based, not index-based.

Common Reasons

1. Large percentage of rows returned

2. Low-cardinality column

3. Outdated statistics

4. Functions used on indexed column

5. Implicit data type conversion

Example (Index Not Used)

WHERE YEAR(order_date) = 2024;

SQL Interview Questions Guide | © Crack Interview 2025

 196
369. How do functions in the WHERE clause affect index usage?

Answer:

Applying a function on an indexed column prevents the optimizer from using the index.

Bad

WHERE UPPER(name) = 'RAHUL';

Good

WHERE name = 'Rahul';

Reason

Index stores raw column values, not computed values.

Interview Insight

This is a very common performance pitfall.

SQL Interview Questions Guide | © Crack Interview 2025

 197
370. What is the difference between a clustered index and a non-clustered index?

Answer:

Feature Clustered Index Non-Clustered Index

Physical data order ✅ Yes (actual table data is sorted) ❌ No (data stored separately)

Number per table ❗ Only one ✅ Multiple allowed

Storage Data is the index Separate structure + pointer

Speed (read) 🚀 Faster for range queries 🚀 Fast for search

Speed (write) ❌ Slower (reorders data) ❌ Slight overhead

Requires extra space ❌ No ✅ Yes

Key used Usually Primary Key Any column

Leaf nodes contain Actual data rows Pointer to clustered index

Table without index Heap table Works on heap too

Range queries ⭐ Best choice ⭐ Good but slower

Sorting Already sorted Needs sorting

Use case Large datasets, range scans Search-heavy queries

Example

• Primary key → clustered (often)

• Secondary indexes → non-clustered

SQL Interview Questions Guide | © Crack Interview 2025

 198
371. What is the logical execution order of a SQL query?

Answer:

Although we write SQL queries in one order, the database executes them in a different logical order.

Logical Execution Order

1. FROM – Identify tables

2. JOIN / ON – Combine tables

3. WHERE – Filter rows

4. GROUP BY – Group rows

5. HAVING – Filter groups

6. SELECT – Choose columns

7. DISTINCT – Remove duplicates

8. ORDER BY – Sort result

9. LIMIT / OFFSET

SQL Interview Questions Guide | © Crack Interview 2025

 199
372. Explain the difference between DELETE, TRUNCATE, and DROP commands in SQL.
Compare them in detail.

Answer:

Simple Understanding (Student-Friendly)

• DELETE → “I want to remove some records but keep the table safe.”

• TRUNCATE → “I want to empty the table very fast but keep its structure.”

• DROP → “I don’t need this table anymore—delete everything.”

Feature / Aspect DELETE TRUNCATE DROP

Command Type DML (Data Manipulation DDL (Data Definition DDL (Data Definition
Language) Language) Language)

What it does Removes specific rows or Removes all rows Completely removes
all rows from a table from a table at once the table itself

WHERE clause ✅ Supported (can delete ❌ Not supported ❌ Not supported

selective rows)

Rollback ✅ Yes (if not committed) ❌ No (auto-commit) ❌ No

possible?

Table structure ✅ Yes ✅ Yes ❌ No (table is gone)

remains?

Data removed? Selected rows or all rows All rows All data + structure

Indexes remain? ✅ Yes ✅ Yes (but reset) ❌ No

Triggers fired? ✅ Yes (row-by-row) ❌ No ❌ No

Speed 🐢 Slower (row-by-row ⚡ Very fast ⚡ Very fast

operation)

Can be used on ✅ Yes (with conditions) ❌ No ✅ Yes

view?

Use case Remove specific or Clear full table quickly Remove table
conditional data permanently

SQL Interview Questions Guide | © Crack Interview 2025

 200

373. How do you find the second highest salary in SQL? Explain using 3 different
methods.

Answer:

Method 1: Using MAX() with Subquery (Most Common & Easy)

Query

SELECT MAX(salary) AS SecondHighestSalary

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

Explanation (Step-by-Step)

• Inner query finds the highest salary in the table.

• WHERE salary < highest_salary removes the top salary.

• Outer MAX() now returns the highest among remaining salaries, which is the second highest.

• Works well when salaries are distinct.

Method 2: Using ORDER BY with LIMIT / OFFSET (Ranking Based)

Query (MySQL / PostgreSQL)

SELECT salary FROM employees

ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Explanation

• ORDER BY salary DESC sorts salaries from highest to lowest.

• OFFSET 1 skips the highest salary.

• LIMIT 1 fetches the next record → second highest salary.

SQL Server Version

SELECT salary
FROM employees
ORDER BY salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

SQL Interview Questions Guide | © Crack Interview 2025

 201
Method 3: Using DENSE_RANK() (Most Professional & Interview-Ready)

Query

SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)t
WHERE rnk = 2;
Explanation

• DENSE_RANK() assigns ranks based on salary:

o Highest salary → rank 1

o Second highest → rank 2

• Subquery filters rnk = 2.

• Handles duplicate salaries correctly.

• Considered the best real-world approach.

When to Use

✔ Handles duplicates
✔ Scalable for Nth highest salary
✔ Preferred in advanced interviews

374. Difference between Rank, Row Number & Dense Rank?

Answer:

Sample Data (Employee Salary Table)

EmpID Name Department Salary

101 Rahul IT 50000

102 Ankit IT 60000

103 Priya IT 60000

104 Neha IT 70000

105 Aman IT 80000

SQL Interview Questions Guide | © Crack Interview 2025

 202
ROW_NUMBER()

What it does

• Assigns a unique sequential number to each row

• No ties allowed

• Even if values are same, numbering is different

SQL Query

SELECT Name, Salary,

ROW_NUMBER() OVER (ORDER BY Salary DESC) AS row_num

FROM Employee;

Result

Name Salary ROW_NUMBER

Aman 80000 1

Neha 70000 2

Ankit 60000 3

Priya 60000 4

Rahul 50000 5

Key Point

Same salary but different row numbers

RANK()

What it does

• Assigns same rank to equal values

• Skips numbers after duplicates

SQL Query

SELECT Name, Salary,

RANK() OVER (ORDER BY Salary DESC) AS rank_no

FROM Employee;

Result

SQL Interview Questions Guide | © Crack Interview 2025

 203
Name Salary RANK

Aman 80000 1

Neha 70000 2

Ankit 60000 3

Priya 60000 3

Rahul 50000 5

Key Point

Rank 4 is skipped because two people share rank 3

DENSE_RANK()

What it does

• Assigns same rank to equal values

• Does NOT skip numbers

SQL Query

SELECT Name, Salary,

DENSE_RANK() OVER (ORDER BY Salary DESC) AS dense_rank_no

FROM Employee;

Result

Name Salary DENSE_RANK

Aman 80000 1

Neha 70000 2

Ankit 60000 3

Priya 60000 3

Rahul 50000 4

Key Point

No gap in ranking numbers

SQL Interview Questions Guide | © Crack Interview 2025

 204
375. Write a SQL query to find all customers whose total purchase amount is greater
than the average total purchase amount across all customers.

Answer:

SELECT customer_id FROM sales

GROUP BY customer_id
HAVING SUM(amount) >
(
SELECT AVG(total_amount)
FROM (
SELECT SUM(amount) AS total_amount
FROM sales
GROUP BY customer_id
)t
);

Step-by-Step Explanation

Inner Subquery (per-customer total)

SELECT SUM(amount) AS total_amount

FROM sales
GROUP BY customer_id
• Calculates total purchase amount for each customer

• Result = one row per customer

Outer Subquery (average of totals)

SELECT AVG(total_amount)
FROM ( ... ) t
• Computes the average of all customers’ total purchases

• This gives benchmark value for comparison

Main Query

SELECT customer_id
FROM sales
GROUP BY customer_id
HAVING SUM(amount) > (average value)
• Groups data by customer

• HAVING filters after aggregation

• Returns only customers whose total > average total

SQL Interview Questions Guide | © Crack Interview 2025

 205
376. How do you find the department-wise 3rd highest salary in SQL? Explain clearly.

Answer:

Query

SELECT department, salary

FROM (
SELECT
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS rnk
FROM employees
)t
WHERE rnk = 3;

Step-by-Step Explanation (Very Easy)

1. PARTITION BY department
→ Separates data department-wise (HR, IT, Sales, etc.)

2. ORDER BY salary DESC

→ Sorts salaries from highest to lowest within each department

3. DENSE_RANK()
→ Assigns ranking:

o Highest salary → Rank 1

o Second highest → Rank 2

o Third highest → Rank 3

4. Outer query (WHERE rnk = 3)

→ Fetches only the 3rd highest salary per department

✔ Correct even when duplicate salaries exist

✔ Works in real-world datasets
✔ Scalable for Nth highest salary

SQL Interview Questions Guide | © Crack Interview 2025

 206
377. What is a Running Total in SQL and how do you calculate it? Explain with example.

Answer:

A Running Total is a cumulative sum where each row shows the total of the current value plus all previous
values based on a defined order (date, id, month, etc.).

Example meaning:

Total sales till today / till this month / till this transaction

Best & Modern Method: Using Window Function (SUM() OVER)

Query (Most Recommended)

SELECT
order_date,
sales,
SUM(sales) OVER (ORDER BY order_date) AS running_total
FROM orders;

Step-by-Step Explanation (Very Easy)

1. ORDER BY order_date
→ Defines the sequence in which rows are accumulated

2. SUM(sales) OVER(...)
→ Calculates cumulative sum instead of grouping

3. No GROUP BY used
→ Because window functions keep row-level details

SQL Interview Questions Guide | © Crack Interview 2025

 207
378. Write a SQL query to find the total salary and number of employees in each
department. Explain the query.

Answer:

SELECT
department,
COUNT(emp_id) AS total_employees,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Step-by-Step Explanation

1. SELECT department
→ We want to group data by each department.

2. COUNT(emp_id)
→ Counts total employees in that department.

3. SUM(salary)
→ Calculates total salary of that department.

4. AVG(salary)
→ Finds average salary in the department.

5. GROUP BY department
→ Groups all rows by department so aggregate functions work per group.

379. Find all departments where the average salary is greater than 50,000 and the total
number of employees is more than 2. Explain the query.

Answer:

SELECT
department,
COUNT(emp_id) AS total_employees,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000 AND COUNT(emp_id) > 2;

Step-by-Step Explanation

1. GROUP BY department
→ Groups employees by their department.

SQL Interview Questions Guide | © Crack Interview 2025

 208
2. COUNT(emp_id)
→ Counts total employees in each department.

3. AVG(salary)
→ Calculates average salary per department.

4. HAVING clause
→ Filters groups after aggregation:

o Only departments with average salary > 50,000

o And more than 2 employees

5. Why not WHERE?

o WHERE filters rows before aggregation

o HAVING filters aggregated groups

380. Find the total salary paid to employees in each department, including departments
that currently have no employees. Explain the query.

Answer:

Sample Tables

departments table

dept_id dept_name

1 IT

2 HR

3 Sales

4 Marketing

employees table

emp_id emp_name dept_id salary

1 Rahul 1 50000

2 Priya 2 40000

3 Aman 1 60000

SQL Interview Questions Guide | © Crack Interview 2025

 209
4 Neha 2 45000

5 Rohan 3 55000

Query Using LEFT JOIN + GROUP BY

SELECT
d.dept_name,
COUNT(e.emp_id) AS total_employees,
COALESCE(SUM([Link]), 0) AS total_salary
FROM departments d
LEFT JOIN employees e
ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY total_salary DESC;

Step-by-Step Explanation

1. LEFT JOIN
→ Ensures all departments appear, even if no employees exist.

2. ON d.dept_id = e.dept_id
→ Matches employees to their departments.

3. GROUP BY d.dept_name
→ Aggregates data per department.

4. COUNT(e.emp_id)
→ Counts employees in each department. Departments with no employees → 0.

5. SUM([Link])
→ Calculates total salary per department. Departments with no employees → NULL.

6. COALESCE(SUM([Link]), 0)
→ Replaces NULL with 0 for departments with no employees.

7. ORDER BY total_salary DESC

→ Optional: Shows highest paying departments first.

Output

dept_name total_employees total_salary

IT 2 110000

HR 2 85000

Sales 1 55000

SQL Interview Questions Guide | © Crack Interview 2025

 210
Marketing 0 0

✔ Includes Marketing even though no employees exist.

381. Count employees in each department having more than 5 employees.

Answer:

SELECT department_id, COUNT(*) AS num_employees

FROM employees

GROUP BY department_id

HAVING COUNT(*) > 5;

Goal

Return only departments where employee count > 5.

Concepts used

• GROUP BY

• Aggregate function COUNT()

• HAVING filter (filter after aggregation)

Step-by-step

1. FROM employees → start with all employees.

2. GROUP BY department_id → combine rows by department.

o Each group now represents one department.

3. COUNT(*) → count rows in each group.

4. HAVING COUNT(*) > 5 → filter grouped results.

o Cannot use WHERE because WHERE runs before grouping.

Interview tip

Use:

• WHERE → filter rows

• HAVING → filter groups

SQL Interview Questions Guide | © Crack Interview 2025

 211
382. Find employees who joined in the last 6 months.

Answer:

SELECT *

FROM employees

WHERE join_date >= CURRENT_DATE - INTERVAL '6 months';

Goal

Find recent hires.

Concepts used

• Date arithmetic

• CURRENT_DATE

Step-by-step

1. CURRENT_DATE → today’s date.

2. CURRENT_DATE - INTERVAL '6 months' → date 6 months ago.

3. Filter employees whose join date ≥ that value.

Real use

• probation checks

• new employee analysis

383. Write a query to find the median salary.

Answer:

SELECT AVG(salary) AS median_salary

FROM (

SELECT salary

FROM employees

ORDER BY salary

LIMIT 2 - (SELECT COUNT(*) FROM employees) % 2

OFFSET (SELECT (COUNT(*) - 1) / 2 FROM employees)

) AS median_subquery;
SQL Interview Questions Guide | © Crack Interview 2025
 212
Goal

Return the middle salary value.

Why complex?

SQL has no built-in MEDIAN in many DBs.

Logic

1. Sort salaries.
2. If odd rows → pick middle row.
3. If even rows → pick 2 middle rows and average them.

Key formulas

Case Rows picked

Odd count 1 row
Even count 2 rows

LIMIT 2 - COUNT%2

• odd → limit 1
• even → limit 2

Then AVG() returns median.

Interview tip

This is a classic advanced SQL question.

384. Find the longest consecutive streak of daily logins for each user.

Answer:

WITH login_dates AS (

SELECT user_id, login_date,

login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS grp

FROM user_logins

SELECT user_id, COUNT(*) AS streak_length, MIN(login_date) AS start_date, MAX(login_date) AS end_date

FROM login_dates

SQL Interview Questions Guide | © Crack Interview 2025

 213
GROUP BY user_id, grp

ORDER BY streak_length DESC;

Goal

Find consecutive daily login streaks.

Core trick → Gaps and Islands problem

How it works

1. Assign row numbers per user:

2. 2024-01-01 → rn 1

3. 2024-01-02 → rn 2

4. 2024-01-03 → rn 3

5. Subtract row_number from date:

6. login_date - rn → constant for consecutive days

7. Same result = same streak group.

Then group by (user_id, grp) to get streak length.

385. Recursive query to find the full reporting chain for each employee.

Answer:

WITH RECURSIVE reporting_chain AS (

SELECT id, name, manager_id, 1 AS level

FROM employees

WHERE manager_id IS NULL

UNION ALL

SELECT [Link], [Link], e.manager_id, [Link] + 1

FROM employees e

JOIN reporting_chain rc ON e.manager_id = [Link]

SELECT * FROM reporting_chain ORDER BY level, id;

SQL Interview Questions Guide | © Crack Interview 2025

 214
Goal

Build company hierarchy (CEO → employees).

Concepts

• Recursive CTE

• Hierarchical queries

Parts

Base query (CEO)

manager_id IS NULL

Top level.

Recursive part

Find employees whose manager is already found.

This repeats until all levels are built.

Real use

• Org charts

• File systems

• Category trees

386. Write a query to find gaps in a sequence of numbers (missing IDs).

Answer:

SELECT (id + 1) AS missing_id

FROM employees e1

WHERE NOT EXISTS (

SELECT 1 FROM employees e2 WHERE [Link] = [Link] + 1

ORDER BY missing_id;

Goal

Find missing numbers in ID sequence.

SQL Interview Questions Guide | © Crack Interview 2025

 215
Logic

For each id:

Check if next id exists.

If id=5 exists but id=6 doesn’t → missing 6.

387. Calculate cumulative distribution (CDF) of salaries.

Answer:

SELECT name, salary,

CUME_DIST() OVER (ORDER BY salary) AS salary_cdf

FROM employees;

Goal

Find salary percentile.

What is CDF?

CUME_DIST = % of rows ≤ current row.

Example:

Salary CDF

20k 0.2

30k 0.4

40k 0.6

Use cases

• Percentile ranking

• Pay analysis

388. Compare two tables and find rows with differences in any column (all columns).

Answer:

SELECT *

FROM table1 t1

FULL OUTER JOIN table2 t2 ON [Link] = [Link]

SQL Interview Questions Guide | © Crack Interview 2025
 216
WHERE t1.col1 IS DISTINCT FROM t2.col1

OR t1.col2 IS DISTINCT FROM t2.col2

OR t1.col3 IS DISTINCT FROM t2.col3;

Goal

Find rows that differ between two tables.

Concepts

• FULL OUTER JOIN → keep unmatched rows

• IS DISTINCT FROM → NULL-safe comparison

Real use

• Data migration validation

• ETL testing

389. Find customers who have not made any purchase.

Answer:

SELECT c.customer_id, [Link]

FROM customers c

LEFT JOIN sales s ON c.customer_id = s.customer_id

WHERE s.sale_id IS NULL;

Goal

Find inactive customers.

Logic

LEFT JOIN keeps all customers.

If no match in sales → NULL appears.

Filter NULL = no purchase.

Interview tip

Classic LEFT JOIN anti-join pattern.

SQL Interview Questions Guide | © Crack Interview 2025

 217
390. Write a query to perform a conditional aggregation (count males and females in
each department).

Answer:

SELECT department_id,

COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,

COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count

FROM employees

GROUP BY department_id;

Goal

Count categories in one query.

Trick

COUNT ignores NULL.

CASE returns value only when condition true.

Used in dashboards a LOT.

391. Write a query to calculate the difference between current row and previous row's
salary (lag function).

Answer:

SELECT name, salary,

salary - LAG(salary) OVER (ORDER BY id) AS salary_diff

FROM employees;

Compare row with previous row.

Window function LAG

LAG(column) → previous row value.

Example:

Salary Prev Diff

30k NULL NULL

35k 30k 5k

Uses
SQL Interview Questions Guide | © Crack Interview 2025
 218
• Growth analysis

• Trend detection

392. Write a query to get the first and last purchase date for each customer.

Answer:

SELECT customer_id,

MIN(purchase_date) AS first_purchase,

MAX(purchase_date) AS last_purchase

FROM sales

GROUP BY customer_id;

Goal

Find customer lifecycle timeline.

Concepts

• Aggregation

• MIN / MAX

How it works

Group all purchases by customer → then:

• MIN() → earliest purchase

• MAX() → most recent purchase

Real use

• Customer retention analysis

• RFM segmentation

393. Find departments with the highest average salary.

Answer:

WITH avg_salaries AS (

SELECT department_id, AVG(salary) AS avg_salary

FROM employees
SQL Interview Questions Guide | © Crack Interview 2025
 219
GROUP BY department_id

SELECT *

FROM avg_salaries

WHERE avg_salary = (SELECT MAX(avg_salary) FROM avg_salaries);

Goal

Find top-paying department(s).

Concepts

• CTE

• Aggregate of aggregate

Step-by-step

1. CTE calculates avg salary per department.

2. Find maximum of those averages.

3. Return department(s) equal to that value.

Interview tip

Handles ties automatically.

394. Write a query to find the difference in days between two dates in the same table.

Answer:

SELECT id, DATEDIFF(day, start_date, end_date) AS days_difference

FROM projects;

Note: DATEDIFF syntax varies — replace accordingly (e.g., DATEDIFF('day', start_date, end_date) in some
systems).

Goal

Find duration of projects.

Concepts

Date functions differ by DB:

DB Function

SQL Interview Questions Guide | © Crack Interview 2025

 220
DB Function

SQL Server DATEDIFF(day, start, end)

MySQL DATEDIFF(end, start)

PostgreSQL end_date - start_date

Real use

• SLA tracking

• Project analytics

395. Calculate the moving average of salaries over the last 3 employees ordered by hire
date.

Answer:

SELECT name, hire_date, salary,

AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS
moving_avg_salary

FROM employees;

Goal

Smooth salary trend over time.

Window frame explanation

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

For each row → take:

• current employee

• previous 2 employees

Then compute average.

Example window

Employee Salaries used

E3 E1,E2,E3

E4 E2,E3,E4

Used in
SQL Interview Questions Guide | © Crack Interview 2025
 221
• Trend analysis

• Forecasting

396. Find the most recent purchase per customer using window functions.

Answer:

SELECT *

FROM (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date DESC) AS rn

FROM sales

) sub

WHERE rn = 1;

Goal

Latest purchase for each customer.

Concepts

• Window ranking
• Top N per group

Logic

1. Partition by customer.
2. Order purchases newest first.
3. Row_number assigns:
4. newest → 1
5. older → 2,3,4...
6. Keep only row 1.

397. Write a query to perform a self-join to find pairs of employees in the same
department

Answer:

SELECT [Link] AS Employee1, [Link] AS Employee2, e1.department_id

SQL Interview Questions Guide | © Crack Interview 2025
 222
FROM employees e1

JOIN employees e2 ON e1.department_id = e2.department_id AND [Link] < [Link];

Goal

Find unique pairs in same department.

Why [Link] < [Link]?

Without it:

• A,B and B,A both appear.

• Also self pairing A,A.

This condition removes duplicates.

Real use

• Team pairing

• Peer matching

398. Write a query to pivot rows into columns dynamically (if dynamic pivot is not
supported, simulate it for fixed values).

Answer:

SELECT

department_id,

SUM(CASE WHEN job_title = 'Manager' THEN 1 ELSE 0 END) AS Managers,

SUM(CASE WHEN job_title = 'Developer' THEN 1 ELSE 0 END) AS Developers,

SUM(CASE WHEN job_title = 'Tester' THEN 1 ELSE 0 END) AS Testers

FROM employees

GROUP BY department_id;

Goal

Convert row categories → columns.

Pivot trick

CASE WHEN converts category to numeric flag:

Manager → 1

SQL Interview Questions Guide | © Crack Interview 2025

 223
Others → 0

Then SUM counts them.

399. Find customers who made purchases in every category available.

Answer:

SELECT customer_id

FROM sales s

GROUP BY customer_id

HAVING COUNT(DISTINCT category_id) = (SELECT COUNT(DISTINCT category_id) FROM sales);

Goal

Find power customers.

Concept → Relational Division

Logic

1. Count categories each customer bought.

2. Count total categories available.

3. Keep customers where both counts match.

Real use

• Loyalty analysis

• Cross-sell success

400. Identify employees who haven’t received a salary raise in more than a year.

Answer:

SELECT [Link]

FROM employees e

JOIN salary_history sh ON [Link] = sh.employee_id

GROUP BY [Link], [Link]

HAVING MAX(sh.raise_date) < CURRENT_DATE - INTERVAL '1 year';

SQL Interview Questions Guide | © Crack Interview 2025

 224
Goal

Find employees overdue for raise.

Key logic

1. Each employee has multiple raise records.

2. MAX(raise_date) → last raise.
3. Compare with today minus 1 year.

Real use

• HR analytics
• Performance review planning

401. What are the two mandatory parts of a Recursive CTE?

Answer:

A recursive CTE must contain:

1. Anchor Member – the base query that starts the recursion

2. Recursive Member – references the CTE itself and runs repeatedly

They are connected using UNION ALL.

WITH cte_name AS (

-- Anchor

SELECT ...

UNION ALL

-- Recursive

SELECT ...

FROM cte_name

SELECT * FROM cte_name;

SQL Interview Questions Guide | © Crack Interview 2025

 225
402. Write a query to generate numbers from 1 to 10 using a recursive CTE

Answer:

WITH numbers AS (

SELECT 1 AS n -- Anchor

UNION ALL

SELECT n + 1

FROM numbers

WHERE n < 10

SELECT * FROM numbers;

403. Find the hierarchy level of each employee

Answer:

Employees(emp_id, emp_name, manager_id)

Answer

sql

Copy code

WITH emp_hierarchy AS (

SELECT emp_id, emp_name, manager_id, 0 AS level

FROM Employees

WHERE manager_id IS NULL

UNION ALL

SELECT e.emp_id, e.emp_name, e.manager_id, [Link] + 1

FROM Employees e

JOIN emp_hierarchy h

ON e.manager_id = h.emp_id

SQL Interview Questions Guide | © Crack Interview 2025

 226
SELECT * FROM emp_hierarchy;

404. Why is UNION ALL used instead of UNION in recursive CTEs?

Answer:

Answer

• UNION removes duplicates → expensive & may break recursion

• UNION ALL allows fast iteration

• Recursive termination is controlled by the WHERE clause, not duplicate removal

405. Get full reporting chain (CEO → Employee path)

Answer:

Show full path like:

CEO → Manager → Employee

Answer

WITH emp_path AS (

SELECT emp_id, emp_name, manager_id,

CAST(emp_name AS VARCHAR(200)) AS path

FROM Employees

WHERE manager_id IS NULL

UNION ALL

SELECT e.emp_id, e.emp_name, e.manager_id,

CONCAT([Link], ' → ', e.emp_name)

FROM Employees e

JOIN emp_path p

ON e.manager_id = p.emp_id

SQL Interview Questions Guide | © Crack Interview 2025

 227
SELECT * FROM emp_path;

406. Detect cycles in an employee hierarchy

Answer:
WITH emp_cycle AS (

SELECT emp_id, manager_id,

CAST(emp_id AS VARCHAR(100)) AS path

FROM Employees

UNION ALL

SELECT e.emp_id, e.manager_id,

CONCAT([Link], ',', e.emp_id)

FROM Employees e

JOIN emp_cycle c

ON e.manager_id = c.emp_id

WHERE [Link] NOT LIKE '%' + CAST(e.emp_id AS VARCHAR) + '%'

SELECT * FROM emp_cycle;

407. Calculate factorial of a number using recursive CTE

Answer:
(Hard – logic based)

Factorial of 5

Answer

WITH fact AS (

SELECT 1 AS n, 1 AS result

UNION ALL

SELECT n + 1, result * (n + 1)
SQL Interview Questions Guide | © Crack Interview 2025
 228
FROM fact

WHERE n < 5

SELECT MAX(result) AS factorial FROM fact;

408. What is the difference between a Scalar Function and a Table-Valued Function?

Answer:

Scalar Function Table-Valued Function

Returns single value Returns a table

Can be used in SELECT Used in FROM clause

Slower for large data Better for performance

Example:

-- Scalar

SELECT [Link]('1998-01-01');

-- Table-Valued

SELECT * FROM [Link](10);

409. Write a Scalar Function to calculate age from DOB

Answer:

CREATE FUNCTION [Link] (@dob DATE)

RETURNS INT
AS
BEGIN
RETURN DATEDIFF(YEAR, @dob, GETDATE())
- CASE
WHEN DATEADD(YEAR, DATEDIFF(YEAR, @dob, GETDATE()), @dob) > GETDATE()
THEN 1 ELSE 0
END;
END;

SQL Interview Questions Guide | © Crack Interview 2025

 229
410. Why are scalar UDFs slow in SQL Server?

Answer:

• Executed row-by-row

• Cannot be parallelized

• Break query optimizer logic

• Act like hidden cursors

SQL Server 2019+ improves this with Scalar UDF Inlining, but only for eligible functions.

411. Create an Inline Table-Valued Function to return employees of a department

Answer:

Employees(emp_id, emp_name, dept_id, salary)

Answer

sql

Copy code

CREATE FUNCTION [Link] (@dept_id INT)

RETURNS TABLE

AS

RETURN

SELECT emp_id, emp_name, salary

FROM Employees

WHERE dept_id = @dept_id

);

SQL Interview Questions Guide | © Crack Interview 2025

 230
412. Difference between Inline TVF and Multi-Statement TVF

Answer:

Inline TVF Multi-Statement TVF

No BEGIN/END Uses table variable

Optimized like view Poor cardinality

Faster Slower

413. Create a Multi-Statement TVF to return employees with grade

Answer:

Logic

• Salary ≥ 80K → A

• Salary ≥ 50K → B

• Else → C

CREATE FUNCTION [Link] ()

RETURNS @result TABLE (

emp_id INT,

emp_name VARCHAR(50),

grade CHAR(1)

AS

BEGIN

INSERT INTO @result

SELECT emp_id, emp_name,

CASE

WHEN salary >= 80000 THEN 'A'

WHEN salary >= 50000 THEN 'B'

ELSE 'C'

SQL Interview Questions Guide | © Crack Interview 2025

 231
END

FROM Employees;

RETURN;

END;

414. Use a function inside a SELECT query

Answer:

SELECT emp_name,

[Link](dob) AS age

FROM Employees;

415. Can we modify data inside a SQL function?

Answer:

No

Functions:

• Cannot use INSERT, UPDATE, DELETE

• Cannot use TRY...CATCH

• Cannot call stored procedures

Only Stored Procedures can modify data.

SQL Interview Questions Guide | © Crack Interview 2025

 232
416. Create a function to split a comma-separated string

Answer:

Input

'A,B,C'

Answer (SQL Server)

CREATE FUNCTION [Link] (@str VARCHAR(MAX))

RETURNS TABLE

AS

RETURN

SELECT value

FROM STRING_SPLIT(@str, ',')

);

Usage:

SELECT * FROM [Link]('A,B,C');

417. What is a VIEW in SQL?

Answer:

A VIEW is a virtual table created from a SELECT query.

It does not store data (except indexed/materialized views).

CREATE VIEW vw_active_employees AS

SELECT emp_id, emp_name, dept_id

FROM Employees

WHERE is_active = 1;

Explanation

• View stores query definition, not data

• Data is fetched from base tables at runtime

SQL Interview Questions Guide | © Crack Interview 2025

 233
• Used for:

o Security

o Simplifying complex queries

o Reusability

• Changes in base table data reflect automatically

418. Can we INSERT, UPDATE, DELETE data through a VIEW?

Answer:
Yes, but only if the view is updatable

Conditions for Updatable View:

• Single base table

• No GROUP BY, DISTINCT, JOIN

• No aggregate functions

• No computed columns

UPDATE vw_active_employees

SET dept_id = 20

WHERE emp_id = 101;

Explanation

• SQL rewrites the operation to base table

• If view logic becomes complex → not updatable

• Interviewers check if you know limitations

SQL Interview Questions Guide | © Crack Interview 2025

 234
419. What is the difference between VIEW and TABLE?

Answer:

View Table

Virtual Physical

No data storage Stores data

Faster to create Takes space

Depends on base tables Independent

Explanation

• View = saved query

• Table = actual data

• Performance depends on query complexity & indexing on base tables

420. What is a MATERIALIZED VIEW?

Answer:

A Materialized View stores the query result physically and refreshes it periodically.

-- Oracle / PostgreSQL

CREATE MATERIALIZED VIEW mv_sales_summary

AS

SELECT product_id, SUM(amount) AS total_sales

FROM Sales

GROUP BY product_id;

Explanation

• Unlike normal views, data is stored

• Improves performance for heavy aggregation

• Requires refresh strategy

• Used in data warehousing & reporting

SQL Interview Questions Guide | © Crack Interview 2025

 235

421. Difference between VIEW and MATERIALIZED VIEW

Answer:

View Materialized View

Virtual Physical
Always fresh data May be stale
No storage Uses storage
Slower for aggregates Very fast
Explanation

• Views recompute every time

• Materialized views trade freshness for speed

• Ideal for dashboards & BI tools (Power BI, Tableau)

422. What is an Indexed View in SQL Server?

Answer:

An Indexed View is SQL Server’s version of a materialized view.

CREATE VIEW dbo.vw_sales_summary

WITH SCHEMABINDING

AS

SELECT product_id, COUNT_BIG(*) AS cnt, SUM(amount) AS total

FROM [Link]

GROUP BY product_id;

CREATE UNIQUE CLUSTERED INDEX idx_vw

ON dbo.vw_sales_summary(product_id);

Explanation

• Data is stored physically

• Automatically updated on base table changes

• SCHEMABINDING ensures table structure doesn’t change

• Used for high-performance aggregations

SQL Interview Questions Guide | © Crack Interview 2025

 236
423. Why is SCHEMABINDING mandatory for Indexed Views?

Answer:

Because SQL Server must ensure:

• Base tables don’t change

• Indexed view remains valid

Explanation

• Prevents DROP / ALTER of underlying tables

• Guarantees index consistency

• Mandatory for performance & data integrity

424. When should you NOT use a View?

Answer:

Avoid views when:

• View logic is too complex

• Used repeatedly inside loops

• Heavy nested views

• Performance-critical OLTP queries

Explanation

• Views don’t cache results

• Nested views = nested execution plans

• Can hide bad query design

• Use CTEs or temp tables instead

SQL Interview Questions Guide | © Crack Interview 2025

 237
425. How do Materialized Views get refreshed?

Answer:

Types of refresh:

• Complete Refresh – full rebuild

• Fast Refresh – incremental

• On Demand

• Scheduled (Daily / Hourly)

REFRESH MATERIALIZED VIEW mv_sales_summary;

Explanation

• Fast refresh requires logs (Oracle)

• Scheduling balances performance & freshness

• Critical in reporting systems

426. View vs CTE vs Materialized View – When to use what?

Answer:

Use Case Best Option

Reusable logic View

One-time query CTE

Heavy aggregation Materialized View

Performance critical Indexed View

Readability CTE

SQL Interview Questions Guide | © Crack Interview 2025

 238
427. What is a Stored Procedure?

Answer:

A Stored Procedure (SP) is a precompiled set of SQL statements stored in the database and executed as a
single unit.

CREATE PROCEDURE usp_GetEmployees

AS

BEGIN

SELECT * FROM Employees;

END;

Explanation

• Stored in DB server → reusable

• Improves performance due to execution plan reuse

• Reduces network traffic

• Centralizes business logic

428. Difference between Stored Procedure and Function

Answer:

Stored Procedure Function

Can return multiple result sets Returns single value / table

Supports DML (INSERT/UPDATE) ❌ DML not allowed

Can use TRY…CATCH ❌

Called using EXEC Used in SELECT

Explanation

• SP = business logic

• Function = calculation / reusable expression

• Interviewers expect clarity here

SQL Interview Questions Guide | © Crack Interview 2025

 239
429. How do you pass parameters to a Stored Procedure?

Answer:
CREATE PROCEDURE usp_GetEmpByDept

@dept_id INT

AS

BEGIN

SELECT * FROM Employees

WHERE dept_id = @dept_id;

END;

EXEC usp_GetEmpByDept @dept_id = 10;

Explanation

Parameters make SP reusable

Prevents hardcoding

Helps in security (avoids SQL injection)

430. What is the difference between EXEC and sp_executesql?

Answer:

EXEC sp_executesql

No parameterization Supports parameters

Poor plan reuse Better plan reuse

SQL injection risk Safer

EXEC sp_executesql

N'SELECT * FROM Employees WHERE dept_id = @d',

N'@d INT',

@d = 10;

Explanation

• sp_executesql enables query plan reuse

SQL Interview Questions Guide | © Crack Interview 2025

 240
• Safer for dynamic SQL

• Interviewers love this question

431. How do Stored Procedures improve performance?

Answer:

Stored procedures improve performance by:

• Execution plan caching

• Reduced network traffic

• Optimized execution path

• Less parsing & compilation

Explanation

• First execution → plan compiled

• Subsequent executions reuse plan

• Major benefit in high-volume systems

432. What is TRY…CATCH in Stored Procedures?

Answer:

Used for handling runtime errors.

BEGIN TRY

INSERT INTO Orders VALUES (1, 'Test');

END TRY

BEGIN CATCH

SELECT ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

Explanation

• Prevents abrupt failures

• Enables logging & rollback

• Functions cannot use TRY…CATCH

SQL Interview Questions Guide | © Crack Interview 2025

 241

433. What is the difference between RETURN and OUTPUT parameter?

Answer:

RETURN OUTPUT
Returns INT only Any datatype
Used for status codes Business values
One value only Multiple possible
Explanation

• RETURN → success/failure

• OUTPUT → actual data

• Good design uses both

434. When should you use Stored Procedures?

Answer:

Use SPs when:

• Business logic is complex

• Multiple DML operations required

• Security is important

• Performance matters

• Transactions are involved

Explanation

• SPs enforce controlled access

• Best practice in enterprise databases

• Preferred over ad-hoc queries

SQL Interview Questions Guide | © Crack Interview 2025

 242
435. Find the 2nd highest salary in each department (without window functions)

Answer:
Technique: CTE + Subquery

WITH dept_salary AS (

SELECT DISTINCT dept_id, salary

FROM Employees

SELECT d1.dept_id, MAX([Link]) AS second_highest

FROM dept_salary d1

WHERE [Link] < (

SELECT MAX([Link])

FROM dept_salary d2

WHERE d2.dept_id = d1.dept_id

GROUP BY d1.dept_id;

Explanation

• CTE removes duplicates

• Subquery finds max salary per dept

• Outer query finds max salary below the highest

• Works without window functions

436. Find employees earning more than department average

Answer:

Technique: CTE

WITH dept_avg AS (

SELECT dept_id, AVG(salary) AS avg_salary

FROM Employees

GROUP BY dept_id

SQL Interview Questions Guide | © Crack Interview 2025

 243
)

SELECT e.emp_id, [Link]

FROM Employees e

JOIN dept_avg d

ON e.dept_id = d.dept_id

WHERE [Link] > d.avg_salary;

Explanation

• Aggregate first (CTE)

• Compare row-level data later

• Cleaner & faster than correlated subquery

437. Remove duplicate rows using CTE

Answer:

Technique: CTE + ROW_NUMBER

WITH cte AS (

SELECT *,

ROW_NUMBER() OVER (

PARTITION BY emp_id

ORDER BY emp_id

) AS rn

FROM Employees

DELETE FROM cte

WHERE rn > 1;

Explanation

• Assign row numbers per duplicate group

• Keep first, delete rest

• CTE allows delete on derived result

SQL Interview Questions Guide | © Crack Interview 2025

 244

438. Find continuous date ranges (gap detection)

Answer:

Technique: CTE

WITH cte AS (

SELECT visit_date,

DATEADD(day,

-ROW_NUMBER() OVER (ORDER BY visit_date),

visit_date

) AS grp

FROM Visits

SELECT MIN(visit_date) AS start_date,

MAX(visit_date) AS end_date

FROM cte

GROUP BY grp;

Explanation

• ROW_NUMBER creates sequence

• Subtracting creates constant group for continuous dates

• Classic hard interview question

SQL Interview Questions Guide | © Crack Interview 2025

 245

439. Generate numbers from 1 to 100

Answer:

Technique: Recursive CTE

WITH numbers AS (

SELECT 1 AS n

UNION ALL

SELECT n + 1

FROM numbers

WHERE n < 100

SELECT * FROM numbers

OPTION (MAXRECURSION 100);

Explanation

• Anchor = 1

• Recursive part increments

• MAXRECURSION prevents infinite loop

440. Identify top 3 earners per department (no TOP, no window)

Answer:

Technique: CTE + correlated subquery

WITH cte AS (

SELECT DISTINCT dept_id, salary

FROM Employees

SELECT e.*

FROM Employees e

WHERE 3 > (

SQL Interview Questions Guide | © Crack Interview 2025

 246
SELECT COUNT(DISTINCT [Link])

FROM cte c

WHERE c.dept_id = e.dept_id

AND [Link] > [Link]

);

Explanation

• Counts salaries greater than current

• If fewer than 3 → top 3

• Pure logic, no window functions

441. Find employees earning more than their manager

Answer:

Technique: Self Join

SELECT e.emp_name

FROM Employees e

JOIN Employees m

ON e.manager_id = m.emp_id

WHERE [Link] > [Link];

Explanation

• Same table joined twice

• One as employee, one as manager

• Very common interview question

SQL Interview Questions Guide | © Crack Interview 2025

 247

442. Find pairs of employees from same department

Answer:

Technique: Self Join

SELECT e1.emp_name, e2.emp_name

FROM Employees e1

JOIN Employees e2

ON e1.dept_id = e2.dept_id

AND e1.emp_id < e2.emp_id;

Explanation

• < avoids duplicate & self-pair

• Used for combinations

• Tests understanding of join conditions

443. Detect duplicate records

Answer:

Technique: Self Join

SELECT e1.*

FROM Employees e1

JOIN Employees e2

ON e1.emp_id = e2.emp_id

AND [Link] <> [Link];

Explanation

• Same primary key, different physical rows

• Works when no unique constraint

• RowID is DB-specific

SQL Interview Questions Guide | © Crack Interview 2025

 248

444. Find overlapping date ranges

Answer:

Technique: Self Join

SELECT [Link], [Link]

FROM Events a

JOIN Events b

ON [Link] < [Link]

AND a.start_date <= b.end_date

AND a.end_date >= b.start_date;

Explanation

• Checks overlap logic

• Used in booking systems

• Very tricky but important

445. Find customers who made repeat purchases

Answer:

Technique: Self Join

SELECT DISTINCT o1.customer_id

FROM Orders o1

JOIN Orders o2

ON o1.customer_id = o2.customer_id

AND o1.order_id <> o2.order_id;

Explanation

• Same customer, different orders

• Self join reveals repetition

• Alternative to GROUP BY HAVING

SQL Interview Questions Guide | © Crack Interview 2025

 249

446. Find consecutive login days

Answer:

Technique: Self Join

SELECT a.user_id

FROM Logins a

JOIN Logins b

ON a.user_id = b.user_id

AND DATEADD(day, 1, a.login_date) = b.login_date;

Explanation

• Matches today with next day

• Used in streak detection

• Common product analytics question

447. Find employees earning above company average

Answer:

Technique: Subquery

SELECT *

FROM Employees

WHERE salary > (

SELECT AVG(salary)

FROM Employees

);

Explanation

• Scalar subquery

• Executes once

• Simple but frequently asked

SQL Interview Questions Guide | © Crack Interview 2025

 250

448. Find department with highest total salary

Answer:

Technique: Subquery

SELECT dept_id

FROM Employees

GROUP BY dept_id

HAVING SUM(salary) = (

SELECT MAX(total_salary)

FROM (

SELECT SUM(salary) AS total_salary

FROM Employees

GROUP BY dept_id

)t

);

Explanation

• Nested subqueries

• Required when aggregates depend on aggregates

• Shows depth of understanding

SQL Interview Questions Guide | © Crack Interview 2025

 251

449. Find employees who never placed an order

Answer:

Technique: Subquery (NOT EXISTS)

SELECT *

FROM Customers c

WHERE NOT EXISTS (

SELECT 1

FROM Orders o

WHERE o.customer_id = c.customer_id

);

Explanation

• NOT EXISTS handles NULL safely

• Preferred over NOT IN

• Interviewers look for this

450. Find 3rd highest salary

Answer:

Technique: Subquery

SELECT MAX(salary)

FROM Employees

WHERE salary < (

SELECT MAX(salary)

FROM Employees

WHERE salary < (

SELECT MAX(salary)

FROM Employees

));

SQL Interview Questions Guide | © Crack Interview 2025

 252

Explanation

• Nested logic

• No window functions

• Tests pure SQL thinking

451. Correlated subquery vs join example

Answer:

Technique: Correlated subquery

SELECT *

FROM Employees e

WHERE salary > (

SELECT AVG(salary)

FROM Employees

WHERE dept_id = e.dept_id

);

Explanation

• Subquery runs per row

• Flexible but slower

• Join/CTE often better

452. Find latest record per group

Answer:

Technique: Subquery

SELECT *

FROM Orders o

WHERE order_date = (

SELECT MAX(order_date)

SQL Interview Questions Guide | © Crack Interview 2025

 253
FROM Orders

WHERE customer_id = o.customer_id

);

Explanation

• Correlated subquery

• One record per group

• Frequently asked in analytics roles

453. Delete duplicate rows using subquery

Answer:

Technique: Subquery

DELETE FROM Employees

WHERE emp_id NOT IN (

SELECT MIN(emp_id)

FROM Employees

GROUP BY email

);

Explanation

• Keeps one row per email

• Deletes rest

• Must ensure NULL safety

SQL Interview Questions Guide | © Crack Interview 2025

 254

454. What are ACID properties and why are they important?

Answer:

ACID ensures reliable transactions in a database.

Property Meaning
Atomicity All or nothing
Consistency Valid state
Isolation No interference
Durability Permanent commit
Explanation

• Prevents partial updates

• Ensures data accuracy

• Critical for financial & enterprise systems

455. Explain Atomicity with an example

Answer:

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 100 WHERE acc_id = 1;

UPDATE Accounts SET balance = balance + 100 WHERE acc_id = 2;

COMMIT;

Explanation

Either both updates succeed or both fail

If second query fails → rollback

No half-completed transaction

SQL Interview Questions Guide | © Crack Interview 2025

 255
456. What happens if Consistency is violated?

Answer:

Answer

Transaction is rolled back.

Explanation

• Constraints (PK, FK, CHECK) enforce consistency

• Example:

INSERT INTO Orders (order_id, customer_id)

VALUES (1, 999); -- customer_id does not exist

• Violates FK → rollback

• Database never enters invalid state

457. Explain Isolation levels and dirty read

Answer:

Isolation Level Dirty Read

READ UNCOMMITTED ✅

READ COMMITTED ❌

REPEATABLE READ ❌

SERIALIZABLE ❌

Explanation

• Dirty read = reading uncommitted data

• Higher isolation = safer but slower

• Trade-off between performance & accuracy

SQL Interview Questions Guide | © Crack Interview 2025

 256
458. What is Durability and how is it ensured?

Answer:

Once committed, data survives crashes.

Explanation

• Achieved using:

o Transaction logs

o Write-ahead logging (WAL)

o Disk persistence

• Even power failure won’t lose committed data

459. ACID vs BASE

Answer:

ACID BASE
Strong consistency Eventual consistency
Transaction safety High availability
Slower Faster
Explanation

• ACID → banking systems

• BASE → large distributed systems (e.g., caching)

SQL Interview Questions Guide | © Crack Interview 2025

 257
460. What is a Bridge (Junction) Table?

Answer:

Used to resolve many-to-many relationships.

Students ↔ Courses

Student_Course (

student_id,

course_id

Explanation

• Avoids data duplication

• Maintains normalization

• Common in data modeling interviews

461. Why can’t many-to-many be stored directly?

Answer:

Explanation

• Violates 1NF

• Repeating groups

• Poor scalability

• Bridge table ensures flexibility

SQL Interview Questions Guide | © Crack Interview 2025

 258
462. Convert this table in the desired output

Gender
M
F
M
F
F

Output:-

Male Female
2 3

Answer:

SELECT SUM(Gender= “M”) as Male,

SUM(Gender= “F”) as Female From Table_name;

Or

SELECT

SUM(CASE WHEN Gender = “M” THEN 1 END ) AS Male,

SUM(CASE WHEN Gender = “F” THEN 1 END ) AS Female

FROM table_name

463. Find names starting with ‘A’ and ending with ‘na’

Answer:

SELECT *

FROM Employees

WHERE name LIKE 'A%na';

Explanation

% matches any length in between

464. Case-insensitive wildcard search

Answer:

SQL Interview Questions Guide | © Crack Interview 2025

 259
SELECT *

FROM Employees

WHERE LOWER(name) LIKE 'a%';

Explanation

Useful in case-sensitive DBs

But may impact performance

465. Find records containing special characters

Answer:

SELECT *

FROM Employees

WHERE name LIKE '%[%]%';

Explanation

Square brackets escape characters

Used to detect special symbols

Very tricky interview question

466. What is TCL in SQL?

Answer:

TCL (Transaction Control Language) manages transactions in a database.

Common TCL commands:

• BEGIN / START TRANSACTION

• COMMIT

SQL Interview Questions Guide | © Crack Interview 2025

 260
• ROLLBACK

• SAVEPOINT

Explanation

• TCL ensures ACID properties

• Controls when data changes become permanent

• Mainly used with INSERT, UPDATE, DELETE

467. Difference between COMMIT and ROLLBACK

Answer:

COMMIT ROLLBACK

Saves changes permanently Undoes changes

Ends transaction Ends transaction

Cannot be reversed Reverts to last commit

Explanation

• COMMIT = “save”

• ROLLBACK = “undo”

• Both close the transaction

468. What happens after COMMIT?

Answer:

• Data becomes permanent

• Cannot be rolled back

• Locks are released

Explanation
SQL Interview Questions Guide | © Crack Interview 2025
 261
• Ensures Durability

• Transaction log records are finalized

• Very common interview trap question

469. What is implicit vs explicit transaction?

Answer:

Explicit Implicit
User controls COMMIT Auto-commit
BEGIN TRANSACTION Each statement commits
Explanation

• Explicit → enterprise apps

• Implicit → default behavior in many tools

470. What is a SAVEPOINT?

Answer:

A SAVEPOINT allows partial rollback within a transaction.

BEGIN TRANSACTION;

INSERT INTO Orders VALUES (1);

SAVEPOINT sp1;

INSERT INTO Orders VALUES (2);

ROLLBACK TO sp1;

COMMIT;

Explanation

SQL Interview Questions Guide | © Crack Interview 2025

 262
• Only second insert is undone

• First insert remains

• Useful in complex transactions

471. What is GRANT in SQL?

Answer:

GRANT provides permissions to users or roles.

GRANT SELECT, INSERT

ON Employees

TO user1;

Explanation

• Controls data access

• Improves security

• Used heavily in enterprise DBs

472. What is REVOKE?

Answer:

REVOKE removes granted permissions.

REVOKE INSERT

ON Employees

FROM user1;

Explanation

SQL Interview Questions Guide | © Crack Interview 2025

 263
• Security enforcement

• Removes privileges explicitly

473. Difference between GRANT and REVOKE

Answer:

GRANT REVOKE

Gives access Removes access

Security enable Security restrict

474. What is WITH GRANT OPTION?

Answer:

GRANT SELECT

ON Employees

TO user1

WITH GRANT OPTION;

Explanation

User1 can grant SELECT to others

Hierarchical permission system

Very common interview question

475. What is PIVOT in SQL and why is it used?

Answer:

PIVOT converts row-level data into columns (row-to-column transformation).

Example input:

month sales
SQL Interview Questions Guide | © Crack Interview 2025
 264
Jan 100
Feb 200
Output:

Jan Feb

100 200

Explanation

• Improves readability

• Used heavily in reports & dashboards

• Alternative to multiple CASE WHEN expressions

476. Basic PIVOT example – monthly sales

Answer:

SELECT *

FROM (

SELECT month, amount

FROM Sales

) src

PIVOT (

SUM(amount)

FOR month IN ([Jan], [Feb], [Mar])

) p;

Explanation

Inner query = source dataset

SUM(amount) = aggregation

IN list defines static columns

Output contains one column per month

SQL Interview Questions Guide | © Crack Interview 2025

 265

477. Why is aggregation mandatory in PIVOT?

Answer:

Because multiple rows may map to the same pivot column.

Explanation

• SQL must know how to combine values

• Common aggregations:

o SUM

o COUNT

o AVG

• Without aggregation → ambiguity

478. PIVOT vs CASE WHEN – which is better?

Answer:

PIVOT CASE WHEN

Cleaner syntax More flexible
Static columns Dynamic logic
Readable for reports Better for dynamic needs
Explanation

• PIVOT is syntactic sugar

• CASE WHEN works in all DBs

SQL Interview Questions Guide | © Crack Interview 2025
 266
• Interviewers test conceptual clarity

479. Pivot sales by year and month

Answer:

SELECT year, [Jan], [Feb], [Mar]

FROM (

SELECT year, month, amount

FROM Sales

)s

PIVOT (

SUM(amount)

FOR month IN ([Jan], [Feb], [Mar])

) p;

Explanation

year remains a row identifier

Pivot applies only to month

Common BI reporting pattern

480. Handle missing values in PIVOT

Answer:

SELECT year,

ISNULL([Jan], 0) AS Jan,

ISNULL([Feb], 0) AS Feb

FROM (

SELECT year, month, amount

FROM Sales

)s

PIVOT (

SQL Interview Questions Guide | © Crack Interview 2025

 267
SUM(amount)

FOR month IN ([Jan], [Feb])

) p;

Explanation

Missing data becomes NULL

ISNULL / COALESCE ensures clean reporting

Important for dashboards

481. Pivot with COUNT instead of SUM

Answer:

SELECT *

FROM (

SELECT dept, emp_id

FROM Employees

)s

PIVOT (

COUNT(emp_id)

FOR dept IN ([IT], [HR], [Finance])

) p;

Explanation

Aggregation can be COUNT

Useful for headcount analysis

Shows flexibility of PIVOT

482. What is UNPIVOT and when is it used?

Answer:

SQL Interview Questions Guide | © Crack Interview 2025

 268
UNPIVOT converts columns into rows (column-to-row).

Input:

Jan Feb
100 200
Output:

month sales
Jan 100
Feb 200
Explanation

• Normalizes denormalized data

• Required before analytics

• Often used before joins & aggregations

483. Basic UNPIVOT example

Answer:

SELECT month, sales

FROM SalesSummary

UNPIVOT (

sales FOR month IN (Jan, Feb, Mar)

) u;

Explanation

Converts columns into row values

Column names become data values

Essential for BI transformations

484. Find customers who placed orders but never received delivery

Answer:

Tables

• Customers(customer_id, name)

SQL Interview Questions Guide | © Crack Interview 2025

 269
• Orders(order_id, customer_id)

• Deliveries(order_id, delivered_date)

Query

SELECT DISTINCT c.customer_id, [Link]

FROM Customers c

JOIN Orders o

ON c.customer_id = o.customer_id

LEFT JOIN Deliveries d

ON o.order_id = d.order_id

WHERE d.delivered_date IS NULL;

Explanation

• INNER JOIN ensures customer has orders

• LEFT JOIN checks delivery status

• IS NULL filters undelivered orders

• Tests understanding of join order + NULL filtering

485. Find employees earning more than department average

Answer:

Tables

• Employees(emp_id, dept_id, salary)

Query

SELECT e.emp_id, [Link]

FROM Employees e

JOIN (

SELECT dept_id, AVG(salary) AS avg_salary

FROM Employees

GROUP BY dept_id

)d

SQL Interview Questions Guide | © Crack Interview 2025

 270
ON e.dept_id = d.dept_id

WHERE [Link] > d.avg_salary;

Explanation

• Subquery calculates department averages

• Join compares row-level salary

• Cleaner and faster than correlated subquery

• Very common interview problem

486. Find records present in Table A but missing in Table B

Answer:

Tables

• TableA(id)

• TableB(id)

Query

SELECT [Link]

FROM TableA a

LEFT JOIN TableB b

ON [Link] = [Link]

WHERE [Link] IS NULL;

Explanation

• LEFT JOIN keeps all rows from A

• NULL indicates missing match in B

• Called ANTI JOIN

• Preferred over NOT IN (NULL-safe)

487. Find overlapping time intervals

Answer:
SQL Interview Questions Guide | © Crack Interview 2025
 271
Table

• Meetings(id, start_time, end_time)

Query

SELECT [Link], [Link]

FROM Meetings m1

JOIN Meetings m2

ON [Link] < [Link]

AND m1.start_time < m2.end_time

AND m1.end_time > m2.start_time;

Explanation

• Self join compares each meeting with others

• < avoids duplicate pairs

• Time overlap logic is key

• Frequently asked in scheduling systems

488. Find managers who have no subordinates

Answer:

Table

• Employees(emp_id, manager_id)

Query

SELECT e.emp_id

FROM Employees e

LEFT JOIN Employees s

ON e.emp_id = s.manager_id

WHERE s.emp_id IS NULL;

Explanation

• Self LEFT JOIN checks subordinate presence

• NULL means no one reports to them

SQL Interview Questions Guide | © Crack Interview 2025

 272
• Tests understanding of hierarchical joins

489. Find customers who purchased products but never returned them

Answer:

Tables

• Customers(customer_id, name)

• Orders(order_id, customer_id)

• Returns(order_id)

Query

SELECT DISTINCT c.customer_id, [Link]

FROM Customers c

JOIN Orders o

ON c.customer_id = o.customer_id

LEFT JOIN Returns r

ON o.order_id = r.order_id

WHERE r.order_id IS NULL;

Explanation

• Customers → Orders (INNER JOIN)

• Orders → Returns (LEFT JOIN)

• NULL means no return

• Demonstrates multi-table join flow

490. Does Snowflake, Redshift have integrity constraints ?

Answer:

In short, Snowflake and Amazon Redshift support primary key, unique, and not null constraints. However, they
generally do not enforce foreign key constraints or check constraints commonly found in traditional relational

SQL Interview Questions Guide | © Crack Interview 2025

 273
databases like PostgreSQL or MySQL. This is due to their focus on optimizing performance and scalability for
analytics workloads rather than strict transactional consistency.

491. what is self-referential integrity constraint ?

Answer:

A self-referential integrity constraint, also known as a self-referencing foreign key or recursive relationship,
occurs when a table in a relational database contains a foreign key that references its own primary key. This
type of constraint is used to establish relationships within the same table, typically representing hierarchical or
recursive structures.

example :- emp and manager id.

492. Write query on Employees table in SQL_PRACTICE_SCHEMA in snowflake that will

give records whose salary is greater than 15000 and department_id is not 90.

Answer:

SELECT * FROM SQL_PRACTICE_SCHEMA.Employees WHERE Salary > 150000 AND department_id != 90

493. Delete duplicate rows but keep only one

Answer:

WITH cte AS (

SELECT *,

ROW_NUMBER() OVER (

PARTITION BY email

ORDER BY emp_id

) AS rn

FROM Employees

DELETE FROM cte

WHERE rn > 1;

Explanation

ROW_NUMBER assigns unique order per email

SQL Interview Questions Guide | © Crack Interview 2025
 274
First row kept (rn = 1)

Others deleted

CTE allows delete on derived result

494. Query returns rows even when WHERE condition seems false — WHY?

Answer:

SELECT *

FROM Employees

WHERE salary NOT IN (NULL);

Expected by beginners:

All rows returned

Actual result:

NO ROWS returned

Deep Explanation

NOT IN (NULL) → comparison becomes UNKNOWN

SQL uses 3-valued logic: TRUE / FALSE / UNKNOWN

WHERE filters only TRUE

UNKNOWN rows are eliminated

Golden Rule:

Never use NOT IN when NULLs are possible

Use NOT EXISTS

495. Query gives different results on different databases — WHY?

Answer:
SQL Interview Questions Guide | © Crack Interview 2025
 275
SELECT dept_id, emp_name

FROM Employees

GROUP BY dept_id;

Explanation

SQL Standard (invalid)

MySQL (non-strict mode) works

PostgreSQL / Oracle error

Why? emp_name is not aggregated

DB returns arbitrary value per group

This tests SQL standard vs vendor behavior

496. Why does this recursive CTE go into infinite loop?

Answer:

WITH RECURSIVE emp_cte AS (

SELECT emp_id, manager_id

FROM Employees

WHERE emp_id = 1

UNION ALL

SELECT e.emp_id, e.manager_id

FROM Employees e

JOIN emp_cte c

ON e.manager_id = c.emp_id

SELECT * FROM emp_cte;

Explanation

Circular hierarchy (employee manages themselves indirectly)

No termination condition

SQL engine keeps expanding

SQL Interview Questions Guide | © Crack Interview 2025
 276

Fix:

WHERE e.emp_id <> c.emp_id

or add depth column + limit

497. Why this subquery is slower than JOIN?

Answer:

SELECT *

FROM Employees e

WHERE dept_id IN (

SELECT dept_id

FROM Departments

WHERE location = 'Delhi'

);

Explanation

IN subquery may execute repeatedly

JOIN allows optimizer to:

• reorder
• use indexes better

Faster:

SELECT e.*

FROM Employees e

JOIN Departments d

ON e.dept_id = d.dept_id

WHERE [Link] = 'Delhi';

SQL Interview Questions Guide | © Crack Interview 2025

 277

498. Why is NOT IN dangerous when NULLs exist in subqueries?

Answer:

NOT IN compares each value against every value returned by the subquery.
If even a single NULL appears in the subquery result, the comparison becomes UNKNOWN due to SQL’s three-
valued logic.
Since WHERE filters only TRUE conditions, all rows get rejected.
This behavior is logical but unintuitive and often causes silent bugs.
NOT EXISTS is safer because it checks row existence instead of value comparison.

499. Why does using functions on indexed columns hurt performance?

Answer:

Indexes store raw column values in sorted order.

When a function is applied, the database must compute the function for every row.
This prevents the optimizer from using the index efficiently.
Such conditions are called non-sargable.
Rewriting the condition using range predicates restores index usage.

500. Why does SERIALIZABLE isolation still allow deadlocks?

Answer:

SERIALIZABLE ensures logical correctness, not deadlock prevention.

Transactions may lock resources in different orders.
This creates circular wait conditions.
The database resolves deadlocks by aborting one transaction.
Isolation level does not control lock acquisition order.

SQL Interview Questions Guide | © Crack Interview 2025

 278

501. Why does adding an index sometimes slow down SELECT queries?

Answer:

Indexes increase write overhead and storage cost.

If the index has low selectivity, scanning it costs more than table scan.
Extra indexes increase optimizer decision complexity.
They can cause suboptimal plan selection.
Indexes must be justified by query patterns, not assumptions.

502. Why does LIMIT without ORDER BY return different results each time?

Answer:

Relational tables represent unordered sets.

Without ORDER BY, the database may return rows in any physical order.
Execution plans, parallelism, and storage layout affect row order.
Repeated executions may differ.
Deterministic results require explicit ordering.

503. Why does EXISTS scale better than COUNT(*) > 0?

Answer:

EXISTS stops evaluating as soon as a match is found.

COUNT(*) must scan all qualifying rows.
On large datasets, this difference is significant.
Optimizers treat EXISTS as a semi-join.
COUNT-based existence checks waste unnecessary computation

504. Why does ROW_NUMBER() not guarantee deterministic results without ORDER BY?

Answer:

ROW_NUMBER assigns sequence based on processing order.

Without ORDER BY, row order is undefined.
Physical storage or execution plan determines order.
Results may change between executions.
ORDER BY is mandatory for deterministic ranking.

SQL Interview Questions Guide | © Crack Interview 2025

 279

505. Find records where same value appears consecutively 3 times

Answer:

SELECT DISTINCT status

FROM (
SELECT status,
LAG(status,1) OVER() AS prev1,
LAG(status,2) OVER() AS prev2
FROM logs
)t
WHERE status = prev1
AND status = prev2;

506. Prevent Negative Salary (BEFORE INSERT)

Answer:

CREATE TRIGGER check_salary

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

IF [Link] < 0 THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'Salary cannot be negative';

END IF;

END;

507. Auto Update Stock (AFTER INSERT)

Answer:

CREATE TRIGGER update_stock

AFTER INSERT ON orders

FOR EACH ROW

BEGIN

SQL Interview Questions Guide | © Crack Interview 2025

 280
UPDATE products

SET quantity = quantity - [Link]

WHERE product_id = NEW.product_id;

END;

508. Difference: Trigger vs Stored Procedure?

Answer:

Trigger Stored Procedure

Auto execution Manually called

Event-based Parameter-based

No input params Supports params

Cannot return values Can return values

SQL Interview Questions Guide | © Crack Interview 2025