Учебная база TechStore и первый SELECT

Курс MS SQL с нуля · Урок 2 из 12 · Часть 1. Среда и первые запросы

Урок 2 ставит три цели: поднять учебную базу, понять её схему и уверенно писать простейшие запросы — выбор столбцов, сокращение результата, сортировка. Это фундамент всех последующих уроков.

Оба скрипта идемпотентны в паре: когда данные испорчены экспериментами, повторный прогон приводит TechStore к исходному состоянию курса.

Содержание

1. Что вы освоите
- Перейти к разделу
2. Скрипт 00: создание базы и схемы
- Перейти к разделу
3. Скрипт 01: заполнение данными
- Перейти к разделу
4. Карта базы TechStore
- Перейти к разделу
5. Анатомия SELECT
- Перейти к разделу
- TOP и ORDER BY
6. Задачи и эталонные решения
- Перейти к разделу
7. Типичные ошибки урока
- Перейти к разделу
8. Чек-лист практики и источники
- Перейти к разделу

1. Что вы освоите

К концу урока вы:

поднимаете учебную базу TechStore двумя скриптами и сверяете счётчики;
называете назначение каждой таблицы схемы и центральную пару ключей;
пишете простейший SELECT с перечнем столбцов, алиасами и сортировкой;
объясняете, почему TOP без ORDER BY непредсказуем.

↑ К оглавлению

2. Скрипт 00: создание базы и схемы

Скрипт открывается в DataGrip и выполняется целиком: Ctrl+A, затем Ctrl+Enter на контексте любой системной базы. Выполнение именно целиком важно: строки GO делят скрипт на независимые пакеты, и объявления CREATE не должны попасть в один пакет с разными командами. Скрипт удаляет прежнюю TechStore, если она была, и создаёт схему заново: этим обеспечивается возврат курса в исходную точку.

Чистый код

/* ============================================================================
   TechStore — учебная база курса «MS SQL с нуля» (DataGrip Edition)
   Шаг 00 — создание базы и схемы.
   ВНИМАНИЕ: скрипт ПЕРЕСОЗДАЁТ базу TechStore, если она существует.
   DataGrip: выполнить целиком (Ctrl+A, затем Ctrl+Enter → Execute).
   ========================================================================== */
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

/* Если версия SQL Server 2019+ (compat 150): включит интеллектуальные оптимизации
   (table variable deferred compilation, scalar UDF inlining, batch mode on rowstore).
   ALTER DATABASE TechStore SET COMPATIBILITY_LEVEL = 150;
*/

USE TechStore;
GO

/* ---------- Категории: иерархия (дерево), для рекурсии в модуле 2 ---------- */
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,          -- JSON-атрибуты (модуль 3)
    IsActive    BIT               NOT NULL CONSTRAINT DF_Products_Active DEFAULT (1)
);

/* ---------- Клиенты ---------------------------------------------------------
   Email намеренно VARCHAR — понадобится для задачи про implicit conversion
   в модуле 4. В данных есть дубликаты email — для дедупликации в модуле 2. */
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      -- денормализация (триггер в модуле 6)
);

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      -- Card / Cash / Transfer
);

/* ---------- История цен (сид + триггер из модуля 6) ------------------------- */
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())
);

/* ---------- Универсальный журнал аудита (модуль 6) -------------------------- */
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())
);

/* ---------- Таблица чисел (tally table) — используется во всём курсе -------- */
CREATE TABLE dbo.Numbers
(
    N INT NOT NULL PRIMARY KEY
);
GO

Построчный разбор

Ниже приведён тот же фрагмент с пояснением после каждой смысловой строки. Строки, которые начинаются с двух дефисов и стрелки вправо, содержат комментарий преподавателя. Эти строки не являются обязательной частью программы.

/* ============================================================================
   TechStore — учебная база курса «MS SQL с нуля» (DataGrip Edition)
   Шаг 00 — создание базы и схемы.
   ВНИМАНИЕ: скрипт ПЕРЕСОЗДАЁТ базу TechStore, если она существует.
   DataGrip: выполнить целиком (Ctrl+A, затем Ctrl+Enter → Execute).
   ========================================================================== */
