귤귤의 대모험

CREATE TABLE PLAYER (
       PLAYER_ID                CHAR(7)             NOT NULL,
       PLAYER_NAME        VARCHAR2(20)  NOT NULL,
       TEAM_ID                   CHAR(3)             NOT NULL,
       E_PLAYER_NAME   VARCHAR2(40),
       NICKNAME               VARCHAR2(30),
       JOIN_YYYY               CHAR(4),  
       POSITION                  VARCHAR2(10),
       BACK_NO                 NUMBER(2),
       NATION                     VARCHAR2(20),
       BIRTH_DATE            DATE,
       SOLAR                      CHAR(1),
       HEIGHT                    NUMBER(3),

      CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),
      CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)
    
);

UNIQUE 조건

데이터의 유일성을 보장(중복되는 데이터가 존재할 수 없다)되고, 자동으로 인덱스가 생성된다..

-- deptno 컬럼에 UNIQUE 제약조건 생성

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_uk_deptno UNIQUE (deptno);

-- 제약 조건의 삭제

SQL> ALTER TABLE emp2 DROP CONSTRAINT emp2_uk_deptno;

CHECK 조건

컬럼의 값을 어떤 특정 범위로 제한할 수 있다.

-- comm 컬럼에 1에서 100까지의 값만을 가질수 있는 체크조건 생성

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_ck_comm CHECK (comm >= 1 AND comm <= 100);

-- 제약 조건의 삭제

SQL> ALTER TABLE emp2 DROP CONSTRAINT emp2_ck_comm;

-- 10000,20000,30000,40000,50000의 값만을 가질수 있는 체크조건 생성

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_ck_comm CHECK comm IN (10000,20000,30000,40000,50000);

DEFAULT(컬럼 기본값) 지정

데이터를 입력하지 않아도 지정된 값이 기본으로 입력 된다.

-- hiredate 컬럼에 값을 입력하지 않아도 오늘 날짜가 입력된다.

SQL> CREATE TABLE emp4(

... (컬럼생략) ...,

hiredate DATE DEFAULT SYSDATE );

PRIMARY KEY 지정

- 기본키는 UNIQUE 와 NOT NULL의 결합과 같다.

- 기본키는 그 데이터 행을 대표하는 컬럼으로서의 역할을 수행하여 다른 테이블에서 외래키들이 참조할 수 있는 키로서의 자격을 가지고 있다. 이를 참조 무결성이라 한다.

- UNIQUE 조건과 마찬가지로 기본키를 정의하면 자동으로 인덱스를 생성하며, 그 이름은 기본 키 제약 조건의 이름과 같다.

- INDEX: 검색 키로서 검색 속도를 향상 시킨다.(UNIQUE,PRIMARY KEY 생성시 자동적으로 생긴다.)

-- PRIMARY KEY 생성 예제

SQL> CREATE TABLE emp5( empno NUMBER CONSTRAINT emp5_pk_empno PRIMARY KEY );

-- ALTER TABLE 명령어로 PRIMARY KEY 생성 예제

ALTER TABLE emp2 ADD CONSTRAINT emp2_pk_empno PRIMARY KEY (empno) ;

FOREIGN KEY(외래 키)지정

- 기본키를 참조하는 컬럼 또는 컬럼들의 집합이다.

- 외래키를 가지는 컬럼의 데이터 형은 외래키가 참조하는 기본키의 컬럼과 데이터형과 일치해야 한다. 이를 어기면 참조무결성 제약에의해 테이블을 생성할수 없다.

- 외래키에 의해 참조되고 있는 기본 키는 삭제 할 수 없다.

- ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 그 기본키가 삭제 될 때 같이 삭제 된다.

-- emp 테이블의 deptno 컬럼이 dept 테이블에 deptno 컬럼을 참조하는 외래키 생성

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno);

시퀀스란?

• 유일(UNIQUE)한 값을 생성해주는 오라클 객체이다.
• 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다.
• 보통 PRIMARY KEY 값을 생성하기 위해 사용 한다.
• 메모리에 Cache되었을 때 시퀀스값의 액세스 효율이 증가한다.
• 시퀀스는 테이블과는 독립적으로 저장되고 생성된다.

시퀀스 생성 Syntax

CREATE SEQUENCE sequence_name

• [START WITH n] : 시퀀스의 시작 값을 지정한다. n을 1로 지정하면 1부처 순차적으로 시퀀스번호가 증가한다
• [INCREMENT BY n] : 시퀀스의 증가 값을 지정한다. n을 2로 하면 2씩 증가한다. START WITH를 1로, INCREMENT BY를 2로 설정하면 1, 3, 5, 7,.. 이렇게 시퀀스 번호가 증가한다.
• [MAXVALUE n | NOMAXVALUE] : 시퀀스 최대값 | 최대값 없음
• [MINVALUE n | NOMINVALUE] : 시퀀스 최소값 | 최소값 없음
• [CYCLE | NOCYCLE] : 최대값 도달시 순환 여부
• [CACHE | NOCACHE] : CACHE 여부, 원하는 숫자만큼 미리 만들어 Shared Pool의 Library Cache에 상주시킨다.

1. 증감 숫자가 최대 값에 도달 하면 생성을 중단 할 지 ( NOCYCLE )
2. 처음 부터 다시 생성 할지 ( CYCLE )
3. 메모리에 시퀀스 값을 미리 할당 ( CACHE )
4. 혹은 할당 하지 않음 ( NOCACHE )

시퀀스 수정

START WITH 값을 제외하고 ALTER SEQUENCE 명령어로 수정 할 수 있다.

시퀀스 수정 Syntax

