자바 풀 스택 11/25 오전 기록 003-1

8:00 무렵 학원 도착

수업 준비 세팅, 복습, 노트 정리하기

 

<9:30 1교시> 지난 주에 했던 함수 내용 복습 : 문자열 함수, 숫자함수, 날짜 함수, 변환 함수

DQL은 데이터 베이스의 여러 테이블의 여러 row 중에서 원하는 data 값만 빼서 조회하는 언어이다.

 

 

 

 

 

변환 함수 to_date : 문자열을 날짜로 변환

 

 

<10:30 2교시> 단일행 함수 중 일반 함수, 복수행 함수 GROUP BY의 활용

※ 함수가 있는 위치는 함수가 반환하는 결과 값으로 대체된다.

※ row의 각 데이터만 대소문자가 구별되고, 명령어나 칼럼명 등의 식별자들은 대소문자를 가리지 않는다.

 

■ 단일행 함수 : 일반 함수

 

아래의 SELECT문은 직업이 'MANAGER'로 참인 사람들의 결과 값으로는 high를, 거짓인 사람들의 결과 값으로는 low를 입력하게 나타내는 일반 함수 decode 함수이다.

row의 데이터는 대소문자를 구분해주지 않으면 오류가 난다.

 

 

■ 복수행 함수

복수행 함수는 여러 개의 row로부터 1개의 row 값만 나타나게 추출하는 함수이다.

 

여전히, row의 데이터는 대소문자를 구분해주지 않으면 오류가 난다.

 

 

null 값이 있는 경우, null 값이 있는 row의 데이터는 무시되고 계산되기 때문에 null 값의 row의 경우를 포함한 평균이나 총합을 구하고 싶으면 null 값을 우선 0으로 부여하고 나서 진행해야 한다.

 

 

칼럼의 row 값 중 최대, 최소를 구하는 복수행 함수

 

단일행 함수로 얻는 결과 값과 복수행 함수로 얻는 결과 값을 같은 select(결과값)테이블로 만드는 것은 불가능하다.

 

■GROUP BY의 사용

DQL 명령어 중 GROUP BY는 row를 특정 조건으로 그룹으로 묶는다.

부서별, 직업별 등 그룹으로 묶는 상황들에 사용한다.

 

 

 

 

 

 

 

 

 

<11:30 3교시> 보다 복잡한 경우의 GROUP BY 명령어 사용, 테이블의 조인

 

 

■ 테이블의 조인

테이블의 공통 요소를 찾고 테이블 두 개를 연결해준다. 

 

 

공통요소는 표현될 수도 있고 표현되지 않아도 될 수도 있다.

 

 

 

<12: 30 4교시> 오전에 했던 내용을 정리하는 실습 자습 시간

지난 시간에 끝까지 풀지 못했던 이 문제를 풀 수 있을 것 같다. 

 

EMP 테이블에서 SAL(급여)과 COMM(수당)을 더한 값을 TOTAL_SALARY라는 이름으로 표시하며, ENAME, TOTAL_SALARY를 조회하세요.

 

지난 시간에는 NVL(null 값이 존재하는 변수, null 값에 집어 넣을 수) 함수를 배우지 않아서 위 질문에 대한 답을 아래와 같이 했었다.

지난 시간에 했던거 다시 타이핑 함. 이제 ALIAS 함수 as "" 대신 붙은 문구는 그냥 옆에 별칭 붙여주면 되는걸 앎.

 

이 식에서 문제가 됐던 것은 null 값인 comm 부분들이 비교 불가능 및 연산 불가능하여 null 인 comm과 더해진 sal 들도 다 알 수 없는 null 값이 되어 사라져버린 상태였던 것.

이걸 해결 하기 위해 nvl 함수를 활용해서 comm에서 null 값인 애들에 0을 부여한 다음 →nvl(comm,0)

nvl(comm,0)과 sal을 더해서 Total_Salary 라는 별칭을 주고 select 하는 것이다.

정답!!

 

 

응용으로 테이블 조인을 통해서 부서가 SALE 이면서 매니저인 사람들의 이름과 total_salary를 구해보자.

