7:55 무렵 학원 도착
수업 준비 세팅, 복습, 노트 정리하기
<9:30 1교시>
지난 시간 복습
테이블의 조인 방법 : ANSI(AMERICA NATIONAL STANDARD INSTITUTE, 미국국가표준협회) JOIN
테이블의 셀프 조인
테이블의 아우터 조인
- outer join이면 빈공간이 되는 쪽에 (+) 해줘야 하고, outer join을 입력할거면 (+) 굳이 안써줘도 됨
- null 값이던 녀석에 값을 부여하고 싶으면 NVL을 활용하면 됨
테이블 3개의 조인 : 조인할 테이블 개수-1만큼의 join 절이 나온다.
서브쿼리
+ 지난시간 복습에 추가된 내용
■ USING 절의 사용 : join 조건에서 칼럼명이 두 테이블에 동일하게 들어가 있을 때 간단하게 쓰는 방법
join 조건의 칼럼명이 같은 경우 좀 더 간단한 표현식을 쓸 수 있다. 이때는 select 에서의 칼럼명이 모호해도 되고,
join 절에서 on 이후를 USING(칼럼명)으로 대체해서 쓰면 된다.
<10:30 2교시> 서브쿼리의 종류들, 서브쿼리를 이용한 페이징 방식의 기초 다지기
■ 서브쿼리의 유형
- 단일행 서브쿼리 : 서브쿼리로 들어갈 SELECT 문에서 선택한 row 값이 1개
-다중행 서브쿼리 : 여러 row를 반환하는 서브쿼리, 주로 IN, ANY, ALL 연산자와 함께 사용됨
- 상관 서브쿼리
- FROM 절에서의 서브쿼리 : 서브쿼리가 임시 테이블처럼 작동하여 데이터를 제공한다
■ 서브쿼리를 활용한 페이징의 기초 다지기
우리가 만들게 되는 웹페이지에서 여러가지 글 등이 입력되면 우리는 웹브라우저 이용자들에게 1페이지에 모든 글을 제공하지 않고 페이지마다 몇 개의 글만 띄워주게 된다. 페이지당 보이는 글의 개수가 다른 것이다.
이걸 운동장에 모인 사람들에 비유하자면, 운동장에 모인 사람들을 키 순서로 정렬시키고 그 사람들에게 번호를 부여한 다음, 몇번부터 몇번까지 나오세요 라고 하면 된다는 거.
그래서 페이징의 핵심은
① 정렬하고(SELECT, ORDER BY) ② 순번을 부여하고(ROWNUM) ③ 번호를 추출하는 것(SELECT,WHERE)
SQL> --사원의 급여로 내림차순 정렬해서 위에서부터 5명씩 잘랐을 때 2번째 그룹의 사원번호, 사원 이름, 급여를 출력해보세요.
SQL> select empno, ename, sal
2 from emp
3 order by sal desc;
EMPNO ENAME SAL
---------- -------------------- ----------
7839 KING 5000
7902 FORD 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7654 MARTIN 1250
7521 WARD 1250
7900 JAMES 950
7369 SMITH 800
12 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select sub.*
2* from (select empno, ename, sal from emp order by sal desc) sub
SQL> /
EMPNO ENAME SAL
---------- -------------------- ----------
7839 KING 5000
7902 FORD 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7654 MARTIN 1250
7521 WARD 1250
7900 JAMES 950
7369 SMITH 800
12 rows selected.
SQL> ed/
SQL> ed
Wrote file afiedt.buf
1 select sub.*,ROWNUM
2* from (select empno, ename, sal from emp order by sal desc) sub
SQL> /
EMPNO ENAME SAL ROWNUM
---------- -------------------- ---------- ----------
7839 KING 5000 1
7902 FORD 3000 2
7566 JONES 2975 3
7698 BLAKE 2850 4
7782 CLARK 2450 5
7499 ALLEN 1600 6
7844 TURNER 1500 7
7934 MILLER 1300 8
7654 MARTIN 1250 9
7521 WARD 1250 10
7900 JAMES 950 11
7369 SMITH 800 12
12 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select sub.*,ROWNUM order
2* from (select empno, ename, sal from emp order by sal desc) sub
SQL> /
select sub.*,ROWNUM order
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> /
select sub.*,ROWNUM order
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ed
Wrote file afiedt.buf
1 select sub.*,ROWNUM order
2* from (select empno, ename, sal from emp order by sal desc) sub
SQL> /
select sub.*,ROWNUM order
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ed
Wrote file afiedt.buf
1 select *
2 from
3 (select sub.*,ROWNUM orderbunho
4 from (select empno, ename, sal from emp order by sal desc) sub
5* where orderbunho between 6 and 10
SQL> /
where orderbunho between 6 and 10
*
ERROR at line 5:
ORA-00907: missing right parenthesis
SQL> ed
Wrote file afiedt.buf
1 select *
2 from
3 (select sub.*,ROWNUM orderbunho
4 from (select empno, ename, sal from emp order by sal desc) sub)
5* where orderbunho between 6 and 10
SQL> /
EMPNO ENAME SAL ORDERBUNHO
---------- -------------------- ---------- ----------
7499 ALLEN 1600 6
7844 TURNER 1500 7
7934 MILLER 1300 8
7654 MARTIN 1250 9
7521 WARD 1250 10
ROWNUM은 order라고 별칭을 지으면 안된다.
■ WITH절 서브쿼리서브쿼리의 사용은 식을 복잡하게 만들 수 있어서 사용하기 전에 WITH절을 이용해 미리 정의해준 것을 별칭처럼 사용해서 식을 더 간단하게 만들어 가독성을 높여줄 수 있다.아래는 약간의 삽질과 WITH절을 이용해서 만든 표.
SQL> with AvgSalaries As (select deptno, avg(sal) as avg_sal from
2
SQL>
SQL> with AvgSalaries As (
2 select depno, avg(Sal) as avg_sal
3 from emp
4 group by deptno)
5 select d.dname, a.avg_sal
6 from AvgSalaries a
7 join dept d on a.deptno = d.deptno;
select depno, avg(Sal) as avg_sal
*
ERROR at line 2:
ORA-00904: "DEPNO": invalid identifier
SQL> with AvgSalaries As (
2 select depno, avg(Sal) as avg_sal
3 from emp
4 group by deptno)
5 select d.danme, a.avg_sal
6 from AvgSalaries a
7 join dept d on a.deptno = d.deptno;
select depno, avg(Sal) as avg_sal
*
ERROR at line 2:
ORA-00904: "DEPNO": invalid identifier
SQL> with AvgSalaries As (
2 select deptno, avg(Sal) as avg_sal
3 from emp
4 group by deptno)
5 from AvgSalaries a
6 join dept d on a.deptno = d.deptno;
from AvgSalaries a
*
ERROR at line 5:
ORA-00928: missing SELECT keyword
SQL> ed
Wrote file afiedt.buf
1 with AvgSalaries As (
2 select deptno, avg(Sal) as avg_sal
3 from emp
4 group by deptno)
5 select d.dname, a.avg_sal
6 from AvgSalaries a
7* join dept d on a.deptno = d.deptno
SQL> /
DNAME AVG_SAL
---------------------------- ----------
SALES 1566.66667
RESEARCH 2258.33333
ACCOUNTING 2916.66667삽질한 부분 : 오타와 문장 날린거 -> ed 누르고 엔터 -> 메모장에서 수정하고 저장하고 닫기 -> / 눌러 메모장에서 만든거 실행
<11:30 3교시>
데이터 출력 시 서브쿼리와 조인 방식이 모두 가능한 경우가 다수 존재.
물론 두 방법 다 적용하기 힘든 경우도 존재.
single row 서브쿼리에 여러 row 가 반환된 경우 오류가 나는데, 이럴 때는 IN을 써서 해결할 수 있다.
예제 삽질과 올바른 풀이(나머지 예제는 자습 시간에 마저 풀어봐야겠다)
SQL> select ename, sal, deptno
2 from emp
3 where sal IN(select sal from emp where Max(sal) group by deptno);
where sal IN(select sal from emp where Max(sal) group by deptno)
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SQL> select empno, sal, deptno
2 from emp
3 where sal IN(select Max(sal) from emp group by deptno);
EMPNO SAL DEPTNO
---------- ---------- ----------
7698 2850 30
7839 5000 10
7902 3000 20
SQL> -- 정답 먼저 작성해봄
SQL> select deptno, dname, loc from dept where deptno in(select deptno from emp where job='MANAGER');
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
SQL> -문제 보면서 작성을 시도 해봄
SQL> select deptno, dname, loc from emp where job in(select deptno from emp where job='MANAGER');
select deptno, dname, loc from emp where job in(select deptno from emp where job='MANAGER')
*
ERROR at line 1:
ORA-00904: "LOC": invalid identifier
SQL> -- 첫번째 에러는 loc from dept여야 하는데 from emp라고 적었던거
SQL> select deptno, dname, loc from dept where job in(select deptno from emp where job='MANAGER');
select deptno, dname, loc from dept where job in(select deptno from emp where job='MANAGER')
*
ERROR at line 1:
ORA-00904: "JOB": invalid identifier
SQL> 두번째 에러는 from dept인데 여기서 job을 찾고 앉아 있었다는거
SQL> select deptno, dname, loc from dept where deptno in(select deptno from emp where job="MANAGER")
2 ;
select deptno, dname, loc from dept where deptno in(select deptno from emp where job="MANAGER")
*
ERROR at line 1:
ORA-00904: "MANAGER": invalid identifier
SQL> 세번째 에러는 '' 써야하는데 "" 쓴거
SQL> select deptno, dname, loc from dept where deptno in(select deptno from emp where job='MANAGER')
2 ;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
SQL> select empno, sal, deptno
2 from emp
3 where sal in (SELect Max(sal) from emp group by deptno);
EMPNO SAL DEPTNO
---------- ---------- ----------
7698 2850 30
7839 5000 10
7902 3000 20
SQL> select ename, sal from emp where sal > (select Max(Sal) from emp where deptno=30);
ENAME SAL
-------------------- ----------
JONES 2975
KING 5000
FORD 3000
SQL> select ename, sal
2 from emp
3 where Max(sal) in (selec sal from emp where deptno=30)
4 ;
where Max(sal) in (selec sal from emp where deptno=30)
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SQL> select ename, sal
2 from emp
3 where Max(sal) in (select sal from emp where deptno=30);
where Max(sal) in (select sal from emp where deptno=30)
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SQL> select ename, sal
2 from emp
3 where Max(sal) in(select sal from emp where deptno=30);
where Max(sal) in(select sal from emp where deptno=30)
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SQL> select ename, sal
2 from emp
3 where Max(sal) all(select sal from emp where deptno=30);
where Max(sal) all(select sal from emp where deptno=30)
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SQL> select ename, sal
2 from emp
3 where sal all(select sal from emp where deptno=30);
where sal all(select sal from emp where deptno=30)
*
ERROR at line 3:
ORA-00920: invalid relational operator
SQL> select ename, sal
2 from emp
3 where sal> all(select sal from emp where deptno=30);
ENAME SAL
-------------------- ----------
JONES 2975
FORD 3000
KING 5000여기서 마지막 즈음에
where sal>all(select sal from emp where deptno=30);
이것이 비교가 가능한 이유는 all이 다중행 서브 쿼리에 포함된 row들을 모두 비교하며 실행해주기 때문.
이 경우도 마찬가지로, sal > any가 비교 가능한 이유는
(select sal from emp where job ='SALESMAN')이라는 다중행(multirow) 서브쿼리의 여러 row 중 어느 하나라도 만족하면 되니까 any가 sal에 대한 크기 비교를 각각 실행해서 가능하다.
<12:30 4교시> 오전 내용 각자 복습 및 예제 풀어보기
-- 급여가 3000이상인 직원의 이름과 급여를 조회하시오
SQL> -- 급여가 3000이상인 직원의 이름과 급여를 조회하시오
SQL> select ename, sal from emp
2 where sal >=3000;
ENAME SAL
-------------------- ----------
KING 5000
FORD 3000
-- 매니저가 NULL인 직원의 이름을 조회하시오
SQL> -- 매니저가 NULL인 직원의 이름을 조회하시오
SQL> select ename from emp
2 where mgr is NULL;
ENAME
--------------------
KING
--커미션이 NULL이 아닌 직원의 이름과 커미션을 조회하시오 (오타 에러 주의)
SQL> --커미션이 NULL이 아닌 직원의 이름과 커미션을 조회하시오
SQL> select ename, comm form emp where comm is not null;
select ename, comm form emp where comm is not null
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select ename, comm from emp where comm is not null;
ENAME COMM
-------------------- ----------
ALLEN 300
WARD 500
MARTIN 1400
TURNER 0--이름이 'A'로 시작하는 직원의 이름과 직책을 조회하시오
SQL> --이름이 'A'로 시작하는 직원의 이름과 직책을 조회하시오
SQL> select ename, job where ename = 'A%';
select ename, job where ename = 'A%'
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select ename, job from emp where ename = 'A%';
no rows selected
SQL> select ename, job from emp where ename Like('A%');
ENAME JOB
-------------------- ------------------
ALLEN SALESMAN--부서 번호가 20인 직원 중 급여가 가장 높은 직원의 이름과 급여를 조회하시오
SQL> --부서 번호가 20인 직원 중 급여가 가장 높은 직원의 이름과 급여를 조회하시오
SQL> select ename, sal from emp
2 where sal (select max(sal) from emp where deptno=20);
where sal (select max(sal) from emp where deptno=20)
*
ERROR at line 2:
ORA-00936: missing expression
SQL> select ename, sal from emp
2 where sal=(select max(sal) from emp where deptno=20);
ENAME SAL
-------------------- ----------
FORD 3000--각 부서의 직원 수를 조회하시오
+ 응용 시도 --각 부서의 직원수를 조회하시오(직원수가 0인 부서도 함께 출력할것) : 실패
SQL> --각 부서의 직원 수를 조회하시오
SQL> select count(ename) from emp group by deptno;
COUNT(ENAME)
------------
6
3
3
SQL> select deptno, count(ename)(+) from emp group by deptno;
select deptno, count(ename)(+) from emp group by deptno
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select deptno, count(ename) from emp group by deptno;
DEPTNO COUNT(ENAME)
---------- ------------
30 6
20 3
10 3
SQL> select deptno, count(nvl(ename, '0')) from emp group by deptno;
DEPTNO COUNT(NVL(ENAME,'0'))
---------- ---------------------
30 6
20 3
10 3
SQL> --각 부서의 직원수를 조회하시오(직원수가 0인 부서도 함께 출력할것)
SQL> select deptno, count(nvl(deptno, '0')) from emp group by deptno;
DEPTNO COUNT(NVL(DEPTNO,'0'))
---------- ----------------------
30 6
20 3
10 3
SQL> select deptno from emp group by deptno;
DEPTNO
----------
30
20
10
SQL> select deptno, ename from emp group by deptno;
select deptno, ename from emp group by deptno
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> select deptno, count(ename) from emp e, dept d where e.deptno=d.deptno;
select deptno, count(ename) from emp e, dept d where e.deptno=d.deptno
*
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL> select e.deptno, count(ename) form emp e, dept d where e.deptno=d.deptno;
select e.deptno, count(ename) form emp e, dept d where e.deptno=d.deptno
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select e.deptno, count(ename) from emp e, dept d where e.deptno=d.deptno;
select e.deptno, count(ename) from emp e, dept d where e.deptno=d.deptno
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> select e.deptno, count(ename) from emp e, dept d where e.deptno=d.deptno group by deptno;
select e.deptno, count(ename) from emp e, dept d where e.deptno=d.deptno group by deptno
*
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL> select e.deptno, count(ename) from emp e, dept d where e.deptno=d.deptno group by e.deptno;
DEPTNO COUNT(ENAME)
---------- ------------
30 6
20 3
10 3
SQL> select e.deptno, count(nvl(ename,'0')) from emp e, dept d where e.deptno=d.deptno group by e.dept
no;
DEPTNO COUNT(NVL(ENAME,'0'))
---------- ---------------------
30 6
20 3
10 3
SQL> select e.deptno, count(nvl(e.deptno,'0')) from emp e, dept d where e.deptno=d.deptno group by e.d
eptno;
DEPTNO COUNT(NVL(E.DEPTNO,'0'))
---------- ------------------------
30 6
20 3
10 3--급여 합계가 5000 이상인 부서의 부서 번호와 급여 합계를 조회하시오.
일단 삽질의 흔적 더보기로 닫아 놓기...
SQL> --급여 합계가 5000 이상인 부서의 부서 번호와 급여 합계를 조회하시오.
SQL> select deptno, SUM(sal) from emp where SUM(sal)>=5000;
select deptno, SUM(sal) from emp where SUM(sal)>=5000
*
ERROR at line 1:
ORA-00934: group function is not allowed here
SQL> select deptno, SUM(sal) from emp where SUM(sal)>=5000 group by deptno;
select deptno, SUM(sal) from emp where SUM(sal)>=5000 group by deptno
*
ERROR at line 1:
ORA-00934: group function is not allowed here
SQL> select deptno, sum(sal) from (select deptno, sal from emp group by deptno) where sum(sal)>=5000;
select deptno, sum(sal) from (select deptno, sal from emp group by deptno) where sum(sal)>=5000
*
ERROR at line 1:
ORA-00934: group function is not allowed here
SQL> select deptno, sum_sal from (select deptno, sum(sal) SUM_SAL from emp group by deptno) where sum(
sal)>=5000;
select deptno, sum_sal from (select deptno, sum(sal) SUM_SAL from emp group by deptno) where sum(sal)>=5000
*
ERROR at line 1:
ORA-00934: group function is not allowed here
SQL> select deptno, Tot from emp where Sum(select sal from emp group by deptno) Tot>=5000;
select deptno, Tot from emp where Sum(select sal from emp group by deptno) Tot>=5000
*
ERROR at line 1:
ORA-00934: group function is not allowed here
SQL> select deptno, sum(select sal from emp group by deptno) from emp;
select deptno, sum(select sal from emp group by deptno) from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select sal from emp group by deptno;
select sal from emp group by deptno
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> select deptno, ename, sal from emp group by deptno;
select deptno, ename, sal from emp group by deptno
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
삽질한 이유가...... 하.... group by 해놓고 where 절로 그룹을 없애려고 하고 앉아 있어서 그랬다는 거지~
HAVING 절 썼어야 함...
SQL> select deptno, sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 6775
10 8750
SQL> select deptno, sum(Sal) from emp group by deptno where deptno;
select deptno, sum(Sal) from emp group by deptno where deptno
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select deptno, sum(Sal) from emp group by deptno where sum(sal)>=5000;
select deptno, sum(Sal) from emp group by deptno where sum(sal)>=5000
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select deptno, sum(Sal) from emp group by deptno having sum(sal)>=5000;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 6775
10 8750
--부서별 최고 급여를 받는 직원의 이름과 급여를 조회하시오
->점심에도 실패 5교시로
--급여가 부서별 평균급여보다 높은 직원의 이름, 급여를 조회하시오.
->점심에도 실패 5교시로
--모든 직원의 급여와 평균 급여의 차이를 계산하시오.
->점심에도 실패 5교시로
<오전 수업 마무리>
오전에 자꾸 졸음도 오고 집중력도 떨어졌다 ㅠㅠ
밤에 자꾸 잠을 못 자서 그러는거 같긴한데.. 오늘은 더 일찍 자려고 시도해봐야겠...는데 또 4시간만 자고 깨버리면.. 음.. 어떡하지 ㅠ
7,8,9,10 예제는 삽질만 계속 했다.
뭔가 서브쿼리가 들어오면서 어제 배운 조인이랑 뒤죽박죽 섞인 느낌...
'공부의 기록 > 자바 풀 스택 : 수업내용정리' 카테고리의 다른 글
| 자바 풀 스택 11/27 오전 기록 005-1 (1) | 2024.11.27 |
|---|---|
| 자바 풀 스택 11/26 오후 기록 004-2 (0) | 2024.11.26 |
| 자바 풀 스택 11/25 오후 기록 003-2 (0) | 2024.11.25 |
| 자바 풀 스택 11/25 오전 기록 003-1 (0) | 2024.11.25 |
| 자바 풀 스택 11/22 오후 기록 002-2 (0) | 2024.11.22 |