ALTER SEQUENCE sequence_name

[INCREMENT BY n]

[MAXVALUE n | NOMAXVALUE]

[MINVALUE n | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE | NOCACHE]

시퀀스의 사용예

8000부터 시작하는 empno를 자동 증가 시퀀스를 만들어 보자

CREATE SEQUENCE seq_empno

START WITH 8000

INCREMENT BY 1

CACHE 20;

시퀀스가 정상적으로 생성되었는지 조회해 보자

SELECT seq_empno.NEXTVAL FROM DUAL;

SELECT seq_empno.NEXTVAL FROM DUAL;

SELECT seq_empno.CURRVAL FROM DUAL;

시퀀스를 이용해서 데이터를 등록해 보자

INSERT 시 시퀀스 사용

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)

              VALUES(seq_empno.NEXTVAL,'TIGER', 'MANAGER', 7839, SYSDATE, 3000, null, 20);

-- SELECT 절에서 시퀀스 사용

CREATE TABLE emp2

AS

SELECT seq_empno.NEXTVAL as empno, ename,

job, mgr, hiredate, sal, comm, deptno

FROM emp;

-- 시퀀스가 정상적으로 반영되었는지 조회해 보자

SELECT * FROM emp;

시퀀스 수정

-- seq_empno 시퀀스의 증과값과 Cache 사이즈를 변경하는 예이다.

ALTER SEQUENCE seq_empno

INCREMENT BY 10

CACHE 40;

-- 시퀀스가 정상적으로 변경되었는지 조회해 보자

SELECT seq_empno.NEXTVAL FROM DUAL;

SELECT seq_empno.NEXTVAL FROM DUAL;

SELECT seq_empno.NEXTVAL FROM DUAL;

SELECT seq_empno.CURRVAL FROM DUAL;

시퀀스 삭제

DROP SEQUENCE 명령으로 시퀀스를 삭제 할 수 있다.

-- seq_empno 시퀀스를 삭제한다.

DROP SEQUENCE seq_empno;

인덱스

인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조이다.

• 자동 인덱스 : 프라이머리 키 또는 UINQUE 제한 규칙에 의해 자동적으로 생성되는 인덱스.
• 수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스

인덱스를 생성하는 것이 좋은 컬럼

• WHERE절이나 join조건 안에서 자주 사용되는 컬럼
• null 값이 많이 포함되어 있는 컬럼
• WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들 다음과 같은 경우에는 인덱스 생성이 불 필요 하다.
• (테이블이 작을 때, 테이블이 자주 갱신될 때)

오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있다.

B-tree 인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 낸다.

이 알고리즘 원리는 주어진 값을 리스트의 중간점에 있는 값과 비교한다.

만약 그 값이 더 크면 리스트의 아래쪽 반을 버린다. 만약 그 값이 더 작다면 위쪽 반을 버린다.

하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도 반복한다.

비트맵 인덱스

• 비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 사용하는 것이 좋다.(ex 남,여의 값을 가지는 성별 컬럼)
• 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킨다.
• 테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있다.
• SQL> CREATE BITMAP INDEX emp_deptno_indx ON emp(deptno);

UNIQUE 인덱스

• UNIQUE 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있다.
• 프라이머리키 와 UNIQUE 제약 조건시 생성되는 인덱스는 UNIQUE 인덱스이다.
• SQL> CREATE UNIQUE INDEX emp_ename_indx ON emp(ename);

NON-UNIQUE 인덱스

• NON-UNIQUE 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있다.
• SQL> CREATE INDEX dept_dname_indx ON dept(dname);

결합 인덱스

• 복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수 있는 최대 컬럼값은 16개 이다
• SQL> CREATE UNIQUE INDEX emp_empno_ename_indx ON emp(empno, ename);

인덱스의 삭제

• 인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지 않는다.
• 인덱스를 삭제하려면 인덱스의 소유자 이거나 DROP ANY INDEX권한을 가지고 있어야 한다.
• 인덱스는 ALTER를 할 수 없다.
• SQL> DROP INDEX emp_empno_ename_indx;

1. 인덱스 컬럼을 변형하여 비교할 때

• BAD - WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '19980518';
• NOT BAD - WHERE HIREDATE = TO_DATE('19980518')

비교하는 인덱스 컬럼의 형이나 값을 변경하면 발생합니다.이 경우에는 비교값을 변경해 주어야 인덱스를 사용하게 됩니다.

인덱스를 HIREDATE로 했을 때 인덱스를 타기위해서는 INDEX를 생성한것에 변형을 주어서는 안된다.

위 처럼 결과도 빠르게 나온다 그러나 중요한 점이 있다 결과가 같지 않을 수도 있다는 것이다

날짜 타입은 날짜와 시분초의 정보도 가지고 있다. 따라서 TO_DATE(‘19980518’)라는 말은 정확히 1998년5월18일 0시0분0초라는뜻이다.

그래서 우리가 원하는 1998년5월18일자와는 차이가 있다.

따라서 1998년5월18일 0시0분1초 ~ 23시59분59초까지의 데이터는 나오지 않게되는것이다.

이것은 튜닝할 때 유의할 점이다. 결과를 같게 유지해야하는것이다. 이 상황을 알고있다면 방법은 간단하다.

아래아 같이 고치면 빠른시간에 원하는 결과를 얻을 수 있을 것이다.

• VERY GOOD
• WHERE HIREDATE BETWEEN TO_DATE('19980518'||'00:00:00','YYYYMMDD HH24:MI:SS') AND TO_DATE('19980518'||'23:59:59','YYYYMMDD HH24:MI:SS')

