README

테이블

1. 각 학년별 학생들의 평균 키를 구하시오.

SELECT GRADE AS 학년, AVG(HEIGHT) AS 평균키
FROM STUDENT2
GROUP BY GRADE;

2. 96학번과 97학번 중 가장 키가 큰 학생의 학번, 이름, 아이디, 키를 출력하시오.

▶ 학번 별 가장 키가 큰 값을 구한다.

SELECT SUBSTR(STUDNO,1,2) AS STNO, MAX(HEIGHT) AS HEIGHT
FROM STUDENT2
WHERE SUBSTR(STUDNO,1,2) IN (96, 97)
GROUP BY SUBSTR(STUDNO,1,2);

▶ 코드

SELECT S.STUDNO AS 학번, S.NAME AS 이름, S.ID AS 아이디, S.HEIGHT AS 키
FROM STUDENT2 S
INNER JOIN (
    SELECT SUBSTR(STUDNO,1,2) AS STNO, MAX(HEIGHT) AS HEIGHT
    FROM STUDENT2
    WHERE SUBSTR(STUDNO,1,2) IN (96, 97)
    GROUP BY SUBSTR(STUDNO,1,2)
) S2 ON SUBSTR(STUDNO,1,2) = STNO AND S.HEIGHT = S2.HEIGHT
WHERE ROWNUM = 1;

3. 교수의 평균임금이 가장 높은 학과의 학과명, 평균 임금을 출력하시오.

▶ 학과별 교수님의 평균 임금을 구한다.

SELECT DEPTNO, ROUND(AVG(PAY), 1) AS PAY
FROM PROFESSOR
GROUP BY DEPTNO
ORDER BY PAY DESC;

▶ 코드

SELECT DNAME AS 학과명, P.PAY AS 평균임금
FROM DEPARTMENT D
INNER JOIN (
    SELECT DEPTNO, ROUND(AVG(PAY), 1) AS PAY
    FROM PROFESSOR
    GROUP BY DEPTNO
    ORDER BY PAY DESC
) P ON D.DEPTNO = P.DEPTNO
WHERE ROWNUM=1;

4. 95년~05년 사이에 부임한 교수의 수를 구하시오.

SELECT COUNT(*) AS 부임교수의_수
FROM PROFESSOR
WHERE HIREDATE BETWEEN '95/01/01' AND '05/12/31';

5. '인문대학'에 소속된 교수들의 목록을 구하시오.

▶ 인문대학의 DEPTNO 값을 구한다. 인문대학에 소속된 학과 : 301 문헌정보학과

SELECT D1.DEPTNO
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
WHERE D3.DNAME = '인문대학';

▶ 코드

SELECT *
FROM PROFESSOR P
INNER JOIN (
    SELECT D1.DEPTNO AS DEPINO
    FROM DEPARTMENT D1
    INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
    INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
    WHERE D3.DNAME = '인문대학'
) D ON P.DEPTNO = D.DEPINO;

▶ 출력 결과

6. 각 학년별 키가 가장 큰 학생의 학번, 이름, 학년, 키를 구하시오.

▶ 학년별 키가 가장 큰 학생의 키를 구한다.

SELECT GRADE, MAX(HEIGHT)
FROM STUDENT2
GROUP BY GRADE;

▶ 코드

SELECT S1.STUDNO AS 학번, S1.NAME AS 이름, S1.GRADE AS 학년, S1.HEIGHT AS 키
FROM STUDENT2 S1
INNER JOIN (
    SELECT GRADE, MAX(HEIGHT) HEIGHT
    FROM STUDENT2
    GROUP BY GRADE
) S2 ON S1.GRADE = S2.GRADE
    AND S1.HEIGHT = S2.HEIGHT
;

▶ 출력 결과

7. 각 학과별 몸무게가 가장 적은 학생의 학번, 이름, 학년, 키를 구하시오.

▶ 각 학과별 가장 적은 몸무게

SELECT DEPTNO1, MIN(WEIGHT)
FROM STUDENT2
GROUP BY DEPTNO1;

▶ 코드

SELECT S.STUDNO, S.NAME, S.GRADE, S2.WEIGHT
FROM STUDENT2 S
INNER JOIN(
    SELECT DEPTNO1, MIN(WEIGHT) AS WEIGHT
    FROM STUDENT2
    GROUP BY DEPTNO1
) S2 ON S.DEPTNO1 = S2.DEPTNO1
    AND S.WEIGHT = S2.WEIGHT
;

▶ 출력 결과

8. 78년생 학생들의 성별(남,여) 인원수를 구하시오.

