[SQLP] 조인 기본 문제

1.

POPULATIONS CENSUS

SELECT SUM(CI.POPULATION)
FROM CITY CI, COUNTRY CO
WHERE CI.COUNTRYCODE=CO.CODE AND CO.CONTINENT='Asia'
GROUP BY CONTINENT;

2.

African Cities

--query the names of all cities where the CONTINENT is 'Africa'.
SELECT CI.NAME
FROM CITY CI, COUNTRY CO
WHERE CI.COUNTRYCODE=CO.CODE AND CO.CONTINENT='Africa';

 

3.

정수

: FLOOR 내림 - ROUND 반올림 

--, query the names of all the continents (COUNTRY.Continent)
--their respective average city populations (CITY.Population) rounded down to the nearest integer.

SELECT CO.CONTINENT, FLOOR(AVG(CI.POPULATION))
FROM CITY CI, COUNTRY CO
WHERE CI.COUNTRYCODE = CO.CODE
GROUP BY CO.CONTINENT;

 

값		내림 FLOOR		반올림 ROUND
Oceania	109189.667	Oceania	109189	Oceania	109190
South America	147435.2	South Ameria	147435	South Ameria	147435
Europe	175138.3	Europe	175138	Europe	175138
Africa	274439	Africa	274439	Africa	274439
Asia	693038.051	Asia	693038	Asia	693038

 

 

4.

2개 이상일 경우 -> 큰 값을 가져온다. -> 최댓값을 가져오는 인라인뷰를 만들어서 -> 아우터 조인 [조건 ON절]

SELECT HA.HACKER_ID, HA.NAME, SUM(MT.MAXSCORE)
FROM (SELECT HACKER_ID, MAX(SCORE) MAXSCORE
     FROM SUBMISSIONS SU
     GROUP BY HACKER_ID, CHALLENGE_ID)MT
     LEFT JOIN HACKERS HA
     ON (HA.HACKER_ID=MT.HACKER_ID)
 GROUP BY HA.HACKER_ID, HA.NAME
 HAVING MAX(MT.MAXSCORE) != 0
 ORDER BY SUM(MAXSCORE) DESC, HACKER_ID;

 

5.

SQL Project Planning

연속된 날짜는 동일한 프로젝트

Start_Date = End_Date -> Start_Date가 가장 작고, End_Date가 가장 큰게 한 프로젝트

SELECT MIN(START_DATE), MAX(END_DATE)
FROM (
      SELECT START_DATE
           , END_DATE
           , START_DATE - ROW_NUMBER() OVER(ORDER BY START_DATE) AS PRJ
      FROM PROJECTS P
     )
GROUP BY PRJ
ORDER BY MAX(END_DATE) - MIN(START_DATE), MIN(START_DATE)
;

 

 

6.

통합

Weather Observation Station 18

SELECT ROUND(ABS(MAX(LAT_N)-MIN(LAT_N))+ABS(MAX(LONG_W)-MIN(LONG_W)),4)
FROM STATION;

 

7.

-- query the names of all the continents (COUNTRY.Continent)
--their respective average city populations (CITY.Population) rounded down to the nearest integer.

SELECT CONTINENT, FLOOR(AVG(CI.POPULATION))
FROM CITY CI, COUNTRY CO
WHERE CI.COUNTRYCODE = CO.CODE
GROUP BY CONTINENT;

 

 

8.

--전체 도전 과제를 내림차순으로 정렬합니다.

ORDER BY COUNT(CHALLENGE_ID) DESC

--두 명 이상의 학생이 동일한 수의 문제를 만든 경우 hacker_id별로 결과를 정렬합니다.

COUNT(COUNT(CHALLENGE_ID))>=2 이면
ORDER BY HACKER_ID

--만약 한 명 이상의 학생이 같은 수의 과제를 만들었고, 그 수가 생성된 최대 과제 수보다 적다면, 그 학생들을 결과에서 제외시키세요.

