Introduction to Databases for Business Analytics
Subqueries and Merge Statements Big Data
Lecturer-in-Charge: Kam-Fung (Henry) 2 2022
Email: Tutors:
Copyright By PowCoder代写 加微信 powcoder
PASS Leader:
Week 9 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.
Acknowledgement of Country
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
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
❑ MyExperience
❑ Discussion of Subqueries and Merge Statements ❑ Week 9 Sandbox Activity
❑ Big Data Strategy
MyExperience
Our aim is 80% response rate! Please fill in the MyExperience survey now!
https://web.microsoftstream.com/video/48ac8258-0c17-4b84-89c7-80dff76e6db5
Thank you for being so patient with us and we look forward to your feedback.
Tell us about your experience. Shape the future of education at UNSW.
myexperience.unsw.edu.au
Moodle to complete
myExperience
❑ MyExperience
❑ Discussion of Subqueries and Merge Statements ❑ Week 9 Sandbox Activity
❑ Big Data Strategy
Week 9 Lab Manual Discussion
1. What is the purpose of subqueries and how might they be useful?
2. What are the different types of subqueries?
3. What new concept has been introduced?
4. What new clauses have been introduced?
❑ MyExperience
❑ Discussion of Subqueries and Merge Statements ❑ Week 9 Sandbox Activity
❑ Big Data Strategy
Week 9 Sandbox Activity
1. Determine which other customer visited the restaurant on the same day as .
2. Determine which food items have a longer preparation time than Ox Tongue.
3. Determine which food items cost less than the most frequently ordered drink item. Display the name of the dish and the price.
4. List each chef’s name, and the average price of all items ordered that each chef prepares (per day).
Week 9 Sandbox Activity
1. Determine which other customer visited the restaurant on the same day as .
SELECT DISTINCT c.name FROM CUSTOMER c INNER JOIN ORDERS o USING (customer_id) WHERE order_date =
(SELECT order_date FROM ORDERS WHERE customer_id =
(SELECT customer_id FROM CUSTOMER where name = ‘ ’)
AND c.name <> ‘ ‘;
Week 9 Sandbox Activity
2. Determine which food items have a longer preparation time than Ox Tongue.
FROM FOOD WHERE cook_time >
(SELECT cook_time FROM FOOD WHERE dish_name = ‘Ox Tongue’);
Week 9 Sandbox Activity
3. Determine which food items cost less than the most frequently ordered drink item. Display the name of the dish and the price.
SELECT * FROM FOOD WHERE price <
SELECT d.price
FROM DRINKS d INNER JOIN ORDERS o ON d.drink_id = o.drink_id
GROUP BY d.drink_id, d.drink_name, d.price
ORDER BY count(*) DESC
FETCH FIRST 1 ROWS ONLY -- Can be done alternatively using more subqueries to get the
maximum count, then join back );
Week 9 Sandbox Activity
4. List each chef’s name, and the average price of all items ordered that each chef prepares (per day).
SELECT c.name, TO_CHAR(av.average_revenue, '$99.99') "Average Daily Revenue" FROM CHEF c
INNER JOIN
SELECT staff_id, AVG(daily_revenue) average_revenue FROM
(SELECT c.staff_id, o.order_date, sum(f.price*(100 - d.percentage)/100.0) daily_revenue FROM orders o INNER JOIN discounts d
ON o.discount_id = d.discount_id
INNER JOIN food f
ON o.food_id = f.food_id
INNER JOIN cooks c
ON f.food_id = c.food_id
GROUP BY c.staff_id, o.order_date) GROUP BY staff_id
av ON c.staff_id = av.staff_id;
Online Safety - Break
▪ 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_WorkRave_ 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
❑ MyExperience
❑ Discussion of Subqueries and Merge Statements ❑ Week 9 Sandbox Activity
❑ Big Data Strategy
Big Data Strategy (Discussion)
1. Research a company of your choice (different from last week’s company if possible)
2. Build a Big Data strategy using the 5 steps of the Enterprise Big Data Framework: https://www.bigdataframework.org/formulating-a-big-data- strategy/
3. Focus in this discussion on the challenges each Big Data project would face, i.e., the three interdependent components that cause success or failure: People, Process and Technology
See next slide
30 min in breakout rooms/groups
Present your work in groups (3 min per group)
https://www.bigdataframework.org/formulating-a-big-data-strategy/
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com