讲解 QHE5701、辅导 SQL程序设计
QHE5701 – Database Systems 2024
Lab – 6 1
Lab 6: Data Warehouse Design and SQL Query Development for Business Insights
In this lab you’ll use MySQL to create queries for the database provided in sql script.
Database Specification:
In Lab – 5, you created a company that manages a big chain of doughnut-selling shops.
The client needed a database for the data accumulated through the last few years.
The client has now provided additional data for your reference. For your ease, this data is
in the form of a database. SQL script for the database is provided, use Lab6.sql
However, the data provided is much more than you will need so you will have to consider
what to include. The company wants a data warehouse which allows them to answer the
questions (or at least as many as possible for these) for which you need to write SQL
queries.
Task Description:
Task – BASIC:
As a requirement from the client, you need to provided solution for these basic questions
as part of the data analysis. You are required to create queries for all BASIC questions.
i) List the branches in Asia.
ii) Create a list of full-time Employee
iii) What is the average number of sales in each month?
iv) What is the total revenue of the company (sum of prices associated with each
sale)?
v) What is the percentage of registered sales, which originate from police oTicers?
vi) What is the distribution of gender among registered Customers?
Task – MEDIUM:
Client wants you to create solution for the following questions to help the client in
analysis of their sales and revenue. From the following MEDIUM questions, you can select
any 3 of the following questions to create SQL queries. QHE5701 – Database Systems 2024
Lab – 6 2
i) Which is the branch that generates the most revenue (sum of price based on
sales)?
ii) Which ingredients are generating the most sales?
iii) What size of the branch performs the best (in terms of revenue generated)?
iv) Find the worst 5 part-time employees according to sales.
v) Which continent generates the least sales?
Task – ADVANCED:
Create SQL queries that to answer the following business insight queries to help the
client in their business expansion. From the following ADVANCED questions, you can
select any 1 of the following questions to create SQL query.
i) Find the country, that if it would be sanctioned, would mean the biggest disruption
to the supply chain (consider both most products and sales aTected separately)
ii) What is the most profitable ingredient? Remember to consider the price of that
ingredient.
iii) Find the best menu. Consider: Volume of sales of specific menu * (the price of a
given pastry - the cost of production of it)
NOTE: Use the provided SQL script (Lab6.sql) to generate an example Data Warehouse
for this exercise.
Lab Submission Guidelines:
• Include a title page with your full name, module name, QMUL ID Number, BUPT ID
Number, Class Number, and lab number.
• Use a clear and organized layout.
• Clearly label each task (e.g., Lab 5 – Task (i), Lab 5 – Task (ii), Lab 6 – Task Basic (i),
Lab 6 – Medium (i), etc.) QHE5701 – Database Systems 2024
Lab – 6 3
• Provide SQL code, outputs (screenshots), and any explanation (including
assumptions) where necessary especially in case of designing ER Model or
mapping ER to logical model (Lab – 5).
• Create a folder and place all your database files (check where you saved your
database in your laptop) along with the solution document (pdf) within the folder.
• Compress this folder and rename it with your QMUL ID number.
• Upload the compressed file on the QMPlus module page in Assessment Section.
NOTE: Submit both labs (Lab 5 and Lab 6) in a single report (Week - 3).