Day 43. [ORACLE SQL] 서브쿼리와 조인, DDL DML DCL

42일 눈 치료 받고 쉬었지요... < 복습해야지...........

ORACLE DB join

다시 43일 가즈아!!!!

---

PAIRWISE 다중 칼럼 서브쿼리

- 메인쿼리와 서브쿼리의 비교 대상 칼럼을 쌍으로 묶어서 행별로 비교하는 방법

- 메인쿼리와 서브쿼리에서 비교하는 칼럼의 수는 반드시 동일해야함

 

-- PAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 춗력하여라.
SELECT NAME, GRADE, WEIGHT
FROM STUDENT
WHERE (GRADE, WEIGHT) IN (
    SELECT GRADE, MIN(WEIGHT)
    FROM STUDENT
    GROUP BY GRADE);

---

UNPAIRWISE 다중 칼럼 서브쿼리

- 메인쿼리와 서브쿼리의 비교 대상 칼럼을 분리하여 개별적으로 비교한 후 AND 연산에 의해 최종 결과를 출력

- 각 칼럼이 동시에 만족하지 않더라도 개별적으로 만족하는 경우에는 비교 조건이 참이 되어 결과를 출력 가능

 

-- UNPAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 춗력하여라.
SELECT NAME, GRADE, WEIGHT
FROM STUDENT
WHERE GRADE IN (
    SELECT GRADE
    FROM STUDENT
    GROUP BY GRADE)
AND WEIGHT IN (
    SELECT MIN(WEIGHT)
    FROM STUDENT
    GROUP BY GRADE)
ORDER BY 2;

---

서브쿼리 코스트가 조인보다 적다!!!

시간차이는 아주 미세...

 

SELECT STUDNO, NAME, DEPTNO, (SELECT DNAME FROM DEPARTMENT D WHERE S.DEPTNO = D.DEPTNO) DNAME 
FROM STUDENT S;

SELECT STUDNO, NAME, DEPTNO, DNAME
FROM STUDENT
JOIN DEPARTMENT USING(DEPTNO);

---

-- DDL, DML, DCL
-- DDL : DATA DEFINITION LANGUAGE 데이터 정의어
-- CREATE, ALTER, DROP 등등
-- DML : DATA MANUFACTUAL LANGUAGE 데이터 조작어
-- INSERT, UPDATE, DELETE ETC..
-- DCL : DATA CONTROL LANGUAGE 데이터 제어어
-- COMMIT, ROLLBACK, SAVEPOINT, GRANT, REVOKE ETC...
-- >> TCL (COMMIT, ROLLBACK, SAVEPOINT ... )

 

SELECT *
FROM PROFESSOR
WHERE POSITION = (
    SELECT POSITION
    FROM PROFESSOR
    WHERE NAME = '전은지');
    
-- 사용자 아이디가 „jun123‟인 학생과 같은 학년인 학생의 학번, 이름, 학년을 춗력하여라
SELECT * FROM STUDENT;
SELECT GRADE, NAME, STUDNO
FROM STUDENT
WHERE GRADE = (
    SELECT GRADE
    FROM STUDENT
    WHERE USERID = 'jun123');

-- 101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의 이름, 학과번호, 몸무게를 춗력하여라
SELECT NAME, STUDNO, WEIGHT
FROM STUDENT
WHERE WEIGHT < (
    SELECT AVG(WEIGHT)
    FROM STUDENT
    WHERE DEPTNO = 101);

-- 20101번 학생과 학년이 같고, 키는 20101번 학생보다 큰 학생의 이름, 학년, 키를 춗력하여라
SELECT NAME, GRADE, HEIGHT 
FROM STUDENT
WHERE GRADE = (
    SELECT GRADE 
    FROM STUDENT 
    WHERE STUDNO = '20101') 
AND HEIGHT > (
    SELECT HEIGHT 
    FROM STUDENT 
    WHERE STUDNO = '20101');