(엄청난 삽질의 시작)

삽질의 기록

SQL> --부서가 SALE 이면서 매니저인 사람들의 이름과 total_salary를 구해보자.
SQL>
SQL> select ename, sal+nvl(comm,0) total_salary
  2  from emp, dept
  3  where dname.dept = "SALES" AND emp.job="MANAGER";
where dname.dept = "SALES" AND emp.job="MANAGER"
                                       *
ERROR at line 3:
ORA-00904: "MANAGER": invalid identifier


SQL> select job, ename from emp;

JOB        ENAME
---------- ----------
CLERK      SMITH
SALESMAN   ALLEN
SALESMAN   WARD
MANAGER    JONES
SALESMAN   MARTIN
MANAGER    BLAKE
MANAGER    CLARK
PRESIDENT  KING
SALESMAN   TURNER
CLERK      JAMES
ANALYST    FORD
CLERK      MILLER

12 rows selected.

SQL> --부서가 SALE 이면서 매니저인 사람들의 이름과 total_salary를 구해보자.
SQL> select ename, sal+nvl(comm,0) total_salary
  2  from emp, dept
  3  where dept.dname ="SALES" and emp.job="MANAGER";
where dept.dname ="SALES" and emp.job="MANAGER"
                                      *
ERROR at line 3:
ORA-00904: "MANAGER": invalid identifier


SQL> --부서가 SALE 이면서 매니저인 사람들의 이름과 total_salary를 구해보자.
SQL> select ename, sal+nvl(comm,0) total_salary
  2  from emp, dept
  3  where dept.dname ="SALES" and job="MANAGER";
where dept.dname ="SALES" and job="MANAGER"
                                  *
ERROR at line 3:
ORA-00904: "MANAGER": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary
  2  from emp, dept
  3  where dept.dname = "SALES";
where dept.dname = "SALES"
                   *
ERROR at line 3:
ORA-00904: "SALES": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary
  2  from emp, dept
  3  where dept.dname "SALES" and job "MANAGER";
where dept.dname "SALES" and job "MANAGER"
                 *
ERROR at line 3:
ORA-00920: invalid relational operator


SQL> select ename, sal+nvl(comm,0) total_salary
  2  from emp, dept
  3  where dept.dname "SALES" and emp.job "MANAGER";
where dept.dname "SALES" and emp.job "MANAGER"
                 *
ERROR at line 3:
ORA-00920: invalid relational operator


SQL> select ename, sal+nvl(comm,0) total_salary
  2  from emp, dept
  3  where dname="SALES";
where dname="SALES"
            *
ERROR at line 3:
ORA-00904: "SALES": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary
  2  from emp, dept
  3  where dept.dname="SALES";
where dept.dname="SALES"
                 *
ERROR at line 3:
ORA-00904: "SALES": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary
  2  from emp, dept
  3  where dept.dname ="SALES" and emp.job="MANAGER" and dept.deptno=emp.deptno;
where dept.dname ="SALES" and emp.job="MANAGER" and dept.deptno=emp.deptno
                                      *
ERROR at line 3:
ORA-00904: "MANAGER": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary
  2  from emp, dept
  3  where dept.dname ="SALES" and emp.job="MANAGER" and dept.deptno=emp.deptno;
where dept.dname ="SALES" and emp.job="MANAGER" and dept.deptno=emp.deptno
                                      *
ERROR at line 3:
ORA-00904: "MANAGER": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary, dname
  2  from emp, dept
  3  where dept.dname ="SALES" and emp.job="MANAGER" and dept.deptno=emp.deptno;
where dept.dname ="SALES" and emp.job="MANAGER" and dept.deptno=emp.deptno
                                      *
ERROR at line 3:
ORA-00904: "MANAGER": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary, dname
  2  from emp, dept
  3  where dname="SALES" and job="MANAGER" and dept.deptno=emp.deptno;
where dname="SALES" and job="MANAGER" and dept.deptno=emp.deptno
                            *
ERROR at line 3:
ORA-00904: "MANAGER": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary, dname
  2  from emp, dept;

