SQL Notes

Hub Advantages and Disadvantages of SQL Features of SQL History of SQL SQL vs NoSQL What is SQL?SQL Comments SQL Data Types SQL Keywords SQL Operators SQL Syntax CHECK Constraint in SQL Composite Keys in SQL DEFAULT Constraint in SQL FOREIGN KEY Constraint in SQL NOT NULL Constraint in SQL PRIMARY KEY Constraint in SQL SQL Constraints UNIQUE Key Constraint in SQL DELETE Data INSERT Data SELECT Data SQL Aliases UPDATE Data BETWEEN Operator DISTINCT Keyword GROUP BY Clause HAVING Clause IN Operator LIKE Operator LIMIT Clause ORDER BY Clause TOP Clause WHERE Clause Wildcard Characters EXCEPT Operator in SQL INTERSECT Operator in SQL MINUS Operator in SQL UNION Operator in SQL UNION ALL Operator in SQL CROSS JOIN in SQL FULL OUTER JOIN INNER JOIN JOIN vs UNION LEFT JOIN RIGHT JOIN SELF JOIN SQL Joins Overview ANY and ALL Operators CASE Expression in SQL Correlated Subquery EXISTS Operator Nested Subqueries in SQL Scalar Subqueries Subquery Introduction Aggregate Functions AVG Function Conversion Functions COUNT Function Date Functions MIN and MAX Functions Numeric Functions String Functions SUM Function ALTER TABLE Create Table DROP TABLE Rename Table Temporary Table TRUNCATE TABLE Backup Database Create Database Drop Database Restore Database CREATE VIEW in SQL DROP VIEW in SQL Materialized Views in SQL Update and Modify Views in SQL Create Stored Procedure in SQL Execute Stored Procedures in SQL Parameters in Stored Procedures Stored Procedure vs Function in SQL Stored Procedure Introduction AFTER Trigger BEFORE Trigger INSTEAD OF Trigger Trigger Examples Trigger Introduction ACID Properties in SQL COMMIT in SQL Isolation Levels in SQL ROLLBACK in SQL SAVEPOINT in SQL Introduction to SQL Transactions Boyce-Codd Normal Form (BCNF)Database Normalization Denormalization First Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Clustered Index in SQL Index Performance Non-Clustered Index Introduction to SQL Indexes Unique Index SQL Execution Plans EXPLAIN Plan Indexing Strategies Partitioning Query Optimization SQL Performance Tips Common Table Expressions (CTE)Dynamic SQL Functions in SQL Materialized Views MERGE Statement PIVOT and UNPIVOT RANK(), DENSE_RANK(), and ROW_NUMBER()Recursive CTE Stored Procedures Triggers in SQL Views in SQL Window Functions SQL Challenges SQL Exercises (Advanced)SQL Exercises (Beginner)SQL Exercises (Intermediate)SQL Projects SQL Interview Questions (Advanced)SQL Interview Questions (Beginner)SQL Interview Questions (Intermediate)SQL Output-Based Interview Questions SQL Scenario-Based Interview Questions Create Your First Database Install MySQL on Your Computer Install PostgreSQL Install SQLite SQL Online Compilers SQL Roadmap

SQL Interview Questions (Intermediate)

Learn the most important intermediate-level SQL interview questions with detailed answers, examples, joins, subqueries, indexing, transactions, and optimization concepts.

Once you've crushed the basics, interviewers start asking about real problems:

text example

Practical SQL
Query Logic
Joins
Subqueries
Indexes
Transactions
Performance

These are the questions you'll get for:

text example

SQL Developer
Backend Developer
Database Developer
Software Engineer
Data Analyst

positions.

HAVING

Filters groups after grouping is done.

Example:

sql example

SELECT DepartmentID,
COUNT(*)

FROM Employees

WHERE Salary > 50000

GROUP BY DepartmentID

HAVING COUNT(*) > 5;

Q2. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN

