· 9 years ago · Oct 31, 2016, 02:26 AM
1use Railway
2go
3
4
5-- Scalar subquery expected to return one value
6select r_TrainDirector1
7from Route
8where r_DepotId = (select d_Id from Depot where d_Name like 'O%')
9
10
11-- Multi-Valued Subqueries
12select r_TrainDirector1
13from Route
14where r_DepotId in (select d_Id from Depot where d_Name like 'D%')
15
16-- Correlated subquery
17select *
18from Route as R1
19where r_TrainId =
20 (select MAX(R2.r_TrainId)
21 from Route as R2
22 where R2.r_DepotId = R1.r_DepotId);
23
24-- EXISTS
25-- select only depot that has routes
26select *
27from Depot as D
28where EXISTS
29 (select * from Route as R
30 where R.r_DepotId = D.d_Id);
31
32-- Derived Tables
33select *
34from (select loc_Id, loc_Number
35 from Locomotive
36 where loc_Number % 2 = 0) AS PairLoc
37join Train on t_LocomotiveId = loc_Id
38
39-- Common Table Expressions
40
41with KStation as
42(
43 select s_Id, s_Name, s_Number, s_RoutId, s_Director
44 from Station
45 where s_Name like N'K%'
46)
47select * from KStation;
48
49
50-- Multiple References
51
52with KStation as
53(
54 select s_Id, s_Name, s_Number, s_RoutId, s_Director
55 from Station
56 where s_RoutId % 2 = 0
57)
58select *
59from KStation K1
60join KStation K2 on K1.s_Number > K2.s_Number;
61
62-- Views Described
63-- Creating USACusts View
64IF OBJECT_ID('CombiLocomotive') IS NOT NULL
65 DROP VIEW CombiLocomotive;
66GO
67CREATE VIEW CombiLocomotive
68AS
69
70SELECT
71 loc_Id, loc_Number, loc_DepotId, d_Name
72FROM Locomotive
73join Depot on d_Id = loc_DepotId
74join DepotType on dt_Id = d_DepotTypeId
75WHERE dt_Name = N'Combi';
76GO
77
78SELECT loc_Number, d_Name
79FROM CombiLocomotive;
80GO
81
82---------------------------------------------------------------------
83-- Views and ORDER BY
84
85-- Instead, use ORDER BY in Outer Query
86SELECT loc_Number, d_Name
87FROM CombiLocomotive
88order by d_Name
89GO
90
91-- ENCRYPTION
92ALTER VIEW CombiLocomotive
93AS
94
95SELECT
96 loc_Id, loc_Number, loc_DepotId, d_Name
97FROM Locomotive
98join Depot on d_Id = loc_DepotId
99join DepotType on dt_Id = d_DepotTypeId
100WHERE dt_Name = N'Combi';
101GO
102
103SELECT OBJECT_DEFINITION(OBJECT_ID('CombiLocomotive'));
104GO
105
106ALTER VIEW CombiLocomotive WITH ENCRYPTION
107AS
108
109SELECT
110 loc_Id, loc_Number, loc_DepotId, d_Name
111FROM Locomotive
112join Depot on d_Id = loc_DepotId
113join DepotType on dt_Id = d_DepotTypeId
114WHERE dt_Name = N'Combi';
115GO
116
117SELECT OBJECT_DEFINITION(OBJECT_ID('CombiLocomotive'));
118
119EXEC sp_helptext 'CombiLocomotive';
120GO
121
122---------------------------------------------------------------------
123-- SCHEMABINDING
124
125ALTER VIEW CombiLocomotive WITH SCHEMABINDING
126AS
127
128SELECT
129 loc_Id, loc_Number, loc_DepotId, d_Name
130FROM dbo.Locomotive
131join dbo.Depot on d_Id = loc_DepotId
132join dbo.DepotType on dt_Id = d_DepotTypeId
133WHERE dt_Name = N'Combi';
134GO
135
136-- Try a schema change
137
138ALTER TABLE Locomotive DROP COLUMN loc_Number;
139
140GO
141
142---------------------------------------------------------------------
143-- CHECK OPTION
144-- Add CHECK OPTION to the View
145ALTER VIEW CombiLocomotive WITH SCHEMABINDING
146AS
147
148SELECT
149 loc_Id, loc_Number, loc_DepotId, d_Name
150FROM dbo.Locomotive
151join dbo.Depot on d_Id = loc_DepotId
152join dbo.DepotType on dt_Id = d_DepotTypeId
153WHERE dt_Name = N'Combi'
154WITH CHECK OPTION;
155GO
156
157-- Notice that you can't insert a row through the view
158
159IF OBJECT_ID('CombiLocomotive') IS NOT NULL DROP VIEW CombiLocomotive;
160GO
161
162---------------------------------------------------------------------
163-- Inline User Defined Functions
164IF OBJECT_ID('dbo.GetTrainByDType') IS NOT NULL
165 DROP FUNCTION dbo.GetTrainByDType;
166GO
167CREATE FUNCTION dbo.GetTrainByDType
168 (@depotType AS nvarchar(50)) RETURNS TABLE
169AS
170RETURN
171 SELECT Train.*
172 FROM Train
173 join Locomotive on loc_Id = t_LocomotiveId
174 join Depot on d_Id = loc_DepotId
175 join DepotType on dt_Id = d_DepotTypeId
176 WHERE dt_Name = @depotType;
177GO
178
179-- Test Function
180SELECT *
181FROM dbo.GetTrainByDType('Combi') AS O;
182
183-- Cleanup
184IF OBJECT_ID('dbo.GetTrainByDType') IS NOT NULL
185 DROP FUNCTION dbo.GetTrainByDType;
186GO
187
188-- APPLY
189--------------------------------------------------------------------
190
191SELECT R.r_Id, R.r_TrainDirector1, A.s_Name, s_Number
192FROM Route AS R
193 CROSS APPLY
194 (SELECT s_Id, s_Name, s_Number, s_Director
195 FROM Station AS S
196 WHERE R.r_Id = S.s_RoutId) AS A;
197
198
199SELECT R.r_Id, R.r_TrainDirector1, A.s_Name, s_Number
200FROM Route AS R
201 OUTER APPLY
202 (SELECT s_Id, s_Name, s_Number, s_Director
203 FROM Station AS S
204 WHERE R.r_Id = S.s_RoutId) AS A;