Introduc on to Databases CS162 Guest Lecture Sam Madden madden csail mit edu Zoo Data Model En ty Rela onship Diagram 1 Animal name 1 age contains 1 en ty rela onship Cage 1 en ty feedTime 1 Time Name keeps 1 species 1 1 bldg Age Keeper Species Animals have names ages species Keepers have names Cages have cleaning mes buildings Animals are in 1 cage cages have mul ple animals Keepers keep mul ple cages cages kept by mul ple keepers 1 en ty Building name 1 Name Our Zoo Sam the Salamander Slimy Stoica the Shrew Skinny Energe c Sally the Student SQL Structured Query Language SELECT field1 fieldM FROM table1 WHERE condition1 INSERT INTO table VALUES field1 UPDATE table SET field1 X WHERE condition1 Names of Shrews Impera ve for each row r in animals if r species shrew output r name Declara ve SELECT r name FROM animals WHERE r species shrew Cages in VLSB Impera ve for each row a in animals for each row c in cages if a cageno c no and c bldg VLSB output a Declara ve SELECT a name FROM animals AS a cages AS c WHERE a cageno c no AND c bldg VLSB Average Age of Bears Declara ve SELECT AVG age FROM animals WHERE species bear Complex Queries Find pairs of animals of the same species and di erent genders older than 1 year SELECT a1 name a2 name FROM animals as a1 animals as a2 WHERE a1 gender M and a2 gender F AND a1 species a2 species self join AND a1 age 1 and a2 age 1 Find cages with shrews fed later than the average feed me of any cage SELECT cages cageid FROM cages animals WHERE animals species shrew AND animals cageid cages cageid AND cages feed me nested queries SELECT AVG feed me FROM cages Complex Queries 2 Find keepers who keep both shrews and salamanders SELECT keeper name FROM keeper cages as c1 cages as c2 keeps as k1 keeps as k2 animals as a1 animals as a2 WHERE c1 cageid k1 cageid AND keeper keeperid k1 keeperid AND c2 cageid k2 cageid AND keeper keeperid k2 keeperid AND a1 species shrew AND a2 species salamander AND c1 cageid a1 cageid AND c2 cageid a2 cageid a1 c1 k1 species shrew a2 species salamander keeper keeperid k1 keeperid keeper c2 k2 keeper keeperid k2 keeperid
View Full Document
Unlocking...