>
SQL- сценарій
USE master
----//Створення БД з параметрами за замовчуванням//----- DATABASE AutotransportationAutotransportation
----//Створення таблиць//----- TABLE WaySheets
(int IDENTITY (1,1) PRIMARY KEY, char (80) NOT NULL, datetime NOT NULL, int NOT NULL, int NOT NULL, int NULL
) TABLE WaySheet
(int IDENTITY (1,1) PRIMARY KEY, char (80) NOT NULL, datetime NOT NULL, char (20) NOT NULL, char (20) NOT NULL, char (10) NOT NULL , char (80) NOT NULL, int NOT NULL, char (80) NULL, int NULL, char (80) NULL, int NULL, int NULL, int NULL, int NULL, int NULL, char (5) NULL, char ( 5) NULL, char (5) NULL, char (5) NULL, int NULL, int NULL, char (5) NULL, char (5) NULL, char (5) NULL, char (5) NULL, int NULL, int NULL, money NULL, money NULL
) TABLE Drivers
(int IDENTITY (300,1) PRIMARY KEY, char (80) NOT NULL, char (80) NOT NULL, char (80) NOT NULL, datetime NOT NULL, char (10) NOT NULL , char (10) NOT NULL, int NOT NULL
) TABLE WorkedTime
(int IDENTITY (1,1) PRIMARY KEY, int NOT NULL, datetime NULL, datetime NOT NULL, int NOT NULL, int NOT NULL, int NULL, int NULL, int NULL, int NULL, int NULL, int NULL, int NULL, datetime NULL, datetime NULL, datetime NULL, datetime NULL
) TABLE Conductors
(int IDENTITY (200,1) PRIMARY KEY, char (80) NOT NULL, char (80) NOT NULL, char (80) NOT NULL, datetime NOT NULL, char (10) NOT NULL
) TABLE Revenue
(int IDENTITY (1,1) PRIMARY KEY, int NOT NULL, datetime NOT NULL, money NOT NULL, money NOT NULL, int NOT NULL, int NOT NULL, int NOT NULL, int NOT NULL
) TABLE Buses
(int IDENTITY (1,1) PRIMARY KEY, char (80) NOT NULL, char (6) NOT NULL, int NOT NULL
) TABLE Distance
(int IDENTITY (1,1) PRIMARY KEY, int NOT NULL, datetime NOT NULL, int NOT NUlL
) TABLE Routes
(int IDENTITY (1,1) PRIMARY KEY, char (80) NOT NULL, char (200) NULL, int NOT NULL, int NOT NULL, money NOT NULL, int NOT NULL, datetime NOT NULL, datetime NOT NULL
)
----//Створення уявлень//----- VIEW SearchDriversView ASRTRIM (LastName) + + SUBSTRING (FirstName, 1,1) + . Raquo; + SUBSTRING (Patronymic, 1,1) + . Raquo; AS DriverDriversVIEW SearchConductorView ASRTRIM (LastName) + + SUBSTRING (FirstName, 1,1) + . Raquo; + SUBSTRING (Patronymic, 1,1) + . Raquo; AS ConductorConductorsVIEW SearchBusView ASRTRIM (Brend) + ( + RTRIM (StateNumber) + ) AS BusBusesVIEW WaySheetsView ASRTRIM (NumberSheet) AS NumberSheet, Date,
(SELECT RTRIM (LastName) + + SUBSTRING (FirstName, 1,1) + . + SUBSTRING (Patronymic, 1,1) + . FROM Drivers WHERE Drivers.IDDriver=WaySheets.IDDriver) AS Driver,
(SELECT RTRIM (Brend) + ( + RTRIM (StateNumber) + ) FROM Buses WHERE Buses.IDBus=WaySheets.IDBus) AS Bus,
(SELECT RTRIM (LastName) + + SUBSTRING (FirstName, 1,1) + . + SUBSTRING (Patronymic, 1,1) + . FROM Conductors WHERE Conductors.IDConductor=WaySheets.IDConductor) AS ConductorWaySheetsVIEW WaySheets1View ASRTRIM (NumberSheet) AS NumberSheet, Date, IDDriver,
(SELECT RTRIM (LastName) + + SUBSTRING (FirstName, 1,1) + . + SUBSTRING (Patronymic, 1,1) + . FROM Drivers WHERE Drivers.IDDriver=WaySheets.IDDriver) AS Driver,
(SELECT RTRIM (Brend) + ( + RTRIM (StateNumber) + ) FROM Buses WHERE Buses.IDBus=WaySheets.IDBus) AS Bus,
(SELECT RTRIM (LastName) + + SUBSTRING (FirstName, 1,1) + . + SUBSTRING (Patronymic, 1,1) + . FROM Conductors WHERE Conductors.IDConductor=WaySheets.IDConductor) AS ConductorWaySheetsVIEW WaySheetWiew ASRTRIM (NumberSheet) AS NumberSheet, Date, RTRIM (DateDay) AS DateDay, RTRIM (BrendBus) AS BrendBus, StateNumberBus, (Driver) AS Driver, IDDriver, RTRIM (Conductor) AS Conductor, IDConductor, RTRIM (NameRoute) AS NameRoute, Change ,, Distance, PlannedTrip, ActualTrip, PlannedExit, ActualExit, PlannedReturn, ActualReturn, PlannedSpendFuel ,, Repairs, TO2, Stopping, Reserve, SpeedometerExit, SpeedometerReturn, PlannedRevenue, ActualRevenueWaySheetVIEW BusesView ASRTRIM ( Brend) AS Brend, RTRIM (StateNumber) AS StateNumber, SpeedometerBusesVIEW ConductorsView ASIDConductor, RTRIM (LastName) + + SUBSTRING (FirstName, 1,1) + . Raquo; + SUBSTRING (Patronymic, 1,1) + . Raquo; AS Conductor, (LastName) AS LastName, RTRIM (FirstName) AS FirstName, RTRIM (Patronymic) AS Patronymic, Birthday, (PassportNumber, 1, 4) + + SUBSTRING (PassportNumbe...