• BAD - WHERE SALARY + 1000 > 100000;
• GOOD - WHERE SALARY > 100000 - 1000;

함수의한 변형이 아닌 간단한 연산에의한 변형의 경우도 마찬가지이다.

2. 비교 대상의 형이 달라서 내부적으로 형변환을 하는 경우

• BAD - WHERE EMP_ID = 200383;
• GOOD - WHERE EMP_ID = ‘200383’;

EMP_ID가 varchar라고 할 경우에 비교값이 숫자인 경우에 DB에서 자동으로 이를 숫자로 변경하고 비교하게 됩니다.

이 경우에 인덱스 컬럼에 변형이 일어났기 때문에 인덱스를 사용하지 못하게 됩니다.

3. NULL을 비교하는 경우

• BAD - WHERE JOB IS NULL;

일반적으로 Oracle을 기준으로 NULL은 인덱스 대상이 아니라고 합니다.

따라서, 이를 해결하기 위해서는 NULL을 쓰지 말고 다른 정해진 값을 이용해서 비교해야 합니다. 

따라서 위와 같은 경우 반드시 index를 타려거든

job컬럼을 NOT NULL로 설정하고 NULL대신 특정값 (예를 들면 : ‘NOT ASSIGN’ ) 으로 설정하고 

QUERY를 아래와 같이 수정한다면 인덱스를 탈수 있을 것이다.

• GOOD 
• SELECT LAST_NAME,FIRST_NAME FROM EMPLOYEES WHERE JOB = ‘NOT ASSIGN’;

4. 부정형 조건인 경우

• BAD - WHERE JOB NOT IN ( 'INSTRUCTOR', 'STAFF');

부정형 역시 인덱스를 사용하지 못하는 대표적인 조건 쿼리 입니다.

일반적인 INDEX가 =이나 <, > , BETWEEN조건에 만 인덱스를 탈수 있고 부정형으로 비교했을때는 인덱스를 탈수 없기때문이다

아닌 놈을 찾으려면 전체를 뒤지는 수 밖에요. 이를 피하기 위한 근본적인 DB 모델링이 중요합니다.

1. '정남일' 선수가 소속된 팀의 선수들에 대한 정보를 출력하시오

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버

FROM PLAYER

WHERE TEAM_ID = (SELECT TEAM_ID

                                                                        FROM PLAYER

                                                                        WHERE PLAYER_NAME = '정남일')

2. 선수들 중에서 키가 평균 이하인 선수들의 정보를 출력하시오

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버

FROM PLAYER

WHERE HEIGHT <= (SELECT AVG(HEIGHT)

                                                                        FROM PLAYER) ORDER BY PLAYER_NAME;

3. 각 부서별 급여 평균보다 더 많이 받는 사원의 사원번호, 이름, 급여를 출력하세요.

SELECT empno,ename,sal

FROM EMP

WHERE sal > all (SELECT AVG(sal)

                                  FROM EMP

                                  GROUP BY deptno)

4. CHICAGO 지역에 근무하는 사원들 중 BLAKE이 직속상관인 사원들의 사원번호, 이름, 직무를 출력하세요.

SELECT e.empno, e.ename, e.job

FROM EMP e, DEPT d

WHERE e.DEPTNO = d.DEPTNO

AND e.mgr = (SELECT empno

                            FROM EMP

                            WHERE ename = 'BLAKE')

                            AND d.loc = 'CHICAGO';

5. 각 부서별로 최고급여를 받는 직원의 Dept_id, ID, Last_name, Salary를 출력하시오.

SQL> SELECT dept_id, id, last_name, salary

FROM s_emp

WHERE (dept_id,salary) IN (SELECT dept_id, max(salary)

                                                          FROM s_emp

                                                          GROUP BY dept_id);

6. '20120501' 부터 '20120502' 사이에 경기가 있는 경기장을 출력하시오

SELECT STADIUM_ID ID, STADIUM_NAME 경기장명

FROM STADIUM A

WHERE EXISTS (SELECT 1

                                   FROM SCHEDULE X

                                   WHERE X.STADIUM_ID = A.STADIUM_ID

                                   AND X.SCHE_DATE BETWEEN '20120501' AND '20120502')

7. 소속팀별 키가 가장 작은 사람들의 정보를 출력하시오

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER

WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT)

                                                                 FROM PLAYER

                                                                 GROUP BY TEAM_ID)

ORDER BY TEAM_ID, PLAYER_NAME;

8. 각 부서별로 최고급여를 받는 직원의 Dept_id, ID, Last_name, Salary를 출력하시오.

SELECT dept_id, id, last_name, salary

FROM s_emp

WHERE (dept_id,salary) IN (SELECT dept_id, max(salary)

                                                           FROM s_emp

                                                           GROUP BY dept_id);

9. K-리그 선수들 중에서 포지션이 미드필더(MF)인 선수들의 소속팀명 및 선수 정보를 출력하시오

SELECT T.TEAM_NAME 팀명, P.PLAYER_NAME 선수명, P.BACK_NO 백넘버

FROM (SELECT TEAM_ID, PLAYER_NAME, BACK_NO

               FROM PLAYER

               WHERE POSITION = 'MF') P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID ORDER BY 선수명;

10. K리그 선수 중 가장 키가 큰 선수 5명을 출력하시오

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM (SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT

                FROM PLAYER

                WHERE HEIGHT IS NOT NULL

                ORDER BY HEIGHT DESC)

WHERE ROWNUM <= 5;

11. 최근 입사한 5명의 사원을 출력하시오

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM (SELECT *

               FROM EMP

               ORDER BY HIREDATE DESC)

