DB - 03

Join

join 없이 실행하면 아래와 같이 총 40개의 데이터가 리턴이 됨. MMU 4개 * member 10개 // 데카르트 곱, 의미없는 값임

SELECT *
FROM buy b JOIN member m 
WHERE b.mem_id = 'MMU';

join 예제 1

그룹들이 구매한 책 이름과 그룹에 정보(주소, 연락처)를 검색하시오

SELECT m.mem_id, mem_name, b.prod_name, m.addr, 
		CONCAT(phone1, '-', phone2) AS "연락처"
FROM member m JOIN buy b
	ON m.mem_id = b.mem_id
ORDER BY 1;

join 예제 2

경기에 사는 그룹들이 구매한 상품을 리턴

1. 서브쿼리 이용해서 문제해결

2. join 이용해서 문제해결

서브쿼리와 join 둘 중 본인에게 맞는것 선택해서 하면 됨.
굳이 따지자면 이 문제는 서브쿼리를 이용하는것이 조금 더 나음.

연습문제

테이블, 데이터

create table Customers(
cid char(10) primary key,
cname char(10),
city char(10),
discnt decimal(4,2)
);

create table Agents(
aid char(10) primary key,
aname char(10),
acity char(10),
apercent int
);

create table Products(
pid char(10) primary key,
pname char(10),
pcity char(10),
quantity int,
price int
);

create table orders(
 ordno char(10) primary key,
 month char(10),
 cid char(10),
 aid char(10),
 pid char(10),
 qty int,
 wons int,
 /*제약*/
 constraint fk_cid foreign key(cid) 
 references customers(cid),
 constraint fk_aid foreign key(aid)
 references agents(aid),
 constraint fk_pid foreign key(pid)
 references products(pid)
);

insert Customers values('c001','김동길','서울',10.00);
insert Customers values('c002','백남호','대전',12.00);
insert Customers values('c003','신상훈','대전',8.00);
insert Customers values('c004','이재성','서울',8.00);
insert Customers values('c006','이재성','충주',0.00);

insert Agents values('a01','김수현','부산',6);
insert Agents values('a02','김동기','광주',6);
insert Agents values('a03','이학수','경주',7);
insert Agents values('a04','박동호','부산',6);
insert Agents values('a05','김광현','서울',5);
insert Agents values('a06','신동주','대전',5);

insert products values('p01','가위','대전',111400,500);
insert products values('p02','치약','광주',203000,50);
insert products values('p03','치솔','서울',150600,1000);
insert products values('p04','볼펜','서울',125300,1000);
insert products values('p05','연필','대전',221400,1000);
insert products values('p06','홀더','대전',123100,2000);
insert products values('p07','싸인펜','광주',100500,1000);

insert orders values('1011','1월','c001','a01','p01',1000,450000);
insert orders values('1012','1월','c001','a01','p01',1000,450000);
insert orders values('1019','2월','c001','a02','p02',400,180000);
insert orders values('1017','2월','c001','a06','p03',600,540000);
insert orders values('1018','2월','c001','a03','p04',600,540000);
insert orders values('1023','3월','c001','a04','p05',500,450000);
insert orders values('1022','3월','c001','a05','p06',400,720000);
insert orders values('1025','4월','c001','a05','p07',800,720000);
insert orders values('1013','1월','c002','a03','p03',1000,880000);
insert orders values('1026','5월','c002','a05','p03',800,704000);
insert orders values('1015','1월','c003','a03','p05',1200,1104000);
insert orders values('1014','1월','c003','a03','p05',1200,1104000);
insert orders values('1021','1월','c004','a06','p01',1000,460000);
insert orders values('1016','1월','c006','a01','p01',1000,500000);
insert orders values('1020','2월','c006','a03','p07',800,600000);
insert orders values('1024','3월','c006','a06','p01',800,400000);
insert orders values('1027','5월','c006','a01','p07',1000,1000000);

1번. 테이블 4개를 모두 검색하시오

SELECT cid '고객번호', cname '고객이름', city '고객거주지', discnt '할인율'
FROM customers;
-
SELECT aid '판매자번호', aname '판매자이름', acity '판매자거주지', apercent '수수료'
FROM agents;
-
SELECT pid '상품번호', pname '상품이름', pcity '상품보관도시', quantity '수량', price '가격'
FROM products;
-
SELECT *
FROM orders;

2번 고객중에 서울에 사는 고객을 검색하시오.

3번 고객중에 서울에 살고 있지 않는 고객을 검색하시오.

