Introduction to Databases for Business Analytics
Database Access Control
Term 2 2022
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
Copyright By PowCoder代写 加微信 powcoder
PASS Leader:
Week 10 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 Database Access Control ❑ Week 10 Demo Script
❑ Week 10 Sandbox Activity
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 Database Access Control ❑ Week 10 Demo Script
❑ Week 10 Sandbox Activity
Week 10 Lab Manual Discussion
1. What is Access Control?
2. What is the purpose of using a view?
3. What new concept has been introduced?
4. What new clauses have been introduced?
5. What are the DCL commands?
❑ MyExperience
❑ Discussion of Database Access Control ❑ Week 10 Demo Script
❑ Week 10 Sandbox Activity
W10 Demo Script
Today’s Topic: “Views”
Open Week 10 Demo Script (Oracle demo) from Moodle
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 Database Access Control ❑ Week 10 Demo Script
❑ Week 10 Sandbox Activity
Week 10 Sandbox Activity
Create a view of all food items that cost less than the average.
Query the view created in Q1 – how many different foods in this list have never been ordered?
List a report showing each chef’s salary and how many DIFFERENT food items they have cooked. Also include how far above or below the highest paid chef they are.
Create a output (using two queries) showing:
Each food item, and the number of times it has been ordered.
Each drink item, and the number of customers who have ordered it.
Call the columns “Item name” and “Volume” and order by volume descending, and then Item Name in backwards alphabetical order.
Create an output of the same as Q3, but using one query.
Week 10 Sandbox Activity
1. Create a view of all food items that cost less than the average
CREATE OR REPLACE VIEW less_than_avg_food AS
SELECT FROM WHERE
price < (SELECT avg(price) FROM FOOD);
Week 10 Sandbox Activity
2. Query the view created in Q1 – how many different foods in this list have never been ordered?
SELECT FROM WHERE
count(distinct food_id) "Never ordered, less than average" less_than_avg_food
food_id NOT IN (SELECT distinct food_id from ORDERS);
Week 10 Sandbox Activity
3. List a report showing each chef’s salary and how many food items they have cooked. Also include how far above or below the highest paid chef they are.
SELECT c.name, c.salary, count(distinct f.food_id) "Dishes Prepared", salary - (SELECT MAX(SALARY) FROM CHEF) "Above or below max"
FROM chef c INNER JOIN cooks co ON c.staff_id = co.staff_id INNER JOIN food f ON co.food_id = f.food_id
GROUP BY c.name, c.salary;
Week 10 Sandbox Activity
4. Create a output (using two queries) showing:
a. Each food item, and the number of times it has been ordered
b. Each drink item, and the number of customers who have ordered it.
Call the columns “Item name” and “Volume” and order by volume descending, and then Item Name in backwards alphabetical order.
SELECT FROM GROUP BY ORDER BY
SELECT FROM GROUP BY ORDER BY
f.dish_name "Item Name", count(*) AS Volume
food f INNER JOIN orders o ON f.food_id = o.food_id f.dish_name
Volume DESC, "Item Name" DESC;
d.drink_name "Item Name", count(o.customer_id) AS Volume drinks d INNER JOIN orders o ON d.drink_id = o.drink_id d.drink_name
Volume DESC, "Item Name" DESC;
Week 10 Sandbox Activity
5. Create an output of the same as Q3, but using one query.
SELECT * FROM
SELECT f.dish_name "Item Name", count(*) AS Volume FROM food f INNER JOIN orders o ON f.food_id = o.food_id GROUP BY f.dish_name
SELECT d.drink_name, count(o.customer_id)
FROM drinks d INNER JOIN orders o ON d.drink_id = o.drink_id GROUP BY d.drink_name
ORDER BY Volume DESC, "Item Name" DESC;
Thank You and All the Best!
Source: happybirthdaymsg.com
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com