8/3 COMMENT, WHERE, ROWNUM, Database구성, DUAL 테이블, 비교연산자, 논리연산자, SQL연산자, NULL, Primary key 특징, NVL, 집합 연산자, UPDATE, DELETE

1. 프로젝트 할 때 comment 작성하는 것 필수임. 한눈에 알아볼 수 있기 때문.

comment 변경하는 법 ->

   COMMENT ON COLUMN "emp"."hiredate" IS '입사일자';

출력 결과

EMP 테이블의 HIREDATE 변경되어 있음.

 

*오라클에서 값을 넣을 때는 작은따옴표(' ')로 입력해야 함.

별명은 큰따옴표(" ") 사용 가능

 

2.  

   INSERT INTO ex_type Values('sql2','sql2');

실행 결과 : ex_type 테이블

 

3.

   SELECT * FROM ex_type Where c ='sql';

 

(where c ='sql') 조건에 해당하는 값만 보여주기

실행 결과 

 

4. WHERE은 조건문 달 때 사용

 1)  c와 sql이 같은 것 출력 : 'sql'이 char 타입으로 내부변환되어 char과 char간의 비교 결과 같음

    SELECT * FROM ex_type WHERE c='sql';

 

 2)  v와 sql이 같은 것 출력 : 'sql'이 varchar2타입으로 내부변환되어 varchar2과 varchar2간의 비교 결과 같음

    SELECT * FROM ex_type WHERE v='sql';

 

 3)  v와 c가 같은 것 출력 : char과 varchar2의 길이가 다르므로 비교 결과 다름. (중요! 면접에 나오는 내용)

    SELECT * FROM ex_type WHERE c=v;

 

출력 결과 1) 2)

출력결과 3) : 해당하는 값이 없다.

*가로줄이 row 세로줄이 column

 

[ROWID 데이터타입]

- 테이블에서 행의 위치를 지정하는 논리적인 주소값

- 데이타베이스 전체에서 중복되지 않는 유일한 값

- 특정 레코드를 랜덤 액세스 하기 위한 가장 빠른 방법

 

[ROWNUM]

Where 절의 ROWNUM에서 = 조건은 무조건 1부터 시작해야 한다 (중요함)

ROWNUM은 순서를 지정해주는 것이 아니라 갯수를 지정하는 목적이기 때문.

JSP의 페이징 작업에서 많이 쓰임.

 

 

개발자가 SQL(DML)로 개발을 하면 내부적으로 Parsing을 함. 이것이 parser.

*Parsing : 컴퓨터에서 컴파일러 또는 번역기가 원시 부호를 기계어로 번역하는 과정의 한 단계로, 각 문장의 문법적인 구성 또는 구문을 분석하는 과정. 

DDL을 통해서 테이블과 인덱스가 만들어지면 시스템 카탈로그(데이타사전)로 들어감.

시스템 카탈로그 : 스키마정보와 인덱스 정보가 들어가있음.

Optimizer : 어떻게 실행시켜야 최적의 결론에 도달할 것인가를 시스템 카탈로그를 참조하여 계산함.

 

 

DUAL테이블은 데이터 딕셔너리와 함께 Oracle에 의해 자동으로 생성되는 테이블 입니다.

- DUAL테이블은 사용자 SYS의 스키마에 있지만 모든 사용자는 DUAL이라는 이름으로 엑세스 할 수 있습니다.

- DUAL테이블은 VARCHAR2(1)으로 정의된 DUMMY라는 하나의 열이 있으며 값을 가지는 하나의 행도 포함되어 있습니다.

- DUAL테이블은 사용자가 계산이나 사용자 함수등을 실행하고자 할 경우에 유용 합니다.

 

5. 오늘 날짜 구하기

COMMENT ON COLUMN emp.hiredate IS '입사일자';
SELECT sysdate FROM dept;

출력 결과 :

문제점 : Table의 row 갯수만큼 나옴

문제점 해결하기 위해 dual을 사용

SELECT sysdate From dual;

출력 결과

 

6. 과제 : 학생 테이블에서 1학년 학생만 검색하여 학번, 이름, 학과 번호를 출력

SELECT studno, name, deptno FROM student WHERE grade = 1;

출력 결과

*vachar인데 1에 작은따옴표를 쓰지 않아도 실행되는 이유 : 최신버전에서 알아서 컴버터를 넘버로 해줌. 옵티마이저가 최적화로 실행할 수 있게 해 줌. 이것을 실행계획(execute plan)이라고 한다.

 

 

 

 

7. 과제 : 학생 테이블에서 몸무게가 70kg 이하인 학생만 검색하여 학번, 이름, 학년, 학과번호, 몸무게를 출력하여라

SELECT studno, name, grade, deptno, weight FROM student WHERE weight<=70;

출력 결과

 

 

중요 별 3개, 현장에서 많이 씀.

 

[NULL 개념]

[pk특징] 외우기. 면접 단골 질문. 중요

1) Unique : 유일한 값이어서 구별할 수 있어야 한다.

2) not null : null값이면 안된다.

3) 최소성 : 한 테이블에 pk는 중복해서 잡지 말고 최소한으로만 잡아야 한다. 8. 학생 테이블에서 1학년 이면서 몸무게가

 

 

8. 학생 테이블에서 1학년 이면서 몸무게가 70kg 이상인 학생만 검색하여 이름, 학년, 몸무게, 학과번호를 출력

SELECT name, grade, weight, deptno FROM student WHERE grade=1 AND weight>=70;

출력 결과

9. 학생 테이블에서 1학년이거나 몸무게가 70kg 이상인 학생만 검색하여 이름, 학년, 몸무게, 학과번호를 출력

SELECT name, grade, weight, deptno FROM student WHERE grade=1 OR weight>=70;

