아우터조인
쿼리의 성능을 많이 떨어트림
옵티마이져
유효성검사 : 쿼리문법에 문제가 있는지 확인 가장 최적의 실행방법을 만들어냄 인덱스를 쓰지 않고 풀스캔을 함
--학번, 학생이름, 학년, 담당교수이름을 표시하는 쿼리 LEFT OUTER JOIN키워드로 OUTERJOIN으로 처리
--ANSI
SELECT S.STUDNO "학번", S.NAME "학생이름", S.GRADE "학년", P.NAME "담당교수"
FROM STUDENT S LEFT OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO;
--오라클
SELECT S.STUDNO "학번", S.NAME "학생이름", S.GRADE "학년", P.NAME "담당교수"
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO(+);
--UNION
SELECT S.STUDNO "학번", S.NAME "학생이름", S.GRADE "학년", P.NAME "담당교수"
FROM STUDENT S JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO
UNION
SELECT STUDNO "학번", NAME "학생이름", GRADE "학년", null
FROM STUDENT
WHERE PROFNO is null
ORDER BY 3;
--교수명, 직책, 담당학생명을 표시하는 쿼리
SELECT P.NAME "교수명", P.POSITION "직책", S.NAME "담당학생"
FROM STUDENT S RIGHT OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO
ORDER BY 1;
--1. employees에서 모든 사원의 이름, 부서번호, 부서명을 표시
SELECT E.LAST_NAME "이름", D.DEPARTMENT_ID "부서번호", D.DEPARTMENT_NAME "부서명"
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
--2. 80번 부서에 속하는 모든 직종아이디를 표시(부서위치도 포함)
SELECT D.DEPARTMENT_ID "부서아이디", L.STATE_PROVINCE "부서위치"
FROM DEPARTMENTS D JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
WHERE D.DEPARTMENT_ID = 80;
--3. 커미션을 받는 모든 사원의 이름, 부서이름, 위치아이디, 도시명 출력
SELECT E.LAST_NAME "사원이름", D.DEPARTMENT_NAME "부서이름", L.LOCATION_ID "위치아이디", L.CITY "도시명"
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.COMMISSION_PCT IS NOT NULL;
--ANSI
SELECT E.LAST_NAME "사원이름", D.DEPARTMENT_NAME "부서이름", L.LOCATION_ID "위치아이디", L.CITY "도시명"
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
AND E.COMMISSION_PCT IS NOT NULL;
--4. 이름에 a가 포함된 모든 사원의 이름과 부서이름을 표시
SELECT E.LAST_NAME "사원이름", D.DEPARTMENT_NAME "부서이름"
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.LAST_NAME LIKE '%a%';
--5. Toronto에 근무하는 모든 사원의 이름, 부서번호, 부서이름 표시
SELECT E.LAST_NAME "사원이름", D.DEPARTMENT_ID "부서번호", D.DEPARTMENT_NAME "부서이름"
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND L.CITY = 'Toronto';
--6. 사원테이블에서 사원이름, 사원번호, 관리자 이름, 관리자의 사원번호 표시
SELECT E.LAST_NAME "사원이름", E.EMPLOYEE_ID "사원번호", temp.LAST_NAME "관리자이름",temp.EMPLOYEE_ID "관리자번호"
FROM EMPLOYEES E, EMPLOYEES temp
WHERE E.MANAGER_ID = temp.EMPLOYEE_ID;
--7, Davies라는 사원본다 늦게 입사한 사원의 이름과 입사일을 표시
SELECT temp.LAST_NAME, temp.HIRE_DATE
FROM EMPLOYEES E, EMPLOYEES temp
WHERE E.LAST_NAME = 'Olson'
AND E.HIRE_DATE < temp.HIRE_DATE;
--서브쿼리
SELECT last_name, hire_date
FROM EMPLOYEES
WHERE hire_Date > (
SELECT hire_date
FROM EMPLOYEES
WHERE last_name = 'Olson'
);
--DML
-- - 데이타 조작어
-- INSERT 구문
-- UPDATE 구문
-- DELETE 구문
--
-- - COMMIT와 ROLLBACK 명령어의 영향을 받는다.
-- COMMIT은 DML의 실행결과를 DB에 반영한다.
-- ROLLBACK은 DML의 실행결과의 DB반영을 취소한다.
-- INSERT구문
-- INSERT INTO 테이블명 (컬럼명, 컬럼명, 컬럼명.....)
-- VALUES (값, 값, 값.....)
--
-- INSERT INTO 테이블명
-- VALUES(값, 값, 값, 값, 값)
--NULL 값을추가
--INSERT INTO 테이블명(컬럼명, 컬럼명, 컬럼명, ...)
--VALUES(값., 값., 값., ....)
--
--NULL값을 추가
--INSERT INTO 테이블명(컬럼명1, 컬럼명2)
--VALUES(값, NULL);
--명시적으로 컬럼2에 NULL값을 넣기
--
--INSERT INTO 테이블명(컬럼명1)
--VALUES
--- NULL값을 넣고 싶은 컬럼을 INSERT에서 빼기
--UPDATE 구문
--UPDATE 테이블명
--SET
-- 컬럼1 = 값1,
-- 컬럼2 = 값2, ...
--WHERE 조건
--DELETE 구문
--DELETE FROM 테이블명
--WHERE 조건;
--WHERE 조건절을 제시하지 않으면 테이블의 모든행이 삭제된다.
--INSERT, UPDATE, DELETE는 데이타를 조작하는 SQL문이다.
--INSERT, UPDATE, DELETE 작업 수행후에는 COMMIT을 실행시켜서 DB에 반영되게하거나, ROLLBACK을 실행시켜서 DB반영을 취소할 수 있다.
--INSERT, UPDATE, DELETE 작업은 컬럼에 적용된 무결성 제약조건의 영향을 받는다.
--(기본키 제약조건, 고유키 제약조건, 참조키 제약조건, 체크제약조건, NOT NULL 제약조건)
--JDBC
--JDBC에 사용되는 LIB 경로 C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar
--자바에서 제공하는 DATABASE ACCESS API
--JAVA.SQL 패키지에서 다양한 인터페이스와 클래스를 제공한다.
--JDBC는 관계형 데이타베이스 Access에 관련한 표준을 정의하고 있다.
--JDBC에서는
-- DATABASE와의 연결 -> Connection
-- SQL의 전송과 실행 -> Statement, PyparedStatement
-- 결과값의 처리 -> ResultSet
--와 관련된 API를 정의하고 있다.
--JDBC 에서는 DriverManager를 제공한다.
--DriverManaver은 각 제조사들이 제공하는 jdbc드라이버를 이용해서 db연결
-- API(Application Programing Interface)
-- - 제품이나 소프트웨어의 모든 기능에 대한 설명을 포함하고 있는문서
INSERT INTO DEPT2 (DCODE, DNAME, PDEPT, AREA)
VALUES(2011, '개발팀', 1111, '울산지사');
INSERT INTO DEPT2(DCODE, DNAME, PDEPT, AREA)
VALUES(1012, '영업5팀', 1007, '제주지사');
commit;
UPDATE DEPT2 SET AREA = '광주지사', DNAME = '광주영업팀' WHERE DCODE = 1012;
COMMIT;
--컴퓨터에서 가장 오래걸리는 시간 파일의 형태로 읽고 쓰는것이 가장 오래걸린다.
--트랜잭션 - 부분성공을 허용하지 않는다.전부다 적용이 되거나 전부다 적용이 안되거나
UPDATE EMP2 SET PAY = (PAY *0.10) WHERE EMP_TYPE IN('수습직', '인턴');
COMMIT;
INSERT INTO DEPT2 VALUES(1012, '제주영업팀', 1007, '제주지사');--고유해야 하는 제약조건 위배되서 인서트 되지 않음
DELETE DEPT2 WHERE DCODE = 1012;
COMMIT;
SELECT *
FROM DEPT2;
INSERT INTO dapt2(dcode, dname, pdept, area) VALUES(1013, '제주영업팀', 1007, '제주지사');
INSERT INTO dept2(dcode, dname, pdept, area) VALUES(1013, '제주영업팀', 1007, '제주지사');
DELETE FROM dept2 WHERE DCODE = '1013';
COMMIT;
