SQL1

select
        name||'('||substr(occupation,1,1)||')'
  from OCCUPATIONS
;  

with wocc as (
    select  case 
                when occupation = 'Doctor' then name 
                else Null
            end as doc
            ,case 
                when occupation = 'Professor' then name 
                else Null
            end as pro
            ,case 
                when occupation = 'Singer' then name 
                else Null
            end as sing
            ,case 
                when occupation = 'Actor' then name 
                else Null
            end as act
     from OCCUPATIONS
 )
 select * 
 from (
     select d.doc, p.pro, s.sing, a.act
     from
             (select row_number() over (order by sub.doc) col, sub.doc from wocc sub) d
            ,(select row_number() over (order by sub.pro) col, sub.pro from wocc sub) p
            ,(select row_number() over (order by sub.sing) col, sub.sing from wocc sub) s
            ,(select row_number() over (order by sub.act) col, sub.act from wocc sub) a
    where d.col = p.col
      and p.col = s.col
      and s.col = a.col
 )
 where rownum < (select cnt
                 from (
                     select occupation, count(*) as cnt
                     from OCCUPATIONS
                     group by occupation
                     order by 2 desc
                     )
                where rownum = 1)+1
;                

select 
       N
     , case
            when CONNECT_BY_ISLEAF = 1 then 'Leaf'
            when CONNECT_BY_ISLEAF = 0 and P is null then 'Root'
            else 'Inner'
      end
from BST T1
start with P is null
connect by prior N = P
order by N
;

select e.company_code, founder
     , count(distinct e.lead_manager_code)
     , count(distinct e.senior_manager_code) 
     , count(distinct e.manager_code)
     , count(distinct e.employee_code )
from (select distinct company_code, founder from Company) C
left outer join (select distinct lead_manager_code, company_code from Lead_Manager) LM
 on C.company_code = LM.company_code
left outer join (select distinct senior_manager_code, lead_manager_code, company_code from Senior_Manager) SM
 on LM.lead_manager_code = SM.lead_manager_code
left outer join (select distinct manager_code, senior_manager_code, lead_manager_code, company_code from Manager) M
 on SM.senior_manager_code = M.senior_manager_code
left outer join (select distinct employee_code, manager_code, senior_manager_code, lead_manager_code, company_code from Employee) E
 on M.manager_code = E.manager_code
group by e.company_code, founder
order by 1
;

with wstation as 
(select  city
       , length(city) as lencity
from station
order by 2, 1
)
, w2station as
(select city, lencity
  from (select row_number() over (partition by lencity order by city) as rnum
             , city
             , lencity
        from wstation
        )
where rnum < 2
)
select *
from w2station
where (lencity = (select min(lencity) from wstation)
       or lencity = (select max(lencity) from wstation)
      ) 
;

select case
           when g.grade > 7 then s.name
           else NULL
       end as name
        , g.grade
        , s.marks
from STUDENTS s
inner join GRADES g 
  on s.marks between g.min_mark and g.max_mark
order by g.grade desc, name
;

with wsub as (
select h.hacker_id, h.name, count(distinct s.challenge_id) as cnt --, s.score
from Submissions s
left outer join Hackers h
  on s.hacker_id = h.hacker_id
left outer join Challenges c
  on s.challenge_id = c.challenge_id
left outer join Difficulty d
  on c.difficulty_level = d.difficulty_level
where s.score = d.score
  and s.score is not null
group by h.hacker_id, h.name
having count(distinct s.challenge_id) > 1
order by cnt desc, h.hacker_id 
)
select hacker_id, name
from wsub;

with wsub as (
select rank() over (partition by w.power, wp.age order by w.coins_needed) as rank
     , w.power
     , wp.age
     , w.coins_needed
     , w.id
from WANDS w
inner join WANDS_PROPERTY wp
             on w.code = wp.code
            and wp.is_evil = 0  
order by  w.power desc, wp.age desc, w.coins_needed
)
select id, age, coins_needed, power
from wsub
where rank = 1
;

select c.hacker_id, h.name, count(c.challenge_id) as cnt
from hackers h
inner join challenges c
  on h.hacker_id = c.hacker_id
group by c.hacker_id, h.name
having count(c.challenge_id) = (select max(tmp1.cnt)
                from (select count(hacker_id) as cnt
                       from challenges
                     group by hacker_id
                     order by hacker_id) tmp1)
       or
         count(c.challenge_id) in ( select cnt1
        from( select hacker_id, count(challenge_id) as cnt1
            from challenges
            group by hacker_id)
        group by cnt1
        having count(cnt1) = 1)
order by count(c.challenge_id) desc, c.hacker_id
;