SQL代写 | INFO90002 Assignment 2 – SQL St Clemens’s Grocery

本次澳洲代写是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)


程序代写代做C/C++/JAVA/安卓/PYTHON/留学生/PHP/APP开发/MATLAB


本网站支持淘宝 支付宝 微信支付  paypal等等交易。如果不放心可以用淘宝交易!

E-mail: itcsdx@outlook.com  微信:itcsdx


如果您使用手机请先保存二维码,微信识别。如果用电脑,直接掏出手机果断扫描。

blank

发表评论