25.03.27 sql + 연습문제

데이터 정의어 DDL - 테이블이나 관계 구조 생성에 사용 (create, alter, drop)

데이터 조작어 DML - 테이블에 데이터 검색, 삽입, 수정 등에 사용 (select, insert, delete, update) 

데이터 제어어 DCL - 데이터 사용 권한 관리에 사용 (grant, revoke)

 

 

select문

select 검색할 요소

테이블에서 검색 > from 테이블명

where 조건

 

김연아 고객의 전화번호를 찾으시오

> select phone from customer where name = '김연아' ;

 

distinct - 중복 제외

 

모든 도서의 이름과 가격을 검색하시오. 3-1

select bookname, price from book;

모든 도서의 가격과 이름을 검색하시오. 3-1-1

select price, bookname from book;

모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오. 3-2

select bookid, bookname, publisher, price from book; 

도서 테이블에 있는 모든 출판사를 검색하시오. 3-3

select publisher from book;

+ selecct distinct publisher from book; 중복제외

 

where 조건

가격이 20,000원 미만인 도서를 검색하시오. 3-4

select * from book where price < 20000;

가격이 10,000원 이상 20,000원 이하인 도서를 검색하시오. 3-5

select * from book where price between  10000 and 20000;

or

select * from book where price >= 10000 and price <= 20000;

 

출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서를 검색하시오. 3-6

select * from book where publisher in ('굿스포츠', '대한미디어');

출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’가 아닌 도서를 검색하시오.

select * from book where publisher not in ('굿스포츠', '대한미디어');

 

‘축구의 역사’를 출간한 출판사를 검색하시오. 3-7

select bookname, publisher from book where bookname = '축구의 역사';

or where bookname like '축구의 역사';

도서이름에 ‘축구’가 포함된 출판사를 검색하시오. 3-8

select bookname, publisher from book where bookname like '%축구%';

 

도서이름의 왼쪽 두 번째 위치에 ‘구’라는 문자열을 갖는 도서를 검색하시오. 3- 9

select * from book where bookname like '_구%';

축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색하시오. 3-10

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;

 

order by - 정렬 asc 오름차순(생략 가능)  / desc 내림차순

 

도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오. 3-13

select * from book order by price, bookname;

도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 검색한다.  3-14

select * from book order by price DESC, publisher ASC;

 

집계함수

sum(속성) 합계 / avg(속성) 평균 / min(속성) 최소값 / max(속성) 최대값 / count() 개수

null 인 속성을 제외하고 계산함

where 절에서 사용 불가능 / select 절 혹은 having 절에서 사용

 

고객이 주문한 도서의 총 판매액을 구하시오. 3-15

select sum(saleprice) from orders;

2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오. 3-16

select sum(saleprice) as '총 판매액' from orders where custid = 2;

고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오. 3-17

select sum(saleprice) as '총 판매액' , avg(saleprice) as '평균값', min(saleprice) as '최저가', max(saleprice) as '최고가'

from orders;

마당서점의 도서 판매 건수를 구하시오. 3-18

select count (*) from orders;

 

 

group by

같은 그룹 끼리 묶어서 표현할 때 사용

having

group by의 조건으로 그룹을 제한함 > 그룹에 대한 조건으로 where 절이 아닌 having 절에 작성

 

고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오. 3-19

select custid, count(*) as '도서수량', sum(saelprice) as '총 판매액' from orders group by custid;

가격이 8,000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총
수량을 구하시오. 단, 두 권 이상 구매한 고객만 구한다. 3-20

select custid, count(*) as 도서수량 from orders where price >= 8000

group by custid having count(*) >=2 ;

 

주의사항

group by로 그룹으로 묶은 후 select 절에 group by에서 사용한 속성과 집계함수만 나옴

ex) 

select custid, sum(saleprice) from orders group by custid;

select bookid, sum(saleprice) from orders group by custid;  // bookid 속성은 사용 불가능

 

where절과 having절이 같이 포함된 sql문은 검색 조건이 모호해 질 수 있음

having은 반드시 group by 절과 같이 사용하며 where 절보다 뒤에 작성해야함

+검색 조건에는 집계함수가 있어야 함

ex)

select custid, count(*) from orders where saleprice >+ 8000 group by custid having count(*) >=2;

 

내부 실행 순서

select custid, count(*) as '도서수량'  --  4

from orders                                        -- 1

where saleprice >= 8000                   -- 2

group by custid                                  -- 3 

having count(*) >=2                           -- 5

order by custid;                                  -- 6

 

마당서점의 고객이 요구하는 다음 질문에 대해 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 orders where custid = 1;

