-- 도서번호가 1인 도서의 이름
SELECT bookname
FROM BOOK
WHERE BOOKID = 1;
-- 가격이 20000원 이상인 도서의 이름
SELECT bookname
FROM BOOK
WHERE PRICE >= 20000;
-- 박지성의 총 구매액
SELECT SUM(SALEPRICE)
FROM ORDERS
WHERE CUSTID =
(
SELECT CUSTID
FROM customer
WHERE name = '박지성'
);
-- 박지성의 총 구매액 다른방법
SELECT SUM(SALEPRICE)
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID AND NAME LIKE '박지성';
-- 박지성이 구매한 도서의 수
SELECT COUNT(ROWNUM)
FROM ORDERS
WHERE CUSTID =
(
SELECT CUSTID
FROM customer
WHERE name = '박지성'
);
-- 박지성이 구매한 도서의 수 다른방법
SELECT COUNT(*)
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID AND CUSTOMER.NAME LIKE '박지성';
-- 도서의 총개수
SELECT COUNT(*)
FROM BOOK;
-- 출판사의 총개수
SELECT COUNT(DISTINCT publisher)
FROM BOOK;
-- 모든 고객의 이름 주소
SELECT NAME, ADDRESS
FROM customer;
-- 2014년 7월4일 ~ 7월7일 사이에 주문받은 도서의 주문번호
SELECT *
FROM ORDERS
WHERE ORDERDATE BETWEEN '14/07/04' AND '14/07/07';
-- 2014년 7월4일 ~ 7월7일 사이에 주문받은 도서를 제외한 도서의 주문번호
SELECT *
FROM ORDERS
WHERE ORDERDATE < '14/07/04' OR ORDERDATE > '14/07/07';
-- 성이 '김'씨인 고객의 이름과 주소
SELECT *
FROM customer
WHERE NAME LIKE '김%';
-- 성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소
SELECT *
FROM customer
WHERE NAME LIKE '김%아';
----------------------------
-- 박지성이 구매한 도서의 출판사 수
SELECT COUNT(DISTINCT b.publisher)
FROM (
SELECT BOOKID
FROM CUSTOMER C, ORDERS O
WHERE c.custid = o.custid AND NAME = '박지성'
) A, BOOK B
WHERE a.bookid = b.bookid;
-- 박지성이 구매한 도서의 출판사 수 다른방법
SELECT COUNT(DISTINCT b.publisher)
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE c.custid = O.CUSTID AND b.bookid = o.bookid AND NAME = '박지성';
-- 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
SELECT b.bookname, b.price, o.saleprice, (b.price - o.saleprice)
FROM BOOK B, customer C, ORDERS O
WHERE O.bookid = b.bookid AND o.custid = c.custid AND NAME = '박지성';
-- 박지성이 구매하지 않은 도서의 이름
SELECT DISTINCT BOOKNAME
FROM BOOK
WHERE BOOKID NOT IN (SELECT BOOKID
FROM customer C, ORDERS O
WHERE c.custid = O.CUSTID AND c.name = '박지성');
-- 박지성이 구매하지 않은 도서의 이름 다른방법1
SELECT DISTINCT BOOKNAME
FROM BOOK
WHERE NOT EXISTS (SELECT BOOKID
FROM customer C, ORDERS O
WHERE c.custid = O.CUSTID AND BOOK.BOOKID = O.BOOKID AND c.name = '박지성');
-- 박지성이 구매하지 않은 도서의 이름 다른방법2
select bookname
from book
minus
select b.bookname
from customer c , orders o , book b
where c.custid = o.custid
and o.bookid = b.bookid
and name like '박지성';
-- 주문하지 않은 고객의 이름 (조인 사용)
SELECT NAME
FROM CUSTOMER
MINUS
SELECT DISTINCT NAME
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE C.CUSTID = O.CUSTID AND B.BOOKID = O.BOOKID;
-- 주문하지 않은 고객의 이름 NOT IN
SELECT NAME
FROM CUSTOMER
WHERE CUSTID NOT IN (
SELECT CUSTID
FROM ORDERS
GROUP BY custid
);
-- 주문하지 않은 고객의 이름 NOT EXISTS
SELECT NAME
FROM CUSTOMER
WHERE NOT EXISTS (
SELECT CUSTID
FROM ORDERS
WHERE customer.custid = orders.custid
);
-- 주문 금액의 총액과 주문의 평균 금액
SELECT SUM(SALEPRICE), AVG(SALEPRICE)
FROM ORDERS;
-- 고객의 이름과 고객별 구매액
SELECT C.NAME, SUM(o.saleprice)
FROM customer C, ORDERS O
WHERE C.CUSTID = O.CUSTID
GROUP BY C.NAME;
-- 고객의 이름과 고객이 구매한 도서 목록
SELECT c.name, b.bookname
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE c.custid = o.custid AND B.BOOKID = O.BOOKID;
-- 도서의 가격(BOOK 테이블)과 판매가격(ORDERS 테이블)의 차이가 가장 많은 주문
SELECT *
FROM BOOK B, ORDERS O
WHERE b.bookid = o.BOOKid AND (B.PRICE - o.saleprice) = (
SELECT MAX(ABS(B.PRICE - o.saleprice))
FROM BOOK B, ORDERS O
WHERE b.bookid = o.BOOKid
);
-- 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
SELECT C.NAME, AVG(o.saleprice)
FROM customer C, ORDERS O
WHERE c.custid = o.CUSTid
GROUP BY C.NAME
HAVING AVG(o.saleprice) > (
SELECT AVG(SALEPRICE)
FROM ORDERS
);
STUDENT 테이블과 DEPARTMENT 테이블을 사용하여 '서진수' 학생과 1전공(DEPTNO2)이 동일한 학생들의 이름과 1전공 이름을 출력하세요
먼저 서진수 학생의 전공을 추린다
SELECT DEPTNO1
FROM STUDENT
WHERE NAME = '서진수';
101번을 전공하는 학생이름과 전공을 출력한다
SELECT S.NAME STUD_NAME, d.dname DEPT_NAME
FROM STUDENT S, DEPARTMENT D
WHERE D.DEPTNO = 101 AND S.DEPTNO1 = d.deptno;
101자리에 맨 위 코드를 서브쿼리한다
SELECT S.NAME STUD_NAME, d.dname DEPT_NAME
FROM STUDENT S, DEPARTMENT D
WHERE D.DEPTNO =
(
SELECT DEPTNO1
FROM STUDENT
WHERE NAME = '서진수'
)
AND S.DEPTNO1 = d.deptno;
PROFESSOR 테이블과 DEPARTMENT 테이블을 조회하여 '주승재' 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요.
먼저 주승재교수의 입사일을 확인
SELECT HIREDATE
FROM PROFESSOR
WHERE NAME = '주승재';
위 코드를 서브쿼리하여 주승재 교수보다 입사일이 늦은 사람들을 추리기
SELECT P.NAME , P.HIREDATE, d.dname
FROM PROFESSOR P, DEPARTMENT D
WHERE HIREDATE >
(
SELECT HIREDATE
FROM PROFESSOR
WHERE NAME = '주승재'
)
AND p.deptno = d.deptno;
STUDENT 테이블에서 1전공(DEPTNO1)이 201번인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를 출력하세요.
먼저 학생들의 평균 몸무게를 확인
SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 201
GROUP BY DEPTNO1;
위 코드를 서브쿼리하여 필요한 요소를 출력
SELECT NAME, WEIGHT
FROM STUDENT
WHERE WEIGHT >
(
SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 201
GROUP BY DEPTNO1
);
EMP2 테이블을 사용하여 전체 직원 중 '과장' 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급, 연봉을 출력하세요. 단, 연봉 출력 형식은 아래와 같이 천 단위 구분 기호와 $표시를 하세요.
SELECT NAME, POSITION, to_char(pay, '$999,999,999') SALAERY
FROM EMP2
WHERE pay >
(
SELECT MIN(PAY)
FROM EMP2
WHERE POSITION = '과장'
);
EMP2 테이블과 DEPT2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그중에서 평균 연봉이 가장 적은 부서의 평균 연봉보다 적게받는 직원들의 부서명, 직원명, 연봉을 출력하세요.
SELECT DEPT2.DNAME, EMP2.NAME, TO_CHAR(EMP2.PAY, '$999,999,999') SALARY
FROM EMP2, DEPT2
WHERE PAY <
(
SELECT *
FROM
(
SELECT AVG(PAY)
FROM EMP2
GROUP BY DEPTNO
ORDER BY AVG(PAY)
)
WHERE ROWNUM = 1
) AND EMP2.DEPTNO = DEPT2.DCODE;
SYNONYM은 CREATE PUBLIC SYNONYM EMP FOR SCOTT.EMP; (다른 사용자의 테이블을 시노님으로 가져오기) 위처럼 쓰면 FROM EMP; 처럼 이용해도 가져와진다.
위처럼 쓰지 않으면 다른테이블을 가져올때 항상 FROM SCOTT.EMP; 처럼 사용해야 한다
---------------------------------
뷰
뷰의 핵심은 동기화
외부직원에게 테이블의 민감한 정보를 제외하고 제공할때 복제해서주면 동기화의 문제와 다시합칠때 문제가 있다
VIEW를 이용하면 정보를 은닉하여 보여주고 싶은 것만 보여주고 동기화도 자동으로 된다
---------------------------------
CRUD (필수 꼭외울 것)
INSERT - C (create)
SELECT - R (read)
UPDATE - U
DELETE - D
--------------------------------
SELECT * FROM EMP; 를 하면 DB에 검색을 바로 하는게 아니라 DBMS(오라클)를 검색한다 1. 파싱 ( 코드 구문분석) 2. 데이터딕셔너리에게 메타데이터를 물어본다 (EMP테이블이 있어? ENAME라는 컬럼이 있어?) 3. 있으면 DB(램을 검색하고 램에 없으면 하드에서 램으로 올려서 검색) (한번 수행했던 쿼리는 다시 수행하면 파싱이나 딕셔너리접근을 하지않아서 빠르게 출력된다)
--------------------------------
CPU - 캐시메모리 - 램
이면 캐시메모리가 캐시 역할
CPU - 램 - 하드디스크
이면 램이 캐시 역할
CPU가격은 캐시메모리가 정한다(속도가 매우 빠르기때문)
---------------------------------
최근 프로그래밍에서 속도증가는 자료구조 등 보다 하드디스크접근(I/O)을 줄이는 것에 집중되어 있다
디스크 접근(I/O)을 줄이는 방법 1.캐시메모리를 늘리기 / 메모리에 자주쓰는 데이터를 올리는 기법 2.시퀀셜 엑세스 기법 3.랜덤엑세스 기법
1번이 가장 중요
2.시퀀셜 엑세스 기법
순서대로 블록 전체를 다 읽어서 필요한 데이터를 찾아낸다.(풀스캔)
풀스캔 - 찾을 데이터가 많으면 많을수록 유리해진다
3.랜덤 엑세스 기법 (프라이머리 키)
필요한 블록만 하나씩 가져오는 것
데이터가 적으면 적을수록 유리해진다
통상 15%이내의 데이터를 검색할 때 시퀀셜보다 랜덤 엑세스가 유리하다
----------------------------
클러스터 - 군집화
클러스터링 팩터 전략 데이터를 아무곳에나 놓는 것이 아니라 비슷한것은 모아두는 것(검색시 속도향상)
