Answers for ONGD Revision. lolz

ONGD Re-test 5/2/08 LT4 3pm-5pm D=

2a)
SELECT SUM(Quantity) FROM stamp_album;

b)
SELECT Member_ID, Count(*) "Number of Albums" FROM album WHERE Member_ID IN ('S03','S05') GROUP BY Member_ID;

c)
SELECT a.album_ID, a.album_theme, SUM(s.quantity) FROM album a, stamp_album s WHERE a.album_id = s.album_id GROUP BY a.album_ID, a.album_theme;

d)
SELECT * FROM stamp WHERE Country = 'Singapore' AND date_issued between '01-Jan-60' AND '31-Dec-70' AND Price > 15 AND Stamp_desc LIKE '%Orchid%';

3a)i)
DELETE FROM member WHERE member_id = 'S05';

ii)
This operation will fail as it violates Referential integrity constraint. The value from member_id is referenced by album_table.

b) i)
INSERT INTO stamp VALUES (29501, 'U.S.A(Hawaii)','150th Anniversary of the Honolulu Advertiser','2006-07-02',26);

ii)
This operation will succeed. the stamp_id is unique. It does not violate the Primary Key constraint.

e)i)
UPDATE stamp_album SET album_ID = 5 WHERE stamp_id = 10000 AND album_id = 1;
ii)
This operation will fail as it violates referential integrity constraint. there is no album_ID 5 in the album table.