Advertisement
Aleksiev

SoftUni Databases Exam - 14

Mar 1st, 2015
341
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.77 KB | None | 0 0
  1. SELECT c.CountryName AS Country,
  2.     CASE
  3.         WHEN MAX(ISNULL(p.Elevation, 0)) = 0 THEN '(no highest peak)'
  4.         ELSE (SELECT PeakName FROM Peaks WHERE Elevation = MAX(ISNULL(p.Elevation, 0)))
  5.     END AS [Highest Peak Name],
  6.     MAX(ISNULL(p.Elevation, 0)) AS [Highest Peak Elevation],
  7.     CASE
  8.         WHEN MAX(ISNULL(p.Elevation, 0)) = 0 THEN '(no mountain)'
  9.         ELSE (SELECT Mountains.MountainRange FROM Mountains LEFT JOIN Peaks ON Peaks.MountainId = Mountains.Id WHERE Peaks.Elevation = MAX(ISNULL(p.Elevation, 0)))
  10.     END AS Mountain
  11. FROM Countries AS c
  12. LEFT JOIN MountainsCountries AS mc ON mc.CountryCode = c.CountryCode
  13. LEFT JOIN Mountains AS m ON m.Id = mc.MountainId
  14. LEFT JOIN Peaks AS p ON p.MountainId = m.Id
  15. GROUP BY c.CountryName
  16. ORDER BY c.CountryName, [Highest Peak Elevation]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement