Bài tập

Câu 1: Để biểu diễn mối quan hệ giữa nhân viên và người quản lý có thể dùng mã dạng chuỗi “cấp 1.cấp 2.cấp 3…” (Số dấu chấm trong mã cho biết số cấp quản lý của mỗi nhân viên).

Hãy viết hàm fs-Level có 1 tham số là chuỗi dạng mã, ví dụ ‘1.20.56.345.1010’, hàm trả về số cấp của mã đó (bằng với số dấu chấm +1).


Câu 2: Tạo hàm fn_DanhSachNV dạng inline có 1 tham số là mã phòng (từ 1 đến 16). Hàm trả về 1 bảng có dạng như sau chứa danh sách các nhân viên hiện tại của phòng cùng với ca làm việc (shift) của mỗi nhân viên.

➡️Kết quả rả về có dạng như sau:

Capture
Hướng dẫn

Bảng [HumanResources].[EmployeeDepartmentHistory] chứa nhật
ký ngày nhân viên bắt đầu làm việc hay rời khỏi phòng.

Bảng[HumanResources].[Shift] chứa thông tin các ca làm việc


Câu 3: Tạo hàm fn_SP_biendongGia trả về danh sách các sản phẩm hay thay
đổi giá (Một sản phẩm hay thay đổi giá nếu giá mới nhất chênh lệch trên 10% so với giá cũ nhất).

➡️Kết quả trả về có dạng như sau:

Capture.PNG

4. Tạo hàm hn_NhanVienBanHangTheoKV dạng inline có 2 tham số là khu vực và năm. Hàm trả về danh sách nhân viên bán hàng trong từng khu vực (territory) (có tất cả 10 khu vực) cùng với doanh số bán hàng của mỗi nhân viên đó.

➡️Kết quả trả về có dạng như sau:

Capture.PNG

5. Tạo hàm fnGetDiscountPrice trả về giá được chiết khấu. Hàm có 2 tham số là mã sản phẩm và ngày. Nếu sản phẩm được tìm thấy trong bảng [Sales].[SpecialOfferProduct] và ngày nằm trong thời gian được giảm giá thì hàm trả về giá được giảm của sản phẩm đó, ngược lại trả về nguyên giá.


6. Tạo hàm trả fn_Manager trả về bảng liệt kê tên các nhân viên cấp dưới của người quản lý có mã nhân viên được cho.

Hướng dẫn:

Trong bảng [HumanResources].[Employee] có trường OrganizationNode kiểu dữ liệu là hierarchyid xác định phân cấp chức vụ của nhân viên, dùng hàm IsDescendantOf để xác định nhân viên có phải là cấp dưới (Descendant) của nhân viên khác hay không?

Bài giải

Câu 1:

Không hiểu sao mình mình bị lỗi khi để Format code vào. Mọi người chịu khó gõ lại nhé

Câu 2:

CREATE FUNCTION dbo.fn_DanhSachNV(@maphong INT)
    RETURNS TABLE
        AS
        RETURN
            (
                SELECT P.Title,
                       P.FirstName,
                       P.MiddleName,
                       P.LastName,
                       S.Name AS 'Ca lam viec'
                FROM [HumanResources].[Employee] E
                         JOIN [HumanResources].[EmployeeDepartmentHistory] EDH
                              ON E.BusinessEntityID = EDH.BusinessEntityID
                         JOIN [HumanResources].[Shift] S ON S.ShiftID = EDH.ShiftID
                         JOIN [Person].[Person] P ON P.BusinessEntityID = E.BusinessEntityID
                WHERE EDH.DepartmentID = @maphong
                  AND EDH.EndDate IS NULL
            )
--TEST
SELECT *
FROM dbo.fn_DanhSachNV(16)

Câu 3:

CREATE FUNCTION dbo.fn_SP_BiendongGia()
    RETURNS @tbl_gia TABLE
                     (
                         maSp                 INT,
                         tenSp                NVARCHAR(50),
                         giaMoiNhat           MONEY,
                         giaCuNhat            MONEY,
                         PhantramChenhLechGia INT
                     )
