자바 풀 스택 11/26 오후 기록 004-2

<14:30 5교시>

 

--부서 번호가 20인 직원 중 급여가 가장 높은 직원의 이름과 급여를 조회하시오

아래 코드에서 where deptno=20 and sal=(select max(sal) from emp where deptno=20);이라고 한 이유는 부서가 20이 아닌 곳에서 동일한 급여를 가진 사원의 이름도 같이 나올 수 있어서.

where deptno=20 and sal=(3000)이어

SQL> select ename, sal from emp
  2  where deptno=20 and sal=(select max(sal) from emp where deptno=20);

ENAME                       SAL
-------------------- ----------
FORD                       3000

 

--각 부서의 직원 수를 조회하시오(별칭은 한글이어도 띄어쓰기 없으면 문제 없음)

SQL> --각 부서의 직원 수를 조회하시오
SQL> select deptno, count(*) "직원수"
  2  from emp
  3  group by deptno;

    DEPTNO     직원수
---------- ----------
        30          6
        20          3
        10          3

--급여 합계가 5000 이상인 부서의 부서 번호와 급여 합계를 조회하시오.(로우의 배제 없음. 그룹의 배제 있음)

SQL> --급여 합계가 5000 이상인 부서의 부서 번호와 급여 합계를 조회하시오.
SQL> select deptno, sum(sal)
  2  from emp
  3  group by deptno
  4  having sum(sal)>=5000;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20       6775
        10       8750

--부서별 최고 급여를 받는 직원의 이름과 급여를 조회하시오

SQL> --부서별 최고 급여를 받는 직원의 이름과 급여를 조회하시오
SQL> select deptno, ename, sal from emp where sal in(select max(sal) from emp group by deptno);

    DEPTNO ENAME                       SAL
---------- -------------------- ----------
        30 BLAKE                      2850
        10 KING                       5000
        20 FORD                       3000
        

SQL> select deptno, ename, sal from emp
  2  where (deptno, sal) in(select deptno, max(sal) from emp group by deptno);

    DEPTNO ENAME                       SAL
---------- -------------------- ----------
        30 BLAKE                      2850
        10 KING                       5000
        20 FORD                       3000

--급여가 부서별 평균급여보다 높은 직원의 이름, 급여를 조회하시오.

SQL>--급여가 부서별 평균급여보다 높은 직원의 이름, 급여를 조회하시오.
SQL> select ename,sal from emp
  2  where sal >(select AVG(sal) from emp group by deptno)
  3
SQL> select ename, sal
  2  from emp e1
  3  where sal > (select avg(Sal) from emp where deptno=e1.deptno);

ENAME                       SAL
-------------------- ----------
ALLEN                      1600
JONES                      2975
BLAKE                      2850
KING                       5000
FORD                       3000

 

 

<13:30 6교시>

 

 

 

 

--모든 직원의 급여와 평균 급여의 차이를 계산하시오.

 

 

와 미친 겁나 졸았음;;; ㅠㅠㅠㅠㅠ

졸다가 막판에 들은거 외래키 제약 조건 때문에 테이블 사이의 관계를 어떻게 할건지에 관련 관리 방법메모 한거 일단 대충 정리...

 

■ 관계형 DataBase

Table을 만들 때는 관계 설정을 해줘서 테이블 간에 서로 무결성이 없도록 관리해준다.

관계형 DATABASE의 외래키 제약 조건이 테이블 사이의 관계를 어떻게 관리하게 되는지에 대해서 배울 것.

보통 테이블을 만들 때 관계 설정이 필요한데, 보통 부모 테이블이라는 녀석을 먼저 만들고 이거에 연결 지어지는 다른 테이블들에 외래키 제약 조건을 추가한다. 

외래키 제약 조건이 들어가면 부모 테이블은 자식 테이블 보다 먼저 삭제될 수 없다. 다만, CASCADE라는 함수로 강제 삭제가 가능하다.

 

 

 

 

