Introduction

The following exercises are a part of a new section (currently under construction) in this website, providing data analysis tasks that require business understanding alongside technical skills.

 

About this Exercise

In this exercise you’re going to analyse data related to an academic institute.

 

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