1. What is SQL?

• SQL stands for Structured Query Language.

• It is a language used to interact with databases.

• With SQL, we can:

o Create databases and tables

o Insert data into tables

o Retrieve (Query) data

o Update existing data

o Delete data

o Control who can access the data

👉 In short: SQL helps us talk to databases.

2. Why SQL is Important?

Imagine a library database:

• It stores information about books, authors, and members.

• If you want to find all books written by "Chetan Bhagat", you won’t go and check shelf by shelf.

• Instead, you write an SQL query, and the database gives the answer in seconds.

3. SQL is a Declarative Language

• You don’t need to tell how to do something, just what you want.
Example:

SELECT name FROM students WHERE age > 18;

• You are saying: “Give me names of students older than 18”.

• You don’t worry about how the database finds it.

🗂️ SQL Basic Commands (with Simple Examples)

SQL commands are grouped into categories:

(A) Data Definition Language (DDL)

Used to define and manage database structures (like tables).

1. CREATE → To make new table

CREATE TABLE Students (

StudentID INT,

Name VARCHAR(50),

Age INT

);

2. ALTER → To change a table

ALTER TABLE Students ADD Email VARCHAR(100);

3. DROP → To delete a table

DROP TABLE Students;

(B) Data Manipulation Language (DML)

Used to insert, update, and delete data.

1. INSERT → Add new records

INSERT INTO Students (StudentID, Name, Age)

VALUES (1, 'Rahul', 20);

2. UPDATE → Change data

UPDATE Students

SET Age = 21

WHERE StudentID = 1;

3. DELETE → Remove records

DELETE FROM Students WHERE StudentID = 1;

Mainly SELECT – used to retrieve data.

Example:

SELECT Name, Age FROM Students;

👉 Output might be:

Name	Age
Rahul	20
Priya	19

Filtering:

SELECT Name FROM Students WHERE Age > 18;

(D) Data Control Language (DCL)

Used to control access.

1. GRANT → Give permission

GRANT SELECT ON Students TO User1;

2. REVOKE → Take back permission

REVOKE SELECT ON Students FROM User1;

(E) Transaction Control Language (TCL)

Used to manage transactions.

1. COMMIT → Save changes permanently

2. ROLLBACK → Undo changes

Example:

BEGIN;

UPDATE Students SET Age = 25 WHERE StudentID = 2;

ROLLBACK; --undo

Simple Real-Life Example

Example: Online Shopping Database

Table: Products

ProductID	Name	Price	Stock
101	Laptop	50000	10
102	Mobile	20000	25
103	Earphones	1000	100

SQL Queries

1. Find all products:

SELECT * FROM Products;

2. Find products costing more than 10,000:

SELECT Name, Price FROM Products WHERE Price >10000;

3. Add a new product:

INSERT INTO Products VALUES (104, 'Tablet', 15000, 15);

4. Update stock of Laptop:

UPDATE Products SET Stock = 8 WHERE ProductID = 101;

5. Delete a product:

DELETE FROM Products WHERE ProductID = 103;

✅ Summary in Simple Words:

• SQL = Language to manage and query data in databases.

• Commands are grouped as DDL, DML, DQL, DCL, TCL.

• Very powerful, widely used in all industries (banking, e-commerce, healthcare, etc.).

Relationships in SQL Databases

In real life, data is not stored in one big table. Instead, we use multiple related tables.
Relationships help us connect data across tables.

1. Types of Relationships

1. One-to-One (1:1) → One record in a table relates to onerecord in another.
Example: Each student has one ID card.

2. One-to-Many (1:N) → One record relates to many in another.
Example: One teacher teaches many students.

3. Many-to-Many (M:N) → Many records relate to many.
Example: Students can enroll in many courses, and courses can have many students.

2. Creating Relationships with Foreign Keys

A Foreign Key is a column in one table that refers to the Primary Key in another table.

👉 Primary Key = Unique identifier (like StudentID).
👉 Foreign Key = A link to another table’s Primary Key.

3. Example: Students and Courses

Step 1: Create Students table

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50),

Age INT

);

Step 2: Create Courses table

CREATE TABLE Courses (

CourseID INT PRIMARY KEY,

CourseName VARCHAR(50)

);

Step 3: Create Enrollments table (relationship)

Since one student can take many courses, and one course can have many students → Many-to-Many relationship.
We need a middle table (junction table).

CREATE TABLE Enrollments (

EnrollmentID INT PRIMARY KEY,

StudentID INT,

CourseID INT,

FOREIGN KEY (StudentID) REFERENCESStudents(StudentID),

FOREIGN KEY (CourseID) REFERENCESCourses(CourseID)

);

4. Insert Data into Tables

-- Students

INSERT INTO Students VALUES (1, 'Rahul', 20);

INSERT INTO Students VALUES (2, 'Priya', 19);

-- Courses

INSERT INTO Courses VALUES (101, 'DBMS');

INSERT INTO Courses VALUES (102, 'Python');

-- Enrollments (relationships)

INSERT INTO Enrollments VALUES (1, 1, 101); -- Rahul in DBMS

INSERT INTO Enrollments VALUES (2, 1, 102); -- Rahul in Python

INSERT INTO Enrollments VALUES (3, 2, 101); -- Priya in DBMS

5. Query Across Tables (Using JOIN)

👉 Find all students with their enrolled courses:

SELECT s.Name, c.CourseName

FROM Students s

JOIN Enrollments e ON s.StudentID = e.StudentID

JOIN Courses c ON e.CourseID = c.CourseID;

✅ Result:

Name	CourseName
Rahul	DBMS
Rahul	Python
Priya	DBMS

⭐ Real-Life Analogy

Think of:

• Students table = List of people

• Courses table = List of subjects

• Enrollments table = Attendance sheet that connects who attends which course

✅ Summary:

• Relationships = Link between tables using Primary Key + Foreign Key.

• One-to-One, One-to-Many, Many-to-Many are common.

• Many-to-Many needs a junction (bridge) table.

• SQL JOIN helps fetch related data.

AUTO INCREMENT in SQL