Only gives you matches from both tables.

LEFT JOIN

Everything from the left table, plus matches from the right. No match? You still get the left data.

Example:

sql example

SELECT *

FROM Employees E

LEFT JOIN Departments D

ON E.DepartmentID =
   D.DepartmentID;

Q3. What is a Self Join?

A table joining itself.

Example:

sql example

SELECT

E.EmployeeName,
M.EmployeeName AS Manager

FROM Employees E

LEFT JOIN Employees M

ON E.ManagerID =
   M.EmployeeID;

Q4. What is a Cross Join?

Gives you every possible combination — Cartesian product.

Example:

sql example

SELECT *

FROM Employees

CROSS JOIN Departments;

Result:

Output

Every Employee × Every Department

Q5. What is a Correlated Subquery?

A subquery that references the outer query. They talk to each other.

Example:

sql example

SELECT *

FROM Employees E

WHERE Salary >
(
    SELECT AVG(Salary)

    FROM Employees

    WHERE DepartmentID =
          E.DepartmentID
);

Q6. Difference Between IN and EXISTS?

IN

Checks if a value matches items in a list.

EXISTS

Checks if any rows exist from a query.

On big datasets:

sql example

EXISTS

often runs faster.

Q7. What is a CTE?

CTE stands for:

Output

Common Table Expression

Think of it as a temporary named query you create for one query.

Example:

sql example

WITH HighSalary AS
(
    SELECT *
    FROM Employees
    WHERE Salary > 50000
)

SELECT *
FROM HighSalary;

Q8. What is a Recursive CTE?

A CTE that calls itself. Useful for:

Output

Hierarchies
Organization Charts
Folder Structures

Q9. What is the difference between DELETE and TRUNCATE?

Feature	DELETE	TRUNCATE
WHERE Clause	Yes	No
Row-by-Row	Yes	No
Speed	Slower	Faster
Logging	More	Less

Q10. What is the difference between UNION and UNION ALL?

UNION

Stacks results and removes any duplicates.

UNION ALL

Stacks results and keeps everything, even duplicates.

Example:

sql example

SELECT Name FROM A

UNION ALL

SELECT Name FROM B;

Q11. What is an Index?

An index speeds up data searches. Like a book's index — you don't read every page, you just look up what you need.

Example:

sql example

CREATE INDEX IX_Email

ON Employees(Email);

Q12. What is the difference between Clustered and Non-Clustered Index?

Feature	Clustered	Non-Clustered
Data Sorted	Yes	No
Count per Table	One	Multiple
Range Queries	Faster	Moderate

Q13. What is a Composite Index?

An index built on multiple columns at once.

Example:

sql example

CREATE INDEX IX_DepSalary

ON Employees
(
    DepartmentID,
    Salary
);

Q14. What is the Leftmost Prefix Rule?

For a composite index like:

sql example

(DepartmentID, Salary)

SQL can efficiently use:

sql example

WHERE DepartmentID = 1

but not always:

sql example

WHERE Salary > 50000

alone — order matters.

Q15. What is a Covering Index?

An index that has all the columns your query needs. The database doesn't have to look elsewhere.

Example:

sql example

CREATE INDEX IX_Covering

ON Employees
(
    DepartmentID,
    EmployeeID,
    EmployeeName
);

Q16. What is Normalization?

The process of organizing data to cut down redundancy and improve consistency.

Forms:

Output

1NF
2NF
3NF
BCNF

Q17. What is Denormalization?

The opposite — combining data in ways that break normal forms to get faster reads. It's a tradeoff.

Q18. What is a View?

A virtual table built from a query. It looks like a table but it's just a saved query.

Example:

sql example

CREATE VIEW ActiveEmployees AS

SELECT *

FROM Employees

WHERE IsActive = 1;

Q19. What is a Materialized View?

A view that actually stores the data physically, not just the query. Faster reads, but takes up space.

Pros:

