SQL JOIN Practice: From Basic to Advanced — 39 Real-World Queries with Solutions

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', '[e-mail]', 'Chennai'),
('Anitha', '[e-mail]', '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)