Homework - OnlineShop

Phần A: Tạo bảng

1. Viết các câu lệnh tạo bảng như phần mô tả CSDL OnlineShop
2. Tạo mỗi bảng từ 5-10 records: Sử dụng câu lệnh INSERT, thứ tự nhập dữ liệu cho các tables:
   • Categories
   • Suppliers
   • Customers
   • Employees
   • Products
   • Orders
   • OrderDetails

Phần B: Câu lệnh INSERT, UPDATE, DELETE

1. Viết câu lệnh UPDATE để cập nhật Price với điều kiện: Các mặt hàng có Price <= 100000 thì tăng thêm 10%
2. Viết câu lệnh UPDATE để cập nhật DISCOUNT với điều kiện: Các mặt hàng có Discount <= 10% thì tăng thêm 5%
3. Viết câu lệnh XOÁ tất cả các mặt hàng có Stock là 0

Phần C: Truy vấn cơ bản

1. Hiển thị tất cả các mặt hàng có giảm giá <= 10%
2. Hiện thị tất cả các mặt hàng không có giảm giá
3. Hiển thị tất cả các mặt hàng có số lượng tồn kho <= 5
4. Hiển thị tất cả các mặt hàng có Giá bán sau khi đã giảm giá <= 100.000
5. Hiện thị tất cả các mặt hàng thuộc danh mục CPU, RAM
6. Hiển thị tất cả các khách hàng có địa chỉ ở Quận Hải Châu
7. Hiển thị tất cả các khách hàng có địa chỉ ở Quận Hải Châu hoặc Quận Thanh Khê
8. Hiển thị tất cả các khách hàng có năm sinh 1990.
9. Hiển thị tất cả các khách hàng có tuổi trên 60.
10. Hiển thị tất cả các khách hàng có tuổi từ 20 đến 30.
11. Hiển thị tất cả các khách hàng có sinh nhật là hôm nay. Gợi ý: dùng hàm GETDATE(), MONTH(), DAY()
12. Hiển thị tất cả các đơn hàng có trạng thái là COMPLETED
13. Hiển thị tất cả các đơn hàng có trạng thái là COMPLETED trong ngày hôm nay
14. Hiển thị tất cả các đơn hàng chưa hoàn thành trong tháng này
15. Hiển thị tất cả các đơn hàng có trạng thái là CANCELED
16. Hiển thị tất cả các đơn hàng có trạng thái là CANCELED trong ngày hôm nay
17. Hiển thị tất cả các đơn hàng có trạng thái là COMPLETED trong tháng này
18. Hiển thị tất cả các đơn hàng có trạng thái là COMPLETED trong tháng 1 năm 2021
19. Hiển thị tất cả các đơn hàng có trạng thái là COMPLETED trong năm 2021
20. Hiển thị tất cả các đơn hàng có hình thức thanh toán là CASH
21. Hiển thị tất cả các đơn hàng có hình thức thanh toán là CREADIT CARD
22. Hiển thị tất cả các đơn hàng có địa chỉ giao hàng là Hà Nội
23. Hiển thị tất cả các nhân viên có sinh nhật là tháng này
24. Hiển thị tất cả các nhà cung cấp có tên là: (SONY, SAMSUNG, TOSHIBA, APPLE)
25. Hiển thị tất cả các nhà cung cấp không có tên là: (SAMSUNG, APPLE)
26. Hiển thị tất cả các nhà cung cấp có địa chỉ ở Quận Hải Châu và Quận Thanh Khê.
27. Hiển thị tất cả các nhà cung cấp có địa chỉ ở Quận Hải Châu hoặc Quận Thanh Khê.
28. Hiển thị tất cả các khách hàng có sinh nhật là ngày hôm nay.
29. Hiển thị xem có bao nhiêu mức giảm giá khác nhau.
30. Hiển thị xem có bao nhiêu mức giảm giá khác nhau và số lượng mặt hàng có mức giảm giá đó.
31. Hiển thị xem có bao nhiêu mức giảm giá khác nhau và số lượng mặt hàng có mức giảm giá đó, sắp xếp theo số lượng giảm giá giảm dần.
32. Hiển thị xem có bao nhiêu mức giảm giá khác nhau và số lượng mặt hàng có mức giảm giá đó, sắp xếp theo số lượng giảm giá tăng dần, chỉ hiển thị các mức giảm giá có số lượng mặt hàng >= 5
33. Hiển thị xem có bao nhiêu mức tuổi khác nhau của khách hàng và số lượng khách hàng có mức tuổi đó, sắp xếp theo số lượng khách hàng tăng dần.
34. Hiển thị xem có bao nhiêu mức tuổi khác nhau của nhân viên và số lượng nhân viên có mức tuổi đó, sắp xếp theo số lượng nhân viên giảm dần.
35. Hiển thị số lượng đơn hàng theo từng ngày khác nhau sắp xếp theo số lượng đơn hàng giảm dần.
36. Hiển thị số lượng đơn hàng theo từng tháng khác nhau sắp xếp theo số lượng đơn hàng giảm dần.
37. Hiển thị số lượng đơn hàng theo từng năm khác nhau sắp xếp theo số lượng đơn hàng giảm dần.
38. Hiển thị số lượng đơn hàng theo từng năm khác nhau sắp xếp theo số lượng đơn hàng giảm dần, chỉ hiển thị các năm có số lượng đơn hàng >= 5.