WHERE ROWNUM <= 5;

12. 사원의 급여를 내림차순으로 모든 정보를 출력하시오

SELECT * FROM EMP ORDER BY SAL DESC

13. 사원이름과 급여를 최고 높은 순으로 정렬하여 출력하고 순위를 표시하는 컬럼을 생성하여 출력하시오

SELECT ROWNUM RNUM, ENAME, SAL

FROM (SELECT * FROM EMP ORDER BY SAL DESC)

14. 급여를 많이 받는 6 ~ 10번째 사원을 출력

SELECT RNUM, ENAME, SAL

FROM (SELECT ROWNUM RNUM, ENAME, SAL             -- FROM 절의 INLINE VIEW ROWNUM에 대해 별칭 부여

                FROM (SELECT *

                                  FROM EMP

                                  ORDER BY SAL DESC))

WHERE RNUM BETWEEN 6 AND 10;

15. 삼성블루윙즈팀(K02)의 평균키보다 작은 팀의 이름과 해당 팀의 평균키를 출력하시오

SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키

FROM PLAYER P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID

GROUP BY P.TEAM_ID, T.TEAM_NAME

HAVING AVG(P.HEIGHT) < (SELECT AVG(HEIGHT)

                                                        FROM PLAYER WHERE TEAM_ID ='K02')

16. 사원이 속해있는 부서번호와 부서이름을 출력하시오, 단, 중복 값은 허용하지 않는다

위의 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우

추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를 체크해야 한다.

두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고 DISTINCT로 중복 제거를 한다.

SELECT DISTINCT d.deptno, d.dname

FROM dept d, emp e

WHERE d.deptno = e.deptno;

-- EXISTS를 사용하는 Subquery로 변경

-- 추출하고자 하는 테이블인 dept d을 FROM절에 놓고 emp테이블은 체크만 하기위해 EXISTS절에 위치시켰으며

이로 인해 수행속도가 대폭 감소하게 된다.

SELECT d.deptno, d.dname

FROM dept d

WHERE EXISTS (SELECT 1

                                  FROM emp e

                                  WHERE e.deptno = d.deptno);

테이블은 아래에 링크에 파일첨부가 되어있다https://tantangerine.tistory.com/17?category=379409

비교 연산자 & 논리연산자 활용 & SQL 연산자

1. 소속팀이 삼성블루윙즈팀인 선수들을 출력 하시오

SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER WHERE TEAM_ID = 'K02'

2. 포지션이 미드필더인 선수들을 출력하시오

SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER WHERE POSITION = 'MF'

3. 키가 170이상인 선수들을 출력하시오

SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER WHERE HEIGHT >= 170

4. 소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들을 출력하시오

SELECT * FROM PLAYER WHERE TEAM_ID IN ('K02','K07');

TEAM_ID ='K02' or K07'

5. 소속이 삼성블루윙즈이거나 전남드래곤즈이며,

포지션이 미드필더이면서 현재 키가 170이상 180이하인 선수들을 출력하시오

* SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER WHERE TEAM_ID = 'K02' OR TEAM_ID = 'K07'

AND POSITION = 'MF' AND HEIGHT >= 170 AND HEIGHT <= 180;

* SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER WHERE (TEAM_ID = 'K02' OR TEAM_ID = 'K07')

AND POSITION = 'MF' AND HEIGHT >= 170 AND HEIGHT <= 180 ( BETWEEN ~AND ~ 사용 가능 )

6. 소속이 삼성블루윙즈이거나 전남드래곤즈이며,

포지션이 미드필더가 아니면서 현재 키가 170이상 180이하인 선수들을 출력하

SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM

PLAYER WHERE TEAM_ID = 'K02' AND POSITION <> 'MF' AND HEIGHT BETWEEN 170 AND 180;

7. JOB이 MANGER이면서 20번 부서에 속하거나

JOB이 CLERK이면서 30번 부서에 속하는 사원의 정보를 출력하여라

///SELECT ENAME, JOB, DEPTNO FROM EMP WHERE JOB IN ('MANAGER','CLERK') AND DEPTNO IN (20,30);

SELECT ENAME, JOB, DEPTNO FROM EMP WHERE (JOB, DEPTNO) IN (('MANAGER',20),('CLERK',30));

8, 부서 번호가 20번이면서 급여가 1000이하 받는 사원과 전체 사원 중 급여 4000이상을 받는 사원을 출력하시오

SELECT * FROM emp WHERE (deptno= 20 AND sal< 1000) OR sal> 4000;

9. 부서번호가 20이번 이면서 급여가 1000이하이거나 4000이상인 사원을 출력 하시오

SELECT * FROM emp WHERE deptno= 20 AND (sal<= 1000 OR sal>= 4000);

칼럼 별칭 : (Alias) 12 * (salary + 100) AS ANNUAL_SALARY 12 * (salary + 100) “Annual Salary”

리터럴 문자 : 임의의 문자열 값을 의미

SQL> SELECT ‘Korea Fighting’ FROM emp; (14 번 출력)

SQL> SELECT ‘Korea Fighting’ FROM dual; (1 번 출력)

산술연산자 : (),*,/,+,-의 우선순위를 가진다. 새로운 의미 부여한 것이므로 적절한 ALIAS를 부여하는 것이 좋다

합성연산자 : 컬럼과 컬럼, 또는 컬럼과 다른 값을 연결, 합성연산의 결과로 새로운 문자값을 생성한다

#SQL> SELECT ename||ename AS FULLNAME FROM emp;

