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

1. Tạo 3 lược đồ CSDL sau:

  •  Lược đồ chứa các bảng liên quan đến employee, đặt tên lược đồ là HumanResource
  •  Lược đồ chứa các bảng liên quan đến sales, customer
  •  Lược đồ chứa các bảng liên quan đến product và category

2. Thực hiện các truy vấn sau:

a. Cho biết có bao nhiêu loại person

b. Danh sách các khách hàng bao gồm họ tên, địa chỉ. Có bao nhiêu khách hàng

c. Có hai loại khách hàng: loại IN (khách hàng mua lẻ), SC ( khách hàng là cửa hàng). Liệt kê danh sách họ tên khách hàng mua lẻ và danh sách khách cửa hàng. Có bao nhiêu khách hàng mỗi loại

d. Hiển thị các nhân viên bao gồm mã số, họ tên, và số khách hàng cửa hàng mà nhân viên đó phụ trách.

e. Liệt kê thông tin đầy đủ của tất cả nhân viên bao gồm địa chỉ, số điện thoại, thành phố, vùng miền

f. Đếm số nhân viên mỗi loại

g. Hiển thị tên phòng, và họ tên nhân viên hiện tại mỗi phòng

h. Thống kê số lượng nhân viên mỗi phòng ban

3. Liệt kê thông tin của tất cả các xe đạp (Bikes) bao gồm tên gọi, màu sắc, ngày sản xuất (97 loại xe)

4. Liệt kê thông tin của tất cả các xe đạp leo núi (Mountain Bikes) bao gồm tên gọi, màu sắc, ngày sản xuất

5. Bảng workOrder chứa thông tin của các đơn đặt hàng yêu cầu sản xuất theo số lượng và thời hạn. Bảng cũng chứa cả số sản phẩm bị lỗi (scrap) và lý do lỗi cho từng đơn hàng. Thống kê số lượng đặt hàng sản xuất (OrderQty) của tất cả loại xe đạp trong mỗi năm

6. Bảng TransactionHistory chứa tất cả các loại giao dịch. Có 3 loại giao dịch chính: mua hàng Purchase order (P), đặt hàng WorkOrder (W) và bán hàng Sale (S). Thống kê tổng số tiền (actual cost) theo từng loại giao dịch của mỗi quý và năm

7. Thống kê tên mặt hàng và số lượng mua hàng bình quân mỗi năm. Tìm 3 mặt hàng có lượng đặt hàng bình quân cao nhất trong năm 2008

8. Thống kê tên khách hàng (bao gồm khách mua lẻ và khách cửa hàng) cùng tổng số tiền mua hàng mỗi năm. Tìm khách hàng mua hàng nhiều nhất trong năm 2008

9. Liệt kê danh sách các măt hàng không bán được

10. Liệt kê tên các nhà cung cấp chuyên cung cấp phụ kiện (accessories)

11. Trong bảng Product, trường DaysToManufacture dùng để chỉ số ngày cần thiết để sản xuất sản phẩm. Hãy thống kê các sản phẩm theo số ngày cần để sản xuất theo dạng thông thường và dạng bảng pivot.

Hướng dẫn giải

Các bạn vẫn nên tự giải trước nhé vì có như vậy mới mau lên tay được nè ❤️

Câu 1: Dùng Database Diagram Tool để làm. Hướng dẫn coi thêm ở đây

Câu 2:

--2.a
SELECT DISTINCT [PersonType]
FROM [Person].[Person]

--2.b
SELECT [FirstName], [MiddleName], [LastName], [AddressLine1], [AddressLine2]
FROM [Person].[Person] P
         JOIN [Sales].[Customer] C ON P.BusinessEntityID = C.CustomerID
         JOIN [Person].[BusinessEntity] B ON P.BusinessEntityID = B.BusinessEntityID
         JOIN [Person].[BusinessEntityAddress] A ON A.BusinessEntityID = B.BusinessEntityID
         JOIN [Person].[Address] AD ON AD.AddressID = A.AddressID

--2.c
SELECT P.[PersonType], COUNT(*) AS 'SL'
FROM [Person].[Person] P
         JOIN [Sales].[Customer] C ON P.BusinessEntityID = C.CustomerID
WHERE P.[PersonType] IN ('IN', 'SC')
GROUP BY P.[PersonType]
SELECT [FirstName], [MiddleName], [LastName], P.[PersonType]
FROM [Person].[Person] P
         JOIN [Sales].[Customer] C ON P.BusinessEntityID = C.CustomerID
