MS SQL - Subqueries and Joins

               --Exercises: Subqueries and Joins
--1. Employee Address

SELECT TOP(5)
         e.EmployeeID
         ,e.JobTitle
         ,e.AddressID
         ,a.AddressText
    FROM Employees AS e
    JOIN Addresses AS a
      ON e.AddressID = a.AddressID
ORDER BY e.AddressID

--02. Addresses with Towns
SELECT TOP(50)
              e.FirstName
              ,e.LastName
              ,t.[Name] AS Town
              ,a.AddressText
         FROM Employees AS e
    LEFT JOIN Addresses AS a
           ON e.AddressID = a.AddressID
    LEFT JOIN Towns AS t
           ON a.TownID = t.TownID
     ORDER BY e.FirstName, e.LastName

--3. Sales Employee

  SELECT
         e.EmployeeID
         ,e.FirstName
         ,e.LastName
         ,d.[Name] AS DepartmentName
    FROM Employees AS e
    JOIN Departments AS d
      ON e.DepartmentID = d.DepartmentID
   WHERE d.[Name] = 'Sales'
ORDER BY e.EmployeeID

--4. Employee Departments

SELECT TOP(5)
         e.EmployeeID
         ,e.FirstName
         ,e.Salary
         ,d.[Name] AS DepartmentName
    FROM Employees AS e
    JOIN Departments AS d
      ON e.DepartmentID = d.DepartmentID
   WHERE e.Salary > 15000
ORDER BY d.DepartmentID

--5. Employees Without Project

SELECT TOP(3)
          e.EmployeeID
          ,e.FirstName
          --,ep.ProjectID
     FROM Employees AS e
LEFT JOIN EmployeesProjects AS ep
       ON e.EmployeeID = ep.EmployeeID
    WHERE ep.ProjectID IS NULL
 ORDER BY e.EmployeeID

--6. Employees Hired After

  SELECT
         e.FirstName
         ,e.LastName
         ,e.HireDate
         ,d.[Name] AS DeptName
    FROM Employees AS e
    JOIN Departments AS d
      ON e.DepartmentID = d.DepartmentID
     AND e.HireDate > '1.1.1999'
   WHERE d.[Name] IN ('Sales', 'Finance')
ORDER BY e.HireDate

--07. Employees With Project

SELECT TOP(5)
             e.EmployeeID
             ,e.FirstName
             ,p.[Name] AS ProjectName
        FROM Employees AS e
        JOIN EmployeesProjects AS ep
          ON e.EmployeeID = ep.EmployeeID
        JOIN Projects AS p
          ON ep.ProjectID = p.ProjectID
       WHERE p.StartDate > '2002-08-13'
         AND p.EndDate IS NULL
    ORDER BY e.EmployeeID

--08. Employee 24

SELECT
       e.EmployeeID
       ,e.FirstName
       ,CASE
            WHEN DATEPART(YEAR, p.StartDate) >= 2005 THEN NULL
            ELSE p.[Name]
       END AS ProjectName
  FROM Employees AS e
  JOIN EmployeesProjects AS ep
    ON e.EmployeeID = ep.EmployeeID
 JOIN Projects AS p
   ON ep.ProjectID = p.ProjectID
WHERE e.EmployeeID = 24

--09. Employee Manager

SELECT
    e.EmployeeID
    ,e.FirstName
    ,e.ManagerID
    ,m.FirstName AS ManagerName
FROM Employees AS e
JOIN Employees AS m
  ON e.ManagerID = m.EmployeeID
WHERE e.ManagerID IN (3, 7)
ORDER BY e.EmployeeID

--10. Employees Summary
SELECT TOP(50)
    e.EmployeeID
    ,CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName
    ,CONCAT(m.FirstName, ' ', m.LastName) AS ManagerName
    ,d.[Name] AS DepartmentName
FROM Employees AS e
JOIN Employees AS m
  ON e.ManagerID = m.EmployeeID
JOIN Departments AS d
  ON e.DepartmentID = d.DepartmentID
ORDER BY e.EmployeeID

--11. Min Average Salary

SELECT TOP(1)
     AVG(Salary) AS MinAverageSalary
    FROM Employees
GROUP BY DepartmentID
ORDER BY MinAverageSalary

--12. Highest Peaks in Bulgaria
USE Geography

SELECT
     c.CountryCode
     ,m.MountainRange
     ,p.PeakName
     ,p.Elevation