DISTINCT : SELECT 절에 나열된 값들 중 중복 값 제거 옵션 (중복 행 표시, 중복 행 제거, 다중 컬럼에 대한 중복 행 제거)

SELECT DISTINCT deptno FROM emp;

 SELECT DISTINCT job, deptno FROM emp; 두 예문은 결과 값에 대해 생각하기

연산자

- 비교연산자 - 문자형 타입은 인용부호(작은따옴표, 큰따옴표)로 비교처, 숫자형은 제외

SELECT ename, job, salFROM empWHERE job = ‘CLERK’ AND sal>= 1000;

SQL 비교 연산자

- BETWEEN ~ AND ~

SELECT * FROM employee WHERE salary BETWEEN 1000 AND 1500;

- IN (list) : 리스트에 있는 값 중에서 어느 하나라도 일치하면 된다

SELECT ename, deptno FROM emp WHERE deptno IN (10,20);

SELECT ename, deptno FROM emp WHERE deptno = 10 OR deptno = 20;

- LIKE : WHERE절에 사용한다

‘%‘ - 0개 이상의 어떤 문자를 의미한다, ‘_’ - 1개 단일 문자를 의미한다

- IS NULL : NULL을 포함한 산술 표현식 결과는 NULL이 된다 그리고

비교 연산자로 통해 비교할 수 없고 만약 비교한다면 FALSE의 값을 반환한다

논리연산자 우선순위 ( )괄호, NOT, AND, OR 순서대로 처리

- AND : 앞 뒤 모두 참이여야 참이된다

- OR : 둘 중 하나만 참이라도 참이다

- NOT : 뒤에 오는 조건에 반대되는 결과를 되돌려준다.

ORDER BY – ASC 올림차수(DEFAULT), DESC 내림차순

밑의 문제들은 SQL전문가 가이드 실습용 테이블을 활용하여 문제를 풀었다

GROUP BY, HAVING BY, WHERE 절 기본 개념은 아래의 페이지를 참조하자 

https://tantangerine.tistory.com/15

답은 흰색으로 처리하였습니다

1. 포지션별 인원수, 최대키, 최소키, 평균키를 출력하시오 이때 소수점 2번째 자리까지 구하시오

SELECT POSITION 포지션, COUNT(*) 인원수, COUNT(HEIGHT) 키대상,

MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키

FROM PLAYER GROUP BY POSITION

2. K-리그 선수들의 포지션별 평균키를 구하는데, 평균키가 180 센티미터 이상인 정보만 출력하시오

SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키

FROM PLAYER GROUP BY POSITION HAVING AVG(HEIGHT) >= 180;

3. K-리그의 선수들 중 삼성블루윙즈(K02)와 FC서울(K09)의 인원수를 출력하시오.

단, 가장 효율적인 자원 활용을 생각하시오

SELECT TEAM_ID 팀ID, COUNT(*) 인원수

FROM PLAYER

WHERE TEAM_ID IN ('K09', 'K02')

GROUP BY TEAM_ID;

SELECT TEAM_ID 팀ID, COUNT(*) 인원수

FROM PLAYER

GROUP BY TEAM_ID

HAVING TEAM_ID IN ('K09', 'K02');

4. 포지션별 평균 키만 출력하는데, 최대키가 190cm 이상인 선수를 포함하고 있는 포지션의 정보만 출력한다.

SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키

FROM PLAYER GROUP BY POSITION HAVING MAX(HEIGHT) >= 190;

< 서브쿼리 >

ㅇ 서브쿼리의 개념

ㅇ 서브쿼리의 위치에 따른 명칭

• SELECT문에 있는 서브쿼리 : 스칼라 서브쿼리
• FROM절에 있는 서브쿼리 : 인라인 뷰
• WHERE절에 있는 서브쿼리 : 서브쿼리

@인라인 뷰  임시적으로 생성되는 동적인 뷰이기 때문에 해당 정보가 저장되지않는다

ROWNUM과 INLINE VIEW의 특성을 이용하여 페이징 처리 등의 작업을 수행할 수 있다.

ㅇ 서브쿼리의 반환 값에 따른 서브쿼리 종류

- 단일 행 서브쿼리(Single-Row Subquery) 

서브 쿼리의 실행 결과가 항상 1건 이하인 것을 의미한다

단일 행 비교 연산자에는 =, <, <=, >, >=, <>이 있다

- 다중 행 서브쿼리(Multiple-Row Subquery)

    서브쿼리의 결과가 여러 행일 때를 의미한다

다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다

- IN(서브쿼리)

    서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미

- 비교연산자 ALL(서브쿼리) 

    메인쿼리는 서브쿼리 결과 값의 모든 값을 만족해야하므로 비교연산자 <,>올 때 다른값 반환한다

서브쿼리의 값이 1,2,3 일 때 비교연산자 > 일 때 서브쿼리는 3의 값으로 비교 된다

- 비교연산자 ANY(서브쿼리)

    메인쿼리는 서브쿼리의 결과 값들 중 어떤 값이라도 만족하면 되므로 비교연산자 <,>올 때 다른값 반환한다

서브쿼리의 값이 1,2,3 일 때 비교연산자 > 일 때 서브쿼리는 1의 값으로 비교 된다

- EXISTS (서브쿼리)

    EXISTS는 존재하는지 여부를 확인하여 정보를 조회하는 경우사용된다, 즉 필요한 정보는

메인쿼리에 정보가 존재하지만 필요한 조건에 해당하는 다른 테이블에 있을 때 사용한다

그래서 항상 연관 서브 쿼리로 사용

- 다중 컬럼 서브쿼리(Multi-Column Subquery) :

