Do some joins of the pet and event tables. Assume name is the primary key (PK) of pet and the foreign key (FK) in event. pet is the parent, event is the child in this relationship. The FK of event references the PK of pet. Each row of pet can have its PK as the FK of many rows of event. [We could make this change now, but we'll do it later so that first we'll be able to do some "bad" changes to the tables.] --combine each pet row with each event row: i.e. Cartesian product. How many rows? See that most are garbage. SELECT * FROM pet,event; --combine each pet with its events. i.e. natural join on name column. SELECT * FROM pet,events WHERE pet.name=event.name; --same as previous query, but only display name, species, and event type SELECT pet.name,species,type FROM pet,event WHERE pet.name=event.name; --sex of the pets that had litters (duh) SELECT pet.name,sex FROM pet,event WHERE pet.name=event.name AND type='litter'; --owner's whose pets have had birthdays: SELECT DISTINCT owner FROM pet,event WHERE pet.name=event.name AND type='birthday'; --Harold's pets' events: SELECT pet.name,type FROM pet,event WHERE pet.name=event.name AND owner='harold'; --age of pets on visits: (hint: subtract 2 Date values) SELECT pet.name,type,(eventdate-birth)/365 FROM pet,event WHERE pet.name=event.name; --pets who visited when no more than one year old: SELECT pet.name,type,(eventdate-birth)/365 AS age FROM pet,event WHERE pet.name=event.name AND (eventdate-birth)/365<=1; --the age of oldest pet at the time of its event SELECT Max(eventdate-birth)/365 AS "Max age at event" FROM pet,event WHERE pet.name=event.name; --the number of events per species SELECT species,Count(*) FROM pet,event WHERE pet.name=event.name GROUP BY species; --the number of events per species, in increasing order SELECT species,Count(*) FROM pet,event WHERE pet.name=event.name GROUP BY species ORDER BY Count(*) DESC; --the species with the most events (hint: top N analysis) SELECT * FROM (SELECT species,Count(*) FROM pet,event WHERE pet.name=event.name GROUP BY species ORDER BY Count(*) DESC) WHERE Rownum<=1; --the average cuteness of the birthday pets SELECT Avg(cuteness) FROM pet,event WHERE pet.name=event.name AND type='birthday'; --owners who had events 12 or more years ago (hint: Sysdate) SELECT owner,(Sysdate-eventdate)/365 FROM pet,event WHERE pet.name=event.name AND (Sysdate-eventdate)/365>12; --do a select that would show any pets who never had any events (hint: left outer join) SELECT pet.name FROM pet LEFT JOIN event ON pet.name=event.name WHERE event.name IS NULL; --do a select that would show any referential integrity problems of an event that does not have an existing pet PK as its FK SELECT event.name FROM event LEFT JOIN pet ON pet.name=event.name WHERE pet.name IS NULL; --do a select that would show any event that does not have a FK value (hint: not a join) SELECT * FROM event WHERE name IS NULL; Add a row into pet for a new pet. INSERT INTO pet VALUES('Garfield,'Al','cat','f','1-Dec-2000','12-Dec-2000',12,99); Now repeat the: --do a select that would show any pets who never had any events (hint: left outer join) SELECT pet.name FROM pet LEFT JOIN event ON pet.name=event.name WHERE event.name IS NULL; Add a row into event with a name that does not exist in pet. We can do this because the tables have not been declared to be in a PK-FK relationship. INSERT INTO event VALUES('Sluggo','12-Dec-2000','lobotomy','excellent prognosis'); Now repeat the: --do a select that would show any referential integrity problems of an event that does not have an existing pet PK as its FK SELECT event.name FROM event LEFT JOIN pet ON pet.name=event.name WHERE pet.name IS NULL; Now make these tables somewhat better relational tables by adding a primary key for pet and a foreign key for event: (these keys are not actually required to do joins). ALTER TABLE pet ADD PRIMARY KEY(name); ALTER TABLE event ADD FOREIGN KEY(name) REFERENCES pet; (hint: delete Sluggo) pet is the parent, event is the child in this relationship. The FK of event references the PK of pet. Each row of pet can have its PK as the FK of many rows of event. Now try to add Sluggo back to event. Try to set a PK of a row of pet to Null. Try to set a PK of a row of pet to a non-Null name not found in event.