ĐỀ BÀI

Bài 1:
Viết hàm tính số lần giao dịch và tổng số lượng đã đặt mua (purchase), bán ra (sale), đặt làm (order) của 1 sản phẩm được cho bởi mã sản phẩm


Bài 2:
Tạo 1 bảng rỗng đặt tên là Bike_Transaction có cấu trúc giống bảng TransactionHistory.
Tạo trigger để ghi lại toàn bộ giao dịch bán hàng liên quan đến xe đạp vào 1 bảng vừa tạo. Trình tự thực hiện như sau:

  • Tạo trigger liên quan đến lệnh Insert của bảng SaleOrderDetail. Kiểm tra nếu sản phẩm là Xe đạp thì ghi vào bảng Bike_Transaction (Lưu ý: Có thể cùng lúc phát sinh nhiều hàng mới trong bảng SaleOrderDetail)
  • Kiểm tra trigger bằng cách viết lệnh chèn cùng lúc 3 hàng vào bảng SaleOrderDetail, trong đó có 2 hàng liên quan đến xe đạp.
  • Kiểm tra kết quả trong bảng Bike_Transaction


Bài 3:
Bảng Store có trường Name là tên cửa hàng. Bảng này kết nối với bảng BusinessEntityContact cho biết thông tin người đại diện cửa hàng.
Viết hàm trả về danh sách các đại lý có nhu cầu nhận email tiếp thị của
công ty. Yêu cầu cụ thể:

  • Hàm có tên DS_gui_email, trả về danh sách đại lý có nhu cầu nhận email của cửa hàng theo mẫu sau:
Capture
  • Tương tự xây dựng hàm trả về danh sách các cửa hàng không có nhu cầu nhận email tiếp thị


Bài 4:
Viết hàm trả về danh sách các khách hàng lẻ tiềm năng của cửa hàng (là
những khách có tiền mua trung bình > tiền mua bình quân của tất cả khách
hàng lẻ)

BÀI SỬA

Bài 1:

-- CÓ 3 LOẠI GIAO DICH: 
-- W = WorkOrder, S = SalesOrder, P = PurchaseOrder
SELECT DISTINCT [TransactionType] FROM [Production].[TransactionHistory]

--
CREATE FUNCTION dbo.fn_soGiaoDich(@MaSP int)
RETURNs TABLE
AS
RETURN (
SELECT [ProductID], [TransactionType], 
       SUM([Quantity]) AS tongSL, 
       COUNT(*) AS SoLanGD
FROM [Production].[TransactionHistory]
WHERE [ProductID] = @MaSP
GROUP BY [ProductID], [TransactionType]
)

--Test
SELECT * FROM dbo.fn_soGiaoDich(327)

Bài 2:

-- Tạo bảng rỗng cấu trúc giống Bike_Transaction
SELECT *
INTO Bike_Transaction
FROM [Production].[TransactionHistory]
WHERE 1 = 2 --Only create Structure

CREATE TRIGGER trg_check_bike
    ON [Sales].[SalesOrderDetail]
    AFTER INSERT
    AS
    SET IDENTITY_INSERT [dbo].[Bike_Transaction] ON
    INSERT [dbo].[Bike_Transaction] ([TransactionID],
                                     [ProductID],
                                     [ReferenceOrderID],
                                     [ReferenceOrderLineID],
                                     [TransactionDate],
                                     [TransactionType],
                                     [Quantity],
                                     [ActualCost],
                                     [ModifiedDate])
    SELECT (SELECT ISNULL(MAX([TransactionID]), 0) + 1 FROM [dbo].[Bike_Transaction]),
           i.[ProductID],
           i.[SalesOrderID],
           i.[SalesOrderDetailID],
           GETDATE(),
           'S',
           i.[OrderQty],
           i.[UnitPrice],
           GETDATE()
    FROM inserted i
             JOIN [Production].[Product] P ON P.ProductID = i.ProductID
             JOIN [Production].[ProductSubcategory] PS
                  ON P.ProductSubcategoryID = PS.ProductSubcategoryID
             JOIN [Production].[ProductCategory] PC
                  ON PC.ProductCategoryID = PS.ProductCategoryID
    WHERE PC.[Name] = 'Bikes'
    SET IDENTITY_INSERT [dbo].[Bike_Transaction] OFF