1. What is AUTO INCREMENT?

• AUTO INCREMENT is used when you want a column (usually the Primary Key) to automatically generate a unique value whenever you insert a new record.

• You don’t have to manually enter the ID value. The database automatically increases it by 1 (or another step, depending on settings).

👉 Example: Student IDs, Employee IDs, Order Numbers, etc.

2. Syntax in Different Databases

• MySQL / MariaDB

CREATE TABLE Students (

StudentID INT AUTO_INCREMENT PRIMARY KEY,

Name VARCHAR(50),

Age INT

);

• SQL Server

CREATE TABLE Students (

StudentID INT IDENTITY(1,1) PRIMARY KEY, -- (start at 1, increment by 1)

Name VARCHAR(50),

Age INT

);

• Oracle
Oracle does not have AUTO_INCREMENT. Instead, it uses a SEQUENCE:

CREATE SEQUENCE student_seq START WITH 1INCREMENT BY 1;

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50),

Age INT

);

INSERT INTO Students (StudentID, Name, Age) VALUES(student_seq.NEXTVAL, 'Rahul', 20);

• PostgreSQL

CREATE TABLE Students (

StudentID SERIAL PRIMARY KEY,

Name VARCHAR(50),

Age INT

);

3. Example in MySQL

Step 1: Create Table

CREATE TABLE Students (

StudentID INT AUTO_INCREMENT PRIMARY KEY,

Name VARCHAR(50),

Age INT

);

Step 2: Insert Data

INSERT INTO Students (Name, Age) VALUES ('Rahul', 20);

INSERT INTO Students (Name, Age) VALUES ('Priya', 19);

INSERT INTO Students (Name, Age) VALUES ('Aman', 21);

Step 3: Check Data

SELECT * FROM Students;

✅ Result:

StudentID	Name	Age
1	Rahul	20
2	Priya	19
3	Aman	21

👉 Notice that StudentID was filled automatically.

4. Resetting AUTO INCREMENT

• If you want to restart the numbering (e.g., after deleting rows):

ALTER TABLE Students AUTO_INCREMENT = 1;

5. Key Points to Remember

• AUTO INCREMENT is mostly used with Primary Keys.

• You don’t need to insert values into this column; just skip it.

• Each new record gets the next available number.

• Different databases have slightly different keywords (AUTO_INCREMENT, IDENTITY, SERIAL, SEQUENCE).

⭐ Real-Life Example

Think of a library system:

• Each new book gets an auto-generated ID.

• You don’t write it yourself, the system does.

✅ Summary in Simple Words:

• AUTO INCREMENT = Database automatically assigns a unique ID when inserting new records.

• Saves time and avoids duplicate IDs.

• Implemented differently in MySQL, SQL Server, Oracle, and PostgreSQL.

Aggregate Functions in SQL (DBMS)

Aggregate functions are special functions in SQL that perform a calculation on a set of values and return a single summary value.
They are mainly used with the GROUP BY clause to group rows based on some column(s), but they can also be used without GROUP BY to get results for the whole table.

The most common aggregate functions are:

1. MIN() – Finds the minimum value

2. MAX() – Finds the maximum value

3. COUNT() – Counts rows

4. AVG() – Finds the average

5. SUM() – Finds the total sum

1. MIN() Function

• Returns the smallest value from a set of values.

• Works with numeric, date, and string data types (for strings, it gives lexicographically smallest).

Example:

SELECT MIN(salary) AS Lowest_Salary

FROM Employees;

👉 Finds the lowest salary in the Employees table.

If the Employees table has:

EmpID	Name	Salary
1	Raj	35000
2	Neha	42000
3	Ankit	28000
4	Meena	50000

Result:

Lowest_Salary

28000

2. MAX() Function

• Returns the largest value from a set of values.

Example:

SELECT MAX(salary) AS Highest_Salary

FROM Employees;

Result:

Highest_Salary

50000

3. COUNT() Function

• Returns the number of rows in a result set.

• Two variations:

o COUNT(*) → counts all rows (including NULLs).

o COUNT(column) → counts only non-NULL valuesin that column.

Example 1: Count all employees

SELECT COUNT(*) AS Total_Employees

FROM Employees;

Result:

Total_Employees

Example 2: Count employees with salary

SELECT COUNT(salary) AS Count_Salaries

FROM Employees;

👉 If any employee has NULL salary, that employee will not be counted.

4. AVG() Function

• Returns the average (mean) of numeric values.

• Ignores NULL values.

Example:

SELECT AVG(salary) AS Average_Salary

FROM Employees;

Calculation:

(35000+42000+28000+50000)/4=38750(35000 + 42000 + 28000 + 50000) / 4 = 38750(35000+42000+28000+50000)/4=38750

Result:

Average_Salary

38750

5. SUM() Function

• Returns the total sum of numeric values.

Example:

SELECT SUM(salary) AS Total_Salary

FROM Employees;

Calculation:

35000+42000+28000+50000=15500035000 + 42000 + 28000 + 50000 = 15500035000+42000+28000+50000=155000

Result:

Total_Salary

155000

🔹 Using Aggregate Functions with GROUP BY

Aggregate functions are more powerful when used with GROUP BY to analyze data by categories.

Example: Total salary per department

SELECT Department, SUM(salary) AS Total_Salary,

AVG(salary) AS Avg_Salary,

MIN(salary) AS Min_Salary,

MAX(salary) AS Max_Salary,

COUNT(*) AS Employee_Count

FROM Employees

GROUP BY Department;

If the table is:

EmpID	Name	Salary	Department
1	Raj	35000	IT
2	Neha	42000	HR
3	Ankit	28000	IT
4	Meena	50000	HR
5	Pooja	45000	IT

Result:

Department	Total_Salary	Avg_Salary	Min_Salary	Max_Salary	Employee_Count
IT	108000	36000	28000	45000	3
HR	92000	46000	42000	50000	2

🔹 Key Points to Remember

• Aggregate functions ignore NULL values (except COUNT(*)).

• They return one value per group (or for the whole table if no grouping).

