FIT2094-FIT3171 Databases
Session 11 Tutorial Activities
SQL Part III (SQL Advanced)
FIT Database Teaching Team
Complete session 11 activities in week 6 listed below
11.1 Class Discussion
11.2 SQL Advanced Questions
FIT2094-FIT3171 2021 Summer B
FIT2094-FIT3171 Databases
Author: FIT Database Teaching Team
License: Copyright © Monash University, unless otherwise stated. All Rights Reserved.
COPYRIGHT WARNING
Warning
This material is protected by copyright. For use within Monash University only. NOT FOR RESALE. Do not remove this notice.
Page 1 of 7
Learning Objectives
● be able to write subqueries
● be able to use CASE
● be able to use different Oracle’s built-in functions.
● be able to use different set operators (INTERSECT, MINUS, UNION).
● be able to perform OUTER join and join between tables that have more than one
relationship.
Important
Remember before starting any lab activity which involves working with files, first use SQL Developer to pull from FIT GitLab server to ensure your local files and the FIT GitLab server files are in sync. During this activity, you will be creating a set of SQL scripts, these MUST be sent to the FIT GitLab server.
This session we will continue to use the UNIVERSITY database model:
University Data model
Page 2 of 7
11.1 Class Discussion
1. Assuming that the student name is unique, display Claudette Serman’s academic record, include the unit code, unit name, year, semester, mark and explained_grade in the listing. The Explained Grade column must show Fail for N, Pass for P, Credit for C, Distinction for D and High Distinction for HD. Order the list in increasing order of year, within the same year order the list in increasing order of semester, within the same semester order the list in increasing order of unit code order.
2. Find the total number of prerequisite units for all units. Include in the list the unit code of units that do not have a prerequisite. Order the list in descending order of the number of prerequisite units.
3. List the unit code, year, semester, number of enrolments and the average mark for each unit offering. Include offerings without any enrolment in the list. Round the average to 2 digits after the decimal point. If the average result is ‘null’, display the average as 0.00. All values must be shown with two decimal digits. Order the list in increasing order of average mark.
Page 3 of 7
11.2 SQL Advanced Questions
Download session11_sql_advanced.sql from the Session 11 block in Moodle, place this file in your working directory in your Tut11 folder. Write your answers for question 1 – 7 in the provided area. Test the select statements one by one, then when you finish all questions, run the whole script and save the output file as session11_sql_advanced_output.txt. To save the output, use the inbuilt Oracle SPOOL command.
1. Find the number of scheduled classes assigned to each staff member for each semester in 2019. If the number of classes is 2 then this should be labelled as a correct load, more than 2 as an overload and less than 2 as an underload. Order the list by decreasing order of the number of scheduled classes and when the number of classes is the same order by increasing order of staff id.
2. Display the unit code and unit name for units that do not have a prerequisite. Order the list in increasing order of unit code. There are many approaches that you can take in writing an SQL statement to answer this query. You can use the SET OPERATORS, OUTER JOIN and a SUBQUERY. Write SQL statements based on all three approaches.
Page 4 of 7
3. List all units offered in semester 2 2019 which do not have any enrolment. Include the unit code, unit name, and the chief examiner’s name in the list. Order the list based on the unit code.
4. List the full names of students who are enrolled in both ‘Introduction to databases’ and ‘Introduction to computer architecture and networks’ (note: both unit names are unique) in semester 1 2020. Order the list by the students’ full name.
5. Given that the payment rate for a tutorial is $42.85 per hour and the payment rate for a lecture is $75.60 per hour, calculate the weekly payment per type of class for each staff member in semester 1 2020. In the display, include staff id, staff name, type of class (lecture or tutorial), number of classes, number of hours (total duration), and weekly payment (number of hours * payment rate). Order the list by increasing order of staff id and for a given staff id by type of class.
6. Given that the payment rate for a tutorial is $42.85 per hour and the payment rate for a lecture is $75.60 per hour, calculate the total weekly payment (the sum of both tutorial and lecture payments) for each staff member in semester 1 2020. In the display, include staff id, staff name, total weekly payment for tutorials, total weekly payment for lectures and the total weekly payment. If the payment is null, show it as $0.00. Order the list by increasing order of staff id.
Page 5 of 7
7. Assume that all units are worth 6 credit points each, calculate each student’s Weighted Average Mark (WAM) and GPA. Please refer to these Monash websites: https://www.monash.edu/exams/results/wam and https://www.monash.edu/exams/results/gpa for more information about WAM and GPA respectively. Do not include WH or DEF grade in the calculation
Calculation example for student 14374036 (Claudette Serman):
WAM = (56×6 + 16×6 + 81×6 + 77×6 + 64×6)/(6+6+6+6+6) = 58.80 GPA = (1×6+ 0.3×6 + 4×6 + 3×6 + 2×6)/(6+6+6+6+6) = 2.06
Calculation example for student 13119134 (Shandra Lindblom):
WAM = (62×3 + 91×3 + 64×6 +82×6)/(3+3+6+6) = 74.17 GPA = (2×6 + 4×6+ 2×6 + 4×6)/(6+6+6+6) = 3
Include student id, student full name (in a 40 characters wide column headed “Student Full Name”), WAM and GPA in the display. Order the list by descending order of WAM then descending order of GPA.
Page 6 of 7
Only some data shown:
Important
You need to get into the habit of establishing this as a standard FIT2094-FIT3171 workflow – pull at the start of your working session, work on the activities you wish to/are able to complete during this session, save the files, add all (stage), commit and then push the changes back to the FIT GitLab server
Page 7 of 7