SQLize Online / PHPize Online  /  SQLtest Online

/****************** PetOwners Database INFO 1620 Written by Lisa Thoendel Last Updated Summer 2022 ******************/ /**** The below code will create a database for us to work with. Copy and paste this code to the DDL pane of SQL Fiddle. --<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>-- Creation Code. DO NOT EDIT! ****/ 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; ALTER TABLE Pets ADD COLUMN Age CHAR(1); DELETE FROM Owns WHERE petID = 4 AND ownerID = 4; CREATE TABLE DogBreeds (breedID CHAR(8) NOT NULL); INSERT INTO DogBreeds (breedID) VALUES ('Pug'); SELECT * FROM DogBreeds; DROP TABLE DogBreeds; SELECT * FROM Pets; SELECT MAX(ownerID) FROM PetOwners; SELECT * FROM Pets ORDER BY petName ASC; ; SELECT * FROM Breeds;