1. View

Suppose you have these two tables:

CREATE TABLE Customer (

CustName VARCHAR(64),

CustID INT PRIMARY KEY,

DateOfBirth DATE

);

CREATE TABLE Purchase (

CustID INT,

ProdID INT,

TransactionNum INT PRIMARY KEY

);

(a) Create a View called CustomerAgeGroup that has the following columns:

CustName, CustID, DateOfBirth, Age, AgeGroup.

The Age of a customer is his/her age (in number of years) at the time of query.

To accurately get the age, see Bryan Dennys answer here:

https://stackoverflow.com/questions/2533890/how-to-get-an-age-from-a-d-o-b-field-in-mysql

You may create an intermediate View if you need.

Classify into AgeGroups as follows:

0: Baby

1 - 2: Toddler

3 - 11: Child

12 - 17: Youth

18+: Adult

(b) Write a query to find the total number of purchases by all customers in the same AgeGroup.

2. Subquery

(a) Write a single SQL statement with a subquery to output ID of all customers who have purchased an item with ID 6.

(b) Write a single SQL statement with a subquery to get the Customer ID, Date of Birth and Name of all customers who are born later than the customer who made the most purchases.