WHERE P.[PersonType] = 'IN'

SELECT [FirstName], [MiddleName], [LastName], P.[PersonType]
FROM [Person].[Person] P
         JOIN [Sales].[Customer] C ON P.BusinessEntityID = C.CustomerID
WHERE P.[PersonType] = 'SC'

--2.d
SELECT [FirstName], [MiddleName], [LastName], COUNT(*)
FROM [Sales].[SalesPerson] SP
         JOIN [HumanResources].[Employee] E ON SP.BusinessEntityID = E.BusinessEntityID
         JOIN [Person].[Person] P ON P.BusinessEntityID = E.BusinessEntityID
         JOIN [Sales].[Store] ST ON ST.SalesPersonID = SP.BusinessEntityID
GROUP BY [FirstName], [MiddleName], [LastName]

SELECT [SalesPersonID], COUNT(*) AS 'SO CUA HANG QUAN LY'
FROM [Sales].[Store]
GROUP BY [SalesPersonID]

--2.e
SELECT 'HO VA TEN'= [FirstName] + ' ' + [MiddleName] + ' ' + [LastName], [PhoneNumber]
FROM [HumanResources].[Employee] E
         JOIN [Person].[Person] P ON P.BusinessEntityID = E.BusinessEntityID
         JOIN [Person].[PersonPhone] ph ON p.BusinessEntityID = ph.BusinessEntityID
--2.f
SELECT [JobTitle], COUNT(*)
FROM [HumanResources].[Employee]
GROUP BY [JobTitle]

Câu 3:

SELECT P.[Name], [Color], [DaysToManufacture]
FROM [Production].[Product] P
         JOIN [Production].[ProductSubcategory] PS ON P.ProductSubcategoryID = P.ProductSubcategoryID
         JOIN [Production].[ProductCategory] PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE PC.[Name] = 'Bikes'

Câu 4:

SELECT P.[Name], [Color], [DaysToManufacture]
FROM [Production].[Product] P
         JOIN [Production].[ProductSubcategory] PS ON P.ProductSubcategoryID = P.ProductSubcategoryID
WHERE PS.[Name] = 'Mountain Bikes'

Câu 5

SELECT DISTINCT P.Name,
                YEAR([StartDate])  N'NĂM',
                SUM([OrderQty])    N'TỔNG SỐ LƯỢNG',
                SUM([ScrappedQty]) 'TONG SO SP LOI'
FROM [Production].[WorkOrder] WO
         JOIN [Production].[Product] P ON WO.ProductID = P.ProductID
         JOIN [Production].[ProductSubcategory] PS ON P.ProductSubcategoryID = P.ProductSubcategoryID
         JOIN [Production].[ProductCategory] PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE PC.[Name] = 'Bikes'
GROUP BY P.Name, YEAR([StartDate])

Câu 6:

SELECT CASE [TransactionType]
           WHEN 'W' THEN 'WORK ORDER'
           WHEN 'P' THEN 'PIRCHASE'
           WHEN 'S' THEN 'SALES'
           END,
       DATEPART(QQ, [TransactionDate]) N'QÚY',
       YEAR([TransactionDate])         N'NĂM',
       SUM([ActualCost])               N'TỔNG SỐ TIỀN'
FROM [Production].[TransactionHistory] TH
         JOIN [Production].[Product] P ON TH.ProductID = P.ProductID
GROUP BY [TransactionType], DATEPART(QQ, [TransactionDate]), YEAR([TransactionDate])
ORDER BY [TransactionType]

Câu 7:

SELECT TOP 3 WITH TIES P.Name, AVG([OrderQty]) N'TỔNG SỐ'
FROM [Purchasing].[PurchaseOrderHeader] POH
         JOIN [Purchasing].[PurchaseOrderDetail] POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
         JOIN [Production].[Product] P ON P.ProductID = POD.ProductID
WHERE YEAR([OrderDate]) = 2008
GROUP BY P.Name
ORDER BY AVG([OrderQty]) DESC

Câu 8: Sắp xếp theo thứ tự giảm dần rồi lấy TOP 1

Câu 9:

