Добавил первые три задания

1 files changed, 326 insertions, 0 deletions

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;