Introduction to Databases for Business Analytics
Week 9 Subqueries and Merge Statement
Term 2 2022
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
Copyright By PowCoder代写 加微信 powcoder
PASS Leader:
• 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
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.
Moodle to complete
or accessing myexperience.unsw.edu.au directly.
myExperience
Subqueries and Merge Statements
Subqueries and Merge Statements
❑A subquery is a nested query – one complete query inside another
❑The results of the subquery (i.e., the inner query) will be passed to the outer query (also called the parent query).
Subqueries and Merge Statements
With the exception of the ORDER BY clause, you can virtually nest a subquery in any of the clauses in a SELECT statement
Subqueries and Merge Statements
Description
Single-row subquery
Returns to the outer query one row of results that consists of one column
Multiple-row subquery
Returns to the outer query more than one row of results
Multiple-column subquery
Returns to the outer query more than one column of results
Correlated subquery
References a column in the outer query, and executes the subquery once for every row in the outer query
Uncorrelated subquery
Executes the subquery first and passes the value to the outer query
Multiple-Row Subqueries
Description
More than the highest value returned by the subquery
Less than the lowest value returned by the subquery
Less than the highest value returned by the subquery
More than the lowest value returned by the subquery
Equal to any value returned by the subquery (same as IN)
Multiple-Row Subqueries
❑Multiple Row Subqueries return more than one row of results.
❑Operators such as IN, ANY, ALL, or EXISTS.
❑ANY and ALL operators combine with other comparison operators to treat a subquery’s results as a set of values instead of single values.
Multiple-Column Subqueries
A multiple column subquery returns more than one column to the parent query and can be listed in the parent query’s FROM, WHERE or HAVING clause.
Nested Subqueries
The innermost subquery is executed first, then the next highest level subquery is executed, and so on, until the outermost query is reached.
Correlated Subqueries vs Uncorrelated Subqueries
❑The sequences of uncorrelated subqueries process are as follows:
1) Inner query is executed first
2) The result is then passed from the inner query to the parent query
3) Parent query is executed
❑ On the other hand, the process sequence for correlated subqueries is
different:
1) Inner query is executed once for each row processed by the parent query
2) Inner query references the row contained in the parent query
Logistics Database
Consider the Logistics Database from https://www.w3schools.com/sql/sql_any_all.asp
Tables in the database:
Customers (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country) Category (CategoryID, CategoryName, Description)
Employees (EmployeeID, LastName, FirstName, BirthDate, Photo, Notes)
Suppliers (SupplierID, SupplierName, ContactName, Address, City, PostalCode, Country, Phone) Products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price)
Shippers (ShipperID, ShipperName, Phone)
Orders (OrderID, CustomerID, EmployeeID, OrderDate, ShipperID) OrderDetails (OrderDetailID, OrderID, ProductID, Quantity)
Exercise 1
List the product name if it finds any records in the OrderDetails table has quantity equal to 3.
( SELECT ProductID
ProductName Products ProductID = ANY
FROM OrderDetails
WHERE Quantity = 3 );
Exercise 2
List the customer’s name who has ordered a product with quantity of at least 80.
SELECT FROM WHERE
CustomerName Customers CustomerID = ANY CustomerID Orders
OrderID = ANY
1. Find OrderID with Quantity >= 80 in OrderDetails table;
2. Find CustomerID in Orders table using the eligible OrderID in Step 1;
3. List all the eligible customers’ names in Customers table.
Customers (CustomerID, CustomerName, …)
Orders (OrderID, CustomerID, …)
OrderDetails (OrderDetailID, OrderID, …, Quantity)
FROM WHERE
FROM WHERE
OrderID OrderDetails Quantity >= 80
Exercise 3
List the product name if ALL the records in the OrderDetails table has quantity equal to 3.
SELECT FROM WHERE
FROM WHERE
ProductName Products ProductID = ALL ProductID OrderDetails Quantity = 3
Exercise 4
List the customer’s name who has ordered the same products as customer 3.
(Please use this link https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join) SELECT DISTINCTCustomerName
FROM WHERE
FROM WHERE
Customers JOIN Orders USING (CustomerID) JOIN OrderDetails USING (OrderID) ProductID IN
OrderDetails JOIN Orders USING (OrderID)
CustomerID = 3
Source: stacker.com
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com