Week 4 Restricting Rows and Sorting Data & Selected Single-Row Function
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
PASS Leader:
Copyright By PowCoder代写 加微信 powcoder
Term 2 2022
Week 4 Welcome Video
(for online classes)
• 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 & Feedback Survey ❑ Week 4 Demo Lab Scripts
❑ Week 4 Sandbox Activity
❑ Assignment Group Discussion
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
Your UNSW email
Message body
Full Name zID
Group Name/ID
All GroupIDs were updated on Moodle
• Please check the Excel spreadsheet on Moodle and join your Group NOW
If you do not join a group, you have to do the assignment on your own.
Please refer to the assignment brief for more information
• You are responsible to read the assignment brief • If you have any question, ask on Moodle
Practice Exercises and Training Videos
Online Web Tutorials Tutorials Point
https://www.tutorialspoint.com/sql/
w3schools.com
https://www.w3schools.com/sql/
Useful Online
Sign up is free
https://www.datacamp.com/
Highly Recommended:
Intro to SQL for Data Science
Training Videos
LinkedIn Learning – Free access as a UNSW
student: https://www.linkedin.com/learning/
Highly Recommended:
Introduction to Oracle SQL Developer
We need your feedback NOW
We will analyse them and make changes if necessary. Please do not ignore
Source: Wikipedia – Uncle Sam
This Week 4 Survey is your chance to provide us with early feedback (unlike MyExperience)
Your feedback is anonymous, and no respondents can be identified! Please provide responses with constructive feedback:
https://forms.office.com/Pages/ResponsePage.aspx?id=pM_2PxXn20i44Qhnufn7o0KszAg PgNBAjB8ETnYCVsdUQkRGVEJTOVBEM0dDTkJGQ1hFU1RaNE5ZQS4u
❑ Housekeeping & Feedback Survey ❑ Week 4 Demo Lab Scripts
❑ Week 4 Sandbox Activity
❑ Assignment Group Discussion
Week 4 Demo Script
Open Week 4 Demo Script (Oracle Demo) from Moodle
Restricting Rows, Single-row Functions
▪ Restricting rows
▪ The WHERE clause – Q1-3
▪ Comparing values – Q4-6, Q10, Q12, Q15-16 ▪ IN, NOT, NULL, and LIKE – Q17-20
▪ Wildcards – 21-23
▪ ORDER BY – Q24-27
▪ Single-Row Functions
▪ Q3, Q4, Q7
▪ Dual Table – A “dummy table – Q8, Q10
▪ Null replacements and CASE – Q14, Q15, Q18
▪ 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
❑ Housekeeping & Feedback Survey ❑ Week 4 Demo Lab Scripts
❑ Week 4 Sandbox Activity
❑ Assignment Group Discussion
Week 3 Sandbox Script Issue
If you have problems in running the SQL queries in Week 3, e.g., errors in creating tables, insertion values error, etc. Please run the following queries to drop the tables first:
DROP TABLE COOKS;
DROP TABLE Produce;
DROP TABLE Orders;
DROP TABLE Food;
DROP TABLE Drinks;
DROP TABLE Supplier;
DROP TABLE Chef;
DROP TABLE Customer; DROP TABLE Delivery_service; DROP TABLE Discounts;
Then, re-run all the queries in the W3 Sandbox Build Script (https://moodle.telt.unsw.edu.au/pluginfile.php/8038231/mod_resource/content/1/SandboxBuild.sql) to recreate the whole Korean BBQ Restaurant database.
Week 4 Sandbox Activity
1. List the customer_id and names of all customers whose names begin with “D”. Display their names in lower case.
2. Create a list containing the dish_name and price for all food that costs more than $10. Round each price to the nearest $10. The rounded price should be formatted to display a dollar sign and three decimal places.
3. Create a list containing delivery_company_id and company_name. If the delivery company uses a “Car”, display the text “FAST”. If the delivery company uses a “Bike”, display the text “SLOW”. Use a column alias to show the heading “Speed” for this column.
4. List the order_date for all orders that were placed between 5th April 2021 and 17th August 2021. For each of these orders, determine the amount of time in years that has elapsed between when they were placed and today’s date. This calculated figure should be displayed in a column with the heading “Years Elapsed (2dp)” and formatted to display no more than two decimal places.
5. Create a unique list of customer_id and order_date from the ORDERS table. If the delivery_company_id field is null for any row, display the text “ ”. If the delivery_company_id field is not null, display the text “Takeaway”. Use the heading “Eat in?” for this column.
Join your breakout room to work in groups on the above queries – 25 minutes
Each group must present 1 query solution to the class (allocated randomly by your class facilitator)
Week 4 Sandbox Activity – Answers
1. List the customer_id and names of all customers whose names begin with “D”. Display their names in lower case.
SELECT customer_id, LOWER(name) Name FROM CUSTOMER
WHERE name LIKE ‘D%’;
Week 4 Sandbox Activity – Answers
2. Create a list containing the dish_name and price for all food that costs more than $10. Round each price to the nearest $10. The rounded price should be formatted to display a dollar sign and three decimal places.
SELECT dish_name, TO_CHAR(ROUND(price,-1), ‘$999.999’) “Price” FROM FOOD
WHERE price > 10;
Week 4 Sandbox Activity – Answers
3. Create a list containing delivery_company_id and company_name. If the delivery company uses a “Car”, display the text “FAST”. If the delivery company uses a “Bike”, display the text “SLOW”. Use a column alias to show the heading “Speed” for this column.
SELECT delivery_company_id, company_name, CASE
WHEN delivery_type = ‘Car’ THEN ‘FAST’
WHEN delivery_type = ‘Bike’ THEN ‘SLOW’ END AS “Speed”
FROM delivery_service;
Week 4 Sandbox Activity – Answers
4. List the order_date for all orders that were placed between 5th April 2021 and 17th August 2021. For each of these orders, determine the amount of time in years that has elapsed between when they were placed and today’s date. This calculated figure should be displayed in a column with the heading “Years Elapsed (2dp)” and formatted to display no more than two decimal places.
SELECT order_date, ROUND(MONTHS_BETWEEN(sysdate, order_date)/12, 2) “Years Elapsed (2dp)” FROM ORDERS
WHERE order_date BETWEEN ’05/APR/2021′ AND ’17/AUG/2021′;
Week 4 Sandbox Activity – Answers
5. Create a unique list of customer_id and order_date from the ORDERS table. If the delivery_company_id field is null for any row, display the text “ ”. If the delivery_company_id field is not null, display the text “Takeaway”. Use the heading “Eat in?” for this column.
SELECT DISTINCT customer_id, order_date, NVL2(delivery_company_id, ‘Takeaway’, ‘ ’) AS “Eat in?” FROM ORDERS;
❑ Housekeeping & Feedback Survey ❑ Week 4 Demo Lab Scripts
❑ Week 4 Sandbox Activity
❑ Assignment Group Discussion
Please join your group (or breakout room) and work on your assignment.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com