Scribd
Upload
24 views2 pages

Mysql Subquery Tasks

The document outlines the structure of four database tables: Customers, Orders, Products, and Order_Items, detailing their columns and data types. It also presents seven tasks involving SQL queries, such as finding customers with above-average orders, listing products not purchased recently, and retrieving the top three most expensive orders. Each task requires specific queries to extract relevant data from the tables.

Uploaded by

journeysyntax
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
24 views2 pages

Mysql Subquery Tasks

The document outlines the structure of four database tables: Customers, Orders, Products, and Order_Items, detailing their columns and data types. It also presents seven tasks involving SQL queries, such as finding customers with above-average orders, listing products not purchased recently, and retrieving the top three most expensive orders. Each task requires specific queries to extract relevant data from the tables.

Uploaded by

journeysyntax
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

MySQL Subquery Tasks

Table Structures

Customers Table:
Column Name | Data Type | Description
----------------------------------------
customer_id | INT | Primary key
name | VARCHAR | Name of the customer
email | VARCHAR | Email address
city | VARCHAR | City where customer lives
age | INT | Age of the customer

Orders Table:
Column Name | Data Type | Description
----------------------------------------
order_id | INT | Primary key
customer_id | INT | Foreign key from Customers
order_date | DATE | Date of the order
total_amount | DECIMAL | Total amount of the order

Products Table:
Column Name | Data Type | Description
----------------------------------------
product_id | INT | Primary key
product_name | VARCHAR | Name of the product
price | DECIMAL | Price of the product

Order_Items Table:
Column Name | Data Type | Description
----------------------------------------
order_item_id | INT | Primary key
order_id | INT | Foreign key from Orders
product_id | INT | Foreign key from Products
quantity | INT | Quantity of the product
Task 1: Find Customers with Orders Greater than Average Order Amount
Write a query to find all customers whose total order amount is greater than the average
order amount of all customers.

Task 2: List Products Not Purchased in the Last 6 Months


Write a query to display all products that have not been purchased in the last 6 months.

Task 3: Find the Oldest Customer who Placed an Order


Write a query to find the name of the oldest customer who has placed an order.

Task 4: Get Total Revenue per Customer


Write a query to calculate the total revenue generated by each customer.

Task 5: List Orders with More Than 3 Products


Write a query to find all orders that have more than 3 products.

Task 6: Find Customers Who Have Ordered Every Product


Write a query to find customers who have ordered at least one of every product available.

Task 7: Retrieve the Top 3 Most Expensive Orders


Write a query to retrieve the top 3 orders with the highest total amount.

You might also like