• Can be combined with HAVING clause to filter aggregated results.

Example: Departments with average salary above 40,000

SELECT Department, AVG(salary) AS Avg_Salary

FROM Employees

GROUP BY Department

HAVING AVG(salary) > 40000;

✅ Now you have a complete detailed explanation of aggregate functions in SQL with examples, tables, and queries.

What are Nested Subqueries?

• A subquery is a query inside another query.

• A nested subquery is when one query is written inside another query’s WHERE, FROM, or SELECT clause.

• Subqueries can be used with aggregate functions to make powerful queries.

👉 Aggregate functions (MIN, MAX, COUNT, AVG, SUM) are often combined with subqueries to:

1. Compare values with overall maximum/minimum.

2. Filter groups.

3. Calculate aggregated values dynamically.

🔹 Syntax of Subquery with Aggregates

SELECT column_name(s)

FROM table_name

WHERE column_name = (SELECTAGG_FUNC(column_name) FROM table_name);

Here, AGG_FUNC can be MAX, MIN, AVG, SUM, COUNT.

🔹 Examples of Nested Subqueries with Aggregate Functions

Let’s use an Employees table:

EmpID	Name	Department	Salary
1	Raj	IT	35000
2	Neha	HR	42000
3	Ankit	IT	28000
4	Meena	HR	50000
5	Pooja	IT	45000

1. Find Employee with Maximum Salary (using MAX() in subquery)

SELECT Name, Salary

FROM Employees

WHERE Salary = (SELECT MAX(Salary) FROMEmployees);

👉 Subquery (SELECT MAX(Salary)...) returns 50000.
Main query fetches employee(s) with that salary → Meena, 50000.

2. Find Employee with Minimum Salary in IT Department

SELECT Name, Salary

FROM Employees

WHERE Salary = (

SELECT MIN(Salary)

FROM Employees

WHERE Department = 'IT'

);

👉 Inner query finds MIN(Salary) in IT → 28000.
Outer query fetches → Ankit, 28000.

3. Find Employees who earn more than the Average Salary

SELECT Name, Salary

FROM Employees

WHERE Salary > (SELECT AVG(Salary) FROMEmployees);

👉 Subquery finds AVG(Salary) = (35000+42000+28000+50000+45000)/5 = 40000.
Main query fetches employees with salary > 40000 → Neha(42000), Meena (50000), Pooja (45000).

4. Find Departments with Total Salary Greater than the Average Total Salary of All Departments

(aggregate inside aggregate with GROUP BY)

SELECT Department, SUM(Salary) AS Dept_Total

FROM Employees

GROUP BY Department

HAVING SUM(Salary) > (

SELECT AVG(Dept_Total)

FROM (

SELECT SUM(Salary) AS Dept_Total

FROM Employees

GROUP BY Department

) AS Temp

);

👉 Explanation:

• Inner subquery calculates total salary for each department.

• Then another AVG() is applied over those totals.

• Outer query selects only those departments whose total > average of all departments.

5. Find Employee(s) with the Second Highest Salary

SELECT Name, Salary

FROM Employees

WHERE Salary = (

SELECT MAX(Salary)

FROM Employees

WHERE Salary < (SELECT MAX(Salary) FROMEmployees)

);

👉 Steps:

• (SELECT MAX(Salary)...) → highest salary = 50000.

• (SELECT MAX(Salary) FROM Employees WHERE Salary < 50000) → second highest = 45000.

• Outer query → Employee(s) with 45000 → Pooja.

🔹 Key Points to Remember

1. Subqueries return a single value (scalar), a row, or a table.

2. Aggregate functions inside subqueries are most common in scalar subqueries (single value).

3. Use subqueries in:

o WHERE → to filter rows by aggregate condition.

o HAVING → to filter groups.

o FROM → to use aggregated result as a virtual table.

4. Nested subqueries allow complex analysis like “find employees above average of their department” or “second highest salary.”

🔹 1. What is GROUP BY in SQL?

• The GROUP BY clause groups rows that have the same values in one or more columns.

• It is used with aggregate functions like COUNT(), SUM(), AVG(), MIN(), MAX() to produce summary results per group.

• Without GROUP BY, aggregate functions apply to the entire table. With GROUP BY, they apply per group.

🔹 2. Syntax

SELECT column1, AGG_FUNC(column2)

FROM table_name

GROUP BY column1;

• column1 → column used for grouping.

• AGG_FUNC → aggregate function (SUM, AVG, MIN, MAX, COUNT, etc.).

🔹 3. Example Table

Let’s use a Sales table:

SaleID	Product	Category	Amount
1	Laptop	Electronics	50000
2	Mobile	Electronics	20000
3	TV	Electronics	30000
4	Shirt	Clothing	4000
5	Jeans	Clothing	3500
6	Watch	Accessories	8000

🔹 4. Examples of GROUP BY with Aggregate Functions

a) Total Sales per Category

SELECT Category, SUM(Amount) AS Total_Sales

FROM Sales

GROUP BY Category;

👉 Groups rows by Category and sums Amount.

Category	Total_Sales
Electronics	100000
Clothing	7500
Accessories	8000

b) Average Sale Amount per Category

SELECT Category, AVG(Amount) AS Avg_Sale

FROM Sales

GROUP BY Category;

👉 Average per group.

Category	Avg_Sale
Electronics	33333.33
Clothing	3750
Accessories	8000

c) Minimum and Maximum Sale per Category

SELECT Category,

MIN(Amount) AS Min_Sale,

MAX(Amount) AS Max_Sale

FROM Sales

GROUP BY Category;

Category	Min_Sale	Max_Sale
Electronics	20000	50000
Clothing	3500	4000
Accessories	8000	8000

d) Count of Sales per Category

SELECT Category, COUNT(*) AS No_of_Sales

FROM Sales

GROUP BY Category;

Category	No_of_Sales
Electronics	3
Clothing	2
Accessories	1

🔹 5. Using GROUP BY with Multiple Columns

You can group by more than one column.

Example: Total sales per Category and Product

SELECT Category, Product, SUM(Amount) AS Total_Sales

