2023年11月25日

数据分析代写 | 7SSMM603 Research Methods Data Analysis Essay 20% of the Module Assessment

本次澳洲作业是SQL代写的一个assignment

St Clemens’s Grocery is a grocery store that specialises in fresh produce delivery (primarily fruit and
vegetables) to local restaurants (‘trade’) in the Brunswick, Carlton, Parkville, Coburg and Essendon
area. During the Covid19 pandemic they also branched out into contactless delivery for residential
(‘retail’) customers.

Questions

Write a single SQL statement to answer the following questions. Do not use inline views / schema on
read, views unless explicitly instructed to do so. Views inline views and schema on read for Q1-Q9
will earn 0 marks.

1. List all produce that is currently available to order. List the name and price. List the
result by produce name in alphabetical order.
(10 marks)

2. List all produce that has four or more different prices (exclude produce that is not
available)
(10 marks)

3. List the number of orders for each month of each year. Order the result by month then
year.
(10 marks)

4. List all clients who placed an order in one month and received the produce in a
different month. Be sure to eliminate duplicates from your result set.
(15 marks)

5. List the name of the produce that has never been available to order.
(30 marks)

6. List the name of the produce that has always been available to order but has never
been ordered.
(30 marks)

7. List the order number, full client name and amount charged which needs to be
refunded for produce ordered but not shipped
(20 marks)

8. List all trade customers who have ordered less than 10 orders in 2020. Order the by
trade name in alphabetical order.
(15 marks)

9. List the total cost of all orders for all trade clients excluding produce not shipped
(20 marks)

10.

a. Write the SQL DDL to create a view that lists the client name, month name,
order count per month, order cost per month
(20 marks)

b. Using the View you created in Task 10a, write a query to identify who placed
the highest in order cost, and the highest in order count in the month of
January 2021
(20 marks)