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). 
 
請加QQ：99515681  郵箱：99515681@qq.com   WX：codinghelp