Ngày 4 — Database và SQL

-- Cleanup nếu chạy lại
IF OBJECT_ID('OrderItems', 'U') IS NOT NULL DROP TABLE OrderItems;
IF OBJECT_ID('Orders',     'U') IS NOT NULL DROP TABLE Orders;
IF OBJECT_ID('Products',   'U') IS NOT NULL DROP TABLE Products;
IF OBJECT_ID('Customers',  'U') IS NOT NULL DROP TABLE Customers;
IF OBJECT_ID('Employees',  'U') IS NOT NULL DROP TABLE Employees;
IF OBJECT_ID('Users',      'U') IS NOT NULL DROP TABLE Users;

CREATE TABLE Customers (
    Id    INT PRIMARY KEY IDENTITY,
    Name  NVARCHAR(100) NOT NULL,
    Email NVARCHAR(150),
    City  NVARCHAR(50),
    CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME()
);

CREATE TABLE Products (
    Id    INT PRIMARY KEY IDENTITY,
    Name  NVARCHAR(100) NOT NULL,
    Price DECIMAL(18, 2) NOT NULL,
    Stock INT NOT NULL DEFAULT 0
);

CREATE TABLE Orders (
    Id         INT PRIMARY KEY IDENTITY,
    CustomerId INT NOT NULL,
    OrderDate  DATE NOT NULL,
    Status     NVARCHAR(20) NOT NULL DEFAULT 'Pending',
    Total      DECIMAL(18, 2),
    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId) REFERENCES Customers(Id)
);

CREATE TABLE OrderItems (
    Id        INT PRIMARY KEY IDENTITY,
    OrderId   INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity  INT NOT NULL,
    UnitPrice DECIMAL(18, 2) NOT NULL,
    CONSTRAINT FK_Items_Orders   FOREIGN KEY (OrderId)   REFERENCES Orders(Id),
    CONSTRAINT FK_Items_Products FOREIGN KEY (ProductId) REFERENCES Products(Id)
);

CREATE TABLE Employees (
    Id        INT PRIMARY KEY IDENTITY,
    Name      NVARCHAR(100) NOT NULL,
    ManagerId INT NULL,
    Salary    DECIMAL(18, 2),
    Dept      NVARCHAR(50),
    CONSTRAINT FK_Emp_Manager FOREIGN KEY (ManagerId) REFERENCES Employees(Id)
);

CREATE TABLE Users (
    Id    INT PRIMARY KEY IDENTITY,
    Name  NVARCHAR(100),
    Email NVARCHAR(150)
);

CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);
CREATE INDEX IX_Orders_Date       ON Orders(OrderDate);
CREATE INDEX IX_Items_OrderId     ON OrderItems(OrderId);

INSERT INTO Customers (Name, Email, City) VALUES
('Alice Nguyen',  'alice@gmail.com',   'HCM'),
('Bob Tran',      'bob@gmail.com',     'HN'),
('Charlie Le',    'charlie@gmail.com', 'HCM'),
('Dave Pham',     'dave@gmail.com',    'DN'),
('Eve Hoang',     'eve@gmail.com',     'HCM'),
('Frank Vo',      'frank@gmail.com',   'HCM'),
('Grace Bui',     'grace@gmail.com',   'HN'),
('Henry Do',      NULL,                'CT');     -- Henry chưa từng mua (test LEFT JOIN)

INSERT INTO Products (Name, Price, Stock) VALUES
('Laptop Dell XPS',     32000000, 5),
('iPhone 15',           25000000, 12),
('AirPods Pro',          5500000, 30),
('Bàn phím cơ Keychron', 2200000, 25),
('Chuột Logitech MX',    1800000, 50),
('Màn hình LG 27 4K',   12000000, 8);

INSERT INTO Orders (CustomerId, OrderDate, Status, Total) VALUES
(1, '2026-01-15', 'Paid',      37500000),
(1, '2026-02-10', 'Paid',       5500000),
(1, '2026-03-22', 'Pending',    2200000),
(2, '2026-01-05', 'Paid',      25000000),
(3, '2026-02-18', 'Paid',      14200000),
(3, '2026-04-01', 'Cancelled',  1800000),
(4, '2026-03-12', 'Paid',      32000000),
(5, '2026-04-20', 'Pending',   18000000),
(5, '2026-05-02', 'Paid',       5500000),
(6, '2026-01-28', 'Paid',      37000000),
(6, '2026-05-15', 'Paid',       7700000),
(7, '2026-04-10', 'Pending',   12000000);

INSERT INTO Employees (Name, ManagerId, Salary, Dept) VALUES
('CEO An',          NULL, 80000000, 'Executive'),
('CTO Bao',            1, 60000000, 'Tech'),
('CFO Cuong',          1, 55000000, 'Finance'),
('Lead Dev Dao',       2, 45000000, 'Tech'),
('Senior Dev Phong',   4, 35000000, 'Tech'),
('Junior Dev Giang',   4, 18000000, 'Tech'),
('Fresher Hieu',       4, 12000000, 'Tech');

