diff options
| -rw-r--r-- | .gitignore | 3 | ||||
| -rw-r--r-- | docker-compose.yml | 14 | ||||
| -rw-r--r-- | task1_dbschema.jpg | bin | 0 -> 67502 bytes | |||
| -rw-r--r-- | task2_init-databases.sql | 326 | ||||
| -rw-r--r-- | task3_requests.sql | 158 |
5 files changed, 501 insertions, 0 deletions
diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..60887e7 --- /dev/null +++ b/.gitignore @@ -0,0 +1,3 @@ +mssql-data +go.sum +.idea
\ No newline at end of file diff --git a/docker-compose.yml b/docker-compose.yml new file mode 100644 index 0000000..051b9b5 --- /dev/null +++ b/docker-compose.yml @@ -0,0 +1,14 @@ +version: '3' + +services: + mssql-db: + container_name: mssql-db + image: mcr.microsoft.com/mssql/server:2019-latest + ports: + - 1433:1433 + environment: + ACCEPT_EULA: Y + # DEFAULT_USER: sa + SA_PASSWORD: Datab@sed1337 + volumes: + - ./mssql-data:/var/opt/mssql diff --git a/task1_dbschema.jpg b/task1_dbschema.jpg Binary files differnew file mode 100644 index 0000000..9538ed3 --- /dev/null +++ b/task1_dbschema.jpg diff --git a/task2_init-databases.sql b/task2_init-databases.sql new file mode 100644 index 0000000..6955130 --- /dev/null +++ b/task2_init-databases.sql @@ -0,0 +1,326 @@ +CREATE TABLE Payments ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Payments PRIMARY KEY, + order_id INT NOT NULL, + amount INT NOT NULL, + date DATETIME NOT NULL +) +GO + +CREATE TABLE Boxes ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Boxes PRIMARY KEY, + order_id INT NOT NULL, + warehouse_id INT NOT NULL, + wrapping_id INT NOT NULL, + volume REAL NOT NULL, + weight REAL NOT NULL +) +GO + +CREATE TABLE Wrappings ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Wrappings PRIMARY KEY, + type NVARCHAR(30) NOT NULL, + price INT NOT NULL +) +GO + +CREATE TABLE Customers ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Customers PRIMARY KEY, + name NVARCHAR(50) NOT NULL, + email NVARCHAR(96) NOT NULL +) +GO + +CREATE TABLE Orders ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Orders PRIMARY KEY, + customer_id INT NOT NULL, + city_id INT NOT NULL, + date DATETIME NOT NULL +) +GO + +CREATE TABLE Warehouses ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Warehouses PRIMARY KEY, + city_id INT NOT NULL, + area INT NOT NULL +) +GO + +CREATE TABLE Shipments ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Shipments PRIMARY KEY, + order_id INT NOT NULL, + delivery_id INT NOT NULL +) +GO + +CREATE TABLE Deliveries ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Deliveries PRIMARY KEY, + driver_id INT NOT NULL, + vehicle_id INT NOT NULL, + route_id INT NOT NULL, + date DATETIME NOT NULL +) +GO + +CREATE TABLE Cities ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Cities PRIMARY KEY, + name NVARCHAR(30) NOT NULL +) +GO + +CREATE TABLE Drivers ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Drivers PRIMARY KEY, + city_id INT NOT NULL, + salary INT NOT NULL, + on_delivery BIT NOT NULL +) +GO + +CREATE TABLE Vehicles ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Vehicles PRIMARY KEY, + name NVARCHAR(30) NOT NULL, + tonnage INT NOT NULL, + gas_consumption FLOAT NOT NULL +) +GO + +CREATE TABLE Routes ( + id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Routes PRIMARY KEY, + city1_id INT NOT NULL, + city2_id INT NOT NULL, + distance FLOAT NOT NULL +) +GO + + +ALTER TABLE Payments ADD FOREIGN KEY (order_id) REFERENCES Orders (id) +GO + + +ALTER TABLE Boxes ADD FOREIGN KEY (order_id) REFERENCES Orders (id) +GO + +ALTER TABLE Boxes ADD FOREIGN KEY (warehouse_id) REFERENCES Warehouses (id) +GO + +ALTER TABLE Boxes ADD FOREIGN KEY (wrapping_id) REFERENCES Wrappings (id) +GO + + +ALTER TABLE Orders ADD FOREIGN KEY (customer_id) REFERENCES Customers (id) +GO + +ALTER TABLE Orders ADD FOREIGN KEY (city_id) REFERENCES Cities (id) +GO + + +ALTER TABLE Warehouses ADD FOREIGN KEY (city_id) REFERENCES Cities (id) +GO + + +ALTER TABLE Shipments ADD FOREIGN KEY (order_id) REFERENCES Orders (id) +GO + +ALTER TABLE Shipments ADD FOREIGN KEY (delivery_id) REFERENCES Deliveries (id) +GO + + +ALTER TABLE Deliveries ADD FOREIGN KEY (driver_id) REFERENCES Drivers (id) +GO + +ALTER TABLE Deliveries ADD FOREIGN KEY (vehicle_id) REFERENCES Vehicles (id) +GO + +ALTER TABLE Deliveries ADD FOREIGN KEY (route_id) REFERENCES Routes (id) +GO + + +ALTER TABLE Drivers ADD FOREIGN KEY (city_id) REFERENCES Cities (id) +GO + + +ALTER TABLE Routes ADD FOREIGN KEY (city1_id) REFERENCES Cities (id) +GO + +ALTER TABLE Routes ADD FOREIGN KEY (city2_id) REFERENCES Cities (id) +GO +INSERT INTO Wrappings (type, price) +VALUES (N'Жёсткая', 100), + (N'Полужёсткая', 100), + (N'Мягкая', 100), + (N'Воздушно-Пузырьковая', 100), + (N'Рулон', 100); +GO + +INSERT INTO Customers (name, email) +VALUES (N'Рога и копыта', 'business@rogaikopyta.ru'), + (N'Microsoft', 'business@microsoft.com'), + (N'Жмых Airlines', 'zshmyh@airlines.com'), + (N'Иван и Ко.', 'ivan@ivan.ru'), + (N'Саратовский Государственный Университет', 'shipments@sgu.ru'); +GO + +INSERT INTO Vehicles (name, tonnage, gas_consumption) +VALUES (N'УАЗ-3303', 1225, 10), + (N'Toyota Supra', 5000, 17), + (N'Сокол', 10000, 20), + (N'MAN TGE', 15000, 30), + (N'MAN TGX', 15000, 32); +GO + +INSERT INTO Cities (name) +VALUES (N'Саратов'), + (N'Подольск'), + (N'Воронеж'), + (N'Магадан'), + (N'Омск'); +GO + +INSERT INTO Drivers (city_id, salary, on_delivery) +SELECT Cities.id, 35000, 0 +FROM Cities WHERE name=N'Саратов'; +GO + +INSERT INTO Drivers (city_id, salary, on_delivery) +SELECT Cities.id, 30000, 0 +FROM Cities WHERE name=N'Подольск'; +GO + +INSERT INTO Drivers (city_id, salary, on_delivery) +SELECT Cities.id, 31000, 0 +FROM Cities WHERE name=N'Воронеж'; +GO + +INSERT INTO Drivers (city_id, salary, on_delivery) +SELECT Cities.id, 28000, 0 +FROM Cities WHERE name=N'Магадан'; +GO + +INSERT INTO Drivers (city_id, salary, on_delivery) +SELECT Cities.id, 36000, 0 +FROM Cities WHERE name=N'Омск'; +GO + + +INSERT INTO Routes (city1_id, city2_id, distance) +SELECT c1.id, c2.id, 2200 +FROM Cities c1 CROSS JOIN Cities c2 +WHERE c1.name=N'Омск' AND c2.name=N'Саратов'; +GO + +INSERT INTO Routes (city1_id, city2_id, distance) +SELECT c1.id, c2.id, 10000 +FROM Cities c1 CROSS JOIN Cities c2 +WHERE c1.name=N'Магадан' AND c2.name=N'Подольск'; +GO + +INSERT INTO Routes (city1_id, city2_id, distance) +SELECT c1.id, c2.id, 490 +FROM Cities c1 CROSS JOIN Cities c2 +WHERE c1.name=N'Подольск' AND c2.name=N'Воронеж'; +GO + +INSERT INTO Routes (city1_id, city2_id, distance) +SELECT c1.id, c2.id, 10000 +FROM Cities c1 CROSS JOIN Cities c2 +WHERE c1.name=N'Воронеж' AND c2.name=N'Магадан'; +GO + +INSERT INTO Routes (city1_id, city2_id, distance) +SELECT c1.id, c2.id, 2200 +FROM Cities c1 CROSS JOIN Cities c2 +WHERE c1.name=N'Саратов' AND c2.name=N'Омск'; +GO + +INSERT INTO Warehouses (city_id, area) +SELECT Cities.id, 36000 +FROM Cities WHERE name=N'Саратов'; +GO + +INSERT INTO Warehouses (city_id, area) +SELECT Cities.id, 36000 +FROM Cities WHERE name=N'Подольск'; +GO + +INSERT INTO Warehouses (city_id, area) +SELECT Cities.id, 36000 +FROM Cities WHERE name=N'Воронеж'; +GO + +INSERT INTO Warehouses (city_id, area) +SELECT Cities.id, 36000 +FROM Cities WHERE name=N'Магадан'; +GO + +INSERT INTO Warehouses (city_id, area) +SELECT Cities.id, 36000 +FROM Cities WHERE name=N'Омск'; +GO + + +INSERT INTO Orders (customer_id, city_id, date) +SELECT Customers.id, Cities.id, GETDATE() +FROM Customers CROSS JOIN Cities +WHERE Customers.name=N'Рога и копыта' AND Cities.name=N'Саратов'; +GO + +INSERT INTO Orders (customer_id, city_id, date) +SELECT Customers.id, Cities.id, GETDATE() +FROM Customers CROSS JOIN Cities +WHERE Customers.name=N'Microsoft' AND Cities.name=N'Подольск'; +GO + +INSERT INTO Orders (customer_id, city_id, date) +SELECT Customers.id, Cities.id, GETDATE() +FROM Customers CROSS JOIN Cities +WHERE Customers.name=N'Жмых Airlines' AND Cities.name=N'Воронеж'; +GO + +INSERT INTO Orders (customer_id, city_id, date) +SELECT Customers.id, Cities.id, GETDATE() +FROM Customers CROSS JOIN Cities +WHERE Customers.name=N'Иван и Ко.' AND Cities.name=N'Магадан'; +GO + +INSERT INTO Orders (customer_id, city_id, date) +SELECT Customers.id, Cities.id, GETDATE() +FROM Customers CROSS JOIN Cities +WHERE Customers.name=N'Саратовский Государственный Университет' AND Cities.name=N'Омск'; +GO + + +INSERT INTO Payments (order_id, amount, date) +VALUES (1, 10000, GETDATE()), + (2, 7000, GETDATE()), + (3, 12000, GETDATE()), + (4, 9000, GETDATE()), + (5, 10000, GETDATE()); +GO + + +INSERT INTO Boxes (order_id, warehouse_id, wrapping_id, volume, weight) +VALUES (1, 5, 1, 10, 20), + (2, 4, 2, 10, 20), + (3, 2, 3, 10, 20), + (4, 3, 4, 10, 20), + (5, 1, 5, 10, 20); +GO + + +INSERT INTO Deliveries (driver_id, vehicle_id, route_id, date) +VALUES (1, 1, 1, GETDATE()), + (2, 2, 2, GETDATE()), + (3, 3, 3, GETDATE()), + (4, 4, 4, GETDATE()), + (5, 5, 5, GETDATE()); +GO + +INSERT INTO Shipments (order_id, delivery_id) +VALUES (1, 1), + (2, 2), + (3, 3), + (4, 4), + (5, 5); +GO + +-- DELETE FROM Shipments; +-- DELETE FROM Payments WHERE amount < 3000; diff --git a/task3_requests.sql b/task3_requests.sql new file mode 100644 index 0000000..e9effec --- /dev/null +++ b/task3_requests.sql @@ -0,0 +1,158 @@ +-- INNER JOIN, LOWER +SELECT LOWER(Customers.email) email, Orders.date +FROM Orders INNER JOIN Customers +ON Orders.customer_id = Customers.id; + +-- LEFT JOIN, UPPER +SELECT UPPER(Customers.name) name, Orders.date +FROM Orders LEFT JOIN Customers +ON Orders.customer_id = Customers.id; + +-- RIGHT JOIN +SELECT Customers.name, Orders.date +FROM Orders RIGHT JOIN Customers +ON Orders.customer_id = Customers.id; + +-- FULL JOIN, IIF +-- Выбрать все коробки и ограничить цену на обёртки в 40 рублей. +SELECT Boxes.id, order_id, warehouse_id, + IIF(price > 40, 40, price) price, + volume, weight +FROM Boxes FULL JOIN Wrappings +ON Boxes.wrapping_id = Wrappings.id; + +-- CROSS JOIN +SELECT gas_consumption, distance +FROM Vehicles CROSS JOIN Routes; + +-- CROSS APPLY +SELECT id, city_id, volume, weight +FROM Warehouses CROSS APPLY ( + SELECT Boxes.volume, Boxes.weight + FROM Boxes WHERE Boxes.warehouse_id = Warehouses.id +) ap; + +-- САМОСОЕДИНЕНИЕ +SELECT R1.city1_id, R2.city2_id, R1.distance + R2.distance AS distance +FROM Routes as R1 +INNER JOIN Routes AS R2 ON R1.city2_id = R2.city1_id; + +--- *** --- + +-- UNION +-- Все коробки, у которых обёртка должна быть мягкой или воздушно-пузырьковой. +SELECT id, volume FROM Boxes WHERE wrapping_id = 3 +UNION +SELECT id, volume FROM Boxes where wrapping_id = 4; + +-- UNION ALL +SELECT city_id FROM Warehouses +UNION ALL SELECT city_id FROM Drivers; + +-- EXCEPT +-- Выбрать все заказы, кроме сделанных 29 февраля 2004 года. +SELECT * FROM Orders +EXCEPT SELECT * FROM Orders WHERE date='2004-02-02'; + +-- INTERSECT +-- ВЫбрать доставки, в которых Водитель 1 ехал на Машине 1 +SELECT * FROM Deliveries WHERE driver_id=1 +INTERSECT SELECT * FROM Deliveries WHERE vehicle_id=1; + +--- *** --- + +-- EXISTS +-- Выбрать оплаченные заказы +SELECT * FROM Orders +WHERE EXISTS( + SELECT * FROM Payments WHERE Payments.order_id = Orders.id +); + +-- IN, BETWEEN +-- Выбрать доставки, исполненные водителями из Города #4 +-- между 20 апреля и 30 апреля 2021 года. +SELECT * FROM Deliveries +WHERE Deliveries.driver_id IN( + SELECT id FROM Drivers WHERE city_id=4 +) AND (Deliveries.date BETWEEN '2021-04-20' AND '2021-04-30'); + +-- LIKE, REPLACE +-- ВЫбрать всех клиентов с почтой на домене mail.ru и заменить этот домен на vk.com +SELECT id, name, REPLACE(email, '@mail.ru', '@vk.com') +FROM Customers WHERE email LIKE '%@mail.ru'; + +-- CASE +SELECT id, + CASE WHEN on_delivery = 1 + THEN N'Водитель в данный момент на доставке' + ELSE N'Водитель свободен' + END AS delivery_status +FROM Drivers; + +--- *** --- + +-- CAST +-- Оценить обёртку для коробок (формула не несёт особого смысла) +SELECT Boxes.id, Boxes.volume / Boxes.weight * CAST(price AS FLOAT) +FROM Boxes FULL JOIN Wrappings on Boxes.wrapping_id = Wrappings.id; + +-- CONVERT +SELECT id, CONVERT(NVARCHAR, Payments.date, 0) FROM Payments; + +-- CHOOSE +SELECT id, order_id, warehouse_id, + CHOOSE( wrapping_id, N'Жёсткая', + N'Полужёсткая', N'Мягкая', + N'Воздушно-Пузырьковая', N'Рулон') wrapping, + volume, weight +FROM Boxes; + +--- *** --- + +-- STUFF, SUBSTRING +-- Скрыть email клиентов +SELECT id, name, STUFF(SUBSTRING(email, 1, 4), 4, 1, '***') +FROM Customers; + +-- ALL +-- Выбрать наибольшую выплату. +SELECT * FROM Payments WHERE amount >= ALL (SELECT amount FROM Payments); + +-- ANY +-- Выбрать машины, занятые в заданный интервал времени +SELECT * FROM Vehicles +WHERE id = ANY ( + SELECT vehicle_id FROM Deliveries + WHERE (date BETWEEN '2021-04-20' AND '2021-04-30') +); + +-- STR +SELECT N'Площадь склада ' + STR(Warehouses.id, 3) + N' в городе ' + Cities.name + N' равна ' + STR(area, 7) +FROM Warehouses INNER JOIN Cities +ON Warehouses.city_id = Cities.id; + +--- *** --- + +-- DATEADD, SYSDATETIMEOFFSET, DATEPART, GETDATE +-- Выбрать доставки следующего месяцы +SELECT * FROM Deliveries +WHERE DATEPART(year, date) = DATEPART(year, GETDATE()) AND + DATEPART(month, DATEADD(month, 1, SYSDATETIMEOFFSET())) = + DATEPART(month, date); + +-- DATEDIFF +-- Время ожидания до доставки от оформления заказа. +SELECT Shipments.id, + DATEDIFF(year, Orders.date, Deliveries.date) * 12 + + DATEDIFF(month, Orders.date, Deliveries.date) +FROM Shipments INNER JOIN Orders +ON Shipments.order_id = Orders.id +FULL JOIN Deliveries +ON Shipments.delivery_id = Deliveries.id; + +-- DATEPART, GETDATE +-- Вычислить количество доставок в каждом месяце этого года, если их больше 50. +SELECT COUNT(id) count, DATEPART(month, date) month FROM Deliveries +WHERE DATEPART(year, date) = DATEPART(year, GETDATE()) +GROUP BY DATEPART(month, date) +HAVING COUNT(id) > 50;
\ No newline at end of file |
