--Tâm 1713057--
USE dbTipee
GO
-- Table
CREATE TABLE tblCART(
    id          VARCHAR(50) NOT NULL,
    PRIMARY KEY(id),
    --id nvarchar(9) primary key,
    idclient    VARCHAR(50) NOT NULL,
    --foreign key(idclient) references Client(id)
);
GO
CREATE TABLE tblADD_CART(
    idcart      nvarchar(9) NOT NULL,
    idproduct   VARCHAR(50) NOT NULL,
    idshop      VARCHAR(6) NOT NULL,
    PRIMARY KEY(idcart,idproduct,idshop),
    quantity    INT )
;
GO
CREATE TABLE tblCATEGORY(
    id              CHAR(3) NOT NULL,
    PRIMARY KEY(id),
    name            nvarchar(30),
    quantity        INT         DEFAULT 0           --- tt dẫn xuất
);
GO
CREATE TABLE tblBELONG_CATEGORY(
    idproduct   VARCHAR(50) NOT NULL,
    idcate      CHAR(3) NOT NULL,
    PRIMARY KEY(idcate,idproduct)
);
GO
/*Insert data*/
INSERT INTO tblCART VALUES ('1',12);

INSERT INTO tblADD_CART VALUES ('1','123a',167,5);

INSERT INTO tblCATEGORY VALUES ('EL',N'Electronics',1);
INSERT INTO tblCATEGORY VALUES ('FA',N'Fashion',1);
INSERT INTO tblCATEGORY VALUES ('BO',N'Book',1);
INSERT INTO tblCATEGORY VALUES ('FO',N'FOOD',1);
INSERT INTO tblCATEGORY VALUES ('ST',N'Stationery',1);
INSERT INTO tblCATEGORY VALUES ('TO',N'Toy',1);

INSERT INTO tblBELONG_CATEGORY VALUES ('123a','DT');
GO

ALTER TABLE tblCART
    ADD
        CONSTRAINT      fk_cart_customer        FOREIGN KEY (idclient)
    REFERENCES tblCustomer(id_customer)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
GO
--ALTER TABLE tblADD_CART   idproduct   VARCHAR(50);
GO

ALTER TABLE tblADD_CART
    ADD
        CONSTRAINT  fk_cart_addcart     FOREIGN KEY (idcart)
    REFERENCES tblCART(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
        CONSTRAINT  fk_pro_addcart      FOREIGN KEY (idproduct)
    REFERENCES tblProduct(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
        CONSTRAINT  fk_shop_addcart     FOREIGN KEY (idshop)
    REFERENCES tblShop(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
        CONSTRAINT  check_quantity      CHECK(quantity>0)
GO

ALTER TABLE tblCATEGORY
    ADD
        CONSTRAINT  check_quantity_cate     CHECK(quantity>=0);
GO

ALTER TABLE tblBELONG_CATEGORY
    ADD
    CONSTRAINT  fk_cate FOREIGN KEY (idcate)
    REFERENCES tblCATEGORY(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT  fk_cate_pro FOREIGN KEY (idproduct)
    REFERENCES tblProduct(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
GO
--- procedure insert + validate + print error
CREATE Proc usp_insert_cate
    @id             CHAR(3),
    @name           nvarchar(30),
    @quantity       INT
AS  BEGIN
--declare set
            BEGIN try
        INSERT INTO tblCATEGORY(id, name, quantity) VALUES (@id, @name, @quantity)
        print 'Insert product successfully'
        RETURN @@ROWCOUNT
            END try
--- catch
            BEGIN catch
        print 'Error insert category
Category was already exist'
        RETURN 0
            END catch
    END
;
GO
EXEC usp_insert_cate 'HO','home',3;
EXEC usp_insert_cate 'HO','home',-1;
GO
-- trigger after--
CREATE TRIGGER check_quantity_trigger ON tblADD_CART
FOR INSERT
AS
BEGIN
    DECLARE @quantity INT
    SET @quantity = (SELECT quantity FROM inserted)
    IF (@quantity <=0)
    BEGIN
        print 'error: quantity must have value'
        ROLLBACK
    END
END;
GO
INSERT INTO tblADD_CART VALUES ('1','Toy','3',-1);
---trigger after affect other table---
CREATE TRIGGER Update_QuanofCate ON tblBELONG_CATEGORY
FOR INSERT
AS
BEGIN

    DECLARE @idcate CHAR(3)

    SELECT @idcate = Inserted.idcate FROM Inserted

    UPDATE tblCATEGORY SET quantity=quantity+1 WHERE id = @idcate

END
GO
INSERT INTO tblBELONG_CATEGORY VALUES ('231','HO');
SELECT TOP 10 *
        FROM tblCATEGORY
        ORDER BY quantity ASC;
GO
--Procedure has Query Statement hiểnthị dữ liệu,tham số đầu vào là giá trị trong mệnh đề WHERE và/hoặc Having
--a. 1 câu truy vấn từ 2 bảng trở lên có mệnh đề where, order by---
CREATE prOC usp_Sort_Name_Cate
    AS
    BEGIN

    END
GO
CREATE prOC usp_List_Cart       -- Link 4 relation: cart, product, addcart, customer
    @first_name     NVARCHAR(20),
    @last_name      NVARCHAR(20)
    AS
    BEGIN
    DECLARE @id_cus VARCHAR(50)
    SELECT @id_cus = id_customer FROM tblCustomer
    WHERE first_name=@first_name AND last_name=@last_name;
    SELECT idproduct,name AS name_pro,  idshop, quantity
    FROM    (tblADD_CART INNER JOIN tblProduct
    ON  tblADD_CART.idproduct=tblProduct.id) INNER JOIN tblCART
    ON  tblADD_CART.idcart=tblCART.id
    WHERE   tblCART.idclient=@id_cus
    ORDER BY quantity ASC;
    --Select id_customer from tblCustomer

    END
GO
EXEC    usp_List_Cart 'b','a'
WITH RESULT SETS
(
(
[Product ID] VARCHAR(50),
[Product Name] nvarchar(100),
[Shop ID] VARCHAR(50),
[ORDER Quantity] INT
)
)
GO
--procedure có aggreate function, group by, having, where và order by có liên kết từ 2 bảng trở lên
CREATE PROCEDURE usp_Pro_MulFunction
    -- Add the parameters for the stored procedure here
    @name_pro nvarchar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    SELECT idcate, name
    FROM tblBELONG_CATEGORY INNER JOIN
            tblCATEGORY ON idcate=id
    WHERE idproduct IN (SELECT idproduct
    FROM tblBELONG_CATEGORY
    GROUP BY idproduct
    HAVING COUNT(idcate) > 1)
    AND idproduct IN (SELECT id FROM tblProduct
                        WHERE name=@name_pro)
END
GO
EXEC usp_Pro_MulFunction 'book'
WITH RESULT sets (
    ( [Code Cate] VARCHAR(50) ,
        [Name Cate] VARCHAR(50)
    )
)

GO
/* Chứa câu lệnh IF và/hoặc LOOP để tính toán dữ liệu được lưu trữ
b. Chứa câu lệnh truy vấn dữ liệu, lấy dữ liệu từ câu truy vấn để kiểm tra tính toán
c. Có tham số đầu vào và kiểm tra tham số đầu vào
Mỗi thành viên viết 2 câu SELECT để minh họa việc gọi hàm trong câu SELECT */