INSERT INTO Users (Name, Email) VALUES
('User A', 'duplicate@mail.com'),
('User B', 'duplicate@mail.com'),
('User C', 'unique1@mail.com'),
('User D', 'another@mail.com'),
('User E', 'another@mail.com');

           [Root]
          /  |   \
     [..]  [..]  [..]      ← internal
     / |    | \    | \
   [L][L]  [L][L] [L][L]   ← leaf (sorted)

CLUSTERED (PK = Id) → data sorted by Id

Leaf:
  [Id=1, full row data] → [Id=2, full row data] → [Id=3, ...]

NON-CLUSTERED (on Email) → tree riêng sorted by Email

Leaf:
  [Email='a@b.com', Id=42] → [Email='b@c.com', Id=7] → ...
                       ↓
              Lookup Id=42 trong clustered → lấy full row

-- Clustered (default = PK)
CREATE TABLE Orders (
    Id INT PRIMARY KEY IDENTITY,   -- = clustered tự động
    ...
);

-- Non-clustered đơn cột
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);

-- Non-clustered composite (thứ tự cột RẤT QUAN TRỌNG)
CREATE INDEX IX_Orders_Cust_Date ON Orders(CustomerId, OrderDate);

-- Covering — INCLUDE thêm cột để query không cần seek về bảng
CREATE INDEX IX_Orders_Cover ON Orders(CustomerId)
INCLUDE (OrderDate, Total, Status);

-- Filtered — chỉ index 1 phần dữ liệu
CREATE INDEX IX_Orders_Active ON Orders(CustomerId)
WHERE Status <> 'Cancelled';

-- Unique
CREATE UNIQUE INDEX IX_Users_Email ON Users(Email);

-- ❌ Non-sargable — function lên cột
WHERE YEAR(OrderDate) = 2026         -- engine phải tính YEAR cho mỗi row
WHERE UPPER(Email) = 'A@B.COM'       -- function lên cột
WHERE Total + Tax > 100              -- biểu thức lên cột
WHERE Status LIKE '%Active%'         -- wildcard ở đầu

-- ✅ Sargable
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
WHERE Email = 'a@b.com'              -- nếu collation case-insensitive
WHERE Total > 100 - Tax
WHERE Status LIKE 'Active%'          -- wildcard cuối OK

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM Orders WHERE CustomerId = 1;

-- Output:
-- Table 'Orders'. Scan count 1, logical reads 8, physical reads 2
-- CPU time = 0 ms, elapsed time = 12 ms

-- INNER — chỉ row match cả 2
SELECT c.Name, o.Total
FROM Customers c
INNER JOIN Orders o ON o.CustomerId = c.Id;

-- LEFT — tất cả Customers + matching Orders (NULL nếu không có)
SELECT c.Name, o.Total
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id;

-- RIGHT — đảo lại (hiếm dùng, swap LEFT thuận)
-- FULL OUTER — tất cả row 2 bên
-- CROSS — tích Descartes m×n

-- Tìm khách hàng chưa mua đơn nào — 3 cách:

-- Cách 1: LEFT JOIN ... IS NULL
SELECT c.* FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
WHERE o.Id IS NULL;

-- Cách 2: NOT EXISTS (THƯỜNG TỐT NHẤT)
SELECT c.* FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id);

-- Cách 3: NOT IN (cẩn thận NULL!)
SELECT c.* FROM Customers c
WHERE c.Id NOT IN (SELECT CustomerId FROM Orders WHERE CustomerId IS NOT NULL);

-- Employees có ManagerId trỏ về Employees.Id
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON m.Id = e.ManagerId;

-- Output:
-- CEO An | NULL
-- CTO Bao | CEO An
-- Lead Dev Dao | CTO Bao
-- ...

function_name() OVER (
    [PARTITION BY col1, col2 ...]
    [ORDER BY col1 ...]
    [ROWS|RANGE BETWEEN ... AND ...]
)

-- Cùng 100, 100, 90, 80
SELECT
    Value,
    ROW_NUMBER() OVER (ORDER BY Value DESC) AS Rn,    -- 1, 2, 3, 4
    RANK()       OVER (ORDER BY Value DESC) AS Rk,    -- 1, 1, 3, 4 (skip)
    DENSE_RANK() OVER (ORDER BY Value DESC) AS Dk     -- 1, 1, 2, 3 (no skip)
FROM Scores;

-- Đơn hàng mới nhất của MỖI khách
WITH Ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
    FROM Orders
)
SELECT * FROM Ranked WHERE rn = 1;

SELECT
    OrderDate, Total,
    SUM(Total) OVER (ORDER BY OrderDate) AS RunningTotal,
    AVG(Total) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS MovingAvg7Days
FROM Orders;

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 1000 WHERE Id = 1;
    UPDATE Accounts SET Balance = Balance + 1000 WHERE Id = 2;
    
    IF (SELECT Balance FROM Accounts WHERE Id = 1) < 0
        THROW 50001, 'Insufficient funds', 1;
    
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- ...
COMMIT;

SELECT * FROM Orders WITH (NOLOCK);
-- ↑ = READ UNCOMMITTED cho query này — cho phép dirty read

Transaction A:                  Transaction B:
  UPDATE Account 1 (lock A1)      UPDATE Account 2 (lock A2)
  UPDATE Account 2 ← chờ A2       UPDATE Account 1 ← chờ A1
  
  (chờ vô tận — DEADLOCK)