(4) 박지성이 구매한 도서의 수(박지성의 고객번호는 1번으로 놓고 작성)

select count(*) from orders where custid =1;

 

마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL 문을 작성하시오.

(1) 마당서점 도서의 총 개수

select count(*) from book;

(2) 마당서점에 도서를 출고하는 출판사의 총 개수
select count(distinct publisher) from book;

(3) 모든 고객의 이름, 주소

select name, address from customer;

(4) 2023년 7월 4일~7월 7일 사이에 주문 받은 도서의 주문번호
select bookid from orders where orderdate between 20230704 and 20230707;

(5) 2023년 7월 4일~7월 7일 사이에 주문 받은 도서를 제외한 도서의 주문번호
(6) 성이 ‘김’ 씨인 고객의 이름과 주소
(7) 성이 ‘김’ 씨이고 이름이 ‘아’로 끝나는 고객의 이름과 주소

 

 

 

조인 

고객과 고객의 주문에 관한 데이터를 모두 보이시오. 3-21

select orderid, c.custid, name, b.bookid 
from orders as o, customer as c , book as b
where o.custid = c.custid
and o.bookid = b.bookid;

 

고객과 고객의 주문에 관한 데이터를 고객번호 순으로 정렬하여 보이시오.  3-22

select * 
from customer c, orders o
where c.custid = o.custid
order by c.custid;

 

고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오. 3-23

select name, saleprice 
from customer c, orders o
where c.custid = o.custid;

 

고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오. 3-24

select name, sum(saleprice) 
from customer c , orders o
where c.custid = o.custid
group by c.name
order by c.name;

 

고객의 이름과 고객이 주문한 도서의 이름을 구하시오. 3-25

select c.name, b.bookname
from customer c, book b, orders o 
where c.custid = o.custid
and o.bookid = b.bookid;

 

가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오. 3-26

select c.name, b.bookname
from customer c, book b, orders o
where c.custid = o.custid and o.bookid = b.bookid and b.price = 20000;

조인 먼저 하고 조건 정하기

 

도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의판매가격을 구하시오.  3-27

select c.name, saleprice

from customer c left join orders o

on c.custid = o.custid;

 

서브쿼리

가장 비싼 도서의 이름을 보이시오.3-28

select bookname from book where price = (select max(price) from book);

 

도서를 구매한 적이 있는 고객의 이름을 검색하시오. 3-29

select name from customer where custid in (select custid from orders);

 

대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오. 3-30

select name from customer where custid in 
(select custid from orders where bookid in 
(select custid from book where publisher = '대한미디어'));

 

부속질의

상관 부속질의는 상위 부속질의의 투플을 이용해 부속질의를 계산함 > 상위와 하위가 독립적이지 않고 관계를 맺고 있음

상관쿼리는 서브쿼리 단독으로 실행 불가능

실행 순서 - 메인 > 서브

 

연습문제

매출테이블에서 각 브랜드별 제품 판매가 각 브랜드별 평균판매보다 높은 매출을 구하시오.(demo_sales.sql 실행)

SELECT *
FROM sales AS s1
WHERE s1.saleprice > ( SELECT AVG(s2.saleprice)
FROM sales AS s2
WHERE s1.brand = s2.brand);

 

union - 합집합 / minus - 차집합 / intersect - 교집합

대한민국에서 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 보이시오. 3-32

elect name from customer where address like '대한민국%'  union
select name from customer where custid in (select distinct custid from orders);

 

union 중복 제외하고 보여줌 / union all 중복제외 하지 않고 전부 보여줌

 

차집합

select * from customer c left outer join orders o
on c.custid = o.custid
where o.custid is null;

 

교집합

select * from customer c inner join orders o
on c.custid = o.custid;

 

 

exists - 조건에 맞는 투플이 조냊하면 결과에 포함시킴 > 어떤 행이 조건에 만족하면 참

not exists - 모든 행이 조건에 만족하지 않을 때만 참

 

주문이 있는 고객의 이름과 주소를 보이시오. 3-33

select name, address from customer c where exists
(select * from orders o where c.custid = o.custid); 

 

 

 

in의 경우

서브쿼리 먼저 실행

main에서 행 하나 가져옴

행의 값이 포함되는지 비교

일치하면 행 출력

반복

 

 

exists 의 경우

main에서 행 하나 가져옴

해당 행에 대해 서브쿼리 실행해 결과가 존재하면 true > 행에대한 select 실행 출력

 

in - 값 직접 비교 > 테이블에 행 많을 수록 느려짐 // 직관적

exists - 값 true/false 비교 후 찾으면 바로 중지 ( in보다 빠름) // 직관적