<16:30 7교시>

 

테이블을 만들 때 CONSTRAINT 뒤에 오는 제약 조건의 명령을 입력하지 않으면 SQL이 무작위의 이름을 마음대로 배정해놓기 때문에 이를 사용하기가 불편해서 우리가 보고 싶은 이름으로 정의하는 것이 좋다.

외래키를 설정한 테이블 칼럼에서 외래키의 범위에서 벗어난 값을 입력하면 외래키 제약 조건 위배로 row 값이 입력되지 않는다.

SQL> --연습용 테이블 dept2와 emp2 테이블을 만들어보자
SQL> CREATE TABLE dept2(deptno NUMBER(2) CONSTRAINT dept2_deptno_pk PRIMARY KEY,
  2  dname VARCHAR2(12) CONSTRaiNT dept2_dname_nn not null,
  3  loc VARCHAR2(12) Constraint dept2_loc_ck Check(loc In('Seoul', 'Busan')));

Table created.

SQL> create table emp2(empno NUMBER(4) CONSTRAINT emp2_empno_pk PRIMARY KEY,
  2  ename VARCHAR2(12) Constraint emp2_ename_nn not null,
  3  deptno NUMBER(2) CONsTRAINT emp2_deptno_fk REFEReNCES dept2(deptno));

Table created.

SQL> insert into dept2(deptno, dname, loc) values (11, '마케팅', 'Seoul');

1 row created.

SQL> insert into dept2(deptno, dname, loc) values (12, '리서치', 'Seoul');

1 row created.

SQL> insert into emp2(empno, ename, deptno) values (1, '유재석', 11);

1 row created.

SQL> insert into emp2(empno,ename, deptno) values(2, '하석진', 12);

1 row created.

SQL> select * from dept2;

    DEPTNO DNAME                    LOC
---------- ------------------------ ------------------------
        11 마케팅                   Seoul
        12 리서치                   Seoul

SQL> select * from emp2;

     EMPNO ENAME                        DEPTNO
---------- ------------------------ ----------
         1 유재석                           11
         2 하석진                           12

 

■ 테이블 수준 제약 조건

이번에는 제약 조건을 다른 방식으로 부여하는 테이블을 만들어보기로 함.

그동안은 칼럼을 정의하고 그 옆에 선언하는 제약 조건을 칼럼 수준(COLUMN LEVEL)에서 정의했던거인데

이번에는 테이블을 정의할 때 선언하는 제약조건(테이블 수준 제약 조건, TABLE LEVEL 제약 조건)을 배울 것.

제약 조건의 이름과 종류와 함께 칼럼명이 들어가야 함

CONSTRAINT emp2_empno_pk PRIMARY KEY(empno),

CONSTRAINT emp2_deptno_fk FOREIGN KEY(deptno) REFERENCES dept2(deptno))

단, NOT NULL 제약 조건은 테이블 수준 제약 조건으로 들어가지 못해서 칼럼 수준 제약 조건으로 정의한다.

 

SQL> CREATE TABLE dept2(deptno NUMBER(2) CONSTRAINT dept2_deptno_pk PRIMARY KEY,
  2  dname VARCHAR2(12) CONSTRaiNT dept2_dname_nn not null,
  3  loc VARCHAR2(12) Constraint dept2_loc_ck Check(loc In('Seoul', 'Busan')));

Table created.

SQL> create table emp2
  2  (empno NUMBER(4),
  3  ename VARCHAR2(12) constraint emp2_ename_nn NOt Null,
  4  deptno NUMBER(2),
  5  CONSTRAINT emp2_empno_pk PRIMARY KEY(empno),
  6  CONSTRAINT emp2_deptno_fk FOREIGN KEY(deptno) REFERENCES dept2(deptno))

CHECK 같은 명령어는 괄호 안에 있는 조건을 사용하여 TRUE이어야만 row가 입력된다.

 

 

 

