We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. Occupations
  5. Discussions

Occupations

  • sehgal_khwahish
     + 0 comments
    WITH doctors As (
    SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) as rn
    FROM OCCUPATIONS
    WHERE Occupation = 'Doctor'),
professors AS (
    SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) as rn
    FROM OCCUPATIONS
    WHERE Occupation = 'Professor'),
singers AS (
    SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) as rn
    FROM OCCUPATIONS
    WHERE Occupation = 'Singer'),
actors AS (
    SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) as rn
    FROM OCCUPATIONS
    WHERE Occupation = 'Actor')

SELECT d.Name, p.Name, s.Name, a.Name
FROM Doctors AS d
FULL OUTER JOIN Professors AS p ON d.rn = p.rn
FULL OUTER JOIN Singers AS s ON p.rn = s.rn
FULL OUTER JOIN Actors AS a ON s.rn = a.rn``