Introduction to Databases for Business Analytics
Week 3 Table Creation and Basic SQL Functions
Term 2 2022
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
Copyright By PowCoder代写 加微信 powcoder
PASS Leader:
(Tutor-in-Charge) Liam Li
Week 3 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
❑ Housekeeping
❑ Setting Up Oracle
❑ Week 2 Revision Activity
❑ Discussion of Basic SQL Functions and Table Creation ❑ Week 3 Demo Script
❑ Week 3 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)
On-campus BUS 216 Online via MS Teams
Group Assignment
❑ Every student must be in their respective assignment group
❑ If you did not connect or you do not know your group, speak to your tutor
immediately
❑ End of this tutorial session is the final deadline to join a group
❑ Housekeeping
❑ Setting Up Oracle
❑ Week 2 Revision Activity
❑ Discussion of Basic SQL Functions and Table Creation ❑ Week 3 Demo Script
❑ Week 3 Sandbox Activity
Setting Up Oracle
❑ To download SQL developer –Moodle & e-mail
❑ Usernames and passwords have been sent to your UNSW email account ❑ Connection details:
Oracle via MyAccess
❑ If you have issues on your laptop, please access Oracle SQL Developer via MyAccess: ❑ https://www.myaccess.unsw.edu.au/
❑To logon to MyAccess: https://aaa-access.unsw.edu.au/Citrix/aaaWeb/
Training Videos
Useful Online Resources Online Web Tutorials
❑LinkedIn Learning – Free access as a UNSW student:
https://www.linkedin.com/learning/
❑Highly Recommended: Introduction to Oracle SQL Developer
❑w3schools.com https://www.w3schools.com/sql/
❑ Housekeeping
❑ Setting Up Oracle
❑ Week 2 Revision Activity
❑ Discussion of Basic SQL Functions and Table Creation ❑ Week 3 Demo Script
❑ Week 3 Sandbox Activity
ER Modelling Reminders
❑ Remember to draw all your attributes, it is not enough to just draw the entity and relationships
❑ Primary keys are underlined
❑ Foreign keys are dotted underlined
❑ Optional relationships are reflected by a cardinality of (0,N) or (0,1).
❑ Composite Entity is composed of the PKs of the original entities
❑ Composition Entities may contain additional attributes
❑ Housekeeping
❑ Setting Up Oracle
❑ Week 2 Revision Activity
❑ Discussion of Basic SQL Functions and Table Creation ❑ Week 3 Demo Script
❑ Week 3 Sandbox Activity
Week 3 Lab Manual Discussion
❑What is SQL?
❑ What is the basic structure of a SQL statement? ❑What SQL functions have been introduced?
❑ What do these functions allow you to do?
❑ How do you create a table in SQL?
❑ Housekeeping
❑ Setting Up Oracle
❑ Week 2 Revision Activity
❑ Discussion of Basic SQL Functions and Table Creation ❑ Week 3 Demo Script
❑ Week 3 Sandbox Activity
Week 3 Demo Script
Open Week 3 Demo Script (Oracle Demo) from Moodle
Take a 5 minute break
❑ Housekeeping
❑ Setting Up Oracle
❑ Week 2 Revision Activity
❑ Discussion of Basic SQL Functions and Table Creation ❑ Week 3 Demo Script
❑ Week 3 Sandbox Activity
Week 3 Sandbox Activity
1. Display a list of all data contained in the FOOD table.
2. Create a list of customer names
3. List the name and cook time of each dish in the FOOD table. Use the column headings “Name of Dish” for the dish_name field and “Preparation Time” for the cook_time field.
4. Create a list containing the supplier’s name, the supplier’s telephone number, and the category of produce they provide. The output should be displayed in the following format: “[Supplier Name] provides [category] and can be contacted on [telephone number].”. Use a column alias to show the heading “Summary of Supplier” in the output.
5. Create a list of supplier categories – do not repeat items.
6. List the cook times for each food (in minutes), and then again in hours. Label the columns appropriately.
7. All food delivery service fees are increased by 10% as a result of new taxes. List the new delivery fees.
8. Create a new table – call it Dummy_Table, with fields “dummy_int” (integer) and “dummy_char” (varchar of length 30). Then Drop the table.
9. Restore the Farm table, and list all the data inside it.
5. Create a list of supplier categories – do not repeat items.
Week 3 Sandbox Activity – SOLUTION
1. Display a list of all data contained in the FOOD table.
SELECT * FROM Food;
2. Create a list of customer names.
SELECT Name FROM customer;
3. List the name and cook time of each dish in the FOOD table. Use the column headings “Name of Dish” for the dish_name field and “Preparation Time” for the cook_time field.
SELECT dish_name AS “Name of Dish”, cook_time AS “Preparation Time” FROM food;
4. Create a list containing the supplier’s name, the supplier’s telephone number, and the category of produce they provide. The output should be displayed in the following format: “[Supplier Name] provides [category] and can be contacted on [telephone number].”. Use a column alias to show the heading “Summary of Supplier” in the output.
SELECT supplier_name || ‘ provides ‘ || category || ‘ and can be contacted on ‘ || phone_number FROM supplier;
5. Create a list of supplier categories – do not repeat items.
SELECT DISTINCT category FROM supplier;
Week 3 Sandbox Activity – SOLUTION
6. List the cook times for each food (in minutes), and then again in hours. Label the columns appropriately.
SELECT dish_name, cook_time AS “Cook Time in Minutes”, cook_time / 60.0 AS “Cook Time in Hours” FROM food;
7. All food delivery service fees are increased by 10% as a result of new taxes. List the new delivery fees.
SELECT company_name, delivery_type, fee * 1.1 AS ” ” FROM delivery_service;
8. Create a new table – call it Dummy_Table, with fields “dummy_int” (integer) and “dummy_char” (varchar of length 30). Then Drop the table.
CREATE TABLE Dummy_Table (
dummy_int INT,
dummy_char VARCHAR(30) );
DROP TABLE Dummy_Table;
9. Restore the Farm table, and list all the data inside it.
a. FLASHBACK TABLE employees_hist_2011 TO BEFORE DROP;
b. There should be no data/table.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com