FROM Sales

GROUP BY Category, Product;

Category	Product	Total_Sales
Electronics	Laptop	50000
Electronics	Mobile	20000
Electronics	TV	30000
Clothing	Shirt	4000
Clothing	Jeans	3500
Accessories	Watch	8000

🔹 6. Using HAVING with GROUP BY

• WHERE cannot be used with aggregate functions.

• Instead, we use HAVING (a filter applied after grouping).

Example: Find categories with total sales greater than 8000

SELECT Category, SUM(Amount) AS Total_Sales

FROM Sales

GROUP BY Category

HAVING SUM(Amount) > 8000;

Category	Total_Sales
Electronics	100000

🔹 7. Key Points about GROUP BY

1. Every column in SELECT (that is not an aggregate function) must be in the GROUP BY clause.
❌ Wrong:

2. SELECT Product, SUM(Amount) FROM Sales;

✅ Correct:

SELECT Product, SUM(Amount) FROM Sales GROUPBY Product;

3. WHERE filters before grouping, HAVING filters after grouping.

4. GROUP BY is often used with ORDER BY to sort results.

🔹 8. Real-Life Examples

• Banking: Find total deposits per branch.

• School: Find average marks per subject.

• E-commerce: Find number of orders per customer.

• Hospital: Find max bill per doctor.

✅ So, GROUP BY is the backbone of reporting and data analysis in SQL.

🔹 1. What is HAVING in SQL?

• The HAVING clause is used to filter groups of rowscreated by the GROUP BY clause.

• Unlike WHERE, which filters rows before grouping, HAVING filters after grouping.

• It is generally used with aggregate functions like SUM(), AVG(), MAX(), MIN(), COUNT().

🔹 2. Syntax

SELECT column1, AGG_FUNC(column2)

FROM table_name

GROUP BY column1

HAVING condition;

🔹 3. Example Table: Sales

SaleID	Product	Category	Amount
1	Laptop	Electronics	50000
2	Mobile	Electronics	20000
3	TV	Electronics	30000
4	Shirt	Clothing	4000
5	Jeans	Clothing	3500
6	Watch	Accessories	8000
7	Speaker	Electronics	15000

🔹 4. Difference Between WHERE and HAVING

• WHERE: filters rows before grouping.

• HAVING: filters groups after aggregation.

👉 Example:

-- WHERE filters rows

SELECT *

FROM Sales

WHERE Amount > 10000;

-- HAVING filters groups

SELECT Category, SUM(Amount) AS Total_Sales

FROM Sales

GROUP BY Category

HAVING SUM(Amount) > 40000;

🔹 5. Examples of HAVING with Aggregate Functions

a) Find categories with total sales greater than 40,000

SELECT Category, SUM(Amount) AS Total_Sales

FROM Sales

GROUP BY Category

HAVING SUM(Amount) > 40000;

✅ Result:

Category	Total_Sales
Electronics	115000

b) Find categories where average sale is greater than 10000

SELECT Category, AVG(Amount) AS Avg_Sale

FROM Sales

GROUP BY Category

HAVING AVG(Amount) > 10000;

✅ Result:

Category	Avg_Sale
Electronics	38333.33

c) Find categories with more than 2 products

SELECT Category, COUNT(*) AS No_of_Products

FROM Sales

GROUP BY Category

HAVING COUNT(*) > 2;

✅ Result:

Category	No_of_Products
Electronics	4

d) Combine WHERE and HAVING

👉 First filter rows with WHERE, then filter groups with HAVING.

SELECT Category, SUM(Amount) AS Total_Sales

FROM Sales

WHERE Amount > 5000

GROUP BY Category

HAVING SUM(Amount) > 20000;

Steps:

1. WHERE Amount > 5000 → removes Shirt (4000) and Jeans (3500).

2. Groups by category.

3. HAVING SUM(Amount) > 20000 → selects groups with total above 20000.

✅ Result:

Category	Total_Sales
Electronics	115000

🔹 6. Key Points About HAVING

1. HAVING is like WHERE, but for groups.

2. You can use aggregate functions in HAVING, but not in WHERE.
❌ Wrong:

3. SELECT Category FROM Sales WHERESUM(Amount) > 20000;

✅ Correct:

SELECT Category FROM Sales GROUP BY Category HAVING SUM(Amount) > 20000;

4. You can use both WHERE and HAVING in the same query.

5. Without GROUP BY, HAVING acts like a WHERE on the aggregated result.

🔹 7. Real-Life Use Cases of HAVING

• Banking: Find branches where total deposits > ₹1 crore.

• E-commerce: Find customers who placed more than 5 orders.

• Education: Find subjects where average marks < 40.

• Healthcare: Find doctors with more than 50 patients.

✅ So, HAVING = filter aggregated results, while WHERE = filter individual rows.

🔹 1. What is EXISTS in SQL?

• EXISTS is a logical operator that checks whether a subquery returns any rows.

• If the subquery returns at least one row → EXISTS = TRUE.

• If the subquery returns no rows → EXISTS = FALSE.

👉 It is often used with correlated subqueries (subqueriesthat depend on the outer query).

🔹 2. Syntax

SELECT column1, column2, ...

FROM table1

WHERE EXISTS (SELECT 1

FROM table2

WHERE condition);

• The SELECT 1 is just a placeholder (any constant can be used).

• What matters is whether the subquery returns rows or not.

🔹 3. Using EXISTS with Aggregate Functions

Aggregate functions (SUM, AVG, COUNT, MIN, MAX) are often placed in subqueries with EXISTS to check conditions like:

• Does a group exceed a threshold?

• Is there any record above/below average?

• Does a department have a maximum/minimum compared to others?

🔹 4. Example Table: Employees

EmpID	Name	Department	Salary
1	Raj	IT	35000
2	Neha	HR	42000
3	Ankit	IT	28000
4	Meena	HR	50000
5	Pooja	IT	45000

🔹 5. Examples of EXISTS with Aggregate Functions

a) Find employees who earn more than the average salary

SELECT Name, Salary

FROM Employees e

