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 * from PetOwners; select * from Breeds; select * from Pets; select * from Owns; select * from PetOwners p join Owns o on p.ownerID = o.ownerID; select * from Breeds b left outer join Pets p on b.breedID = p.breedID left outer join Owns o on p.petID = o.petID; SELECT * FROM Breeds b WHERE b.breedID NOT IN ( SELECT p.breedID FROM Pets p JOIN Owns o ON p.petID = o.petID ); select * from Breeds b where b.breedID in (select p.breedID from Pets p where p.petID in (select o.petID from Owns o where o.ownerID = 1)); select * from Breeds b where EXISTS (select p.breedID from Pets p where b.breedID = p.breedID and EXISTS (select o.petID from Owns o where o.petID = p.petID and o.ownerID = 1)); create view FurBabies as select po.ownerID, po.ownerName, p.petID, p.petName, b.type, b.breedName from PetOwners po join Owns o on po.ownerID = o.ownerID join Pets p on o.petID = p.petID join Breeds b on p.breedID = b.breedID; select * from FurBabies f where f.type in ('fish', 'bird'); select po.ownerID, po.ownerName, p.petID, p.petName, b.type, b.breedName from PetOwners po join Owns o on po.ownerID = o.ownerID join Pets p on o.petID = p.petID join Breeds b on p.breedID = b.breedID;