Module Code: BUS2DAC

Module Subject: Data Analytics Concepts

Assignment 2: Data retrieval using SQL

Overview

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.

Specific Requirements

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

FROM OrderSupplier.Customers

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?
Code SELECT

FROM

Returned table  

 

  • 3.3 (2 marks)
Question Display the total quantity sold for each product in each month of 2013.
Code
Returned table

 

  • 3.4 (2 marks)
Question Which suppliers supply at least three products?
Code  
Returned table  

 

  • 3.5 (2 marks)
Question Which products has the highest total dollar sale?
Code  

 

Returned table  

 

  • 3.6 (2 marks)
Question Display name and phone number of the top three customers that have spent most for coffee products?
Code  
Returned table  
  • 3.7 (2 marks)
Question Which products that were sold to German customers but never sold to Denmark customers?
Code  

 

Returned table  
  • 3.8 (2 marks)
Question Which customers have ordered both sauce and spread?
Code  
Returned table  

 

  • 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  
Code  
Returned table  
Your criterion to choose song  
Code  
Returned table  

 

Marking rubrics

The marker in assessing your work will use the following marking guide.

Question 1:

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.

Question 2:

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.

Question 3:

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

  • Multiple Revisions

  • 100% Confidential

  • On-time Delivery

  • 500+ Experts

Order Now

Over 5,000
4.9 Star Ratings

Click Here! Get Free Assistance

Rated 4.9/5 by 5,000 Students

Get Solution Now

Leave A Comment

Related Posts