서브 쿼리의 실행결과로 여러 칼럼을 반환한다

메인 쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야한다

메인 쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다

다중 행 비교 연산과 같은 의미를 가지고 있으며 동시 비교가 가능하다

- HAVING 절 서브쿼리

 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용한다.

ㅇ 스칼라 서브쿼리(Scala Subquery)

- SELECT문에서 사용하는 서브쿼리로 1행만 반환

ㅇ 상호연관 서브쿼리(Correlated Subquery)

- 메인쿼리의 값을 서브쿼리가 사용하고, 서브쿼리의 값을 받아서 메인쿼리가 계산하는 구조의 쿼리

GROUP BY 절 특징

• GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT절에 집계함수를 사용.
• 집계함수의 통계정보는 NULL 값을 가진 행을 제외하고 수행한다.
• GROUP BY 절에서는 ALIAS 명을 사용할 수 없다.
• 집계함수는 WHERE 절에 올 수 없다.
• 집계함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다.
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.
• HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시 할 수있다.
• GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력.

HAVING 절 특징

• HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용된다는 점에서 차이가 있다
• HAVING 절과 GROUP 절의 순서를 바꾸어서 수행하더라도 문법 에러는 없다. 하지만 논리적으로 순서를 지키는 것을 권고
• HAVING 절은 SELECT 절에서 사용되지 않은 칼럼이나 집계함수가 아니더라도 소그룹 집계함수를 이용한 조건을 표시을 가능

데이터의 효율적인 자원 활용 (WHERE 절과 HAVING 절의 관계)

• GROUP BY 연산 전 WHERE 절에서 조건을 적용하여 필요한 데이터만 추출하여 GROUP BY 연산하는 방법
• GROUP BY 연산 후 HAVING 절에서 필요한 데이터만 필터링하는 방법
• 위의 두 가지 방법 중 WHERE 절에서 조건절을 적용하여 GROUP BY의 계산 대상을 줄이는 것이 효율적인 자원 사용이다

SELECT 문장 실행 순서

1. 발췌대상 테이블을 참조한다 (FROM)
2. 발췌대상 데이터가 아닌 것은 제거한다. (WHERE)
3. 행들을 소그룹화 한다 (GROUP BY)
4. 그룹핑된 값의 조건에 맞는 것만을 출력한다 (HAVING)
5. 데이터 값을 출력/계산한다 (SELECT)
6. 데이터를 정렬한다 (ORDER BY)

• 집계함수

- COUNT(표현식) : 표현식의 NULL 값인 것을 제외한 행의 수를 출력한다

- COUNT(*) : NULL값을 포함한 행의 수를 출력한다

– SUM(DISTINCT | ALL) : 표현식의 NULL 값인 것을 제외한 합계

– AVG(DISTINCT | ALL) : 표현식의 NULL 값인 것을 제외한 평균값

– MAX(DISTINCT | ALL | 표현식) : 최대값

– MIN(DISTINCT | ALL | 표현식) : 최소값

– STDDEV(DISTINCT | ALL) : 표준편차

– VARIANCE(DISTINCT | ALL) : 분산

• 공집합이란 NULL과는 다르게 조건에 맞는 데이터가 단한건도 없는 경우를 말한다
• 일반적으로 NULL과는 다른 데이터 값이다 

공집합은 어떠한 특정 테이블에 존재하지 않는 컬럼의 데이터 값을 출력할 때 발생한다

SELECT EMPNO FROM EMP WHERE ENAME ='KIM';

위의 쿼리문으로 공집합이 발생한다면 여기서 NVL 함수를 써도 결과는 변하지 않는다.

NVL 함수는 NULL 값을 변경할수 있는 함수이며, 데이터가 없는 공집합은 대상이 되지 않는다

그래서 다른 방법을 생각해야한다

집계함수는 결과가 공집합인 경우에도 NULL을 출력할 수 있다

그러므로 먼저 집계함수를 사용하여 NULL값으로 만든 다음 NVL함수를 사용해서 원하는 형태로 출력한다

SELECT NVL(MAX(EMPNO), '1111') EMPNO FROM EMP WHERE ENAME='KIM'

출력값 : 1111

• 조건함수

• DECODE (칼럼 또는 수식, 조건1, 값1, 조건2, 값2, 조건3, 값3,…….., 디폴트 값)

–IF –THEN –ELSE 문장과 같은 처리

–칼럼 또는 수식의 값이 조건1에 해당하면 값1을 리턴하고, 조건2에 해당하면 값2를 리턴한다.

–조건에 맞는 값이 없으면 디폴트 값을 리턴한다.

• NULL 함수

• NVL함수

– NVL( expr1, expr2 ) expr1 : NULL 값을 포함하는 값이나 표현식, expr2 : NULL 대신 사용할 값

# SELECT first_name, titile, salary * NVL(commission_pct,0)/100 COMM from s_emp;

- NULLIF( expr1, expr2 ) expr1 : 어떤 표현식에 해당하는 범주 expr2: expr1의 표현식과 같은 값을 비교할 표현식

#SELECT ENAME, EMPNO, MGR, NULLIF(MGR,7698) FROM EMP

#SELECT ENAME, EMPNO, MGR,

                      CASE MGR WHEN 7698

                                             THEN NULL

                                             ELSE MGR

                                             END AS MGR2 FROM EMP;

• 숫자함수

• ROUND (칼럼명or 숫자값, n) : 숫자값을소수점 n의 위치까지 반올림한다

. – ROUND(45.925, 2)=>45.93

• TRUNC (칼럼명or 숫자값, n) : 숫자값을소수점 n의 위치 아래까지 잘라낸다.

