Each part was worth 5 points

6.1.2 (b, d, e)
6.1.3 (c, d, f)
6.1.5 (b, c, e)
6.2.1 (b, c, e)
6.2.2 (c, d, e)
6.3.1 (b, c, d, e)
6.3.3
6.3.5 (b)
6.3.7 (c)
6.3.9

Total: 115 points

6.1.2

b) SELECT birthdate FROM MovieStar WHERE name = 'Sandra Bullock';

d) SELECT * FROM MovieExec WHERE netWorth >= 10000000;

e) SELECT * FROM MovieStar WHERE gender = 'Male' OR address LIKE '%Malibu%';

6.1.3

c) SELECT maker FROM Product, Printer WHERE Product.model = Printer.model;

simpler answer:

SELECT maker FROM Product WHERE type = 'printer';

results:

maker
-----
F
C
C
F
G
C
H

You can also change it to "SELECT DISTINCT maker..." to return only unique makers without duplicates


d) SELECT model, ram, screen FROM Laptop WHERE price > 2000;

results:

model   ram   screen
-----   ---   ------
2002    96    15.1
2003    64    15.1
2005    64    12.1
2006    96    15.7
2007    128   15.0
2009    256   15.1


f) SELECT model, speed, hd FROM PC WHERE (rd = '12xDVD' OR rd = '16xDVD') AND price < 2000;

results:

model   speed   hd
-----   -----   --
1004    866     10
1005    1000    20
1009    1200    80
1011    1100    60

6.1.5

b) All tuples where a = 10 and b = 20 and are both are nonnull

c) All tuples where a is nonnull, and b can be any value, including null

e) All tuples where a <= b and both are nonnull

6.2.1

b) SELECT starName FROM StarsIn, Movie
WHERE Movie.title = StarsIn.movieTitle
AND Movie.year = StarsIn.movieYear
AND studioName = 'MGM'
AND year = 1995;

c) SELECT name FROM MovieExec, Studio
WHERE Studio.name = 'MGM'
AND MovieExec.cert# = Studio.presC#;

e) SELECT name FROM MovieExec
WHERE netWorth > (SELECT netWorth FROM MovieExec WHERE name = 'Merv Griffin');

6.2.2

c) SELECT maker FROM Product, Laptop
WHERE Product.model = Laptop.model
AND maker NOT IN (SELECT maker FROM Product, PC WHERE Product.model = PC.model);

alternative:
SELECT maker FROM Product, Laptop
WHERE Product.model = Laptop.model
AND maker NOT IN (SELECT maker FROM Product WHERE type = 'pc');

alternative:
SELECT maker FROM Product WHERE type='laptop' AND type<>'pc';

d) SELECT hd FROM PC GROUP BY hd HAVING COUNT(*) > 1;

alternative:
SELECT DISTINCT hd pc1.hd FROM PC pc1, PC pc2
WHERE pc1.hd = pc2.hd AND pc1.model <> pc2.model;

e) SELECT pc1.model, pc2.model FROM PC pc1, PC pc2
WHERE pc1.speed = pc2.speed
AND pc1.ram = pc2.ram
AND pc1.model < pc2.model;

6.3.1

b) SELECT model FROM Printer WHERE >= ALL (SELECT price FROM Printer);

c) SELECT model FROM Laptop WHERE speed < ALL (SELECT speed FROM PC);

alternative:
SELECT DISTINCT model FROM Laptop L
WHERE NOT EXISTS (SELECT speed FROM PC WHERE PC.speed <= L.speed);

d) SELECT R.model FROM

(SELECT model, price FROM Printer
UNION SELECT model, price FROM PC
UNION SELECT model, price FROM Laptop) AS R

WHERE R.price >= ALL
(SELECT price FROM Printer
UNION SELECT price FROM PC
UNION SELECT price FROM Laptop);

e) SELECT maker FROM Product
WHERE model IN
(SELECT model FROM Printer WHERE color
AND price <= ALL (SELECT price FROM Printer WHERE color));

6.3.3
SELECT title FROM Movie Movie1, Movie Movie2
WHERE Movie1.year < Movie2.year
AND Movie1.title = Movie2.title;

6.3.5 (b)
SELECT name, address FROM MovieStar
WHERE (name, address) NOT IN
(SELECT name, address FROM MovieExec);

6.3.7 (c)
The result has 7 attributes (the 4 attributes from MovieStar and 3 from StarsIn).
The full outer join means that when a MovieStar does not appear in StarsIn, the StarsIn
attributes are NULL, and when someone in StarsIn does not have an entry
in MovieStar, the MovieStar attributes are NULL.

6.3.9
SELECT * FROM Classes NATURAL RIGHT OUTER JOIN Ships

also:
SELECT * FROM Ships NATURAL LEFT OUTER JOIN Classes