SQL2

SELECT Start_Date, MIN(End_Date)
FROM 
    (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
    (SELECT End_date FROM PROJECTS WHERE End_date NOT IN (SELECT Start_Date FROM Projects)) b
where Start_Date < End_date
GROUP BY Start_Date
ORDER BY (Start_Date - MIN(End_date)) DESC, Start_Date
;

select  con.contest_id,
        con.hacker_id, 
        con.name, 
        sum(total_submissions), 
        sum(total_accepted_submissions), 
        sum(total_views), sum(total_unique_views)
from contests con 
join colleges col on con.contest_id = col.contest_id 
join challenges cha on  col.college_id = cha.college_id 
left join
(select challenge_id, sum(total_views) as total_views, sum(total_unique_views) as total_unique_views
   from view_stats 
 group by challenge_id) vs on cha.challenge_id = vs.challenge_id 
left join
(select challenge_id, sum(total_submissions) as total_submissions, sum(total_accepted_submissions) as total_accepted_submissions 
   from submission_stats 
 group by challenge_id) ss on cha.challenge_id = ss.challenge_id
group by con.contest_id, con.hacker_id, con.name
order by con.contest_id
;

select s.name
     -- , s.id
     -- , mysal.salary
     -- , f.friend_id
     -- , frdsal.salary
from Students s
join Packages mysal on s.id = mysal.id
join Friends f on s.id = f.id
join Packages frdsal on f.friend_id = frdsal.id
where mysal.salary < frdsal.salary
order by frdsal.salary
;

select outf.x
     , outf.y
     , inf.x
     , inf.y
join까지
>   11 11 11 11
    11 15 15 11
    12 12 12 12
    13 13 13 13
    13 13 13 13
    13 13 13 13
    13 13 13 13
    14 14 14 14
    15 11 11 15
    15 15 15 15


select outf.x
     , outf.y
     , count(inf.x)
group by까지  
>   11 11 1
    11 15 1
    12 12 1
    13 13 4
    14 14 1
    15 11 1
    15 15 1

select outf.x
     , outf.y
     , count(inf.x)
from Functions outf
join Functions inf on ( outf.x = inf.y and outf.y = inf.x) 
group by outf.x, outf.y
having outf.x < outf.y 
    or count(outf.x) > 1
order by 1
;

> 11 15 
  13 13 

소수 아닌 애들 거르는 작업
with num as 
(select level as no
        from dual
        connect by level <= 10)
select n1.no, n2.no, mod(n1.no, n2.no)
from num n1
   , num n2
where n1.no > n2.no
  and ( n2.no <> 1 and mod(n1.no, n2.no) = 0 )
-- group by n1.no
order by 1
 ;

> 4 2 0
  6 2 0
  6 3 0
  8 2 0
  8 4 0
  9 3 0
  10 2 0
  10 5 0
  
  
pivot 전
with num as 
(select level as no
        from dual
        connect by level <= 10)
select num.no
from num
where num.no not in (select n1.no
                from num n1
                   , num n2
                where n1.no > n2.no
                  and ( n2.no <> 1 and mod(n1.no, n2.no) = 0 )
                group by n1.no)
and num.no <> 1
order by 1
;
> 2
  3
  5
  7
  
LISTAGG로 피봇팅
with num as 
(select level as no
        from dual
        connect by level <= 10)
select LISTAGG(num.no,'&') within group(order by num.no)
from num
where num.no not in (select n1.no
                from num n1
                   , num n2
                where n1.no > n2.no
                  and ( n2.no <> 1 and mod(n1.no, n2.no) = 0 )
                group by n1.no)
and num.no <> 1
order by 1
;
> 2&3&5&7