4번 고객중에 서울에 살고 할인율이 10%이상을 검색하시오.

SELECT cname, city, discnt
FROM customers
WHERE city = '서울' and discnt >= 10;

5번 고객중에 대전에 살고 있지 않거나 또는 할인율이 8%이상을 검색하시오.

SELECT cname, city, discnt
FROM customers
WHERE city != '대전' or discnt >= 8;

6번 판매자 중에 부산, 대전에 살고 있는 사람의 이름을 검색하시오.

SELECT aname, acity
FROM agents
WHERE acity IN('부산', '대전');
-- where acity = '부산' or acity = '대전'; 도 가능하지만 IN이 효율적임 --

7번 ⚡어려움⚡ 판매자 중에 수수료가 가장 높은 사람의 수수료 구하시오

• 틀린 답

SELECT aname, max(apercent)
FROM agents;

위와 같은 쿼리로는 첫행의 aname, apercent의 최대값이 따로따로 나옴.
apercent의 최대값과 연결된 aname을 도출해내기 위해서는 아래와같이 서브쿼리를 이용해야함.

• 서브쿼리 사용

SELECT aname, apercent
FROM agents
WHERE apercent IN (SELECT max(apercent) -- IN 대신 '=' 사용가능 --
					  FROM agents);

max()는 group by를 사용한 상태에서 해야함...

• 다른방법1 (오답)

SELECT aname, apercent
FROM agents
ORDER BY apercent DESC
LIMIT 1;

이 경우에는 최대값이 여러개일경우에도 결과값이 1개만 나오기때문에 정확한 쿼리가 아님.

• 다른방법2 (정답)

SELECT aname, apercent
FROM agents
WHERE apercent = (SELECT apercent -
					  FROM agents
                      ORDER BY apercnet DESC
                      LIMIT 1);

위와같은 형식으로는 최대값이 7인 모든 데이터가 나오기때문에 정답임.

8번 부산에 살고 성씨가 김씨인 사람의 판매자 번호를 검색하시오

SELECT aid, aname, acity
FROM agents
WHERE aname LIKE '김%' AND acity = '부산'

• 다른방법 (LEFT함수 사용)

SELECT aid, aname, acity
FROM agents
WHERE acity = '부산' AND LEFT(aname, 1) = '김';

9번 상품가격이 1000원 이상인 상품이름과 재고수량을 검색하시오.

SELECT pname, quantity, price
FROM products
WHERE price >= 1000
ORDER BY 3;

10번. 제품보관도시가 대전이지 않거나 또는 가격이 500원에서 1500원 사이의 제품번호를 검색하시오.

SELECT pid, pcity, price
FROM products
WHERE pcity != '대전' OR price BETWEEN 500 AND 1500
ORDER BY 1;

트랜잭션

A, B 기능을 묶어놓고 A는 실행되고 B는 실행이안된 상태에서 서버가 다운됐을때 A를 롤백시켜주는 기능

START TRANSACTION 
-- 한줄 먼저 실행 --

DELETE FROM buy 
WHERE num = 1;
-- 두줄 실행 -> 1 삭제됨, but 여기서 commit을 하면 완전히 삭제되지만 롤백을 하면 다시 돌아옴  --

SELECT *
FROM buy;
-- 검색해보면 1 삭제되어있음 --

ROLLBACK;
-- 다시 돌아옴 --

/* COMMIT; */
-- 완전히 삭제됨 --

위 기능을 활용해서 11~20번 문제 풀기

11번 주문수량이 1000개를 초과한 상품번호를 검색하시오.

SELECT distinct pid '상품번호', qty '주문수량'
FROM orders
WHERE qty > 1000;

12번 주문날짜가 1월 아니고 주문액수가 500,000원에서 800,000원사이에
주문한 고객번호를 검색하시오.
나의 답

SELECT c.cid
FROM customers c JOIN orders o
			ON c.cid = o.cid
WHERE MONTH != '1월' AND wons BETWEEN 500000 AND 800000; 

정답

SELECT cid
FROM orders
WHERE MONTH != '1월' AND (wons BETWEEN 500000 AND 800000); 

결과는 동일함

13번 서울에 사는 고객의 정보를 삭제하라.
(실행하지말고 SQL문만 작성)

DELETE FROM customers; -- 실행이 안됨. orders 테이블에서 참조하고 있기 떄문에 -> FK를 제거해줘야함 --
-- FK 제거 / 한줄씩 실행하기 --

START TRANSACTION;

