BJqYaJfL

1--1c
2--2c
3--3b
4
5USE Prep_practical
6GO
7
8DROP TABLE IF EXISTS StationsRoutes
9DROP TABLE IF EXISTS Stations
10DROP TABLE IF EXISTS Routes
11DROP TABLE IF EXISTS Trains
12DROP TABLE IF EXISTS TrainTypes
13
14CREATE TABLE TrainTypes
15	(TrainTypeId INT PRIMARY KEY,
16	TTName VARCHAR(60),
17	TTDescription VARCHAR(400))
18
19
20CREATE TABLE Trains
21	(TrainId INT PRIMARY KEY,
22	TName VARCHAR(60),
23	TrainTypeId INT REFERENCES TrainTypes(TrainTypeId))
24
25CREATE TABLE Routes
26	(RouteId INT PRIMARY KEY,
27	RName VARCHAR(60) UNIQUE,
28	TrainId INT REFERENCES Trains(TrainId))
29
30CREATE TABLE Stations
31	(StationId INT PRIMARY KEY,
32	SName VARCHAR(60) UNIQUE)
33
34CREATE TABLE StationsRoutes
35	(StationId INT REFERENCES Stations(StationId),
36	RouteId INT REFERENCES Routes(RouteId),
37	Arrival TIME,
38	Departure TIME,
39	PRIMARY KEY(StationId, RouteId)
40	)
41
42GO
43
44CREATE OR ALTER PROC uspUpdateRoute @Route VARCHAR(60), @Station VARCHAR(60), @Arrival TIME, @Departure TIME
45AS
46	DECLARE @RID INT, @SID INT
47	IF NOT EXISTS (SELECT * FROM Routes WHERE RName = @Route)
48	BEGIN
49		RAISERROR('Route name not valid.', 16, 1)
50		RETURN
51	END
52
53	IF NOT EXISTS (SELECT * FROM Stations WHERE SName = @Station)
54	BEGIN
55		RAISERROR('Station name not valid.', 16, 1)
56		RETURN
57	END
58
59	SELECT @RID = (SELECT RouteId FROM Routes WHERE RName = @Route),
60		@SID = (SELECT StationId FROM Stations WHERE SName = @Station)
61
62	IF EXISTS (SELECT *
63				FROM StationsRoutes
64				WHERE StationId = @SID AND RouteId = @RID)
65			UPDATE StationsRoutes
66			SET Arrival = @Arrival, Departure = @Departure
67			WHERE StationId = @SID AND RouteId = @RID
68	ELSE
69		INSERT StationsRoutes(StationId, RouteId, Arrival, Departure)
70		VALUES(@SID, @RID, @Arrival, @Departure)
71GO
72
73INSERT TrainTypes VALUES(1, 'type1', 'description1')
74INSERT Trains VALUES (1, 't1', 1), (2, 't2', 1), (3, 't3', 1)
75INSERT Routes VALUES (1, 'r1', 1), (2, 'r2', 2), (3, 'r3', 3)
76INSERT Stations VALUES (1, 's1'), (2, 's2'), (3, 's3')
77
78SELECT * FROM TrainTypes
79SELECT * FROM Trains
80SELECT * FROM Routes
81SELECT * FROM Stations
82
83SELECT * FROM StationsRoutes
84ORDER BY RouteId
85
86GO
87EXEC uspUpdateRoute @Route = 'r1', @Station = 's1', @Arrival = '4:20', @Departure = '4:30'
88EXEC uspUpdateRoute @Route = 'r1', @Station = 's2', @Arrival = '1:20', @Departure = '1:30'
89EXEC uspUpdateRoute @Route = 'r1', @Station = 's3', @Arrival = '2:20', @Departure = '2:30'
90
91EXEC uspUpdateRoute @Route = 'r2', @Station = 's1', @Arrival = '4:20', @Departure = '4:30'
92EXEC uspUpdateRoute @Route = 'r2', @Station = 's2', @Arrival = '1:20', @Departure = '1:30'
93EXEC uspUpdateRoute @Route = 'r2', @Station = 's3', @Arrival = '2:20', @Departure = '2:30'
94
95EXEC uspUpdateRoute @Route = 'r3', @Station = 's1', @Arrival = '4:20', @Departure = '4:30'
96
97GO
98
99
100CREATE OR ALTER VIEW vRoutesAllStations
101AS
102	SELECT r.RName
103	FROM Routes r
104	WHERE NOT EXISTS 
105		(SELECT StationId
106		FROM Stations
107		EXCEPT
108		SELECT StationId
109		FROM StationsRoutes
110		WHERE RouteId = r.RouteId)
111
112GO
113
114SELECT * FROM vRoutesAllStations
115
116GO
117
118CREATE OR ALTER FUNCTION ufFilterStationsByNumOfRoutes(@R INT)
119RETURNS TABLE
120RETURN
121SELECT S.SName
122FROM Stations S
123WHERE S.StationId IN (
124	SELECT SR.StationId
125	FROM StationsRoutes SR
126	GROUP BY SR.StationId
127	HAVING COUNT(*) > @R
128)
129
130GO
131
132SELECT *
133FROM ufFilterStationsByNumOfRoutes(2)