Applied 11 SQL Part III - Advanced SQL

Applied 11 SQL Part III - Advanced SQL

After completing this activity, you should be able to:

Consolidate your understanding of subqueries

Use different SQL set operators (INTERSECT, DIFFERENCE, UNION)
Outline the role of VIEWs in the database
Code SQL OUTER join, recursive relationship joins and join between tables that have more than
one relationship
Describe the role of query optimisation and its relationship to relational algebra

This activity supports unit learning outcomes 1 and 4.

A11-1 SQL Part III (SQL Advanced)
Important: Use of ORDER BY CLAUSE ASC/DESC

When answering SQL queries for this unit please note the following:

if descending order is required this will be explicitly stated in the form"Order by …

descending"
if the questions simply states "Order by …" ie. with no qualifier, then this must be treated as
ordering by the default order ie. ascending

For this module we will continue to use the UNIVERSITY database model:

Download applied11_sql_advanced.sql from below:

applied11_sql_advanced.sql

Place this file in your working directory in your App11 folder. Write your answers for question 1 - 10
in the provided area. Make sure that you include a semicolon ‘;’ at the end of each select statement.

Test the select statements one by one, by highlighting the required code and then using the run
option in VS Code. The run statement uses proportional fonts which makes it difficult to size output,
for example when testing LPAD.

If the query is concerned with placement of the column data e.g., right aligning, we need non-
proportional fonts. In such a situation you can test/run the code by highlighting the code you wish to
tun and using the Run Script button:

(a) In VS Code:

(b) As an alternative, with Oracle SQL Developer for VS Code, you add a SPOOL command to the top
of your script, such as:

SPOOL [Link]
SET PAGESIZE 100
SET LINESIZE 300

pagesize = how many lines of output before a page feed (a new set of column headings is
output)
linesize = how many characters on a line

When run as a script, this will spool the output into a test file using non-proportional fonts. You can
then open the output file and view the data placement.

NOTE: The sample output is shown for your guidance as to the displayed columns and formats. The
data in the database is constantly changing and as a result your actual data displayed (the values in
the rows of output) may be different. Therefore, it is important to:

check whether your query is correct by manually checking the data in the table/s, and
ensuring your query will work for any future possible cases/changes to the data.

1. Assuming that the student's 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 by year, within the same year order the list by semester, and within the same semester
order the list by the unit code.

2. 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. Include the staff id, staff first name, staff last name, semester,
number of scheduled classes and load in the listing. Sort the list by decreasing order of the number of
scheduled classes and when the number of classes is the same, sort by the staff id then by the
semester.

3. 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 and
where several units have the same number of prerequisites order then by unit code.

4. Display the unit code and unit name for units that do not have a prerequisite. Order the list by 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.
5. List the unit code, semester, number of enrolments and the average mark for each unit offering in
2019. A unit offering is a particular unit in a particular semester for a particular year - for example the
offering of FIT3176 in semester 2 of 2019 is one 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. The average must be shown with two decimal digits and right aligned. Order the list
by the average mark, and when the average mark for several offerings is the same, sort by the
semester then by the unit code.

6. List all units offered in semester 2 2019 which do not have any students enrolled. Include the unit
code, unit name, and the chief examiner's name in a single column titled ce_name. Order the list
based on the unit code.

7. List the id and full name (in a single column titled student_full_name) 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. You should note that the case provided for these unit
names does not necessarily match the case in the database. Order the list by the student id.
8. 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 - L or tutorial - T), number of
classes, number of hours (total duration), and weekly payment (number of hours * payment rate).
The weekly payment must be displayed to two decimal points and right aligned. Order the list by the
staff id and for a given staff id by the type of class.

9. 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 as a single line
of output. If the payment is null, show it as $0.00. The tutorial payment, lecture payment and total
weekly payment must be displayed to two decimal points and right aligned. Order the list by the staff
id.
10. 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:
[Link] and [Link] for
more information about WAM and GPA respectively. Do not include NULL, WH or DEF grade in the
calculation.

Calculation example for student 14374036 (Claudette Serman):

WAM = (56x6 + 16x6 + 81x6 + 77x6 + 64x6)/(6+6+6+6+6) = 58.80

GPA = (1x6+ 0.3x6 + 4x6 + 3x6 + 2x6)/(6+6+6+6+6) = 2.06

Calculation example for student 23545528 (Benny Plunket):

WAM = (53x3 + 97x3 + 78x6 + 94x6 + 85 x 6)/(3+3+6+6+6) = 83.00

GPA = (1x6 + 4x6+ 3x6 + 4x6 + 4x6)/(6+6+6+6+6) = 3.20

Include student id, student full name (in a 40 characters wide column headed student_fullname),
WAM and GPA in the display. Order the list by descending order of WAM then descending order of
GPA. If two students have the same WAM and GPA, order them by their respective id.

Only some data shown:

Important
You need to get into the habit of establishing this as a standard workflow:

before modifying any file/s, pull at the start of your working session, work on the activities you
wish to/are able to complete during this session, add all(stage), commit changes and then push
the changes back to the FIT GitLab server.
Oracle SQL Common Functions

1 Automatic Zoom