DATABASE DAY-3

Salespers
ID Region Product Units Unit_Price Sale_Date
on
2024-01-
1 Amit North Pen 10 5.00
10
2024-01-
2 Rita South Notebook 5 15.00
11
2024-01-
3 John East Pencil 20 2.00
12
2024-01-
4 Meena West Eraser 15 1.50
13
2024-01-
5 Raj North Pen 12 5.00
15
2024-01-
6 Nita South Notebook 6 15.00
16
2024-01-
7 Arjun East Pencil 25 2.00
17
2024-01-
8 Tara West Eraser 10 1.50
18
2024-01-
9 Karan North Marker 8 10.00
19
2024-01-
10 Divya South Pen 7 5.00
20
2024-01-
11 Amit East Notebook 9 15.00
1. Total units sold per region.
2. Total revenue (Units × Unit_Price) by salesperson.
3. Average unit price of each product.
4. Highest number of units sold in a single transaction.
5. Regions with more than 3 sales transactions.
6. Salespeople who sold more than 15 units total.
7. Total revenue generated in the "South" region.
8. Product with the highest average unit price.
9. Count of transactions per product.
10. Minimum units sold by any salesperson.
11. Salespersons who sold more than average units.
12. Number of unique products sold in each region.
13. Region-wise total revenue sorted by highest to lowest.
14. Days with more than 20 units sold.
15. Who sold the most units of "Notebook"?
16. Average units per sale for each product.
17. Revenue per product (grouped).
18. List salespersons who sold "Marker".
19. Transactions where unit price > 5.
20. Top 3 regions by total units sold.
 GROUP BY : TEMPLATE
SELECT
column1, -- (must be in GROUP BY)

AGG_FUNC(column2), -- (like SUM, AVG, COUNT, etc.)

...

FROM
table_name

WHERE
condition -- (optional row filter)

GROUP BY
column1 -- (column used to group)

HAVING
condition_on_group -- (optional filter on aggregated results)

ORDER BY
... -- (optional sorting)

LIMIT n; -- (optional limit)

 SOLUTIONS
1) SELECT region, SUM(units) AS total_units
FROM sales_records
GROUP BY region;

2) SELECT salesperson,
SUM(units * unit_price) AS total_revenue
FROM sales_records
GROUP BY salesperson;

3) SELECT product, AVG(unit_price) AS avg_price

FROM sales_records
GROUP BY product;

4) SELECT MAX(units) AS max_units_sold

FROM sales_records;

5) SELECT region,
COUNT(*) AS transaction_count
FROM sales_records
GROUP BY region
HAVING COUNT(*) > 3;
 SOLUTIONS
6) SELECT salesperson,
SUM(units) AS total_units
FROM sales_records
GROUP BY salesperson
HAVING total_units > 15;

7) SELECT SUM(units * unit_price) AS south_revenue

FROM sales_records
WHERE region = 'South’;

8) SELECT product, AVG(unit_price) AS avg_price

FROM sales_records
GROUP BY product
ORDER BY avg_price DESC LIMIT 1;

9) SELECT product, COUNT(*) AS transactions

FROM sales_records
GROUP BY product;
 SOLUTIONS
10)SELECT salesperson,
SUM(units) AS total_units
FROM sales_records
GROUP BY salesperson
ORDER BY total_units ASC
LIMIT 1;

11) SELECT salesperson,

SUM(units) AS total_units
FROM sales_records
GROUP BY salesperson
HAVING total_units > (
SELECT AVG(units) FROM sales_records
);

12) SELECT region,

COUNT(DISTINCT product) AS unique_products
FROM sales_records
GROUP BY region;
 SOLUTIONS
13) SELECT region,
SUM(units * unit_price) AS total_revenue
FROM sales_records
GROUP BY region
ORDER BY total_revenue DESC;

14) SELECT sale_date, SUM(units) AS total_units

FROM sales_records
GROUP BY sale_date
HAVING total_units > 20;

15) SELECT salesperson, SUM(units) AS notebook_units

FROM sales_records
WHERE product = 'Notebook’
GROUP BY salesperson
ORDER BY notebook_units DESC
LIMIT 1;
 Departmen
ID Name Salary (₹) Age Join Date
t
1 Amit HR 50,000 28 2020-01-15

2 Rita IT 75,000 32 2019-03-22

3 John Finance 62,000 30 2021-07-01

4 Meena IT 78,000 29 2018-11-13

5 Raj HR 52,000 35 2022-04-10

6 Nita Finance 66,000 31 2020-08-25

