Week 5 SQL Joins: Joining Data from Multiple Tables
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
PASS Leader:
Copyright By PowCoder代写 加微信 powcoder
Term 2 2022
Week 5 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 SQL Joins ❑ Week 5 Demo Script
❑ Week 5 Sandbox Activity
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
Assignment Group SQL Accounts
❑ A separate Oracle SQL account will be 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
Thank you for all who provided the feedback in week 4. We are still collecting feedback
This is your chance to provide us with early feedback
Please do not ignore
https://forms.office.com/Pages/ResponsePage.aspx?id=pM_2PxXn20i44Qhnufn7o0KszAg PgNBAjB8ETnYCVsdUQkRGVEJTOVBEM0dDTkJGQ1hFU1RaNE5ZQS4u
❑ Housekeeping
❑ Discussion of SQL Joins ❑ Week 5 Demo Script
❑ Week 5 Sandbox Activity
Week 5 Lab Manual Discussion
1. What is the purpose of the queries and how might they be useful?
2. What new concept has been introduced?
3. What are the different types of joins?
4. In what scenarios might each type of join be useful?
5. What new clauses have been introduced?
❑ Housekeeping
❑ Discussion of SQL Joins ❑ Week 5 Demo Script
❑ Week 5 Sandbox Activity
Week 5 Demo Script
Open Week 5 Demo Script (Oracle Demo) from Moodle:
– Query 1-11
– Query 12-20
Week 5 Demo Script
Open Week 5 Demo Script (Query 1-11)
▪ Rest Breaks: https://safety.unsw.edu.au/office-safety-toolkit/rest-breaks
▪ WorkRave at UNSW: https://safety.unsw.edu.au/sites/default/files/documents/UNSW_WorkRa ve_V1.0.pdf
▪ Setting up you Workstation : https://safety.unsw.edu.au/setting-your- workstation-video
▪ Online Safety and Wellbeing Tutorial: https://student.unsw.edu.au/safety
▪ SafeWork 6 Simple stretches: https://www.safework.nsw.gov.au/resource-library/six-simple-stretches
Take a 5 minute break
Week 5 Demo Script
Open Week 5 Demo Script (Query 12-20)
❑ Housekeeping
❑ Discussion of SQL Joins ❑ Week 5 Demo Script
❑ Week 5 Sandbox Activity
Week 5 Sandbox Activity
1. Create a list that displays each dish_name and the name of the chef that cooked it. Sort the output by dish_name in descending order.
2. Produce a list of all customers who have ordered the dish “Beef Inside Skirt” after 20th March 2021. Include the customer’s name and the date of their order in the output.
3. Display a list of every drink which has been ordered by customers. Include the name of the company that supplied it, the name of the customer who ordered it, and the date when they ordered the drink.
4. Provide a list of all customers (ID + Name) who did NOT receive a discount on their order (i.e. there is no discount value).
Week 5 Sandbox Activity
1. Create a list that displays each dish_name and the name of the chef that cooked it. Sort the output by dish_name in descending order.
SELECT f.dish_name, c.name “Cooked By” FROM FOOD f
INNER JOIN COOKS ck
USING (food_ID)
INNER JOIN chef c
USING (staff_ID)
ORDER BY f.dish_name DESC;
Week 5 Sandbox Activity
2. Produce a list of all customers who have ordered the dish “Beef Inside Skirt” after 20th March 2021. Include the customer’s name and the date of their order in the output.
SELECT c.Name “Customer Name”, o.order_date “Order Date” FROM customer c
INNER JOIN orders o
USING (customer_ID)
INNER JOIN food f
USING (food_ID)
WHERE f.dish_name = ‘Beef Inside Skirt’ AND o.order_date >= ’20-MAR-21′;
Week 5 Sandbox Activity
3. Display a list of every drink which has been ordered by customers. Include the name of the company that supplied it, the name of the customer who ordered it, and the date when they ordered the drink.
SELECT d.Drink_Name, s.supplier_name, c.name “CUSTOMER_NAME”, o.order_date FROM Drinks d
INNER JOIN orders o
USING (drink_ID)
INNER JOIN customer c
USING (customer_ID)
INNER JOIN supplier s USING (supplier_ID);
Week 5 Sandbox Activity
4. Provide a list of all customers (ID + Name) who did NOT receive a discount on their order (i.e. there is no discount value).
SELECT c.customer_id “ID”, c.name “Name” FROM customer c
LEFT JOIN orders o
on c.customer_id = o.customer_id
WHERE o.Discount_ID IS NULL;
▪ No classes next week (week 6), classes resume in week 7.
▪ Start working on your Part B assignment.
▪ Any group conflicts (unequal contribution) must be raised ASAP with the LiC and the tutor.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com