[DB]_23.02.10
- 프로젝트 설명
- ER 은 목요일(16일)에 작성해서 제출
- 주관식 출제
- 쿼리문의 의미를 작성하라는 문제 출제 가능 (어떤 값을 요구하는지 주관식 출제)
두 개 이상 테이블에서 SQL 질의
- CUSTOMER 테이블을 ORDERS 테이블과 조건 없이 연결
- CUSTOMER 와 ORDERS 테이블의 카티전 프로덕트 결과, 투플의 개수는 고객이 다섯 명이고 주문이 열 개 이므로 5x10 해서 50이 됨
SELECT *
FROM CUSTOMER, ORDERS;- 질의 3-21 고객과 고객의 주문에 관한 데이터를 연결하여 보이시오.
select *
from customer, orders
where customer.custid=orders.custid; - 질의 3-22 고객과 고객의 주문에 관한 데이터를 고객 번호 순으로 정렬하여 보이시오.
select *
from customer, orders
where customer.custid=orders.custid
order by customer.custid;- 질의 3-23 고객의 이름과 고객이 주문한 도서의 판매 가격을 검색하시오.
select name, saleprice
from customer, orders
where customer.custid=orders.custid; - 질의 3-24 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객명으로 정렬하시오.
🔽
- 다중값 문제 때문에
group by 시 집계함수 속성인 name 를 기준으로 삼아야함
(그 외 custid 사용시 에러 발생)
select name, sum(saleprice)
from customer, orders
where customer.custid=orders.custid
group by customer.name
order by customer.name; WHERE, HAVING, GROUP BY 비교
🔽
-
GROUP BY는 컬럼들의 값을 그룹으로 만들어(중복제거) 그 그룹들의 집계를 낼때 사용한다.
-
컬럼에 어떠한 데이터들이 있는지 확인만 하려고 한다면 DISTINCT를 이용하여 중복제거를 해주는게 좋다.
-
(중복 값이 많을때는 DISTINCT에 비해 GROUP BY가 성능이 떨어질 수 있음)
-
HAVING은 GROUP BY한 그룹들에 조건을 적용할 때 사용한다.
-
WHERE과HAVING차이점WHERE: 기본적으로모든 행에 대해서 조건을 적용HAVING: GROUP BY절과 같이 사용하여야 되고그룹화된 결과들에 조건을 적용
조인
select customer.name, saleprice
from customer left outer join orders
on customer.custid=orders.custid;
select customer.name, saleprice
from customer right outer join orders
on customer.custid=orders.custid;
select customer.name, saleprice
from customer full outer join orders
on customer.custid=orders.custid; - 질의 3-25 고객이 이름과 고객이 주문한 도서의 이름을 구하시오.
select customer.name, book.bookname
from customer, orders, book
where customer.custid=orders.custid
and orders.bookid=book.bookid; - 질의 3-26 가격이 20000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.
select customer.name, book.bookname
from customer, orders, book
where customer.custid=orders.custid
and orders.bookid=book.bookid and book.price=20000; - 질의 3-27 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매 가격을 구하시오.
select customer.name, saleprice
from customer left outer join orders
on customer.custid=orders.custid; - 질의 3-28 가장 비싼 도서의 이름을 보이시오.
select bookname
from book
where price=(select max(price) from book); - 집합 연산
- 질의 3-32) 도서를 주문하지 않은 고객의 이름을 보이시오
select name
from customer
minus
select name
from customer
where custid in (select custid from orders); 연습문제 1번 풀이
- 마당서점의 고객이 요구하는 다음 질문에 대해 SQL문을 작성하시오.
- (1) 도서번호가 1인 도서의 이름
- select bookname from book where bookid=1;- (2) 가격이 20,000원 이상인 도서의 이름
- select bookname from book where price>=20000;- (3) 박지성의 총 구매액 (박지성의 고객 번호는 1번으로 놓고 작성)
- select sum(saleprice)
from customer c, orders o
where c.custid=o.cutid and c.name='박지성'; - (4) 박지성이 구매한 도서의 수 (박지성의 고객 번호는 1번으로 놓고 작성)
- select count(*)
from orders where custid=1;
- select count(*)
from customer c, orders o
where c.custid = o.custid and c.name = '박지성';- (5) 박지성이 구매한 도서의 출판사 수
- select count(distinct publisher)
from customer c, orders o, book b
where (c.custid=o.custid)
and (b.bookid=o.bookid)
and (c.name='박지성');- (6) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
- select bookname, price, price-saleprice
from customer c, orders o, book b
where (c.custid=o.custid)
and (b.bookid=o.bookid)
and (c.name='박지성');- (7) 박지성이 구매하지 않은 도서의 이름
- select bookname from book, orders, customer
minus
select bookname from book b, orders o, customer c
where (c.custid=o.custid)
and (b.bookid=o.bookid)
and (c.name='박지성');연습문제 2번 풀이
- 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL문을 작성하시오.
- (1) 마당서점 도서의 총 개수
- select count(*)
from book; - (2) 마당서점에 도서를 출고하는 출판사의 총 개수(null 제외)
- select count(distinct publisher)
from book; +) count 시 null 포함 유무
-> null 제외 개수 세기 select count(publisher) from book;
-> null 포함 개수 세기 select count(*) from book;
- (3) 모든 고객의 이름, 주소
- select name, address
from customer; - (4) 2020년 7월 4일 ~ 7월 7일 사이에 주문받은 도서의 주문번호
- select orderid, orderdate
from orders
where orderdate
between to_date('2020-07-04','YYYY-MM-DD')
and to_date('2020-07-07','YYYY-MM-DD');- +) 오라클에서 문자열을 날짜형 데이터로 형 변환을 하기 위해서는 TO_DATE 함수를 사용하면 된다. TO_DATE("문자열", "날짜 포맷")
- (5) 2020년 7월 4일 ~ 7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호
- select orderid, orderdate
from orders
where orderdate
not between to_date('2020-07-04','YYYY-MM-DD')
and to_date('2020-07-07','YYYY-MM-DD');- (6) 성이 '김'씨인 고객의 이름과 주소
- select name, address
from customer
where name like '김%'; - (7) 성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소
- select name, address
from customer
where name like '김%아'; - (8) 주문하지 않은 고객의 이름
- select name
from book, orders, Customer
MINUS
select name
from book b, orders o, customer c
where (c.custid=o.custid) and (b.bookid=o.bookid);- (9) 주문 금액의 총액과 주문의 평균 금액
- select sum(saleprice), avg(saleprice)
from orders;- (10) 고객의 이름과 고객별 구매액
- select name, sum(saleprice)
from orders o, customer c
where o.custid=c.custid group by name;- +) GROUP BY는 컬럼 값을 그룹짓고(중복을 제거하고) 이에 대해 건수나 값의 합을 계산할 때 사용
- (11) 고객의 이름과 고객이 구매한 도서 목록
- select name, bookname
from customer c, orders o, book b
where (c.custid=o.custid) and (b.bookid=o.bookid)
연습문제 6번 풀이
where, having, group by 의미 차이 기억
[출제 형식]
이 질의의 의미는 무엇인가? 1,2,3,4 4지 선다
학생이 10명 이상 가입한 동아리에 대하여 동아리와 제출한 총 과제 수를 출력하시오.
select 동아리, sum(과제 수)
from 과제
group by 동아리
having sum(학생 수) >= 10;
-- 기본키는 학년과 동아리 WHERE
- SELECT * FROM 테이블 WHERE 조건절;
WHERE 은 기본적인 조건절로 항상 FROM 뒤에 위치하며 다양한 비교 연산자로 구체적인 조건을 줄 수 있다.
HAVING
-
SELECT * FROM 테이블 GROUP BY 필드 HAVING 조건절;
항상GROUP BY 뒤에 위치하며 WHERE 과 마찬가지로 다양한 비교 연산자로 조건을 줄 수 있다. -
둘 다 필드에 조건을 줄 수 있다는 것은 동일하나,
WHERE은 기본적으로모든 필드에 조건을 둘 수 있지만HAVING은 GROUP BY로그룹화 된 필드에 조건을 줄 수 있다. 또한, HAVING에서 조건을 줄 필드는 SELECT에 반드시 명시되어 있어야 한다. -
만약 두 조건절을 함께 사용한다면 아래와 같은 형태가 된다.
SELECT * FROM 테이블명 WHERE 조건절 GROUP BY 컬럼 HAVING 조건절;
연습문제 8번 풀이
r 테이블에서 중복 제거해서 1,2,3,5 4개이고, s 테이블 전체 1,1,2,4,6, 합집합해서 9개
🔽
create table 문
🔽
- create table 문 개념
- 속성의 데이터 타입 종류
- 질의 3-34) NEWBOOK 테이블을 생성하시오, 정수형은 NUMBER, 문자형은 가변형 문자타입인 VARCHAR2를 사용
CREATE TABLE NEWBOOK(
BOOKID NUMBER,
BOOKNAME VARCHAR2(20),
PUBLISHER VARCHAR2(20),
PRICE NUMBER); -
unique는 중복값 허용 안함
-
질의 3-35) 다음과 같은 속성을 가진 NEWCUSTOMER 테이블을 생성하시오.
CREATE TABLE NEWCUSTOMER(
CUSTID NUMBER PRIMARY KEY,
NAME VARCHAR2(40),
ADDRESS VARCHAR2(40),
PHONE VARCHAR2(30)); - 질의 3-36)
CREATE TABLE NEWORDERS(
ORDERID NUMBER,
CUSTID NUMBER NOT NULL,
BOOKID NUMBER NOT NULL,
SALEPRICE NUMBER,
ORDERDATE DATE,
PRIMARY KEY(ORDERID),
FOREIGN KEY(CUSTID) REFERENCES NEW CUSTOMER(CUSTID) ON DELETE CASCADE); - 질의 3-42) NEWBOOK 테이블을 삭제하시오
DROP TABLE NEWBOOK; - 질의 3-43) NEWCUSTOMER 테이블을 삭제하시오. 만약 삭제가 거절된다면 원인을 파악하고 관련된 테이블을 같이 삭제하시오. (힌트: NEWORDERS 테이블이 NEWCUSTOMER를 참조하고 있음)
DROP TABLE NEWCUSTOMER;
+) 쿼리문 실행 순서
🔽
- 질의 3-37) NEWBOOK 테이블에 VARCHAR2(13)의 자료형을 가진 ISBN 속성을 추가하시오.
ALTER TABLE NEWBOOK ADD ISBN VARCHAR2(13);- 질의 3-38) NEWBOOK 테이블의 ISBN 속성의 데이터 타입을 NUMBER형으로 변경하시오
ALTER TABLE NEWBOOK MODIFY ISBN NUMBER; - 질의 3-39) NEWBOOK 테이블의 ISBN 속성을 삭제하시오.
ALTER TABLE NEWBOOK DROP COLUMN ISBN; - 질의 3-40) NEWBOOK 테이블의 BOOKID 속성에 NOT NULL 제약 조건을 적용하시오
ALTER TABLE NEWBOOK MODIFY BOOKID NUMBER NOT NULL; - 질의 3-41) NEWBOOK 테이블의 BOOKID 속성을 기본키로 변경하시오
ALTER TABLE NEWBOOK ADD PRIMARY KEY(BOOKID); - 질의 3-44) BOOK테이블에 새로운 도서 '스포츠 의학'을 삽입하시오. 스포츠 의학은 한솔의학서적에서 출간했으며 가격은 90000원이다.
INSERT INTO BOOK(BOOKID, BOOKNAME, PUBLISHER, PRICE)
VALUES (11, '스포츠 의학', '한솔의학서적', 90000);- 질의 3-45) BOOK 테이블에 새로운 도서 '스포츠 의학'을 삽입하시오. 스포츠 의학은 한솔의학서적에서 출간했으며 가격은 미정이다.
INSERT INTO BOOK(BOOKID, BOOKNAME, PUBLISHER) VALUES (14, '스포츠 의학', '한솔의학서적`); - 질의 3-46) 수입 도서 목록(IMPORTED_BOOK)을 BOOK 이블에 모두 삽입하시오.
INSERT INTO BOOK(BOOKID, BOOKNAME, PRICE, PUBLISHER) SELECT BOOKID, BOOKNAME, PRICE, PUBLISHER FROM IMPORTED_BOOK; - 질의 3-47) CUSTOMER 테이블에서 고객번호가 5인 고객의 주소를 '대한민국 부산'으로 변경하시오.
UPDATE CUSTOMER SET ADDRESS='대한민국 부산' WHERE CUSTID=5; - 질의 3-48) CUSTOMER 테이블에서 박세리 고객의 주소를 김연아 고객의 주소로 변경하시오.
UPDATE CUSTOMER SET ADDRESS = (SELECT ADDRESS FROM CUSTOMER WHERE NAME='김연아') WHERE NAME='박세리'; - 질의 3-49) CUSTOMER 테이블에서 고객번호가 5인 고객을 삭제하시오
DELETE FROM CUSTOMER WHERE CUSTID=5; SELECT * FROM CUSTOMER; - 질의 3-50) 모든 고객을 삭제하시오
DELETE FROM CUSTOMER;
연습문제 4번 풀이
- 4-1) 새로운 도서 ('스포츠 세계', '대한미디어', 10000원)이 마당서점에 입고되었다. 삽입이 안 될 경우 필요한 데이터가 더 있는지 찾아보시오
INSERT INTO BOOK(BOOKNAME, PUBLISHER, PRICE)
VALUES('스포츠 세계', '대한미디어', '10000원');- 4-2) '삼성당'에서 출판한 도서를 삭제하시오.
DELETE FROM BOOK WHERE PUBLISHER='삼성당';- 4-3) '이상미디어'에서 출판한 도서를 삭제하시오, 삭제가 안 되면 원인을 생각해 보시오.
- 다른 테이블에서 PUBLISHER를 참조하고 있을 것으로 예상(
참조 무결성 제약조건이 위배)
- 다른 테이블에서 PUBLISHER를 참조하고 있을 것으로 예상(
DELETE FROM BOOK WHERE PUBLISHER='이상미디어';- 4-4) 출판사 '대한미디어'를 '대한출판사'로 이름을 바꾸시오
UPDATE BOOK
SET PUBLISHER='대한출판사'
WHERE PUBLISHER='대한미디어';- 4-5) (테이블 생성) 출판사에 대한 정보를 저장하는 테이블 Bookcompany(name, address,
,begin)를 생성하고자 한다. name은 기본키이며 VARCHAR(20), address는 VARCHAR(20),
begin은 DATE 타입으로 선언하여 생성하시오.
CREATE TABLE Bookcompany (
name VARCHAR(20) PRIMARY KEY,
address VARCHAR(20),
begin DATE);- 4-6) (테이블 수정) Bookcompany 테이블에 인터넷 주소를 저장하는 webaddress 속성을 VARCHAR(30)으로 추가하시오
ALTER TABLE Bookcompany
ADD webaddress VARCHAR(30);- 4-7) Bookcompany 테이블에 임의의 투플 name=한빛아카데미, address=서울시 마포구,begin=1993-01-01, webaddress=http://hanbit.co.kr을 삽입하시오.
INSERT INTO Bookcompany
(name, address, begin, webaddress)
VALUES
('한빛아카데미', '서울시 마포구',
'1993-01-01', 'http://hanbit.co.kr');
heejoojeon@daou.co.kr