COURSE: DATABASE

PLEASE: HAND WRITING AND PICTURE DON'T PREFERRED

Given the three tables below, write the following queries.

Customer

custNo	custName	custSt	custCity	age
1	C1	Olaya St	Jeddah	20
2	C2	Mains St	Riyadh	30
3	C3	Mains Rd	Riyadh	25
4	C4	Mains Rd	Dammam
5	C5	Mains Rd	Riyadh

Product

prodNo	prodName	prodDes	price
100	P0	Food	100
101	P1	healthy Food	100
102	P2		200
103	P3	self_raising flour,80%wheat	300
104	P4	network 80x	300

Order

ordNo	ordDate	custNo	prodNo	quantity
R03010101	01-jan-2003	C01	100	2
R03010203	02-jan-2003	C01	101	1
R03010102	01-jan-2003	C2	102	1
R03010103	01-jan-2003	C343	100	2
R03010301	03-jan-2003	C01	101	1
R03030601	06-mar-2003	C2	100	10

1. A DDL command to build order table, including all integrity constraints. Make sure that ordDate value exists and quantity value is always positive.

2. A DML command to answer: How many orders were made by customer C01 and how many products pieces did he order?

3. A DML command to list customers who have ordered the product 100. (Hint: use join)

4. A DML command to find products with price more than average (Hint: use nested queries)