SET NOCOUNT ON;
-- → сообщения «rows affected» не выводятся: консоль остаётся чистой
USE master;
-- → пересоздание ведётся из master: работающую базу удалить не выйдет
GO

IF DB_ID(N'TechStore') IS NOT NULL
BEGIN
    ALTER DATABASE TechStore SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TechStore;
END
-- → если TechStore существовала: сессии разрываются, база удалена, шаг идемпотентен
GO

CREATE DATABASE TechStore;
-- → пустая база с настройками по умолчанию
GO

ALTER DATABASE TechStore SET RECOVERY SIMPLE;   -- чтобы лог не разрастался на учебных нагрузках
-- → «простая» модель восстановления: журнальные файлы не распухают
GO

/* Если версия SQL Server 2019+ (compat 150): включит интеллектуальные оптимизации
   (table variable deferred compilation, scalar UDF inlining, batch mode on rowstore).
   ALTER DATABASE TechStore SET COMPATIBILITY_LEVEL = 150;
*/

USE TechStore;
-- → точка входа: ниже всё создаётся уже в новой базе
GO

/* ---------- Категории: иерархия (дерево), для рекурсии в модуле 2 ---------- */
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)
);
-- → дерево категорий: ParentCategoryID указывает на родителя, NULL — корневая категория

/* ---------- Товары --------------------------------------------------------- */
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,          -- JSON-атрибуты (модуль 3)
    IsActive    BIT               NOT NULL CONSTRAINT DF_Products_Active DEFAULT (1)
);
-- → товары: SKU уникален, цена и остаток обязательны, Attributes — будущий JSON

/* ---------- Клиенты ---------------------------------------------------------
   Email намеренно VARCHAR — понадобится для задачи про implicit conversion
   в модуле 4. В данных есть дубликаты email — для дедупликации в модуле 2. */
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())
);
-- → клиенты: Email намеренно VARCHAR, дефолт сегмента — Retail, дата сама

/* ---------- Заказы ---------------------------------------------------------- */
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      -- денормализация (триггер в модуле 6)
);
-- → заказы: статус по умолчанию New, итог считается денормализованно

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      -- Card / Cash / Transfer
);
-- → платежи: привязаны к заказу, способ — перечислимая строка

/* ---------- История цен (сид + триггер из модуля 6) ------------------------- */
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())
);
-- → история цен: старая и новая цена плюс момент изменения

/* ---------- Универсальный журнал аудита (модуль 6) -------------------------- */
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())
);
-- → универсальный журнал: что, где и когда менялось

/* ---------- Таблица чисел (tally table) — используется во всём курсе -------- */
CREATE TABLE dbo.Numbers
(
    N INT NOT NULL PRIMARY KEY
);
-- → таблица чисел: служебный генератор последовательностей всего курса
GO

Две схемы в одной базе: все таблицы курса живут в схеме dbo; обращение всегда двухкомпонентное: dbo.Products. Сюда же скрипт кладёт служебную таблицу чисел Numbers — она пригодится в продвинутом курсе.

↑ К оглавлению

3. Скрипт 01: заполнение данными

Второй скрипт заполняет справочники и генерирует двадцать пять тысяч заказов за примерно два года назад от момента запуска; выполняется около минуты-двух — это нормально, прогресс виден индикатором внизу окна.

Чистый код

/* ============================================================================
   TechStore — учебная база курса «MS SQL с нуля» (DataGrip Edition)
   Объёмы: 12 категорий, 500 товаров, 3000 клиентов, 25000 заказов,
           ~62 500 позиций, ~55% платежей, история цен.
   Данные специально содержат «дефекты» для учебных задач:
     - дубликаты email у части клиентов         (модуль 2);
     - 60% клиентов из Москвы — перекос данных  (модуль 4, parameter sniffing);
     - некластерные индексы НЕ создаются        (модуль 5).
   Выполнять целиком, ~1–2 минуты.
   ========================================================================== */
USE TechStore;
GO
SET NOCOUNT ON;

/* ---------- 1. Таблица чисел 1..100 000 (стек Ицика Бен-Гана) -------------- */
;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)) -- 16
     ,L1 AS (SELECT 1 AS c FROM L0 a CROSS JOIN L0 b)                                                          -- 256
     ,L2 AS (SELECT 1 AS c FROM L1 a CROSS JOIN L1 b)                                                          -- 65 536
     ,L3 AS (SELECT 1 AS c FROM L2 a CROSS JOIN L0 b)                                                          -- 1 048 576
     ,Nums AS (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L3)