ENAME      TOTAL_SALARY DNAME
---------- ------------ ----------------------------
SMITH               800 ACCOUNTING
ALLEN              1900 ACCOUNTING
WARD               1750 ACCOUNTING
JONES              2975 ACCOUNTING
MARTIN             2650 ACCOUNTING
BLAKE              2850 ACCOUNTING
CLARK              2450 ACCOUNTING
KING               5000 ACCOUNTING
TURNER             1500 ACCOUNTING
JAMES               950 ACCOUNTING
FORD               3000 ACCOUNTING
MILLER             1300 ACCOUNTING
SMITH               800 RESEARCH
ALLEN              1900 RESEARCH
WARD               1750 RESEARCH
JONES              2975 RESEARCH
MARTIN             2650 RESEARCH

ENAME      TOTAL_SALARY DNAME
---------- ------------ ----------------------------
BLAKE              2850 RESEARCH
CLARK              2450 RESEARCH
KING               5000 RESEARCH
TURNER             1500 RESEARCH
JAMES               950 RESEARCH
FORD               3000 RESEARCH
MILLER             1300 RESEARCH
SMITH               800 SALES
ALLEN              1900 SALES
WARD               1750 SALES
JONES              2975 SALES
MARTIN             2650 SALES
BLAKE              2850 SALES
CLARK              2450 SALES
KING               5000 SALES
TURNER             1500 SALES
JAMES               950 SALES

ENAME      TOTAL_SALARY DNAME
---------- ------------ ----------------------------
FORD               3000 SALES
MILLER             1300 SALES
SMITH               800 OPERATIONS
ALLEN              1900 OPERATIONS
WARD               1750 OPERATIONS
JONES              2975 OPERATIONS
MARTIN             2650 OPERATIONS
BLAKE              2850 OPERATIONS
CLARK              2450 OPERATIONS
KING               5000 OPERATIONS
TURNER             1500 OPERATIONS
JAMES               950 OPERATIONS
FORD               3000 OPERATIONS
MILLER             1300 OPERATIONS

48 rows selected.

SQL> select ename, sal+nvl(comm,0) total_salary, dname
  2  from emp, dept;

ENAME      TOTAL_SALARY DNAME
---------- ------------ ----------------------------
SMITH               800 ACCOUNTING
ALLEN              1900 ACCOUNTING
WARD               1750 ACCOUNTING
JONES              2975 ACCOUNTING
MARTIN             2650 ACCOUNTING
BLAKE              2850 ACCOUNTING
CLARK              2450 ACCOUNTING
KING               5000 ACCOUNTING
TURNER             1500 ACCOUNTING
JAMES               950 ACCOUNTING
FORD               3000 ACCOUNTING
MILLER             1300 ACCOUNTING
SMITH               800 RESEARCH
ALLEN              1900 RESEARCH
WARD               1750 RESEARCH
JONES              2975 RESEARCH
MARTIN             2650 RESEARCH

ENAME      TOTAL_SALARY DNAME
---------- ------------ ----------------------------
BLAKE              2850 RESEARCH
CLARK              2450 RESEARCH
KING               5000 RESEARCH
TURNER             1500 RESEARCH
JAMES               950 RESEARCH
FORD               3000 RESEARCH
MILLER             1300 RESEARCH
SMITH               800 SALES
ALLEN              1900 SALES
WARD               1750 SALES
JONES              2975 SALES
MARTIN             2650 SALES
BLAKE              2850 SALES
CLARK              2450 SALES
KING               5000 SALES
TURNER             1500 SALES
JAMES               950 SALES

ENAME      TOTAL_SALARY DNAME
---------- ------------ ----------------------------
FORD               3000 SALES
MILLER             1300 SALES
SMITH               800 OPERATIONS
ALLEN              1900 OPERATIONS
WARD               1750 OPERATIONS
JONES              2975 OPERATIONS
MARTIN             2650 OPERATIONS
BLAKE              2850 OPERATIONS
CLARK              2450 OPERATIONS
KING               5000 OPERATIONS
TURNER             1500 OPERATIONS
JAMES               950 OPERATIONS
FORD               3000 OPERATIONS
MILLER             1300 OPERATIONS

