diff options
Diffstat (limited to 'task2_init-databases.sql')
| -rw-r--r-- | task2_init-databases.sql | 326 |
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; |