• Please submit your assignment on git till 11.10.
2024 (23:59 pm)
• Please SUBMIT your final queries, DON’T change on git!
Assignment 2
You are given a database with five tables:
customers:
Columns: customer_id (integer), customer_name (text), email (text), phone (text),
address (text).
orders:
Columns: order_id (integer), customer_id (integer), order_date (date), total_amount
(numeric).
order_items:
Columns: order_item_id (integer), order_id (integer), product_id (integer), quantity
(integer), unit_price (numeric).
products:
Columns: product_id (integer), product_name (text), category_id (integer), description
(text).
categories:
Columns: category_id (integer), category_name (text).
Create these tables and insert your own 10 records on each table.
Exercise 1: CTE
a. Create a CTE named order_summary that provides a summary of orders. The CTE should
include the following columns: order_id, customer_name, order_date, total_amount, and the
total quantity of items in the order.
b. Create a CTE named product_sales that provides a summary of product sales. The CTE
should include the following columns: product_id, product_name, category, and the total
revenue generated by selling that product.
Exercise 2: Query Optimization
Write queries that retrieve:
• The customer who has made the most orders.
• The product that has been ordered the most in terms of quantity.
• The product that has generated the most revenue.
• The total revenue generated by the customer with the most orders.
