[DB]_23.02.09
✔ 오라클 설치 및 실습
+) [DB] 📈 데이터 모델링 개념 & ERD 다이어그램 그리기 💯 총정리
https://inpa.tistory.com/entry/DB-%F0%9F%93%9A-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%AA%A8%EB%8D%B8%EB%A7%81-1N-%EA%B4%80%EA%B3%84-%F0%9F%93%88-ERD-%EB%8B%A4%EC%9D%B4%EC%96%B4%EA%B7%B8%EB%9E%A8#%EC%97%94%ED%8B%B0%ED%8B%B0(Entity)_%F0%9F%93%84
연습문제 12번 풀이
🔽 연습문제 12번
- (참고)
- 호텔, 방 -> 개체 2개, 1:N 관계
- 호텔, 고객 -> N:M 관계(다대다 관계)
- 투숙 , 예약 모델링은
(1) SQL Developer를 이용하여 모델링 수행
(2) ER 다이어그램을 테이블로 변환
- (리뷰) ref. https://inpa.tistory.com/entry/DB-%F0%9F%93%9A-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%AA%A8%EB%8D%B8%EB%A7%81-1N-%EA%B4%80%EA%B3%84-%F0%9F%93%88-ERD-%EB%8B%A4%EC%9D%B4%EC%96%B4%EA%B7%B8%EB%9E%A8#%EC%97%94%ED%8B%B0%ED%8B%B0(Entity)_%F0%9F%93%84
1:1 관계(일대일 관계)
1:1 관계란 어느 엔티티 쪽에서 상대 엔티티와 반드시 단 하나의 관계를 가지는 것을 말한다.
1:N 관계(일대다 관계)
1:N 관계는 한 쪽 엔티티가 관계를 맺은 엔티티 쪽의 여러 객체를 가질 수 있는 것을 의미한다.
N:M 관계(다대다 관계)
N:M 관계는 관계를 가진 양쪽 엔티티 모두에서 1:N 관계를 가지는 것을 말한다.
6장 연습문제 8번 풀이
🔽 연습문제 8번
(0) 테이블 4개 생성
🔽
(1) SQL Developer를 이용하여 모델링 수행
(2) ER 다이어그램을 테이블로 변환
6장 연습문제 9번 풀이
🔽 연습문제 9번
(1) SQL Developer를 이용하여 모델링 수행
(2) ER 다이어그램을 테이블로 변환
6장 연습 문제 10번 풀이
🔽 연습문제 10번
(1) SQL Developer를 이용하여 모델링 수행
(2) ER 다이어그램을 테이블로 변환
(3) ddl 문 생성
CREATE TABLE 직원 (
직원번호 NUMBER NOT NULL,
직원이름 VARCHAR2(40),
직원_직원번호 NUMBER NOT NULL
);
ALTER TABLE 직원 ADD CONSTRAINT 직원_pk PRIMARY KEY ( 직원번호 );
CREATE TABLE 참가 (
직원_직원번호 NUMBER NOT NULL,
프로젝트_프로젝트번호 NUMBER NOT NULL,
시간 VARCHAR2(40)
);
ALTER TABLE 참가 ADD CONSTRAINT 참가_pk PRIMARY KEY ( 직원_직원번호,
프로젝트_프로젝트번호 );
CREATE TABLE 프로젝트 (
프로젝트번호 NUMBER NOT NULL,
프로젝트이름 VARCHAR2(40)
);
ALTER TABLE 프로젝트 ADD CONSTRAINT 프로젝트_pk PRIMARY KEY ( 프로젝트번호 );
ALTER TABLE 직원
ADD CONSTRAINT 직원_직원_fk FOREIGN KEY ( 직원_직원번호 )
REFERENCES 직원 ( 직원번호 );
ALTER TABLE 참가
ADD CONSTRAINT 참가_직원_fk FOREIGN KEY ( 직원_직원번호 )
REFERENCES 직원 ( 직원번호 );
ALTER TABLE 참가
ADD CONSTRAINT 참가_프로젝트_fk FOREIGN KEY ( 프로젝트_프로젝트번호 )
REFERENCES 프로젝트 ( 프로젝트번호 );
-- Oracle SQL Developer Data Modeler 요약 보고서:
--
-- CREATE TABLE 3
-- CREATE INDEX 0
-- ALTER TABLE 6
-- CREATE VIEW 0
-- ALTER VIEW 0
-- CREATE PACKAGE 0
-- CREATE PACKAGE BODY 0
-- CREATE PROCEDURE 0
-- CREATE FUNCTION 0
-- CREATE TRIGGER 0
-- ALTER TRIGGER 0
-- CREATE COLLECTION TYPE 0
-- CREATE STRUCTURED TYPE 0
-- CREATE STRUCTURED TYPE BODY 0
-- CREATE CLUSTER 0
-- CREATE CONTEXT 0
-- CREATE DATABASE 0
-- CREATE DIMENSION 0
-- CREATE DIRECTORY 0
-- CREATE DISK GROUP 0
-- CREATE ROLE 0
-- CREATE ROLLBACK SEGMENT 0
-- CREATE SEQUENCE 0
-- CREATE MATERIALIZED VIEW 0
-- CREATE MATERIALIZED VIEW LOG 0
-- CREATE SYNONYM 0
-- CREATE TABLESPACE 0
-- CREATE USER 0
--
-- DROP TABLESPACE 0
-- DROP DATABASE 0
--
-- REDACTION POLICY 0
--
-- ORDS DROP SCHEMA 0
-- ORDS ENABLE SCHEMA 0
-- ORDS ENABLE OBJECT 0
--
-- ERRORS 0
-- WARNINGS 0
✔ chapter 03. SQL 기초
- 주문 하나 당 책 하나
- 하나의 책은 여러번 주문 가능
- 책-주문 1:N
- 책-고객 M:N
🔽
-
용어 숙지
🔽 -
행 (row) = 레코드 (record)= 튜플 (tuple)
- 행의 수 = 카디널리티 = 기수 = 대응수
-
열 (column) = 속성 = 필드
- 열의 수 = 디그리 (degree) = 차수
-
sql과 일반 프로그래밍 언어 차이점
🔽
sql 기능에 따른 분류
- 🔽
- grant: 권한 부여
- revoke: 권한 회수
- 스키마(구조)를 건드리는건 ddl
- 인스턴스(데이터)를 건드리는건 dml
- insert, delete는 행 단위로 작업
🔽 데이터 정의어와 데이터 조작어의 주요 명령어
+) sql 연산자 이용해서 주가 지수 산출 등 기능 다양화 가능
데이터 조작어 - 검색
- 집합
select *
from book
publisher in ('굿스포츠', '대한미디어');
=
select *
from book
publisher = '굿스포츠' or publisher = '대한미디어';
-
패턴
🔽 와일드 문자의 종류
-
is null, is not null
SELECT FROM Customer WHERE phone IS NULL;
SELECT FROM Customer WHERE phone IS NOT NULL;
+) having절과 order by 절 차이점
- HAVING 절은 WHERE 절과 비슷하지만 그룹 전체 즉, 그룹을 나타내는 결과 집합의 행에만 적용된다는 점에서 차이가 있습니다. 반면, WHERE 절은 개별 행에 적용됩니다. 쿼리에는 WHERE 절과 HAVING 절이 모두 포함될 수 있습니다.
집계함수 종류
🔽 집계함수 종류
-
집계 함수는 여러 행으로부터 하나의 결괏값을 반환하는 함수이다. SELECT 구문에서만 사용되며, 이전에 다룬 기본 함수들이 행(row)끼리 연산을 수행했다면, 집계 함수는 열(column)끼리 연산을 수행한다.
-
주로 평균, 합, 최대, 최소 등을 구하는 데 사용된다.
-
집계함수 distinct 사용 예시
SELECT COUNT(DISTINCT (saleprice))
FROM orders;SELECT sum(DISTINCT (saleprice))
FROM orders;
- count 시 null 포함 유무
-> null 제외 개수 세기
select count(phone) from customer;
-> null 포함 개수 세기
select count(*) from customer;
- having 절과 where 절 차이점
HAVING 절은 WHERE 절과 비슷하지만 그룹 전체 즉, 그룹을 나타내는 결과 집합의 행에만 적용된다는 점에서 차이가 있습니다.
반면, WHERE 절은 개별 행에 적용됩니다. 쿼리에는 WHERE 절과 HAVING 절이 모두 포함될 수 있습니다.
- sql 실습
🔽
CREATE TABLE Book (
bookid NUMBER(2) PRIMARY KEY,
bookname VARCHAR2(40),
publisher VARCHAR2(40),
price NUMBER(8)
);
CREATE TABLE Customer (
custid NUMBER(2) PRIMARY KEY,
name VARCHAR2(40),
address VARCHAR2(50),
phone VARCHAR2(20)
);
CREATE TABLE Orders (
orderid NUMBER(2) PRIMARY KEY,
custid NUMBER(2) REFERENCES Customer(custid),
bookid NUMBER(2) REFERENCES Book(bookid),
saleprice NUMBER(8) ,
orderdate DATE
);
-- Book, Customer, Orders 데이터 생성
INSERT INTO Book VALUES(1, '축구의 역사', '굿스포츠', 7000);
INSERT INTO Book VALUES(2, '축구아는 여자', '나무수', 13000);
INSERT INTO Book VALUES(3, '축구의 이해', '대한미디어', 22000);
INSERT INTO Book VALUES(4, '골프 바이블', '대한미디어', 35000);
INSERT INTO Book VALUES(5, '피겨 교본', '굿스포츠', 8000);
INSERT INTO Book VALUES(6, '역도 단계별기술', '굿스포츠', 6000);
INSERT INTO Book VALUES(7, '야구의 추억', '이상미디어', 20000);
INSERT INTO Book VALUES(8, '야구를 부탁해', '이상미디어', 13000);
INSERT INTO Book VALUES(9, '올림픽 이야기', '삼성당', 7500);
INSERT INTO Book VALUES(10, 'Olympic Champions', 'Pearson', 13000);
INSERT INTO Customer VALUES (1, '박지성', '영국 맨체스타', '000-5000-0001');
INSERT INTO Customer VALUES (2, '김연아', '대한민국 서울', '000-6000-0001');
INSERT INTO Customer VALUES (3, '장미란', '대한민국 강원도', '000-7000-0001');
INSERT INTO Customer VALUES (4, '추신수', '미국 클리블랜드', '000-8000-0001');
INSERT INTO Customer VALUES (5, '박세리', '대한민국 대전', NULL);
-- 주문(Orders) 테이블의 책값은 할인 판매를 가정함
INSERT INTO Orders VALUES (1, 1, 1, 6000, TO_DATE('2014-07-01','yyyy-mm-dd'));
INSERT INTO Orders VALUES (2, 1, 3, 21000, TO_DATE('2014-07-03','yyyy-mm-dd'));
INSERT INTO Orders VALUES (3, 2, 5, 8000, TO_DATE('2014-07-03','yyyy-mm-dd'));
INSERT INTO Orders VALUES (4, 3, 6, 6000, TO_DATE('2014-07-04','yyyy-mm-dd'));
INSERT INTO Orders VALUES (5, 4, 7, 20000, TO_DATE('2014-07-05','yyyy-mm-dd'));
INSERT INTO Orders VALUES (6, 1, 2, 12000, TO_DATE('2014-07-07','yyyy-mm-dd'));
INSERT INTO Orders VALUES (7, 4, 8, 13000, TO_DATE( '2014-07-07','yyyy-mm-dd'));
INSERT INTO Orders VALUES (8, 3, 10, 12000, TO_DATE('2014-07-08','yyyy-mm-dd'));
INSERT INTO Orders VALUES (9, 2, 10, 7000, TO_DATE('2014-07-09','yyyy-mm-dd'));
INSERT INTO Orders VALUES (10, 3, 8, 13000, TO_DATE('2014-07-10','yyyy-mm-dd'));
CREATE TABLE Imported_Book (
bookid NUMBER ,
bookname VARCHAR(40),
publisher VARCHAR(40),
price NUMBER(8)
);
INSERT INTO Imported_Book VALUES(21, 'Zen Golf', 'Pearson', 12000);
INSERT INTO Imported_Book VALUES(22, 'Soccer Skills', 'Human Kinetics', 15000);
COMMIT;
----------------------------------
SELECT phone
FROM Customer
WHERE name='김연아';
----------------------------------쿼리문 정리
- 공부법: 쿼리문을 보고 질의문을 추측할 수 있어야함.
- 질의 3-1 모든 도서의 이름과 가격을 검색하시오.
SELECT bookname, price
FROM Book;- 질의 3-1 변형 모든 도서의 가격과 이름을 검색하시오.
SELECT price, bookname
FROM Book;- 질의 3-2 모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오.
SELECT bookid, bookname, publisher, price
FROM Book;SELECT *
FROM Book;- 질의 3-3 도서 테이블에 있는 모든 출판사를 검색하시오.
SELECT publisher
FROM Book;- 질의 3-3* 중복 제거시 distinct 키워드 사용
SELECT DISTINCT publisher
FROM Book;- 질의 3-4 가격이 20000원 미만인 도서를 검색하시오.
SELECT *
FROM Book
WHERE price < 20000;- 질의 3-5 가격이 10000원 이상 20000 이하인 도서를 검색하시오.
SELECT *
FROM Book
WHERE price BETWEEN 10000 AND 20000;SELECT *
FROM Book
WHERE price >= 10000 AND price <= 20000;- 질의 3-6 출판사가 '굿스포츠' 혹은 '대한미디어' 인 도서를 검색하시오.
SELECT *
FROM Book
WHERE publisher IN ('굿스포츠', '대한미디어');- 질의 3-7 '축구의 역사' 를 출간한 출판사를 검색하시오.
select bookname, publisher
from book
where bookname like '축구의 역사' ; - 질의 3-8 도서이름에 '축구'가 포함된 출판사를 검색하시오.
select bookname, publisher
from book
where bookname like '%축구%'; - 질의 3-9 도서이름의 왼쪽 두 번째 위치에 '구'라는 문자를 갖는 도서를 검색하시오.
SELECT *
FROM Book
WHERE bookname LIKE '_구%';- 질의 3-10 축구에 관한 도서 중 가격이 20000원 이상인 도서를 검색하시오.
SELECT *
FROM Book
WHERE bookname LIKE '%축구%' AND price >= 20000;- 질의 3-11 출판사가 '굿스포츠' 혹은 '대한미디어' 인 도서를 검색하시오.
SELECT *
FROM Book
WHERE publisher = '굿스포츠' OR publisher = '대한미디어'; - 질의 3-12 도서를 이름순으로 검색하시오.
SELECT *
FROM Book
ORDER BY bookname;- 질의 3-13 도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오.
SELECT *
FROM Book
ORDER BY price, bookname;- 질의 3-14 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 검색하시오.
SELECT *
FROM Book
ORDER BY price DESC, bookname ASC;- 질의 3-15 고객이 주문한 도서의 총 판매액을 구하시오.
SELECT SUM(saleprice)
FROM Orders;SELECT SUM(saleprice) AS 총매출
FROM Orders;- 질의 3-16 2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오.
SELECT SUM(saleprice) AS 총매출
FROM Orders
WHERE custid = 2;- 질의 3-17 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오
SELECT SUM(saleprice) AS Total,
AVG(saleprice) AS Average,
MIN(saleprice) AS Minimum,
MAX(saleprice) AS Maximum
FROM Orders;- 질의 3-18 마당 서점의 도서 판매 건수를 구하시오.
SELECT COUNT(*)
FROM Orders;- 질의 3-19 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오.
SELECT custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액
FROM Orders
GROUP BY custid;- 질의 3-20 가격이 8000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량을 구하시오. 단, 가격이 8000원 이상인 도서를 두 권 이상 구매한 고객만 구한다.
SELECT custid, COUNT(*) AS 도서수량
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING count(*) >= 2;
heejoojeon@daou.co.kr