COUNT(COUNT(CHALLENGE_ID))>=2 이고, COUNT(CHALLENGE_ID)<MAX(COUNT(CHALLENGE_ID))인 경우,
COUNT(CHALLENGE_ID)=MAX(COUNT(CHALLENGE_ID))인 COUNT(CHALLENGE_ID)만 추출

 

즉, COUNT(CHALLENGE_ID)가 같고, COUNT(COUNT(CHALLENGE_ID)가 유일하면 그대로 출력,

COUNT(CHALLENGE_ID)가 같고, COUNT(COUNT(CHALLENGE_ID)가 둘 이상이면, MAX(COUNT(CHALLENGE_ID)만 출력

 

 

SELECT HA.HACKER_ID, HA.NAME, COUNT(CHALLENGE_ID) CHCOUNT
FROM HACKERS HA, CHALLENGES CH
WHERE HA.HACKER_ID=CH.HACKER_ID
GROUP BY HA.HACKER_ID, HA.NAME
HAVING (SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
        FROM HACKERS HA2, CHALLENGES CH2
        WHERE HA2.HACKER_ID=CH2.HACKER_ID
        GROUP BY HA2.HACKER_ID, HA2.NAME
        HAVING COUNT(CH.CHALLENGE_ID)=COUNT(CH2.CHALLENGE_ID))=1;

 

SELECT HA.HACKER_ID, HA.NAME, COUNT(CHALLENGE_ID) CHCOUNT
FROM HACKERS HA, CHALLENGES CH
WHERE HA.HACKER_ID=CH.HACKER_ID
GROUP BY HA.HACKER_ID, HA.NAME
HAVING (SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
        FROM HACKERS HA2, CHALLENGES CH2
        WHERE HA2.HACKER_ID=CH2.HACKER_ID
        GROUP BY HA2.HACKER_ID, HA2.NAME
        HAVING COUNT(CH.CHALLENGE_ID)=COUNT(CH2.CHALLENGE_ID))=1
        OR
        (SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
        FROM HACKERS HA2, CHALLENGES CH2
        WHERE HA2.HACKER_ID=CH2.HACKER_ID
        GROUP BY HA2.HACKER_ID, HA2.NAME
        HAVING COUNT(CH.CHALLENGE_ID)=COUNT(CH2.CHALLENGE_ID))>=2
        AND
        (SELECT MAX(COUNT(CH2.CHALLENGE_ID))
        FROM HACKERS HA2, CHALLENGES CH2
        WHERE HA2.HACKER_ID=CH2.HACKER_ID
        GROUP BY HA2.HACKER_ID, HA2.NAME)= COUNT(CH.CHALLENGE_ID)
ORDER BY CHCOUNT DESC, HA.HACKER_ID;

 

 

9.
가장 친한 친구가 그들보다 더 높은 월급을 제안 받은 학생들의 이름을 출력하기 위해 질의를 쓰세요.

이름은 친한 친구에게 제공되는 급여 금액에 따라 정렬되어야 합니다.

단, 두 학생이 같은 연봉 제안을 받지 않았다.

SELECT MYNAME
FROM
    (SELECT ST.NAME MYNAME, ST.ID MYID, PA.SALARY MYSALARY, FRIEND_ID MYFRIEND, PA2.SALARY FRIENDSALARY
     FROM STUDENTS ST
     INNER JOIN PACKAGES PA ON(ST.ID=PA.ID)
     INNER JOIN FRIENDS FR ON(FR.ID=PA.ID)
     INNER JOIN PACKAGES PA2 ON(FR.FRIEND_ID=PA2.ID))FS
WHERE FRIENDSALARY > MYSALARY
ORDER BY FRIENDSALARY;

-> 이름이 같은 칼럼을 여러번 조인할 때에는 테이블의 별칭을 설정해줘서 중복을 피한다.

 

10.

각 콘테스트에 대한 total_accepted_submissions, total_unique_views의 합계를 출력하기 위한 쿼리를 작성

4개의 합이 모두 인 경우 결과에서 경연을 제외

 

SELECT CONTEST, HACKER_ID,NAME,SUM(SUBMISSIONS),SUM(ACCEPTED),SUM(VIEWS),SUM(UNIQUES)
FROM 
(SELECT
 CT.CONTEST_ID CONTEST, CT.HACKER_ID HACKER_ID,
 CT.NAME NAME, CL.COLLEGE_ID COLLEGE,
 VS.TOTAL_VIEWS VIEWS, VS.TOTAL_UNIQUE_VIEWS UNIQUES,CH.CHALLENGE_ID CHALLENGE,
 SS.TOTAL_SUBMISSIONS SUBMISSIONS, SS.TOTAL_ACCEPTED_SUBMISSIONS ACCEPTED
FROM CONTESTS CT
INNER JOIN COLLEGES CL ON(CT.CONTEST_ID=CL.CONTEST_ID)
INNER JOIN CHALLENGES CH ON(CL.COLLEGE_ID=CH.COLLEGE_ID)
LEFT OUTER JOIN VIEW_STATS VS ON(VS.CHALLENGE_ID=CH.CHALLENGE_ID)
LEFT OUTER JOIN SUBMISSION_STATS SS ON(SS.CHALLENGE_ID=CH.CHALLENGE_ID))
WHERE (VIEWS+UNIQUES+SUBMISSIONS+ACCEPTED)!=0
GROUP BY CONTEST,HACKER_ID,NAME
ORDER BY CONTEST;

내부 조인 : 컨테스트 테이블, 컬리지 테이블 챌린지 테이블

외부 조인 : 뷰스탯 테이블, 서미션 테이블

-> 문제점 : 챌린지 아이디에 대해 중복이 발생

-> 해결 : 챌린지 아이디 별로 소그룹화가 필요

 

 

SELECT A.CONTEST_ID, A.HACKER_ID, A.NAME
     , SUM(TOTAL_SUBMISSIONS)
     , SUM(TOTAL_ACCEPTED_SUBMISSIONS)
     , SUM(TOTAL_VIEWS)
     , SUM(TOTAL_UNIQUE_VIEWS)
FROM CONTESTS A,
COLLEGES B,
CHALLENGES C,
(SELECT C.CHALLENGE_ID,
 SUM(TOTAL_SUBMISSIONS) TOTAL_SUBMISSIONS, 
 SUM(TOTAL_ACCEPTED_SUBMISSIONS) TOTAL_ACCEPTED_SUBMISSIONS
 FROM CHALLENGES C
 LEFT OUTER JOIN SUBMISSION_STATS D ON (C.CHALLENGE_ID=D.CHALLENGE_ID)
 GROUP BY C.CHALLENGE_ID)S,
 (SELECT C.CHALLENGE_ID,
  SUM(TOTAL_VIEWS) TOTAL_VIEWS,
  SUM(TOTAL_UNIQUE_VIEWS) TOTAL_UNIQUE_VIEWS
 FROM CHALLENGES C
 LEFT OUTER JOIN VIEW_STATS E ON (C.CHALLENGE_ID=E.CHALLENGE_ID)
 GROUP BY C.CHALLENGE_ID)V
WHERE A.CONTEST_ID=B.CONTEST_ID AND B.COLLEGE_ID=C.COLLEGE_ID
GROUP BY A.CONTEST_ID, A.HACKER_ID, A.NAME
HAVING SUM(S.TOTAL_SUBMISSIONS) > 0 OR SUM(S.TOTAL_ACCEPTED_SUBMISSIONS) > 0
OR SUM(V.TOTAL_VIEWS) > 0 OR SUM(V.TOTAL_UNIQUE_VIEWS) > 0
ORDER BY CONTEST_ID;

 

 

LEFT OUTER JOIN:    A.ID=B.ID(+) [A테이블 기준으로 A테이블에 없는 행들을 B에서 가져온다.] 

RIGHT OUTER JOIN : A.ID(+)=B.ID [B테이블 기준으로 B테이블에 없는 행들을 A에서 가져온다.]

 

SELECT A.CONTEST_ID, A.HACKER_ID, A.NAME
     , SUM(S.TOTAL_SUBMISSIONS)
     , SUM(S.TOTAL_ACCEPTED_SUBMISSIONS)
     , SUM(V.TOTAL_VIEWS)
     , SUM(V.TOTAL_UNIQUE_VIEWS)
  FROM CONTESTS A
     , COLLEGES B
     , CHALLENGES C
     , (SELECT CHALLENGE_ID
             , SUM(TOTAL_SUBMISSIONS) TOTAL_SUBMISSIONS
             , SUM(TOTAL_ACCEPTED_SUBMISSIONS) TOTAL_ACCEPTED_SUBMISSIONS
          FROM SUBMISSION_STATS
         GROUP BY CHALLENGE_ID
       ) S
     , (SELECT CHALLENGE_ID
             , SUM(TOTAL_VIEWS) TOTAL_VIEWS
             , SUM(TOTAL_UNIQUE_VIEWS) TOTAL_UNIQUE_VIEWS
          FROM VIEW_STATS
         GROUP BY CHALLENGE_ID
       ) V
 WHERE A.CONTEST_ID = B.CONTEST_ID
   AND B.COLLEGE_ID = C.COLLEGE_ID
   AND C.CHALLENGE_ID = S.CHALLENGE_ID(+)
   AND C.CHALLENGE_ID = V.CHALLENGE_ID(+)
 GROUP BY A.CONTEST_ID, A.HACKER_ID, A.NAME
 HAVING
       SUM(S.TOTAL_SUBMISSIONS) > 0 OR SUM(S.TOTAL_ACCEPTED_SUBMISSIONS) > 0
    OR SUM(V.TOTAL_VIEWS) > 0 OR SUM(V.TOTAL_UNIQUE_VIEWS) > 0
 ORDER BY CONTEST_ID
;

 

SELECT A.CONTEST_ID, A.HACKER_ID, A.NAME
     , SUM(S.TOTAL_SUBMISSIONS)
     , SUM(S.TOTAL_ACCEPTED_SUBMISSIONS)
     , SUM(V.TOTAL_VIEWS)
     , SUM(V.TOTAL_UNIQUE_VIEWS)
  FROM CONTESTS A
     , COLLEGES B
     , CHALLENGES C
     , (SELECT S.CHALLENGE_ID
             , SUM(TOTAL_SUBMISSIONS) TOTAL_SUBMISSIONS
             , SUM(TOTAL_ACCEPTED_SUBMISSIONS) TOTAL_ACCEPTED_SUBMISSIONS
          FROM SUBMISSION_STATS S LEFT JOIN CHALLENGES C ON (C.CHALLENGE_ID = S.CHALLENGE_ID)
         GROUP BY S.CHALLENGE_ID
       ) S
     , (SELECT V.CHALLENGE_ID
             , SUM(TOTAL_VIEWS) TOTAL_VIEWS
             , SUM(TOTAL_UNIQUE_VIEWS) TOTAL_UNIQUE_VIEWS
          FROM VIEW_STATS V LEFT JOIN CHALLENGES C ON (C.CHALLENGE_ID = V.CHALLENGE_ID)
         GROUP BY V.CHALLENGE_ID
       ) V
 WHERE A.CONTEST_ID = B.CONTEST_ID
   AND B.COLLEGE_ID = C.COLLEGE_ID
 GROUP BY A.CONTEST_ID, A.HACKER_ID, A.NAME
 HAVING
       SUM(S.TOTAL_SUBMISSIONS) > 0 OR SUM(S.TOTAL_ACCEPTED_SUBMISSIONS) > 0
    OR SUM(V.TOTAL_VIEWS) > 0 OR SUM(V.TOTAL_UNIQUE_VIEWS) > 0
 ORDER BY CONTEST_ID
;

 

 

11.

--첫날은 다 출력
--그 다음부터 첫날 제출한 사람들중에 있으면 출력
--여러명일 경우 HACKER_ID가 가장 낮은 사람 출력