▶ 78년생인 학생들의 성별 구하기

SELECT SUBSTR(JUMIN,1,2) AS JUMIN
    , DECODE(SUBSTR(JUMIN,7,1), 1, '남자', 3, '남자', 2, '여자', 4, '여자') AS GENDER
FROM STUDENT2
WHERE JUMIN LIKE '78%';

SELECT S2.JUMIN, S2.GENDER, COUNT(GENDER) AS 인원수
FROM STUDENT2 S1
INNER JOIN (
    SELECT STUDNO, SUBSTR(JUMIN,1,2) AS JUMIN
        , DECODE(SUBSTR(JUMIN,7,1), 1, '남자', 3, '남자', 2, '여자', 4, '여자') AS GENDER
    FROM STUDENT2
    WHERE JUMIN LIKE '78%'
) S2 ON S1.STUDNO = S2.STUDNO
GROUP BY S2.JUMIN, S2.GENDER;

▶ 출력 결과

▶ 다른 방법으로 출력이 가능하다.

SELECT 
    COUNT(DECODE(SUBSTR(JUMIN, 7, 1), 1, 1)) AS 남
    , COUNT(DECODE(SUBSTR(JUMIN, 7, 1), 2, 1)) AS 여
FROM STUDENT2
WHERE SUBSTR(JUMIN, 1, 2) = '78';

•  COUNT(or SUM), DECODE, SUBSTR을 이용하여 주민번호 7 번째 자리의 값을 받아와 성별을 구분하여 출력하였다.

9. 96학번의 가장 큰 키 값과 97학번 중 가장 작은 키 값의 차이 구하시오.

▶ 96학번의 가장 큰 키 값 구하기

SELECT SUBSTR(STUDNO, 1, 2) AS STNO, MAX(HEIGHT) AS HEIGHT
FROM STUDENT2
WHERE SUBSTR(STUDNO, 1, 2) IN (96)
GROUP BY SUBSTR(STUDNO, 1, 2);

▶ 97학번의 가장 작은 키 값 구하기

SELECT SUBSTR(STUDNO, 1, 2) AS STNO, MIN(HEIGHT) AS HEIGHT
FROM STUDENT2
WHERE SUBSTR(STUDNO, 1, 2) IN (97)
GROUP BY SUBSTR(STUDNO, 1, 2);

▶ 코드

SELECT  MAX(S2.HEIGHT)- MIN(S3.HEIGHT) AS 차이
FROM STUDENT2 S
LEFT JOIN(
    SELECT SUBSTR(STUDNO, 1, 2) AS STNO, MAX(HEIGHT) AS HEIGHT
    FROM STUDENT2
    WHERE SUBSTR(STUDNO, 1, 2) IN (96)
    GROUP BY SUBSTR(STUDNO, 1, 2)
) S2 ON SUBSTR(S.STUDNO, 1, 2) = S2.STNO 
    AND S.HEIGHT = S2.HEIGHT
LEFT JOIN(
    SELECT SUBSTR(STUDNO, 1, 2) AS STNO, MIN(HEIGHT) AS HEIGHT
    FROM STUDENT2
    WHERE SUBSTR(STUDNO, 1, 2) IN (97)
    GROUP BY SUBSTR(STUDNO, 1, 2)
)S3 ON SUBSTR(S.STUDNO, 1, 2) = S3.STNO
    AND S.HEIGHT = S3.HEIGHT;

• 최댓값과 최솟값을 구한 테이블 두 개를 조인했다.
• 최댓값과 최솟값을 빼 결과를 얻었다.

▶ 출력 결과

▶ 좋지 않은 코드의 예제. 출력은 같다.

SELECT
    (   SELECT MAX(HEIGHT)
        FROM STUDENT2
        WHERE STUDNO LIKE '96%' ) -
    (   SELECT MIN(HEIGHT)
        FROM STUDENT2
        WHERE STUDNO LIKE '97%' )
FROM DUAL;

테이블

1. 공과대학에 속한 학생의 수를 구하시오. (전공 기준)

▶ 공과대학에 속한 학부를 구한다.

SELECT D2.DEPTNO, D2.DNAME, D1.DNAME
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D1.DEPTNO = D2.PART
WHERE D1.DNAME = '공과대학';

▶ 학부를 이용해 공과 대학에 속한 학과를 구한다.

SELECT D3.DEPTNO, D3.DNAME
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D1.DEPTNO = D2.PART
INNER JOIN DEPARTMENT D3 ON D2.DEPTNO = D3.PART
WHERE D1.DNAME = '공과대학';

▶ 코드

