Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

DATA 2100 Major Homework

SQL Queries

100 points

Purpose

In this assignment, students will gain experience writing SQL SELECT statements in an Access database (if applicable, instructions can be viewed in the Creating a Database in Microsoft Access file) or will base their queries from the below database diagram and the AtomYak excel sheet if needed. This assignment requires students to apply an understanding of SQL clauses in general, column renaming, aggregate and analytical functions, multi-table joins (using the WHERE clause), use of table aliases and/or prefixes, conditional record inclusion using WHERE, aggregating data using grouped records, conditional inclusion of group using HAVING, and ordering outputs.
Submission: Write your answers/queries to the questions below under the question in a clearly labeled, well-formatted manner. You will submit this assignment in Canvas.

Background

In this assignment, students will “pull” data out of the database, described below in the ERD, that could be used for data analysis. Students should use the AtomYak database as discussed above. The diagram for this database is included below:

Assignment

Write SQL SELECT queries to address all of the data output requests listed below. For each request:

a. Include your query into the submission document

Once complete, submit via Canvas as a (single) Word document or PDF. Organize your submission in accordance with the numbering given below. Be sure the information in your document is presented clearly and unambiguously.

1. Write queries and provide output for each of the following (8 points each):

a. For all sales, output the invoice number, the customer ID, and the sales date.

b. For each customer from who has 1 or more children, output the customer ID and income.

c. For each Model ID, output how many of those models were sold in the time period of this dataset. Order the results by Model ID descending.

d. How many total dollars of sales has AtomYak gained from the selling of kayaks with the Model ID of 1?

e. On what three dates in June 2017 did the lowest total order revenue occur?

f. Write a subquery to find the total number of customers with a fishing license who have bought a kayak with a Model ID of 1.

g. Output the number of AtomShark units sold by each of the Dallas, Columbus, Cranberry, and Jacksonville sales centers.

h. For each customer, list the customer’s customer ID as well as the name of the kayak model purchased.

i. For the Pittsburgh sales center, what was the average recommendation score for each day of the month?

j. List the top five sales centers for making sales to customers that have had fishing licenses within the past year.

2. Come up with two queries that you think could help lead to important insight for the company (20 points). For each: