CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Position NVARCHAR(100),
    ManagerId INT NULL,  -- FOREIGN KEY → сама таблиця!
    FOREIGN KEY (ManagerId) REFERENCES Employees(Id)
);

INSERT INTO Employees VALUES
(1, 'Олена', 'Іваненко', 'CEO', NULL),           -- CEO без менеджера
(2, 'Петро', 'Коваль', 'CTO', 1),                -- CTO → CEO
(3, 'Марія', 'Сидорова', 'Team Lead Dev', 2),   -- Team Lead → CTO
(4, 'Іван', 'Петренко', 'Senior Developer', 3), -- Developer → Team Lead
(5, 'Олег', 'Смирнов', 'Junior Developer', 3);

SELECT
    E.FirstName + ' ' + E.LastName AS Employee,
    E.Position,
    M.FirstName + ' ' + M.LastName AS Manager
FROM Employees AS E
LEFT JOIN Employees AS M ON E.ManagerId = M.Id;

SELECT
    S1.FirstName + ' ' + S1.LastName AS Student1,
    S2.FirstName + ' ' + S2.LastName AS Student2,
    S1.Grants
FROM Students AS S1
INNER JOIN Students AS S2
    ON S1.Grants = S2.Grants AND S1.Id < S2.Id;

CREATE TABLE Products (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Price DECIMAL(10,2),
    Category NVARCHAR(50)
);

SELECT
    P1.Name,
    P1.Price,
    P1.Category,
    AVG(P2.Price) AS CategoryAvgPrice
FROM Products AS P1
INNER JOIN Products AS P2 ON P1.Category = P2.Category
GROUP BY P1.Name, P1.Price, P1.Category
HAVING P1.Price > AVG(P2.Price);

WITH EmployeeHierarchy AS (
    -- Anchor: Петро Коваль (точка старту)
    SELECT Id, FirstName, LastName, Position, ManagerId, 1 AS Level
    FROM Employees
    WHERE Id = 2  -- CTO

    UNION ALL

    -- Recursive: Підлеглі підлеглих
    SELECT E.Id, E.FirstName, E.LastName, E.Position, E.ManagerId, EH.Level + 1
    FROM Employees AS E
    INNER JOIN EmployeeHierarchy AS EH ON E.ManagerId = EH.Id
)
SELECT
    REPLICATE('  ', Level - 1) + FirstName + ' ' + LastName AS Employee,
    Position,
    Level
FROM EmployeeHierarchy
ORDER BY Level;

SELECT
    S.FirstName,
    S.AvgGrade,
    GroupAvg.Avg AS GroupAverage
FROM (
    SELECT
        StudentId,
        AVG(Grade) AS AvgGrade
    FROM Achievements
    GROUP BY StudentId
) AS S
INNER JOIN Students AS St ON S.StudentId = St.Id
INNER JOIN (
    SELECT
        G.Id AS GroupId,
        AVG(A.Grade) AS Avg
    FROM Groups AS G
    INNER JOIN Students AS S ON G.Id = S.GroupId
    INNER JOIN Achievements AS A ON S.Id = A.StudentId
    GROUP BY G.Id
) AS GroupAvg ON St.GroupId = GroupAvg.GroupId
WHERE S.AvgGrade > GroupAvg.Avg;

WITH StudentAvg AS (
    SELECT StudentId, AVG(Grade) AS AvgGrade
    FROM Achievements
    GROUP BY StudentId
),
GroupAvg AS (
    SELECT
        G.Id AS GroupId,
        AVG(A.Grade) AS Avg
    FROM Groups AS G
    INNER JOIN Students AS S ON G.Id = S.GroupId
    INNER JOIN Achievements AS A ON S.Id = A.StudentId
    GROUP BY G.Id
)
SELECT
    St.FirstName,
    SA.AvgGrade,
    GA.Avg AS GroupAverage
FROM Students AS St
INNER JOIN StudentAvg AS SA ON St.Id = SA.StudentId
INNER JOIN GroupAvg AS GA ON St.GroupId = GA.GroupId
WHERE SA.AvgGrade > GA.Avg;

Hash Match (75%) ← Алгоритм JOIN (% від загальної вартості)
  ├─ Table Scan (50%) ← Table Scan = ПОВІЛЬНО!
  └─ Index Seek (10%) ← Index Seek = ШВИДКО!

-- Запит
SELECT S.FirstName, S.LastName, S.Grants, G.GroupName
FROM Students AS S
INNER JOIN Groups AS G ON S.GroupId = G.Id
WHERE S.Grants > 1200;

-- Створення Covering Index
CREATE NONCLUSTERED INDEX IX_Students_Covering
ON Students(GroupId)  -- JOIN column
INCLUDE (FirstName, LastName, Grants);  -- SELECT columns

-- Запит з кількома умовами
FROM Students AS S
INNER JOIN Groups AS G
    ON S.GroupId = G.Id AND G.Faculty = 'IT';

-- Створення Composite Index
CREATE INDEX IX_Groups_Faculty_Id ON Groups(Faculty, Id);

WITH StudentGrades AS (
    SELECT
        StudentId,
        SubjectId,
        AVG(Grade) AS AvgGrade,
        COUNT(*) AS TestCount
    FROM Achievements
    GROUP BY StudentId, SubjectId
)
SELECT
    S.FirstName + ' ' + S.LastName AS Student,
    G.GroupName,
    T.FirstName + ' ' + T.LastName AS Teacher,
    Sub.SubjectName,
    SG.AvgGrade,
    SG.TestCount,
    CASE
        WHEN SG.AvgGrade >= 90 THEN 'Відмінно'
        WHEN SG.AvgGrade >= 75 THEN 'Добре'
        WHEN SG.AvgGrade >= 60 THEN 'Задовільно'
        ELSE 'Незадовільно'
    END AS Performance
FROM Students AS S
INNER JOIN Groups AS G ON S.GroupId = G.Id
INNER JOIN Teachers AS T ON G.TeacherId = T.Id
INNER JOIN StudentGrades AS SG ON S.Id = SG.StudentId
INNER JOIN Subjects AS Sub ON SG.SubjectId = Sub.Id
WHERE SG.AvgGrade >= 60
ORDER BY G.GroupName, S.LastName, Sub.SubjectName;

SELECT
    G.GroupName,
    G.Faculty,
    T.FirstName + ' ' + T.LastName AS Teacher,
    COUNT(DISTINCT S.Id) AS TotalStudents,
    COUNT(DISTINCT CASE WHEN S.Grants IS NOT NULL THEN S.Id END) AS StudentsWithGrants,
    AVG(S.Grants) AS AvgGrant,
    COUNT(DISTINCT A.Id) AS TotalAchievements,
    AVG(A.Grade) AS AvgGrade,
    MAX(A.Grade) AS MaxGrade,
    MIN(A.Grade) AS MinGrade
FROM Groups AS G
LEFT JOIN Teachers AS T ON G.TeacherId = T.Id
LEFT JOIN Students AS S ON G.Id = S.GroupId
LEFT JOIN Achievements AS A ON S.Id = A.StudentId
GROUP BY G.GroupName, G.Faculty, T.FirstName, T.LastName
ORDER BY TotalStudents DESC;