INSERT dbo.Numbers (N) SELECT n FROM Nums;

/* ---------- 2. Категории: дерево глубиной до 3 ------------------------------ */
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;

/* ---------- 3. Товары: 500 шт. по листовым категориям ----------------------- */
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;   -- 5% неактивны
UPDATE dbo.Products SET Attributes = NULL WHERE ProductID % 10 = 0; -- у 10% нет JSON (для NULL-обработки)

/* ---------- 4. Клиенты: 3000. 60% — Москва (перекос), есть дубли email ------ */
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;

/* ---------- 5. Заказы: 25 000 за последние ~2 года -------------------------- */
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;

/* ---------- 6. Позиции заказов: 1–4 на заказ (~62 500 строк) ----------------- */
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;

/* ---------- 7. Статусы заказов ---------------------------------------------- */
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;

/* ---------- 8. Платежи: только для Paid/Shipped/Delivered -------------------- */
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');

/* ---------- 9. Денормализованный TotalAmount --------------------------------- */
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;

/* ---------- 10. История цен: три «волны» изменений --------------------------- */
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;

/* ---------- 11. Контрольная сводка ------------------------------------------- */
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;

Построчный разбор

/* ============================================================================
   TechStore — учебная база курса «MS SQL с нуля» (DataGrip Edition)
   Объёмы: 12 категорий, 500 товаров, 3000 клиентов, 25000 заказов,
           ~62 500 позиций, ~55% платежей, история цен.
   Данные специально содержат «дефекты» для учебных задач:
     - дубликаты email у части клиентов         (модуль 2);
     - 60% клиентов из Москвы — перекос данных  (модуль 4, parameter sniffing);
     - некластерные индексы НЕ создаются        (модуль 5).
   Выполнять целиком, ~1–2 минуты.
   ========================================================================== */
USE TechStore;
GO
SET NOCOUNT ON;

/* ---------- 1. Таблица чисел 1..100 000 (стек Ицика Бен-Гана) -------------- */
;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)) -- 16
     ,L1 AS (SELECT 1 AS c FROM L0 a CROSS JOIN L0 b)                                                          -- 256
     ,L2 AS (SELECT 1 AS c FROM L1 a CROSS JOIN L1 b)                                                          -- 65 536
     ,L3 AS (SELECT 1 AS c FROM L2 a CROSS JOIN L0 b)                                                          -- 1 048 576
     ,Nums AS (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L3)
INSERT dbo.Numbers (N) SELECT n FROM Nums;
-- → стек Ицика Бен-Гана: перемножение табличек L0-L3 даёт миллион строк,
-- → из которых берутся первые сто тысяч порядковых номеров

/* ---------- 2. Категории: дерево глубиной до 3 ------------------------------ */
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;
-- → двенадцать категорий вставлены с явными номерами: согласованное дерево курса

/* ---------- 3. Товары: 500 шт. по листовым категориям ----------------------- */
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;
-- → пятьсот товаров: цены и остатки — «шум» из CHECKSUM(NEWID()), атрибуты собраны в JSON

UPDATE dbo.Products SET IsActive = 0   WHERE ProductID % 20 = 0;   -- 5% неактивны
UPDATE dbo.Products SET Attributes = NULL WHERE ProductID % 10 = 0; -- у 10% нет JSON (для NULL-обработки)
-- → учебные дефекты: 5 процентов товаров неактивны, у 10 процентов нет атрибутов

/* ---------- 4. Клиенты: 3000. 60% — Москва (перекос), есть дубли email ------ */
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;
-- → три тысячи клиентов: перекос 60 процентов в Москву, часть email пуста или дублируется

/* ---------- 5. Заказы: 25 000 за последние ~2 года -------------------------- */
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;
-- → двадцать пять тысяч заказов: клиент и дата — случайные за два года

/* ---------- 6. Позиции заказов: 1–4 на заказ (~62 500 строк) ----------------- */
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;
-- → от одной до четырёх позиций на заказ; товар подбирается формулой из номера заказа

