---

Ниже представлен обновленный скрипт, в котором названия всех таблиц и столбцов (включая псевдонимы в запросах) приведены к формату с заглавной буквы (PascalCase с подчеркиванием, как на концептуальной схеме в методичке: Employee_ID, Last_Name, Department_ID и т.д.).
Часть 1. Создание БД, таблиц и связей (DDL)
-- 1. Создание базы данных
CREATE DATABASE [King_Corporation_Иллюминарский];
GO

USE [King_Corporation_Иллюминарский];
GO

-- 2. Создание таблиц и связей между ними

-- Таблица Location - места размещения отделов
CREATE TABLE Location (
    Location_ID SMALLINT NOT NULL PRIMARY KEY,
    Regional_Group VARCHAR(20)
);

-- Таблица Department - отделы фирмы
CREATE TABLE Department (
    Department_ID SMALLINT NOT NULL PRIMARY KEY,
    Name VARCHAR(14),
    Location_ID SMALLINT REFERENCES Location(Location_ID)
);

-- Таблица Job - должности в фирме
CREATE TABLE Job (
    Job_ID SMALLINT NOT NULL PRIMARY KEY,
    [Function] VARCHAR(30)
);

-- Таблица Employee - сотрудники фирмы
CREATE TABLE Employee (
    Employee_ID SMALLINT NOT NULL PRIMARY KEY,
    Last_Name VARCHAR(15),
    First_Name VARCHAR(15),
    Middle_Initial VARCHAR(1),
    Manager_ID SMALLINT REFERENCES Employee(Employee_ID),
    Job_ID SMALLINT REFERENCES Job(Job_ID),
    Hire_Date DATE,
    Salary NUMERIC(7,2),
    Commission NUMERIC(7,2),
    Department_ID SMALLINT REFERENCES Department(Department_ID)
);

-- Таблица Customer - фирмы-покупатели
CREATE TABLE Customer (
    Customer_ID INTEGER NOT NULL PRIMARY KEY,
    Name VARCHAR(45),
    Address VARCHAR(40),
    City VARCHAR(30),
    State VARCHAR(2),
    Zip_Code VARCHAR(9),
    Area_Code SMALLINT,
    Phone_Number INTEGER,
    Salesperson_ID SMALLINT REFERENCES Employee(Employee_ID),
    Credit_Limit NUMERIC(9,2),
    Comments VARCHAR(500)
);

-- Таблица Product - товары
CREATE TABLE Product (
    Product_ID INTEGER NOT NULL PRIMARY KEY,
    Description VARCHAR(30)
);

-- Таблица Price - цены
CREATE TABLE Price (
    Product_ID INTEGER NOT NULL REFERENCES Product(Product_ID),
    List_Price NUMERIC(8,2),
    Min_Price NUMERIC(8,2),
    Start_Date DATE NOT NULL,
    End_Date DATE,
    PRIMARY KEY (Product_ID, Start_Date)
);

-- Таблица Sales_Order - договоры о продаже
CREATE TABLE Sales_Order (
    Order_ID SMALLINT NOT NULL PRIMARY KEY,
    Order_Date DATE,
    Customer_ID INTEGER REFERENCES Customer(Customer_ID),
    Ship_Date DATE,
    Total NUMERIC(8,2)
);

-- Таблица Item - акты продаж (позиции заказа)
CREATE TABLE Item (
    Order_ID SMALLINT NOT NULL REFERENCES Sales_Order(Order_ID),
    Item_ID SMALLINT NOT NULL,
    Product_ID INTEGER REFERENCES Product(Product_ID),
    Actual_Price NUMERIC(8,2),
    Quantity INTEGER,
    Total NUMERIC(8,2),
    PRIMARY KEY (Order_ID, Item_ID)
);
GO

Часть 2. Заполнение таблиц данными (DML)
-- Заполнение таблицы Location
INSERT INTO Location (Location_ID, Regional_Group) VALUES
(122, 'NEW YORK'),
(123, 'DALLAS'),
(124, 'CHICAGO'),
(167, 'BOSTON');

-- Заполнение таблицы Department
INSERT INTO Department (Department_ID, Name, Location_ID) VALUES
(10, 'ACCOUNTING', 122),
(20, 'RESEARCH', 124),
(30, 'SALES', 123),
(40, 'OPERATIONS', 167),
(12, 'RESEARCH', 122),
(13, 'SALES', 122),
(14, 'OPERATIONS', 122),
(23, 'SALES', 124),
(24, 'OPERATIONS', 124),
(34, 'OPERATIONS', 123),
(43, 'SALES', 167);

-- Заполнение таблицы Job
INSERT INTO Job (Job_ID, [Function]) VALUES
(667, 'CLERK'),
(668, 'STAFF'),
(669, 'ANALYST'),
(670, 'SALESPERSON'),
(671, 'MANAGER'),
(672, 'PRESIDENT');

-- Заполнение таблицы Employee
ALTER TABLE Employee NOCHECK CONSTRAINT ALL;

INSERT INTO Employee (Employee_ID, Last_Name, First_Name, Middle_Initial, Manager_ID, Job_ID, Hire_Date, Salary, Commission, Department_ID) VALUES
(7839, 'KING', 'FRANCIS', 'A', NULL, 672, '1981-11-17', 5000.00, NULL, 10),
(7566, 'JONES', 'TERRY', 'M', 7839, 671, '1981-04-02', 2975.00, NULL, 20),
(7698, 'BLAKE', 'MARION', 'S', 7839, 671, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'CAROL', 'F', 7839, 671, '1981-06-09', 2450.00, NULL, 10),
(7505, 'DOYLE', 'JEAN', 'K', 7839, 671, '1985-04-04', 2850.00, NULL, 13),
(7506, 'DENNIS', 'LYNN', 'S', 7839, 671, '1985-05-15', 2750.00, NULL, 23),
(7507, 'BAKER', 'LESLIE', 'D', 7839, 671, '1985-06-10', 2200.00, NULL, 14),
(7599, 'ALBERTS', 'CHRIS', 'L', 7839, 671, '1985-04-06', 3000.00, NULL, 40),
(7788, 'SCOTT', 'DONALD', 'T', 7566, 669, '1982-12-09', 3000.00, NULL, 20),
(7902, 'FORD', 'PATRICK', 'F', 7566, 669, '1981-12-03', 3000.00, NULL, 20),
(7604, 'LEWIS', 'RICHARD', 'M', 7566, 668, '1985-04-01', 1800.00, NULL, 24),
(7789, 'FISHER', 'MATTHEW', 'G', 7566, 669, '1985-02-12', 3000.00, NULL, 34),
(7916, 'ROBERTS', 'GRACE', 'R', 7566, 668, '1985-01-04', 2875.00, NULL, 40),
(7369, 'SMITH', 'JOHN', 'Q', 7902, 667, '1980-12-17', 800.00, NULL, 20),
(7499, 'ALLEN', 'KEVIN', 'J', 7698, 670, '1981-02-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'CYNTHIA', 'D', 7698, 670, '1981-02-22', 1250.00, 500.00, 30),
(7555, 'PETERS', 'DANIEL', 'T', 7505, 670, '1985-03-31', 1250.00, 300.00, 13),
(7557, 'SHAW', 'KAREN', 'P', 7505, 670, '1985-04-03', 1250.00, 1200.00, 13),
(7560, 'DUNCAN', 'SARAH', 'S', 7506, 670, '1985-05-31', 1250.00, NULL, 23),
(7564, 'LANGE', 'GREGORY', 'J', 7506, 670, '1985-06-01', 1250.00, 300.00, 23),
(7600, 'PORTER', 'RAYMOND', 'Y', 7505, 670, '1985-04-15', 1250.00, 900.00, 13),
(7654, 'MARTIN', 'KENNETH', 'J', 7698, 670, '1981-09-28', 1250.00, 1400.00, 30),
(7676, 'SOMMERS', 'DENISE', 'D', 7507, 668, '1985-04-19', 1850.00, NULL, 34),
(7820, 'WEST', 'PAUL', 'S', 7506, 670, '1985-01-04', 1500.00, 1000.00, 23),
(7844, 'TURNER', 'MARY', 'A', 7698, 670, '1981-09-08', 1500.00, 0.00, 30),
(7876, 'ADAMS', 'DIANE', 'G', 7788, 667, '1983-01-12', 1100.00, NULL, 20),
(7900, 'JAMES', 'FRED', 'S', 7698, 667, '1981-12-03', 950.00, NULL, 30),
(7934, 'MILLER', 'BARBARA', 'M', 7782, 667, '1982-01-23', 1300.00, NULL, 10),
(7950, 'JENSEN', 'ALICE', 'B', 7505, 667, '1985-01-15', 750.00, NULL, 12),
(7954, 'MURRAY', 'JAMES', 'T', 7506, 667, '1985-01-16', 750.00, NULL, 23),
(7800, 'ROSS', 'PAUL', 'E', 7599, 670, '1985-06-04', 1300.00, 800.00, 43),
(7919, 'DOUGLAS', 'MICHAEL', 'A', 7782, 667, '1985-04-04', 800.00, NULL, 10);

ALTER TABLE Employee CHECK CONSTRAINT ALL;

-- Заполнение таблицы Customer
INSERT INTO Customer (Customer_ID, Name, Address, City, State, Zip_Code, Area_Code, Phone_Number, Salesperson_ID, Credit_Limit, Comments) VALUES
(100, 'JOCKSPORTS', '345 VIEWRIDGE', 'BELMONT', 'CA', '96711', 415, 5986609, 7844, 5000.00, 'Very friendly people to work with -- sales rep likes to be called Mike.'),
(101, 'TKB SPORT SHOP', '490 BOLI RD.', 'REDWOOD CITY', 'CA', '94061', 415, 3681223, 7521, 10000.00, 'Rep called 5/8 about change in order - contact shipping.'),
(102, 'VOLLYRITE', '9722 HAMILTON', 'BURLINGAME', 'CA', '95133', 415, 6443341, 7654, 7000.00, 'Company doing heavy promotion beginning 10/89. Prepare for large orders during winter.'),
(103, 'JUST TENNIS', 'HILLVIEW MALL', 'BURLINGAME', 'CA', '97544', 415, 6779312, 7521, 3000.00, 'Contact rep about new line of tennis rackets.'),
(104, 'EVERY MOUNTAIN', '574 SURRY RD.', 'CUPERTINO', 'CA', '93301', 408, 9962323, 7499, 10000.00, 'Customer with high market share (23%) due to aggressive advertising.'),
(105, 'K + T SPORTS', '3476 EL PASEO', 'SANTA CLARA', 'CA', '91003', 408, 3769966, 7844, 5000.00, 'Tends to order large amounts of merchandise at once. Accounting is considering raising their credit limit. Usually pays on time.'),
(106, 'SHAPE UP', '908 SEQUOIA', 'PALO ALTO', 'CA', '94301', 415, 3649777, 7521, 6000.00, 'Support intensive. Orders small amounts (< 800) of merchandise at a time.'),
(107, 'WOMENS SPORTS', 'VALCO VILLAGE', 'SUNNYVALE', 'CA', '93301', 408, 9674398, 7499, 10000.00, 'First sporting goods store geared exclusively towards women. Unusual promotional style and very willing to take chances towards new products!'),
(108, 'STADIUM SPORTS', '4783 MAIN ST', 'NEW YORK', 'NY', '10113', 212, 5553927, 7557, 10000.00, 'Largest general-purpose sporting goods store in the NY area.');

-- Заполнение таблицы Product
INSERT INTO Product (Product_ID, Description) VALUES
(100860, 'ACE TENNIS RACKET I'),
(100861, 'ACE TENNIS RACKET II'),
(100870, 'ACE TENNIS BALLS-3 PACK'),
(100871, 'ACE TENNIS BALLS-6 PACK'),
(100890, 'ACE TENNIS NET'),
(101860, 'SP TENNIS RACKET'),
(101863, 'SP JUNIOR RACKET'),
(102130, 'RH: "GUIDE TO TENNIS"'),
(200376, 'SB ENERGY BAR-6 PACK'),
(200380, 'SB VITA SNACK-6 PACK'),
(103120, 'DUNK BASKETBALL INDOOR');

-- Заполнение таблицы Price
INSERT INTO Price (Product_ID, List_Price, Min_Price, Start_Date, End_Date) VALUES
(100860, 30.00, 24.00, '1989-01-01', '1989-05-31'),
(100860, 32.00, 25.60, '1989-06-01', '1989-12-31'),
(100860, 35.00, 28.00, '1990-01-01', '1990-05-31'),
(100860, 38.00, 30.40, '1990-06-01', NULL),
(100861, 40.00, 32.00, '1989-01-01', '1989-05-31'),
(100861, 42.00, 33.60, '1989-06-01', '1989-12-31'),
(100861, 45.00, 36.00, '1990-01-01', '1990-05-31'),
(100861, 50.00, 40.00, '1990-06-01', NULL),
(100870, 2.40, 1.90, '1989-01-01', '1989-12-31'),
(100870, 2.80, 2.40, '1990-01-01', NULL),
(100871, 4.80, 3.80, '1989-01-01', '1989-12-31'),
(100871, 5.60, 4.80, '1990-01-01', NULL),
(100890, 54.00, 44.00, '1989-01-01', '1990-05-31'),
(100890, 58.00, 46.40, '1990-06-01', NULL),
(101860, 24.00, 18.00, '1989-02-15', NULL),
(101863, 12.50, 9.40, '1989-02-15', NULL),
(102130, 3.40, 2.80, '1989-08-18', NULL),
(200376, 2.40, 1.75, '1990-11-15', NULL),
(200380, 4.00, 3.20, '1990-11-15', NULL),
(103120, 40.00, 32.00, '1989-01-01', '1989-12-31'),
(103120, 48.00, 38.40, '1990-01-01', NULL);

-- Заполнение таблицы Sales_Order
INSERT INTO Sales_Order (Order_ID, Order_Date, Customer_ID, Ship_Date, Total) VALUES
(601, '1989-05-01', 108, '1989-05-10', 640.00),
(602, '1989-06-05', 102, '1989-06-15', 560.00),
(603, '1989-08-15', 108, '1989-08-20', 896.00),
(604, '1990-06-15', 106, '1990-06-20', 64.00),
(605, '1990-07-14', 106, '1990-07-20', 8324.00),
(606, '1990-07-14', 100, '1990-07-30', 3.40),
(607, '1990-07-18', 104, '1990-07-18', 5.60),
(608, '1990-07-25', 104, '1990-07-25', 35.20),
(609, '1990-08-01', 100, '1990-08-15', 97.50),
(610, '1990-05-07', 101, '1990-05-10', 101.40),
(611, '1990-06-11', 102, '1990-06-15', 45.00),
(612, '1990-06-15', 104, '1990-06-20', 5860.00),
(613, '1990-12-01', 108, '1990-12-05', 6400.00),
(614, '1991-02-01', 108, '1991-02-05', 23940.00),
(615, '1991-02-01', 107, '1991-02-06', 710.00),
(616, '1991-02-03', 103, '1991-02-10', 760.00),
(617, '1991-02-05', 105, '1991-03-04', 46370.00),
(618, '1991-02-15', 102, '1991-03-06', 3510.50),
(619, '1991-02-22', 104, '1991-02-24', 1260.00),
(620, '1991-02-23', 100, '1991-03-12', 4450.00),
(621, '1991-03-01', 100, '1991-03-08', 730.00);

-- Заполнение таблицы Item
INSERT INTO Item (Order_ID, Item_ID, Product_ID, Actual_Price, Quantity, Total) VALUES
(601, 1, 103120, 32.00, 20, 640.00),
(602, 1, 100860, 28.00, 20, 560.00),
(603, 1, 100860, 22.40, 40, 896.00),
(604, 1, 100890, 58.00, 1, 58.00),
(604, 2, 100861, 42.00, 2, 84.00),
(604, 3, 100860, 30.00, 12, 360.00),
(605, 1, 100861, 45.00, 100, 4500.00),
(605, 2, 100870, 2.80, 500, 1400.00),
(605, 3, 100890, 58.00, 5, 290.00),
(605, 4, 101860, 24.00, 50, 1200.00),
(605, 5, 101863, 9.00, 100, 900.00),
(605, 6, 102130, 3.40, 10, 34.00),
(606, 1, 102130, 3.40, 1, 3.40),
(607, 1, 100871, 5.60, 1, 5.60),
(608, 1, 101860, 24.00, 1, 24.00),
(608, 2, 100871, 5.60, 2, 11.20),
(609, 1, 100861, 32.50, 3, 97.50),
(610, 1, 100860, 35.00, 1, 35.00),
(610, 2, 100870, 2.80, 3, 8.40),
(610, 3, 100890, 58.00, 1, 58.00),
(611, 1, 100861, 45.00, 1, 45.00),
(612, 1, 100860, 30.00, 100, 3000.00),
(612, 2, 100861, 40.50, 20, 810.00),
(612, 3, 101863, 10.00, 150, 1500.00),
(612, 4, 100870, 2.50, 220, 550.00),
(613, 1, 103120, 48.00, 100, 4800.00),
(613, 2, 100871, 5.60, 200, 1120.00),
(613, 3, 100890, 48.00, 10, 480.00),
(614, 1, 100860, 35.00, 444, 15540.00),
(614, 2, 100870, 2.80, 1000, 2800.00),
(614, 3, 100871, 5.60, 1000, 5600.00),
(615, 1, 100861, 45.00, 4, 180.00),
(615, 2, 100870, 2.50, 100, 250.00),
(615, 3, 100871, 5.60, 50, 280.00),
(616, 1, 100861, 45.00, 10, 450.00),
(616, 2, 100870, 2.80, 50, 140.00),
(616, 3, 100890, 58.00, 2, 116.00),
(616, 4, 102130, 3.40, 10, 34.00),
(616, 5, 200376, 2.00, 10, 20.00),
(617, 1, 100860, 35.00, 50, 1750.00),
(617, 2, 100861, 45.00, 100, 4500.00),
(617, 3, 100870, 2.80, 500, 1400.00),
(617, 4, 100871, 5.60, 500, 2800.00),
(617, 5, 100890, 58.00, 500, 29000.00),
(617, 6, 101860, 24.00, 100, 2400.00),
(617, 7, 101863, 12.50, 200, 2500.00),
(617, 8, 102130, 3.40, 100, 340.00),
(617, 9, 200376, 2.40, 200, 480.00),
(617, 10, 200380, 4.00, 300, 1200.00),
(618, 1, 100860, 35.00, 23, 805.00),
(618, 2, 100861, 45.11, 50, 2255.50),
(618, 3, 100870, 45.00, 10, 450.00),
(619, 1, 200380, 4.00, 100, 400.00),
(619, 2, 200376, 2.40, 100, 240.00),
(619, 3, 101863, 12.50, 4, 50.00),
(619, 4, 100871, 5.60, 10, 56.00),
(619, 5, 100860, 35.00, 10, 350.00),
(619, 6, 103120, 41.00, 4, 164.00),
(620, 1, 100860, 35.00, 10, 350.00),
(620, 2, 200376, 2.40, 1000, 2400.00),
(620, 3, 102130, 3.40, 500, 1700.00),
(621, 1, 100861, 45.00, 10, 450.00),
(621, 2, 100870, 2.80, 100, 280.00);
GO

Часть 3. Выполнение запросов (3 – 50)
-- 3. Вывести два инициала (с точками) и фамилии всех сотрудников, например: W. J. Smith
SELECT 
    LEFT(First_Name, 1) + '. ' + ISNULL(Middle_Initial + '. ', '') + 
    UPPER(LEFT(Last_Name, 1)) + LOWER(SUBSTRING(Last_Name, 2, LEN(Last_Name))) AS Formatted_Name
FROM Employee;

-- 4. По каждой сделке вывести точную сумму сделки, округленную в большую сторону, 
-- в меньшую сторону, и по общепринятым правилам округления.
SELECT 
    Order_ID,
    Total AS Exact_Total,
    CEILING(Total) AS Rounded_Up,
    FLOOR(Total) AS Rounded_Down,
    ROUND(Total, 0) AS Rounded_Standard
FROM Sales_Order;

-- 5. Выбрать фамилии всех сотрудников, у которых комиссионные больше зарплаты.
SELECT Last_Name 
FROM Employee 
WHERE Commission > Salary;

-- 6. Выбрать фамилии всех сотрудников, фамилии которых начинаются на букву 'S'.
SELECT Last_Name 
FROM Employee 
WHERE Last_Name LIKE 'S%';

-- 7. Выбрать имена всех сотрудников, которые не являются менеджерами (Job_ID=671) 
-- и не работают в отделе SALES в NEW YORK (Department_ID=13).
SELECT First_Name, Last_Name, Job_ID, Department_ID
FROM Employee 
WHERE Job_ID <> 671 AND Department_ID <> 13;

-- 8. Выбрать фамилии всех сотрудников, у которых код должности 670 или 667 (CLERK или SALESPERSON).
SELECT Last_Name 
FROM Employee 
WHERE Job_ID IN (670, 667);

-- 9. Выбрать всех сотрудников, имена которых состоят из 6 букв и начинаются на 'MAR'.
SELECT * 
FROM Employee 
WHERE First_Name LIKE 'MAR___';

-- 10. Выбрать фамилии всех сотрудников, которые поступили на работу после 15 апреля 1985 года.
SELECT Last_Name, Hire_Date
FROM Employee 
WHERE Hire_Date > '1985-04-15';

-- 11. Выбрать фамилии всех сотрудников, которые поступили на работу в 1985 году.
SELECT Last_Name, Hire_Date
FROM Employee 
WHERE YEAR(Hire_Date) = 1985;

-- 12. Для каждого сотрудника выбрать количество полных лет работы в фирме.
SELECT 
    Last_Name, 
    Hire_Date,
    DATEDIFF(YEAR, Hire_Date, GETDATE()) - 
        CASE 
            WHEN DATEADD(YEAR, DATEDIFF(YEAR, Hire_Date, GETDATE()), Hire_Date) > GETDATE() THEN 1 
            ELSE 0 
        END AS Full_Years_Worked
FROM Employee;

-- 13. Выбрать количество сотрудников, получающих комиссионные.
SELECT COUNT(Commission) AS Employees_With_Commission 
FROM Employee 
WHERE Commission > 0;

-- 14. Выбрать количество и общую сумму сделок, совершенных с покупателем, код которого 104.
SELECT 
    COUNT(Order_ID) AS Orders_Count, 
    SUM(Total) AS Total_Amount
FROM Sales_Order 
WHERE Customer_ID = 104;

-- 15. Выбрать среднюю зарплату по каждой должности.
SELECT 
    Job_ID, 
    AVG(Salary) AS Avg_Salary
FROM Employee 
GROUP BY Job_ID;

-- 16. Выбрать среднюю зарплату продавцов (код должности - 670).
SELECT AVG(Salary) AS Avg_Salesperson_Salary
FROM Employee 
WHERE Job_ID = 670;

-- 17. Выбрать средние зарплаты продавцов (код должности 670) и клерков (код должности - 667).
SELECT 
    Job_ID, 
    AVG(Salary) AS Avg_Salary
FROM Employee 
WHERE Job_ID IN (670, 667)
GROUP BY Job_ID;

-- 18. Выбрать коды продуктов, по которым было совершено меньше 10 продаж.
SELECT 
    Product_ID, 
    COUNT(Item_ID) AS Sales_Count
FROM Item 
GROUP BY Product_ID 
HAVING COUNT(Item_ID) < 10;

-- 19. Выбрать максимальную зарплату продавцов (код должности 670) по каждому отделу.
SELECT 
    Department_ID, 
    MAX(Salary) AS Max_Salesperson_Salary
FROM Employee 
WHERE Job_ID = 670 
GROUP BY Department_ID;

-- 20. Выбрать список сотрудников фирмы в алфавитном порядке.
SELECT Last_Name, First_Name 
FROM Employee 
ORDER BY Last_Name ASC, First_Name ASC;

-- 21. Выбрать всю информацию о каждом отделе и месте его расположения.
SELECT 
    d.Department_ID, 
    d.Name AS Department_Name, 
    l.Location_ID, 
    l.Regional_Group
FROM Department d
JOIN Location l ON d.Location_ID = l.Location_ID;

-- 22. Выбрать список фамилий сотрудников по городам, где они работают, с указанием для каждого его должности.
SELECT 
    l.Regional_Group AS City, 
    e.Last_Name, 
    j.[Function] AS Job_Title
FROM Employee e
JOIN Department d ON e.Department_ID = d.Department_ID
JOIN Location l ON d.Location_ID = l.Location_ID
JOIN Job j ON e.Job_ID = j.Job_ID
ORDER BY l.Regional_Group, e.Last_Name;

-- 23. Выбрать всех покупателей, с которыми работает продавец TURNER.
SELECT c.*
FROM Customer c
JOIN Employee e ON c.Salesperson_ID = e.Employee_ID
WHERE e.Last_Name = 'TURNER';

-- 24. Выбрать сотрудников, которые не закреплены за конкретным заказчиком.
SELECT e.*
FROM Employee e
LEFT JOIN Customer c ON e.Employee_ID = c.Salesperson_ID
WHERE c.Customer_ID IS NULL;

-- 25. Выбрать имена продавцов, которые работают более чем с одним покупателем, и названия покупателей, которых они обслуживают.
SELECT 
    e.First_Name, 
    e.Last_Name, 
    c.Name AS Customer_Name
FROM Employee e
JOIN Customer c ON e.Employee_ID = c.Salesperson_ID
WHERE e.Employee_ID IN (
    SELECT Salesperson_ID 
    FROM Customer 
    GROUP BY Salesperson_ID 
    HAVING COUNT(Customer_ID) > 1
)
ORDER BY e.Last_Name, c.Name;

-- 26. Выбрать сумму всех продаж, которые обеспечил продавец TURNER.
SELECT SUM(so.Total) AS Turner_Total_Sales
FROM Sales_Order so
JOIN Customer c ON so.Customer_ID = c.Customer_ID
JOIN Employee e ON c.Salesperson_ID = e.Employee_ID
WHERE e.Last_Name = 'TURNER';

-- 27. Выбрать сумму всех продаж товаров, связанных с теннисом, за лето 1990г.
SELECT SUM(i.Total) AS Tennis_Summer_1990_Sales
FROM Item i
JOIN Sales_Order so ON i.Order_ID = so.Order_ID
JOIN Product p ON i.Product_ID = p.Product_ID
WHERE p.Description LIKE '%TENNIS%'
  AND so.Order_Date BETWEEN '1990-06-01' AND '1990-08-31';

-- 28. Выбрать имена и зарплаты самого старого и самого молодого сотрудника фирмы ("Возраст" определять по дате поступления).
SELECT First_Name, Last_Name, Salary, Hire_Date
FROM Employee
WHERE Hire_Date = (SELECT MIN(Hire_Date) FROM Employee)
   OR Hire_Date = (SELECT MAX(Hire_Date) FROM Employee);

-- 29. Для каждого продавца вывести разность между его зарплатой и средней зарплатой продавцов в фирме.
SELECT 
    Last_Name, 
    Salary, 
    Salary - AVG(Salary) OVER() AS Diff_From_Avg
FROM Employee
WHERE Job_ID = 670;

-- 30. (*) Вывести имена, средние инициалы и фамилии всех однофамильцев.
SELECT First_Name, Middle_Initial, Last_Name
FROM Employee
WHERE Last_Name IN (
    SELECT Last_Name 
    FROM Employee 
    GROUP BY Last_Name 
    HAVING COUNT(*) > 1
)
ORDER BY Last_Name, First_Name;

-- 31. Для каждого отдела выбрать процент, который составляют сотрудники отдела от общего числа сотрудников фирмы, 
-- и процент, который составляет зарплата отдела от общей зарплаты фирмы.
SELECT 
    Department_ID,
    CAST(COUNT(Employee_ID) * 100.0 / (SELECT COUNT(*) FROM Employee) AS NUMERIC(5,2)) AS Emp_Percent,
    CAST(SUM(Salary) * 100.0 / (SELECT SUM(Salary) FROM Employee) AS NUMERIC(5,2)) AS Salary_Percent
FROM Employee
GROUP BY Department_ID;

-- 32. Выбрать фамилии начальников тех отделов, которые имеют разветвленную структуру 
-- (т.е. тех начальников, у прямых подчиненных которых есть свои подчиненные).
SELECT DISTINCT m.Last_Name AS Manager_Name, m.Department_ID
FROM Employee m
JOIN Employee sub1 ON sub1.Manager_ID = m.Employee_ID
JOIN Employee sub2 ON sub2.Manager_ID = sub1.Employee_ID;

-- 33. Выбрать названия отдела, заключившего сделки на самую большую сумму, и города, в котором он находится.
SELECT TOP 1 WITH TIES
    d.Name AS Department_Name, 
    l.Regional_Group AS City, 
    SUM(so.Total) AS Total_Sales
FROM Department d
JOIN Location l ON d.Location_ID = l.Location_ID
JOIN Employee e ON d.Department_ID = e.Department_ID
JOIN Customer c ON e.Employee_ID = c.Salesperson_ID
JOIN Sales_Order so ON c.Customer_ID = so.Customer_ID
GROUP BY d.Department_ID, d.Name, l.Regional_Group
ORDER BY SUM(so.Total) DESC;

-- 34. Выбрать название товара, дату продажи, цену продажи для всех случаев, когда товары продавались ниже, чем за 75% их объявленной цены.
SELECT 
    p.Description, 
    so.Order_Date, 
    i.Actual_Price, 
    pr.List_Price
FROM Item i
JOIN Sales_Order so ON i.Order_ID = so.Order_ID
JOIN Product p ON i.Product_ID = p.Product_ID
JOIN Price pr ON p.Product_ID = pr.Product_ID
    AND so.Order_Date >= pr.Start_Date 
    AND (so.Order_Date <= pr.End_Date OR pr.End_Date IS NULL)
WHERE i.Actual_Price < 0.75 * pr.List_Price;

-- 35. Выбрать общую сумму скидок, предоставленных покупателю STADIUM SPORTS в 1989г.
SELECT 
    SUM((pr.List_Price - i.Actual_Price) * i.Quantity) AS Total_Discount
FROM Item i
JOIN Sales_Order so ON i.Order_ID = so.Order_ID
JOIN Customer c ON so.Customer_ID = c.Customer_ID
JOIN Price pr ON i.Product_ID = pr.Product_ID
    AND so.Order_Date >= pr.Start_Date 
    AND (so.Order_Date <= pr.End_Date OR pr.End_Date IS NULL)
WHERE c.Name = 'STADIUM SPORTS' 
  AND YEAR(so.Order_Date) = 1989;

-- 36. Выбрать список сотрудников фирмы с указанием фамилии непосредственного начальника каждого.
SELECT 
    e.Last_Name AS Employee_Name, 
    m.Last_Name AS Manager_Name
FROM Employee e
LEFT JOIN Employee m ON e.Manager_ID = m.Employee_ID;

-- 37. Для каждого сотрудника вывести разность между его зарплатой и средней зарплатой сотрудников, выполняющих те же функции.
SELECT 
    Last_Name, 
    Job_ID, 
    Salary, 
    Salary - AVG(Salary) OVER(PARTITION BY Job_ID) AS Diff_From_Job_Avg
FROM Employee;

-- 38. Вывести таблицу распределения объема продаж товара DUNK BASKETBALL INDOOR по годам.
SELECT 
    YEAR(so.Order_Date) AS Sales_Year, 
    SUM(i.Quantity) AS Total_Quantity, 
    SUM(i.Total) AS Total_Amount
FROM Item i
JOIN Sales_Order so ON i.Order_ID = so.Order_ID
JOIN Product p ON i.Product_ID = p.Product_ID
WHERE p.Description = 'DUNK BASKETBALL INDOOR'
GROUP BY YEAR(so.Order_Date)
ORDER BY Sales_Year;

-- 39. Выбрать фамилии тех сотрудников, у которых суммарный доход (зарплата + комиссионные) больше 2000.
SELECT 
    Last_Name, 
    Salary + ISNULL(Commission, 0) AS Total_Income
FROM Employee
WHERE Salary + ISNULL(Commission, 0) > 2000;

-- 40. Выбрать названия товаров, для которых нынешняя цена увеличилась по сравнению с ценой на 15 декабря 1989г. более, чем на 15%.
SELECT 
    p.Description, 
    pr_old.List_Price AS Price_1989, 
    pr_now.List_Price AS Current_Price
FROM Product p
JOIN Price pr_old ON p.Product_ID = pr_old.Product_ID
    AND '1989-12-15' BETWEEN pr_old.Start_Date AND ISNULL(pr_old.End_Date, '9999-12-31')
JOIN Price pr_now ON p.Product_ID = pr_now.Product_ID
    AND pr_now.End_Date IS NULL
WHERE pr_now.List_Price > pr_old.List_Price * 1.15;

-- 41. Выбрать имена и коды отдела для сотрудников, у которых зарплата выше средней по отделу (без учета комиссионных).
SELECT First_Name, Last_Name, Department_ID, Salary
FROM (
    SELECT 
        First_Name, 
        Last_Name, 
        Department_ID, 
        Salary,
        AVG(Salary) OVER(PARTITION BY Department_ID) AS Avg_Dept_Salary
    FROM Employee
) t
WHERE Salary > Avg_Dept_Salary;

-- 42. Выбрать имена и зарплаты 10 самых высокооплачиваемых сотрудников фирмы (без учета комиссионных).
SELECT TOP 10 
    First_Name, 
    Last_Name, 
    Salary
FROM Employee
ORDER BY Salary DESC;

-- 43. Выбрать данные для построения графика зависимости средней зарплаты рядового служащего от срока работы в фирме (с точностью до месяца).
SELECT 
    DATEDIFF(MONTH, Hire_Date, GETDATE()) AS Tenure_Months,
    AVG(Salary) AS Avg_Salary
FROM Employee
WHERE Job_ID NOT IN (671, 672)
GROUP BY DATEDIFF(MONTH, Hire_Date, GETDATE())
ORDER BY Tenure_Months;

-- 44. Выбрать продукты в порядке увеличения их прибыльности. 
-- Прибыль определяется как относительная разность между реальной ценой продажи и минимальной ценой.
SELECT 
    p.Product_ID, 
    p.Description,
    AVG((i.Actual_Price - pr.Min_Price) / pr.Min_Price) AS Profitability_Ratio
FROM Product p
JOIN Item i ON p.Product_ID = i.Product_ID
JOIN Sales_Order so ON i.Order_ID = so.Order_ID
JOIN Price pr ON p.Product_ID = pr.Product_ID
    AND so.Order_Date >= pr.Start_Date 
    AND (so.Order_Date <= pr.End_Date OR pr.End_Date IS NULL)
GROUP BY p.Product_ID, p.Description
ORDER BY Profitability_Ratio ASC;

-- 45. Выбрать для каждого отдела (с указанием названия и места расположения отдела) разность между 
-- суммой обеспеченных отделом продаж и суммой зарплаты и комиссионных сотрудников отдела за 1990 год.
WITH DeptSales1990 AS (
    SELECT 
        e.Department_ID, 
        SUM(so.Total) AS Sales_1990
    FROM Employee e
    JOIN Customer c ON e.Employee_ID = c.Salesperson_ID
    JOIN Sales_Order so ON c.Customer_ID = so.Customer_ID
    WHERE YEAR(so.Order_Date) = 1990
    GROUP BY e.Department_ID
),
DeptPay AS (
    SELECT 
        Department_ID, 
        SUM(Salary + ISNULL(Commission, 0)) AS Total_Pay
    FROM Employee
    GROUP BY Department_ID
)
SELECT 
    d.Department_ID,
    d.Name AS Department_Name,
    l.Regional_Group AS Location,
    ISNULL(s.Sales_1990, 0) - ISNULL(p.Total_Pay, 0) AS Net_Difference
FROM Department d
JOIN Location l ON d.Location_ID = l.Location_ID
LEFT JOIN DeptSales1990 s ON d.Department_ID = s.Department_ID
LEFT JOIN DeptPay p ON d.Department_ID = p.Department_ID;

-- 46. Для каждого продукта выбрать регион, в котором он лучше всего продается: название продукта, название региона (Location.Regional_Group).
WITH ProductRegionSales AS (
    SELECT 
        p.Product_ID,
        p.Description AS Product_Name,
        l.Regional_Group,
        SUM(i.Total) AS Region_Sales,
        RANK() OVER(PARTITION BY p.Product_ID ORDER BY SUM(i.Total) DESC) AS Rnk
    FROM Product p
    JOIN Item i ON p.Product_ID = i.Product_ID
    JOIN Sales_Order so ON i.Order_ID = so.Order_ID
    JOIN Customer c ON so.Customer_ID = c.Customer_ID
    JOIN Employee e ON c.Salesperson_ID = e.Employee_ID
    JOIN Department d ON e.Department_ID = d.Department_ID
    JOIN Location l ON d.Location_ID = l.Location_ID
    GROUP BY p.Product_ID, p.Description, l.Regional_Group
)
SELECT Product_Name, Regional_Group, Region_Sales
FROM ProductRegionSales
WHERE Rnk = 1;

-- 47. Выбрать фамилии всех прямых и вышестоящих начальников ADAMS (вверх по иерархии).
WITH ManagersCTE AS (
    SELECT Manager_ID
    FROM Employee
    WHERE Last_Name = 'ADAMS'
    UNION ALL
    SELECT e.Manager_ID
    FROM Employee e
    JOIN ManagersCTE m ON e.Employee_ID = m.Manager_ID
)
SELECT e.Last_Name AS Manager_Last_Name
FROM Employee e
JOIN ManagersCTE m ON e.Employee_ID = m.Manager_ID;

-- 48. Вывести структуру подчиненности в фирме.
WITH OrgChart AS (
    SELECT 
        Employee_ID, 
        Last_Name, 
        Manager_ID, 
        1 AS [Level],
        CAST(Last_Name AS VARCHAR(MAX)) AS Hierarchy_Path
    FROM Employee
    WHERE Manager_ID IS NULL
    UNION ALL
    SELECT 
        e.Employee_ID, 
        e.Last_Name, 
        e.Manager_ID, 
        o.[Level] + 1,
        CAST(o.Hierarchy_Path + ' -> ' + e.Last_Name AS VARCHAR(MAX))
    FROM Employee e
    JOIN OrgChart o ON e.Manager_ID = o.Employee_ID
)
SELECT 
    REPLICATE('    ', [Level] - 1) + Last_Name AS Tree_Structure,
    [Level],
    Hierarchy_Path
FROM OrgChart
ORDER BY Hierarchy_Path;

-- 49. Выбрать среднюю зарплату всех подчиненных JONES (включая непрямых).
WITH JonesSubordinates AS (
    SELECT Employee_ID, Salary
    FROM Employee
    WHERE Manager_ID = (SELECT Employee_ID FROM Employee WHERE Last_Name = 'JONES')
    UNION ALL
    SELECT e.Employee_ID, e.Salary
    FROM Employee e
    JOIN JonesSubordinates j ON e.Manager_ID = j.Employee_ID
)
SELECT AVG(Salary) AS Avg_Subordinates_Salary
FROM JonesSubordinates;

-- 50. Выбрать список отделов и их менеджеров, включив в него также и отделы без менеджеров и менеджеров без отделов.
SELECT 
    d.Department_ID, 
    d.Name AS Department_Name, 
    e.Last_Name AS Manager_Last_Name
FROM Department d
FULL OUTER JOIN (
    SELECT * FROM Employee WHERE Job_ID = 671
) e ON d.Department_ID = e.Department_ID;