Week 7: Group Functions
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
PASS Leader:
Copyright By PowCoder代写 加微信 powcoder
Term 2 2022
Week 7 Welcome Video
• There are some file-sharing websites that specialise in buying and selling academic work to and from university students.
• If you upload your original work to these websites, and if another student downloads
and presents it as their own either wholly or partially, you might be found guilty of collusion — even years after graduation.
• These file-sharing websites may also accept purchase of course materials, such as copies of lecture slides and tutorial handouts. By law, the copyright on course materials, developed by UNSW staff in the course of their employment, belongs to UNSW. It constitutes copyright infringement, if not academic misconduct, to trade these materials.
UNSW Business School acknowledges the Bidjigal (Kensington campus) and Gadigal (City campus) the traditional custodians of the lands where each campus is located.
We acknowledge all Aboriginal and Islander Elders, past and present and their communities who have shared and practiced their teachings over thousands of years including business practices.
We recognise Aboriginal and Islander people’s ongoing leadership and contributions, including to business, education and industry.
UNSW Business School. (2022, May 7). Acknowledgement of Country [online video]. Retrieved from https://vimeo.com/369229957/d995d8087f
❑ Housekeeping
❑ Discussion of Group Functions ❑ Week 7 Demo Script
❑ Week 7 Sandbox Activity
❑ Assignment Q/A
COMM1822 Consultation
Consultation with the LiC
Kam-Fung (Henry)
10.00 – 11.00 am (AEST)
Send an email to book a time:
COMM1822 PASS Class
PASS Class
10:00 – 11:00 am (AEST)
Online via
https://au.bbcollab.com/guest/734c9 39b0ddf42d2868fc2304b801d82
Team Assignment Part B
❑ Start working on your Team Assignment Part B (if you have not started)
❑ Check the due date for your Team Assignment Part B in the Assignment brief.
❑ The Team Leader of each group must submit both the PDF file and the Zip file separately (the Zip file must NOT contain the PDF file)
Assignment Group SQL Accounts
❑ A separate Oracle SQL account was provided to each assignment group (Each assignment group will have 1 account)
❑ Complete the assignment SQL activities using the group account instead of your personal student SQL account(for Part B only)
❑ Do not change your group SQL account login password
❑Disregard the existing DB in there.
❑ If more than one person works on a table and makes changes, this might create errors in the DB and the account. Please collaborate within the group who will be what and when!
Assignment – Database Implementation
❑ Populate the database with at least ten sample records in each table.
❑ Make sure your sample data can return the queries as specified in the assignment.
❑ Housekeeping
❑ Discussion of Group Functions ❑ Week 7 Demo Script
❑ Week 7 Sandbox Activity
❑ Assignment Q/A
Week 7 Lab Manual Discussion
1. What is the purpose of the Group Functions and how might they be useful?
2. When might it be useful to summarise data at a group level?
3. What new clauses have been introduced?
❑ Housekeeping
❑ Discussion of Group Functions ❑ Week 7 Demo Script
❑ Week 7 Sandbox Activity
❑ Assignment Q/A
Week 7 Demo Script
Open Week 7 Demo Script (Oracle Demo) from Moodle
Take a 5 minutes break
❑ Housekeeping
❑ Discussion of Group Functions ❑ Week 7 Demo Script
❑ Week 7 Sandbox Activity
❑ Assignment Q/A
Week 7 Sandbox Activity
1. How expensive/cheap are the most expensive/cheapest foods?
2. Determine the average cost of all food items without associated allergies. The calculated average should be formatted to display a dollar sign and two decimal places.
3. Display the cheapest drink, most expensive drink, and average price of all drinks. The calculated numbers should be rounded to the nearest dollar and formatted to display a dollar sign. Use the column headings “Cheapest”, “Dearest”, and “AVERAGE”.
4. Determine the average cost of food items prepared by each chef. The calculated averages should be formatted to one decimal place. Use the column headings “Chef Name” and “Average cost per chef”. Sort the output by average cost in ascending order.
5. Return the chefs and their average cost from (4) where the average is in the range $11 – $15 inclusive.
Week 7 Sandbox Activity
1. How expensive/cheap are the most expensive/cheapest foods?
SELECT min(price) “Cheapest”, max(price) “Most expensive” FROM FOOD;
Week 7 Sandbox Activity
2. Determine the average cost of all food items without associated allergies. The calculated average should be formatted to display a dollar sign and two decimal places.
SELECT TO_CHAR(AVG(price), ‘$99.99’) “Average” FROM FOOD
WHERE allergy IS NULL;
Week 7 Sandbox Activity
3. Display the cheapest drink, most expensive drink, and average price of all drinks. The calculated numbers should be rounded to the nearest dollar and formatted to display a dollar sign. Use the column headings “Cheapest”, “Dearest”, and “AVERAGE”.
SELECT TO_CHAR(ROUND(min(price), 0), ‘$999’) “Cheapest”, TO_CHAR(ROUND(max(price), 0), ‘$999’) “Dearest”, TO_CHAR(ROUND(avg(price), 0), ‘$999’) “Average”
FROM DRINKS;
Week 7 Sandbox Activity
4. Determine the average cost of food items prepared by each chef. The calculated averages should be formatted to one decimal place. Use the column headings “Chef name” and “Average cost per chef”. Sort the output by average cost in ascending order.
SELECT ch.name “Chef Name”, ROUND(avg(f.price), 1) “Average cost per chef” FROM FOOD f INNER JOIN COOKS c
ON f.food_id = c.food_id
INNER JOIN chef ch
ON ch.staff_id = c.staff_id GROUP BY ch.name ORDER BY avg(f.price);
Week 7 Sandbox Activity
5. Return the chefs and their average cost from (4) where the average is in the range $11 – $15 inclusive.
SELECT ch.name “Chef Name”, ROUND(avg(f.price), 1) “Average cost per chef” FROM FOOD f INNER JOIN cooks c
ON f.food_id = c.food_id
INNER JOIN chef ch
ON ch.staff_id = c.staff_id
GROUP BY ch.name
HAVING avg(price) BETWEEN 11 AND 15;
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com