– TRUNC(45.925, 2)=>45.92

• FLOOR (칼럼명or 숫자값) : 숫자값보다 같거나 작은 최대의 정수를 리턴한다.

– FLOOR(45.925)=>45

• CEIL (칼럼명or 숫자값) : 숫자값보다 같거나 큰 최소의 정수를 리턴한다.

– CEIL(45.925)=>46

• MOD (칼럼명or 숫자값, 칼럼명or 숫자값) : 첫 번째 인수를 두 번째 인수로 나눈 나머지를 리턴한다.

– MOD(45, 2)=>1

• TO_CHAR (날짜 값, 형식) : 날짜 값을 형식에 맞춰 문자 스트링으로만들어 준다.

• TO_CHAR (숫자 값, 형식) : 숫자 값을 문자 스트링으로만들어 준다.

• TO_NUMBER (문자 스트링, 형식) : 문자 스트링을주어진 형식대로 해석하여 숫자 값으로 만들어 준다.

문자함수

LOWER (칼럼명or 스트링) : 알파벳 문자를 소문자로 바꿔준다.

–LOWER(‘Oracle DBMS’)=>oracle dbms

UPPER (칼럼명or 스트링) : 알파벳 문자를 대문자로 바꿔준다.

–UPPER(‘Oracle DBMS’)=>ORACLE DBMS

INITCAP (칼럼명or 스트링) : 알파벳 단어의 첫 글자는 대문자로, 나머지는 소문자로 바꿔준다.

SUBSTR (칼럼명or 스트링, m, n) : m의 위치에서 n 길이만큼 문자열을 잘라 리턴한다.

- SUBSTR(‘Oracle DBMS’, 2,4)=>racl

LENGTH (칼럼명or 스트링) : 문자열의 길이를 리턴한다.

– LENGTH(‘Oracle DBMS’)=>11

INSTR (칼럼명or 스트링, 문자) : 문자열 내에서 문자의 위치 값을 리턴한다.

– INSTR(‘Oracle DBMS’, ‘a’)=>3

LPAD (칼럼명or 스트링, m, 문자) : 문자열 전체 길이가 m이 되도록 문자열의 왼쪽에 문자를 채워준다.

– LPAD(‘Oracle DBMS’, 13, ‘x’)=>xxOracleDBMS

RPAD (칼럼명or 스트링, m, 문자) : 문자열 전체 길이가 m이 되도록 문자열의 오른쪽에 문자를 채워준다.

– RPAD(‘Oracle DBMS’, 13, ‘x’)=>Oracle DBMSxx

TO_CHAR (날짜 값, 형식)

- 날짜 값을 형식에 맞춰 문자 스트링으로만들어 준다.

TO_CHAR (숫자 값, 형식)

- 숫자 값을 문자 스트링으로만들어 준다.

TO_DATE (문자 스트링, 형식)

- 문자 스트링을주어진 형식대로 해석하여 날짜 값으로 만들어 준다.

TO_NUMBER (문자 스트링, 형식)

- 문자 스트링을주어진 형식대로 해석하여 숫자 값으로 만들어 준다.

import React from "react";

import { HashRouter, Route, Switch, Redirect} from "react-router-dom";

// components

import Layout from "./RouteMenunComponents/Layout/Layout";

// pages

import Error from "../pages/error";

import LoginContainer from "../pages/login/LoginContainer";

const App = (props) => {

  // global

  const { isAuthenticated } = props

  return (

    <HashRouter>

      <Switch>

        <Route 

          exact 

          path="/" 

          render = {() => <Redirect to="/app/dashboard" />} 

        />

        <Route

          exact

          path="/app"

          render={() => <Redirect to="/app/dashboard" />}

        />

        <PrivateRoute path="/app" component={Layout} />

        <PublicRoute path="/login" component={LoginContainer} />

        <Route component={Error} />

      </Switch>

    </HashRouter>

  );

•   ========================================================================

PrivateRoute, PublicRoute는 권한을 가지고 있는 변수 값이 true false를 판별하여 그에 따른 값을 치환한다

  function PrivateRoute({ component, ...rest }) {

    return (

      <Route

        {...rest}

        render={props =>

          isAuthenticated ? (

            React.createElement(component, props)

          ) : (

            <Redirect

              to={{

                pathname: "/login",

                state: {

                  from: props.location,

                  explain: '로그인 인증 실패'

                },

              }}

            />

          )

        }

      />

    );

  }

•   ========================================================================

 function PublicRoute({ component, ...rest }) {

    return (

      <Route

        {...rest}

        render={props =>

          isAuthenticated ? (

            <Redirect

              to={{

                pathname: "/",

              }}

            />

          ) : (

            React.createElement(component, props)

          )

        }

      />

    );

  }

}

export default App;

redux-thunk는 기존 redux의 createStore에 applyMiddleware를 추가하여  action함수에서 dispatch를 하게 해 준다 

그 결과 밑에 로직처럼 axios를 사용하여 결과 값을 dispatch 한다

export const fetchPosts = () => async dispatch => {

  const response = await jsonPlaceholder.get('/posts');

  dispatch({ type: 'FETCH_POSTS', payload: response.data });

};

export const fetchUser = id => async dispatch => {

  const response = await jsonPlaceholder.get(`/users/${id}`);

  dispatch({ type: 'FETCH_USER', payload: response.data });

};

Provider의 property 값에 applyMiddleware 추가하여 선언한다

import { Provider } from 'react-redux';

import { createStore, applyMiddleware } from 'redux';

import thunk from 'redux-thunk';

import App from './components/App';

import reducers from './reducers';

