path: root/task2_init-databases.sql

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;