Phần D: Truy vấn nâng cao

1. Hiển thị tất cả các mặt hàng cùng với CategoryName.

SELECT ProductName, CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryId = Categories.Id

1. Hiển thị tất cả các mặt hàng cùng với SupplierName.

SELECT ProductName, SupplierName
FROM Products
INNER JOIN Suppliers ON Products.SupplierId = Suppliers.Id

1. Hiển thị tất cả các mặt hàng cùng với thông tin chi tiết của Category và Supplier.

SELECT ProductName, CategoryName, SupplierName
FROM Products
INNER JOIN Categories ON Products.CategoryId = Categories.Id
INNER JOIN Suppliers ON Products.SupplierId = Suppliers.Id

1. Hiển thị tất cả các đơn hàng cùng với thông tin chi tiết khách hàng Customer.
2. Hiển thị tất cả các đơn hàng cùng với thông tin chi tiết nhân viên Employee.
3. Hiển thị tất cả các đơn hàng cùng với thông tin chi tiết khách hàng Customer và nhân viên Employee.
4. Hiển thị tất cả danh mục (Categories) với số lượng hàng hóa trong mỗi danh mục
   • Dùng INNER JOIN + GROUP BY với lệnh COUNT
   • Dùng SubQuery với lệnh COUNT
5. Hiển thị tất cả nhà cung cấp (Suppliers) với số lượng hàng hóa mỗi nhà cung cấp
   • Dùng INNER JOIN + GROUP BY với lệnh COUNT
   • Dùng SubQuery với lệnh COUNT
6. Hiển thị tất cả các mặt hàng được bán trong khoảng từ ngày, đến ngày
7. Hiển thị tất cả các khách hàng mua hàng trong khoảng từ ngày, đến ngày
8. Hiển thị tất cả các khách hàng mua hàng (với tổng số tiền) trong khoảng từ ngày, đến ngày
   • Dùng INNER JOIN + GROUP BY với lệnh SUM
   • Dùng SubQuery với lệnh SUM
9. Hiển thị tất cả đơn hàng với tổng số tiền của đơn hàng đó
10. Hiển thị tất cả các nhân viên bán hàng với tổng số tiền bán được
11. Hiển thị tất cả các mặt hàng không bán được
12. Hiển thị tất cả các nhà cung cấp không bán được trong khoảng từ ngày, đến ngày
13. Hiển thị top 3 các nhân viên bán hàng với tổng số tiền bán được từ cao đến thấp trong khoảng từ ngày, đến ngày
14. Hiển thị top 5 các khách hàng mua hàng với tổng số tiền mua được từ cao đến thấp trong khoảng từ ngày, đến ngày
15. Hiển thị danh sách các mức giảm giá của cửa hàng
16. Hiển thị tất cả danh mục (Categories) với tổng số tiền bán được trong mỗi danh mục
    • Dùng INNER JOIN + GROUP BY với lệnh SUM
    • Dùng SubQuery với lệnh SUM
17. Hiển thị tất cả đơn hàng với tổng số tiền mà đã được giao hàng thành công trong khoảng từ ngày, đến ngày
18. Hiển thị tất cả đơn hàng có tổng số tiền bán hàng nhiều nhất trong khoảng từ ngày, đến ngày
19. Hiển thị tất cả đơn hàng có tổng số tiền bán hàng ít nhất trong khoảng từ ngày, đến ngày
20. Hiển thị trung bình cộng giá trị các đơn hàng trong khoảng từ ngày, đến ngày
21. Hiển thị các đơn hàng có giá trị cao nhất
22. Hiển thị các đơn hàng có giá trị thấp nhất

