Introduction to Databases for Business Analytics
Week 7 Group Functions
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
SQL Query Structures
SELECT [DISTINCT | ALL] {| [column_expression AS new_name] [, …] } FROM table_name [alias] [, …]
[WHERE condition]
[GROUP BY column_list]
[HAVING condition] [ORDER BY column_list]
: indicates optional elements.
: indicates that the element may or may not appear. : indicates “or”.
: indicates the end of the statement.
SQL Aggregate Functions
COUNT : the number of rows containing a specified attribute. MAX : the maximum value encountered.
MIN : the minimum value encountered.
AVG : the arithmetic mean (average) for the specified. SUM : the total value for the specified numeric attribute.
Numeric functions yield only one single value.
SQL Aggregate Functions
How many vendors referenced in the PRODUCT table have supplied products with prices that are less than or equal A$10.00?
SQL for DBMS Oracle:
SELECT FROM WHERE
COUNT (DISTINCT V_Code) PRODUCT
P_Price <= 10.00;
UNIQUE vs. DISTINCT SELECT DISTINCT XY is correct ANSI SQL syntax.
SELECT UNIQUE XY is old Oracle SQL syntax (otherwise identical to DISTINCT). Note, you still do use UNIQUE to create tables and indexes:
CREATE TABLE Test (Attribute Numeric NOT NULL UNIQUE);
CREATE UNIQUE INDEX Unique_Index ON Table (Attribute) TABLESPACE Tablespace;
Note: Unique indexes guarantee that no two rows of a table have duplicate values in the key column(s). Non-unique indexes do not impose this restriction.
SQL Aggregate Functions Which product has the highest price?
SELECT MAX (P_Price) FROM PRODUCT;
This query displays the highest product price from the product table... 🙂 ...but does not give us the product details. 🙁
What we need here is a nested query (query in a query). The nested (inner) query is performed first.
SELECT FROM WHERE
P_Code, P_Description, P_Price
P_Price = (SELECT MAX(P_Price) FROM PRODUCT);
SQL Aggregate Functions What is the total value of all the items carried in inventory?
SELECT SUM(P_Onhand * P_Price) FROM PRODUCT;
* : here arithmetic operator for multiplying
What is the average of product price?
SELECT AVG(P_Price) FROM PRODUCT;
Grouping Data in SQL ❑ GROUP BY
❑ SELECT clause may contain column names, aggregate functions or constants. ❑ The GROUP BY clause is valid only when used with SQL aggregate functions.
Grouping Data in SQL
Find out how many product units are available from vendors with a vendor code of less than 21344.
GROUPBY V_Code,P_Description;
V_Code, P_Description, SUM(P_Onhand) PRODUCT
V_Code < 21344
Multiple Table Operations in SQL “Multiple table operations” are “joining operations”! (see also earlier)
❑SELECT clause identifies the attributes to be displayed.
❑FROM clause identifies the tables from which attributes are selected. ❑WHERE clause specifies the joining condition for common columns.
Multiple Table Operations in SQL For multiple tables queries, refer to attributes in the form table.attribute.
Student Registration Course
(StdNo, StdName)
(StdNo, CourseNo, Major, Grade) (CourseNo, CourseTitle)
List the student details for course number ‘COMM1822’:
SELECT FROM WHERE
StdNo, StdName, Major, CourseNo, Grade
Student, Registration
Student.StdNo = Registration.StdNo AND Registration.CourseNo = ‘COMM1822’;
Multiple Table Operations in SQL
Alternatively, use aliases instead of the full table names
List the student and course details for student number 38214:
SELECT FROM WHERE
StdName, CourseTitle, Grade Student S, Registration R, Course C S.StdNo = R.StudentNo
AND C.CourseNo = R.CourseNo AND R.StdNo = 38214
Logistics Database
Consider the Logistics Database from https://www.w3schools.com/sql/sql_count_avg_sum.asp or
https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_sum
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
Determine how many categories in the logistics company.
SELECT COUNT(*) FROM Categories;
Exercise 2
Display the number of products with a price of more than $36.00.
SELECT FROM WHERE
COUNT(*) Products Price > 36;
Exercise 3
Determine the total revenue generated by sales to customer 8. Note: Quantity should be reflected in the total revenue calculation.
SELECT SUM(Price * Quantity)
FROM Products, OrderDetails, Orders
WHERE OrderDetails.ProductID = Products.ProductID AND OrderDetails.OrderID = Orders.OrderID
AND CustomerID = 8;
Exercise 4
Determine the average revenue generated by orders in the ORDERS table. Note: The total revenue by order must be calculated before finding the average revenue.
SELECT AVG(RevenueSum) FROM
SELECT FROM WHERE AND GROUP BY
SUM(Price * Quantity) AS RevenueSum Products, OrderDetails, Orders Products.ProductID = OrderDetails.ProductID Orders.OrderID = OrderDetails.OrderID Orders.OrderID
Exercise 5
Determine the average price of products by supplier name and category name. Include only the categories Beverages and Condiments and the groups with an average price greater than $20.
SELECT SupplierName, CategoryName, AVG(Price)
FROM Products INNER JOIN Suppliers USING (SupplierID) INNER JOIN Categories USING (CategoryID)
WHERE GROUP BY HAVING
CategoryName IN (‘Beverages’, ‘Condiments’) SupplierName, CategoryName
AVG(Price) > 20;
Exercise 6
List the customers living in Berlin or London who have recently placed an order totalling more than $599.
SELECT FROM WHERE AND
AND GROUP BY HAVING
DISTINCT CustomerName
Customers, Products, Orders, OrderDetails Customers.CustomerID = Orders.CustomerID Orders.OrderID = OrderDetails.OrderID OrderDetails.ProductID = Products.ProductID (City = ‘Berlin’ OR City = ‘London’) Orders.OrderID, CustomerName
SUM(Price * Quantity) > 599;
Exercise 6 – More Explanation
List the customers living in Berlin or London who have recently placed an order totalling
more than $599.
Use Around the Horn for verification
Result with totalling > $599
Check City (London); Find CustomerID (Around the Horn’s CustomerID is 4)
SELECT City, CustomerID FROM Customers WHERE CustomerName = ‘Around the Horn’; Find Around the Horn’s Order(s) (Only two orders in the database with OrderID: 10355, 10383)
SELECT OrderID FROM Orders WHERE CustomerID = 4;
Find all relevant details, including product price and quantity, about the orders with OrderID = 10355, 10383
SELECT * FROM OrderDetails JOIN Products USING (ProductID) WHERE (OrderID = 10355 or OrderID = 10383);
Total = Price * Quantity = $1,723.75 Result with totalling > $1,723.75
If we set totalling more than $1,723.75, Around the Horn will be disappeared from the result!
Source: petcare.com.au
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com