■ 제약 조건 없이 테이블을 만들고, 따로 제약 조건을 부여하는 명령 : ALTER

보통 개발을 하게 되면 데이터가 거의 없는 상태에서 만들기 때문에 무엇에 제약 조건을 걸지가 명확하지 않다. 

그래서 날것의 테이블을 만들어 놓고 개발이 끝나고 데이터가 어느정도 수집되면서부터 제약 조건을 걸게 된다.

ALTER TABLE을 사용해 내용을 추가(ADD)하고, 이때의 문법은 테이블 수준의 제약 조건을 작성할 때와 똑같게 된다.

다만, NOT NULL은 독특한 애라 또 조금 다르게 추가(MODIFY)한다.

 

SQL> --연습용 테이블 dept2와 emp2 테이블을 만들어보자
SQL> CREATE TABLE dept2(deptno NUMBER(2) CONSTRAINT dept2_deptno_pk PRIMARY KEY,
  2  dname VARCHAR2(12) CONSTRaiNT dept2_dname_nn not null,
  3  loc VARCHAR2(12) Constraint dept2_loc_ck Check(loc In('Seoul', 'Busan')));

Table created.

SQL> create table emp2
  2  (empno NUMBER(4),
  3   ename VARCHAR2(12),
  4   deptno NUMBER(2)
  5  );

Table created.

SQL> ALTER TABLE emp2
  2  ADD constraint emp2_empno_pk primary key(empno);

Table altered.

SQL> ALTER TABLE emp2
  2  ADD constraint emp2_deptno_fk FOREIGN KEY(deptno) REFERENCES dept2(deptno);

Table altered.


SQL> ALTER TABLE emp2
  2  MODIFY ename Constraint emp2_ename_nn Not Null;

Table altered.

 

<17:30 8교시>

1. dept2 테이블과 emp2 테이블을 여러번 만들고 삭제하고를 반복해 보세요(제약 조건 활용!)

2. 만들어진 테이블에 row를 제약조건을 위배하지 않고 추가해보기.

3. 제약 조건을 위배하는 다양한 테스트 해보기.

 

 

<8교시 때 실습하면서 생각했던 것들>

• 수업 때 dept2를 부모 테이블로, emp2를 자식 테이블로 만들어서 거꾸로 만들어볼 생각을 했는데, dept2를 부모 테이블로 만드는 게 훨씬 나은 선택일 거 같은게, dept는 emp랑 다르게 들어가는 칼럼 정보도 일단 deptno, dname, loc 세 개 뿐이고, 회사나 유저 입장에서 웹브라우저로 받아서 저장하는 정보 중에 부서의 변경 내용보다 emp의 변경 내용이 훨씬 많을 거고. 그래서 dept를 부모 테이블로 만드는 게 이래저래 맞는 방법이긴 한듯.
• 표 정의할 때 VARCHAR2()를 자꾸 VARCHAR로 적음
• 오타 자주 냄(특히 CONSTRAINTS, REFERENCE, dept인데 dpet, dpte이런거 개많음, table도 tabel 이런거 등등)
• 괄호 위치, 콤마 위치, 작은따옴표인지 큰따옴표 인지 제대로 잘 하기...
• 각 명령어 어떻게 쓰는지 계속 연습하기.... 자꾸 명령어 빼먹고 오류내고 그래...

SQL> --삽질 코드 CONSTRAINT 사라짐...
SQL> create table dept3
  2  (deptno NUMBER(3) dept3_deptno_pk PRIMARY KEY,
  3  dname VARCHAR2(16) dept3_dname_nn NOT NULL,
  4  loc VARCHAR2(16) dept3_loc_ck CHECK(loc in('seoul', 'los angeles', 'Paris')));