WHERE EXISTS (

SELECT 1

FROM Employees

WHERE Salary < e.Salary

HAVING AVG(Salary) < e.Salary

);

👉 Explanation:

• Subquery calculates the average salary.

• HAVING checks if employee’s salary > average.

• EXISTS ensures only those rows are returned.

✅ Output: Neha (42000), Meena (50000), Pooja (45000)

b) Find departments where total salary exceeds 1,00,000

SELECT DISTINCT Department

FROM Employees e

WHERE EXISTS (

SELECT 1

FROM Employees

WHERE Department = e.Department

GROUP BY Department

HAVING SUM(Salary) > 100000

);

👉 Explanation:

• Subquery groups by department and checks if total salary > 100000.

• If true, EXISTS = TRUE, so the department is returned.

✅ Output: IT Department (35000+28000+45000 = 108000)

c) Find employees working in a department where at least one employee has maximum salary

SELECT Name, Department

FROM Employees e

WHERE EXISTS (

SELECT 1

FROM Employees

WHERE Department = e.Department

HAVING MAX(Salary) = (SELECT MAX(Salary) FROMEmployees)

);

👉 Explanation:

• Inner query checks if the department has the employee with highest salary overall.

• EXISTS ensures only employees from that department are returned.

✅ Output: Employees from HR department (because Meena has the global MAX = 50000).

d) Find employees from departments where average salary is greater than 40,000

SELECT Name, Department, Salary

FROM Employees e

WHERE EXISTS (

SELECT 1

FROM Employees

WHERE Department = e.Department

GROUP BY Department

HAVING AVG(Salary) > 40000

);

👉 Explanation:

• Subquery groups employees by department.

• HAVING AVG(Salary) > 40000 checks average condition.

• EXISTS ensures employees from such departments are returned.

✅ Output: All employees from HR (average = 46000) and IT (average = 36000 → excluded).

🔹 6. Key Points about EXISTS with Aggregates

1. EXISTS only cares about whether subquery returns rows, not the values.

2. Aggregates (SUM, AVG, COUNT, MIN, MAX) are usually placed inside HAVING.

3. EXISTS is often more efficient than IN with large datasets.

4. You can combine EXISTS with correlated subqueriesto filter based on group-level aggregate conditions.

🔹 7. Real-Life Use Cases

• Banking: Find branches where total deposits exceed a limit.

• E-commerce: Find customers who belong to groups with average purchase > ₹10,000.

• Education: Find students from classes where maximum marks > 95.

• Healthcare: Find doctors working in departments where patient count > 50.

✅ In short:

• EXISTS checks existence of rows.

• With aggregate functions, it helps filter rows/groups by totals, averages, max/min, counts.

🔹 1. What is CASE in SQL?

• The CASE expression lets you apply conditional logic in SQL (like IF-THEN-ELSE).

• General syntax:

CASE

WHEN condition THEN result

ELSE result

END

🔹 2. Why use CASE with Aggregate Functions?

Aggregate functions (SUM, AVG, COUNT, MIN, MAX) work on all rows.
But sometimes we want to apply aggregate only to rows matching a condition → that’s where CASE helps.

👉 Example: “Count male employees separately from female employees in the same query.”

🔹 3. Example Table: Employees

EmpID	Name	Gender	Department	Salary
1	Raj	M	IT	35000
2	Neha	F	HR	42000
3	Ankit	M	IT	28000
4	Meena	F	HR	50000
5	Pooja	F	IT	45000

🔹 4. Examples of CASE in Aggregate Functions

a) Count Male vs Female Employees

SELECT

COUNT(CASE WHEN Gender = 'M' THEN 1 END) ASMale_Count,

COUNT(CASE WHEN Gender = 'F' THEN 1 END) ASFemale_Count

FROM Employees;

✅ Result:

Male_Count	Female_Count
2	3

b) Total Salary by Gender

SELECT

SUM(CASE WHEN Gender = 'M' THEN Salary END) ASMale_Salary_Total,

SUM(CASE WHEN Gender = 'F' THEN Salary END) ASFemale_Salary_Total

FROM Employees;

✅ Result:

Male_Salary_Total	Female_Salary_Total
63000	137000

c) Average Salary by Department (Conditional AVG)

SELECT

Department,

AVG(CASE WHEN Gender = 'M' THEN Salary END) ASAvg_Male_Salary,

AVG(CASE WHEN Gender = 'F' THEN Salary END) ASAvg_Female_Salary

FROM Employees

GROUP BY Department;

✅ Result:

Department	Avg_Male_Salary	Avg_Female_Salary
IT	31500	45000
HR	NULL	46000

(NULL appears if no males in HR.)

d) Count Employees with Salary Above 40,000

SELECT

COUNT(CASE WHEN Salary > 40000 THEN 1 END) ASAbove_40k,

COUNT(CASE WHEN Salary <= 40000 THEN 1 END) AS Below_Or_Equal_40k

FROM Employees;

✅ Result:

Above_40k	Below_Or_Equal_40k
3	2

e) Department-Wise Conditional SUM

SELECT

Department,

SUM(CASE WHEN Salary > 40000 THEN Salary ELSE 0END) AS High_Salary_Total,

SUM(CASE WHEN Salary <= 40000 THEN Salary ELSE0 END) AS Low_Salary_Total

FROM Employees

GROUP BY Department;

✅ Result:

Department	High_Salary_Total	Low_Salary_Total
IT	45000	63000
HR	50000	42000

🔹 5. Key Points about CASE with Aggregates

1. CASE works inside aggregates for conditional counting/summing/averaging.

2. Very useful for pivot-like queries (rows → columns).

3. ELSE 0 or ELSE NULL changes behavior:

o ELSE 0 → adds 0 to the aggregate.

o ELSE NULL → ignored by aggregate.

4. Common in reporting and dashboards (gender-wise, category-wise, region-wise summaries).

🔹 6. Real-Life Use Cases

• Banking: Count accounts with balance > 1 lakh vs < 1 lakh.

• E-commerce: Total sales of returned vs non-returned products.

• Education: Average marks of pass vs fail students.

