Problem 1

Consider the following insurance database, where the primary keys are bolded.

• Person(sin, name, address)
• Car(license, year, model)
• Owns(sin, license)
• Accident(acc#, license, sin, date, city, damage)

Construct the following SQL queries for this relational database.

(a). Find the Total Number of People whose Cars Were Involved in Accidents in 2002

SELECT COUNT(DISTINCT sin)
FROM Accident
WHERE date LIKE "2002%";

(b). Find the Number of Accidents Each Person Has Had while Driving a Car Belonging to Them

SELECT p.sin, p.name, COUNT(DISTINCT a.acc#)
FROM PERSON AS p
LEFT JOIN Accident as a
ON a.sin = p.sin
LEFT JOIN Owns as o
ON o.license = a.license AND a.sin = o.sin
GROUP BY p.sin, p.name;

(c). Add a New Customer to the Database

INSERT INTO Person VALUES (123123123, 'Name', 'Addr');

(d). Delete the Car 'Mazda' Belonging to ‘Bebe Yang'

DELETE FROM Car
WHERE model = 'Mazda'
AND license IN (
	SELECT o.license FROM Owns AS o
	JOIN Person as p ON o.sin = p.sin
	WHERE p.name = 'Bebe Yang'
);

(e). Add a New Accident that Happened in Vancouver on 2002/12/31 and Involved the Toyota Belonging to and Driven by "Jones". The Accident Id is 2222 and the Damage is Unknown

INSERT INTO Accident VALUES (
	2222,
	(
		SELECT o.license FROM Person AS p
		JOIN Owns as o
		ON o.sin = p.sin
		JOIN CAR as c
		ON c.license = o.license
		WHERE p.name = "Jones" AND c.model = "Toyota"
	),
	(
		SELECT o.sid FROM Person AS p
		JOIN Owns as o
		ON o.sin = p.sin
		JOIN CAR as c
		ON c.license = o.license
		WHERE p.name = "Jones" AND c.model = "Toyota"
	),
	"2002/12/31",
	"Vancouver",
	"Unknown"
);

Problem 2

Consider the following collection of relation schemas:

• Professor(profname, deptname)
• Department(deptname, building)
• Committee(commname, profname)

Write an SQL query to answer each of the following questions.

(a). Find All the Professors Who Are in Any One of the Committees that Professor Piper is in

SELECT p.profname FROM Professor AS p
JOIN Committee AS c1 ON p.profname = c1.profname
WHERE c1.commname IN (
	SELECT c2.commname FROM Committee as c2
	WHERE c2.profname = 'Piper'
);

(b). Find All the Professors Who Are in at Least All Those Committees that Professor Piper is in

SELECT p.profname FROM Professor as p
WHERE NOT EXISTS (
	SELECT commname FROM Committee c1 WHERE c1.profname = 'Piper' AND
	NOT EXISTS (
		SELECT * FROM Committee c2 WHERE c2.commname = c1.commname AND c2.profname = p.profname
	)
);

(c). Find All the Professors Who Have no Offices in Any of Those Buildings that Professor Piper Has Offices in

SELECT p.profname FROM Professor as p
WHERE p.deptname NOT IN (
	SELECT d1.deptname FROM Department as d1
	WHERE EXISTS (
		SELECT * from Department as d2
		JOIN Professor as p1
		ON p1.deptname = d2.deptname
		WHERE p1.profname = 'Piper' AND d2.building = d1.building
	)
);

Problem 3

Consider the following relational schemas.

• Sailors(sid, sname, rating, age)
• Boats(bid, bname, color)
• Reserves(sid, bid, date)

Write an SQL query to answer each of the following questions.

(a). Find the Colors of Boats Reserved by Albert

SELECT DISTINCT b.color FROM Boats AS b
JOIN Reserves as r ON r.bid = b.bid
JOIN Sailors as s ON s.sid = r.sid
WHERE s.sname = 'Albert'

(b). Find All Sailor id’s of Sailors Who Have a Rating of at Least 8 or Reserved Boat 103

SELECT s.sid FROM Sailors AS s
WHERE (
	s.rating >= 8
	OR EXISTS (
		SELECT * FROM Reserves AS r
		WHERE s.sid = r.sid AND r.bid = 103
	)
);

(c). Find the Names of Sailors Who Have not Reserved a Boat whose name Contains the String "storm". Order the Names in Ascending order

SELECT s.sname FROM Sailors AS s
WHERE NOT EXISTS (
	SELECT * FROM Boats AS b
	JOIN Reserves AS r
	ON b.bid = r.bid
	WHERE r.sid = s.sid AND (
		b.bname ILIKE '%storm%'
	)
)
ORDER BY s.sname ASC;

(d). Find the Sailor id’s of Sailors with Age over 20 Who Have not Reserved a Boat whose name Includes the String “thunder”

SELECT s.sid FROM Sailors AS s
WHERE s.age > 20
AND NOT EXISTS (
	SELECT * FROM Boats AS b
	JOIN Reserves AS r
	ON b.bid = r.bid
	WHERE r.sid = s.sid AND (
		b.bname ILIKE '%thunder%'
	)
);

(e). Find the Names of Sailors Who Have Reserved at Least Two Boats

SELECT s.sname FROM Sailors AS s
JOIN Reserves as r ON s.sid = r.sid
GROUP BY s.sid, s.sname
HAVING COUNT(DISTINCT r.bid) >= 2;

(f). Find the Names of Sailors Who Have Reserved All Boats

SELECT s.sname FROM Sailors AS s
WHERE NOT EXISTS (
	SELECT * FROM Boats AS b
	WHERE NOT EXISTS (
		SELECT * FROM Reserves AS r
		WHERE r.bid = b.bid AND r.sid = s.sid
	)
);

(g). Find the Names of Sailors Who Have Reserved All Boats whose name Starts with "typhoon"

SELECT s.sname FROM Sailors AS s
WHERE NOT EXISTS (
	SELECT * FROM Boats AS b
	WHERE b.bname ILIKE 'typhoon%' 
	AND NOT EXISTS (
		SELECT * FROM Reserves as r
		WHERE b.bid = r.bid AND r.sid = s.sid
	)
);

(h). Find the Sailor id’s of Sailors whose Rating is Better than Some Sailor Called Bob

SELECT s1.sid FROM Sailors AS s1
WHERE s1.rating > ANY(
	SELECT s2.rating FROM Sailors as s2
	WHERE s2.sname = 'Bob'
);

(i). Find the Sailor id’s of Sailors whose Rating is Better than Every Sailor Called Bob

SELECT s1.sid FROM Sailors AS s1
WHERE s1.rating > ALL(
	SELECT s2.rating FROM Sailors AS s2
	WHERE s2.sname = 'Bob'
);

(j). Find the Sailor id’s of Sailors with the Highest Rating

SELECT s1.sid FROM Sailors AS s1
WHERE s1.rating = SELECT MAX(s2.rating) FROM Sailors AS s2;

(k). Find the Sname and Age of the Oldest Sailor

SELECT s1.name, s1.age FROM Sailors AS s1
WHERE s1.age = (SELECT MAX(s2.age) FROM Sailors AS s2);

(l). Find the Names of Sailors Who Have Reserved Every Boat Reserved by Those with a Lower Rating

SELECT s1.sname FROM Sailors AS s1
WHERE NOT EXISTS (
	SELECT * FROM Sailors AS s2
	JOIN Reserves AS r1
	ON r1.sid = s2.sid
	JOIN Boats AS b1
	ON b1.bid = r1.bid
	WHERE (
		s1.rating > s2.rating
		AND NOT EXISTS (
			SELECT * FROM Reserves AS r2
			WHERE r2.sid = s1.sid AND r2.bid = b1.bid
		)
	)
);

(m). For Each Rating, Find the Average Age of Sailors at that Level of Rating

SELECT rating, AVG(age) FROM Sailors
GROUP BY rating;

(n). For Each Boat Which Was Reserved by at Least 5 Distinct Sailors, Find the Boat Id and the Average Age of Sailors Who Reserved it

SELECT r.bid, AVG(s.age) FROM Reserves AS r
JOIN Sailors s ON r.sid = s.sid
GROUP BY r.bid
HAVING COUNT(DISTINCT r.sid) >= 5;

(o). For Each Boat Which Was Reserved by at Least 5 Sailors with Age >= 40, Find the Boat Id and the Average Age of such Sailors

SELECT r.bid, AVG(s.age) FROM Reserves AS r
JOIN Sailors s ON r.sid = s.sid
WHERE s.age >= 40
GROUP BY r.bid
HAVING COUNT(DISTINCT r.sid) >= 5;

(p). For Each Boat Which Was Reserved by at Least 5 Sailors with Age >= 40, Find the Boat Id and the Average Age of All Sailors Who Reserved the Boat

WITH prev AS (
	SELECT r.bid FROM Reserves AS r
	JOIN Sailors s ON r.sid = s.sid
	WHERE s.age >= 40
	GROUP BY r.bid
	HAVING COUNT(DISTINCT r.sid) >= 5
)
SELECT r.bid, AVG(s.age) FROM Reserves as r
	JOIN Sailors s ON r.sid = s.sid
	JOIN prev ON r.bid = prev.bid
	GROUP BY r.bid;

Problem 4

Consider the following relations

• Course(cid, deptName, cName, year, majorRequired)
  • The primary key of this relation is {cid, deptName} (e.g., 304, CPSC)
  • cid is a course id, e.g., 110, 304;
  • deptName is the name of the department, e.g., CPSC, MATH;
  • cName is the name of the course, e.g., “database management”;
  • year is an integer in the set {1, 2, 3, 4};
  • majorRequired is a Boolean value which is either true or false.
• Prereq(cid, deptName, prereqCid, prereqDeptName)
  • The primary key for this relation is {cid, deptName, prereqCid, prereqDeptName}
  • This is a relation that stores the prerequisite courses for the course (cid, deptName);
  • Each prerequisite course is denoted by (prereqCid, prereqDeptName).
• Offering(cid, deptName, calendarYear, term, enrollment, avgGrade)
  • The primary key for this relation is {cid, deptName, calendarYear, term}
  • This is a relation that stores all the offerings of a course (cid, deptName);
  • calendarYear is the year the course was offered, e.g., 2017, 2018;
  • term is an integer in the set {1, 2, 3}, i.e., there are only 3 terms per year;
  • enrollment is the number of students enrolled in that course during that year and that term;
  • avgGrade is the average grade of the class for that offering; it ranges from 0% to 100%.
• Took(sid, cid, deptName, calendarYear, term, grade)
  • The primary key of this relation is {sid, cid, deptName, calendarYear, term}
  • This is a relation that records the grade of a student taking a particular course during a particular year and term;
  • sid is the student id of the student;
  • (cid, deptName, calendarYear, term) specifies a particular offering of a course;
  • grade is the grade of the student who took that offering of the course; it ranges from 0% to 100%, without any null values. The passing grade of any course is 50%.

Write an SQL query to answer each of the following questions.

(a). Find the Student Id Who Has Taken the Most Courses (i.e., Which Student Id Has Taken the Highest Number of courses)?

SELECT sid FROM Took as t1
GROUP BY t1.sid
HAVING COUNT(DISTINCT t1.cid, t1.deptName) = (
	SELECT MAX(course_count)
    FROM (
        SELECT COUNT(DISTINCT cid, deptName) AS course_count
        FROM Took
        GROUP BY sid
    )
);

(b). For Each Course, Sum up the Number of Students that Have Taken that Specific Course

SELECT cid, deptName, COUNT(DISTINCT sid) as total_students FROM Took
GROUP BY cid, deptName;

(c). Find All Students Who Has Taken All CPSC Classes

SELECT DISTINCT sid FROM Took as t1
WHERE NOT EXISTS (
	SELECT c.cid FROM Course AS c 
	WHERE c.deptName = 'CPSC'
	AND NOT EXISTS (
		SELECT * FROM Took as t2
		WHERE t1.sid = t2.sid
		AND t2.cid = c.cid
		AND t2.deptName = c.deptName
	)
);

(d). Oops! The University Ran out of Money. Delete All the Courses that Are not Required for a Major

DELETE FROM Course
WHERE majorRequired = FALSE

(e). Oops! The University Made a Mistake Tabulating Grades. For Every Course Offered in 2007, Update the Course Average to Be 10% Higher than what is Currently Listed

UPDATE Offering
SET avgGrade = LEAST(avgGrade * 1.10, 100)
WHERE calendarYear = 2007;

(f). Find the Course with the Highest Number of Offerings

CREATE VIEW NumberOfOfferingsPerCourse(cid, deptName, numOfferings) AS 
SELECT cid, deptName, COUNT(*) AS numOfferings
FROM Offering
GROUP BY cid, deptName;

SELECT cid, deptName FROM NumberOfOfferingsPerCourse
WHERE numOfferings = (
	SELECT MAX(numOfferings) FROM NumberOfOfferingsPerCourse
);

(g). Return All the Courses that Have the Word "uwu" in Their Course Names

SELECT cid, deptName FROM Course
WHERE cName ILIKE '%uwu%';

(h). Find the Course that Has the Most Number of Prerequisites

CREATE VIEW PreReqsPerCourse(cid, deptName, numPreReqs) AS
SELECT cid, deptName COUNT(*) AS numPreReqs
FROM Prereq
GROUP BY cid, deptName;

SELECT cid, deptName FROM PreReqsPerCourse
WHERE numPreReqs = (
	SELECT MAX(numPreReqs) FROM PreReqsPerCourse
);

(i). Find All the Courses Taken by Sid = 1010102 where the Final Course Grade Was a B (for the Purposes of the Problem, a B is 72-80)

SELECT cid, deptName FROM Took
WHERE (
	sid = 1010102
	AND grade >= 72
	AND grade <= 80
)

Problem 5

A zoo has the following schema:

• Animal(id, genus, species, enclosureID)
• AnimalType(genus, species)
• Enclosure(id, latitude, longitude)
• Employee(employeeID, firstName, lastName, occupation, startDate, endDate)
• TakesCareOf(employeeID, animalID, date)
• Visitor(visitorID, firstName, lastName, dateOfBirth)
• Pass(passID, visitorID, startDate, endDate, cost)
• Visits(passID, enclosureID)

Where Bold indicates primary keys and italic indicates foreign keys.

and conforms the the ER-diagram

Write the following queries in SQL.

(a). Find All the Visitors that Have More than 3 Passes, and whose Passes Must Cost More than $15

SELECT visitorID FROM Visitor AS v
JOIN Pass AS p ON p.visitorID = v.visitorID
WHERE p.cost > 15
GROUP BY visitorID
HAVING COUNT(*) > 3;

(b). Find All the Species whose name Contains the Phrase "cat"

SELECT species FROM AnimalType 
WHERE LOWER(species) LIKE '%cat%';

(c). Find All the Species whose name Does not Start with the Phrase "cat"

SELECT species FROM AnimalType 
WHERE LOWER(species) NOT LIKE 'cat%';

(d). Find Visitors Who Have at Least One Pass that is More Expensive than All Passes that 'Sam Smith' Has Purchased

SELECT DISTINCT visitorID FROM Pass AS p1
WHERE cost > ALL (
    SELECT p.cost FROM Pass AS p
    JOIN Visitor AS v ON v.visitorID = p.visitorID
    WHERE v.firstName = 'Sam'
    AND v.lastName = 'Smith'
);

(e). Find Visitors Who Have at Least One Pass that is More Expensive than Any Pass that 'Sam Smith' Has Purchased

SELECT DISTINCT visitorID FROM Pass AS p1
WHERE cost > ANY (
    SELECT p.cost FROM Pass AS p
    JOIN Visitor AS v ON v.visitorID = p.visitorID
    WHERE v.firstName = 'Sam'
    AND v.lastName = 'Smith'
);

(f). Find All Unique Employee First Names in Alphabetical order

SELECT DISTINCT firstName FROM Employee
ORDER BY firstName ASC;

(g). Find All Possible Enclosure and Animal Id Pairs (including the Empty enclosures)

SELECT DISTINCT e.id as enclosureID, a.id as animalID 
FROM Enclosure as e
LEFT JOIN Animal as a
ON a.enclosureId = e.id

(h). Find All Possible Employee Last Names and Enclosure Id Pairs if there is a Match on Ids

SELECT DISTINCT em.lastName, ec.id as enclosureID
FROM Employee AS em
JOIN TakesCareOf as t
ON t.employeeID = em.employeeID
JOIN Animal as a
ON a.id = t.animalID
JOIN Enclosure as ec
ON ec.id = a.enclosureID