Output

Faster Reads

Cons:

text example

Takes Storage

Q20. What is a Stored Procedure?

SQL code saved inside the database that you can call over and over.

Example:

sql example

CREATE PROCEDURE GetEmployees
AS

SELECT *
FROM Employees;

Q21. What is the difference between Procedure and Function?

Feature	Procedure	Function
Returns Value	Optional	Required
In SELECT	No	Yes
Complex Logic	Yes	Limited

Q22. What is a Trigger?

Code that fires automatically when something happens:

Output

INSERT
UPDATE
DELETE

Q23. What are ACID Properties?

The four guarantees for transactions:

text example

Atomicity
Consistency
Isolation
Durability

Q24. What is Transaction Isolation Level?

Controls how much one transaction can see from another while both are running.

Levels:

text example

Read Uncommitted
Read Committed
Repeatable Read
Serializable

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for SQL Interview Questions (Intermediate).

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, interview, questions, intermediate

Continue learning this concept

Interview QuestionsSQL Interview Questions (Advanced)Learn advanced SQL interview questions with detailed answers covering query optimization, execution plans, indexing, partitioning, transactions, window functions, and database architecture.Interview QuestionsSQL Interview Questions (Beginner)Learn the most important beginner-level SQL interview questions with answers, examples, and explanations commonly asked in technical interviews.Interview QuestionsSQL Output-Based Interview QuestionsLearn the most important SQL output-based interview questions with tables, queries, outputs, explanations, and interview tips.Interview QuestionsSQL Scenario-Based Interview QuestionsLearn the most important SQL scenario-based interview questions with practical solutions, query logic, optimization techniques, and real-world database cases.Advanced SQLTriggers in SQLLearn SQL Triggers in detail, understand automatic database actions, BEFORE and AFTER triggers, event-driven execution, auditing, validation, and real-world applications.PracticeSQL Exercises (Intermediate)Practice intermediate-level SQL exercises covering joins, subqueries, CTEs, window functions, transactions, indexes, and optimization.

PreviousSQL Interview Questions (Beginner)NextSQL Output-Based Interview Questions

SQL Notes

SQL Interview Questions (Intermediate)

Learn the most important intermediate-level SQL interview questions with detailed answers, examples, joins, subqueries, indexing, transactions, and optimization concepts.

Once you've crushed the basics, interviewers start asking about real problems:

text example

Practical SQL
Query Logic
Joins
Subqueries
Indexes
Transactions
Performance

These are the questions you'll get for:

text example

SQL Developer
Backend Developer
Database Developer
Software Engineer
Data Analyst

positions.

HAVING

Filters groups after grouping is done.

Example:

sql example

SELECT DepartmentID,
COUNT(*)

FROM Employees

WHERE Salary > 50000

GROUP BY DepartmentID

HAVING COUNT(*) > 5;

Q2. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN

Only gives you matches from both tables.

LEFT JOIN

Everything from the left table, plus matches from the right. No match? You still get the left data.

Example:

sql example

SELECT *

FROM Employees E

LEFT JOIN Departments D

ON E.DepartmentID =
   D.DepartmentID;

Q3. What is a Self Join?

A table joining itself.

Example:

sql example

SELECT

E.EmployeeName,
M.EmployeeName AS Manager

FROM Employees E

LEFT JOIN Employees M

ON E.ManagerID =
   M.EmployeeID;

Q4. What is a Cross Join?

Gives you every possible combination — Cartesian product.

Example:

sql example

SELECT *

FROM Employees

CROSS JOIN Departments;

Result:

Output

Every Employee × Every Department

Q5. What is a Correlated Subquery?

A subquery that references the outer query. They talk to each other.

Example:

sql example

SELECT *

FROM Employees E

WHERE Salary >
(
    SELECT AVG(Salary)

    FROM Employees

    WHERE DepartmentID =
          E.DepartmentID
);

