Day056

INNER JOIN, OUTER JOIN

• ANSI INNER JOIN

SELECT s.name, p.name
FROM student s INNER JOIN professor p
ON s.profno = p.profno;

• ANSI OUTER JOIN

SELECT *
FROM professor p LEFT OUTER JOIN student s
ON s.profno = p.profno;

• ANSI FULL OUTER JOIN

SELECT s.name, p.name
FROM student s FULL OUTER JOIN professor p
ON s.profno = p.profno;

• Oracle, LEFT OUTER JOIN

--지도 학생이 결정되지 않은 교수의 명단
SELECT s.name 학생, p.name 지도교수
FROM student s, professor p
WHERE p.profno = s.profno(+);

• Oracle, FULL OUTER JOIN

--지도학생이 결정 안 된 교수명단, 지도교수가 결정 안 된 학생명단
SELECT s.name, p.name
FROM student s, professor p
WHERE s.profno = p.profno(+)
UNION
SELECT s.name, p.name
FROM student s, professor p
WHERE s.profno(+) = p.profno;

SELF JOIN

SELECT e1.ename, e1.mgr, e2.empno, e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno(+);

Sub Query, 서브쿼리(subquery)

• SQL문을 실행하는 데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 사용하는 SELECT문을 의미

---

JOIN 연습문제

--1번
--student, department
--학생이름, 1전공학과번호, 1전공 학과 이름을 출력
SELECT s.name, s.deptno1, d.dname
FROM student s, department d
WHERE s.deptno1 = d.deptno;

--2번
--emp2, p_grade
--현재 직급이 있는 사원의 이름과 직급, 현재 연봉,
--해당 직급 연봉의 하한금액과 상한금액
SELECT e.name
     , e.position
     , TO_CHAR(e.pay, '999,999,999') PAY
     , TO_CHAR(p.s_pay, '999,999,999') "Low PAY"
     , TO_CHAR(p.e_pay, '999,999,999') "High PAY"
FROM emp2 e, p_grade p
WHERE e.position = p.position(+)
    AND e.position IS NOT NULL;

--3번
--emp2, p_grade
--사원들의 이름, 나이, 현재 직급, 예상 직급
--나이는 2005.05.30일 기준, 소수점 이하는 TRUNC
SELECT e.name
     , TRUNC(MONTHS_BETWEEN(TO_DATE(20050530), e.birthday) / 12) AGE
     , e.position 현재직급
     , p.position 예상직급
FROM emp2 e, p_grade p
WHERE TRUNC((MONTHS_BETWEEN(TO_DATE(20050530), e.birthday) / 12))
        BETWEEN p.s_age AND p.e_age
ORDER BY 2;

--4번
--customer, gift
--Notebook을 선택할 수 있는 고객명과 포인트, 상품명을 출력
SELECT c.gname CUST_NAME, c.point, g.gname
FROM customer c, gift g
WHERE c.point > g.g_start
    AND g.gno = 7
    --AND g.gname = 'Notebook'
ORDER BY c.point DESC;--c.gno, g.gno;
    
--5번
--professor
--교수의 번호, 이름, 입사일, 자신보다 입사일이 빠른 사람 인원수
--자신보다 입사일이 빠른 사람수를 오름차순으로 출력
--(1) RANK()
SELECT profno, name, hiredate
    , RANK() OVER(ORDER BY hiredate)-1
FROM professor;
--(2) JOIN
SELECT p1.profno, p1.name, p1.hiredate, COUNT(p2.profno) COUNT
FROM professor p1, professor p2
WHERE p1.hiredate > p2.hiredate(+)
GROUP BY p1.profno, p1.name, p1.hiredate
ORDER BY p1.hiredate;

--6번
--emp
--사원번호, 이름, 입사일, 자신보다 먼저 입사한 사람 인원수를 출력
--자신보다 입사일이 빠른 사람수를 오름차순으로 출력
SELECT e1.empno, e1.ename, e1.hiredate, COUNT(e2.empno)
FROM emp e1, emp e2
WHERE e1.hiredate > e2.hiredate(+)
GROUP BY e1.empno, e1.ename, e1.hiredate
ORDER BY e1.hiredate;

Sub Query 연습문제

--단일행 Sub Query 연습문제1
--student, department
--Anthony Hopkins 학생과 전공(deptno1)이 동일한 학생들의 이름과 전공이름을 출력
SELECT s.name, s.deptno1, d.dname
FROM student s, department d
WHERE s.deptno1 = d.deptno
  AND s.deptno1 = (SELECT deptno1
                   FROM student
                   WHERE name = 'Anthony Hopkins');
     
--단일행 Sub Query 연습문제2     
--professor, department
--Meg Ryan 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력
SELECT p.name, p.hiredate, d.dname
FROM professor p, department d
WHERE p.deptno = d.deptno
    AND p.hiredate > (SELECT hiredate
                      FROM professor 
                      WHERE name = 'Meg Ryan');

--단일행 Sub Query 연습문제3         
--student
--전공이 201인 학과의 평균 몸무게 보다 몸무게가 많은 학생들의 이름과 몸무게를 출력
SELECT name, weight
FROM student
WHERE weight >
        (SELECT AVG(weight)
         FROM student
         WHERE deptno1 = 201)
ORDER BY weight DESC;