FROM Countries AS c
JOIN MountainsCountries AS mc
  ON c.CountryCode = mc.CountryCode
JOIN Mountains AS m
  ON mc.MountainId = m.Id
JOIN Peaks AS p
  ON m.Id = p.MountainId
WHERE c.CountryCode = 'BG'
  AND p.Elevation > 2835
ORDER BY p.Elevation DESC

--13. Count Mountain Ranges

SELECT
     c.CountryCode
     ,COUNT(mC.MountainId) AS MountainRange
FROM Countries AS c
JOIN MountainsCountries AS mc
  ON c.CountryCode = mc.CountryCode
JOIN Mountains AS m
  ON mc.MountainId = m.Id
WHERE c.CountryCode IN ('BG', 'RU', 'US')
GROUP BY c.CountryCode

--14. Countries With or Without Rivers

SELECT TOP(5)
     c.CountryName
     ,r.RiverName
FROM Countries AS c
LEFT JOIN CountriesRivers AS cr
  ON c.CountryCode = cr.CountryCode
LEFT JOIN Rivers AS r
  ON cr.RiverId = r.Id
WHERE c.ContinentCode = 'AF'
ORDER BY c.CountryName

--15. Continents and Currencies
USE Geography

SELECT
       ContinentCode
       ,CurrencyCode
       ,CurrencyUsage
  FROM (
       SELECT *
              ,DENSE_RANK() OVER (PARTITION BY ContinentCode order by CurrencyUsage DESC) AS CurrUsRank
         FROM (
                SELECT
                       c.ContinentCode
                       ,ctr.CurrencyCode
                       ,COUNT(ctr.CurrencyCode) AS CurrencyUsage
                  FROM Continents AS c
             LEFT JOIN Countries AS ctr ON c.ContinentCode = ctr.ContinentCode
              GROUP BY c.ContinentCode, ctr.CurrencyCode
              ) AS CurrUsageSubquery
        WHERE CurrencyUsage > 1
       ) AS RankingSubquery
WHERE CurrUsRank = 1
ORDER BY ContinentCode

--16. Countries Without any Mountains

   SELECT
          COUNT(*) AS [Count]
     FROM Countries AS c
LEFT JOIN MountainsCountries AS m ON c.CountryCode = m.CountryCode
    WHERE MountainId IS NULL

--17. Highest Peak and Longest River by Country

 SELECT TOP(5)
              c.CountryName
              ,MAX(p.Elevation) AS HighestPeakElevation
              ,MAX(r.[Length]) AS LongestRiverLength
         FROM Countries AS c
    LEFT JOIN MountainsCountries AS mc ON c.CountryCode = mc.CountryCode
    LEFT JOIN Mountains AS m ON mc.MountainId = m.Id
    LEFT JOIN Peaks AS p ON m.Id = p.MountainId
    LEFT JOIN CountriesRivers AS cr ON c.CountryCode = cr.CountryCode
    LEFT JOIN Rivers AS r ON cr.RiverId = r.Id
     GROUP BY c.CountryName
     ORDER BY HighestPeakElevation DESC,
              LongestRiverLength DESC,
              c.CountryName

--18. Highest Peak Name and Elevation by Country

SELECT TOP(5)
       Country
       ,[Highest Peak Name]
       ,[Highest Peak Elevation]
       ,Mountain
  FROM (SELECT
              c.CountryName AS Country
              ,CASE
                    WHEN p.PeakName IS NULL THEN '(no highest peak)'
                    ELSE p.PeakName
                END AS [Highest Peak Name]
              ,CASE
                    WHEN p.Elevation IS NULL THEN '0'
                    ELSE p.Elevation
                END AS [Highest Peak Elevation]
              ,CASE WHEN m.MountainRange IS NULL THEN '(no mountain)'
                    ELSE m.MountainRange
                END AS [Mountain]
              ,DENSE_RANK() OVER (PARTITION BY c.CountryName ORDER BY p.Elevation DESC) AS PeaksRanking
              --,MAX(p.Elevation) AS HighestPeakElevation
              --,MAX(r.[Length]) AS LongestRiverLength
         FROM Countries AS c
    LEFT JOIN MountainsCountries AS mc ON c.CountryCode = mc.CountryCode
    LEFT JOIN Mountains AS m ON mc.MountainId = m.Id
    LEFT JOIN Peaks AS p ON m.Id = p.MountainId
        ) AS PeaksRankingSubquery
 WHERE PeaksRanking = 1
 ORDER BY Country, [Highest Peak Name]