• Healthcare: Number of patients treated by male vs female doctors.

✅ So, CASE inside aggregate functions = conditional aggregation, making SQL queries much more powerful.

🔹 1. What is ORDER BY?

• ORDER BY is used to sort the result set in ascending (ASC, default) or descending (DESC) order.

• Aggregate functions like SUM(), AVG(), COUNT(), MAX(), MIN() are often combined with GROUP BY.

• After aggregation, we can order results based on the aggregate values.

🔹 2. Syntax

SELECT column_name, AGG_FUNC(column_name)

FROM table_name

GROUP BY column_name

ORDER BY AGG_FUNC(column_name) ASC | DESC;

🔹 3. Example Table: Sales

OrderID	Product	Category	Amount
1	Laptop	Electronics	50000
2	Phone	Electronics	30000
3	Shirt	Clothing	2000
4	Jeans	Clothing	3500
5	TV	Electronics	45000
6	Shoes	Clothing	4000
7	Watch	Accessories	6000
8	Belt	Accessories	1500

🔹 4. Examples of ORDER BY with Aggregate Functions

a) Order Categories by Total Sales (SUM)

SELECT Category, SUM(Amount) AS Total_Sales

FROM Sales

GROUP BY Category

ORDER BY SUM(Amount) DESC;

✅ Result:

Category	Total_Sales
Electronics	125000
Clothing	9500
Accessories	7500

👉 Categories are sorted by total sales in descending order.

b) Order Categories by Average Sales (AVG)

SELECT Category, AVG(Amount) AS Avg_Sales

FROM Sales

GROUP BY Category

ORDER BY AVG(Amount) ASC;

✅ Result:

Category	Avg_Sales
Clothing	3166.67
Accessories	3750.00
Electronics	41666.67

👉 Lowest to highest average sales per order.

c) Order Categories by Number of Orders (COUNT)

SELECT Category, COUNT(*) AS Order_Count

FROM Sales

GROUP BY Category

ORDER BY COUNT(*) DESC;

✅ Result:

Category	Order_Count
Electronics	3
Clothing	3
Accessories	2

👉 Sorted by most orders.

d) Order Categories by Highest Sale (MAX)

SELECT Category, MAX(Amount) AS Max_Sale

FROM Sales

GROUP BY Category

ORDER BY MAX(Amount) DESC;

✅ Result:

Category	Max_Sale
Electronics	50000
Clothing	4000
Accessories	6000

👉 Shows the biggest single sale in each category, ordered by size.

e) Order Categories by Smallest Sale (MIN)

SELECT Category, MIN(Amount) AS Min_Sale

FROM Sales

GROUP BY Category

ORDER BY MIN(Amount) ASC;

✅ Result:

Category	Min_Sale
Accessories	1500
Clothing	2000
Electronics	30000

👉 Ordered by lowest sale value in each category.

🔹 5. Using Column Alias in ORDER BY

Instead of writing the aggregate again, we can use the alias:

SELECT Category, SUM(Amount) AS Total_Sales

FROM Sales

GROUP BY Category

ORDER BY Total_Sales DESC;

🔹 6. Key Points

1. ORDER BY always comes after GROUP BY (if both are used).

2. You can order by aggregate functions directly or by their aliases.

3. Default sorting is ASC (ascending). Use DESC for descending.

4. Aggregate + ORDER BY helps in ranking, top-N queries, and reporting.

🔹 7. Real-Life Use Cases

• Sales: Find top-selling categories, products, or regions.

• Education: Rank students by total or average marks.

• HR: List departments by highest/lowest average salary.

• Banking: Show branches with maximum deposits or loan counts.

What is a JOIN?

A JOIN in SQL is used to combine rows from two or more tables based on a related column between them.
Usually, this related column is a primary key in one table and a foreign key in another.

Let’s use two simple example tables throughout:

🧩 Table 1: Students

student_id	name	dept_id
1	Aditi	10
2	Rohan	20
3	Meena	30
4	Karan	40

🧩 Table 2: Departments

dept_id	dept_name
10	Computer Sci
20	Electronics
30	Mechanical
50	Civil

1️⃣ INNER JOIN

🔹 Meaning:

• Returns only the rows that have matching values in both tables.

• If there’s no match, that row is not included in the result.

🔹 Syntax:

SELECT Students.name, Departments.dept_name

FROM Students

INNER JOIN Departments

ON Students.dept_id = Departments.dept_id;

🔹 Result:

name	dept_name
Aditi	Computer Sci
Rohan	Electronics
Meena	Mechanical

👉 Explanation:
Karan’s dept_id = 40 doesn’t exist in Departments, so his row is not shown.

2️⃣ LEFT JOIN (LEFT OUTER JOIN)

🔹 Meaning:

• Returns all rows from the left table (Students) and
matching rows from the right table (Departments).

• If no match is found, NULL values are shown for the right table.

🔹 Syntax:

SELECT Students.name, Departments.dept_name

FROM Students

LEFT JOIN Departments

ON Students.dept_id = Departments.dept_id;

🔹 Result:

name	dept_name
Aditi	Computer Sci
Rohan	Electronics
Meena	Mechanical
Karan	NULL

👉 Explanation:
Karan has no matching department, but still appears because LEFT JOIN keeps all rows from the left table.

3️⃣ RIGHT JOIN (RIGHT OUTER JOIN)

🔹 Meaning:

• Returns all rows from the right table (Departments) and
matching rows from the left table (Students).

• If no match, NULLs are shown for left table columns.

🔹 Syntax:

SELECT Students.name, Departments.dept_name

FROM Students

RIGHT JOIN Departments

ON Students.dept_id = Departments.dept_id;

🔹 Result:

name	dept_name
Aditi	Computer Sci
Rohan	Electronics
Meena	Mechanical
NULL	Civil

👉 Explanation:
Department “Civil” (dept_id 50) has no student, so NULL appears in name.

4️⃣ NATURAL JOIN

🔹 Meaning:

• Automatically joins two tables based on all columns with the same name and same data type.

• You don’t need to write the ON condition.

