Joins Part 2 Querying

 Querying with Joins

Database Here, the database stores the data of students, courses, course reviews, instructors, etc., of an e-learning platform.

Refer the tables in the code playground for a better understanding of the database.

Joins on Multiple Tables

We can also perform join on a combined table.

Example

Fetch all the students who enrolled for the courses taught by the instructor “Arun” (id = 102)

1

2

3

4

5

6

7

8

SELECT T.name AS course_name,

student.full_name

FROM (course

INNER JOIN student_course

ON course.id = student_course.course_id) AS T

INNER JOIN student

ON T.student_id = student.id

WHERE course.instructor_id = 102;

SQL

Output

course_name	full_name
Machine Learning	Varun
Machine Learning	Sandya

Note

Best Practices

1. Use

   ALIAS
   to name the combined table.

2. Use alias table names to refer the columns in the combined table.

Try it Yourself!

Question 1:

Fetch the name of the students who gave reviews to the "Machine Learning" course.

Expected Output:

full_name
Varun

Question 2:

Fetch the course names in which "Varun" has registered.

Expected Output:

course_name
Machine Learning

Let's learn about the Right Join, Full Join and Cross Join in the upcoming cheatsheet.

Using joins with other clauses

We can apply

WHERE

,

ORDER BY

,

HAVING

,

GROUP BY

,

LIMIT

,

OFFSET

and other clauses (which are used for retrieving data tables) on the temporary joined table as well.

Example:

Get the name of the student who scored highest in "Machine Learning" course.

1

2

3

4

5

6

7

8

9

SELECT student.full_name

FROM (course

INNER JOIN student_course

ON course.id = student_course.course_id) AS T

INNER JOIN student

ON T.student_id = student.id

WHERE course.name = "Machine Learning"

ORDER BY student_course.score DESC

LIMIT 1;

SQL

Output

full_name
Sandhya

Try it Yourself!

Question 1: Get all the courses taken by the student with id=1 and his respective scores in each course

Expected Output

name	score
Machine learning	80

Question 2: Get all the students who registered for at least one course.

Expected Output

full_name
Varun
Ajay
Sandhya

Using joins with aggregations

We can apply

WHERE

,

ORDER BY

,

HAVING

,

GROUP BY

,

LIMIT

,

OFFSET

and other clauses (which are used for retrieving data tables) on the temporary joined table as well.

• Get the highest score in each course.

1

2

3

4

5

6

7

8

9

SELECT

course.name AS course_name,

MAX(score) AS highest_score

FROM

course

LEFT JOIN student_course

ON course.id = student_course.course_id

GROUP BY

course.id;

SQL

Output

course_name	highest_score
Machine Learning	90
Cyber Security	60
Linux

Try it Yourself!

Question 1: Get the course name and the average score for each course.

Expected Output

name	avg_score
Machine Learning	85
Cyber Security	60
Linux

Question 2: Get the number of students in each course .

Expected Output

name	no_of_students
Machine learning	2
Cyber Security	1
linux	0