AS
BEGIN
    INSERT @tbl_gia (maSp, tenSp, giaMoiNhat)
    SELECT P.ProductID,
           Name,
           MAX(PCH.StandardCost)
    FROM [Production].[Product] P
             JOIN [Production].[ProductCostHistory] PCH ON P.ProductID = PCH.ProductID
    GROUP BY P.ProductID, Name

    UPDATE @tbl_gia
    SET giaCuNhat = PCH.StandardCost
    FROM @tbl_gia G
             JOIN [Production].[ProductCostHistory] PCH ON PCH.ProductID = g.maSp
    WHERE StartDate =
          (SELECT MIN(StartDate)
           FROM [Production].[ProductCostHistory] P
           WHERE P.ProductID = PCH.ProductID)


    UPDATE @tbl_gia
    SET PhantramChenhLechGia = (giaCuNhat / giaMoiNhat) * 100
    DELETE @tbl_gia WHERE PhantramChenhLechGia < 10
    RETURN
END

SELECT *
FROM dbo.fn_SP_BiendongGia()

Câu 4:

CREATE FUNCTION hn_NhanVienBanHangTheoKV(@khuvuc INT, @nam INT)
    RETURNS TABLE
        AS
        RETURN
            (
                SELECT [FirstName], [MiddleName], [LastName], SUM([TotalDue]) AS 'Doanh so'
                FROM [Sales].[SalesOrderHeader] SOH
                         JOIN [Sales].[SalesTerritory] ST ON ST.TerritoryID = SOH.TerritoryID
                         JOIN [Sales].[SalesTerritoryHistory] STH ON STH.TerritoryID = ST.TerritoryID
                         JOIN [Sales].[SalesPerson] SP ON SP.BusinessEntityID = STH.BusinessEntityID
                         JOIN [HumanResources].[Employee] E ON E.BusinessEntityID = SP.BusinessEntityID
                         JOIN [Person].[Person] P ON P.BusinessEntityID = E.BusinessEntityID
                WHERE ST.TerritoryID = @khuvuc
                  AND YEAR([OrderDate]) = @nam
                  AND [OrderDate] BETWEEN [StartDate] AND [EndDate]--Nếu không có giá trị sẽ nhiều hơn
                GROUP BY [FirstName], [MiddleName], [LastName]
            )
--TEST
SELECT *
FROM dbo.hn_NhanVienBanHangTheoKV(1, 2005)

Câu 5:

CREATE FUNCTION fnGetDiscountPrice(@masp INT, @ngay DATE)
    RETURNS MONEY
AS
BEGIN
    DECLARE @tyleck MONEY
    DECLARE @gia MONEY
    SELECT @tyleCK = DiscountPct
    FROM [Sales].[SpecialOfferProduct] SOP
             JOIN [Sales].[SpecialOffer] SO ON SO.SpecialOfferID = SOP.SpecialOfferID
    WHERE [ProductID] = @masp
      AND @ngay BETWEEN [StartDate] AND [EndDate]

    IF @tyleCK IS NULL
        SET @tyleCK = 0
    SELECT @gia = [ListPrice]
    FROM [Production].[Product]
    WHERE [ProductID] = @masp

    RETURN @gia * (1 - @tyleCK)
END
--TEST
SELECT [dbo].[fnGetDiscountPrice](842, '2005/12/1')

Câu 6:

CREATE FUNCTION dbo.fn_Manager(@manv INT)
    RETURNS @dsnv TABLE
                  (
                      MANV  INT,
                      Hoten NVARCHAR(50),
                      Capql INT,
                      job   NVARCHAR(20)
                  )
AS
BEGIN
    DECLARE @maql HIERARCHYID
    SELECT @maql = [OrganizationNode]
    FROM [HumanResources].[Employee]
    WHERE [BusinessEntityID] = @manv

    INSERT @dsnv
    SELECT E.[BusinessEntityID],
           E.[OrganizationNode],
           P.[FirstName] + ' ' + P.[LastName],
           E.[OrganizationLevel],
           E.[JobTitle]
    FROM [HumanResources].[Employee] E
             JOIN [Person].[Person] P ON P.BusinessEntityID = E.BusinessEntityID
    WHERE [OrganizationNode].GetAcestor(1) = @maql
--WHERE [OrganizationNode].IsDescendantOf(@maql)=1
    RETURN
END
--TEST
SELECT *
FROM [dbo].[fn_manager](16)