-- 서브쿼리에서 IN 부분일치 ALL 전체일치

SELECT DEPTNO
FROM DEPARTMENT
WHERE COLLEGE = 100;

SELECT * FROM DEPARTMENT;

-- 정보미디어학부(부서번호:100)에 소속된 모든 학생의 학번, 이름, 학과 번호를 춗력하여라
SELECT GRADE, NAME, DEPTNO
FROM STUDENT
WHERE DEPTNO IN (
    SELECT DEPTNO
    FROM DEPARTMENT
    WHERE DEPTNO = (
        SELECT DEPTNO
        FROM DEPARTMENT
        WHERE DNAME = '정보미디어학부'));
        
SELECT DEPTNO
FROM DEPARTMENT
WHERE DEPTNO = (
    SELECT DEPTNO
    FROM DEPARTMENT
    WHERE DNAME = '정보미디어학부');
    
-- 모든 학생 중에서 4학년 학생 중에서 키가 제일 작은 학생보다 키가 큰 학생의 학번, 이름, 키를 춗력하여라
SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT > (SELECT MIN(HEIGHT) FROM STUDENT WHERE GRADE = 4);

SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT > ANY(SELECT HEIGHT FROM STUDENT WHERE GRADE = 4);

SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT = (SELECT MIN(HEIGHT) FROM STUDENT WHERE GRADE = 4);

-- 모든 학생 중에서 4학년 학생 중에서 키가 제일 큰 학생보다 키가 큰 학생의 학번, 이름, 키를 춗력하여라
SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT > (SELECT MAX(HEIGHT) FROM STUDENT WHERE GRADE = 4);

SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT > ALL(SELECT HEIGHT FROM STUDENT WHERE GRADE = 4);

-- 보직수당을 받는 교수가 핚 명이라도 있으면 모든 교수의 교수 번호, 이름, 보직수당 그리고 급여와 보직수당의 합을 춗력하여라
SELECT PROFNO, NAME, SAL, COMM, SAL + COMM
FROM PROFESSOR
WHERE EXISTS (
    SELECT *
    FROM PROFESSOR
    WHERE COMM IS NOT NULL);

-- 각 학과 학생의 평균 키보다 키가 큰 학생의 이름, 학과 번호, 키를 춗력하여라
SELECT STUDNO, NAME, DEPTNO, HEIGHT
FROM STUDENT S1
WHERE HEIGHT > (
    SELECT AVG(HEIGHT)
    FROM STUDENT S2
    WHERE S1.DEPTNO = S2.DEPTNO
    GROUP BY DEPTNO)
ORDER BY 3;

SELECT DEPTNO, AVG(HEIGHT)
FROM STUDENT
GROUP BY DEPTNO;

-- 학번, 이름, 학과번호, 학과 이름 조회
-- 조인, 서브쿼리
SELECT STUDNO, NAME, DEPTNO, (SELECT DNAME FROM DEPARTMENT D WHERE S.DEPTNO = D.DEPTNO) DNAME 
FROM STUDENT S;

SELECT STUDNO, NAME, DEPTNO, DNAME
FROM STUDENT
JOIN DEPARTMENT USING(DEPTNO);

-- DDL, DML, DCL
-- DDL : DATA DEFINITION LANGUAGE 데이터 정의어
-- CREATE, ALTER, DROP 등등
-- DML : DATA MANUFACTUAL LANGUAGE 데이터 조작어
-- INSERT, UPDATE, DELETE ETC..
-- DCL : DATA CONTROL LANGUAGE 데이터 제어어
-- COMMIT, ROLLBACK, SAVEPOINT, GRANT, REVOKE ETC...
-- >> TCL (COMMIT, ROLLBACK, SAVEPOINT ... )


INSERT INTO STUDENT
VALUES (1011, '홍길동', 'hong', '1', '8501011143098', '85/01/01', '041)630-3114', 170, 70, 101, 9903);

SELECT * FROM STUDENT;