오늘의 코딩순서 + 포인트
1) SUB Query
: 하나의 SQL 명령문의 결과를 다른 SQL 명령문에 전달하기 위해 두 개 이상의 SQL 명령문을 하나의 SQL명령문으로 연결하여 처리하는 방법
예시)
1-1. 교수 테이블에서 ‘전은지’ 교수와 직급이 동일한 모든 교수의 이름 검색
SELECT position
FROM professor
WHERE name = '전은지'
;1-2. 교수 테이블의 직급 칼럼에서 1 에서 얻은 결과 값과 동일한 직급을 가진 교수 검색 명령문 실행
SELECT name , position
FROM professor
WHERE position = '전임강사'
;
1-3. 교수 테이블에서 ‘전은지’ 교수와 직급이 동일한 모든 교수의 이름 검색--> SUB Query
SELECT name , position
FROM professor
WHERE position = (
SELECT position
FROM professor
WHERE name = '전은지'
)
;
1-1. 단일행 서브쿼리
- 서브쿼리에서 단 하나의 행만을 검색하여 메인쿼리에 반환하는 질의문
- 메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교할 경우에는 반드시 단일행 비교 연산자 중 하나만 사용해야함
문제 1-1) 사용자 아이디가 ‘jun123’인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력하여라
SELECT studno , name , grade
FROM student
WHERE grade = (
SELECT userid
FROM student
WHERE userid = 'jun123'
)
;
문제 1-2) 101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의 이름, 학년 , 학과번호, 몸무게를 출력
-- 조건 : 학과별 올림차순 출력
SELECT name , grade , deptno , weight
FROM student
WHERE weight < (
SELECT AVG(weight)
FROM student
WHERE deptno = 101
)
ORDER BY deptno
;
문제 1-3) 20101번 학생과 학년이 같고, 키는 20101번 학생보다 큰 학생의 이름, 학년, 키, 학과명을 출력하여라
-- 조건 : 학과별 내림차순 출력 ===> 가장 기본적인 문제, Join을 섞어 사용하기
SELECT s.name , s.grade , s.height , d.dname
FROM student s , department d
WHERE s.deptno = d.deptno
AND s.grade = (
--1.같은 학년
SELECT grade
FROM student
WHERE studno = 20101
)
AND s.height > (
--2. 20101번 학생보다 큰 키
SELECT height
FROM student
WHERE studno = 20101
)
ORDER BY d.dname DESC
;
1-2. 다중행 서브쿼리
- 서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리
- 메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교할 경우에는 다중 행 비교 연산자 를 사용하여 비교
- 다중 행 비교 연산자 : IN, ANY, SOME, ALL, EXISTS
- IN: 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치하면 참, ‘=‘비교만 가능
- ANY, SOME: 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치하면 참
- ALL: 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 모든값이 일치하면 참
- EXISTS : 메인 쿼리의 비교 조건이 서브쿼리의 결과중에서 만족하는 값이 하나라도 존재하면 참
-- 1. IN 연산자를 이용한 다중행 서브쿼리
----이렇게 하면 오류남
SELECT name , grade , deptno
FROM student
WHERE deptno = ( -- '='은 하나의 값을 뜻함
SELECT deptno
FROM department
WHERE college = 100
)
;오류: single-row subquery returns more than one row
오류나는 이유: 단일행 하위 질의에 2개 이상의 행이 리턴되었기 때문
↓↓↓ 따라서 다중행 서브쿼리를 사용해야 한다
SELECT name , grade , deptno
FROM student
WHERE deptno IN (
SELECT deptno
FROM department
WHERE college = 100
)
;SELECT name , grade , deptno
FROM student
WHERE deptno IN (
101,102
)
;
-- 2. ANY 연산자를 이용한 다중행 서브쿼리
문제 1-4)모든 학생 중에서 4학년 학생 중에서 키가 제일 작은 학생보다 키가 큰 학생의 학번, 이름, 키를 출력하여라
SELECT studno , name , height
FROM student
WHERE height > ANY ( -- ANY: 어떤 것이라도 ==>'키가 제일 작은 학생보다'라는 조건에 부합함 ==> min을 생각하면 됨
SELECT height
FROM student
WHERE grade = '4'
)
;
--- 3. ALL 연산자를 이용한 다중행 서브쿼리 (위 문제 사용)
SELECT studno , name , height
FROM student
WHERE height > ALL ( -- ALL: max를 생각하면 됨
SELECT height
FROM student
WHERE grade = '4'
)
;
--- 4. ⭐EXISTS 연산자를 이용한 다중행 서브쿼리 : if문 같은것 (현장에서 많이 쓰임)
SELECT profno , name , sal , comm , position
FROM professor
WHERE EXISTS ( --EXISTS: 아래 조건에 해당하는 것이 1 Row라도 존재하면 위를 실행시켜라
SELECT position
FROM professor
WHERE comm IS NOT NULL
)
;SELECT profno , name , sal , comm , position
FROM professor
WHERE EXISTS (
SELECT position
FROM professor
-- WHERE deptno = 202 -- 202였다면 존재하니까 출력됨
WHERE deptno = 203 ---but, 203의 row값은 존재하지 않기 때문에 아예 출력값이 X
)
;
문제 1-5) 보직수당을 받는 교수가 한 명이라도 있으면 모든 교수의 교수 번호, 이름, 보직수당 그리고 급여와 보직수당의 합(NULL처리)을 출력
SELECT profno , name , sal , comm , sal + NVL(comm,0) sal_comm
FROM professor
WHERE EXISTS (
SELECT profno
FROM professor
WHERE comm IS NOT NULL
)
;1-3. 다중컬럼 서브쿼리
- 서브쿼리에서 여러 개의 칼럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리
- 메인쿼리의 조건절에서도 서브쿼리의 칼럼 수만큼 지정
- 종류
- 1) PAIRWISE : 칼럼을 쌍으로 묶어서(괄호로 묶어서) 동시에 비교하는 방식
- 2) UNPAIRWISE : 칼럼별로 나누어서 비교한 후, AND 연산을 하는 방식
- 둘의 결과는 완전히 다름
-- 1. PAIRWISE : 칼럼을 쌍으로 묶어서(괄호로 묶어서) 동시에 비교하는 방식
문제1-6) PAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하여라
SELECT name , grade , weight
FROM student
WHERE (grade,weight) IN (SELECT grade, MIN(weight)
FROM student
GROUP BY grade
)
;
-- 2) UNPAIRWISE : 칼럼별로 나누어서 비교한 후, AND 연산을 하는 방식
문제1-7) UNPAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력
SELECT name , grade , weight
FROM student
-- 1,2,3,4
WHERE grade IN (SELECT grade
FROM student
GROUP BY grade)
--52,42,70,72
AND weight IN (SELECT MIN(weight)
FROM student
GROUP BY grade)
;
-- 3) 상호연관 서브쿼리 ⭐⭐⭐(난이도↑↑) : 메인쿼리절과 서브쿼리간에 검색 결과를 교환하는 서브쿼리
문제1-8 ) 각 학과 학생의 평균 키보다 키가 큰 학생의 이름, 학과 번호, 키를 출력하여라
SELECT deptno , name , grade , height --실행순서1
-- 2번순서 실행 후 한번 더 마지막으로실행
FROM student s1
WHERE height > (SELECT AVG(height)
FROM student s2
-- WHERE s2.deptno = 101
-- 실행순서 2
WHERE s2.deptno = s1.deptno
)
ORDER BY deptno
;2) 테이블관리
1. 다중 행 입력
1. 생성된 TBL이용 신규 TBL 생성
CREATE Table dept_second
AS SELECT * FROM dept
;
2. TBL 가공 생성
CREATE Table emp20
AS SELECT empno , ename , sal*12 annsal
FROM emp
WHERE deptno = 20
;
3.TBL 구조만
CREATE Table dept30
AS SELECT deptno , dname
FROM dept
WHERE 0=1
-- 0=1: 윗줄까지만 쓰면 deptno,dname의 데이터값까지 가져오지만,
-- WHERE 0=1을 쓰면 구조만 가져오고 데이터값은 가져오지 않는다
;
4. Column 추가
ALTER TABLE dept30
ADD(birth Date)
;
INSERT INTO dept30 VALUES (10,'중앙학교',sysdate)
;
5. Column 변경
ALTER TABLE dept30
Modify dname varchar2(11) --cannot decrease column length because some value is too big
; --'중앙학교'는 12byte, 11보다 커서 크기 변경 불가
ALTER TABLE dept30
Modify dname varchar2(30)
;
6. Column 삭제
ALTER TABLE dept30
Drop Column dname
;
7. TBL명 변경
RENAME dept30 TO dept35;
8. TBL 제거
DROP Table dept35;
9. Truncate
TRUNCATE table dept_second;
+ DELETE, DROP, TRUNCATE 비교⭐⭐⭐⭐⭐
(DML,DDL은 https://mycodingdiary1219.tistory.com/28 참고)
| 비교항목 | DELETE | TRUNCATE | DROP |
| ⭐명령어 분류 | DML | DDL | DDL |
| 삭제범위 | ⭐행 데이터 일부 ~ 전체 | ⭐행 전체 + 인덱스 등 | ⭐행 전체+ 인덱스 + 테이블 전체 |
| 행 데이터(레코드) | 일부 ~ 전체삭제 | 전체 삭제 | 전체 삭제 |
| 데이터 저장공간 | 남겨져있음 | 삭제됨 | 삭제됨 |
| 인덱스 | 남겨져있음 | 삭제됨 | 삭제됨 |
| 테이블 컬럼, 제약조건 | 남겨져있음 | 남겨져있음 | 삭제됨 |
| 테이블 정의 | 남겨져있음 | 남겨져있음 | 삭제됨 |
| 작업속도 | 느림 | 빠름 | 빠름 |
| 복구가능성 | 있음 | 일부 가능 | 없음 |
- '삭제'라는 공통점을 갖지만, 삭제의 범주와 대상이 다름
- 범주의 크기는 DELETE < TRUNCATE < DROP 순
- DELETE: 행 일부 또는 전체 데이터를 지움, 정의된 테이블자체는 남아있음
- TRUNCATE: 행 전체 데이터 + 행이 저장되어있던 공간과 인덱스까지 지움, 정의된 테이블자체는 남아있음
- DROP: 행 데이터 + 인덱스 뿐 아니라 테이블 전체까지 완전히 제거
테이블 정의는 DROP 명령어를 사용한 테이블 삭제일 경우만 가능하며, 나머지 경우는 테이블의 정의가 남는다
- DELETE를 제외하고 TRUNCATE와 DROP는 사용공간(저장공간)을 반납하여 다른 곳에서 해당공간을 재사용할 수 있다
- DELETE를 이용한 데이터 삭제의 경우 삭제되는 행마다 로그를 기록하기 떄문에 작업 속도가 저하된다
3) 데이터 조작어 (DML:Data Manpulation Language)
- 정의 : 테이블에 새로운 데이터를 입력하거나 기존 데이터를 수정 또는 삭제하기 위한 명령어
- 종류
- 1) INSERT : 새로운 데이터 입력 명령어
- 2) UPDATE : 기존 데이터 수정 명령어
- 3) DELETE : 기존 데이터 삭제 명령어
- 4) MERGE : 두개의 테이블을 하나의 테이블로 병합하는 명령어
1. INSERT
예시)
INSERT INTO DEPT VALUES (73,'인사');
INSERT INTO DEPT VALUES (73,'인사','이대'); --이걸 써야 삽입됨
INSERT INTO DEPT (deptno , Dname , LOC) VALUES(74,'회계팀','충정로'); --위치를 맞췄기 때문에 삽입가능
INSERT INTO DEPT (deptno , LOC , Dname) VALUES(75,'신대방','자제팀'); --위치를 맞추지 않아서 삽입 불가능
INSERT INTO DEPT (deptno , LOC) VALUES (76,'홍대');
문제2-1) professor TBL에 새로 삽입하기
INSERT INTO professor (profno , name , position , hiredate , deptno)
VALUES (9920 , '최윤식' , '조교수' , TO_DATE('2006/01/01','YYYY/MM/DD'), 102);
INSERT INTO professor (profno , name , position , hiredate , deptno)
VALUES (9910 , '백미선' , '전임강사' , sysdate , 101);
>>>JOB3테이블 새로 생성 후 밑에 문제 시작
문제2-2)
INSERT INTO JOB3 VALUES (10,'학교');
INSERT INTO JOB3(jobno , jobname) VALUES (11,'공무원');
INSERT INTO JOB3(jobname , jobno) VALUES ('공기업',12);
INSERT INTO JOB3 VALUES (13,'대기업');
INSERT INTO JOB3 VALUES (14,'중소기업');1-1. INSERT ALL(unconditional INSERT ALL) 명령문
height_info, weight_info TBL 먼저 생성
CREATE TABLE height_info
( studNo number(5),
NAME VARCHAR2(20),
height number(5,2)
);
CREATE TABLE weight_info
( studNo number(5),
NAME VARCHAR2(20),
weight number(5,2)
);
INSERT ALL
INTO height_info VALUES(studNo , name , height)
INTO weight_info VALUES(studNo , name , weight)
SELECT studno , name , height , weight , grade
FROM student
WHERE grade >= '2' --조건: grade가 2보다 클 것
;
↓↓↓ 아래 조건절로 하면 각 테이블마다 조건을 세분화해서 입력가능
INSERT ALL
[WHEN 조건절1 THEN
INTO [table1] VLAUES[(column1, column2,…)]
[WHEN 조건절2 THEN
INTO [table2] VLAUES[(column1, column2,…)]
[ELSE
INTO [table3] VLAUES[(column1, column2,…)]
subquery;
문제 2-3) 학생 테이블에서 2학년 이상의 학생을 검색하여
height_info 테이블에는 키가 170보다 큰 학생의 학번, 이름, 키를 입력, weight_info 테이블에는 몸무게가 75보다 큰 학생의 학번, 이름, 몸무게를 각각 입력하여라
INSERT ALL
WHEN height > 170 Then
INTO height_info VALUES(studNo , name , height)
WHEN weight > 75 Then
INTO weight_info VALUES(studNo , name , weight)
SELECT studno , name, height , weight
FROM student
WHERE grade >= '2'
;
2. Update
- 데이터 수정 개요
- UPDATE 명령문은 테이블에 저장된 데이터 수정을 위한 조작어
- WHERE 절을 생략하면 테이블의 모든 행을 수정
문제 2-4) 교수 번호가 9903인 교수의 현재 직급을 ‘부교수’로 수정하여라
UPDATE professor
SET position = '부교수',userid = 'kkk'
WHERE profno = 9903
;
문제 2-5) 서브쿼리를 이용하여 학번이 10201인 학생의 학년과 학과 번호를 10103 학번 학생의 학년과 학과 번호와 동일하게 수정하여라
UPDATE student
SET (grade , deptno) = (
SELECT grade , deptno
FROM student
WHERE studno = 10103
)
WHERE studno = 10201
;3. DELETE
- 데이터 삭제 개요
- DELETE 명령문은 테이블에 저장된 데이터 삭제를 위한 조작어
- WHERE 절을 생략하면 테이블의 모든 행 삭제
문제 2-6) 학생 테이블에서 학번이 20103인 학생의 데이터를 삭제
DELETE
FROM student
WHERE studno = 20103
;
문제 2-7) 학생 테이블에서 컴퓨터공학과에 소속된 학생을 모두 삭제하여라.
DELETE
FROM student
WHERE deptno = ( SELECT deptno
FROM department
WHERE dname = '컴퓨터공학과'
)
;
4. MERGE
- 구조가 같은 두개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작어
- WHEN 절의 조건절에서 결과 테이블에 해당 행이 존재하면 UPDATE 명령문에 의해 새로운 값으로 수정,
- 그렇지 않으면 INSERT 명령문으로 새로운 행을 삽입
예비작업 1)
CREATE TABLE professor_temp
AS SELECT * FROM professor
WHERE position = '교수'
;
UPDATE professor_temp
SET position = '명예교수'
WHERE position = ' 교수'
;
INSERT INTO professor_temp
VALUES (9999,'김도경','arom21','전임강사',200,sysdate , 10, 101)
;
commit;
예비작업 2) professor MERGE 수행
목표 : professor_temp에 있는 직위 수정된 내용을 professor Table에 Update
김도경 씨가 신규 Insert 내용을 professor Table에 Insert
-- 1) 교수가 명예교수로 2행 Update
-- 2) 김도경 씨가 신규 Insert
MERGE INTO professor p
USING professor_temp f
ON (p.profno = f.profno)
when matched then --PK(Primary Key)가 같으면 직위를 Update
update set p.position = f.position
when not matched then --PK가 없으면 신규 Insert
insert values(f.profno , f.name , f.userid , f.position , f.sal , f.hiredate , f.comm , f.deptno)
;
오늘의 숙제
Inner Join 이용
1. 이름, 관리자명(emp TBL)
>>내가 한 것
SELECT e.ename , m.mgr
FROM emp e , emp m
WHERE e.empno = m.empno
;
>>선생님이 한 것
SELECT w.ename , m.ename 관리자
FROM emp w , emp m
WHERE w.mgr = m.empno
;
2. 이름,급여,부서코드,부서명,근무지, 관리자 명, 전체직원(emp ,dept TBL) --Join 3개
>>내가 한 것
SELECT e.ename , e.sal , e.empno , d.dname , d.loc, e.mgr
FROM emp e , dept d
WHERE e.deptno = d.deptno
;
>>선생님이 한 것
SELECT w.ename , w.sal , w.deptn o , dname , loc , m.ename 관리자
FROM emp w , emp m , dept d
WHERE w.deptno = d.deptno
AND w.mgr = m.empno(+)
;
3. 이름,급여,등급,부서명,관리자명, 급여가 2000이상인 사람 (emp, dept,salgrade TBL)
>>내가 한 것
SELECT e.ename , e.sal , s.grade , d.dname , e.mgr
FROM emp e , dept d, salgrade s
WHERE e.deptno = d.deptno
AND sal >= 2000
;
>>선생님이 한 것
SELECT w.ename , w.sal , s.grade , dname , m.ename
FROM emp w , emp m , dept d , salgrade s
WHERE w.mgr = m.empno
AND w.deptno = d.deptno
AND w.sal BETWEEN s.losal AND s.hisal
AND w.sal>=2000
;>>성능을 높이려면?
SELECT w.ename , w.sal , grade , dname , m.ename
FROM ( SELECT * FROM emp WHERE w.sal >= 2000) w
, emp m
, dept d
, salgrade s
WHERE w.mgr = m.empno
AND w.deptno = d.deptno
AND w.sal BETWEEN s.losal AND s.hisal
;
4. 보너스를 받는 사원에 대하여 이름,부서명,위치를 출력하는 SELECT 문장을 작성emp ,dept TBL)
>>내가 한 것
SELECT e.ename , d.dname , d.loc
FROM emp e , dept d
WHERE e.deptno = d.deptno
AND comm>0
;>>선생님이 한 것
SELECT e.ename , d.dname , d.loc
FROM emp e , dept d
WHERE e.deptno = d.deptno
AND e.comm>0
;
5. 사번, 사원명, 부서코드, 부서명을 검색하라. 사원명기준으로 오름차순정열(emp ,dept TBL)
>>내가 한 것
SELECT e.empno , e.ename , d.deptno , d.dname
FROM emp e , dept d
WHERE e.deptno = d.deptno
ORDER BY e.ename
;>>선생님이 한 것 = 나와 같음
SUB Query 이용 (EMP TABLE)
-- 1. Blake와 같은 부서에 있는 모든 사원에 대해서 사원 이름과 입사일을 디스플레이하라
>>내가 한 것
SELECT ename , hiredate
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'BLAKE'
)
;>>선생님이 한 것
SELECT ename , hiredate , deptno
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE INITCAP(ename) = 'BLAKE'
)
;
-- 2. 평균 급여 이상을 받는 모든 사원에 대해서 사원 번호와 이름을 디스플레이하는 질의문을 생성.
-- 단 출력은 급여 내림차순 정렬하라
>>내가 한 것
SELECT ename , empno , sal
FROM emp
WHERE sal > (
SELECT AVG(sal)
FROM emp
)
ORDER BY sal DESC
;>>선생님이 한 것 = 나와 같음
-- 3. 보너스를 받는 어떤 사원의 부서 번호와
-- 급여에 일치하는 사원의 이름, 부서 번호 그리고 급여를 디스플레이하라.
>>내가 한 것
SELECT ename , deptno , sal
FROM emp
WHERE sal IN (
SELECT d.deptno , e.ename
FROM emp d , emp e
WHERE d.sal = e.sal
AND comm > 0
)
;>>선생님이 한 것
SELECT ename , deptno , sal
FROM emp
WHERE (deptno , sal) IN
SELECT deptno , sal
FROM emp
WHERE comm IS NOT NULL
)
;
데이터 조작어 이용
CREATE TABLE Religion
( religion_no NUMBER(2) CONSTRAINT PK_ReligionNo3 PRIMARY KEY,
religion_name VARCHAR2(20)
) ;
--10 기독교
--20 카톨릭교
--30 불교
--40 무교
>>내가 한 것
INSERT INTO RELIGION VALUES (10,'기독교');
INSERT INTO RELIGION VALUES (20,'카톨릭교');
INSERT INTO RELIGION VALUES (30,'불교');
INSERT INTO RELIGION VALUES (40,'무교');>>선생님이 한 것
INSERT INTO RELIGION (religion_no , religion_name) VALUES (10,'기독교');
INSERT INTO RELIGION (religion_name , religion_no) VALUES ('카톨릭교',20);
INSERT INTO RELIGION VALUES (30,'불교');
INSERT INTO RELIGION VALUES (40,'무교');'DB > Oracle' 카테고리의 다른 글
| 2024_07_02_화 (0) | 2024.07.02 |
|---|---|
| 2024_07_01_월 (0) | 2024.07.01 |
| 2024_06_27_목 (0) | 2024.06.27 |
| 2024_06_26_수 (0) | 2024.06.26 |
| 2024_06_26_수 (0) | 2024.06.26 |