SELECT COUNT(*) AS 공과대학_학생수
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D1.DEPTNO = D2.PART
INNER JOIN DEPARTMENT D3 ON D2.DEPTNO = D3.PART
INNER JOIN STUDENT2 S ON S.DEPTNO1 = D3.DEPTNO
WHERE D1.DNAME = '공과대학';

▶ <STUDENT2> 테이블을 메인으로 잡은 코드 (출력은 같다)

SELECT COUNT(*) AS 공과대학_학생수
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO --학과
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO --학부
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO --공과대학
WHERE D3.DNAME ='공과대학';

2. 공과대학 학생들의 평균 키 출력

SELECT ROUND(AVG(HEIGHT),1) AS 공과대학_평균키
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
WHERE D3.DNAME ='공과대학';

3. 공과대학, 인문대학 각각의 평균 키 출력

SELECT D3.DEPTNO, D3.DNAME, ROUND(AVG(HEIGHT),1) AS 평균키
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
GROUP BY D3.DEPTNO, D3.DNAME;

4. 컴퓨터정보학부와 메카트로닉스학부의 평균 키 차이 값

▶ 컴퓨터정보학부, 메카트로닉스학부의 평균 키만 출력

SELECT D2.DNAME AS DNAME, AVG(HEIGHT) AS HEIGHT
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
WHERE D2.DNAME IN ('컴퓨터정보학부','메카트로닉스학부')
GROUP BY D2.DNAME;

▶ 코드

SELECT MAX(S.HEIGHT)-MIN(S.HEIGHT) AS 평균키_차이값
FROM (
    SELECT D2.DNAME AS DNAME, AVG(HEIGHT) AS HEIGHT
    FROM STUDENT2 S
    INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
    INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
    WHERE D2.DNAME IN ('컴퓨터정보학부','메카트로닉스학부')
    GROUP BY D2.DNAME
) S;

▶ 출력 결과

테이블

1. <DEPARTMENT> 공과대학 학부와 인문대학 학부의 학과 목록을 출력하시오.

SELECT D1.PART, D2.DNAME, D1.DNAME
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D2.DEPTNO = D1.PART
WHERE D2.LV = 1;

2. <DEPARTMENT> 컴퓨터정보학부의 학과 목록을 출력하시오.

SELECT D1.PART, D2.DNAME, D1.DNAME
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D2.DEPTNO = D1.PART
    AND D2.DNAME IN('컴퓨터정보학부');

3. <STUDENT2> 학생들의 주민번호를 800000-1****** 형태로 구하고 성별을 구분하시오.

SELECT NAME
    , (SUBSTR(JUMIN,1,6) || '-' || RPAD(SUBSTR(JUMIN,7,1), 7, '*')) AS JUMIN
    , (DECODE(SUBSTR(JUMIN,7,1),1,'남자',3,'남자',2,'여자',4,'여자','?')) AS GENDER
FROM STUDENT2;

4. 이름 가운데를 '*'으로 출력하시오.

SELECT SUBSTR(NAME,1,1) || '*' || SUBSTR(NAME,3,1) AS NAME
FROM STUDENT2;

5. 학부를 포함한 학생 목록을 출력하시오.

▶ 학생의 학과를 구한다.

SELECT S.*, D1.DNAME
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO;

▶ 학과를 이용하여 학부를 구한다.

SELECT S.*, D2.DNAME
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO;

▶ 출력 결과

6. 평균 키가 가장 큰 학과의 학과명, 평균 키를 구하시오.

▶ 학과별 평균 키

SELECT D.DNAME, ROUND(AVG(S.HEIGHT),1) AS HEIGHT
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
GROUP BY D.DNAME
ORDER BY HEIGHT DESC;

• 여기서 바로 최댓값을 출력하고 싶었으나 ORDER BY 정렬 전에 WHERE절이 먼저 실행이 되어 최댓값을 출력할 수 없었다.
• 정렬된 테이블을 이용해 최댓값을 출력했다.

▶ 정렬된 테이블을 이용해 가장 큰 평균 값을 가진 학과를 출력

SELECT S.DNAME AS 학과, S.HEIGHT AS 평균키
FROM (
    SELECT D.DNAME, ROUND(AVG(S.HEIGHT),1) AS HEIGHT
    FROM STUDENT2 S
    INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
    GROUP BY D.DNAME
    ORDER BY HEIGHT DESC
) S
WHERE ROWNUM = 1;

▶ 출력 결과

7. 각 학과별 키가 가장 큰 학생의 이름, 학과명, 키를 구하시오.

▶ 학과별 가장 큰 키를 출력

