This SQL exercise is a part of a section dedicated for data analysts , providing data analysis tasks that require both business understanding and technical skills.
 

About this Exercise

StanCord in an academic institute located in the UK. in this exercise you’re going to explore its database, and analyze data related to courses, classes, lecturers, and students.
 

Diagram



 

ERD general description

This databases consists of four tables :

  • Lecturers table provides detail regarding the different lecturers
  • Students table lists the different students
  • Courses table indicates the various courses that can be taken (by students)
  • Classes table lists the the different classes that were taken by students.
    • student_id is a Foreign Key of Students, lecturer_id is a foreign key of Lecturers, course_id is a foreign key of Courses
    • The combination of: class_id, course_id, lecturer_id, and student_id makes a Primary Key
    • grade_test_a is the class score on the first attempt, grade_test_b is the class score on the second attempt, grade_test_a is the class score on the third, the final score is determined by the score of the latest attempt (whether its better or worse than the previous ones)

 

Download

The database creation script can be downloaded using the following link

 

Exercises

The following exercises are constructed in ascending difficulty order. First ones are pretty easy, made for warm-up. The advanced exercises, on the other hand, can be quite challenging.

 
Basic Analysis

  1. How many lecturers are living in Liverpool
  2. List all course titles containing the words “cognition” or “cognitive”
  3. How many classes, and how many students were taught by each lecturer
  4. List the female students that are living in london

 
Advanced Analysis

  1. Restrict your last query only for those who went through the course : “Topics in Applied Psychology”
  2. How many students, in average, are being taught by each lecturer
  3. List the students who were tought by Mr Jacob Willshear
  4. List all lecturers who are teaching the same courses as Mr Jacob Willshear
  5. List all lecturers who are teaching “Topics in Perception & Cognition”
  6. Display the lecturer whose students got the highest average score in ‘Topics in Perception & Cognition’
  7. How many students have succeeded to improve their final grade
  8. How many courses in average were taken by each student
  9. Display how many students and how many lecturers, are coming from outside of London
  10. For each course, display the top 2 students (by their final grade)

 

Solutions