Q6. Difference Between IN and EXISTS?

IN

Checks if a value matches items in a list.

EXISTS

Checks if any rows exist from a query.

On big datasets:

sql example

EXISTS

often runs faster.

Q7. What is a CTE?

CTE stands for:

Output

Common Table Expression

Think of it as a temporary named query you create for one query.

Example:

sql example

WITH HighSalary AS
(
    SELECT *
    FROM Employees
    WHERE Salary > 50000
)

SELECT *
FROM HighSalary;

Q8. What is a Recursive CTE?

A CTE that calls itself. Useful for:

Output

Hierarchies
Organization Charts
Folder Structures

Q9. What is the difference between DELETE and TRUNCATE?

Feature	DELETE	TRUNCATE
WHERE Clause	Yes	No
Row-by-Row	Yes	No
Speed	Slower	Faster
Logging	More	Less

Q10. What is the difference between UNION and UNION ALL?

UNION

Stacks results and removes any duplicates.

UNION ALL

Stacks results and keeps everything, even duplicates.

Example:

sql example

SELECT Name FROM A

UNION ALL

SELECT Name FROM B;

Q11. What is an Index?

An index speeds up data searches. Like a book's index — you don't read every page, you just look up what you need.

Example:

sql example

CREATE INDEX IX_Email

ON Employees(Email);

Q12. What is the difference between Clustered and Non-Clustered Index?

Feature	Clustered	Non-Clustered
Data Sorted	Yes	No
Count per Table	One	Multiple
Range Queries	Faster	Moderate

Q13. What is a Composite Index?

An index built on multiple columns at once.

Example:

sql example

CREATE INDEX IX_DepSalary

ON Employees
(
    DepartmentID,
    Salary
);

Q14. What is the Leftmost Prefix Rule?

For a composite index like:

sql example

(DepartmentID, Salary)

SQL can efficiently use:

sql example

WHERE DepartmentID = 1

but not always:

sql example

WHERE Salary > 50000

alone — order matters.

Q15. What is a Covering Index?

An index that has all the columns your query needs. The database doesn't have to look elsewhere.

Example:

sql example

CREATE INDEX IX_Covering

ON Employees
(
    DepartmentID,
    EmployeeID,
    EmployeeName
);

Q16. What is Normalization?

The process of organizing data to cut down redundancy and improve consistency.

Forms:

Output

1NF
2NF
3NF
BCNF

Q17. What is Denormalization?

The opposite — combining data in ways that break normal forms to get faster reads. It's a tradeoff.

Q18. What is a View?

A virtual table built from a query. It looks like a table but it's just a saved query.

Example:

sql example

CREATE VIEW ActiveEmployees AS

SELECT *

FROM Employees

WHERE IsActive = 1;

Q19. What is a Materialized View?

A view that actually stores the data physically, not just the query. Faster reads, but takes up space.

Pros:

Output

Faster Reads

Cons:

text example

Takes Storage

Q20. What is a Stored Procedure?

SQL code saved inside the database that you can call over and over.

Example:

sql example

CREATE PROCEDURE GetEmployees
AS

SELECT *
FROM Employees;

Q21. What is the difference between Procedure and Function?

Feature	Procedure	Function
Returns Value	Optional	Required
In SELECT	No	Yes
Complex Logic	Yes	Limited

Q22. What is a Trigger?

Code that fires automatically when something happens:

Output

INSERT
UPDATE
DELETE

Q23. What are ACID Properties?

The four guarantees for transactions:

text example

Atomicity
Consistency
Isolation
Durability

Q24. What is Transaction Isolation Level?

Controls how much one transaction can see from another while both are running.

Levels:

text example

Read Uncommitted
Read Committed
Repeatable Read
Serializable

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for SQL Interview Questions (Intermediate).

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, interview, questions, intermediate

Continue learning this concept

PreviousSQL Interview Questions (Beginner)NextSQL Output-Based Interview Questions