Phần E: Views

1. Hiển thị danh sách các mức giảm giá của cửa hàng cùng với số lượng mặt hàng được giảm giá đó, gồm các fields: Discount, NumberOfProducts, Total.
2. Hiển thị tất cả các mặt hàng cùng với thông tin chi tiết của Category và Supplier gồm các fields: Id, Name, Price, Discount, CategoryId, CategoryName, SupplierId, SupplierName.
3. Hiển thị tất cả các đơn hàng cùng với thông tin chi tiết khách hàng Customer và Employee gồm các fields: Id, OrderDate, Status, CustomerId, CustomerName, CustomerAddress, CustomerPhone, EmployeeId, EmployeeName, EmployeeAddress, EmployeePhone, Total.
4. Hiển thị tất cả danh mục (Categories) với số lượng hàng hóa trong mỗi danh mục, gồm các fields: Id, Name, Description, NumberOfProducts.
   • Dùng INNER JOIN + GROUP BY với lệnh COUNT
   • Dùng SubQuery với lệnh COUNT
5. Hiển thị tất cả nhà cung cấp (Suppliers) với số lượng hàng hóa mỗi nhà cung cấp, gồm các fields: Id, Name, Address, PhoneNumber, NumberOfProducts.
   • Dùng INNER JOIN + GROUP BY với lệnh COUNT
   • Dùng SubQuery với lệnh COUNT
6. Hiển thị tất cả các khách hàng mua hàng với tổng số tiền mua hàng, gồm các fields: Id, Name, Address, PhoneNumber, Total.
   • Dùng INNER JOIN + GROUP BY với lệnh SUM
   • Dùng SubQuery với lệnh SUM

CREATE VIEW v_CustomersWithTotal
AS
SELECT
    O.CustomerId,
    C.FirstName + ' ' + C.LastName AS [FullName],
    C.PhoneNumber,
    C.Address,
    SUM((OD.Price * (100 - OD.Discount) / 100) * OD.Quantity) AS [Total]
FROM
    Orders AS O
    INNER JOIN OrderDetails AS OD ON O.Id = OD.OrderId
    INNER JOIN Customers AS C ON O.CustomerId = C.Id
GROUP BY
    O.CustomerId,
    C.FirstName + ' ' + C.LastName,
    C.PhoneNumber,
    C.Address

Dùng Sub Query

CREATE VIEW v_CustomersWithTotal
AS
SELECT V.*
FROM
(
    SELECT
    DISTINCT
        C.Id,
        C.FirstName + ' ' + C.LastName AS [FullName],
        C.PhoneNumber,
        C.Address,
        (
            SELECT SUM((OD.Price * (100 - OD.Discount) / 100) * OD.Quantity)
            FROM OrderDetails AS OD
                INNER JOIN Orders AS O ON OD.OrderId = O.Id
            WHERE O.CustomerId = C.Id
        ) AS [Total]

    FROM
    Customers AS C
) AS V
WHERE V.Total IS NOT NULL

1. Hiển thị tất cả các nhân viên bán hàng với tổng số tiền bán được, gồm các fields: Id, Name, Address, PhoneNumber, Total.
   • Dùng INNER JOIN + GROUP BY với lệnh SUM
   • Dùng SubQuery với lệnh SUM
2. Hiển thị tất cả các mặt hàng không bán được, gồm các fields: Id, Name, Price, Discount, CategoryId, CategoryName, SupplierId, SupplierName.

CREATE VIEW v_Products_NotSold
AS
SELECT
   P.*, C.Name AS 'CategoryName', S.Name AS 'SupplierName'
FROM Products AS P
   INNER JOIN Categories AS C ON P.CategoryId = C.Id
   INNER JOIN Suppliers AS S ON P.SupplierId = S.Id
WHERE NOT EXISTS (
    SELECT P.Id FROM OrderDetails AS OD WHERE P.Id = OD.ProductId
)

1. Hiển thị tất cả các nhà cung cấp không bán được, gồm các fields: Id, Name, Address, PhoneNumber.
2. Hiển thị tất cả các nhân viên không bán được hàng, gồm các fields: Id, Name, Address, PhoneNumber.