7 Arjun IT 80,000 27 2021-03-05

8 Tara Marketing 55,000 26 2022-06-17

9 Karan Marketing 58,000 33 2019-09-09

10 Divya HR 54,000 30 2023-01-12

1. List all candidates who applied for the role Software Dev.
2. Show the names and expected salaries of candidates who expect more than ₹800,000.
3. Find all female candidates (gender = 'F') with less than 3 years of experience.
4. List candidates who applied after July 1, 2024.
5. Find all candidates whose name starts with 'S’.
6. Get the top 5 candidates with the highest expected salary.
7. Show all candidates who want to work in either Mumbai or Delhi.
8. List candidates aged between 25 and 30 who applied for the Tester role.
9. Find candidates whose qualification is either 'B.Tech' or 'M.Tech' and who have more than 5 years of experience.
10. Show all candidates sorted by their application_date in descending order.
11. Find the average expected salary for each job_role.
12. Count how many candidates applied for each location.
13. Find the total number of HR Manager applications.
14. Which job_role has the highest average expected salary?
15. Show the minimum, maximum, and average expected salary of all candidates.
16. Find candidates whose expected salary is above the average expected salary.
17. List candidates who have the maximum years of experience.
18. Find the candidate(s) who applied for a role with the least number of total applicants.
19. Write a query that adds a new column experience_level: 'Junior' if experience < 3, 'Mid' if between 3 and 6, 'Senior' if
> 6.20.
20. Find all candidates where their expected salary is more than 10x their experience (expected_salary >
 Degree Of Relationship
Relationships define how tables are connected through primary
and foreign keys. The degree (or type) of relationship tells us how
many records in one table are related to records in another.
One-to-One (1:1)
Definition:
One record in Table A is related to exactly one record in Table B.
Person (parent)
person_id PK name date_of_birth
1 Alice 1995-03-12
2 Bob 1990-07-25

Passport (child)
person_id
passport_id PK FK→Person.person_i issue_date
d
P-1001 1 2022-05-10
P-1002 2 2021-11-18
 One-to-Many (1:N)
Definition:
One record in Table A is related to many records in Table B.
Example:
•One customer can place many orders
•Each order belongs to one customer
Most common relationship type

One-to-Many (1 : N) — Customer → Orders

Customer (parent)
customer_id PK name
10 Alice
11 Bob

Orders (child)
customer_id
order_id PK FK→Customer.c date amount
ustomer_id
5001 10 2025-05-10 ₹1 200
5002 10 2025-05-12 ₹ 650
5003 11 2025-05-13 ₹2 050
Many-to-One (N:1)
Definition:
Many records in Table A relate to one record in Table B.
This is basically the reverse of One-to-Many
•Many orders can belong to one customer
Practically the same as 1:N, just viewed from the child table side.
A single customer can place many orders; each order belongs to one
customer.
Orders (many)
customer_id
order_id PK FK→Customer.c date amount
ustomer_id
5001 10 2025-05-10 ₹1 200
5002 10 2025-05-12 ₹ 650
5003 11 2025-05-13 ₹2 050
Orders (child)
customer_id
order_id PK FK→Customer.c date amount
ustomer_id
5001 10 2025-05-10 ₹1 200
5002 10 2025-05-12 ₹ 650
5003 11 2025-05-13 ₹2 050
Many-to-One (N:1)
Definition:
Many records in Table A relate to one record in Table B.
✅ This is basically the reverse of One-to-Many
Many orders can belong to one customer
✅ Practically the same as 1:N, just viewed from the child table side.
Many orders map back to a single customer.
Orders (many)
customer_id
order_id PK FK→Customer.custome date amount
r_id
2025-
5001 10 ₹1 200
05-10
2025-
5002 10 ₹ 650
05-12
2025-
5003 11 ₹2 050
Customer (one) 05-13

customer_id PK name
10 Alice
11 Bob
A Many-to-Many relationship occurs when:
One record in Table A can be related to many records in Table B,
and one record in Table B can be related to many records in Table A.

Example: Students & Courses

Scenario:
•A student can enroll in many courses
•A course can have many students
So we cannot store this directly using just two tables. We need a third table to
connect them.
Student Table
student_id (PK) name
101 Alice
102 Bob
103 Carol

Courses Table
course_id (PK) course_name
C1 Database Systems
C2 Web Development
C3 Python Programming
Student_Courses Table (Join Table)

This third table is called a junction table or bridge table.

It breaks the many-to-many into two one-to-many relationships:

Students → Student_Courses

Courses → Student_Courses

student_id (FK) course_id (FK)

101 C1
101 C2
102 C1
103 C1
103 C3
JOINS
INNER JOINS
Returns only rows that have matching values in both tables.

students

student_id name
1 Raj
2 Simran
3 Ayaan
4 Neha

enrollments

student_id course
1 Python
2 Java
4 Web Development
SELECT students.name, enrollments.course
FROM students
INNER JOIN enrollments ON students.student_id =
enrollments.student_id;

• Ignores unmatched rows.

• Most commonly used join.
• Result is usually smaller than the source tables.
TrainerID Trainer Course
1 Abhinav AI/ML
2 Ashish DS
3 Srajan DBMS/Data Analysis

StudentID Name TrainerID

1 Ram 1
2 Mohan 3
3 Ravi 2
TrainerI Name Course Student Name CourseID
D ID
1 Abhinav AI/ML 1 Ram 1
1 Abhinav AI/ML 2 Mohan 3
1 Abhinav AI/ML 3 Ravi 2
2 Ashish DS 1 Ram 1
2 Ashish DS 2 Mohan 3
2 Ashish DS 3 Ravi 2
3 Srajan DBMS/Data 1 Ram 1
Analysis
3 Srajan DBMS/Data 2 Mohan 3
Analysis
3 Srajan DBMS/Data 3 Ravi 2
Analysis
TrainerI Name Course Student Name TrainerID
D ID
1 Abhinav AI/ML 1 Ram 1
2 Ashish DS 2 Mohan 3
3 Srajan DBMS/Data 3 Ravi 2
Analysis
 INNER JOINS TASKS
1. Show all student names with their trainer names.
2. List students and the courses they are enrolled in .
3. Show student name, course name, and trainer name.
4. Find which trainer is assigned to student 'Ram’.
5. Find which course 'Mohan' is learning.
6. Get the total number of students under each trainer.
7. Show all students learning from 'Ashish’.
8. List courses that have more than 1 student enrolled.
9. List student names and their Trainer IDs.
10. Show all student-trainer pairs where trainer teaches "DS".
Customer

CustomerID CustomerName Email City

101 Rahul Sharma [email protected] Delhi
m
102 Anita Mehra [email protected] Mumbai

103 John Smith johnsmith@yaho New York

o.com
104 Ayesha Khan [email protected] Bangalore
om
105 Peter Parker peterparker@ma New York
rvel.com
106 Ravi Kumar ravi.kumar@gma Chennai
il.com
107 Sunita Joshi [email protected] Pune
om
Order Info

OrderID CustomerID OrderDate Amount Status

1001 101 2024-12-01 1500 Delivered
1002 102 2024-12-03 2200 Pending
1003 101 2024-12-10 800 Delivered
1004 103 2024-12-12 4500 Cancelled
1005 105 2024-12-14 1300 Delivered
1006 104 2024-12-15 2700 Shipped
1007 106 2024-12-16 500 Delivered
1008 107 2024-12-17 3300 Pending
1009 101 2024-12-20 2500 Delivered
1010 104 2024-12-22 1900 Shipped
CustomerI CustomerNa
Email City OrderID OrderDate Amount Status
D me

101 Rahul Sharma [email protected] Delhi 1001 2024-12-01 1500 Delivered

101 Rahul Sharma [email protected] Delhi 1003 2024-12-10 800 Delivered

101 Rahul Sharma [email protected] Delhi 1009 2024-12-20 2500 Delivered

102 Anita Mehra [email protected] Mumbai 1002 2024-12-03 2200 Pending

103 John Smith [email protected] New York 1004 2024-12-12 4500 Cancelled

104 Ayesha Khan [email protected] Bangalore 1006 2024-12-15 2700 Shipped

104 Ayesha Khan [email protected] Bangalore 1010 2024-12-22 1900 Shipped

105 Peter Parker [email protected] New York 1005 2024-12-14 1300 Delivered
m

106 Ravi Kumar [email protected] Chennai 1007 2024-12-16 500 Delivered

107 Sunita Joshi [email protected] Pune 1008 2024-12-17 3300 Pending

Basic Queries
1.List all customers and their orders.
2.Show the names and emails of customers who placed an order.
3.Find all customers from ‘New York’.
4.Find the total number of orders placed by each customer.

Aggregate Queries
6.Get the total amount spent by each customer.
7.Find the average order amount for each customer.
8.Find the highest order amount and the customer who placed
it.
9.Count how many orders have status 'Delivered'.
10.Show the total amount of all ‘Pending’ orders.
Filter + Join
11.List customer names who have placed orders with amount > 2000.
12.Show names of customers who have more than one order.
13.Find customers who have not placed any orders.
14.List all orders along with customer name, email, and status.
15.Find customers who placed orders but live outside ‘Delhi’ and
‘Mumbai’.
LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table, and matched rows from the
right. If no match, right side has NULL.
Use to include all records from main table, even if not related.

SELECT s.name, e.course

FROM students as s
LEFT JOIN enrollments as e ON s.student_id =
e.student_id;
 student_id course
student_id name
1 Python
1 Raj
2 Java
2 Simran
4 Web Dev
3 Ayaan
5 C++
4 Neha

Result Table

name course
Raj Python
Simran Java
Ayaan NULL
Neha Web Dev
Basic LEFT JOIN Questions
1.List all products along with their category names.
2.Find products that do not belong to any category.
3.List all category names with their products (even
if no product exists).
4.Count how many products are in each category.
5.Find all categories that have no products.
Intermediate Practice
6.List product names with category names sorted
by category.
7.Show all products and replace missing category
name with "Uncategorized".
8.Show total number of products, including those
without category.
9.List categories with at least 2 products.
10.Find products whose names contain the word
"Book".
Advanced Practice
11.Add a price column to products, insert values, and find average price per category.
12.Show products grouped by category, and count how many are uncategorized.
13.Find all categories and show how many products they have, including 0.
14.List all unique categories used in the products table.
RIGHT JOIN (Right Outer Join)
• Returns all rows from the right table,
and matched rows from the left. If no
match, left side has NULL.
• When to Use:
• To keep everything from secondary table,
even if not related.Example: All products,
even if not purchased.
• Reverse of LEFT JOIN.Not used often—can
usually be swapped by flipping tables in
LEFT JOIN.Highlights records that lack
corresponding data on the left.
 students
student_id student_name club_id
1 Alice 201
2 Bob 202
3 Charlie NULL
4 David 203
5 Eva 201
6 Farhan 204

clubs

club_id club_name
201 Robotics Club
202 Drama Club
203 Sports Club
204 Music Club
205 Debate Club
Basic RIGHT JOIN Usage
1.List all clubs and the names of students who joined them.
2.Display all club names, even if no student is assigned.
3.Show student names and their corresponding club names using
RIGHT JOIN.
4.Find clubs that do not have any students joined.
5.Show all clubs and if no student is joined, display "No Member"
in student_name.
filtering with RIGHT JOIN
6.List all clubs where the club name starts with 'D' and show their student
members.
7.Find the club(s) where more than one student has joined (using RIGHT JOIN
with GROUP BY).
8.List all student-club pairs where the student name starts with 'A' or 'E'.
9.Show all clubs and students, but only include clubs with 'Club' in the name.
Aggregations
10.Display with
clubs RIGHT
and JOIN where the student name is not null (i.e., only
members
11.Count
joined the number of students in each club using RIGHT JOIN.
ones).
12.Show each club and the average student ID of members.
13.Find clubs with the least number of students joined.

Output Formatting with RIGHT JOIN

14.List club names and student names. If a student hasn’t joined, display
"None".
Full Outer Join
Returns all rows from both tables. Matched rows appear once, unmatched rows get NULLs.

SELECT s.name, e.course

FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
UNION
SELECT s.name, e.course
FROM students s
RIGHT JOIN enrollments e ON s.student_id = e.student_id;

When to Use:
1.To combine all data from both tables.
2.To detect all unmatched records.
3.For synchronization or merging data.

name course
Raj Python
Simran Java
Neha Web Dev
Ayaan NULL
NULL C++
Self Join
A table joined with itself to compare rows.

SELECT A.name AS student1, B.name AS student2

FROM students A
JOIN students B ON A.student_id != B.student_id;

When to Use:
1.To compare students to each other.
2.For peer or team matching.
3.In referral or friend systems.

student1 student2
Raj Simran
Raj Ayaan
Raj Neha
Simran Raj
Simran Ayaan
... ...
 employees

emp_id emp_name manager_id

1 Alice NULL
2 Bob 1
3 Charlie 1
4 David 2
5 Eva 2
6 Frank 3
7 Grace NULL

Practice Question Ideas (SELF JOIN)

1.Show each employee and their manager.
2.Find employees who do not have a manager.
3.List all managers and count how many employees report to them.
4.Show a hierarchy list: employee → manager → manager's manager.
5.Find employees who manage someone but also report to another manager.