DEV Community

VINOTH
VINOTH

Posted on

SQL Multiple JOIN

Scenario - Students -> Student Courses -> Trainers -> Courses

Level 1: Difficulty Level: Easy

  1. Display the course name and trainer name for every course. 
cinemas=# select c.course_name, t.trainer_name
cinemas-# from courses c
cinemas-# join trainers t
cinemas-# on c.trainer_id = t.trainer_id;
Enter fullscreen mode Exit fullscreen mode

o/p:

  course_name    | trainer_name 
------------------+--------------
 Python           | Vijay
 Java             | Muthu
 PostgreSQL       | Arun
 Spring Boot      | Muthu
 React            | Prithvi
 Software Testing | Vallarasu
 AWS              | Vijay
(7 rows)
Enter fullscreen mode Exit fullscreen mode
  1. Display: ◦ Course Name ◦ Trainer Name ◦ Trainer Specialization 
cinemas=# select c.course_name, t.trainer_name, t.specialization 
from courses c
join trainers t
on c.trainer_id = t.trainer_id;
Enter fullscreen mode Exit fullscreen mode

o/p:

   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)
Enter fullscreen mode Exit fullscreen mode
  1. Display all courses taught by Vijay. 
cinemas=# select c.course_name   
from courses c
join trainers t
on c.trainer_id = t.trainer_id
cinemas-# where t.trainer_name = 'Vijay';
Enter fullscreen mode Exit fullscreen mode

o/p:

 course_name 
-------------
 Python
 AWS
(2 rows)
Enter fullscreen mode Exit fullscreen mode
  1. Display all courses taught by trainers having more than 5 years of experience. 
cinemas=# select c.course_name, t.trainer_name 
from courses c
join trainers t
on c.trainer_id = t.trainer_id
where t.experience_years > 5;
Enter fullscreen mode Exit fullscreen mode

o/p:

   course_name    | trainer_name 
------------------+--------------
 Python           | Vijay
 Java             | Muthu
 PostgreSQL       | Arun
 Spring Boot      | Muthu
 Software Testing | Vallarasu
 AWS              | Vijay
(6 rows)
Enter fullscreen mode Exit fullscreen mode
  1. Display the course name and fee along with the trainer name. 
cinemas=# select c.course_name, c.fee, t.trainer_name 
from courses c
join trainers t
on c.trainer_id = t.trainer_id;
Enter fullscreen mode Exit fullscreen mode

o/p:

   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)
Enter fullscreen mode Exit fullscreen mode

Level 2 — Student + Course

Difficulty: Easy

  1. Display the student name and course name for every enrollment. 
cinemas=# select student_name, c.course_name
from students s
join student_courses sc
on s.student_id = sc.student_id
join courses c
on sc.course_id = c.course_id;
Enter fullscreen mode Exit fullscreen mode

o/p:

 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)
Enter fullscreen mode Exit fullscreen mode
  1. Display: ◦ Student Name ◦ City ◦ Course Name ◦ Enrollment Date 
cinemas=# select student_name, s.city, c.course_name, sc.enrolled_date 
from students s
join student_courses sc
on s.student_id = sc.student_id
join courses c
on sc.course_id = c.course_id;
Enter fullscreen mode Exit fullscreen mode

o/p:

 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)
Enter fullscreen mode Exit fullscreen mode
  1. Find all courses taken by Muthu. 
cinemas=# select c.course_name
cinemas-# from courses c
cinemas-# join trainers t
cinemas-# on c.trainer_id = t.trainer_id
cinemas-# where t.trainer_name = 'Muthu';
Enter fullscreen mode Exit fullscreen mode

o/p:

 course_name 
-------------
 Java
 Spring Boot
(2 rows)
Enter fullscreen mode Exit fullscreen mode
  1. Find all students who have enrolled in Java. 
