SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE PetOwners (ownerID integer NOT NULL, ownerName VARCHAR(25), CONSTRAINT PetOwners_PK PRIMARY KEY (ownerID)); CREATE TABLE Breeds (breedID CHAR(8) NOT NULL, type enum('dog','cat','bird','fish'), breedName VARCHAR(35), CONSTRAINT Breeds_PK PRIMARY KEY (breedID)); CREATE TABLE Pets (petID integer NOT NULL, breedID CHAR(8) NOT NULL, petName CHAR(25), CONSTRAINT Pets_PK PRIMARY KEY(petID), CONSTRAINT Pets_FK FOREIGN KEY (breedID) REFERENCES Breeds (breedID)); CREATE TABLE Owns (ownerID integer NOT NULL, petID integer NOT NULL, CONSTRAINT Owns_PK PRIMARY KEY(ownerID, petID), CONSTRAINT Owns_Pets_FK FOREIGN KEY(petID) REFERENCES Pets(petID), CONSTRAINT Owns_PetOwners_FK FOREIGN KEY(ownerID) REFERENCES PetOwners(ownerID)); /*** Insert statements to give us data to work with. DO NOT EDIT! ***/ insert into PetOwners(ownerID, ownerName) values(1, "Beth Anderson"), (2, "Alison Jensen"), (3, "Charlie Wilcox"), (4, "Jo Yessen"), (5, "Deb Donnelly"), (6, "Kevin Thoendel"); insert into Breeds(breedID, type, breedName) values(1, 'cat','tuxedo'), (2, 'cat','russian blue'), (3, 'dog','maltese'), (4, 'dog','shih tzu'), (5, 'fish','lionfish'), (6, 'bird', 'parrot'); insert into Pets(petID, breedID, petName) values(1, 1, 'Pengin'), (2, 2, 'Kupo'), (3, 3, 'Greta'), (4, 4, 'Luma'), (5, 4, 'Carson'), (6, 5, 'Yoshi'), (7, 5, 'Gizmo'); insert into Owns(ownerID, petID) values(1,3), (2, 6), (3, 2), (4, 4), (5, 5), (6, 1), (1, 7); /* Test Code: Execute in query pane */ SELECT DISTINCT BreedName FROM Breeds WHERE EXISTS ( SELECT Pets.petID FROM Pets, Owns WHERE Pets.breedID = Breeds.breedID AND Pets.petID = Owns.petID AND Owns.ownerID = 1);