CS计算机代考程序代写 SQL database DTA (M) Database Theory & Applications

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