----------Cách 1:
SELECT *
SELECT *
FROM [Production].[Product] P
WHERE P.ProductID NOT IN
      (SELECT P.ProductID
       FROM [Sales].[SalesOrderDetail] SOD
                JOIN [Sales].[SalesOrderHeader] SOH ON SOD.SalesOrderID = SOD.SalesOrderID
       WHERE YEAR([OrderDate]) = 2008)

----------Cách 2:
SELECT *
FROM [Production].[Product] P
WHERE NOT EXISTS
    (SELECT *
     FROM [Sales].[SalesOrderDetail] SOD
              JOIN [Sales].[SalesOrderHeader] SOH ON SOD.SalesOrderID = SOD.SalesOrderID
     WHERE YEAR([OrderDate]) = 2008
       AND SOD.ProductID = P.ProductID)

----------Cách 3:
SELECT DISTINCT *
FROM (SELECT SOD.SalesOrderID, SOD.ProductID
      FROM [Sales].[SalesOrderDetail] SOD
               JOIN [Sales].[SalesOrderHeader] SOH ON SOD.SalesOrderID = SOD.SalesOrderID
      WHERE YEAR([OrderDate]) = 2008) DD
         RIGHT JOIN [Production].[Product] P ON P.ProductID = DD.ProductID
WHERE SalesOrderID IS NULL

Bonus 😂Tìm tên sản phẩm bán chạy nhất

SELECT TOP 1 WITH TIES SOD.ProductID, P.Name, PC.Name, COUNT(*) AS N'SỐ LẦN BÁN'
FROM [Production].[Product] P
         JOIN [Sales].[SalesOrderDetail] SOD ON P.ProductID = SOD.ProductID
         JOIN [Sales].[SalesOrderHeader] SOH ON SOD.SalesOrderID = SOD.SalesOrderID
WHERE YEAR([OrderDate]) = 2011
GROUP BY SOD.ProductID, P.Name, PC.Name
ORDER BY COUNT(*) DESC

Câu 10:

SELECT DISTINCT V.Name
FROM [Purchasing].[Vendor] V
         JOIN [Purchasing].[PurchaseOrderHeader] POH ON V.BusinessEntityID = POH.VendorID
         JOIN [Purchasing].[PurchaseOrderDetail] POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
         JOIN [Production].[Product] P ON P.ProductID = POD.ProductID
         JOIN [Production].[ProductSubcategory] PS ON PS.ProductSubcategoryID = P.ProductSubcategoryID
         JOIN [Production].[ProductCategory] PC ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE PC.Name = 'Accessories'

Câu 11:

--Thống kê doanh số bán hàng của nhân viên bán hàng theo năm
SELECT [FirstName] + ' ' + [MiddleName] + ' ' + [LastName] AS 'NAME',
       YEAR([OrderDate])                                   AS 'YEAR',
       SUM([SubTotal])                                     AS N'DOANH SỐ BÁN HÀNG'
FROM [Sales].[SalesOrderHeader] SOH
         JOIN [Sales].[SalesPerson] SP ON
    SOH.SalesPersonID = SP.BusinessEntityID
         JOIN [HumanResources].[Employee] E ON E.BusinessEntityID = SP.BusinessEntityID
         JOIN [Person].[Person] P ON P.BusinessEntityID = SP.BusinessEntityID
GROUP BY [FirstName] + ' ' + [MiddleName] + ' ' + [LastName], YEAR([OrderDate])
ORDER BY [FirstName] + ' ' + [MiddleName] + ' ' + [LastName]

--Dùng Pivot
SELECT [NAME], [2011], [2012], [2013], [2014]
FROM (SELECT [FirstName] + ' ' + [MiddleName] + ' ' + [LastName] AS 'NAME',
             YEAR([OrderDate])                                   AS 'NAM',
             [SubTotal] --BỎ DÙNG HÀM SUM Ở ĐÂY--
      FROM [Sales].[SalesOrderHeader] SOH
               JOIN [Sales].[SalesPerson] SP ON
          SOH.SalesPersonID = SP.BusinessEntityID
               JOIN [HumanResources].[Employee] E ON E.BusinessEntityID = SP.BusinessEntityID
               JOIN [Person].[Person] P ON P.BusinessEntityID = SP.BusinessEntityID) AS PVT --Đặt tên bất kì nhưng ko dùng lại--
         PIVOT (SUM([SubTotal]) FOR
        NAM IN ([2011],[2012],[2013],[2014])) AS P