-----Extra Credit-----
1
SELECT Hometown
FROM Circus
WHERE Name IN
(
SELECT CircusName
FROM Creates C1 INNER JOIN BalloonAnimals BA1 ON C1.AnimalName =
BA1.Name
WHERE BA1.MinNumberOfBalloonsNeeded >= 10
AND BA1.DifficultyLevel IN ('Extreme', 'NearImpossible')
GROUP BY C1.CircusName
HAVING Count(C1.CircusName) >= ALL
(
SELECT Count(C.CircusName)
FROM Creates C INNER JOIN BalloonAnimals BA ON C.AnimalName =
BA.Name
WHERE BA.MinNumberOfBalloonsNeeded >= 10
AND BA.DifficultyLevel IN ('Extreme', 'NearImpossible')
GROUP BY C.CircusName
)
)
ORDER BY Hometown;
2
each record has:
circusname, avgshoesize, 1 difficulty level name, that difficulty's
actual and min totals.
SELECT AvgShoeTbl.CircusName, AvgShoeSize, DifficultyLevel, SumActual,
SumMinNeeded
FROM (
SELECT R.Name AS CircusName, AVG(C.ShoeSize) AS AvgShoeSize,
FROM Circus R INNER JOIN Clowns C ON R.Name = C.PerformsWith
GROUP BY R.Name
) AS AvgShoeTbl
INNER JOIN
(
SELECT CR.CircusName, BA.DifficultyLevel,
SUM( CR.ActualNumberOfBalloonsUsed) as SumActual,
SUM( BA.MinNumberOfBalloonsNeeded) AS SumMinNeeded
FROM Creates CR INNER JOIN BalloonAnimals BA ON CR.AnimalName =
BA.Name
GROUP BY CR.CircusName, BA.DifficultyLevel
) AS BallonUseTbl
ON AvgShoeTbl.CircusName = BalloonUseTbl.CircusName
);
3
SELECT UsedTooMany.*, R.CEO,
FROM Circus R INNER JOIN
(
SELECT C.Name as ClownName,C.PerformsWith, C.RealName, C.ShoeSize,
C.FavoriteBalloonAnimal,
C.FavoriteJoke,
CR.ActualNumberOfBalloonsUsed as NumUsed,
BA.MinNumberOfBalloonsUsed AS MinNeeded
FROM (Clowns C INNER JOIN Creates CR
ON C.Name = CR.ClownName AND C.FavoriteBalloonAnimal =
CR.AnimalName)
INNER JOIN BalloonAnimals BA ON C.FavoriteBalloonAnimal = BA.Name
WHERE CR.ActualNumberOfBalloonsUsed >= (CR.MinNumberOfBalloons *2)
) AS UsedTooMany
ON R.Name = UsedToMany.PerformsWith
ORDER BY NumUsed/MinNeeded DESC;