SELECT D.DNAME AS DNAME, MAX(HEIGHT) AS HEIGHT
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
GROUP BY D.DNAME;

▶ 학과가 일치하면서 가장 큰 키 값을 가진 학생을 출력

SELECT S.NAME AS 이름, S2.DNAME AS 학과, S.HEIGHT AS 키
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
INNER JOIN(
    SELECT D.DNAME AS DNAME, MAX(HEIGHT) AS HEIGHT
    FROM STUDENT2 S
    INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
    GROUP BY D.DNAME
) S2 ON S2.DNAME = D.DNAME
    AND S2.HEIGHT = S.HEIGHT
;

▶ 출력 결과

8. 부전공(DEPTNO2)를 가진 학생의 목록과 전공 학부를 출력하시오.

▶ 부전공을 가진 학생 출력

SELECT S.NAME, S.DEPTNO1, D1.DNAME, S.DEPTNO2, D2.DNAME
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON S.DEPTNO2 = D2.DEPTNO;

▶ 코드

SELECT S.*, D3.DNAME
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON S.DEPTNO2 = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D1.PART = D3.DEPTNO;

▶ 출력 결과

9. 부전공의 학부를 구하시오.

SELECT S.*, D2.DNAME
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO2 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO;

10. 학번, 이름, 부전공의 학과, 학부 목록을 출력.  (부전공이 없을 경우는 NULL로 출력)

SELECT S.STUDNO, S.NAME, NVL(D1.DNAME, '없음') AS 부전공, NVL(D2.DNAME, '없다') AS 부전공학부
FROM STUDENT2 S
LEFT JOIN DEPARTMENT D1 ON S.DEPTNO2 = D1.DEPTNO
LEFT JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO;

테이블

1. 각 학과에서 가장 키가 큰 학생의 학번과 이름, 키를 출력

▶ 학과별로 가장 키가 큰 학생의 키

SELECT STU_HEIGHT, MAX(STU_HEIGHT)
FROM STUDENT
WHERE STU_DEPT IS NOT NULL
GROUP BY STU_DEPT;

▶ 코드

SELECT *
FROM  STUDENT S
INNER JOIN (
    SELECT STU_DEPT, MAX(STU_HEIGHT) AS HEIGHT
    FROM STUDENT
    WHERE STU_DEPT IS NOT NULL
    GROUP BY STU_DEPT
) S2 ON S.STU_DEPT = S2.STU_DEPT
    AND S.STU_HEIGHT = S2.HEIGHT
;

• 키 값만 동일하게 매치한다면 다른 학과에서 동일한 키 값을 가진 사람이 출력될 수 있다.
• 그렇기 때문에 과목도 같고 키 값도 일치하는 사람을 찾아야 한다.

▶ WHERE절 을 이용하여 학과와 최대 키 값을 매치할 수도 있다.

SELECT *
FROM  STUDENT S
WHERE (STU_DEPT, STU_HEIGHT) IN (
    SELECT STU_DEPT, MAX(STU_HEIGHT) AS HEIGHT
    FROM STUDENT
    WHERE STU_DEPT IS NOT NULL
    GROUP BY STU_DEPT
);

▶ 출력 결과

2. 각 학과별으로 점수가 가장 높은 학생의 학번, 학과, 이름, 키, 점수 출력

▶ 학과 별 최대 키 값

SELECT S.STU_DEPT, MAX(E.ENR_GRADE) AS GRADE
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
GROUP BY S.STU_DEPT;

▶ 코드

SELECT S.STU_NO, S.STU_DEPT, S.STU_NAME, S.STU_HEIGHT, S2.GRADE
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN (
    SELECT S.STU_DEPT, MAX(E.ENR_GRADE)AS GRADE
    FROM STUDENT S
    INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
    GROUP BY S.STU_DEPT
) S2 ON S.STU_DEPT = S2.STU_DEPT
    AND E.ENR_GRADE = S2.GRADE
;

▶ 출력 결과

3.  부하직원이 가장 많은 사람의 사번, 이름, 부하직원수

▶ 부하직원의 수

SELECT MGR, COUNT(MGR)
FROM EMP
GROUP BY MGR
ORDER BY COUNT(MGR) DESC;

• 부하직원수를 내림차순으로 정렬하여 최댓값을 최상위에 위치시킨다.

▶ 코드

SELECT *
FROM EMP E
INNER JOIN (
    SELECT MGR, COUNT(MGR)
    FROM EMP
    GROUP BY MGR
    ORDER BY COUNT(MGR) DESC
) E2 ON E.MGR = E2.MGR
WHERE ROWNUM=1;

▶ 출력 결과