-- tim san pham xe dap
    SELECT p.ProductID, p.Name
    FROM [Production].[Product] p
             JOIN [Production].[ProductSubcategory] s ON s.ProductSubcategoryID = p.ProductSubcategoryID
             JOIN [Production].[ProductCategory] c ON c.ProductCategoryID = s.ProductCategoryID
    WHERE c.[Name] = 'Bikes'
GO

    ----
-- Giả sử:
-- Chọn sản phẩm xe đạp mã 760
-- Tìm hóa đơn muốn nhập thêm xe đạp mới
    SELECT *
    FROM [Sales].[SalesOrderDetail]
    WHERE SalesOrderID = 43661
--       AND [ProductID] = 760
    ORDER BY SalesOrderID DESC

    --  SET IDENTITY_INSERT [dbo].[Bike_Transaction] ON ▶ Đưa vào trong TRIGGER tránh lỗi khởi tạo
-- "Cannot insert the value NULL into column 'TransactionID', ..." ▶  Bảng SalesOrderDetail đang NULL -> Sử lý ISNULL
    INSERT [Sales].[SalesOrderDetail] ([SalesOrderID], [OrderQty], [ProductID], [SpecialOfferID],
                                       [UnitPrice], [UnitPriceDiscount])
    VALUES (43661, 10, 760, 1, 350, 0)

Bài 3:

CREATE FUNCTION DS_gui_Email()
    RETURNS TABLE 
        AS
        RETURN
            (
                SELECT s.Name                                              AS 'Ten cua hang',
                       [FirstName] + ' ' + [MiddleName] + ' ' + [LastName] AS 'Ho Ten NV',
                       [EmailAddress]
                FROM [Sales].[Store] S
                         JOIN [Person].[BusinessEntityContact] BEC ON BEC.BusinessEntityID = S.BusinessEntityID
                         JOIN [Person].[Person] P ON P.BusinessEntityID = BEC.PersonID
                         JOIN [Person].[EmailAddress] EA ON EA.BusinessEntityID = P.BusinessEntityID
                WHERE [EmailPromotion] = 0 -- Thay thành 1
            )
-- Test
SELECT *
FROM dbo.DS_gui_Email()

Bài 4:

CREATE FUNCTION DSKhachTiemnang()
    RETURNS @DSKhach TABLE
                     (
                         MaKhach  INT,
                         Tenkhach NVARCHAR(50),
                         TongTien MONEY
                     )
AS
BEGIN

    DECLARE @dsbq MONEY =
        (SELECT AVG(SOH.[TotalDue])
         FROM [Sales].[SalesOrderHeader] SOH
                  JOIN [Person].[Person] P ON P.BusinessEntityID = SOH.CustomerID
         WHERE P.[PersonType] = 'IN')
    ----
--  SC = Store Contact,
--  IN = Individual (retail) customer,
--  SP = Sales person,
--  EM = Employee (non-sales),
--  VC = Vendor contact,
--  GC = General contact

    INSERT @DSKhach (MaKhach, Tenkhach, TongTien)
    SELECT P.[BusinessEntityID], 
           P.[FirstName] + ' ' + P.[MiddleName] + ' ' + P.[LastName], 
           AVG(SOH.[TotalDue])
    FROM [Sales].[SalesOrderHeader] SOH
             JOIN [Person].[Person] P ON P.BusinessEntityID = SOH.CustomerID
    WHERE [PersonType] = 'IN' --Khach hàng lẻ
    GROUP BY P.[BusinessEntityID], P.[FirstName], P.[MiddleName], P.[LastName]
    HAVING AVG([TotalDue]) > @dsbq
    RETURN
END

-- Test
SELECT *
FROM dbo.DSKhachTiemnang()