연습문제 21번~30번

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

SELECT pid, pcity, quantity, price
FROM products
WHERE pcity != '서울'
ORDER BY 3 DESC, 4;

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

• 방법 1 - % 사용
  10/3=3(3.3333 X)
  (정수와 정수를 계산하면 정수가 나옴)

SELECT *
FROM orders
WHERE MONTH%2=1
ORDER BY 6 DESC;

• 방법 2 - mod 사용
  mod(열,n) -> 열 나누기 n

SELECT *
FROM orders
WHERE MOD(MONTH,2) = 1
ORDER BY qty DESC;

• 방법 3 - substring 사용

SELECT *
FROM orders
WHERE SUBSTRING_INDEX(MONTH,'월',1)%2 = 1
ORDER BY qty DESC;

• 방법 4 - replace 사용
  REPLACE(열,'A','B') -> 열의 A 문자를 B로 변경

SELECT *
FROM orders
WHERE REPLACE(MONTH,'월','')%2 = 1 -- month의 월 이라는 글자를 없애면 숫자만 남음-> 그 숫자를 2로 나눴을때 1이 남는 수 도출 --
ORDER BY qty DESC;

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

group by는

SELECT pcity, avg(price)
FROM products
GROUP BY pcity
ORDER BY 2 DESC;

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

• 틀린 답...테이블 데이터 잘 보기

SELECT pname, pcity, quantity
FROM products
WHERE price IN(SELECT price
					FROM products
					WHERE price >= 700);

• 정답

SELECT pcity, SUM(quantity)
FROM products
WHERE price >= 700
GROUP BY pcity;

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

SELECT pcity, AVG(price) "평균가격"
FROM products
WHERE price <= 1000
GROUP BY pcity  
HAVING AVG(price) >= 800;

26번. 고객테이블의 cid를 외래키로 참조 하면서 테이블명은 hobby이고 컬럼은 hid 주키 자동증가, h_name 취미명 , h_cid는 고객번호이고 customers 테이블에 cid와 fk 연결하세요.

PRIMARY KEY :

CREATE TABLE hobby(
 hid INT AUTO_INCREMENT,
 h_name CHAR(10),
 h_cid CHAR(10),
 PRIMARY KEY(hid),
 FOREIGN KEY(h_cid) REFERENCES customers(cid)
);

복합주키

-- 복합주키 --
CREATE TABLE test1(
a CHAR(20),
b CHAR(20),
c CHAR(20),
PRIMARY KEY(a, b)
)

INSERT test1 VALUES('aaa','bbb','ccc');
INSERT test1 VALUES('aaa','ccc','ddd');

SELECT * FROM test1;

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

• ANSI 조인

-- 내 답 --
SELECT cname, h_name
  FROM hobby INNER JOIN customers ON hobby.h_cid = customers.cid
 WHERE cname = '김동길';
 
-- 선생님 답 --
SELECT c.cname, h.h_name
FROM customers c JOIN hobby h
	  ON c.cid = h.h_cid
WHERE c.cname = '김동길';

• 일반 조인

-- 내 답 --
SELECT c.cname '고객명', h.h_name '취미'
FROM hobby h JOIN customers c
	  ON h.h_cid = c.cid
WHERE c.cname = '김동길';


-- 선생님 답 --
SELECT c.cname, h.h_name
FROM customers c, hobby h
WHERE c.cid = h.h_cid AND
	   c.cname = '김동길'

• 하위질의(subquery)

SELECT h_name
FROM hobby
WHERE h_cid IN (SELECT cid
					  FROM customers
					  WHERE cname = '김동길');

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

• 조인

-- 내 답 --
SELECT p.pname, p.pcity, o.qty
FROM products p JOIN orders o
ON p.pid = o.pid
WHERE p.pcity = '광주'
ORDER BY 3 DESC;


-- 선생님 답 --
SELECT qty, pname
FROM products p JOIN orders o
	  ON p.pid = o.pid
WHERE p.pcity = '광주'
ORDER BY qty DESC;

• 하위질의

-- 내 답 : desc가 빠짐 --
SELECT qty
FROM orders
WHERE pid IN (SELECT pid
					  FROM products
					  WHERE pcity = '광주');
                      