48 rows selected.

SQL> select ename, sal+nvl(comm,0) total_salary, dname
  2  from emp, dept
  3  where dept.deptno=emp.deptno;

ENAME      TOTAL_SALARY DNAME
---------- ------------ ----------------------------
SMITH               800 RESEARCH
ALLEN              1900 SALES
WARD               1750 SALES
JONES              2975 RESEARCH
MARTIN             2650 SALES
BLAKE              2850 SALES
CLARK              2450 ACCOUNTING
KING               5000 ACCOUNTING
TURNER             1500 SALES
JAMES               950 SALES
FORD               3000 RESEARCH
MILLER             1300 ACCOUNTING

12 rows selected.

SQL> select ename, sal+nvl(comm,0) total_salary, dname
  2  from emp, dept
  3  where dept.deptno=emp.deptno and Dname = sales;
where dept.deptno=emp.deptno and Dname = sales
                                         *
ERROR at line 3:
ORA-00904: "SALES": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary, dname
  2  where dept.deptno=emp.deptno and Dname = sales;
where dept.deptno=emp.deptno and Dname = sales
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected


SQL> select ename, sal+nvl(comm,0) total_salary, dname
  2  from emp, dept
  3  where dept.deptno=emp.deptno and Dname = 'SALES';

ENAME      TOTAL_SALARY DNAME
---------- ------------ ----------------------------
ALLEN              1900 SALES
WARD               1750 SALES
MARTIN             2650 SALES
BLAKE              2850 SALES
TURNER             1500 SALES
JAMES               950 SALES

6 rows selected.

SQL> select ename, sal+nvl(comm,0) total_salary, dname, job
  2  from emp, dept
  3  where dept.deptno=emp.deptno and Dname = 'SALES' and job="MANAGER";
where dept.deptno=emp.deptno and Dname = 'SALES' and job="MANAGER"
                                                         *
ERROR at line 3:
ORA-00904: "MANAGER": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary, dname, job
  2  from emp, dept
  3  where dept.deptno=emp.deptno and Dname = 'SALES;
ERROR:
ORA-01756: quoted string not properly terminated


SQL> select ename, sal+nvl(comm,0) total_salary, dname, job
  2  where dept.deptno=emp.deptno and Dname = 'SALES;
ERROR:
ORA-01756: quoted string not properly terminated


SQL> select ename, sal+nvl(comm,0) total_salary, dname, job
  2  from emp, dept
  3  where dept.deptno=emp.deptno and Dname = 'SALES';

ENAME      TOTAL_SALARY DNAME                        JOB
---------- ------------ ---------------------------- ----------
ALLEN              1900 SALES                        SALESMAN
WARD               1750 SALES                        SALESMAN
MARTIN             2650 SALES                        SALESMAN
BLAKE              2850 SALES                        MANAGER
TURNER             1500 SALES                        SALESMAN
JAMES               950 SALES                        CLERK

6 rows selected.

SQL> select ename, sal+nvl(comm,0) total_salary, dname, job="MANAGER"
  2  from emp, dept
  3  where dept.deptno=emp.deptno and Dname = 'SALES';
select ename, sal+nvl(comm,0) total_salary, dname, job="MANAGER"
                                                      *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL>  select ename, sal+nvl(comm,0) total_salary, dname, job
  2    2  from emp, dept
  3    2  from emp, dept^Z^Z
;
  2  from emp, dept
  *
ERROR at line 2:
ORA-00923: FROM keyword not found where expected


SQL>  select ename, sal+nvl(comm,0) total_salary, dname, job
  2    2  from emp, dept
  3    where job="MANAGER" and dept.deptno=emp.deptno and Dname = 'SALES';
  2  from emp, dept
  *
ERROR at line 2:
ORA-00923: FROM keyword not found where expected


