代做QHE5701、代写SQL程序设计

日期：2024-12-02 09:21

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.

You are allowed to resubmit before the deadline. In case you face any issues, email me

([email protected]).

NOTE: Submit both labs (Lab 5 and Lab 6) in a single report (Week - 3).