Содержание курса "Продвинутый курс по MS SQL"

SET NOCOUNT ON;
USE master;
GO

IF DB_ID(N'TechStore') IS NOT NULL
BEGIN
    ALTER DATABASE TechStore SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TechStore;
END
GO

CREATE DATABASE TechStore;
GO

ALTER DATABASE TechStore SET RECOVERY SIMPLE;
GO

USE TechStore;
GO

CREATE TABLE dbo.Categories
(
    CategoryID       INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name             NVARCHAR(100)     NOT NULL,
    ParentCategoryID INT               NULL
        REFERENCES dbo.Categories (CategoryID)
);

CREATE TABLE dbo.Products
(
    ProductID   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SKU         NVARCHAR(20)      NOT NULL UNIQUE,
    Name        NVARCHAR(200)     NOT NULL,
    CategoryID  INT               NOT NULL REFERENCES dbo.Categories (CategoryID),
    Price       DECIMAL(18,2)     NOT NULL,
    StockQty    INT               NOT NULL CONSTRAINT DF_Products_Stock  DEFAULT (0),
    Attributes  NVARCHAR(MAX)     NULL,
    IsActive    BIT               NOT NULL CONSTRAINT DF_Products_Active DEFAULT (1)
);

CREATE TABLE dbo.Customers
(
    CustomerID   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FullName     NVARCHAR(200)     NOT NULL,
    Email        VARCHAR(320)      NULL,
    City         NVARCHAR(100)     NOT NULL,
    Segment      NVARCHAR(20)      NOT NULL CONSTRAINT DF_Customers_Segment DEFAULT (N'Retail'),
    RegisteredAt DATETIME2(0)      NOT NULL CONSTRAINT DF_Customers_Reg     DEFAULT (SYSDATETIME())
);

CREATE TABLE dbo.Orders
(
    OrderID     INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerID  INT               NOT NULL REFERENCES dbo.Customers (CustomerID),
    OrderDate   DATETIME2(0)      NOT NULL,
    Status      NVARCHAR(20)      NOT NULL CONSTRAINT DF_Orders_Status DEFAULT (N'New'),
    TotalAmount DECIMAL(18,2)     NULL
);

CREATE TABLE dbo.OrderItems
(
    OrderItemID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    OrderID     INT               NOT NULL REFERENCES dbo.Orders (OrderID),
    ProductID   INT               NOT NULL REFERENCES dbo.Products (ProductID),
    Quantity    INT               NOT NULL,
    UnitPrice   DECIMAL(18,2)     NOT NULL
);

CREATE TABLE dbo.Payments
(
    PaymentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    OrderID   INT               NOT NULL REFERENCES dbo.Orders (OrderID),
    PaidAt    DATETIME2(0)      NOT NULL,
    Amount    DECIMAL(18,2)     NOT NULL,
    Method    NVARCHAR(20)      NOT NULL
);

CREATE TABLE dbo.PriceHistory
(
    HistoryID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ProductID INT               NOT NULL REFERENCES dbo.Products (ProductID),
    OldPrice  DECIMAL(18,2)     NOT NULL,
    NewPrice  DECIMAL(18,2)     NOT NULL,
    ChangedAt DATETIME2(0)      NOT NULL CONSTRAINT DF_PriceHistory_ChangedAt DEFAULT (SYSDATETIME())
);

CREATE TABLE dbo.AuditLog
(
    AuditID   BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    TableName SYSNAME      NOT NULL,
    Action    NVARCHAR(20) NOT NULL,
    Details   NVARCHAR(MAX) NULL,
    CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_AuditLog_CreatedAt DEFAULT (SYSDATETIME())
);

CREATE TABLE dbo.Numbers
(
    N INT NOT NULL PRIMARY KEY
);
GO

USE TechStore;
GO
SET NOCOUNT ON;

WITH L0 AS (SELECT 1 AS c FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(c))
   , L1 AS (SELECT 1 AS c FROM L0 a CROSS JOIN L0 b)
   , L2 AS (SELECT 1 AS c FROM L1 a CROSS JOIN L1 b)
   , L3 AS (SELECT 1 AS c FROM L2 a CROSS JOIN L0 b)
   , Nums AS (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L3)
INSERT dbo.Numbers (N) SELECT n FROM Nums;

SET IDENTITY_INSERT dbo.Categories ON;
INSERT dbo.Categories (CategoryID, Name, ParentCategoryID) VALUES
 (1,  N'Электроника',         NULL),
 (2,  N'Дом и кухня',          NULL),
 (3,  N'Смартфоны',            1),
 (4,  N'Ноутбуки',             1),
 (5,  N'Аксессуары',           1),
 (6,  N'Кухонная техника',     2),
 (7,  N'Мебель',               2),
 (8,  N'Игровые ноутбуки',     4),
 (9,  N'Ультрабуки',           4),
 (10, N'Зарядные устройства',  5),
 (11, N'Чехлы',                5),
 (12, N'Складные смартфоны',   3);
SET IDENTITY_INSERT dbo.Categories OFF;

INSERT dbo.Products (SKU, Name, CategoryID, Price, StockQty, Attributes)
SELECT N'SKU-' + RIGHT(N'0000' + CAST(n.N AS NVARCHAR(5)), 5),
       N'Товар ' + CAST(n.N AS NVARCHAR(10)),
       CHOOSE(1 + n.N % 8, 8, 9, 10, 11, 12, 3, 6, 7),
       CAST(500 + ABS(CHECKSUM(NEWID())) % 500000 / 100.0 AS DECIMAL(18,2)),
       ABS(CHECKSUM(NEWID())) % 500,
       (SELECT brand           = CHOOSE(1 + n.N % 5, N'TechNoir', N'Voltex', N'Orion', N'Kappa', N'Delta'),
               color           = CHOOSE(1 + n.N % 4, N'black', N'white', N'gray', N'blue'),
               warranty_months = 6 * (1 + n.N % 4)
        FOR JSON PATH)
