[SQL] JOIN 실습 7 (23일차)

1. 아이디 앞 쪽 3개만 그대로 출력하고 나머지 값은 별으로 출력하시오.

SELECT RPAD(SUBSTR(ID, 1, 3),LENGTH(ID),'*') AS ID, NAME
FROM STUDENT2;

 

 

 

2. 급여 + 보너스의 평균이 가장 높은 학과를 구하시오

▶ 학과별 급여+보너스의 평균

SELECT DEPTNO, ROUND(AVG(PAY+(NVL(BONUS,0))),1) AS PN
FROM PROFESSOR
GROUP BY DEPTNO
ORDER BY PN DESC;

 

▶ 코드

SELECT D.DNAME AS 학과명,P.PN AS 급여_보너스
FROM DEPARTMENT D
INNER JOIN (
    SELECT DEPTNO, ROUND(AVG(PAY+(NVL(BONUS,0))),1) AS PN
    FROM PROFESSOR
    GROUP BY DEPTNO
    ORDER BY PN DESC
) P ON D.DEPTNO = P.DEPTNO
WHERE ROWNUM=1;

 

 

3. 컴퓨터정보학부 교수들과 인문사회학부 교수들의 급여 차이

▶ 학부의 학과 구하기

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

▶ 컴퓨터정보학부/인문사회학부 평균

SELECT D2.DEPTNO, ROUND(AVG(P.PAY),1) AS PAY
FROM PROFESSOR P
INNER JOIN DEPARTMENT D ON D.DEPTNO = P.DEPTNO
INNER JOIN DEPARTMENT D2 ON D2.DEPTNO = D.PART
    AND D2.DNAME IN ('컴퓨터정보학부', '인문사회학부')
GROUP BY D2.DEPTNO;

 

▶ 코드

SELECT MAX(PAY)-MIN(PAY) AS 급여차이
FROM (
    SELECT D2.DEPTNO, ROUND(AVG(P.PAY),1) AS PAY
    FROM PROFESSOR P
    INNER JOIN DEPARTMENT D ON D.DEPTNO = P.DEPTNO
    INNER JOIN DEPARTMENT D2 ON D2.DEPTNO = D.PART
        AND D2.DNAME IN ('컴퓨터정보학부', '인문사회학부')
    GROUP BY D2.DEPTNO
);