(deptno NUMBER(3) dept3_deptno_pk PRIMARY KEY,
                  *
ERROR at line 2:
ORA-00907: missing right parenthesis

SQL> --제약 조건 부모 테이블 만들었다
SQL> create table dept3
  2  (deptno NUMBER(3) constraint dept3_deptno_pk PRIMARY KEY,
  3  dname VARCHAR2(16) constraint dept3_dname_nn NOT NULL,
  4  loc VARCHAR2(16) constraint dept3_loc_ck CHECK(loc in('seoul', 'los angeles', 'Paris')));

Table created.

SQL>--empno number를 5로 해서 틀린줄 알았는데 constriant 때문이었어서 아예 지우고 다시 해봐야지 -> 해봤더니 ㅇㅇ NUMBER안의 문자열 길이는 문제되지 않음.
SQL> create table emp3
  2  (empno NUMBER(5) contraint emp3_empno_pk PRIMARY KEY,
  3  ename VARCHAR2(16) constraint emp3_ename_nn NOT NULL,
  4  deptno NUMBER(5) constraint emp3_deptno REFERENCE dept3(deptno));
(empno NUMBER(5) contraint emp3_empno_pk PRIMARY KEY,
                 *
ERROR at line 2:
ORA-00907: missing right parenthesis


SQL> create table emp3
  2  (empno NUMBER(5) contraint emp3_empno_pk PRIMARY KEY,
  3  ename VARCHAR2(16) constraint emp3_ename_nn NOT NULL,
  4  deptno NUMBER(5) constraint emp3_deptno REFERENCES dept3(deptno));
(empno NUMBER(5) contraint emp3_empno_pk PRIMARY KEY,
                 *
ERROR at line 2:
ORA-00907: missing right parenthesis


SQL> create table emp3
  2  (empno NUMBER(3) contraint emp3_empno_pk PRIMARY KEY,
  3  ename VARCHAR2(16) constraint emp3_ename_nn NOT NULL,
  4  deptno NUMBER(5) constraint emp3_deptno REFERENCES dept3(deptno));
(empno NUMBER(3) contraint emp3_empno_pk PRIMARY KEY,
                 *
ERROR at line 2:
ORA-00907: missing right parenthesis


SQL> create table emp3
  2  (empno NUMBER(3) constraint emp3_empno_pk PRIMARY KEY,
  3  ename VARCHAR2(16) constraint emp3_ename_nn NOT NULL,
  4  deptno NUMBER(5) constraint emp3_deptno REFERENCES dept3(deptno));

Table created.

 

 

<오늘 수업 마무리 하기>

중간에 나도 모르게 빠져들 듯이 졸고 있는 바람에 놓친 부분 선생님께서 차근차근 다시 해주셔서 간신히 수습한 것 같고

일단 SELECT 문 어려운 문제들(예제 8,9,10) 같은거는 우리가 익숙해지려고 연습하는 차원에서 한거지 실제 개발 환경에서 그렇게까지 어려운건 나올일 없다고 하시는데 아니 그래도 나는 정복하고 싶긴하다고? ㅋㅋㅋㅋㅋ ㅠㅠ

 

어제 너무 개나대서 오늘 피곤해져서 열심히 못한거 같으니까 오늘의 겸손함을 좀 오래 가져가려고 노력하면서 피로도 풀어야겠다 ㅠ

챗 지피티가 내가 졸린 이유가 오히려 커피를 너무 많이 마셔서 일 수도 있다고 했다! ㅠ

그렇다면 커피를 줄이긴 해야하는데 아득하긴 하다.

아침 한잔만 마시는 정도로 해야 할거 같긴한데 ㅠ

지금 아침 먹고 한잔, 지하철 내려서 학원 오면서 오전에 한잔 점심 먹구 오후에 한잔 최소 세 잔에 요 며칠은 오후에도 맛있다고 미쳐서 커피 마셨던 기억이..... 아니 커피 너무 맛있어 ㄹㅇ..

커피 줄이자.... 흑흑.. ㅠㅠ