FIT9132 Introduction to Databases
Week 10 Tutorial Activities
SQL Part II (SQL Intermediate)
FIT Database Teaching Team
Complete the week 10 activities listed below:
10.1 Using the SQL Developer Query Builder
10.2 SQL Intermediate
10.2.1 Class Discussion
10.2.2 SQL Intermediate Questions
FIT9132 2020 S2
FIT9132 Introduction to 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 8
Learning Objectives:
¡ñ be able to use SQL aggregate functions (SUM, AVG, COUNT)
¡ñ be able to write SQL using GROUP BY Clause
¡ñ be able to use SQL clause HAVING
¡ñ be able to use a subquery in SQL
10.1 Using the SQL Developer Query Builder
First, set the preference to open the table using a double click instead of a single click. Open SQL Developer Preferences ¡ú Database ¡ú uncheck Open Object on Single Click.
The Query Builder tab is located alongside the Worksheet tab:
Using the Query Builder you can quickly and efficiently create SQL queries or beginning templates. As an example take the query “List the student name of all students who have marks in the range
of 60 to 70”.
1. First, we need to identify the tables which will be needed – here uni.student and uni.enrolment
Page 2 of 8
2. Drag and drop the tables from the uni account into query builder:
3. Indicate the join condition between the two tables, by dragging the STUDID attribute from STUDENT onto the STUDID attribute of ENROLMENT:
If required the join can be further specified by double-clicking on the join line, for example, to specify this should be an outer join (Select all from the appropriate side)
4. Indicate which attributes you wish to be displayed in the select list by ticking the appropriate boxes:
Right-clicking on an output row offers a number of actions including move up/down to position the item
Page 3 of 8
5. Use the Criteria column to set the WHERE clause:
6. Lastly, specify the sort order:
The query can be run directly from within the Query Builder by selecting the normal Run Statement (the green arrow) as you did in the Worksheet.
Page 4 of 8
Alternatively, if you swap back to the Worksheet, you will see your full SQL query built:
The query builder can help you quickly and efficiently build queries. However, it is very important that you are able to code your own queries in the worksheet and do not depend on the query builder to do the task for you. It is simply a tool you may use in building queries.
Page 5 of 8
10.2 SQL Intermediate
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.
The following exercises will allow you to be familiar with:
¡ñ All the SQL statements previously used
¡ñ Aggregate functions such as min, max, avg ¡ñ The GROUP BY clause
¡ñ Subqueries
This week we will continue to use the UNIVERSITY database model:
University Data model
Page 6 of 8
10.2.1 Class Discussion
1. Find the maximum mark for FIT9136 in semester 2, 2019.
2. Find the total number of enrolments per semester for each unit in the year 2019. The list should include the unitcode, semester and year. Order the list in increasing order of enrolment numbers.
3. Find the oldest student/s in FIT9132? Display the student¡¯s full name and the date of birth. Sort the list by student id.
10.2.2 SQL Intermediate Questions
Download week10_sql_intermediate.sql from the Week 10 block in Moodle, place this file in your working directory in your Tut10 folder. Write your answers for question 1 – 10 in the provided area. Test the select statement one by one, then when you have finished all questions, run the whole script and save the output file as week10_sql_intermediate_output.txt. To save the output, use the inbuilt Oracle SPOOL command.
1. Find the average mark for FIT2094 in semester 2, 2019. Show the average mark with two decimal places. Name the output column as ¡°Average Mark¡±.
2. List the average mark for each offering of FIT9136. In the listing, include the year and semester number. Sort the result according to the year then the semester.
3. Find the number of students enrolled in FIT1045 in the year 2019, under the following conditions (note two separate selects are required):
a. Repeat students are counted multiple times in each semester of 2019
b. Repeat students are only counted once across 2019
4. Find the total number of prerequisite units for FIT5145.
5. Find the total number of prerequisite units for each unit. In the list, include the unit code for which the count is applicable. Order the list by unit code.
6. Find the total number of students whose marks are being withheld (grade is recorded as ‘WH’) for each unit offered in semester 1 2020. In the listing include the unit code for which the count is applicable. Sort the list by descending order of the total number of students whose marks are being withheld.
7. For each prerequisite unit, calculate how many times it has been used as a prerequisite (number of times used). In the listing include the prerequisite unit code, the prerequisite unit name and the number of times used. Sort the output by unit name.
8. Display the unit code and unit name of units which had at least 1 student who was granted a deferred exam (grade is recorded as ‘DEF’) in semester 1 2020. Order the list by unit code.
9. Find the unit/s with the highest number of enrolments for each offering in the year 2019. Sort the list by semester then by unit code.
Page 7 of 8
10. Find all students enrolled in FIT3157 in semester 1, 2020 who have scored more than the average mark for FIT3157 in the same offering? Display the students’ name and the mark. Sort the list in the order of the mark from the highest to the lowest then in increasing order of student name.
Important
You need to get into the habit of establishing this as a standard FIT9132 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 8 of 8