CREATE TABLE person
(personID int PRIMARY KEY,
personName varchar(20))

CREATE TABLE personPet
(personPetID int PRIMARY KEY,
personID int,
petType varchar(20),
petName varchar(20),
alive varchar(20))

INSERT person VALUES (1, 'Tom')
INSERT person VALUES (2, 'Bob')
INSERT person VALUES (3, 'George')

INSERT personPet VALUES (1, 1, 'Cat', 'Fluffy','Yes')
INSERT personPet VALUES (2, 2, 'Dog', 'Spot','Yes')
INSERT personPet VALUES (3, 3, 'Cat', 'Foo Foo','No')

GO

CREATE VIEW vPersonPet AS
	
	SELECT personPetID,
		personID,
		petName,
		petType,
		alive,
		CASE WHEN petType = 'Dog' THEN 'Cool'
		ELSE 'Un-Cool' END AS petCoolness
	FROM personPet
GO


CREATE VIEW vPersonPetLiving AS
	
	SELECT personPetID,
		personID,
		petName,
		petType,
		alive,
		petCoolness
	FROM vPersonPet
	WHERE alive = 'Yes'
GO


SELECT p.personName,
	pp.petType,
	pp.petName,
	pp.petCoolness
FROM person p
INNER JOIN vPersonPet pp ON p.personID = pp.personID

SELECT p.personName,
	pp.petType,
	pp.petName,
	pp.petCoolness
FROM person p
LEFT OUTER JOIN vPersonPetLiving pp ON p.personID = pp.personID

DROP TABLE person
DROP TABLE personPet
DROP VIEW vPersonPet
DROP VIEW vPersonPetLiving