Week 9
SQL Intermediate
Workshop 2023 S2
2Please
▪ connect to Flux - flux.qa and be ready to answer questions
▪ login to the Oracle database via ORDS:
https://ora-fit.ocio.monash.edu:8441/ords/sql-developer
Preparation for the workshop - ready, set ……
3 3
Access tables via
DRONE.tablename in
the Monash Oracle
database
4Aggregate Functions
• COUNT, MAX, MIN, SUM, AVG
• Example:
SELECT
MAX(drone_flight_time)
FROM
drone.drone;
SELECT
MIN(drone_flight_time)
FROM
drone.drone;
SELECT
AVG(drone_flight_time)
FROM
drone.drone;
SELECT COUNT(*)
FROM drone.drone
WHERE drone_flight_time > 100;
5count(*) and count(column_name)
SQL> SELECT
2 COUNT(*),
3 COUNT(rent_out_dt),
4 COUNT(rent_in_dt)
5 FROM
6 drone.rental;
COUNT(*) COUNT(RENT_OUT_DT) COUNT(RENT_IN_DT)
---------- --------------- --------------
25 25 22
6Anatomy of an SQL Statement - Revisited
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY;
clauses statement
Predicate / search
condition
7GROUP BY
▪ If a GROUP BY clause is used with aggregate
function, the DBMS will apply the aggregate
function to the different groups defined in the
clause rather than all rows.
SELECT dt_code, AVG(drone_flight_time)
FROM drone.drone
GROUP BY dt_code
ORDER BY dt_code;
SELECT
AVG(drone_flight_time)
FROM
drone.drone;
8SQL> SELECT
2 AVG(drone_flight_time)
3 FROM
4 drone.drone;
AVG(DRONE_FLIGHT_TIME)
----------------------
74.025
SQL>
SQL> SELECT
2 dt_code,
3 AVG(drone_flight_time)
4 FROM
5 drone.drone
6 GROUP BY
7 dt_code
8 ORDER BY
9 dt_code;
DT_C AVG(DRONE_FLIGHT_TIME)
---- ----------------------
DIN2 78.6666667
DMA2 53.3333333
DSPA 45.5
PAPR 97.625
SWPS 56.3
9Q1. List all customer ids and the total number of courses taken by each customer:
A. select cust_id, count(*) as no_of_courses_taken
from drone.cust_train
order by cust_id;
B. select cust_id, sum(train_code) as no_of_courses_taken
from drone.cust_train
group by cust_id
order by cust_id;
C. select cust_id, count(*) as no_of_courses_taken
from drone.cust_train
group by cust_id
order by cust_id;
D. None of the above
10
SELECT cust_id, COUNT(*) AS no_courses_taken
FROM drone.cust_train
GROUP BY cust_id
ORDER BY cust_id;
SELECT count(*)
FROM drone.cust_train;
SELECT AVG(COUNT(*))
AS average_no_courses_taken
FROM drone.cust_train
GROUP BY cust_id;
What output is produced?
11
SQL> SELECT count(*)
2 FROM drone.cust_train;
COUNT(*)
----------
22
SQL> SELECT cust_id, COUNT(*) AS
no_courses_taken
2 FROM drone.cust_train
3 GROUP BY cust_id
4 ORDER BY cust_id;
CUST_ID NO_COURSES_TAKEN
---------- ----------------
1 1
2 1
3 1
4 1
5 2
6 2
7 1
8 1
9 4
10 1
11 1
12 1
14 1
15 1
16 1
17 1
18 1
17 rows selected.
SQL> SELECT AVG(COUNT(*))
2 AS average_no_courses_taken
3 FROM drone.cust_train
4 GROUP BY cust_id;
AVERAGE_NO_COURSES_TAKEN
------------------------
1.29411765
12
Q2. List all customer ids and the number of times each customer has taken a specific
course:
A. select cust_id, train_code, count(*) as no_of_courses_taken
from drone.cust_train
group by cust_id
order by cust_id;
B. select cust_id, train_code, count(*) as no_of_courses_taken
from drone.cust_train
group by cust_id, train_code
order by cust_id, train_code;
C. select cust_id, count(*) as no_of_courses_taken
from drone.cust_train
group by train_code
order by train_code;
D. None of the above
13
SELECT cust_id, train_code, count(train_code)
as no_of_courses_taken
FROM drone.cust_train
GROUP BY cust_id, train_code
ORDER BY cust_id, train_code;
What output is produced?
14
SQL> SELECT cust_id, train_code, count(train_code) as no_of_courses_taken
2 FROM drone.cust_train
3 GROUP BY cust_id, train_code
4 ORDER BY cust_id, train_code;
CUST_ID TRAIN NO_OF_COURSES_TAKEN
---------- ----- -------------------
1 DJIHY 1
2 DJIHY 1
3 DJIHY 1
4 DJIHY 1
5 DJIHY 1
5 DJIPR 1
6 DJIPR 2
7 DJIPR 1
8 DJIPR 1
9 DJIPR 2
9 PARPO 1
9 SWELL 1
10 PARPO 1
11 PARPO 1
12 PARPO 1
14 PARPO 1
15 PARPO 1
16 SWELL 1
17 SWELL 1
18 SWELL 1
20 rows selected.
15
SELECT cust_id,
to_char(ct_date_start, 'yyyy') as licence_start_year,
count(train_code) as no_of_courses_taken
FROM drone.cust_train
GROUP BY cust_id, to_char(ct_date_start, 'yyyy')
ORDER BY cust_id, licence_start_year;
Note: column alias cannot be used in group by clause
WHY?
What output is produced?
16
SQL> SELECT cust_id, to_char(ct_date_start, 'yyyy') as licence_start_year, count(train_code) as no_of_courses_taken
2 FROM drone.cust_train
3 GROUP BY cust_id, to_char(ct_date_start, 'yyyy')
4 ORDER BY cust_id, licence_start_year;
CUST_ID LICE NO_OF_COURSES_TAKEN
---------- ---- -------------------
1 2021 1
2 2021 1
3 2021 1
4 2021 1
5 2021 1
5 2022 1
6 2021 1
6 2022 1
7 2021 1
8 2021 1
9 2021 3
9 2022 1
10 2021 1
11 2021 1
12 2021 1
14 2021 1
15 2021 1
16 2021 1
17 2021 1
18 2021 1
20 rows selected.
17
Q3. Which rows that will be returned by this select statement:
SELECT cust_id, train_code, count(train_code)
as no_of_courses_taken
FROM drone.cust_train
GROUP BY cust_id, train_code
HAVING count(train_code) > 1
ORDER BY cust_id, train_code;
A. all rows
B. 7, 10
C. none of them
D. all rows except row 7 and 10
18
HAVING clause
▪ It is used to put a condition or conditions on the
groups defined by GROUP BY clause.
SELECT cust_id, train_code, count(train_code)
as no_of_courses_taken
FROM drone.cust_train
GROUP BY cust_id, train_code
HAVING count(train_code) > 1
ORDER BY cust_id, train_code;
19
SELECT cust_id, train_code, count(train_code) as no_of_courses_taken
FROM drone.cust_train
GROUP BY cust_id, train_code
HAVING count(train_code) > 1
ORDER BY cust_id, train_code;
SELECT dt_code, AVG(drone_flight_time) as average_drone_flight
FROM drone.drone
GROUP BY dt_code
HAVING AVG(drone_flight_time)>50
ORDER BY dt_code;
What output is produced?
20
SQL> SELECT cust_id, train_code, count(train_code) as no_of_courses_taken
2 FROM drone.cust_train
3 GROUP BY cust_id, train_code
4 HAVING count(train_code) > 1
5 ORDER BY cust_id, train_code;
CUST_ID TRAIN NO_OF_COURSES_TAKEN
---------- ----- -------------------
6 DJIPR 2
9 DJIPR 2
SQL> SELECT dt_code, AVG(drone_flight_time) as average_drone_flight
2 FROM drone.drone
3 GROUP BY dt_code
4 HAVING AVG(drone_flight_time)>50
5 ORDER BY dt_code;
DT_C AVERAGE_DRONE_FLIGHT
---- --------------------
DIN2 78.6666667
DMA2 53.3333333
PAPR 97.625
SWPS 56.3
21
Q4. Write the SQL Query to report the average drone flight time
for each type of drone. Display the average for only those types
that have an average flight time of more than 50 minutes and for
drones which were purchased in 2021.
22
SELECT dt_code, AVG(drone_flight_time) as average_drone_flight
FROM drone.drone
WHERE to_char(drone_pur_date,'yyyy') = '2021'
GROUP BY dt_code
HAVING AVG(drone_flight_time)>50
ORDER BY dt_code;
HAVING and WHERE clauses
• The WHERE clause is applied to ALL rows in the table.
• The HAVING clause is applied to the groups defined by the GROUP BY clause.
• The order of operations performed is FROM, WHERE, GROUP BY, HAVING and then
ORDER BY.
• On the above example, the logic of the process will be:
• All rows where drone purchase year = 2021 are retrieved. (due to the WHERE clause)
• The retrieved rows then are grouped into different dt_code.
• If the average flight time in a group is greater than 50, the dt_code and the average flight
time is displayed. (due to the HAVING clause)
23
SQL> SELECT
2 dt_code,
3 AVG(drone_flight_time) AS average_drone_flight
4 FROM
5 drone.drone
6 WHERE
7 to_char(drone_pur_date, 'yyyy') = '2021'
8 GROUP BY
9 dt_code
10 HAVING
11 AVG(drone_flight_time) > 50
12 ORDER BY
13 average_drone_flight desc;
DT_C AVERAGE_DRONE_FLIGHT
---- --------------------
DIN2 200
PAPR 97.625
DMA2 80
SWPS 56.3
24
SELECT cust_id, train_code, count(*) as no_of_courses_taken
FROM drone.cust_train
GROUP BY cust_id
ORDER BY cust_id;
The above SQL generates error message
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
Why and how to fix this?
• Why? Because the grouping is based on the cust_id, whereas the display is
based on cust_id and train_code. The two groups may not have the same
members.
• How to fix this?
• Include the train_code as part of the GROUP BY condition.
• Attributes that are used in the SELECT, HAVING and ORDER BY must be
included in the GROUP BY clause (reverse is not necessary).
25
Subqueries
Query within a query.
"Find all drones which flight time is higher than the average flight
time of all drones"
SELECT *
FROM drone.drone
WHERE drone_flight_time >
(
SELECT AVG(drone_flight_time)
FROM drone.drone
)
ORDER BY drone_id;
26
Types of Subqueries
Single-value
Main query
Subquery
returns APPLE
Multiple-row subquery (a list of values – many rows, one column)
APPLE
PEAR
Main query
Subquery
returns
Multiple-column subquery (many rows, many columns)
APPLE 4.99
PEAR 3.99
Main query
Subquery
returns
27
Q5. What will be returned by the inner query?
SELECT *
FROM drone.drone
WHERE drone_pur_price > (SELECT AVG(drone_pur_price)
FROM drone.drone
GROUP BY drone_pur_date)
A. A value (a single column, single row).
B. A list of values.
C. Multiple columns, multiple rows.
D. None of the above.
28
SQL> SELECT
2 *
3 FROM
4 drone.drone
5 WHERE drone_pur_price > (SELECT AVG(drone_pur_price)
6 FROM drone.drone
7 GROUP BY drone_pur_date);
Error starting at line : 1 in command -
SELECT
*
FROM
drone.drone
WHERE drone_pur_price > (SELECT AVG(drone_pur_price)
FROM drone.drone
GROUP BY drone_pur_date)
Error report -
ORA-01427: single-row subquery returns more than one row
29
Q6. What will be returned by the inner query?
SELECT dt_code,dt_model,drone_id, drone_pur_price
FROM drone.drone_type natural join drone.drone
WHERE (dt_code, drone_pur_price) IN
(SELECT dt_code, MAX(drone_pur_price)
FROM drone.drone_type NATURAL JOIN drone.drone
GROUP BY dt_code)
A. A value (a single column, single row).
B. A list of values.
C. Multiple columns, multiple rows.
D. None of the above.
30
Comparison Operators for Subquery
• Operator for single value comparison.
=, <, >
• Operator for multiple rows or a list comparison.
–equality
• IN
–inequality
•ALL, ANY combined with <, >
31
Q7. Which row(s) in the above table will be retrieved by the
following SQL statement?
SELECT *
FROM dronetypeprice
WHERE drone_pur_price IN (SELECT MAX(drone_pur_price)
FROM dronetypeprice GROUP BY dt_code)
A. 3,5,6,9,10,12
B. 10
C. 3,5,6,9,10,11,12
32
SQL> SELECT
2 *
3 FROM
4 dronetypeprice
5 WHERE drone_pur_price IN (SELECT MAX(drone_pur_price)
6 FROM dronetypeprice
7 GROUP BY dt_code)
8 order by drone_id;
DRONE_ID DT_C DT_MODEL DRONE_PUR_PRICE
---------- ---- ------------------------------ ---------------
102 DSPA DJI Spark 872.44
111 PAPR Parrot Pro 4200
112 PAPR Parrot Pro 4200
118 SWPS SwellPro Spry 1599
119 DIN2 DJI Inspire 2 5600.8
120 DIN2 DJI Inspire 2 4200
121 DMA2 DJI Mavic Air 2 Flymore Combo 1610
33
Q8. Which row/s in the above table will be retrieved by the
following SQL statement?
SELECT *
FROM dronetypeprice
WHERE drone_pur_price >
ANY (SELECT MIN(drone_pur_price)
FROM dronetypeprice
GROUP BY dt_code)