/* ---------- 7. Статусы заказов ---------------------------------------------- */
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;
-- → статусы: каждый двадцатый отменён, каждый пятый новый, остальные в трёх стадиях

/* ---------- 8. Платежи: только для Paid/Shipped/Delivered -------------------- */
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');
-- → оплата существует только у оплаченных и доставленных заказов

/* ---------- 9. Денормализованный TotalAmount --------------------------------- */
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;
-- → итог заказа копируется суммой его позиций: сознательная денормализация

/* ---------- 10. История цен: три «волны» изменений --------------------------- */
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;
-- → история цен: три волны изменений, строки склеены UNION ALL

CHECKPOINT;
-- → принудительная точка восстановления: страницы данных сброшены на диск

/* ---------- 11. Контрольная сводка ------------------------------------------- */
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;
-- → финал: счётчики строк по всем таблицам для самопроверки

Контрольная сводка: последний запрос скрипта отдаёт счётчики: Categories 12, Products 500, Customers 3000, Orders 25000, OrderItems около шестидесяти трёх тысяч, Payments около четырнадцати тысяч.

↑ К оглавлению

4. Карта базы TechStore

Таблица	Что хранит	Особенность данных
Categories	Дерево категорий товаров	ParentCategoryID ссылается на родителя
Products	Товары: артикул SKU, цена, остаток StockQty, JSON-атрибуты	у десяти процентов Attributes пуст; пять процентов неактивны
Customers	Клиенты: город, сегмент, дата регистрации	Email местами NULL и местами дублируется; шестьдесят процентов из Москвы
Orders	Заказы: клиент, дата, статус, итог	статус Cancelled примерно у каждого двадцатого
OrderItems	Строки заказов: товар, количество, цена строки	от одной до четырёх строк на заказ
Payments и PriceHistory	Оплаты и архив изменений цен	используются старшими уроками курса

Столбец OrderID в Orders — первичный ключ; в OrderItems.OrderID — внешний ключ-ссылка на заказ. На этой паре урок 4 строит соединения.

↑ К оглавлению

5. Анатомия SELECT

Анатомия простого запроса:

Чистый код

SELECT ProductID, SKU, Name, Price, StockQty
FROM dbo.Products
WHERE IsActive = 1
ORDER BY Price DESC;

Построчный разбор

SELECT ProductID, SKU, Name, Price, StockQty
-- → какие столбцы: перечень явный, звёздочка не применяется
FROM dbo.Products
-- → откуда: двухкомпонентное имя, схема dbo не опускается
WHERE IsActive = 1
-- → какие строки: только активные; фильтры внимательно разобраны в уроке 3
ORDER BY Price DESC;
-- → в каком порядке выдать: дорогие сначала

звёздочка SELECT * годится для «заглянуть в таблицу» и запрещена в настоящих запросах: тянет лишние столбцы и ломает код клиента;
точка с запятой в конце оператора — привычка с первого дня;
комментарии: два дефиса для строки, дробь-звёздочка для блока.

TOP и ORDER BY

SELECT TOP (100) ограничивает число строк; скобки у TOP — обязательная современная форма. Без ORDER BY выбор «первых ста» непредсказуем: сервер волен отдать любые. «Последние заказы» — это сортировка по дате по убыванию плюс TOP (10):

Чистый код

SELECT TOP (10) OrderID, OrderDate, Status, TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC;

Построчный разбор
Ниже приведён тот же фрагмент с пояснением после каждой смысловой строки. Строки, которые начинаются с двух дефисов и стрелки вправо, содержат комментарий преподавателя. Эти строки не являются обязательной частью программы.
```
SELECT TOP (10) OrderID, OrderDate, Status, TotalAmount
-- → не больше десяти строк; скобки у TOP — современная обязательная форма
FROM dbo.Orders
-- → из таблицы заказов
ORDER BY OrderDate DESC, OrderID DESC;
-- → новые сначала; второй ключ стабилизирует порядок при равных датах
```
SQL

Порядок не договаривается: результат запроса без ORDER BY не имеет порядка вообще: отсутствие фразы означает «порядок не определён», а не «порядок вставки».

↑ К оглавлению

6. Задачи и эталонные решения

Шесть задач на контексте TechStore.

