Joins Coding Practice - 1

 In this practice set, let’s get the hold of SQL Joins operations using the following database.

Database: The database stores the sample data of an e-learning platform. The database consists of instructor, course, review, and student tables.

• An instructor can teach many courses. A course is taught by only one instructor.
• A student can enroll for multiple courses. A course can have multiple students.
• A student can give multiple reviews.
• A course can have multiple reviews

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

1. Perform natural join between

course

and

instructor

table.

Note:

• Do not apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

id	name	duration	instructor_id	full_name	gender
...	...	...	...	...

2. Perform inner join between

review

and

student

table.

Note:

• Do not apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

id	course_id	content	created_at	student_id	id	full_name	age	gender
...	...	...	...	...

3. Get all the reviews along with the course names.

Every review is associated with a course. So, we can perform an inner join on

review

and

course

tables.

Note:

• Do not apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

id	course_id	content	created_at	student_id	id	name	duration	instructor_id
...	...	...	...	...

4. Continuation of question 3.

Get all the reviews on the “Cyber Security” course.

Note:

• We can perform inner join on
  review
  and
  course
  table.
• Do not apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

id	course_id	content	created_at	student_id	id	name	duration	instructor_id
...	...	...	...	...

5. Get all the courses and corresponding reviews.

For every course, there need not be a review. So, we need to perform a left join between the

course

and

review

tables.

Note:

Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem

Expected Output Format:

id	name	duration	instructor_id	id	course_id	content	created_at	student_id
...	...	...	...	...

6. Continuation of question 5.

For the “Cyber Security” course, get all the reviews using the left join between the

course

and

review

tables.

Note:

• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem

Expected Output Format:

id	name	duration	instructor_id	id	course_id	content	created_at	student_id
...	...	...	...	...

7. Continuation of question 5.

For the “Linux” course, get all the reviews using the left join between the

course

and

review

tables.

Note:

• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem

Expected Output Format:

id	name	duration	instructor_id	id	course_id	content	created_at	student_id
...	...	...	...	...

8. In question 7, as the course “Linux” has no reviews, the columns corresponding to review table i.e id, content, etc., in the output has NULL value.

Now, to get all the courses that does not have any reviews, We shall perform left join between

course

and

review

tables, and then filter the rows for which

review.id

is NULL

Note:

Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem

Expected Output Format:

name
...

9. Get the full_name of students who have not enrolled in any course.

Note:

• We can perform left join between
  student
  and
  student_course
  tables, and then filter the rows for which
  course_id
  is NULL
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

full_name
...

10. Get the course names in which no student has registered.

Note:

• We can perform left join between
  course
  and
  student_course
  tables, and then filter the rows for which
  student_id
  is NULL
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

name
...

.......