MX5pvTTU

1use TestSample
2go
3
4IF OBJECT_ID('RoutesStations', 'U') IS NOT NULL
5	DROP TABLE RoutesStations
6
7IF OBJECT_ID('Stations', 'U') IS NOT NULL
8	DROP TABLE Stations
9
10IF OBJECT_ID('Routes', 'U') IS NOT NULL
11	DROP TABLE Routes
12
13IF OBJECT_ID('Trains', 'U') IS NOT NULL
14	DROP TABLE Trains
15
16IF OBJECT_ID('TrainTypes', 'U') IS NOT NULL
17	DROP TABLE TrainTypes
18GO
19
20create table TrainTypes (
21	TTid int primary key IDENTITY(1,1),
22	TTDescription varchar(50)
23);
24GO
25
26create table Trains (
27	Tid int primary key IDENTITY(1,1),
28	TName varchar(50),
29	TTid int,
30	CONSTRAINT FK_Trains_TrainTypes FOREIGN KEY (TTid) REFERENCES TrainTypes (TTid)
31 );
32GO
33
34 create table Routes (
35	Rid int primary key IDENTITY(1,1),
36	RName varchar(50) unique,
37	Tid int, -- se putea pune REFERENCES direct aici
38	CONSTRAINT FK_Routes_Trains FOREIGN KEY (Tid) REFERENCES Trains(Tid)
39 );
40GO
41
42create table Stations (
43	Sid int primary key IDENTITY(1,1),
44	SName varchar(50) unique,
45);
46GO
47
48create table RoutesStations (
49	Rid int REFERENCES Routes(Rid),
50	Sid int REFERENCES Stations(Sid),
51	Arrival time,
52	Departure time,
53	PRIMARY KEY (Rid, Sid) -- COMPOSITE primary key
54);
55GO
56
57INSERT TrainTypes VALUES('regio'), ('interregio')
58INSERT Trains VALUES('t1', 1), ('t2', 1), ('t3', 2)
59INSERT Routes VALUES('r1', 1), ('r2', 2), ('r3', 3)
60INSERT Stations VALUES('s1'), ('s2'), ('s3')
61INSERT RoutesStations(RID, SID, Arrival, Departure) VALUES
62(1, 1, '7:00', '7:10'), (1, 2, '8:00', '8:10'), (1, 3, '9:00', '9:10'),
63(2, 3, '9:00', '9:10'),
64(3, 1, '7:00', '7:10'), (3, 2, '8:00', '8:10'), (3, 3, '9:00', '9:10')
65
66SELECT * FROM TrainTypes
67SELECT * FROM Trains
68SELECT * FROM Routes
69SELECT * FROM Stations
70SELECT * FROM RoutesStations
71GO
72
73-- or alter ca sa nu puste la a doua executie
74create or alter procedure uspAddStationStationToRoute @RName VARCHAR(50), @SName VARCHAR(50), 
75@Arrival TIME, @Departure TIME
76AS
77	DECLARE @RID INT = (SELECT Rid FROM Routes WHERE RName=@RName)
78	DECLARE @SID INT = (SELECT Sid FROM Stations WHERE SName=@SName)
79
80	if @RID is null OR @SID is null
81		RAISERROR('rid/sid is/are null', 16, 1);
82	else
83		if EXISTS (select * from RoutesStations where Rid=@RID and Sid=@SID)
84			RAISERROR('station already on route', 16, 1);
85		else
86			insert RoutesStations (RID, SID, Arrival, Departure) VALUES 
87			(@RID, @SID, @Arrival, @Departure);
88GO
89
90uspAddStationStationToRoute 'r2', 's1', '10:00', '11:00'
91
92SELECT * FROM Routes
93SELECT * FROM Stations
94SELECT * FROM RoutesStations
95GO
96
97-- or alter ca sa nu pushte cand dai a doua oara
98create or alter function ufGetStations(@R int)
99RETURNS TABLE
100RETURN SELECT S.SName
101FROM Stations S
102WHERE S.Sid IN
103	(SELECT RS.Sid
104	FROM RoutesStations RS
105	GROUP BY RS.Sid
106	HAVING COUNT(*) > @R)
107GO
108
109SELECT * FROM ufGetStations(2)