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); ALTER TABLE Pets ADD COLUMN Age INTEGER NOT NULL; CREATE TABLE DogBreeds( breedID CHAR(8) NOT NULL, breedName VARCHAR(35), CONSTRAINT Breeds_PK PRIMARY KEY (breedID) ); INSERT INTO DogBreeds(breedID, breedName) VALUES(1, "Pug"); /* Test Code: Execute in query pane */ select * from PetOwners; select * from Breeds; select * from Pets; select * from Owns; select * from DogBreeds; DELETE FROM Owns WHERE petId = 4; SELECT * FROM Owns; DROP TABLE DogBreeds; SHOW TABLES; SELECT breedID, COUNT(*) AS "# of pets" FROM Pets GROUP BY breedID; SELECT MAX(ownerId) FROM PetOwners; SELECT * FROM Pets ORDER BY petName ASC; SELECT Pets.petName, Breeds.breedName FROM Pets JOIN Breeds on Breeds.breedID = Pets.breedID; SELECT PetOwners.ownerName, COUNT(Owns.petID) AS "petCount" From PetOwners JOIN Owns ON PetOwners.ownerID = Owns.ownerID