query-5(테이블복사, group by, having, 집계함수).sql
-- 테이블을 복사하는 방법
use sqldb;
-- buy의 데이터 전부를 쿼리를 해서 새로운 테이블인 buy_copy로 복사함.
drop table if exists buy_copy;
create table buy_copy(
select *
from buy
);
desc buy;
desc buy_copy;
-- 테이블 복사를 하게 되더라도, PK, FK등의 제약조건은 복사가 되지 않음.
select *
from buy_copy;
-- 기본적 쿼리문 순서(매우 중요함)
-- 무조건 아래와 같은 순서로 작성을 해야한다.
-- select...
-- from...
-- where...
-- group by...
-- having...
-- order by...
-- 고객이 구매한 건수를 확인하는 쿼리문.
-- 문제는 중복되는게 많음. 집계가 안되어 보기에 어려움.
-- group by절을 이용하면 편리함.
select userId, amount
from buy
order by userId;
-- 고객별로 구매한 건수가 한 눈에 들어옴.
-- sum()은 집계함수임. group by를 할때 userId로 하겠다는 의미임.
-- 집계함수류가 나오면 무조건 group by절이 들어가야함.
-- 현업에서 정말 많이 사용함.
select userId, sum(amount)
from buy
group by userId
order by sum(amount) desc;
select userId as '아이디', sum(amount) as '구매 건수'
from buy
group by userId
order by sum(amount);
select userId '아이디1', sum(amount) '구매 건수1'
from buy
group by userId
order by sum(amount);
-- 총 구매액을 집계 해보자
-- 총 구매액은 총구매수량에 * 단가가 됨
select *
from buy;
-- 고객별로 총구매액을 기준으로 내림차순으로 정렬하는 쿼리문임.
select userId as '아이디', sum(price * amount) as '총 구매액'
from buy
group by userId
order by sum(price * amount) desc;
-- 고객별로 평균 구매갯수를 알아보는 쿼리문을 작성하시오.
select userId as '아이디', avg(amount) as '평균 구매갯수'
from buy
group by userId
order by avg(amount) desc;
-- 모든 고객을 대상으로 평균 구매갯수를 알아보는 쿼리문.
select avg(amount)
from buy;
-- max(), min()
select name, height
from user;
-- 지금 결과는 name별로 group by 했기 때문에 10개의 데이터가 다 나옴.
select name, max(height), min(height)
from user
group by name;
-- 서브쿼리 이용하면 적절히 원하는 값을 도출할수 있음.
select name, height
from user
where height = (
select max(height)
from user
) or height = (
select min(height)
from user
);
-- 휴대폰이 있는 사람
select *
from user;
select count(*)
from user
where mobile1 is not null;
#------------------------------------------------------------------
use employees;
select *
from employees;
select count(*)
from employees;
#------------------------------------------------------------------
use sqldb;
-- 총 구매액으로 내림차순으로 정렬하시오.
select userId as '아이디', sum(price * amount) as '총 구매액'
from buy
group by userId
order by sum(price * amount) desc;
-- 총구매액이 1000만원 이상만 보고싶다면 어떻게 햐야 될까요?
-- having : group by의 조건절
select userId as '아이디', sum(price * amount) as '총 구매액'
from buy
group by userId
having sum(price * amount) > 1000
order by sum(price * amount) desc;
-- with rollup
-- 부분별로 소합계를 내어주고 마지막에 총합계를 보여줌.
select num, groupname, sum(price * amount)
from buy
group by groupname, num
with rollup;
query-6(DML,DDL,DCL).sql
-- sql의 종류
-- 1.DML(Data Manipulation Language) : 테이블의 데이터를 조작
-- 취소도 가능하다. rollback이란 키워드로 가능함.
-- ex) select, insert, delete, update
-- 2.DDL(Data Definition Language) : 테이블의 스키마를 정의,수정
-- 취소가 안됨. 실행하면 바로 물리적 저장공간 바로 적용됨.
-- ex) create, drop, alter, truncate(테이블구조를 구대로 남긴채 데이터를 다 지움.
-- 3. DCL (data Control Language) : 접근권한, 권한정의하는 기능
-- 특정사용자에게 어떤 권한 부여하거나 권한을 금지할때 사용하는 구문
-- grant, revoke
drop database if exists samil;
create database samil;
use samil;
drop table if exists book;
create table book(
bookid integer primary key,
bookname varchar(40),
publisher varchar(40),
price integer
);
drop table if exists customer;
create table customer(
custid integer primary key,
name varchar(40),
address varchar(50),
phone varchar(20)
);
drop table if exists orders;
create table orders(
orderid integer primary key,
custid integer,
bookid integer,
saleprice integer,
orderdate date,
foreign key(custid) references customer(custid),
foreign key(bookid) references book(bookid)
);
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, 'Olypic 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);
insert into orders values (1, 1, 1, 6000, str_to_date('2014-07-01','%Y-%m-%d'));
insert into orders values (2, 1, 3, 21000, str_to_date('2014-07-03','%Y-%m-%d'));
insert into orders values (3, 2, 5, 8000, str_to_date('2014-07-03','%Y-%m-%d'));
insert into orders values (4, 3, 6, 6000, str_to_date('2014-07-01','%Y-%m-%d'));
insert into orders values (5, 4, 7, 20000, str_to_date('2014-07-01','%Y-%m-%d'));
insert into orders values (6, 1, 2, 12000, str_to_date('2014-07-01','%Y-%m-%d'));
insert into orders values (7, 4, 8, 13000, str_to_date('2014-07-01','%Y-%m-%d'));
insert into orders values (8, 3, 10, 12000, str_to_date('2014-07-01','%Y-%m-%d'));
insert into orders values (9, 2, 10, 7000, str_to_date('2014-07-01','%Y-%m-%d'));
insert into orders values (10, 3, 8, 13000, str_to_date('2014-07-01','%Y-%m-%d'));
select *
from book;
select *
from customer;
select *
from orders;
-- 1) 가격이 20,000원 미만인 도서를 검색하시오.
select *
from book
where price < 20000;
-- 2) 가격이 10,000원 이상 20,000원 이하인 도서를 검색하시오.
select *
from book
where price between 10000 and 20000;
select *
from book
where price >= 10000 and price <= 20000;
-- 3) 출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하시오.
select *
from book
where publisher in('굿스포츠', '대한미디어');
-- 출판사가 '굿스포츠' 혹은 '대한미디어'가 아닌 도서를 검색하시오.
select *
from book
where publisher not in('굿스포츠', '대한미디어');
-- 4) '축구의 역사'를 출간한 출판사를 검색하시오.
select bookname,publisher
from book
where bookname like '축구의 역사';
-- 5) 도서이름에 '축구'가 포함된 출판사를 검색하시오.
select bookname,publisher
from book
where bookname like '%축구%';
-- 6) 도시이름의 왼쪽 두번째 위치에 '구'라는 문자열을 갖는 도서를 검색하시오.
select bookname,publisher
from book
where bookname like '_구%';
-- 7) 축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색하시오.
select bookname,publisher
from book
where bookname like '%축구%' and price >= 20000;
-- 8) 도서를 이름순으로 검색하시오.
select *
from book
order by bookname asc;
-- 9) 도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오.
select *
from book
order by price, bookname;
-- 10) 고객이 주문한 도서의 총 판매액을 구하시오.
desc orders;
select sum(saleprice) as 총매출
from orders;
-- 11) 2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오.
select sum(saleprice)
from orders
where custid = 2;
-- 12) 고객이 주문한 도시의 총 판매액, 평균값, 최저가, 최고가를 구하시오.
select sum(saleprice) as 총판매액,
avg(saleprice) as '평균값',
min(saleprice) as '최저가',
max(saleprice) as '최고가'
from orders;
-- 13) 삼일서점의 도서 판매 건수를 구하시오.
select count(*)
from book;
-- 14) 고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오.
select custid as 고객번호, count(*) as '도서수량', sum(saleprice) as '총판매액'
from orders
group by custid;
-- 15) 가격이 8000원 이상인 도서를 구매한 고객에 대하여
-- 고객별 주문도서의 총수량을 구하시오. 단, 두권 이상 구매한 고객만 구하라
select custid, count(*) as '도서수량'
from orders
where saleprice >= 8000
group by custid
having count(*) >= 2;
-- 16) 가장 비싼 도서의 이름을 보이시오.
desc book;
select bookname
from book
where price=(
select max(price)
from book
);
desc orders;
-- 17) 도서를 구매한 적이 있는 고객의 이름을 검색하시오.
desc orders;
desc customer;
select name
from customer
where custid in(
select custid
from orders
);
-- 18) 대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오.
select name
from customer
where custid in(
select custid
from orders
where bookid in(
select bookid
from book
where publisher = '대한미디어'
)
);
-- 19) 다음과 같은 속성을 가진 NewBook 테이블을 생성하시오.
-- 정수형은 integer를 사용하며 문자형은 가변형 문자타입인 varchar를 사용한다.
-- bookid(도서번호)-integer
-- bookname(도서이름)-varchar(20)
-- publisher(출판사)-varchar(20)
-- price(가격)-integer
drop table if exists NewBook;
create table NewBook(
bookid integer,
bookname varchar(20) not null,
publisher varchar(20),
price integer default 10000,
primary key(bookid)
);
