Assignment
Q1: Consider the following tables:
|
Customers
|
|
CustomerID
|
LastName
|
FirstName
|
State
|
|
1
|
Cohen
|
Mike
|
CO
|
|
2
|
Highley
|
Michelle
|
MI
|
|
3
|
Cohen
|
Margot
|
MI
|
|
Orders
|
|
OrderID
|
CustomerID
|
Ordered_Item
|
Date_Ordered
|
|
1
|
3
|
DVD Movie
|
1/1/2009
|
|
2
|
1
|
Computer Book
|
3/1/2009
|
|
3
|
2
|
Music CD
|
2/1/2009
|
Write an SQL statement that will return the following result:
|
Name
|
Location
|
Item
|
Date
|
|
Margot Cohen
|
OH
|
DVD Movie
|
1/1/2009
|
|
Michelle Cohen
|
GA
|
Music CD
|
2/1/2009
|
|
Mike Cohen
|
CO
|
Computer Book
|
3/1/2009
|
Q2. Using the two tables above, write an SQL statement that uses a SUB QUERY and the IN keyword to return the First Name of the customers who live in the state of MI.
Q3. Tell why the following SQL statement is incorrect. Rewrite the SQL statement to make it correct. (Hint: The fields of the table are StudentName, Assignment, Grade. A student's name can be in the table more than once since they could have several assignments.)
SELECT StudentName, AVG(Grade)
FROM GradeBookTable