Carlton University teaches computing courses in Melbourne, Australia. Their I.T. department has designed a system to store data about the university’s courses, subjects, students and teachers. Your job is to write SQL queries that answer questions posed by management.
The university offers a range of subjects that are identified by a combination of ‘study area’, ‘year level’, and ‘code’. For example, the subject ‘INFO90002’ belongs to study area INFO, year level 9, code 0002. Each subject is coordinated by one lecturer.
We record information about each student, including which postcode they live in. Students choose one course (e.g. MIS or MIT) and then enrol in a series of subjects over time. Enrolments in subjects are recorded in the join-table StudentTakesSubject, where we record which student took which subject, in which year and semester, and the result they received. In the current semester, which has not yet been assessed, results are null. Students may enrol in the same subject twice, but not in the same semester. (In the sample data, all person names are fictitious.)
The following ER diagram describes the database schema which has been implemented.
To set up the database in your MySQL server, download the file Assignment2Setup.sql from LMS and run it in Workbench. This script creates the schema and database tables and populates them with data. (Note the comments near the start - the script is different depending on whether you run it on the UniMelb server or your own computer.)
The SQL queries required
In this section are listed 10 questions for you to answer. Write one SQL statement per question. Do not use views to answer the questions. Where it would improve readability, order your output and use aliases. Format large numbers and fractions appropriately.
Beside each question is a maximum mark which reflects the difficulty of that question. The total will be scaled to 10% of your marks in the subject.
Your work will be assessed on the correctness and simplicity of the SQL that you write. (A query that produces correct output but is more complex than it needs to be, for example joining more tables than is necessary, may not achieve full marks, even if it produces the correct results.)
Although the subject codes (e.g. “INFO90002”) are divided into 3 columns (“INFO”, “9”, “0002”) in the database, when you print them in your output you should join them into one string.
How many students are in the database but have not enrolled into any subjects?
Which student has the longest name (sum of first and last names)? Print the student’s name and its length.
For each letter of the alphabet, print how many suburbs begin with that letter. (You can skip letters with a count of zero.)
Which suburbs do most students live in? List the top 3 in descending order, showing postcode, suburb name, and number of students living there.
What percentage of suburb names contain the string ‘MELBOURNE’? Show 2 decimal points in your answer.
Which lecturer is associated with the highest average student result, averaged across all the subjects they teach?
Print a list of students, showing studentid and lastname, along with the number of subjects that student has taken, and their Grade Point Average (average of results weighted by credit points). Don’t include subjects without results.
Which students have repeated a subject? Print their full names.
Of the students who have enrolled in at least one subject, which of them have never received a result greater than 80?
Print a list of any students who have enrolled in all available subjects.