代写SQL作业，根据要求完成对应的SQL Query语句。
The Case
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.)
Data model
The following ER diagram describes the database schema which has been
implemented.
Setup Script
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.