A. 10
B. 1,2,4,5,6,7,8,9,10,11,12
C. 4,10
D. No rows will be returned
34
SQL> SELECT *
2 FROM dronetypeprice
3 WHERE drone_pur_price >
4 ANY (SELECT MIN(drone_pur_price)
5 FROM dronetypeprice
6 GROUP BY dt_code)
7 ORDER BY drone_id;
DRONE_ID DT_C DT_MODEL DRONE_PUR_PRICE
---------- ---- ------------------------------ ---------------
100 DMA2 DJI Mavic Air 2 Flymore Combo 1494
101 DMA2 DJI Mavic Air 2 Flymore Combo 1494
103 DIN2 DJI Inspire 2 5300
111 PAPR Parrot Pro 4200
112 PAPR Parrot Pro 4200
113 PAPR Parrot Pro 4000
117 PAPR Parrot Pro 4000
118 SWPS SwellPro Spry 1599
119 DIN2 DJI Inspire 2 5600.8
120 DIN2 DJI Inspire 2 4200
121 DMA2 DJI Mavic Air 2 Flymore Combo 1610

35
Q9. Which row/s in in the above table will be retrieved by the
following SQL statement?
SELECT *
FROM dronetypeprice
WHERE drone_pur_price >
ALL (SELECT MIN(drone_pur_price)
FROM dronetypeprice
GROUP BY dt_code)
ORDER BY drone_id;