const store = createStore(reducers, applyMiddleware(thunk));

ReactDOM.render(

  <Provider store={store}>

    <App />

  </Provider>,

  document.querySelector('#root')

);

그 이외 연결 및 사용방법은 기존 redux활용과 같다

밑의 url을 참고하자

https://tantangerine.tistory.com/3

components/SongList.js

import React, { Component } from 'react';

import { connect } from 'react-redux';

import { selectSong } from '../actions';

class SongList extends Component {

  renderList() {

    return this.props.songs.map(song => {

      return (

        <div className="item" key={song.title}>

          <div className="right floated content">

            <button

              className="ui button primary"

              onClick={() => this.props.selectSong(song)}

            >                                                           // selectSong()은 mapDispatchToProps함수를 통해

                                                                              현 클래스의 property에 추가된다 

              Select

            </button>

          </div>

          <div className="content">{song.title}</div>

        </div>

      );

    });

  }

  render() {

    return <div className="ui divided list">{this.renderList()}</div>;

  }

}

const mapStateToProps = state => {

  return { songs: state.songs };

};

export default connect(

  mapStateToProps,

  { selectSong }

)(SongList);

actions/index.js

import _ from 'lodash';

import jsonPlaceholder from '../apis/jsonPlaceholder';

export const fetchPostsAndUsers = () => async (dispatch, getState) => {

  await dispatch(fetchPosts());

  _.chain(getState().posts)

    .map('userId')

    .uniq()

    .forEach(id => dispatch(fetchUser(id)))

    .value();

};

export const fetchPosts = () => async dispatch => {

  const response = await jsonPlaceholder.get('/posts');

  dispatch({ type: 'FETCH_POSTS', payload: response.data });

};

export const fetchUser = id => async dispatch => {

  const response = await jsonPlaceholder.get(`/users/${id}`);

  dispatch({ type: 'FETCH_USER', payload: response.data });

};

react와 redux를 연결하는 방법을 간략하게 알아보고 이때 사용되는 react-reudx와 reducer의 관계를 알아보자

* 일단 변수명은 지금 로직과 관련이 없다는 것만 알고 다양한 연결방법과 쓰임새만 알고 가자

reuducer는 combineReducers를 활용하여 redux를 생성하게 되고 reducer의 return 값들은 redux의 값으로 활용된다

reducer/index.js

import { combineReducers } from 'redux';

const songsReducer = () => {

  return [

    { title: 'No Scrubs', duration: '4:05' },

    { title: 'Macarena', duration: '2:30' },

    { title: 'All Star', duration: '3:15' },

    { title: 'I Want it That Way', duration: '1:45' }

  ];

};

• ===============================================================

const selectedSongReducer = (selectedSong = null, action) => {

  if (action.type === 'SONG_SELECTED') {

    return action.payload;

  }

  return selectedSong;

};

export default combineReducers({

  songs: songsReducer,

  selectedSong: selectedSongReducer

});

• ===============================================================

reducer와 redux를 선언이 끝나면 redux에 값을 치환하기 위해서는 action함수를 선언한다

그리고 사용될 클래스 내에서 react-redux로  mapDispatchToProps를 활용하여

action함수와 reducer 를 Dispatch한다 그렇게 되면 action함수의 return 값이 reducer의 매개변수로 치환되어

redux값이 생성된다

actions/index.js

export const selectSong = song => {

  return {

    type: 'SONG_SELECTED',

    payload: song

  };

};

• ===============================================================

redux를 사용하기 위해서는 react-redux 라이브러리를 사용하여 redux와 react를 연결해야 한다

는 state와 ownProps를 사용하여 비교 분석하여 값 치환이 가능하다

state는 action함수를 실행시켜 reducer와 바인딩된 값을 가지고 있으며 그것을 사용하기 위해서는

mapStateToProps함수를 사용하여 현재 클래스와 connect를 해주어야 한다

ownProps는 부모 클래스에서 상속받아온 Props값을 나타낸다

아래의 코드는 action함수가 실행되고 나서 다른 클래스에서 reducer의 return값을 사용할 때

mapStateToProps함수를 현재 클래스와 connect 하는 것만으로 사용할 수 있게 된다

mapStateToProps함수에서 user에 값을 치환하여 클래스 내부에서 props로 받아서 사용이 가능하다

selectSong은 mapDispatchToProps함수를 통해 ation함수를 현 클래스의 property에 추가되어

props로 받아서 사용할 수 있다

이때 부모 컴포넌트에게 받아온 props값과 redux의 state값이 동시 추가되는 것을 확인할 수 있다

components/UserHeader.js

import { connect } from 'react-redux';

class UserHeader extends React.Component{

  ... 이하생략 

소스가 궁금하다면 url 확인하자

https://tantangerine.tistory.com/5?category=379283

}

const mapStateToProps = (state, ownProps) => {  

  return { user: state.users.find(user => user.id === ownProps.userId) };

};

export default connect(mapStateToProps,{ selectSong })(UserHeader);

• ===============================================================

Provider로 App 컴포넌트를 감싸게 되면  App 컴포넌트는 물론 하위 컴포넌트까지

store에 저장된 값을 사용할 수 있다 이때 저장되는 값은 redux로 combineReducers함수를 사용한 값이 된다

index.js

import React from 'react';

import ReactDOM from 'react-dom';

import { Provider } from 'react-redux';

import { createStore } from 'redux';

import App from './components/App';

import reducers from './reducers';

ReactDOM.render(

  <Provider store={createStore(reducers)}> 

    <App/>

  </Provider>,

  document.querySelector('#root')

);

• ===============================================================