출력 결과

 

10. 학생 테이블에서 학과번호가 ‘101’이 아닌 학생의 학번과 이름과 학과번호를 출력

--두 방식으로 작성 가능
SELECT name, deptno FROM student WHERE  NOT deptno = '101';
SELECT name, deptno FROM student WHERE  deptno != '101';

출력 결과

 

11. BETWEEN 연산자를 사용하여 몸무게가 50kg에서 70kg 사이인 학생의 학번, 이름, 몸무게를 출력

SELECT deptno, name, weight FROM student WHERE weight BETWEEN 50 AND 70;

출력 결과

 

12. 학생테이블에서 81년에서 83년도에 태어난 학생의 이름과 생년월일을 출력
계획설명(옵티마이저)들어가면 string을 썼는데도 자동으로 처리된 것을 볼 수 있음.

SELECT name, birthdate FROM student WHERE birthdate BETWEEN '81/01/01' AND '83/12/31';

출력 결과

 

13. OR 연산자 / IN 연산자를 사용하여 101번 학과와102번 학과 학생의 이름, 학년, 학과번호를 출력

--두 방식으로 작성 가능
SELECT name, studno, deptno FROM student WHERE deptno = 101 OR deptno = 102;
-- IN연산자가 더 편하다.
SELECT name, studno, deptno FROM student WHERE deptno IN (101, 102);

출력 결과

 

14. Like 연산자 : 학생 테이블에서 성이 ‘김’씨인 학생의 이름, 학년, 학과 번호를 출력

SELECT name, grade, deptno FROM student WHERE name LIKE '김%';

출력 결과

 

15. 학생 테이블에서 이름이 3글자, 성은 ‘김’씨고 마지막 글자가 ‘영’으로 끝나는 학생의 이름, 학년, 학과 번호를 출력

SELECT name, grade, deptno FROM student WHERE name LIKE '김_영';

출력 결과

 

 

[NULL 개념]

 

16. (sal+comm은 총 급여) 이렇게 하면 comm이 null인 경우 합도 null로 나오기 때문에 문제가 됨.

SELECT empno, sal, comm, sal+comm FROM emp;

출력 결과

해결책 : NVL 또는 NVL2 사용. null이 있으면 그 값을 0으로 바꿔줌. (중요, 자주 씀)

SELECT empno, sal, comm, sal+NVL(comm,0) FROM emp;

출력 결과

 

17. COMM이 NULL인 것 비교

SELECT * FROM emp WHERE comm IS NULL;

출력 결과

 

18. COMM이 NULL이 아닌 것 비교. 0은 NULL이 아니다.

SELECT * FROM emp WHERE comm IS NOT NULL;

 

19. 문제 : 교수 테이블에서 보직수당이 없는 교수의 이름, 직급, 보직수당을 출력

SELECT name, position, comm FROM professor WHERE comm IS NULL;

출력 결과

 

20. 문제 : 교수 테이블에서 급여에 보직수당을 더한 값은 null값을 해결하여 sal_com이라는 별명으로 출력하여라

SELECT name, position, sal, comm, sal+NVL(comm,0) AS sal_com FROM professor;

출력 결과

 

21. 102번 학과의 학생 중에서 1학년 또는 4학년 학생의 이름, 학년, 학과 번호를 출력

SELECT name, grade, deptno FROM student WHERE deptno='102' AND (grade In ('1', '4'));
SELECT name, grade, deptno FROM student WHERE deptno='102' AND (grade = '1' OR grade = '4');

출력 결과

 

22. 1학년 이면서 몸무게가 70kg 이상인 학생의 집합을 테이블 stud_heavy로 만듦. 

*이렇게 만들면 기본적인 구조는 그대로이지만 제약조건은 빠진다.

CREATE TABLE stud_heavy AS
SELECT * FROM student WHERE grade='1' AND weight>='70';

출력 결과

23. 1학년 이면서 101번 학과에 소속된 학생(stud_101)

CREATE TABLE stud_101 AS
SELECT * FROM student WHERE grade='1' AND deptno='101';

출력 결과

 

[집합 연산자]

 

24. Union : 중복을 제외한 합집합

SELECT studno, name FROM stud_heavy 
UNION SELECT studno, name FROM stud_101;

출력 결과

 

25.UNION ALL : 중복을 허용한 합집합

SELECT studno, name FROM stud_heavy
UNION ALL SELECT studno, name FROM stud_101;

출력 결과

26. UNION : COLUMN이 다를 때는 오류 발생(UNION할 땐 꼭 타입과 컬럼의 수를 맞춰야 함)

SELECT studno, name, userid FROM stud_heavy 
UNION SELECT studno, name FROM stud_101;

질의 결과

 

27. INTERSECT : 교집합

SELECT studno, name FROM stud_heavy INTERSECT SELECT studno, name FROM stud_101;

출력 결과

28. MINUS  : 차집합 (박동진, 서재진) - (박미경 서재진) = 박동진

SELECT studno, name FROM stud_heavy MINUS SELECT studno, name FROM stud_101;

출력 결과

 

29. update

INSERT INTO emp VALUES 
(1000, '공현지', 'CLERK', 7902, to_date('03-08-2022', 'dd-mm-yyyy'), 2800, NULL, 20);

출력 결과

UPDATE emp SET job = 'MANAGER', deptno =10 WHERE empno = 1000;

출력 결과(변경 후)

30. delete

삭제할 때는 primary key를 삭제해야 안전하다..

INSERT INTO emp VALUES 
(2000, 'test', 'CLERK', 7902, to_date('03-08-2022', 'dd-mm-yyyy'), 2800, NULL, 20);

출력 결과

DELETE emp WHERE empno = 2000;

출력 결과(변경 후) : 없어져있음.