· 9 years ago · Dec 14, 2016, 01:28 PM
1USE Simulare
2GO
3DROP TABLE Orar
4DROP TABLE Statie
5DROP TABLE Ruta
6DROP TABLE Tren
7DROP TABLE TipTren
8GO
9
10CREATE TABLE TipTren
11(IdTip INT PRIMARY KEY,
12Descriere VARCHAR(100))
13
14CREATE TABLE Tren
15(IdTren INT PRIMARY KEY,
16NumeTren VARCHAR(100) UNIQUE,
17IdTip INT REFERENCES TipTren(IdTip))
18
19CREATE TABLE Statie
20(IdStatie INT PRIMARY KEY,
21NumeStatie VARCHAR(100) UNIQUE)
22
23CREATE TABLE Ruta
24(IdRuta INT PRIMARY KEY,
25NumeRuta VARCHAR(100) UNIQUE,
26IdTren INT REFERENCES Tren(IdTren) UNIQUE)
27
28CREATE TABLE Orar
29(IdStatie INT REFERENCES Statie(IdStatie),
30IdRuta INT REFERENCES Ruta(IdRuta),
31Sosire TIME,
32Plecare TIME
33PRIMARY KEY(IdStatie, IdRuta))
34GO
35
36INSERT TipTren(IdTip, Descriere) VALUES (1, 'tip1'), (2, 'tip2')
37INSERT Tren(IdTren, NumeTren, IdTip) VALUES
38(1, 't1', 1), (2, 't2', 1), (3, 't3', 1)
39INSERT Ruta(IdRuta, NumeRuta, IdTren) VALUES
40(1, 'r1', 1), (2, 'r2', 2), (3, 'r3', 3)
41INSERT Statie(IdStatie, NumeStatie) VALUES
42(1, 's1'), (2, 's2'), (3, 's3')
43INSERT Orar(IdStatie, IdRuta, Sosire, Plecare) VALUES
44(1, 1, '5:00 PM', '5:10 PM'), (1, 2, '6:00 PM', '6:10 PM'), (1, 3, '7:00 PM', '7:10 PM'),
45(2, 1, '4:55 PM', '5:05 PM'), (2, 2, '7:55 PM', '8:05 PM'),
46(3, 1, '9:00 PM', '9:05 PM')
47
48GO
49--procedura stocata\
50ALTER PROC StatiePeRuta @NumeStatie VARCHAR(100), @NumeRuta VARCHAR(100), @Sosire TIME, @Plecare TIME
51AS
52 DECLARE @IdStatie INT = (SELECT IdStatie FROM Statie WHERE NumeStatie = @NumeStatie),
53 @IdRuta INT = (SELECT IdRuta FROM Ruta WHERE NumeRuta = @NumeRuta)
54 IF NOT EXISTS (SELECT * FROM Orar WHERE IdRuta = @IdRuta AND IdStatie = @IdStatie)
55 INSERT Orar(IdStatie, IdRuta, Plecare, Sosire)
56 VALUES(@IdStatie, @IdRuta, @Plecare, @Sosire)
57 ELSE
58 UPDATE Orar
59 SET Sosire = @Sosire, Plecare = @Plecare
60 WHERE IdRuta = @IdRuta AND IdStatie = @IdStatie
61GO
62
63EXEC StatiePeRuta @NumeStatie = 's1', @NumeRuta = 'r3', @Sosire = '10:00 AM', @Plecare = '10:10 AM'
64EXEC StatiePeRuta @NumeStatie = 's2', @NumeRuta = 'r3', @Sosire = '13:00', @Plecare = '13:10'
65
66SELECT IdRuta, IdStatie, Sosire, Plecare FROM Orar
67ORDER BY IdRuta, IdStatie
68
69UPDATE Orar
70SET Sosire = '9:00 PM', Plecare = '9:05 PM'
71WHERE IdRuta = 3 AND IdStatie = 1
72DELETE FROM Orar WHERE IdRuta = 3 AND IdStatie = 2
73
74--view
75GO
76
77ALTER VIEW RuteCuToateStatiile
78AS
79 SELECT NumeRuta
80 FROM Ruta
81 WHERE IdRuta IN
82 (SELECT IdRuta
83 FROM Orar
84 GROUP BY IdRuta
85 HAVING COUNT(IdStatie) = (SELECT COUNT(IdStatie) FROM Statie)
86 )
87
88SELECT * FROM RuteCuToateStatiile
89
90-- functia
91ALTER FUNCTION StatiiAglomerate(@Moment TIME)
92RETURNS TABLE
93AS
94RETURN
95 SELECT NumeStatie
96 FROM Statie
97 WHERE IdStatie IN
98 (SELECT IdStatie
99 FROM Orar
100 WHERE @Moment BETWEEN Sosire AND Plecare
101 GROUP BY IdStatie
102 HAVING COUNT(IdRuta) >= 2)
103GO
104
105SELECT * FROM StatiiAglomerate('6:00 PM')
106SELECT IdRuta, IdStatie, Sosire, Plecare
107FROM Orar
108ORDER BY IdRuta, IdStatie