Exam 01.03.15 Problem 14.** Highest Peak Name and Elevation

USE GEOGRAPHY
GO

SELECT c.CountryName as [Country],
    IIF(p.PeakName IS NULL, '(no highest peak)', p.PeakName) AS [Highest Peak Name],
    IIF(max(p.Elevation) IS NULL, 0, max(p.Elevation)) AS [Highest Peak Elevation],
    IIF(m.MountainRange IS NULL, '(no mountain)', m.MountainRange) AS [Mountain]
FROM Peaks p
JOIN Mountains m
    ON p.MountainId = m.Id
JOIN MountainsCountries mc
    ON m.Id = mc.MountainId
RIGHT JOIN Countries c
    ON mc.CountryCode = c.CountryCode
WHERE p.elevation = (
        SELECT max(pp.Elevation)
        FROM Peaks pp
        JOIN Mountains mm
            ON pp.MountainId = mm.Id
        JOIN MountainsCountries mcc
            ON mm.Id = mcc.MountainId
        JOIN Countries cc
            ON mcc.CountryCode = cc.CountryCode
        WHERE cc.CountryCode = c.CountryCode
        )
    OR (
        SELECT Count(pp.Id)
        FROM Peaks pp
        JOIN Mountains mm
            ON pp.MountainId = mm.Id
        JOIN MountainsCountries mcc
            ON mm.Id = mcc.MountainId
        JOIN Countries cc
            ON mcc.CountryCode = cc.CountryCode
        WHERE cc.CountryCode = c.CountryCode
        ) = 0
GROUP BY c.CountryName,
    p.PeakName,
    m.MountainRange
order by [Country], [Highest Peak Name]