SHARE
TWEET

SoftUni Databases Exam - 14

Aleksiev Mar 1st, 2015 247 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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]
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top