🔹 Syntax:

SELECT *

FROM Students

NATURAL JOIN Departments;

🔹 Result:

student_id	name	dept_id	dept_name
1	Aditi	10	Computer Sci
2	Rohan	20	Electronics
3	Meena	30	Mechanical

👉 Explanation:
It automatically joins on the dept_id column (since both tables have it).

⚠️ Be careful:
If two tables have multiple same column names, SQL joins on all of them — which can give unexpected results.

5️⃣ CARTESIAN PRODUCT (CROSS JOIN)

🔹 Meaning:

• Returns all possible combinations of rows from both tables.

• Each row in the first table is combined with every row in the second table.

🔹 Syntax:

SELECT Students.name, Departments.dept_name

FROM Students

CROSS JOIN Departments;

🔹 Result:

name	dept_name
Aditi	Computer Sci
Aditi	Electronics
Aditi	Mechanical
Aditi	Civil
Rohan	Computer Sci
Rohan	Electronics
Rohan	Mechanical
Rohan	Civil
Meena	Computer Sci
Meena	Electronics
Meena	Mechanical
Meena	Civil
Karan	Computer Sci
Karan	Electronics
Karan	Mechanical
Karan	Civil

👉 Explanation:
If Students has 4 rows and Departments has 4 rows → 4 × 4 = 16 total rows.

🧠 Summary Table

Join Type	Matching Condition	Keeps Unmatched Rows	Example Output
INNER JOIN	Only matching rows	❌ No	Only matched
LEFT JOIN	Matching + Left only	✅ Left	Left + NULLs
RIGHT JOIN	Matching + Right only	✅ Right	Right + NULLs
NATURAL JOIN	Auto match by column name	❌ No	Same as inner join (auto condition)
CARTESIAN PRODUCT	No condition	✅ All combinations	All possible pairs

💡 TABLE OF CONTENTS

1️⃣ INNER JOIN
2️⃣ LEFT JOIN
3️⃣ RIGHT JOIN
4️⃣ NATURAL JOIN
5️⃣ CARTESIAN PRODUCT
6️⃣ Summary with visuals (text-based)

🧩 Example Tables

Table 1: Customers

cust_id	cust_name	city
1	Aman	Delhi
2	Sneha	Mumbai
3	Ravi	Pune
4	Neha	Delhi

Table 2: Orders

order_id	cust_id	product
101	1	Laptop
102	2	Mobile
103	1	Keyboard
104	5	Mouse

1️⃣ INNER JOIN

🔹 Concept:

Only rows that have a match in both tables are returned.

🔹 Query:

SELECT Customers.cust_name, Orders.product

FROM Customers

INNER JOIN Orders

ON Customers.cust_id = Orders.cust_id;

🔹 Output:

cust_name	product
Aman	Laptop
Aman	Keyboard
Sneha	Mobile

🔹 Explanation:

• Aman (cust_id 1) has 2 orders → both appear.

• Sneha (cust_id 2) has 1 order.

• Ravi and Neha didn’t order anything → not shown.

• Order 104 (cust_id 5) has no matching customer → ignored.

2️⃣ LEFT JOIN (LEFT OUTER JOIN)

🔹 Concept:

Returns all rows from left table (Customers),
and matching rows from right table (Orders).
If no match → NULL.

🔹 Query:

SELECT Customers.cust_name, Orders.product

FROM Customers

LEFT JOIN Orders

ON Customers.cust_id = Orders.cust_id;

🔹 Output:

cust_name	product
Aman	Laptop
Aman	Keyboard
Sneha	Mobile
Ravi	NULL
Neha	NULL

🔹 Explanation:

• Ravi and Neha didn’t order anything, so product = NULL.

• All customers are displayed.

3️⃣ RIGHT JOIN (RIGHT OUTER JOIN)

🔹 Concept:

Returns all rows from right table (Orders),
and matching rows from left table (Customers).
If no match → NULL.

🔹 Query:

SELECT Customers.cust_name, Orders.product

FROM Customers

RIGHT JOIN Orders

ON Customers.cust_id = Orders.cust_id;

🔹 Output:

cust_name	product
Aman	Laptop
Sneha	Mobile
Aman	Keyboard
NULL	Mouse

🔹 Explanation:

• Order 104 belongs to cust_id = 5, which doesn’t exist in Customers → cust_name is NULL.

• All orders are shown, even those without a customer.

4️⃣ NATURAL JOIN

🔹 Concept:

Automatically joins tables using columns with the same name (and same datatype).
No need for ON.

Let’s use:

Table 1: Employees

emp_id	name	dept_id
1	Ajay	10
2	Bhavna	20
3	Charu	30

Table 2: Departments

dept_id	dept_name
10	HR
20	IT
40	Finance

🔹 Query:

SELECT *

FROM Employees

NATURAL JOIN Departments;

🔹 Output:

emp_id	name	dept_id	dept_name
1	Ajay	10	HR
2	Bhavna	20	IT

🔹 Explanation:

• It automatically matched using dept_id.

• Charu (dept_id 30) not shown because no matching dept_id 30 in Departments.

• No need to write ON condition.

5️⃣ CARTESIAN PRODUCT (CROSS JOIN)

🔹 Concept:

Combines every row of first table with every row of second table.
No condition required → total combinations = (rows in table1 × rows in table2).

Example Tables:

Colors and Shapes

color
Red
Blue
shape
Circle
Square

🔹 Query:

SELECT Colors.color, Shapes.shape

FROM Colors

CROSS JOIN Shapes;

🔹 Output:

color	shape
Red	Circle
Red	Square
Blue	Circle
Blue	Square

🔹 Explanation:

Every color is paired with every shape → 2 × 2 = 4 rows.

🔄 BONUS: CARTESIAN PRODUCT (Without CROSS keyword)

You can also get a Cartesian product by listing multiple tables without a WHERE or JOIN condition:

SELECT *

FROM Students, Departments;

This will also produce all possible combinations.

🧠 SUMMARY COMPARISON

Type	Description	Missing Data Handling	Example Use
INNER JOIN	Only matching rows from both tables	Ignores non-matching	Get customers who placed orders
LEFT JOIN	All from left + matching from right	Shows NULL for right	Show all customers (even without orders)
RIGHT JOIN	All from right + matching from left	Shows NULL for left	Show all orders (even if customer missing)
NATURAL JOIN	Auto joins by same column names	Ignores non-matching	Simplifies syntax when same column names exist
CROSS JOIN	Every combination of both tables	No condition used	Generate all possible pairs (colors × shapes)

🔵 Text-Based Venn Diagram (for quick memory)

INNER JOIN: (A ∩ B)

LEFT JOIN: (A ∪ (A-B))

RIGHT JOIN: (B ∪ (B-A))

CROSS JOIN: A × B (All)

1. Introduction to Views

🔹 Definition:

A View in SQL is a virtual table that is based on the result of a SQL query.
It doesn’t store data physically like a regular table; instead, it shows data stored in other tables.

🔹 Key Features:

• Acts as a window to access data from one or more tables.

• Simplifies complex queries.

• Provides security by limiting access to specific columns or rows.

✅ Example 1: Simple View

CREATE VIEW EmployeeView AS

SELECT emp_id, emp_name, department

FROM Employees;

Explanation:
This view named EmployeeView displays only emp_id, emp_name, and department columns from the Employeestable.
If someone queries this view, they will see only these columns — hiding salary or personal data.

✅ Example 2: View with Join

CREATE VIEW OrderDetailsView AS

SELECT Orders.order_id, Customers.customer_name, Orders.order_date

FROM Orders

JOIN Customers ON Orders.customer_id =Customers.customer_id;

Explanation:
This view joins two tables — Orders and Customers.
Instead of writing the full JOIN query every time, you can simply use:

SELECT * FROM OrderDetailsView;

This makes your queries simpler and reusable.

🎯 2. What are Views For? (Purpose of Views)

🔹 Main Purposes:

1. Simplify complex queries

2. Enhance security (restrict access to certain data)

3. Provide logical data independence (structure changes don’t affect users)

4. Present aggregated or computed data easily

✅ Example 1: Security Purpose

CREATE VIEW PublicEmployeeData AS

SELECT emp_name, department

FROM Employees;

Explanation:
This view hides sensitive data like salary or contact details.
Users who query this view can only see limited columns.

✅ Example 2: Simplifying Complex Queries

CREATE VIEW SalesSummary AS

SELECT customer_id, SUM(order_amount) AS TotalSales

FROM Orders

GROUP BY customer_id;

Explanation:
Instead of writing the entire GROUP BY query every time, users can simply call:

SELECT * FROM SalesSummary;

It saves time and keeps the SQL code clean.

🧱 3. Data Definition using Views

🔹 Definition:

Data Definition refers to creating, modifying, or deleting a view using DDL (Data Definition Language) commands.

✅ Example 1: Create a View

CREATE VIEW DepartmentSalary AS

SELECT department, AVG(salary) AS AvgSalary

FROM Employees

GROUP BY department;

Explanation:
This creates a view that shows the average salary per department.

✅ Example 2: Modify (Alter) a View

ALTER VIEW DepartmentSalary AS

SELECT department, COUNT(emp_id) AS TotalEmployees, AVG(salary) AS AvgSalary

FROM Employees

GROUP BY department;

Explanation:
The altered view now includes an extra column TotalEmployees for each department.

✅ Example 3: Drop a View

DROP VIEW DepartmentSalary;

Explanation:
This removes the view from the database.

✏️ 4. Data Manipulation using Views

🔹 Definition:

Views can sometimes be updated, inserted into, or deleted from, just like tables — but only if the view is based on a single table and doesn’t use GROUP BY, DISTINCT, or JOIN.

✅ Example 1: Update through View

CREATE VIEW EmployeeDept AS

SELECT emp_id, emp_name, department

FROM Employees;

UPDATE EmployeeDept

SET department = 'Sales'

WHERE emp_id = 101;

Explanation:
This updates the department of employee 101 in both the view and the base table Employees.

✅ Example 2: Insert through View

CREATE VIEW EmployeeBasic AS

SELECT emp_id, emp_name, department

FROM Employees;

INSERT INTO EmployeeBasic (emp_id, emp_name, department)

VALUES (110, 'Ravi Kumar', 'HR');

Explanation:
Since this view is based on a single table without joins, the new record is inserted into the base table.

✅ Example 3: Delete through View

DELETE FROM EmployeeDept

WHERE emp_id = 105;

Explanation:
This deletes employee 105 from both the view and the base Employees table.

⚠️ Views that use GROUP BY, DISTINCT, or multiple tables cannot be updated directly.

⚙️ 5. SQL Support for Views

🔹 SQL supports the following operations on Views:

Operation	SQL Command	Description
Create a View	CREATE VIEW	Defines a new view
Modify a View	ALTER VIEW	Changes existing view structure
Remove a View	DROP VIEW	Deletes a view
Query a View	SELECT	Retrieve data like a normal table
Check View Definition	SHOW CREATE VIEW view_name;	See SQL definition

✅ Example 1: Query a View

SELECT * FROM SalesSummary WHERE TotalSales >50000;

Explanation:
Retrieves customers whose total sales exceed 50,000 from the view.

✅ Example 2: Check View Definition

SHOW CREATE VIEW SalesSummary;

Explanation:
Displays the SQL query used to create the view SalesSummary.

✅ Example 3: Drop a View

DROP VIEW IF EXISTS EmployeeView;

Explanation:
Removes the view safely — only if it already exists.

📘 Summary Table

Topic	Description	Example
Introduction	Virtual table showing data from query	CREATE VIEW v AS SELECT * FROM t;
Purpose	Simplify, secure, and organize data	Hiding salary data
Data Definition	Create, alter, drop view	CREATE VIEW, ALTER VIEW, DROP VIEW
Data Manipulation	Insert, update, delete via view	Only for simple single-table views
SQL Support	Commands for managing views	SHOW CREATE VIEW, etc.

What is SQL unit 3

Resources