CREATE VIEW v_Employess_NotSellProducts
AS
SELECT * FROM Employees AS E
WHERE NOT EXISTS (
    SELECT O.EmployeeId FROM Orders AS O WHERE E.Id = O.EmployeeId
)

Phần F: Stored Procedures

1. Hiển thị tất cả các mặt hàng có giảm giá <= @MinDiscount
2. Hiển thị tất cả các mặt hàng có số lượng tồn kho <= @MinStock
3. Hiển thị tất cả các mặt hàng có Giá bán sau khi đã giảm giá <= @Total
4. Hiển thị tất cả các khách hàng có địa chỉ ở @Address
5. Hiển thị tất cả các khách hàng có năm sinh @YearOfBirth
6. Hiển thị tất cả các khách hàng có tuổi từ @MinAge đến MaxAge.
7. Hiển thị tất cả các khách hàng có sinh nhật là @Date
8. Hiển thị tất cả các đơn hàng có trạng thái là @Status trong ngày @Date
9. Hiển thị tất cả các đơn hàng chưa hoàn thành trong tháng @Month của năm @Year
10. Hiển thị tất cả các đơn hàng có hình thức thanh toán là @PaymentMethod
11. Hiển thị tất cả đơn hàng theo trạng thái @Status với tổng số tiền của đơn hàng đó trong khoảng từ ngày @FromDate, đến ngày @ToDate
12. Hiển thị tất cả các nhân viên bán hàng theo trạng thái @Status với tổng số tiền bán được trong khoảng từ ngày @FromDate, đến ngày @ToDate
13. Hiển thị tất cả các mặt hàng không bán được trong khoảng từ ngày @FromDate, đến ngày @ToDate
14. Hiển thị tất cả các nhà cung cấp không bán được trong khoảng từ ngày @FromDate, đến ngày @ToDate
15. Hiển thị tất cả các khách hàng mua hàng với tổng số tiền trong khoảng từ ngày @FromDate, đến ngày @ToDate

Phần G: Functions

1. Viết 1 scalar function ghép FirstName @FirstName và LastName @LastName, tên function là udf_GetFullName
2. Viết 1 scalar funtion tính total price (@Price, @Discount, @Quantity), tên function là udf_Product_GetTotalPrice
3. Viết 1 scalar function tính total price của 1 Order (@OrderID), tên function là udf_Order_GetTotalPrice
4. Viết table function trả về table gồm các fields: OrderId, ProductId, ProductName, CategoryId, CategoryName, Quantity, Price, Discount, Total với tham số @OrderId, tên function là udf_Order_GetOrderDetails
5. Viết table function trả về các mức giá của 1 danh mục loại sản phẩm, với tham số @CategoryId, tên function là udf_Category_GetCategoryPrices
6. Viết table function trả về các mức giảm giá của 1 danh mục loại sản phẩm, với tham số @CategoryId, tên function là udf_Category_GetCategoryDiscounts
7. Viết scalar function nhập vào năm sinh, trả về số tuổi, tên function là udf_CalculateAge
8. Viết scalar function chuyển đổi UNICODE có dấu sang không dấu, tên function là udf_ConvertUnicodeToNonUnicode, ví dụ: udf_ConvertUnicodeToNonUnicode(N'Ngô Thanh Tùng') -> 'Ngo Thanh Tung'.

