DTA (M) Database Theory & Applications
Lab 7 Tasks: Solutions
Task 1: Advanced SQL SELECT Statements ‘Dog’ Database
SQL1:
SELECT O.OWNERID, O.NAME,
COUNT(DISTINCT (K.KENNELNAME))
FROM OWNER AS O,
DOG AS D, KENNEL AS K
WHERE
D.OWNERID = O.OWNERID AND
D.KENNELNAME = K.KENNELNAME
GROUP BY O.OWNERID
ORDER BY O.NAME
SQL2:
SELECT * FROM DOG
WHERE
KENNELNAME IN
(‘Novar kennel club’, ‘Canine
kennel club’, ‘Island kennel club’)
SQL3:
SELECT O.NAME, D.NAME
FROM OWNER AS O, DOG AS D
WHERE O.OWNERID = D.OWNERID
AND D.DOGID NOT IN (
SELECT A.DOGID
FROM ATTENDANCE AS A)
ORDER BY O.NAME
SQL4:
SELECT D1.NAME, O1.NAME
FROM DOG AS D1, OWNER AS O1
WHERE EXISTS (
SELECT *
FROM ATTENDANCE AS A2
WHERE D1.DOGID = A2.DOGID
AND A2.PLACE = 1)
AND O1.OWNERID = D1.OWNERID
SQL 5:
SELECT DISTINCT(A1.DOGID), D1.NAME
FROM ATTENDANCE AS A1, DOG AS D1
WHERE A1.PLACE > ANY(
SELECT A2.PLACE
FROM ATTENDANCE AS A2)
AND D1.DOGID = A1.DOGID
SQL 6:
SELECT DISTINCT(A1.DOGID), D1.NAME
FROM ATTENDANCE AS A1, DOG AS D1
WHERE A1.PLACE <= ALL( SELECT A2.PLACE FROM ATTENDANCE AS A2) AND D1.DOGID = A1.DOGID SQL 7: SELECT Dog.name AS NAME, (4-Attendance.place)*100 AS REWARD FROM Dog, Attendance WHERE Attendance.place <= 3 AND Dog.dogid = Attendance.dogid We just declare at the select statement the function with the attribute we want to calculate. In this case it is (4-attendance.place)*100. Then, we simply provide an alias, like ‘REWARD’ to get a representation of the outcome of the function. DTA (M) Database Theory & Applications SQL 8: SELECT COUNT(DISTINCT(dogid))FROM Attendance We retrieve all the different dogids from the Attendance table, since a dog might have attended to a show more than once, and then we count them with the COUNT operator. SQL 9: SELECT MIN(place) AS MIN_PLACE FROM Attendance, Dog WHERE Dog.dogid = Attendance.dogid AND Dog.name LIKE '%e%' We collect all dogs whose name contains an ‘e’ from the attendance table and then take the minimum of their corresponding place. This minimum is just over the places of those dogs that satisfy the above criterion. SQL 10: SELECT Show.showname, COUNT(Dog.dogid) FROM Show, Attendance, Dog WHERE Show.showname = Attendance.showname AND Attendance.dogid = Dog.dogid GROUP BY Show.showname ORDER BY Show.showname We group all dogs that have attended the same show together, and then simply count them. In order to know which dog was present at a show, we need to involve the attendance table in the query. SQL 11: SELECT Owner.name, Owner.ownerid, COUNT(*) AS DOGS_OWNED FROM Owner, Dog WHERE Owner.ownerid = Dog.ownerid GROUP BY Owner.ownerid We group all dogs with the same owner and then simply count the dogs per owner. This means that for each owner, there are dogs with different dogids, thus, we adopt the COUNT(*) operator. DTA (M) Database Theory & Applications SQL 12: Case 1: SELECT Kennel.kennelname,COUNT(Dog.dogid) as Quantity FROM Kennel, Dog WHERE Dog.kennelname = Kennel.kennelname GROUP BY Kennel.kennelname ORDER BY Quantity DESC LIMIT 1 We group all the dogs with the same kennel together. The grouping is based on the kennelname, which is a primary key. Then, we count all the corresponding dogs for each kennel and sort the kennels according to the number of dogs in decreasing order (from high to low). We just keep/show only the first kennel of the result, which has the most dogs. This means that there exists ONLY one kennel having the most dogs! If there are more than Case 2: We first find the maximum number of dogs in the kennels. Then, check if a kennel has as many dogs as the minimum number. SELECT Kennel.kennelname AS KENNEL_NAME, COUNT(Dog.dogid) AS MOST_DOGS FROM Kennel, Dog WHERE Dog.kennelname = Kennel.kennelname GROUP BY Kennel.kennelname HAVING COUNT(Dog.dogid) = ( SELECT MAX(DOGS_PER_KENNEL.Quantity) FROM ( SELECT COUNT(Dog.dogid) as Quantity FROM Kennel, Dog WHERE Dog.kennelname = Kennel.kennelname GROUP BY Kennel.kennelname) AS DOGS_PER_KENNEL ) In the inner subquery, we construct a relation called DOGS_PER_KENNEL with the attribute Quantity, where we store the number of dogs per kennel. Then, in an outer sub-query, we get the maximum value of the attribute Quantity from the relation DOGS_PER_KENNEL. In the most outer query, we calculate the number of dogs per kennel and then check if this number is equal to the maximum value of the Quantity of DOGS_PER_KENNEL. Similar example solution and explanation is provided in the lecture