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;
Telegram: DB developers channel