Module Code: BUS2DAC
Module Subject: Data Analytics Concepts
Assignment 2: Data retrieval using SQL
Over the past few weeks, you have gained a general understanding of business databases and how to use SQL to query and manipulate business data. This assignment will provide you with an opportunity to demonstrate what you have learned through a business database.
We will make use of the data contained in BUS2DAC.OrderSupplier schema within the lmban-teaching.database.windows.net server. Please refer to Week 6 Tutorial for instructions on how to access the server via DBeaver if you have not accessed it already.
Question 1 (2 marks)
- 1.1 (1 mark) For each table, describe the primary keys and foreign keys if exist.
- 1.2 (1 mark) Describe the four relationships between the five tables.
- Customers and Orders
One order is sent by one and only one customer. One customer can send multiple orders.
- Orders and Order Item
One order may have multiple order items. One order items belongs to one and only one order.
- Products and OrderItem
One product may appear in multiple order items. One order item belongs to one and only one product.
- Products and Suppliers
One supplier may order multiple products. One product may be ordered by multiple suppliers.
Question 2 (8 marks) Run the following code and state (in natural language that a general audience can understand) the question that the code responses. (Note that these statements will work in SQL Server and may not in other SQL-dialects).
- 2.1 (2 marks)
- 2.2 (2 marks)
- 2.3 (2 marks)
- 2.4 (2 marks)
Question 3: (20 marks) Write SQL queries to RETURN A SINGLE TABLE containing the information required to answer each question.
- 3.1 (2 marks)
|Question||Display first name, last name and phone of customers from USA.|
|Code||SELECT FirstName, LastName, Phone, Country
WHERE Country = ‘USA’
|Returned table||(Total 13 rows returned)|
- 3.2 (2 marks)
|Question||What is the total sale amount the company obtained in the first quarter of 2014?|
- 3.3 (2 marks)
|Question||Display the total quantity sold for each product in each month of 2013.|
- 3.4 (2 marks)
|Question||Which suppliers supply at least three products?|
- 3.5 (2 marks)
|Question||Which products has the highest total dollar sale?|
- 3.6 (2 marks)
|Question||Display name and phone number of the top three customers that have spent most for coffee products?|
- 3.7 (2 marks)
|Question||Which products that were sold to German customers but never sold to Denmark customers?|
- 3.8 (2 marks)
|Question||Which customers have ordered both sauce and spread?|
- 3.9 (4 marks)
|Question||In the next marketing campaign, the company wants to offer some promotion to its existing customers in a chosen city. To help the manager to decide on the city to run the campaign and the list of promoted products that can lead to a successful campaign, please:
– Propose a suitable ranking criterion to choose the city (provide justification)
– Propose a ranking criterion you think is best suit to choose the products (provide justification).
– Provide the corresponding code to return:
+ the most favourite city based on your criterion.
+ the list of top ten promoted products based on your criterion.
|Your criterion to choose city|
|Your criterion to choose song|
The marker in assessing your work will use the following marking guide.
Full marks for the correct answer. Half of the mark for something close. The relationship should be described in natural language and easy to understand for a general audience who do not know about databases and SQL.
Full marks for each correct description in natural language and easy to understand for a general audience who do not know about databases and SQL. Half of the mark for something close.
Full marks for each faultless SQL statement (i.e., run without errors and return a correct output). Half of the mark for something close. Reasonable justification is clearly described for each proposed criterion in Question 3.9.
“You can order BUS2DAC – Data Analytics Concepts Assignment from our service and can get a completely high-quality custom paper. Our service offers any of the BUS2DAC Academic paper written from scratch, according to all customers’ requirements, expectations and highest standards.”
100% Plagiarism Free
24X7 Customer Support
Leave A Comment