FROM dbo.Numbers n
WHERE n.N BETWEEN 1 AND 500;

UPDATE dbo.Products SET IsActive = 0     WHERE ProductID % 20 = 0;
UPDATE dbo.Products SET Attributes = NULL WHERE ProductID % 10 = 0;

INSERT dbo.Customers (FullName, Email, City, Segment, RegisteredAt)
SELECT N'Клиент ' + CAST(n.N AS NVARCHAR(10)),
       CASE
           WHEN n.N % 7  = 0 THEN NULL
           WHEN n.N % 11 = 0 THEN 'shared' + CAST((n.N / 11) % 5 + 1 AS VARCHAR(2)) + Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.'
           ELSE 'user' + CAST(n.N AS VARCHAR(10)) + Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.'
       END,
       CASE WHEN n.N % 10 BETWEEN 0 AND 5 THEN N'Москва'
            ELSE CHOOSE(1 + n.N % 8, N'Санкт-Петербург', N'Казань', N'Новосибирск',
                        N'Екатеринбург', N'Сочи', N'Самара', N'Казань', N'Воронеж')
       END,
       CHOOSE(1 + n.N % 10, N'VIP', N'Retail', N'Retail', N'Retail', N'Retail',
              N'Wholesale', N'Retail', N'Retail', N'Retail', N'VIP'),
       DATEADD(DAY, -ABS(CHECKSUM(NEWID()) % 1500), SYSDATETIME())
FROM dbo.Numbers n
WHERE n.N BETWEEN 1 AND 3000;

INSERT dbo.Orders (CustomerID, OrderDate)
SELECT 1 + ABS(CHECKSUM(NEWID()) % 3000),
       DATEADD(SECOND, -ABS(CHECKSUM(NEWID()) % 63072000), SYSDATETIME())
FROM dbo.Numbers n
WHERE n.N BETWEEN 1 AND 25000;

DECLARE @ProdCount INT = (SELECT COUNT(*) FROM dbo.Products);

WITH k AS (SELECT v FROM (VALUES (1),(2),(3),(4)) x(v))
INSERT dbo.OrderItems (OrderID, ProductID, Quantity, UnitPrice)
SELECT o.OrderID,
       p.ProductID,
       1 + (o.OrderID + k.v) % 5,
       p.Price
FROM dbo.Orders o
JOIN k           ON k.v BETWEEN 1 AND 1 + (o.OrderID % 4)
JOIN dbo.Products p
     ON p.ProductID = ((o.OrderID * 37 + k.v * 91) % @ProdCount) + 1;

UPDATE dbo.Orders
SET Status = CASE OrderID % 20
                WHEN 0 THEN N'Cancelled'
                WHEN 1 THEN N'New'
                WHEN 2 THEN N'New'
                ELSE CHOOSE(OrderID % 3 + 1, N'Paid', N'Shipped', N'Delivered')
             END;

INSERT dbo.Payments (OrderID, PaidAt, Amount, Method)
SELECT o.OrderID,
       DATEADD(MINUTE, 30 + o.OrderID % 600, o.OrderDate),
       s.Amt,
       CHOOSE(o.OrderID % 3 + 1, N'Card', N'Cash', N'Transfer')
FROM dbo.Orders o
JOIN (SELECT OrderID, SUM(Quantity * UnitPrice) AS Amt
      FROM dbo.OrderItems
      GROUP BY OrderID) s ON s.OrderID = o.OrderID
WHERE o.Status IN (N'Paid', N'Shipped', N'Delivered');

UPDATE o
SET TotalAmount = s.Amt
FROM dbo.Orders o
JOIN (SELECT OrderID, SUM(Quantity * UnitPrice) AS Amt
      FROM dbo.OrderItems
      GROUP BY OrderID) s ON s.OrderID = o.OrderID;

INSERT dbo.PriceHistory (ProductID, OldPrice, NewPrice, ChangedAt)
SELECT ProductID, Price * 1.15, Price * 1.05, DATEADD(DAY, -500 + ProductID % 100, SYSDATETIME())
FROM dbo.Products WHERE ProductID % 5 = 0
UNION ALL
SELECT ProductID, Price * 1.05, Price * 1.10, DATEADD(DAY, -300 + ProductID % 60, SYSDATETIME())
FROM dbo.Products WHERE ProductID % 7 = 0
UNION ALL
SELECT ProductID, Price * 1.10, Price,        DATEADD(DAY, -100 + ProductID % 40, SYSDATETIME())
FROM dbo.Products WHERE ProductID % 3 = 0;

CHECKPOINT;

SELECT 'Categories'  AS [table], COUNT(*) AS rows_cnt FROM dbo.Categories
UNION ALL SELECT 'Products',     COUNT(*) FROM dbo.Products
UNION ALL SELECT 'Customers',    COUNT(*) FROM dbo.Customers
UNION ALL SELECT 'Orders',       COUNT(*) FROM dbo.Orders
UNION ALL SELECT 'OrderItems',   COUNT(*) FROM dbo.OrderItems
UNION ALL SELECT 'Payments',     COUNT(*) FROM dbo.Payments
UNION ALL SELECT 'PriceHistory', COUNT(*) FROM dbo.PriceHistory
ORDER BY 1;