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.
with tabel as (
select name, occupation, row_number() over (partition by occupation order by name asc) as angka
from occupations
),
tabeldokter as (
select name as doctor_name, angka
from tabel
where occupation = 'Doctor'
),
tabelprofessor as (
select name as professor_name, angka
from tabel
where occupation = 'Professor'
),
tabelsinger as (
select name as singer_name, angka
from tabel
where occupation = 'Singer'
),
tabelactor as (
select name as actor_name, angka
from tabel
where occupation = 'Actor'
)
-- Emulating FULL OUTER JOIN using LEFT JOIN and RIGHT JOIN with UNION
select
tabeldokter.doctor_name,
tabelprofessor.professor_name,
tabelsinger.singer_name,
tabelactor.actor_name
from tabeldokter
left join tabelprofessor on tabeldokter.angka = tabelprofessor.angka
left join tabelsinger on tabeldokter.angka = tabelsinger.angka
left join tabelactor on tabeldokter.angka = tabelactor.angka
union
select
tabeldokter.doctor_name,
tabelprofessor.professor_name,
tabelsinger.singer_name,
tabelactor.actor_name
from tabelprofessor
left join tabeldokter on tabeldokter.angka = tabelprofessor.angka
left join tabelsinger on tabelprofessor.angka = tabelsinger.angka
left join tabelactor on tabelprofessor.angka = tabelactor.angka
union
select
tabeldokter.doctor_name,
tabelprofessor.professor_name,
tabelsinger.singer_name,
tabelactor.actor_name
from tabelsinger
left join tabeldokter on tabelsinger.angka = tabeldokter.angka
left join tabelprofessor on tabelsinger.angka = tabelprofessor.angka
left join tabelactor on tabelsinger.angka = tabelactor.angka
union
select
tabeldokter.doctor_name,
tabelprofessor.professor_name,
tabelsinger.singer_name,
tabelactor.actor_name
from tabelactor
left join tabeldokter on tabelactor.angka = tabeldokter.angka
left join tabelprofessor on tabelactor.angka = tabelprofessor.angka
left join tabelsinger on tabelactor.angka = tabelsinger.angka;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Occupations
You are viewing a single comment's thread. Return to all comments →
with tabel as ( select name, occupation, row_number() over (partition by occupation order by name asc) as angka from occupations ), tabeldokter as ( select name as doctor_name, angka from tabel where occupation = 'Doctor' ), tabelprofessor as ( select name as professor_name, angka from tabel where occupation = 'Professor' ), tabelsinger as ( select name as singer_name, angka from tabel where occupation = 'Singer' ), tabelactor as ( select name as actor_name, angka from tabel where occupation = 'Actor' ) -- Emulating FULL OUTER JOIN using LEFT JOIN and RIGHT JOIN with UNION select tabeldokter.doctor_name, tabelprofessor.professor_name, tabelsinger.singer_name, tabelactor.actor_name from tabeldokter left join tabelprofessor on tabeldokter.angka = tabelprofessor.angka left join tabelsinger on tabeldokter.angka = tabelsinger.angka left join tabelactor on tabeldokter.angka = tabelactor.angka
union
select tabeldokter.doctor_name, tabelprofessor.professor_name, tabelsinger.singer_name, tabelactor.actor_name from tabelprofessor left join tabeldokter on tabeldokter.angka = tabelprofessor.angka left join tabelsinger on tabelprofessor.angka = tabelsinger.angka left join tabelactor on tabelprofessor.angka = tabelactor.angka
union
select tabeldokter.doctor_name, tabelprofessor.professor_name, tabelsinger.singer_name, tabelactor.actor_name from tabelsinger left join tabeldokter on tabelsinger.angka = tabeldokter.angka left join tabelprofessor on tabelsinger.angka = tabelprofessor.angka left join tabelactor on tabelsinger.angka = tabelactor.angka
union
select tabeldokter.doctor_name, tabelprofessor.professor_name, tabelsinger.singer_name, tabelactor.actor_name from tabelactor left join tabeldokter on tabelactor.angka = tabeldokter.angka left join tabelprofessor on tabelactor.angka = tabelprofessor.angka left join tabelsinger on tabelactor.angka = tabelsinger.angka;