join - 가장 빠름, 비직관적 / join 하는 테이블에 동일한 값이 있을 경우 여러행 반환

>>

조회하는 데이터 적음 = in

조회하는 데이터 많음 = exists

빠른 속도 필요 = inner join 

 

마당서점의 고객이 요구하는 다음 질문에 대해 SQL 문을 작성하시오.
(5) 박지성이 구매한 도서의 출판사 수

select publisher from orders o join customer c join book b
on o.custid = c.custid and o.bookid = b.bookid
where name = '박지성';

(6) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이

select bookname, price, saleprice, (saleprice - price) as difference from orders o join customer c join book b
on o.custid = c.custid and o.bookid = b.bookid
where name = '박지성';

(7) 박지성이 구매하지 않은 도서의 이름

select distinct bookname from orders o join customer c join book b
on o.custid = c.custid and o.bookid = b.bookid
where c.name not in ('박지성');

 

마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL 문을 작성하시오.
(8) 주문하지 않은 고객의 이름(부속질의 사용)

select name from customer c where not exists
(select * from orders o where c.custid = o.custid);
(9) 주문 금액의 총액과 주문의 평균 금액

select sum(saleprice) as '주문총액' , avg(saleprice) as '주문 평균 금액' from orders;
(10) 고객의 이름과 고객별 구매액

select c.name, sum(o.saleprice) from customer c  join orders o
on o.custid = c.custid
group by c.name;
(11) 고객의 이름과 고객이 구매한 도서 목록

select c.name, b.bookname from orders o join customer c join book b 

on b.bookid = o.bookid;
(12) 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문

select * from orders o join book b on o.bookid = b.bookid
where (b.price - o.saleprice) = (select max(price - saleprice) from orders o join book b on o.bookid = b.bookid);
(13) 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름

select name, avg(saleprice) 
from orders o join customer c 
on o.custid = c.custid
group by name
having avg(saleprice) > 11800;

 

 

//73~90 ddl일단 넘어가기

 

insert

테이블에 하나 또는 여러 행의 새로운 투플 삽입하는 명령어

기본문법

insert into 테이블명 values 값리스트

>>속성의 순서와 타입을 지켜야 함

 

Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하시오. 스포츠 의학은 한솔의학서적

에서 출간했으며 가격은 90,000원이다. 3-44

insert into book (bookname, publisher, price) 
values ('스포츠 의학', '한솔의학서적', 90000);

 

Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하시오. 스포츠 의학은 한솔의학
서적에서 출간했으며 가격은 미정이다. 3-45

insert into book (bookid, bookname, publisher) 
values (11, '스포츠 의학', '한솔의학서적');

 

대량삽입(bulk insert) - 여러개의 투플을 한꺼번에 삽입

서브쿼리를 values 대신 사용 가능

insert into book(bookid, bookname, price, publisher)
select bookid, bookname, price, publisher from imported_book;

>> 21 'zen golf' 'person' 12000 / 22 'soccer skills' 'human Kinetics' 15000

 

 

update

특정 속성 값을 수정하는 명령어

기본문법

update 테이블명 

set 속성이름1 = 값1 ...

where 검색조건

 

Customer 테이블에서 고객번호가 5인 고객의 주소를 ‘대한민국 부산’으로 변경하시오 3- 47

update customer set address = '대한민국 부산' where custid = 5;

 

다른 테이블의 속성 값 이용 가능 > 서브쿼리를 set / where 절에 사용 가능

Book 테이블에서 14번 ‘스포츠 의학’의 출판사를 imported_book 테이블의 21번
책의 출판사와 동일하게 변경하시오. 3-48

update book

set publisher = ( select publisher from imported_book where bookid = 21)

where bookid = 14;

 

 

delete

기존 투플 삭제함 > where 절에 서브쿼리 사용 가능

기본 문법

delete from 테이블명

where 검색조건

 

Book 테이블에서 도서번호가 11인 도서를 삭제하시오. 3-49

delete from book where bookid = 11;

모든 고객을 삭제하시오. 3-50

delete from customer; 

 

마당서점에서 다음의 심화된 질문에 대해 SQL 문을 작성하시오.
(1) 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름

SELECT distinct name 
FROM customer c2 
JOIN orders o ON c2.custid = o.custid 
JOIN book b ON o.bookid = b.bookid 
WHERE b.publisher IN (
    SELECT b.publisher 
    FROM book b 
    JOIN orders o ON b.bookid = o.bookid 
    JOIN customer c1 ON c1.custid = o.custid 
    WHERE c1.name = '박지성'
) ;
(2) 두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름
(3) 전체 고객의 30% 이상이 구매한 도서