-- create database
CREATE DATABASE bt1776

-- active database
USE bt1776

-- create tables
CREATE TABLE product (
	id INT PRIMARY KEY IDENTITY(1,1),
	title NVARCHAR(150),
	manufacturer_name NVARCHAR(100),
	madein NVARCHAR(50),
	buy_price FLOAT,
	sell_price FLOAT,
	created_at DATE
)

CREATE TABLE orders (
	id INT PRIMARY KEY IDENTITY(1,1),
	id_product INT REFERENCES dbo.product(id),
	note NVARCHAR(500),
	created_at DATE,
	num INT
)

-- insert data
INSERT INTO product( title ,manufacturer_name ,madein ,buy_price ,sell_price ,created_at)
VALUES
( N'san pham 1' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 2' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 3' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 4' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 5' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 6' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 7' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 8' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 9' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 10' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE())

SELECT * FROM dbo.product

INSERT INTO dbo.orders
        (id_product, note, created_at, num )
VALUES
(2,N'OKOK',GETDATE(),2),
(1,N'OKOK',GETDATE(),21),
(6,N'OKOK',GETDATE(),5),
(2,N'OKOK',GETDATE(),12),
(1,N'OKOK',GETDATE(),27),
(2,N'OKOK',GETDATE(),28),
(9,N'OKOK',GETDATE(),34),
(2,N'OKOK',GETDATE(),21),
(5,N'OKOK',GETDATE(),20)

SELECT * FROM dbo.orders
-- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, ghi chu, so luong
SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
ORDER BY dbo.product.id ASC
-- -- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, ghi chu, so luong (tinh tong so luong ban ra)

SELECT dbo.product.id, dbo.product.title, dbo.orders.num, dbo.orders.note
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.orders.note, SUM(dbo.orders.num) AS 'Total'
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title, dbo.orders.note
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title
ORDER BY dbo.product.id ASC

-- -- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, MADEIN, ghi chu, so luong (tinh tong so luong ban ra)
SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
	AND dbo.product.madein = 'Viet Nam'
GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num, dbo.product.madein
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
	AND dbo.product.madein = 'Viet Nam'
ORDER BY dbo.product.id ASC

--- Xem thong tin don hang
SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product

-- Luu xuong duoi 1 view => 1 bang du lieu tam thoi => dc sinh ra khi chay cau select
CREATE VIEW view_show_order_detail
AS
SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product

SELECT * FROM view_show_order_detail
ORDER BY id ASC

ALTER VIEW view_show_order_detail
AS
SELECT * FROM dbo.product

ALTER VIEW view_show_order_detail
AS
SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product

DROP VIEW view_show_order_detail

-- Produce
CREATE PROC proc_test
AS
BEGIN
	SELECT * FROM dbo.product
	SELECT * FROM dbo.orders
	SELECT * FROM view_show_order_detail
END

EXEC proc_test

CREATE PROC proc_fake_data
AS
BEGIN
	-- insert data
	INSERT INTO product( title ,manufacturer_name ,madein ,buy_price ,sell_price ,created_at)
	VALUES
	( N'san pham 1' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 2' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 3' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 4' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 5' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 6' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 7' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 8' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 9' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 10' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE())

	SELECT * FROM dbo.product

	INSERT INTO dbo.orders
			(id_product, note, created_at, num )
	VALUES
	(2,N'OKOK',GETDATE(),2),
	(1,N'OKOK',GETDATE(),21),
	(6,N'OKOK',GETDATE(),5),
	(2,N'OKOK',GETDATE(),12),
	(1,N'OKOK',GETDATE(),27),
	(2,N'OKOK',GETDATE(),28),
	(9,N'OKOK',GETDATE(),34),
	(2,N'OKOK',GETDATE(),21),
	(5,N'OKOK',GETDATE(),20)

	SELECT * FROM dbo.orders
END

EXEC proc_fake_data


SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
	AND dbo.product.madein = 'JP'
GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
ORDER BY dbo.product.id ASC

CREATE PROC proc_view_order_madein
	@madein nvarchar(50)
AS
BEGIN
	SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
	FROM dbo.product, dbo.orders
	WHERE dbo.product.id = dbo.orders.id_product
		AND dbo.product.madein = @madein
	GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
	ORDER BY dbo.product.id ASC
END

EXEC proc_view_order_madein 'Viet Nam'

-- PRO => xem don xuat xu tu 1 quoc + san pham ban ra co gia @min
CREATE PROC proc_view_order_madein_min
	@madein nvarchar(50),
	@min int
AS
BEGIN
	SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
	FROM dbo.product, dbo.orders
	WHERE dbo.product.id = dbo.orders.id_product
		AND dbo.product.madein = @madein
	GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
	HAVING SUM(dbo.orders.num) >= @min
	ORDER BY dbo.product.id ASC
END

EXEC proc_view_order_madein_min 'Viet Nam', 30

-- Dem tong so san pham trong product
CREATE PROC proc_count_product
	@count int OUTPUT
AS
BEGIN
	SELECT @count = COUNT(*) FROM dbo.product
END

DECLARE @countItem INT
EXEC proc_count_product @count=@countItem output
PRINT @countItem