CREATE OR ALTER FUNCTION [dbo].[udf_ConvertUnicodeToNonUnicode]
(
   @Text NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    IF (@Text IS NULL OR @Text = '')  RETURN ''

    DECLARE @RT NVARCHAR(MAX)
    DECLARE @SIGN_CHARS NCHAR(256)
    DECLARE @UNSIGN_CHARS NCHAR (256)

    SET @SIGN_CHARS = N'ăâđêôơưàảãạáằẳẵặắầẩẫậấèẻẽẹéềểễệếìỉĩịíòỏõọóồổỗộốờởỡợớùủũụúừửữựứỳỷỹỵýĂÂĐÊÔƠƯÀẢÃẠÁẰẲẴẶẮẦẨẪẬẤÈẺẼẸÉỀỂỄỆẾÌỈĨỊÍÒỎÕỌÓỒỔỖỘỐỜỞỠỢỚÙỦŨỤÚỪỬỮỰỨỲỶỸỴÝ' + NCHAR(272) + NCHAR(208)
    SET @UNSIGN_CHARS = N'aadeoouaaaaaaaaaaaaaaaeeeeeeeeeeiiiiiooooooooooooooouuuuuuuuuuyyyyyAADEOOUAAAAAAAAAAAAAAAEEEEEEEEEEIIIIIOOOOOOOOOOOOOOOUUUUUUUUUUYYYYYDD'

    DECLARE @COUNTER int
    DECLARE @COUNTER1 int

    SET @COUNTER = 1
    WHILE (@COUNTER <= LEN(@Text))
    BEGIN
        SET @COUNTER1 = 1
        WHILE (@COUNTER1 <= LEN(@SIGN_CHARS) + 1)
        BEGIN
            IF UNICODE(SUBSTRING(@SIGN_CHARS, @COUNTER1, 1)) = UNICODE(SUBSTRING(@Text,@COUNTER ,1))
            BEGIN
                IF @COUNTER = 1
                    SET @Text = SUBSTRING(@UNSIGN_CHARS, @COUNTER1, 1) + SUBSTRING(@Text, @COUNTER + 1, LEN(@Text) - 1)
                ELSE
                    SET @Text = SUBSTRING(@Text, 1, @COUNTER - 1) + SUBSTRING(@UNSIGN_CHARS, @COUNTER1, 1) + SUBSTRING(@Text, @COUNTER + 1, LEN(@Text) - @COUNTER)
                BREAK
            END
            SET @COUNTER1 = @COUNTER1 + 1
        END
        SET @COUNTER = @COUNTER + 1
    END
   SET @Text = REPLACE(@Text, '--', '-')
   SET @Text = REPLACE(@Text, '--', '-')
   SET @Text = REPLACE(@Text, '--', '-')
   SET @Text = REPLACE(@Text, '--', '-')
   RETURN @Text
END

1. Viết scalar function chuyển đổi tên sản phẩm thành SEO Url, tên function là udf_ConvertToSeoUrl, ví dụ: udf_ConvertToSeoUrl(N'Điện thoại iPhone 12 Pro Max 256GB') -> 'dien-thoai-iphone-12-pro-max-256gb'.

Phần H: Triggers

1. Tạo 1 trigger trên bảng Orders: chặn update bảng Orders khi các Orders có Status = 'COMPLETED' hoặc 'CANCELLED'.
2. Tạo 1 trigger trên bảng Orders: chặn delete bảng Orders khi các Orders có Status = 'COMPLETED' hoặc 'CANCELLED'.
3. Tạo 1 trigger trên bảng OrderDetails: chặn update bảng OrderDetails khi các Orders có Status = 'COMPLETED' hoặc 'CANCELLED'.
4. Tạo 1 trigger trên bảng OrderDetails: chặn delete bảng OrderDetails khi các Orders có Status = 'COMPLETED' hoặc 'CANCELLED'.
5. Tạo 1 trigger trên bảng Orders: cập nhật tồn kho (Stock), trừ đi số lượng đã bán (Quantity) khi Orders có Status = 'WAITING', cộng lại số lượng đã bán (Quantity) khi Orders có Status = 'CANCELED'.
6. Tạo 1 trigger trên bảng Orders: ghi nhật ký khi Orders được giao hàng thành công (Status = 'COMPLETED'), Ghi vào bảng OrderLogs (Id, OrderId, Status, CreatedDate, EmployeeId, CustomerId)

Phần I: Transactions

1. Tạo store procedure với 1 transaction để thêm 1 Order mới, với các OrderDetails, và cập nhật tồn kho (Stock) của các mặt hàng, gồm các bước:
   • Kiểm tra tồn kho (Stock) của mặt hàng có ProductId = @ProductId, nếu Stock < @Quantity thì rollback transaction, và trả về lỗi 'Số lượng tồn kho không đủ'
   • Tạo 1 Order mới, với Status = 'WAITING', CreatedDate = GETDATE(), EmployeeId = @EmployeeId, CustomerId = @CustomerId
   • Tạo 1 OrderDetail mới, với OrderId = OrderId vừa tạo, ProductId = @ProductId, Quantity = @Quantity, Price = Product.Price, Discount = Product.Discount
   • Cập nhật tồn kho (Stock) của mặt hàng có ProductId = @ProductId, trừ đi @Quantity.