-- 선생님 답 --
SELECT qty
FROM orders
WHERE pid IN (SELECT pid
				  FROM products
				  WHERE pcity = '광주')
ORDER BY 1 DESC;
                      

SQL 프로그래밍

교재 196p

스토어 프로시저(stored procedure)
: SQL + 프로그램 기법 --

중요도:하

-- 삭제 --
DROP PROCEDURE if EXISTS ifProc1;

-- 생성 --
delimiter $$
CREATE PROCEDURE ifProc1()
BEGIN
 if 100 = 100 then
   SELECT '100은 100과 같습니다.';
 END if;
END $$
delimiter ; -- 끝에 공백 1칸 줘야함 --

-- sp 호출 --
CALL ifProc1();

---

프로시저 생성시 기본 구성

delimiter $$ -- 프로시저 영역이라고 선언해주는 것 --
CREATE PROCEDURE 프로시저명()
BEGIN

END $$
delimiter ;

예제 1

delimiter $$ -- 프로시저 영역이라고 선언해주는 것 --
CREATE PROCEDURE ifProc2()
BEGIN
	DECLARE myNum INT; -- 정수형 변수 선언 --
	SET myNum = 200;
	if myNum = 100 then
	 SELECT '100입니다.';
	ELSE
	 SELECT '100이 아닙니다.';
	END if;
END $$
delimiter ;

예제2

delimiter $$
CREATE PROCEDURE ifProc3() -- 매개변수 없음 --
BEGIN
 DECLARE debutDate DATE; -- APN 데뷔날짜 --
 DECLARE cDate DATE; -- 오늘 날짜 --
 DECLARE days INT;
 
 -- 에이핑크의 데뷔날짜값을 debutDate에 삽입 --
 SELECT debut_date INTO debutDate
 FROM member
 WHERE mem_id = 'APN';
 
 SET cDate = CURRENT_DATE(); -- 오늘 날짜 리턴 --
 SET days = DATEDIFF(cDate,debutDate); -- 일단위로 계산 --
 
 		if(days/365) >= 5 then
 		  SELECT CONCAT('데뷔한지',days,'일이나 지났습니다.'); -- concat : 하나로 합쳐줌 --
		ELSE
  		 SELECT '데뷔한지',days,'일밖에 안되었네요.';
  	 
END if;
END $$
delimiter ;

예제 3

 delimiter $$
 CREATE PROCEDURE caseProc()
 BEGIN
 	 DECLARE pos INT;
 	 DECLARE credit CHAR(1);
 	 SET pos = 88;
 	 
 	 case -- 여러가지 조건이 있을때 사용하는 제어문(자바:switch) --
 	 	 when pos >= 90 then
 	 	 SET credit = 'A';
 	 	 when pos >= 80 then
 	 	 SET credit = 'B';
 	 	 when pos >= 70 then
 	 	 SET credit = 'C';
		ELSE
		SET credit = 'F';
	 END case;
	 
	 SELECT CONCAT('취득점수 ==> ', pos),
	 		  CONCAT('학점 ==> ', credit);
 
 END $$
 delimiter $$
 

---

가변인수

일반적인 함수 : 인수의 개수가 정해져있음. a(ex1, ex2) 같은 경우에는 인수 2개만 넣어야함.
가변인수 : 인수의 개수가 정해져있지 않음. concat(ex1, ex2, ...)같이 인수에 ...이 있는 경우에는 ex5까지 넣어도 됨.

---

outer join

동일한 값이 없어도 리턴

-- 일반 join --
SELECT m.mem_id, mem_name, SUM(price * amount) '총구매액'
FROM buy b JOIN member m
	  ON b.mem_id = m.mem_id
GROUP BY mem_id
ORDER BY 3 DESC;

-- outer join : 값이 null 인것도 나옴 --
SELECT m.mem_id, mem_name, SUM(price * amount) '총구매액'
FROM buy b RIGHT OUTER JOIN member m
	  ON b.mem_id = m.mem_id
GROUP BY mem_id
ORDER BY 3 DESC;