A. 10
B. 1,2,4,5,6,7,8,9,10,11,12
C. 4,10
D. No rows will be returned
36
SQL> SELECT *
2 FROM dronetypeprice
3 WHERE drone_pur_price >
4 ALL (SELECT MIN(drone_pur_price)
5 FROM dronetypeprice
6 GROUP BY dt_code)
7 ORDER BY drone_id;
DRONE_ID DT_C DT_MODEL DRONE_PUR_PRICE
---------- ---- ---------------- ---------------
103 DIN2 DJI Inspire 2 5300
119 DIN2 DJI Inspire 2 5600.8

37
Q10. Write the SQL Query to find the details of all drones which
have a purchase price less than the average purchase price for
all drones manufactured by DJI Da-Jiang Innovations.
Begin by listing the steps which need to be taken
After this code the SQL step by step.
Your output must show the drone id, the type code, the purchase
price, the year purchased and the manufacturers name.
Order the output by drone id.
** Note manufacturers name in italics - unit standard for string to match
38
SELECT
drone_id,
dt_code,
drone_pur_price,
to_char(drone_pur_date,'yyyy') as yearpurchased,
manuf_name
FROM
drone.drone
NATURAL JOIN drone.drone_type
NATURAL JOIN drone.manufacturer
WHERE
drone_pur_price < (
SELECT
AVG(drone_pur_price)
FROM
drone.drone
NATURAL JOIN drone.drone_type
NATURAL JOIN drone.manufacturer
WHERE
upper(manuf_name) = upper('DJI Da-Jiang Innovations')
)
ORDER BY
drone_id;
39
Summary
▪ Aggregate Functions
–count, min, max, avg, sum
▪ GROUP BY and HAVING clauses.
▪ Subquery
–Inner vs outer query
–comparison operators (IN, ANY, ALL)