Assignment Information

The following relational schema (primary keys are underlined) are used by the Food Share Program as described in Assignment 1.

MemberAccounts(accnum, contactName, email, deliveryAddress, signUpDate, activationDate, status)
Producers(accnum, farmAddress)
FoodItems(productCode, name, unit)
SupplyRecords(accnum, productCode, supplyDate, quantity)
Orders(orderNum, placedBy, placeTime, packageType, expectDate, deliverTime, receivedBarcode, receivedPNum)
Batches(barcode, packingTime, sizeType)
Packages(barcode, pNum)
PackageContent(barcode, product, quantity)

This assignment is based on the above relational schema.

Many more details, including constraints, data types, etc, are captured in the SQL definitions of these tables.

You may use the supplied table definitions to create empty tables. You will then need to populate the tables (insert sample data) yourself in order to test your SQL queries.

Your Tasks:

Express each of the following queries in a single SQL query against the relational schema defined above.

1. The Food Sharing Program wants to know how many active and inactive members it has respectively. The result from your query should be easily understandable.
2. For each member who signed up to the Food Sharing Program the earliest, list their contact name, email and current status.
3. List the contact name, email, and farm address of each major broccoli producer of 2024. The "major broccoli producer of 2024" is defined as the producer who supplied at least 10,000 standard units of broccoli (whose name is stored as 'Broccoli' in the FoodItems table) in total in 2024.
4. For each active member (whose status is 'Active'), list their contact name, email, and the total number of orders they've placed. If an active member has never placed any order, their contact name and email should still appear in the result, and their total number of placed orders should be listed as 0. Sort the final result in descending order of the total number orders placed by these active members, then in ascending order of their contact names.
5. The Food Sharing Program wants to know which sized package is the most popular one in 2024. That is, which sized package (package type) was ordered the most in 2024.

How to Submit

You are expected to include all queries in a TEXT file.

Make your queries logically correct, easy to read and easy to understand.

Make sure that your SQL queries can be successfully executed by Oracle database engine.

You can submit your assignment solutions using one of the following three ways:

• Submit a hard copy before the due date/time.
• Login to csci server, in the directory that holds all the files you want to submit, execute the following command:
  ~liuh/bin/submit 370 A5 .
  This submit script currently accept only .sql and .txt files.
  You can use the following command to check what you have submitted to A5:
  ~liuh/bin/checksubmit 370 A5