[SQL] MariaDB 테이블 연습

CREATE TABLE prod(
	pid CHAR(3) PRIMARY KEY,
	pname VARCHAR(20)
);

INSERT INTO prod VALUES('p01','A'),('p02','B'),('p03','C');

CREATE TABLE incoming(
	pid CHAR(3),
	qty INT,
	FOREIGN KEY(pid) REFERENCES prod(pid)
);

INSERT INTO incoming VALUES('p01',10),('p01',5),('p02',3);

CREATE TABLE outgoing(
	pid CHAR(3),
	qty INT,
	FOREIGN KEY (pid) REFERENCES prod(pid)
);

INSERT INTO outgoing VALUES('p01',5),('p01',2),('p02',1);



SELECT SUM(qty)
FROM incoming 
WHERE pid = (SELECT SUM(qty) FROM outgoing GROUP BY pid;);


-- 재고관리

SELECT i.pid, i.icnt, o.ocnt
FROM
(SELECT pid, SUM(qty) icnt FROM incoming GROUP BY pid) i,
(SELECT pid, SUM(qty) ocnt FROM outgoing GROUP BY pid) o
WHERE i.pid = o.pid;




--제품이름과 합치기 (prod테이블 불러오기)

SELECT p.pid,p.pname,io.icnt,io.ocnt, (IO.icnt-IO.ocnt) AS '재고'
FROM
(SELECT i.pid, i.icnt, o.ocnt
from
(SELECT pid, SUM(qty) icnt FROM incoming GROUP BY pid) i,
(SELECT pid, SUM(qty) ocnt FROM outgoing GROUP BY pid) o
WHERE i.pid = o.pid) IO, prod p
WHERE IO.pid = p.pid;




-- 뷰생성

CREATE VIEW v_stockTBL 
AS 
SELECT p.pid,p.pname,io.icnt,io.ocnt, (IO.icnt-IO.ocnt) AS '재고'
FROM
(SELECT i.pid, i.icnt, o.ocnt
from
(SELECT pid, SUM(qty) icnt FROM incoming GROUP BY pid) i,
(SELECT pid, SUM(qty) ocnt FROM outgoing GROUP BY pid) o
WHERE i.pid = o.pid) IO, prod p
WHERE IO.pid = p.pid;


SELECT * from v_stockTBL;