오라클 중심의 SQL 배움터 9장 연습문제 풀이

1. 학생중에서 생년월일이 가장 빠른 학생의 학번, 이름, 생년월일을 출력

 

2. 학년별로 평균 체중이 가장 적은 학년과 그 학년의 평균 몸무게를 출력

 

3. 학년별 최대 몸무게를 가지는 학생의 이름, 몸무게를 PAIRWISE와 UNPAIRWISE 비교 방법에 의해 각각 출력

 

PAIRWISE 출력

UNPAIRWISE 출력

 

4. 교수 테이블에서 평균 연봉보다 많이 받는 교수들의 교수 번호, 이름, 연봉을 출력

( 연봉을 높은순으로 출력, 연봉은 (sal+comm)*12 )

5. 오류를 찾아라

SELECT name, weight, deptno
FROM student
WHERE weight = (SELECT MIN(weight)
                FROM student
                GROUP BY deptno)
ORDER BY deptno;

 

6. 출력 결과가 다른 이유

SELECT profno, name, sal
FROM professor
WHERE sal > ANY (SELECT sal
                 FROM professor
                 WHERE deptno=102);
                 
SELECT profno, name, sal
FROM professor
WHERE sal > ALL (SELECT sal
                 FROM professor
                 WHERE deptno=102);

 

7. 학과별 평균 키보다 큰 학생의 이름, 학번, 키 출력

 

 

(1) 

SELECT studno, name, birthdate
FROM student
WHERE birthdate = (SELECT MIN(birthdate)
                   FROM student);

 

(2)

SELECT grade, AVG(weight)
FROM student
GROUP BY grade
HAVING AVG(weight) = (SELECT MIN(AVG(weight))
                     FROM student
                     GROUP BY grade);

 

(2-1) 아래의 쿼리는 틀린 쿼리이다. 자꾸 왜 안될까 싶었는데...

SELECT grade, AVG(weight)
FROM student
WHERE AVG(weight) = (SELECT MIN(AVG(weight))
                     FROM student
                     GROUP BY grade)
GROUP BY grade;

WHERE 절에 그룹함수를 사용하면 안된다.

 

(3)

-- PIARWISE
SELECT name, weight
FROM student
WHERE (grade, weight) IN (SELECT grade, MAX(weight)
                        FROM student
                        GROUP BY grade);
-- UNPAIRWISE             
SELECT name, weight
FROM student
WHERE grade IN (SELECT grade
               FROM student
               GROUP BY grade)
AND weight IN (SELECT MAX(weight)
               FROM student
               GROUP BY grade);

 

(4)

SELECT profno, name, (sal+NVL(comm,0))*12 "연봉"
FROM professor
WHERE (sal+NVL(comm,0))*12 > (SELECT AVG((sal+NVL(comm,0))*12)
                              FROM professor)
ORDER BY (sal+NVL(comm,0))*12 DESC;

 

(5)

이 쿼리의 서브쿼리는 deptno로 그룹화하여 각 그룹마다 최소 weight를 반환한다.

그래서 각 deptno 그룹 별로 최소 weight의 결과가 한행만 나오는게 아니라 '=' 비교가 불가능하다.

IN으로 WHERE절을 바꿔야한다.

SELECT name, weight, deptno
FROM student
WHERE weight IN (SELECT MIN(weight)
                FROM student
                GROUP BY deptno)
ORDER BY deptno;

 

(6)

서브 쿼리는 102 학과 교수의 sal을 출력한다.

결과가 240과 450이 나온다.

ANY는 어떤 하나라도 만족하면 참이기에 240 또는 450 보다 크면 참이다.

즉, 최소값(240)보다 크면 참이 되는 연산자이다. (MIN과 같은 원리)

 

ALL은 모두 만족해야하기에 240보다도 커야하고 450보다도 커야한다.

즉, 최대값(450)보다 크면 참이된다. (MAX와 같은 원리)

그래서 결과가 다르게 나온것이다.

 

(7)

SELECT name, studno, height
FROM student s1
WHERE height > (SELECT AVG(height)
                FROM student s2
                WHERE s1.deptno=s2.deptno);