day7

📖 서브쿼리 연습문제

student, department 테이블 활용
학과 이름, 학과별 최대키, 학과별 최대키를 가진 학생들의 이름과 키를 출력 하세요.

방안 1) WHERE 에 서브쿼리 넣었을때

SELECT 
	d.dname 학과이름, s.deptno1 학과번호, s.height 학과별최대키, 
    s.name 학생이름, s.height 학생키
FROM student s, department d
WHERE (s.deptno1, s.height) IN (SELECT deptno1, MAX(height)
                                FROM student
                                GROUP BY deptno1)
    AND s.deptno1 = d.deptno;

==> 학과별 최대키 쿼리 먼저 만들고!
==> 학과이름이 나와야하니까 department 테이블이랑 조인하고 겹치는 것들끼리 연결시켜주고
==> 학과별로 최대키 나오는거니까 여러가지 행이 나오겠지 그래서 IN 으로 묶어주고
==> 학과별 최대키 쿼리니까 학과별 최대키 = 제일큰키를 가진 학생키 니까 s.height 변수 똑같은거고!

방안 2) WHERE 에 서브쿼리 넣었을때 + select 에 서브쿼리 넣었을때

SELECT 
    d.dname 학과이름, 
    s.deptno1 학과번호, 
    (SELECT MAX(height)
     FROM student s2
     WHERE s2.deptno1 = s.deptno1) 학과별최대키, 
    s.name 학생이름, 
    s.height 학생키
FROM student s, department d
WHERE (s.deptno1, s.height) IN (SELECT deptno1, MAX(height)
                                FROM student
                                GROUP BY deptno1)
    AND s.deptno1 = d.deptno;

==> 여기서는 s.height라고 표시안하고 학과별 최대키를 서브쿼리로 만들어서 셀프조인으로 해서 만들어서 넣음!

방안 3) FROM 에 서브쿼리 넣었을때 name..은 어케 부르지? -> 셀프조인!!!!

SELECT 
	d.dname 학과이름, s1.max_height 학과별최대키, 
    s2.name 학생이름, s2.height 학생키
FROM (SELECT deptno1, MAX(height) max_height
        FROM student
        GROUP BY deptno1) s1, student s2, department d
WHERE s1.deptno1 = d.deptno
    AND s1.deptno1 = s2.deptno1
    AND s1.max_height = s2.height;

==> 테이블 자체를 서브쿼리로 만들어서 서브쿼리 s1에 있는 컬럼을 학과별 최대키가 나오게 하고
==> 셀프조인으로 해서 s1.deptno1 = s2.deptno1, s1.max_height = s2.height로 s1과 s2를 동일하게 만들어서 넣음!

방안 3) FROM 에 서브쿼리 넣었을때 + select 에 서브쿼리 넣었을때

SELECT 
    (SELECT dname
     FROM department d
     WHERE d.deptno = s1.deptno1) 학과이름, 
    s1.max_height 학과별최대키, 
    s2.name 학생이름, 
    s2.height 학생키
FROM (SELECT deptno1, MAX(height) max_height
        FROM student
        GROUP BY deptno1) s1, student s2
WHERE s1.deptno1 = s2.deptno1
    AND s1.max_height = s2.height;

==> department 테이블을 조인하지 않고 바로 select절에서 서브쿼리로 만들어서 student 테이블과 동일하게 만들어서 넣음!

💻

📖 SQL 쿼리 구분

(1) DDL(정의어-Data Definition Language)

: 테이블 관련
CREATE, ALTER, TRUNCATE(테이블안의 내용삭제), DROP(테이블자체삭제)

테이블 생성 - CREATE

CREATE TABLE SAMPLE01
(
    no NUMBER(4),
    id VARCHAR2(12),
    name VARCHAR2(30), --한글10글자
    st_date DATE    
);

==> SAMPLE01 테이블 생성!
==> no, id, name, st_date 컬럼

테이블 복사 - CREATE

CREATE TABLE 테이블명
AS
SELECT * FROM 복사할테이블;

테이블 구조만 복사(조건에 일부러 거짓을 줘서 내용은 아무것도 복사 안되게끔!)

CREATE TABLE 테이블명
AS
SELECT * FROM 복사할테이블 WHERE 조건모든게거짓(1=2);

--새로운 컬럼 추가

*** 테이블 변경 - ALTER

ALTER TABLE 테이블명
ADD (컬럼명 타입사이즈);

ALTER TABLE 테이블명
ADD (컬럼명 타입사이즈 DEFAULT 값);

--컬럼명 변경 -> 거의안쓰기때문에 쓸때 구글링해서 알면 됨~!

loc -> location
ALTER TABLE 테이블명 RENAME COLUMN loc TO location;

--테이블 이름 자체를 변경

RENAME 원래테이블명 TO 바꿀테이블명;

--컬럼 사이즈 조정

ALTER TABLE 테이블명
MODIFY (name VARCHAR2(60));

--컬럼 삭제

ALTER TABLE 테이블명
DROP COLUMN 컬럼명;

테이블 삭제 - DROP

--DROP TABLE SAMPLE01; --실무에선 조심해야한다!! 함부로 지우면 큰일

(2) DML(조작어-Data Manipulation Language)

: 테이블 내부데이터 조작
INSERT, UPDATE, DELETE, SELECT, MERGE

데이터 추가(삽입) - INSERT

INSERT INTO 테이블명 (컬럼명, 컬럼명, 컬럼명)
VALUES (값, 값, 값);

--모든 컬럼에 데이터를 저장하는 경우, 컬럼명 생략 가능

INSERT INTO 테이블명 
VALUES (값, 값, 값);

다른 테이블에서 데이터를 가지고 와서 저장

INSERT INTO 테이블명
SELECT 컬럼명 FROM 가져올테이블명 WHERE 조건;

데이터 수정(업데이트) - UPDATE

UPDATE 테이블명
SET 컬럼명 = 값
WHERE 조건;

🍟 연습문제

temp_professor 테이블에서
'Sharon Stone' 교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250 만원이 안 되는 교수들의 급여를 15% 인상하세요.

UPDATE temp_professor
SET pay = pay * 1.15
WHERE position = (SELECT position
                  FROM temp_professor
                  WHERE name = 'Sharon Stone')
AND pay < 250;

==> pay가 253으로 바뀌기 전에는 220 이였다!

💻

데이터 삭제 - DELETE

DELETE FROM 테이블명
WHERE 조건;

DML 데이터 조작을 하는 경우 -> COMMIT 까지 완료해야 최종적으로 적용!

(3) DCL(제어어-Data Control Language)

: 조작 권한
GRANT(권한부여), REVOKE(권한회수)

(4) TCL(트랜잭션 제어-Transaction Control Language)

: 트랜잭션 제어
COMMIT, ROLLBACK