cinemas=# select student_name, c.course_name
from students s
join student_courses sc
on s.student_id = sc.student_id
join courses c
on sc.course_id = c.course_id
where c.course_name = 'Java';
Enter fullscreen mode Exit fullscreen mode

o/p:

 student_name | course_name 
--------------+-------------
 Kavin        | Java
 Kathir       | Java
(2 rows)
Enter fullscreen mode Exit fullscreen mode
  1. Find all students from Chennai and the courses they have enrolled in. 
cinemas=# select student_name, c.course_name
from students s
join student_courses sc
on s.student_id = sc.student_id
join courses c
on sc.course_id = c.course_id
where s.city = 'Chennai';
Enter fullscreen mode Exit fullscreen mode

o/p:

 student_name | course_name 
--------------+-------------
 Arul         | Python
 Arul         | AWS
 Venba        | PostgreSQL
 Venba        | Spring Boot
 Paari        | Python
 Paari        | Spring Boot
(6 rows)
Enter fullscreen mode Exit fullscreen mode

Level 3 - Three/Four Table JOIN

Difficulty Level: Intermediate:

  1. Display Student Name, Course Name and Trainer Name 
cinemas=# select s.student_name, c.course_name, t.trainer_name
from students s
join student_courses sc
on s.student_id = sc.student_id
join courses c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id;
Enter fullscreen mode Exit fullscreen mode


plaintext
o/p:

 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)
Enter fullscreen mode Exit fullscreen mode


plaintext

  1. Display Student Name, Student City, Course Name, Trainer Name 
cinemas=# select s.student_name, s.city, c.course_name, t.trainer_name
from students s
join student_courses sc
on s.student_id = sc.student_id
join courses c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id;
Enter fullscreen mode Exit fullscreen mode


plaintext
o/p:

 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)
Enter fullscreen mode Exit fullscreen mode


plaintext

  1. Display Student Name, Course Name, Trainer Name, Trainer Specialization 
cinemas=# select s.student_name, c.course_name, t.trainer_name, t.specialization               
from students s
join student_courses sc
on s.student_id = sc.student_id
join courses c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id;
Enter fullscreen mode Exit fullscreen mode


plaintext
o/p:

 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)
Enter fullscreen mode Exit fullscreen mode


plaintext

  1. Find all students who are learning courses handled by Vallarasu. 
cinemas=# select s.student_name
from students s
join student_courses sc
on s.student_id = sc.student_id
join courses c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
where t.trainer_name = 'Vallarasu';
Enter fullscreen mode Exit fullscreen mode


plaintext
o/p:

 student_name 
--------------
 Mathi
(1 row)
Enter fullscreen mode Exit fullscreen mode


plaintext

  1. Find all students who are learning Python, along with their trainer's name. 
cinemas=# select s.student_name, t.trainer_name
from students s
join student_courses sc
on s.student_id = sc.student_id
join courses c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
where c.course_name = 'Python';
Enter fullscreen mode Exit fullscreen mode


plaintext
o/p:

 student_name | trainer_name 
--------------+--------------
 Arul         | Vijay
 Paari        | Vijay
(2 rows)
Enter fullscreen mode Exit fullscreen mode


plaintext

  1. Find all students who are learning courses taught by trainers having more than 7 years of experience. 
cinemas=# select s.student_name, c.course_name, t.trainer_name
from students s
join student_courses sc
on s.student_id = sc.student_id
join courses c
on sc.course_id = c.course_id
join trainers t
on c.trainer_id = t.trainer_id
where t.experience_years > 7;
Enter fullscreen mode Exit fullscreen mode


plaintext
o/p:

 student_name | course_name | trainer_name 
--------------+-------------+--------------
 Arul         | Python      | Vijay
 Arul         | AWS         | Vijay
 Kavin        | Java        | Muthu
 Venba        | Spring Boot | Muthu
 Paari        | Python      | Vijay
 Paari        | Spring Boot | Muthu
 Kathir       | Java        | Muthu
(7 rows)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)