DTA (M) Database Theory & Applications
Lab 7 Tasks: Solutions [ADVANCED]
Task 1: Advanced SQL SELECT Statements
There are proposed solutions on the SQL selection queries.
SQL 1:
SELECT Owner.name, Owner.ownerid, COUNT(Dog.dogid)
FROM Owner, Dog
WHERE Owner.ownerid = Dog.ownerid
GROUP BY Owner.ownerid
HAVING COUNT(Dog.dogid) >= 8
ORDER BY Owner.name
SQL 2:
SELECT Kennel.kennelname, COUNT(Dog.dogid)
FROM Kennel, Dog
WHERE Dog.kennelname = Kennel.kennelname
GROUP BY Kennel.kennelname
HAVING COUNT(Dog.dogid) > 3
ORDER BY COUNT(Dog.dogid) DESC
SQL 3:
SELECT K1.kennelname, COUNT(D1.dogid)
FROM Kennel K1, Dog D1
WHERE K1.kennelname = D1.kennelname
GROUP BY K1.kennelname
HAVING COUNT(D1.dogid) >= ALL
(SELECT COUNT(Dog.dogid)
FROM Kennel, Dog
WHERE Dog.kennelname = Kennel.kennelname
GROUP BY Kennel.kennelname)
DTA (M) Database Theory & Applications
SQL 4:
SELECT a.dogid, COUNT(a.dogid)
FROM Attendance a
WHERE 1 = ALL (
SELECT a1.place
FROM Attendance a1
WHERE a1.dogid = a.dogid)
GROUP BY a.dogid
HAVING COUNT(a.dogid) > 1
SQL 5:
Part 1: We show the average place (rank) for each owner (whose dog has at least attended a show). We use
the ::numeric(10,2) over the AVG operator to get a real number (avoid integer division).
SELECT Owner.name AS OwnerName, AVG(Attendance.place)::numeric(10,2) AS
Average
FROM Owner, Attendance, Dog
WHERE Owner.ownerid = Dog.ownerid
AND Dog.dogid = Attendance.dogid
GROUP BY Owner.ownerid
Part 2: Show the average place (rank) overall
SELECT X.OwnerName, X.Average FROM
(SELECT ‘Overall’ AS OwnerName, AVG(Attendance.place)::numeric(10,2) AS
Average FROM Attendance) AS X
Part 1 UNION Part 2:
SELECT Owner.name AS OwnerName, AVG(Attendance.place)::numeric(10,2) AS
Average
FROM Owner, Attendance, Dog
WHERE Owner.ownerid = Dog.ownerid
AND Dog.dogid = Attendance.dogid
GROUP BY Owner.ownerid
UNION
SELECT X.OwnerName, X.Average FROM
(SELECT ‘Overall’ AS OwnerName, AVG(Attendance.place)::numeric(10,2) AS
Average FROM Attendance) AS X