первые десять товаров в порядке первичного ключа;
витрина: название, цена, остаток StockQty — только активных, дорогие сначала;
последние пятнадцать заказов: номер, дата, статус, сумма;
все города клиентов без повторов, по алфавиту — через DISTINCT;
те же города с переименованием колонки через AS;
счётчики: товары, клиенты, заказы — три независимых запроса, сверка с контрольными числами 500, 3000 и 25000.

Эталоны урока

открывать после самостоятельной попытки

Чистый код

SELECT TOP (10) ProductID, SKU, Name, CategoryID, Price, StockQty, IsActive
FROM dbo.Products
ORDER BY ProductID;
GO

SELECT Name, Price, StockQty
FROM dbo.Products
WHERE IsActive = 1
ORDER BY Price DESC;
GO

SELECT TOP (15) OrderID, OrderDate, Status, TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC;
GO

SELECT DISTINCT City
FROM dbo.Customers
ORDER BY City;
GO

SELECT DISTINCT City AS city_name
FROM dbo.Customers
ORDER BY city_name;
GO

SELECT COUNT(*) AS products_cnt  FROM dbo.Products;
SELECT COUNT(*) AS customers_cnt FROM dbo.Customers;
SELECT COUNT(*) AS orders_cnt    FROM dbo.Orders;
GO

Построчный разбор

SELECT TOP (10) ProductID, SKU, Name, CategoryID, Price, StockQty, IsActive
-- → задача 2.1: первые десять в порядке первичного ключа
FROM dbo.Products
ORDER BY ProductID;
-- → сортировка по ключу делает «первые» определёнными
GO

SELECT Name, Price, StockQty
-- → задача 2.2: витрина из трёх колонок
FROM dbo.Products
WHERE IsActive = 1
-- → только продающиеся
ORDER BY Price DESC;
-- → дорогие вверху
GO

SELECT TOP (15) OrderID, OrderDate, Status, TotalAmount
-- → задача 2.3: пятнадцать самых свежих
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC;
GO

SELECT DISTINCT City
-- → задача 2.4: города без повторов
FROM dbo.Customers
ORDER BY City;
-- → по алфавиту
GO

SELECT DISTINCT City AS city_name
-- → задача 2.5: то же с переименованием колонки
FROM dbo.Customers
ORDER BY city_name;
-- → алиас доступен в ORDER BY, но недоступен в WHERE
GO

SELECT COUNT(*) AS products_cnt  FROM dbo.Products;
SELECT COUNT(*) AS customers_cnt FROM dbo.Customers;
SELECT COUNT(*) AS orders_cnt    FROM dbo.Orders;
-- → задача 2.6: три счётчика, сверка с числами 500, 3000, 25000
GO

↑ К оглавлению

7. Типичные ошибки урока

Invalid object name Products (Msg 208) — пропущена схема или консоль не на TechStore; лечение: dbo.Products и Ctrl+Shift+F10.
TOP отдаёт «не те» строки — сортировка не указана; без ORDER BY порядок не определён, даже если вчера работало.
DESC применился не к тому столбцу — направление пишется у каждого столбца сортировки отдельно.
DISTINCT не убирает дубли — в выборке есть ещё один различающий строку столбец; DISTINCT работает на строку целиком.
Алиас с пробелом без скобок — пробелы и кириллица в именах колонок требуют квадратных скобок: AS [Имя товара].
Выполняется «целиком», а не выделенное — курсор вне нужного оператора; DataGrip исполняет выделенное, без выделения — под курсором.

↑ К оглавлению

8. Чек-лист практики и источники

Чек-лист урока 2

отметки делаются по факту

TechStore появилась в Database Explorer;
счётчики сведены: 500, 3000, 25000;
запрос витрины отдаёт только активные товары;
DISTINCT-города без повторов и по алфавиту;
TOP всегда идёт вместе с ORDER BY;
умею объяснить, чем * отличается от перечня столбцов.

Источники для углубления:

Итог: база поднята, схема понятна, простые запросы пишутся. Урок 3 учит отсекать строки предикатами WHERE и попутно знакомит с главной ловушкой — NULL.

Проверено: 23.10.2026

↑ К оглавлению

Конспект:

Курс MS SQL с нуля

Четверг, 22 октября 2026

Добавить комментарий