SET FOREIGN_KEY_CHECKS = 0; -- FX 제약 (잠시)중지 --

DELETE FROM customers
WHERE city = '서울';

SELECT * FROM customers; -- 서울에 사는 고객정보 삭제됨 --

ROLLBACK; -- 다시 돌리기 --

SELECT * FROM customers; -- 다시 돌아옴 --

14번 대전에 살고 있지 않고 '김,이,박' 성을 가진 고객을 삭제하라.
(실행하지말고 SQL문만 작성) : 방법 3가지

START TRANSACTION;

SET FOREIGN_KEY_CHECKS = 0;

DELETE FROM customers

1. WHERE (cname LIKE '김%' OR cname LIKE '이%' OR cname LIKE '박%') OR city != '대전'

2. WHERE (LEFT(cname, 1)IN('김','이','박')) OR city != '대전' -- LEFT, IN사용 --

3. WHERE city != '대전' AND REGEXP_LIKE(cname, '^김|^이|^박'); -- REGEXP_LIKE 사용 --

SELECT * FROM customers;

ROLLBACK;

SELECT * FROM customers;

REGEXP_LIKE 참고 : https://wakestand.tistory.com/486

15번 주문수량이 400개이하의 주문은 모두 삭제 하시오.
(실행하지말고 SQL문만 작성)

DELETE FROM orders
WHERE qty <= 400;

16번 주문날짜가 4월이고 주문액수가 720000원 주문을 삭제하라.
(실행하지말고 SQL문만 작성)

DELETE FROM orders
WHERE (MONTH = '4월') AND (wons = 720000);

17번 대전에 살고 있는 고객의 거주지를 부산으로 변경하라.
(실행하지말고 SQL문만 작성)

UPDATE customers
SET city = '부산'
WHERE city = '대전'

18번 할인율이 12% 이상인 고객의 고객번호를 c007로 변경하라.
(실행하지말고 SQL문만 작성)

START TRANSACTION;

SET FOREIGN_KEY_CHECKS = 0;

UPDATE customers
SET cid = 'c007'
WHERE discnt >= 12;

SELECT * FROM customers;

ROLLBACK;

SELECT * FROM customers;

customers는 fk설정이 되어있기때문에 SET FOREIGN_KEY_CHECKS = 0; 를이용하여 FX 제약 (잠시)중지해줘야한다.

19번 고객테이블에서 이름은 오름차순 그리고 거주지는 내림차순으로 가져오시오.

SELECT cname, city
FROM customers
ORDER BY cname, city DESC;

20번 부산에 살고 있는 판매자 중에 수수료가 낮은 순서로 가져오시오.

• -- ANSI 문법 --
  SELECT b.mem_id, m.addr
  FROM buy b JOIN member m
  ON b.mem_id = m.mem_id
  WHERE b.prod_name = '지갑';

• -- 일반문법 --
  SELECT b.mem_id, m.addr
  FROM buy b JOIN member m
  WHERE b.mem_id = m.mem_id AND
  b.prod_name = '지갑'
  

SELECT *
FROM agents
WHERE acity = '부산'
ORDER BY apercent;

/21번 제품 보관도시가 서울이지 않은 상품을 수량은 많고,
가격은 낮은 순으로 상품번호를 가져오시오./

/22번 홀수달에 주문한 제품을 수량이 많은 순으로 가져오시오./

/23번 제품보관도시별로 가격의 평균값을 평균값이 높은 순서대로 가져오시오./

/24번 제품가격이 700원 이상인 제품중에 제품보관도시별로 재고수량을 합을 구하시오./

/25번 제품가격이 1000원 이하의 제품 중에 제품보관도시별로
제품 평균가격이 800원 이상인것만 가져오시오.(having)/

/26번 고객테이블의 cid를 외래키로 참조 하면서 테이블명은
hobby이고 컬럼은 hid 주키 자동증가,
h_name 취미명 , cid 고객번호 이렇게 테이블을 만드시오./

/back data/
insert hobby(h_name,h_cid) values ('인터넷','c001');
insert hobby(h_name,h_cid) values ('수영','c001');
insert hobby(h_name,h_cid) values ('낚시','c001');
insert hobby(h_name,h_cid) values ('독서','c002');

/27~29번 고객명이 김동길인 사람의 취미를 모두 가져오시오.
ANSI, 조인, 하위질의(Subquery)/

/ANSI조인/

/하의질의(subquery)/

/30번 제품 보관도시가 광주인 제품의 주문수량을
내림차순으로 가져오시오.(조인, 하위질의 각각)/