SQL Advanced Topics + Joins + Subqueries + Interview Preparation

Now that you know basic SQL (creating tables, inserting, updating, deleting),
it’s time to level up to Advanced SQL!

You will learn about:

• Joins (connecting multiple tables)
• Subqueries (queries inside queries)
• Indexes (faster searching)
• Views (virtual tables)
• Stored Procedures, Functions, and more.

Joins in SQL?

When you want to combine data from two or more tables based on a common column — you use JOIN.

Example:

You have:

• Students table
• Courses table

You want to see which student enrolled in which course — you use JOIN!

Types of Joins

INNER JOIN Example

SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN StudentCourses
ON Students.ID = StudentCourses.StudentID
INNER JOIN Courses
ON StudentCourses.CourseID = Courses.CourseID;

Explanation:

• Get only students who enrolled in some course.

LEFT JOIN Example

SELECT Students.Name, Courses.CourseName
FROM Students
LEFT JOIN StudentCourses
ON Students.ID = StudentCourses.StudentID
LEFT JOIN Courses
ON StudentCourses.CourseID = Courses.CourseID;

Explanation:

• Get all students — even if they have not enrolled in any course (NULL values will appear).

What are Subqueries?

A Subquery is a query written inside another query.

Example:

SELECT Name
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Explanation:

• First, (SELECT AVG(Salary)) calculates average salary.
• Then, we select employees whose salary is above average.

Subqueries are useful for advanced filtering, comparisons, and conditions.

What are Views?

A View is like a virtual table based on a query.

It does not store actual data; it just saves the result of a query.

Example:

CREATE VIEW HighSalaryEmployees AS
SELECT Name, Salary
FROM Employees
WHERE Salary > 50000;

Now you can use this View:

SELECT * FROM HighSalaryEmployees;

Why use Views?

• Hide complex queries
• Improve security (show limited data)
• Simplify repeated tasks

What are Indexes?

Indexes are like the Index page in a book.
They help find data faster without scanning the entire table.

Example:

CREATE INDEX idx_name
ON Employees(Name);

Explanation:

• idx_name index helps find Employees by their Name faster.

But too many indexes can slow down Insert/Update operations — so use wisely.

What are Stored Procedures?

A Stored Procedure is a saved set of SQL commands that you can call anytime.

Think of it like a function in programming.

Example:

DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM Employees;
END //
DELIMITER ;

Now you can simply run:

CALL GetAllEmployees();

Useful for:

• Repeating tasks
• Improving security
• Reducing mistakes

Advanced Interview Questions and Answers

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

Answer:

• INNER JOIN — Only returns matching records from both tables.
• LEFT JOIN — Returns all records from the left table and matched data from the right table.

Q2. What is a Subquery?

Answer:
A Subquery is a query inside another query.
It helps to filter or calculate values dynamically.

Example:

SELECT Name
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

Q3. What is a View?

Answer:
A View is a saved SQL query that acts like a virtual table.
It simplifies complex queries and improves security.

Q4. What is a Stored Procedure?

Answer:
A Stored Procedure is a group of SQL statements saved together, which you can run with a simple call.

Q5. What is the use of Indexes?

Answer:
Indexes help the database find data faster.
They make SELECT queries faster but may slow down INSERT/UPDATE a little.

Mini Project (Practice)

Let’s create a real-world system:

Step 1: Create Tables

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    DeptID INT,
    Salary INT,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

Step 2: Insert Data

INSERT INTO Departments VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

INSERT INTO Employees VALUES
(1, 'Alice', 1, 45000),
(2, 'Bob', 2, 55000),
(3, 'Charlie', 3, 60000),
(4, 'David', NULL, 40000);

Step 3: Join Tables

SELECT Employees.Name, Departments.DeptName
FROM Employees
LEFT JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Step 4: Create View

CREATE VIEW ITEmployees AS
SELECT Name
FROM Employees
WHERE DeptID = 3;

SELECT * FROM ITEmployees;

Now you have completed full SQL!
You can:

• Create Databases
• Write complex Queries
• Join Multiple Tables
• Optimize Data using Indexes
• Create Views and Procedures
• Clear Interviews confidently