DDL (Data Definition Language) – ngôn ngữ định nghĩa dữ liệu – Dùng để định nghĩa cấu trúc của cơ sở dữ liệu, bao gồm định nghĩa các hàng, các cột, các bảng dữ liệu, các chỉ số và một số thuộc tính khác liên quan đến cơ sở dữ liệu như vị trí của file và là thành phần chính trong các hệ quản lý dữ liệu và có sự khác biệt rất nhiều giữa các ngôn ngữ truy vấn trên các hệ khác nhau

DML (Data Manipulation Language) – ngôn ngữ thao tác dữ liệu – Cho phép thực thi các câu truy vấn,bao gồm cú pháp để cập nhật – sửa đổi, chèn thêm và xoá các mẩu tin.

ĐỀ 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: 
SELECT DISTINCT [TransactionType] FROM [Production].[TransactionHistory]

--
CREATE FUNCTION dbo.TK_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.TK_soGiaoDich(327)

Bài 2:

-- bai 2
SELECT * INTO Bike_Transaction
FROM [Production].[TransactionHistory]
WHERE 1=2

CREATE TRIGGER trg_check_bike ON [Sales].[SalesOrderDetail]
AFTER insert
AS

INSERT [dbo].[bike_transaction] ([TransactionID],[ProductID], [ReferenceOrderID], [ReferenceOrderLineID],
[TransactionDate], [TransactionType], [Quantity], [ActualCost], [ModifiedDate])
SELECT (select max([TransactionID])+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'

-- 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'

-- chon san pham xe dap 760
-- Tim hoa don muon nhap them xe dap
select * from [Sales].[SalesOrderDetail]
where [ProductID] <> 760

-- nhap san pham nay cho hoa don 43659
-- kiem tra
SET identity_insert [dbo].[bike_transaction] on
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
)
-- chay thu
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([TotalDue])
         FROM [Sales].[SalesOrderHeader] SOH
                  JOIN [Person].[Person] P ON P.BusinessEntityID = SOH.CustomerID
         WHERE [PersonType] = 'IN' -- SC, VC, IN, EM or SP
        )

    INSERT @DSKhach (MaKhach, Tenkhach, TongTien)
    SELECT P.[BusinessEntityID], [FirstName] + ' ' + [MiddleName] + ' ' + [LastName], AVG([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], [FirstName], [MiddleName], [LastName]
    HAVING AVG([TotalDue]) > @dsbq
    RETURN
END

-- Test
SELECT *
FROM dbo.DSKhachTiemnang()