Students & Courses
Display student name and course name.
SELECT s.student_name, c.course_name
FROM students s
JOIN courses c
ON s.course_id = c.course_id;
Output:
student_name | course_name
--------------+------------------
Viyan | Java
Kavin | Python
Mathi | Java
Arul | Software Testing
(4 rows)
Display only students who are enrolled in Java.
SELECT s.student_name
FROM students s
JOIN courses c
ON s.course_id = c.course_id
WHERE c.course_name = 'Java';
Output:
student_name
--------------
Viyan
Mathi
(2 rows)
Display students enrolled in Python.
SELECT s.student_name
FROM students s
JOIN courses c
ON s.course_id = c.course_id
WHERE c.course_name = 'Python';
Output:
student_name
--------------
Kavin
(1 row)
Display student name and course name ordered by student name.
SELECT s.student_name, c.course_name
FROM students s
JOIN courses c
ON s.course_id = c.course_id
ORDER BY student_name;
Output:
student_name | course_name
--------------+------------------
Arul | Software Testing
Kavin | Python
Mathi | Java
Viyan | Java
(4 rows)
Level 2 — Employee & Department
Display employee name and department name.
SELECT e.emp_name, d.dept_name
FROM employeesTab e
JOIN departments d
ON e.dept_id = d.dept_id;
Output:
emp_name | dept_name
-----------+-----------
SARAVANAN | IT
SAMUEL | AI
RAJ | MIS
KAMAL | AI
(4 rows)
Display employees working in IT.
SELECT e.emp_name
FROM employeesTab e
JOIN departments d
ON e.dept_id = d.dept_id
WHERE dept_name = 'IT';
Output:
emp_name
-----------
SARAVANAN
(1 row)
Display employees earning more than Rs. 50,000 along with their department.
SELECT e.emp_name, d.dept_name
FROM employeesTab e
JOIN departments d
ON e.dept_id = d.dept_id
WHERE salary > 50000;
Output:
emp_name | dept_name
----------+-----------
SAMUEL | AI
RAJ | MIS
(2 rows)
Display employees sorted by department name.
SELECT e.emp_name
FROM employeesTab e
JOIN departments d
ON e.dept_id = d.dept_id
ORDER BY dept_name;
Output:
emp_name
-----------
SAMUEL
KAMAL
SARAVANAN
RAJ
(4 rows)
Display employee name, salary and department name.
SELECT e.emp_name, e.salary, d.dept_name
FROM employeesTab e
JOIN departments d
ON e.dept_id = d.dept_id;
Output:
emp_name | salary | dept_name
-----------+----------+-----------
SARAVANAN | 35000.00 | IT
SAMUEL | 55000.00 | AI
RAJ | 52000.00 | MIS
KAMAL | 30000.00 | AI
(4 rows)
3. Products & Categories
Display product name and category name.
SELECT p.product_name, c.category_name
FROM productsTab p
JOIN categories c
ON p.category_id = c.category_id;
Output:
product_name | category_name
-------------------------+-----------------
Wireless Mouse | Electronics
Bluetooth Speaker | Electronics
Dell Laptop | Laptops
HP Laptop | Laptops
iPhone 15 | Mobile Phones
Samsung Galaxy | Mobile Phones
Office Chair | Furniture
Computer Table | Furniture
Java Programming Book | Books
Python Programming Book | Books
Air Conditioner | Home Appliances
Microwave Oven | Home Appliances
Display product name, price and category name.
SELECT p.product_name, p.price, c.category_name
FROM productsTab p
JOIN categories c
ON p.category_id = c.category_id;
Output:
product_name | price | category_name
-------------------------+----------+-----------------
Wireless Mouse | 799.00 | Electronics
Bluetooth Speaker | 1499.00 | Electronics
Dell Laptop | 65000.00 | Laptops
HP Laptop | 58000.00 | Laptops
iPhone 15 | 65000.00 | Mobile Phones
Samsung Galaxy | 42000.00 | Mobile Phones
Office Chair | 7500.00 | Furniture
Computer Table | 12000.00 | Furniture
Java Programming Book | 850.00 | Books
Python Programming Book | 950.00 | Books
Air Conditioner | 45000.00 | Home Appliances
Microwave Oven | 12000.00 | Home Appliances
(12 rows)
Display product name, category name and stock.
SELECT p.product_name, c.category_name, p.stock
FROM productsTab p
JOIN categories c
ON p.category_id = c.category_id;
Output:
product_name | category_name | stock
-------------------------+-----------------+-------
Wireless Mouse | Electronics | 50
Bluetooth Speaker | Electronics | 25
Dell Laptop | Laptops | 10
HP Laptop | Laptops | 8
iPhone 15 | Mobile Phones | 15
Samsung Galaxy | Mobile Phones | 20
Office Chair | Furniture | 12
Computer Table | Furniture | 5
Java Programming Book | Books | 30
Python Programming Book | Books | 20
Air Conditioner | Home Appliances | 7
Microwave Oven | Home Appliances | 9
(12 rows)
Display products belonging to Electronics.
SELECT p.product_name
FROM productsTab p
JOIN categories c
ON p.category_id = c.category_id
WHERE c.category_name = 'Electronics';
Output:
product_name
-------------------
Wireless Mouse
Bluetooth Speaker
(2 rows)
Display products whose price is greater than Rs. 50,000.
SELECT p.product_name
FROM productsTab p
JOIN categories c
ON p.category_id = c.category_id
WHERE p.price > 50000;
Output:
product_name
--------------
Dell Laptop
HP Laptop
iPhone 15
(3 rows)
Display products from Laptops category costing more than Rs. 60,000.
SELECT p.product_name
FROM productsTab p
JOIN categories c
ON p.category_id = c.category_id
WHERE c.category_name = 'Laptops' AND p.price > 60000;
Output:
product_name
--------------
Dell Laptop
(1 row)
Display products whose stock is less than 10.
SELECT p.product_name
FROM productsTab p
JOIN categories c
ON p.category_id = c.category_id
WHERE p.stock < 10;
Output:
product_name
-----------------
HP Laptop
Computer Table
Air Conditioner
Microwave Oven
(4 rows)
Display products from Books category with price less than Rs. 1,000.
SELECT p.product_name
FROM productsTab p
JOIN categories c
ON p.category_id = c.category_id
WHERE c.category_name = 'Books' AND p.price < 10000;
Output:
product_name
-------------------------
Java Programming Book
Python Programming Book
(2 rows)
Display all products from Mobile Phones category costing more than Rs. 40,000.
SELECT p.product_name
FROM productsTab p
JOIN categories c
ON p.category_id = c.category_id
WHERE c.category_name = 'Mobile Phones' AND p.price > 40000;
Output:
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
Level 2 — Products & Categories
Display products from Electronics costing more than Rs. 1,000.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Electronics'and p.price > 1000;
Output:
product_name
-------------------
Bluetooth Speaker
(1 row)
Display products from Furniture costing less than Rs. 10,000.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Furniture'and p.price < 10000;
Output:
product_name
--------------
Office Chair
(1 row)
Display products from Mobile Phones having stock greater than 10.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Mobile Phones'and p.stock > 10;
Output:
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
Display products from Home Appliances costing more than Rs. 20,000.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Home Appliances'and p.price > 20000;
Output:
product_name
-----------------
Air Conditioner
(1 row)
Display products whose price is between Rs. 10,000 and Rs. 50,000, along with their category.
select p.product_name, c.category_name
from productsTab p
join categories c
on p.category_id = c.category_id
where p.price between 10000 and 50000;
Output:
product_name | category_name
-----------------+-----------------
Samsung Galaxy | Mobile Phones
Computer Table | Furniture
Air Conditioner | Home Appliances
Microwave Oven | Home Appliances
(4 rows)
Display products from Books where stock is greater than 20.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Books' and p.stock > 20;
Output:
product_name
-----------------------
Java Programming Book
(1 row)
Display products from Laptops where price is less than Rs.60,000
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Laptops' and p.stock < 60000;
Output:
product_name
--------------
Dell Laptop
HP Laptop
(2 rows)
Level 3
Display products whose price is greater than ₹40,000 and stock is greater than 10.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where p.price > 40000 and p.stock > 10;
Output:
product_name
----------------
Samsung Galaxy
iPhone 15
(2 rows)
Display products belonging to either Laptops or Mobile Phones.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name = 'Laptops' or c.category_name = 'Mobile Phones';
Output:
product_name
----------------
Dell Laptop
HP Laptop
iPhone 15
Samsung Galaxy
(4 rows)
Display products belonging to either Books or Electronics, with price greater than ₹1,000.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name IN ('Books' , 'Electronics')
and p.price > 1000;
Output:
product_name
-------------------
Bluetooth Speaker
(1 row)
Display products whose category is not Books.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name NOT IN ('Books');
Output:
product_name
-------------------
Wireless Mouse
Bluetooth Speaker
Dell Laptop
HP Laptop
iPhone 15
Samsung Galaxy
Office Chair
Computer Table
Air Conditioner
Microwave Oven
(10 rows)
Display products whose category name starts with 'M'.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name LIKE 'M%';
Output:
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
Display products whose category name contains the word 'Phone'.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where c.category_name LIKE '%Phone%';
Output:
product_name
----------------
iPhone 15
Samsung Galaxy
(2 rows)
Display products priced between ₹10,000 and ₹60,000 and having stock greater than 8.
select p.product_name
from productsTab p
join categories c
on p.category_id = c.category_id
where (p.price between 10000 and 60000)
and p.stock > 8;
Output:
product_name
----------------
Samsung Galaxy
Microwave Oven
(2 rows)
Level 4: Students → Student Courses → Trainers → Courses
Level 1: Difficulty Level: Easy
1. Display the course name and trainer name for every course.
select c.course_name, t.trainer_name
from course c join trainers t
on c.trainer_id = t.trainer_id;
Output:
course_name | trainer_name
------------------+--------------
Python | Vijay
Java | Muthu
PostgreSQL | Arun
Spring Boot | Muthu
React | Prithvi
Software Testing | Vallarasu
AWS | Vijay
(7 rows)
2. Display:
- Course Name
- Trainer Name
- Trainer Specialization
select c.course_name, t.trainer_name, t.specialization
from course c join trainers t
on c.trainer_id = t.trainer_id;
Output:
course_name | trainer_name | specialization
------------------+--------------+--------------------
Python | Vijay | Python & AWS
Java | Muthu | Java & Spring Boot
PostgreSQL | Arun | PostgreSQL
Spring Boot | Muthu | Java & Spring Boot
React | Prithvi | React
Software Testing | Vallarasu | Software Testing
AWS | Vijay | Python & AWS
(7 rows)
3. Display all courses taught by Vijay.
select c.course_name, t.trainer_name
from course c join trainers t
on c.trainer_id = t.trainer_id
where trainer_name = 'Vijay';
Output:
course_name | trainer_name
-------------+--------------
Python | Vijay
AWS | Vijay
(2 rows)
4. Display all courses taught by trainers having more than 5 years of experience.
select c.course_name from course c join trainers t
on c.trainer_id = t.trainer_id
where experience_years > 5;
Output:
course_name
------------------
Python
Java
PostgreSQL
Spring Boot
Software Testing
AWS
(6 rows)
5. Display the course name and fee along with the trainer name.
select c.course_name, c.fee, t.trainer_name
from course c join trainers t
on c.trainer_id = t.trainer_id;
Output:
course_name | fee | trainer_name
------------------+----------+--------------
Python | 25000.00 | Vijay
Java | 30000.00 | Muthu
PostgreSQL | 20000.00 | Arun
Spring Boot | 28000.00 | Muthu
React | 22000.00 | Prithvi
Software Testing | 18000.00 | Vallarasu
AWS | 32000.00 | Vijay
(7 rows)
Level 2 — Student + Course
Difficulty: Easy
6. Display the student name and course name for every enrollment.
select s.student_name, c.course_name
from student s join student_courses sc
on s.student_id = sc.student_id
join course c
on c.course_id = sc.course_id;
Output:
student_name | course_name
--------------+------------------
Arul | Python
Arul | AWS
Kavin | Java
Venba | PostgreSQL
Venba | Spring Boot
Kanmani | React
Mathi | Software Testing
Paari | Python
Paari | Spring Boot
Kathir | Java
Seyon | React
(11 rows)
7. Display:
- Student Name
- City
- Course Name
- Enrollment Date
select s.student_name, s.city, c.course_name, sc.enrolled_date
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on c.course_id = sc.course_id;
Output:
student_name | city | course_name | enrolled_date
--------------+------------+------------------+---------------
Arul | Chennai | Python | 2026-01-10
Arul | Chennai | AWS | 2026-02-15
Kavin | Madurai | Java | 2026-01-12
Venba | Chennai | PostgreSQL | 2026-01-15
Venba | Chennai | Spring Boot | 2026-02-01
Kanmani | Coimbatore | React | 2026-01-20
Mathi | Salem | Software Testing | 2026-02-05
Paari | Chennai | Python | 2026-01-25
Paari | Chennai | Spring Boot | 2026-02-10
Kathir | Trichy | Java | 2026-02-12
Seyon | Coimbatore | React | 2026-02-20
(11 rows)
8. Find all courses taken by Muthu.
select c.course_name
from course c
join trainers t
on c.trainer_id = t.trainer_id
where trainer_name = 'Muthu';
Output:
course_name
-------------
Java
Spring Boot
(2 rows)
9. Find all students who have enrolled in Java.
select s.student_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on c.course_id = sc.course_id
where course_name = 'Java';
Output:
student_name
--------------
Kavin
Kathir
(2 rows)
10. Find all students from Chennai and the courses they have enrolled in.
select s.student_name, c.course_name
from student s join student_courses sc
on s.student_id = sc.student_id
join course c
on c.course_id = sc.course_id
where s.city = 'Chennai';
Output:
student_name | course_name
--------------+-------------
Arul | Python
Arul | AWS
Venba | PostgreSQL
Venba | Spring Boot
Paari | Python
Paari | Spring Boot
(6 rows)
Level 3 - Three/Four Table JOIN
Difficulty Level: Intermediate
11. Display Student Name, Course Name and Trainer Name
select s.student_name, c.course_name, t.trainer_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on c.course_id = sc.course_id
join trainers t
on c.trainer_id = t.trainer_id;
Output:
student_name | course_name | trainer_name
--------------+------------------+--------------
Arul | Python | Vijay
Arul | AWS | Vijay
Kavin | Java | Muthu
Venba | PostgreSQL | Arun
Venba | Spring Boot | Muthu
Kanmani | React | Prithvi
Mathi | Software Testing | Vallarasu
Paari | Python | Vijay
Paari | Spring Boot | Muthu
Kathir | Java | Muthu
Seyon | React | Prithvi
(11 rows)
12. Display Student Name, Student City, Course Name, Trainer Name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on c.course_id = sc.course_id
join trainers t
on c.trainer_id = t.trainer_id;
Output:
student_name | city | course_name | trainer_name
--------------+------------+------------------+--------------
Arul | Chennai | Python | Vijay
Arul | Chennai | AWS | Vijay
Kavin | Madurai | Java | Muthu
Venba | Chennai | PostgreSQL | Arun
Venba | Chennai | Spring Boot | Muthu
Kanmani | Coimbatore | React | Prithvi
Mathi | Salem | Software Testing | Vallarasu
Paari | Chennai | Python | Vijay
Paari | Chennai | Spring Boot | Muthu
Kathir | Trichy | Java | Muthu
Seyon | Coimbatore | React | Prithvi
(11 rows)
13. Display Student Name, Course Name, Trainer Name, Trainer Specialization
select s.student_name, c.course_name, t.trainer_name, t.specialization
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on c.course_id = sc.course_id
join trainers t
on c.trainer_id = t.trainer_id;
Output:
student_name | course_name | trainer_name | specialization
--------------+------------------+--------------+--------------------
Arul | Python | Vijay | Python & AWS
Arul | AWS | Vijay | Python & AWS
Kavin | Java | Muthu | Java & Spring Boot
Venba | PostgreSQL | Arun | PostgreSQL
Venba | Spring Boot | Muthu | Java & Spring Boot
Kanmani | React | Prithvi | React
Mathi | Software Testing | Vallarasu | Software Testing
Paari | Python | Vijay | Python & AWS
Paari | Spring Boot | Muthu | Java & Spring Boot
Kathir | Java | Muthu | Java & Spring Boot
Seyon | React | Prithvi | React
(11 rows)
14. Find all students who are learning courses handled by Vallarasu.
select s.student_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
where t.trainer_name = 'Vallarasu';
Output:
student_name
--------------
Mathi
(1 row)
15. Find all students who are learning Python, along with their trainer's name.
select s.student_name, t.trainer_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on c.course_id = sc.course_id
join trainers t
on t.trainer_id = c.trainer_id
where c.course_name = 'Python';
Output:
student_name | trainer_name
--------------+--------------
Arul | Vijay
Paari | Vijay
(2 rows)
16. Find all students who are learning courses taught by trainers having more than 7 years of experience.
select s.student_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
where t.experience_years > 7;
Output:
student_name
--------------
Arul
Arul
Kavin
Venba
Paari
Paari
Kathir
(7 rows)
Level 4 - JOIN + WHERE
Difficulty Level: Intermediate
17. Display students from Chennai who are learning Python.
select s.student_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
where s.city = 'Chennai' and c.course_name = 'Python';
Output:
student_name
--------------
Arul
Paari
(2 rows)
18. Display students from Coimbatore along with their course and trainer.
select s.student_name, c.course_name, t.trainer_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
where s.city = 'Coimbatore';
Output:
student_name | course_name | trainer_name
--------------+-------------+--------------
Kanmani | React | Prithvi
Seyon | React | Prithvi
(2 rows)
19. Find courses costing more than Rs. 25,000 and display their trainers.
SELECT c.course_name, t.trainer_name
FROM course c
JOIN trainers t
ON c.trainer_id = t.trainer_id
WHERE c.fee > 25000;
Output:
course_name | trainer_name
-------------+--------------
Java | Muthu
Spring Boot | Muthu
AWS | Vijay
(3 rows)
20. Find students enrolled in courses costing more than Rs. 25,000.
select s.student_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
where c.fee > 25000;
Output:
student_name
--------------
Arul
Kavin
Venba
Paari
Kathir
(5 rows)
21. Find students learning courses taught by trainers with at least 8 years of experience.
select s.student_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
where t.experience_years >= 8;
Output:
student_name
--------------
Arul
Arul
Kavin
Venba
Paari
Paari
Kathir
(7 rows)
22. Find all students from Chennai who are learning courses costing more than Rs.20,000.
select s.student_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
where c.fee > 20000 and s.city = 'Chennai';
Output:
student_name
--------------
Arul
Arul
Venba
Paari
Paari
(5 rows)
Level 5 - JOIN + ORDER BY
Difficulty Level: Easy
23. Display all students, their courses and trainers, sorted by student name.
select s.student_name, c.course_name, t.trainer_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
order by s.student_name;
Output:
student_name | course_name | trainer_name
--------------+------------------+--------------
Arul | Python | Vijay
Arul | AWS | Vijay
Kanmani | React | Prithvi
Kathir | Java | Muthu
Kavin | Java | Muthu
Mathi | Software Testing | Vallarasu
Paari | Spring Boot | Muthu
Paari | Python | Vijay
Seyon | React | Prithvi
Venba | Spring Boot | Muthu
Venba | PostgreSQL | Arun
(11 rows)
24. Display all courses and trainers, sorted by course fee from highest to lowest.
select c.course_name, t.trainer_name
from course c
join trainers t
on c.trainer_id = t.trainer_id
order by c.fee desc;
Output:
course_name | trainer_name
------------------+--------------
AWS | Vijay
Java | Muthu
Spring Boot | Muthu
Python | Vijay
React | Prithvi
PostgreSQL | Arun
Software Testing | Vallarasu
(7 rows)
25. Display students and their courses, sorted by enrollment date.
select s.student_name, c.course_name
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
order by sc.enrolled_date;
Output:
student_name | course_name
--------------+------------------
Arul | Python
Kavin | Java
Venba | PostgreSQL
Kanmani | React
Paari | Python
Venba | Spring Boot
Mathi | Software Testing
Paari | Spring Boot
Kathir | Java
Arul | AWS
Seyon | React
(11 rows)
26. Display courses with their trainers, sorted by trainer experience from highest to lowest.
select c.course_name, t.trainer_name
from course c
join trainers t
on c.trainer_id = t.trainer_id
order by t.experience_years desc;
Output:
course_name | trainer_name
------------------+--------------
Spring Boot | Muthu
Java | Muthu
Python | Vijay
AWS | Vijay
Software Testing | Vallarasu
PostgreSQL | Arun
React | Prithvi
(7 rows)
Level 6 - JOIN + GROUP BY
Difficulty Level: Easy
27. Find the number of students enrolled in each course.
select c.course_name, count(s.student_name) "student_count"
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
group by c.course_name;
Output:
course_name | student_count
------------------+---------------
Python | 2
Software Testing | 1
React | 2
PostgreSQL | 1
Spring Boot | 2
AWS | 1
Java | 2
(7 rows)
28. Find the number of students handled by each trainer.
select t.trainer_name, count(s.student_name) "student_count"
from student s
join student_courses sc
on s.student_id = sc.student_id
join course c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
group by t.trainer_name;
Output:
trainer_name | student_count
--------------+---------------
Muthu | 4
Prithvi | 2
Vijay | 3
Arun | 1
Vallarasu | 1
(5 rows)
29. Find the number of courses handled by each trainer.
select t.trainer_name, count(c.course_name)
from trainers t
join course c
on t.trainer_id = c.trainer_id
group by t.trainer_name;
Output:
trainer_name | count
--------------+-------
Muthu | 2
Prithvi | 1
Vijay | 2
Arun | 1
Vallarasu | 1
(5 rows)
30. Find trainers who are handling more than one course.
select t.trainer_name, count(c.course_name)
from trainers t
join course c
on t.trainer_id = c.trainer_id
group by t.trainer_name
having count(c.course_name)>1;
Output:
trainer_name | count
--------------+-------
Muthu | 2
Vijay | 2
(2 rows)
31. Find courses having more than one student.
select c.course_name, count(s.student_name) "student_count"
from course c
join student_courses sc
on c.course_id = sc.course_id
join student s
on sc.student_id = s.student_id
group by c.course_name
having count(s.student_name)>1;
Output:
course_name | student_count
-------------+---------------
Python | 2
React | 2
Spring Boot | 2
Java | 2
(4 rows)
32. Find the total course fee of all courses handled by each trainer.
select t.trainer_name, sum(c.fee)
from trainers t
join course c
on t.trainer_id = c.trainer_id
group by t.trainer_name;
Output:
trainer_name | sum
--------------+----------
Muthu | 58000.00
Prithvi | 22000.00
Vijay | 57000.00
Arun | 20000.00
Vallarasu | 18000.00
(5 rows)
Level 7 - LEFT JOIN
Now introduce students who haven't enrolled in any course.
For this, insert as below first:
INSERT INTO students (student_name, email, city) VALUES
('Vijay', 'vijay@gmail.com', 'Chennai'),
('Anitha', 'anitha@gmail.com', 'Madurai');
33. Display all students, including students who haven't enrolled in any course.
select s.student_name, c.course_name
from student s
left join student_courses sc
on s.student_id = sc.student_id
left join course c
on sc.course_id = c.course_id;
Output:
student_name | course_name
--------------+------------------
Arul | Python
Arul | AWS
Kavin | Java
Venba | PostgreSQL
Venba | Spring Boot
Kanmani | React
Mathi | Software Testing
Paari | Python
Paari | Spring Boot
Kathir | Java
Seyon | React
Anitha |
Vijay |
(13 rows)
34. Find students who have not enrolled in any course.
select s.student_name, c.course_name
from student s
left join student_courses sc
on s.student_id = sc.student_id
left join course c
on sc.course_id = c.course_id
where c.course_name is null;
Output:
student_name | course_name
--------------+-------------
Anitha |
Vijay |
(2 rows)
35. Display all courses, including courses that currently have no students.
select c.course_name, count(s.student_name)
from course c
left join student_courses sc
on c.course_id = sc.course_id
left join student s
on sc.student_id = s.student_id
group by c.course_name;
Output:
course_name | count
------------------+-------
Python | 2
Software Testing | 1
React | 2
PostgreSQL | 1
Spring Boot | 2
AWS | 1
Java | 2
(7 rows)
36. Display all trainers, including trainers who are currently not teaching any course.
select t.trainer_name, c.course_name
from trainers t
left join course c
on t.trainer_id = c.trainer_id;
Output:
trainer_name | course_name
--------------+------------------
Vijay | Python
Muthu | Java
Arun | PostgreSQL
Muthu | Spring Boot
Prithvi | React
Vallarasu | Software Testing
Vijay | AWS
Siva |
(8 rows)
37. Find trainers who are not assigned to any course.
select t.trainer_name, c.course_name
from trainers t
left join course c
on t.trainer_id = c.trainer_id
where c.course_name is null;
Output:
trainer_name | course_name
--------------+-------------
Siva |
(1 row)
Level 8 – DIFFICULTY LEVEL: HARD
38. Find the trainer who teaches the maximum number of students.
(I tried with sub-query concept, but it didn't work)
select t.trainer_name, max(count)
from (select t.trainer_name, count(s.student_name)
from trainers t
join course c
on t.trainer_id = c.trainer_id
join student_courses sc
on c.course_id = sc.course_id
join student s
on sc.student_id = s.student_id
group by t.trainer_name
);
(Referred Online)
SELECT t.trainer_name, COUNT(s.student_id) AS student_count
FROM trainers t
JOIN course c
ON t.trainer_id = c.trainer_id
JOIN student_courses sc
ON c.course_id = sc.course_id
JOIN student s
ON sc.student_id = s.student_id
GROUP BY t.trainer_name
ORDER BY student_count DESC
LIMIT 1;
Output:
trainer_name | student_count
--------------+---------------
Muthu | 4
(1 row)
39. Find the course with the maximum number of students.
(I was able to find the count of each students, but I couldn't figure out how to find Max number)
select c.course_name, count(s.student_id) AS student_count
from course c
join student_courses sc
on c.course_id = sc.course_id
join student s
on sc.student_id = s.student_id
group by c.course_name
order by student_count desc;
Output:
course_name | student_count
------------------+---------------
Spring Boot | 2
React | 2
Java | 2
Python | 2
PostgreSQL | 1
Software Testing | 1
AWS | 1
(7 rows)
Top comments (0)