SQL>  select ename, sal+nvl(comm,0) total_salary, dname, job
  2  from emp, dept
  3    where job="MANAGER" and dept.deptno=emp.deptno and Dname = 'SALES';
  where job="MANAGER" and dept.deptno=emp.deptno and Dname = 'SALES'
            *
ERROR at line 3:
ORA-00904: "MANAGER": invalid identifier


SQL> select ename, sal+nvl(comm,0)
  2  from (select ename, sal+nvl(comm,0) total_salary, dname, job
  3  from emp, dept
  4  where dept.deptno=emp.deptno and Dname = 'SALES';)
  5  where job="MANAGER";
where dept.deptno=emp.deptno and Dname = 'SALES';)
                                                *
ERROR at line 4:
ORA-00911: invalid character


SQL> select ename, sal+nvl(comm,0)
  2  from (select ename, sal+nvl(comm,0) total_salary, dname, job
  3  from emp, dept
  4  where dept.deptno=emp.deptno and Dname = 'SALES')
  5  where job="MANAGER";
where job="MANAGER"
          *
ERROR at line 5:
ORA-00904: "MANAGER": invalid identifier


SQL> select ename, job from emp where job="MANAGER"
  2  ;
select ename, job from emp where job="MANAGER"
                                     *
ERROR at line 1:
ORA-00904: "MANAGER": invalid identifier


SQL> select * from emp
  2  ;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80/12/17        800                    20
      7499 ALLEN      SALESMAN         7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN         7698 81/02/22       1250        500         30
      7566 JONES      MANAGER          7839 81/04/02       2975                    20
      7654 MARTIN     SALESMAN         7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER          7839 81/05/01       2850                    30
      7782 CLARK      MANAGER          7839 81/06/09       2450                    10
      7839 KING       PRESIDENT             81/11/17       5000                    10
      7844 TURNER     SALESMAN         7698 81/09/08       1500          0         30
      7900 JAMES      CLERK            7698 81/12/03        950                    30
      7902 FORD       ANALYST          7566 81/12/03       3000                    20
      7934 MILLER     CLERK            7782 82/01/23       1300                    10

12 rows selected.

SQL> select ename, sal+nvl(comm,0)
  2  from (select ename, sal+nvl(comm,0) total_salary, dname, job
  3  from emp, dept
  4  where dept.deptno=emp.deptno and Dname = 'SALES')
  5  where job='MANAGER';
select ename, sal+nvl(comm,0)
                      *
ERROR at line 1:
ORA-00904: "COMM": invalid identifier


SQL> select ename, sal+nvl(comm,0) total_salary, dname, job
  2  from emp, dept
  3  where dept.deptno=emp.deptno and Dname = 'SALES' and job='MANAGER';

ENAME      TOTAL_SALARY DNAME                        JOB
---------- ------------ ---------------------------- ----------
BLAKE              2850 SALES                        MANAGER

 

그럼 또 이거 응용으로 부서가 SALES이면서 SALESMAN인 사람들의 이름과 total_salary를 구해보자.

--부서가 SALES 이면서 SALESMAN인 사람들의 이름과 total_salary를 구해보자. //로 시작하고 어딘가 더 추가해서 중간과정이 드러난 모지리 풀이 과정 ㅋㅋ

아싸뵹

이제 이거 마스터한듯? ㅋ.

ㅋ

ㅋ

ㅋ ㅋ ㅋㅋㅋ

ㅋ

 

ㅋ

ㅋ

ㅋ

ㅋㅋㅋㅋㅋㅋㅋㅋ

 

+ 13:44 추가)

아까 배고프고 바보짓 탈출하고 성공했다는 기쁨에 겨워서dname이랑 job은 굳이 select 할 필요도 없단걸 완탕면 먹으면서 깨달음.

--부서가 SALES 이면서 SALESMAN인 사람들의 이름과 total_salary를 구해보자.

 

크하하하하하하하하하하하하하하하하핫

크하핳ㅅ

 

+ 13:55추가)

응용으로 LIKE 함수도 써봄 ㅎ 크하하하하하하핫 

부서가 SALES 이면서 SALESMAN인 사원들 중 이름에 E가 포함된 사원의 이름과 total_salary를 구해보자.