b If the CatalogID of a Catalog in the Catalog table is changed then changes

B if the catalogid of a catalog in the catalog table

This preview shows page 5 - 6 out of 6 pages.

b. If the CatalogID of a Catalog, in the Catalog table, is changed, then changes are automatically propagated to the Stamp table. This ensures that a Stamp will always only appear in an existing Catalog. 5. The following SQL code fragment aims to list the names and emails of the collectors who ordered Canadian stamps that are priced higher than $1,000. The names and emails of the collectors should be displayed by their country of residence . Please correct the fragment. (4) SELECT C.email, C.lastname, C.firstname FROM Collector C, Order O, Stamp S, Catalog G WHERE S.price > 1000 AND G.Country = “Canada”; SELECT C.email, C.lastname, C.firstname FROM Collector C, Order O, Stamp S, Catalog G WHERE C.collectorID = O. CollectorID AND O.StampID = S.StampID AND S.CatalogID = G.CatalogID AND S.price > 1000 AND G.Country = “Canada” GROUP BY C.Country;
Image of page 5
Page - 6 - of 6 6.What does the following SQL code fragment compute? (2) 7. Give the SQL to find the full names and emails of all collectors who bought Estonian stamps in 2010, but never bought any Icelandic stamps. (5) SELECT C.lname, C.fname, C.email FROM Collector C, Order O, Stamp S, Catalog G WHERE C.collectorID = O. CollectorID AND O.StampID = S.StampID AND S.CatalogID = G.CatalogID AND G.country = ‘Estonia’ AND G.Date-of-issues LIKE ‘%2010’ AND C.CollectorID NOT IN (SELECT C2.CollectorID FROM Collector C2, Order O2, Stamp S2, Catalog G2 WHERE C2.collectorID = O2. CollectorID AND O2.StampID = S2.StampID AND S2.CatalogID = G2.CatalogID AND G2.country = ‘Iceland’); Note: Other correct solutions are, of course, also acceptable.
Image of page 6

You've reached the end of your free preview.

Want to read all 6 pages?

  • Spring '15
  • Relational model, Foreign key, primary key, Swifty database

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture