Joins Coding Practice - 2

 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.

Fetch all the courses that are being taught by “Alex”.

Note:

• Solving this problem involves joining of
  course
  table and
  instructor
  table. Note that both the tables have
  instructor_id
  column in common.
• As we only want the courses taught by "Alex", we have to apply filter condition.
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

course_id	course_name	instructor_name
...	...	...

2.

Get all the reviews of “Cyber Security” course .

Note:

• Solving this problem involves performing inner join on
  review
  and
  course
  tables.
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

course_name	student_id	content
...	...	...

3.

For a student with student (id = 1), get all the courses and the scores she/he secured in the year 2021.

Note:

• Solving this question involves performing inner join on
  student_course
  and
  course
  tables.
• You can get the year from the enrollment date.
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

student_id	name	score
...	...	...

4.

Get all the student details who scored more than 70 in Cyber Security course (course_id = 15) in the year 2020.

Note:

• Solving this question involves performing inner join on
  student_course
  and
  student
  tables.
• You can get the year from the enrollment date.
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

student_id	student_name	score	course_id	enrollment_date
...	...	...	...	...

5.

Get all the student_ids who enrolled for the "Machine Learning" course in 2021.

Note:

• Solving this question involves performing inner join on
  student_course
  and
  course
  tables.
• You can get the year from the enrollment date.
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

student_id	course_name	enrollment_date
...	...	...

6.

Continuation of question 5. Get the number of students who enrolled for the "Machine Learning" course in 2021.

Note:

• Solving this question involves performing inner join on
  course
  and
  student_course
  tables.
• You can get the year from the enrollment date.
• We have to perform the count() aggregation.
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

course_name	no_of_students
...	...

7.

Get the number of courses taken by “Ram”.

Note:

• You can get the year from the enrollment date
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

no_of_courses
...

8.

For all the students, get the total number of courses taken by each student.

A student need not register for any course as well. So, we need to perform a left join between the

student

and

student_course

tables.

Note:

• You can get the year from the enrollment date
• As we have to calculate the number of courses for each student, we have to GROUP BY the students first and then perform the count() aggregation.
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

full_name	no_of_courses
...	...

9.

Get the students who have taken at least 2 courses.

Note:

• Solving this problem involves performing join operations on
  student
  and
  student_course
  tables.
• You can get the year from the enrollment date
• Use HAVING clause to filter the students who have taken at least two courses.
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

full_name	no_of_courses
...	...

10.

Get all the students details and all the courses for which they have enrolled.

Note:

• Here, we have to join student, student_course and course tables.
• Performing left join between the student and student_course tables
• perform left join on the combined table and course table.
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

student_id	student_name	course_id	course_name	score	enrollment_date
...	...	...	...	...	...

11.

Get all the student details who enrolled for the “Machine Learning” course in the year 2021.

Note:

• Here, we have to join student, student_course and course tables.
• Apply filters on the combined table.
• You can get the year from the enrollment date.
• Don't apply ORDER BY, LIMIT, OFFSET clauses as it is not required for this problem.

Expected Output Format:

student_id	full_name	course_id	course_name	enrollment_date
...	...	...	...	...