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


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.


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
(10 marks)

3. List the number of orders for each month of each year. Order the result by month then
(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)


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)


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

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