SQL Aggregate Function - Examine the following relations which are part of a database for a company selling horse accessories. The corresponding case study and ER diagram is given in Appendix A. PDF

Preview

Summary

Examine the following relations which are part of a database for a company selling horse accessories. The corresponding case study and ER diagram is given in Appendix A....

Description

Jeffrey Katabira Student Number: M00800833

Email: JK1125@ live.mdx.ac.uk

Week 7 GOALs Jan Answer Sheet Individual Task. Note: For all SQL tasks below you are MUST write the SQL code in the SQL query view in MYSQL. Examine the following relations which are part of a database for a company selling horse accessories. The corresponding case study and ER diagram is given in Appendix A. The code to create the above table is given in the HorseDB.sql file. Download the file and execute it. Use this database to answer the questions below: You will be marked on the following GOALs: These GOALs are all individual and I7.1. to I7.5 are worth 1 mark. For questions I7.2 to I7.5 below, write a SQL statement to: I7.1.

Explain the term ‘aggregate function’ and discuss their usage.

Answer This is a function where values of many tuples/rows are grouped together to form a single summary value. Function Aggregate functions perform calculations on sets of values thereafter returning one single value. COUNT returns no. of rows in a relation when column is not NULL. They are usually used with GROUP BY clause of SELECT statement.

Jeffrey Katabira Student Number: M00800833

I7.2.

Email: JK1125@ live.mdx.ac.uk

List the average product price.

Answer SELECT AVG (Price) FROM Product; Resultant Table (113.466667)

I7.3.

List the maximum and minimum credit limit of all customer.

ANSWER SELECT MAX(Credit_Limit), MIN(Credit_Limit) FROM Customer;

Resultant Table (6000MAX & 500MIN)

Jeffrey Katabira Student Number: M00800833

I7.4.

Email: JK1125@ live.mdx.ac.uk

List the customer number and name of all customers in descending order of customer name. Answer SELECT Customer_No, CName FROM Customer ORDER BY CNAME DESC; Resultant Tables

Scrolled down to get all the values

Jeffrey Katabira Student Number: M00800833

I7.5.

Email: JK1125@ live.mdx.ac.uk

List the product number, price and warehouse number of all product in ascending order of warehouse number and descending order of product price. Answer SELECT Product_No, Price, Supply_Warehouse_No FROM Product ORDER BY Supply_Warehouse_No ASC, Price DESC; Resultant Table

The following GOAL is worth 10 marks. You will be awarded a mark out of 10 based on the accuracy and complexity of the answer. I7.6. Write a SQL statement to “List for each warehouse that stock more than 2 products, the warehouse number, the number of products stocked and the average price of the products.” Use you answer to clearly explain how the group by and having functions work.

Below Is the code and screenshots I used to attain 17.6

Jeffrey Katabira Student Number: M00800833

Email: JK1125@ live.mdx.ac.uk

Answer SELECT Supply_Warehouse_No, COUNT (*), AVG(Price) AS Average_Price FROM Product GROUP BY Supply_Warehouse_No HAVING COUNT (*) > 2 ORDER BY COUNT (*) DESC;

Resultant Table

A short tutorial of my Steps Below (if needed)

Jeffrey Katabira Student Number: M00800833

Email: JK1125@ live.mdx.ac.uk

Attaining how many products each warehouse stores in Descending order using ORDER BY COUNT (*) DESC GROUP BY also used below.

Added a HAVING to attain warehouses that stock more than 2 products (Only 2 did)

Jeffrey Katabira Student Number: M00800833

Email: JK1125@ live.mdx.ac.uk

Found average Price of the products

For all SQL GOALs, the entry into your portfolio MUST contain the SQL code and a screenshot of the resultant table after the code is run.

References  En.wikipedia.org. 2021. Aggregate function. [online] Available at: [Accessed 12 March 2021].  Docs.microsoft.com. 2021. Aggregate Functions (Transact-SQL) - SQL Server. [online] Available at: [Accessed 12 March 2021]....