/
Chapter12.txt
348 lines (315 loc) · 10 KB
/
Chapter12.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
/* Chapter 12 */
/* Subqueries - 1 */
ALTER TABLE ShareDetails.Shares
ADD MaximumSharePrice money
DECLARE @MaxPrice money
SELECT @MaxPrice = MAX(Price)
FROM ShareDetails.SharePrices
WHERE ShareId = 1
SELECT @MaxPrice
UPDATE ShareDetails.Shares
SET MaximumSharePrice = @MaxPrice
WHERE ShareId = 1
/* Subqueries - 2 */
SELECT ShareId,MaximumSharePrice
FROM ShareDetails.Shares
UPDATE ShareDetails.Shares
SET MaximumSharePrice = (SELECT MAX(SharePrice)
FROM ShareDetails.SharePrices sp
WHERE sp.ShareId = s.ShareId)
FROM ShareDetails.Shares s
SELECT ShareId,MaximumSharePrice
FROM ShareDetails.Shares
/* Subqueries - 3 */
DECLARE @BackupSet AS INT
SELECT @BackupSet = position
FROM msdb..backupset
WHERE database_name='ApressFinancial'
AND backup_set_id=
(SELECT MAX(backup_set_id)
FROM msdb..backupset s
WHERE database_name='ApressFinancial')
IF @BackupSet IS NULL
BEGIN
RAISERROR('Verify failed. Backup information for database
''ApressFinancial'' not found.', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.10.MSSQLSERVER\MSSQL\Backup\
ApressFinancial\ApressFinancial_backup_200808061136.bak'
WITH FILE = @BackupSet,
NOUNLOAD,
NOREWIND
/* IN 1 */
SELECT *
FROM ShareDetails.Shares
WHERE ShareId IN (1,3,5)
/* IN 2 */
SELECT *
FROM ShareDetails.Shares
WHERE ShareId IN (SELECT ShareId
FROM ShareDetails.Shares
WHERE CurrentPrice > (SELECT MIN(CurrentPrice)
FROM ShareDetails.Shares)
AND CurrentPrice < (SELECT MAX(CurrentPrice)
FROM ShareDetails.Shares))
/* IN 3 */
SELECT *
FROM ShareDetails.Shares
WHERE ShareId = 1
OR ShareId = 3
OR ShareId = 5
/* EXISTS */
SELECT *
FROM ShareDetails.Shares s
WHERE NOT EXISTS (SELECT *
FROM TransactionDetails.Transactions t
WHERE t.RelatedShareId = s.ShareId)
/* Try It Out - Using a Scalar Function with Subqueries Step 1 to 3 */
SELECT t1.TransactionId, t1.DateEntered,t1.Amount,
(SELECT MIN(DateEntered)
FROM TransactionDetails.Transactions t2
WHERE t2.CustomerId = t1.CustomerId
AND t2.DateEntered> t1.DateEntered) as 'To Date',
TransactionDetails.fn_IntCalc(10,t1.Amount,t1.DateEntered,
(SELECT MIN(DateEntered)
FROM TransactionDetails.Transactions t2
WHERE t2.CustomerId = t1.CustomerId
AND t2.DateEntered> t1.DateEntered)) AS 'Interest Earned'
FROM TransactionDetails.Transactions t1
WHERE CustomerId = 1
/* Try It Out - Table Function and CROSS APPLY - Step 1 */
CREATE FUNCTION TransactionDetails.ReturnTransactions
(@CustId bigint) RETURNS @Trans TABLE
(TransactionId bigint,
CustomerId bigint,
TransactionDescription nvarchar(30),
DateEntered datetime,
Amount money)
AS
BEGIN
INSERT INTO @Trans
SELECT TransactionId, CustomerId, TransactionDescription,
DateEntered, Amount
FROM TransactionDetails.Transactions t
JOIN TransactionDetails.TransactionTypes tt ON
tt.TransactionTypeId = t.TransactionType
WHERE CustomerId = @CustId
RETURN
END
GO
/* Try It Out - Table Function and CROSS APPLY - Step 2 */
SELECT c.CustomerFirstName, CustomerLastName,
Trans.TransactionId,TransactionDescription,
DateEntered,Amount
FROM CustomerDetails.Customers AS c
CROSS APPLY
TransactionDetails.ReturnTransactions(c.CustomerId)
AS Trans
/* Try It Out - Common Table Expressions - Example 1 */
USE AdventureWorks
GO
SELECT p.ProductSubcategoryID, s.Name,SUM(ListPrice) AS ListPrice
INTO #Temp1
FROM Production.Product p
JOIN Production.ProductSubcategory s ON s.ProductSubcategoryID =
p.ProductSubcategoryID
WHERE p.ProductSubcategoryID IS NOT NULL
GROUP BY p.ProductSubcategoryID, s.Name
SELECT ProductSubcategoryID,Name,MAX(ListPrice)
FROM #Temp1
GROUP BY ProductSubcategoryID, Name
HAVING MAX(ListPrice) = (SELECT MAX(ListPrice) FROM #Temp1)
DROP TABLE #Temp1
/* Try It Out - Common Table Expressions - Example 2 */
WITH ProdList (ProductSubcategoryID,Name,ListPrice) AS
(
SELECT p.ProductSubcategoryID, s.Name,SUM(ListPrice) AS ListPrice
FROM Production.Product p
JOIN Production.ProductSubcategory s ON s.ProductSubcategoryID =
p.ProductSubcategoryID
WHERE p.ProductSubcategoryID IS NOT NULL
GROUP BY p.ProductSubcategoryID, s.Name
)
SELECT ProductSubcategoryID,Name,MAX(ListPrice)
FROM ProdList
GROUP BY ProductSubcategoryID, Name
HAVING MAX(ListPrice) = (SELECT MAX(ListPrice) FROM ProdList)
/* Try It Out - Recursive CTE - Example */
USE AdventureWorks;
GO
WITH EmployeeReportingStructure
(ManagerID, EmployeeID, EmployeeLevel, Level,
ManagerContactId,ManagerTitle,ManagerFirst,ManagerLast,
EmployeeTitle,EmployeeFirst,EmployeeLast)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title as EmployeeLevel,
0 AS Level,
e.ContactId as ManagerContactId,
CAST(' ' as nvarchar(8)) as ManagerTitle,
CAST(' ' as nvarchar(50)) as ManagerFirst,
CAST(' ' as nvarchar(50)) as ManagerLast,
c.Title as EmployeeTitle,c.FirstName as EmployeeFirst,
c.LastName as EmployeeLast
FROM HumanResources.Employee AS e
INNER JOIN Person.Contact c ON c.ContactId = e.ContactId
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title as EmployeeLevel, Level + 1,
e.ContactId as ManagerContactId,
m.Title as ManagerTitle,m.FirstName as ManagerFirst,
m.LastName as ManagerLast,
c.Title as EmployeeTitle,c.FirstName as EmployeeFirst,
c.LastName as EmployeeLast
FROM HumanResources.Employee AS e
INNER JOIN Person.Contact c ON c.ContactId = e.ContactId
INNER JOIN EmployeeReportingStructure AS d
ON d.EmployeeID = e.ManagerID
INNER JOIN Person.Contact m ON m.ContactId = d.ManagerContactId
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID,
ISNULL(ManagerTitle+' ','')+ManagerFirst+' '+ManagerLast as Manager,
EmployeeLevel,
ISNULL(EmployeeTitle+' ','')+EmployeeFirst+' '+EmployeeLast as Employee,
Level
FROM EmployeeReportingStructure
ORDER BY Level,EmployeeLast,EmployeeFirst
OPTION (MAXRECURSION 4)
/* Try It Out - Pivot - Example 1 */
USE AdventureWorks
GO
SELECT productID,UnitPriceDiscount,SUM(linetotal)
FROM Sales.SalesOrderDetail
WHERE productID IN (776,711,747)
GROUP BY productID,UnitPriceDiscount
ORDER BY productID,UnitPriceDiscount
/* Try It Out - Pivot - Example 2 */
SELECT pt.Discount,ISNULL([711],0.00) As Product711,
ISNULL([747],0.00) As Product747,ISNULL([776],0.00) As Product776
FROM
(SELECT sod.LineTotal, sod.ProductID, sod.UnitPriceDiscount as Discount
FROM Sales.SalesOrderDetail sod) so
PIVOT
(
SUM(so.LineTotal)
FOR so.ProductID IN ([776], [711], [747])
) AS pt
ORDER BY pt.Discount
/* Try It Out - UnPivot - Example 1 */
USE AdventureWorks
go
SELECT pt.Discount,ISNULL([711],0.00) As Product711,
ISNULL([747],0.00) As Product747,ISNULL([776],0.00) As Product776
INTO #Temp1
FROM
(SELECT sod.LineTotal, sod.ProductID, sod.UnitPriceDiscount as Discount
FROM Sales.SalesOrderDetail sod) so
PIVOT
(
SUM(so.LineTotal)
FOR so.ProductID IN ([776], [711], [747])
) AS pt
ORDER BY pt.Discount
/* Try It Out - UnPivot - Example 2 */
SELECT ProductID,Discount, DiscountAppl
FROM (SELECT Discount, product711, Product747, Product776
FROM #Temp1) up1
UNPIVOT ( DiscountAppl FOR ProductID
IN (Product711, Product747, Product776)) As upv2
WHERE DiscountAppl <> 0
ORDER BY ProductID
/* Try It Out - Ranking - Example */
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;
/* Try It Out - RowNumber - Example 1 */
USE AdventureWorks
GO
SELECT ROW_NUMBER() OVER(ORDER BY LastName) AS RowNum,
FirstName + ' ' + LastName
FROM HumanResources.vEmployee
WHERE JobTitle = 'Production Technician - WC60'
ORDER BY LastName
/* Try It Out - RowNumber - Example 2 */
USE AdventureWorks
GO
SELECT ROW_NUMBER()
OVER(PARTITION BY SUBSTRING(LastName,1,1)
ORDER BY LastName) AS RowNum, FirstName + ' ' + LastName
FROM HumanResources.vEmployee
WHERE JobTitle = 'Production Technician - WC60'
ORDER BY LastName
/* Try It Out - Ranking - Example */
USE AdventureWorks
GO
SELECT ROW_NUMBER() OVER(ORDER BY Department) AS RowNum,
RANK() OVER(ORDER BY Department) AS Ranking,
FirstName + ' ' + LastName AS Employee, Department
FROM HumanResources.vEmployeeDepartment
ORDER BY RowNum
/* Try It Out - Dense_Rank - Example */
USE AdventureWorks
GO
SELECT ROW_NUMBER() OVER(ORDER BY Department) AS RowNum,
DENSE_RANK() OVER(ORDER BY Department) AS Ranking,
CONVERT(varchar(25),FirstName + ' ' + LastName), Department
FROM HumanResources.vEmployeeDepartment
ORDER BY RowNum
/* Try It Out - NTile - Example */
USE AdventureWorks
GO
SELECT NTILE(10) OVER(ORDER BY Department) AS NTile,
FirstName + ' ' + LastName, Department
FROM HumanResources.vEmployeeDepartment
/* Try It Out - PowerShell - Step 3 */
get-help * > c:\users\rdewson\documents\powershell\powershell.txt
/* Try It Out - PowerShell - Step 4 */
get-help detailed get-date > c:\users\rdewson\documents\powershell\get-date.txt
/* Try It Out - PowerShell - Step 5 */
get-date -DisplayHint time
/* Try It Out - PowerShell - Step 7 */
invoke-sqlcmd -query
"SELECT CustomerFirstName + ' ' + CustomerLastName
FROM CustomerDetails.Customers" -serverinstance "FAT-BELLY"
/* Try It Out - PowerShell - Step 8 */
SELECT CustomerFirstName + ' ' + CustomerLastName
FROM CustomerDetails.Customers
SELECT 'Query2'
SELECT CustomerFirstName + ' ' + CustomerLastName
FROM CustomerDetails.Customers
WHERE customerid > 1
DECLARE @return_value int,
@ClearedBalance money,
@UnclearedBalance money
EXEC @return_value = [CustomerDetails].[apf_CustBalances]
@CustId = 1,
@ClearedBalance = @ClearedBalance OUTPUT,
@UnclearedBalance = @UnclearedBalance OUTPUT
SELECT @ClearedBalance
/* Try It Out - PowerShell - Step 9 */
invoke-sqlcmd -inputfile "c:\users\rdewson\documents\powershell\
invoke-cmd-ex.sql" | out-file -filepath c:\users\rdewson\documents\
powershell\multipleoutput.txt"
/* Try It Out - PowerShell - Step 10 */
SELECT CustomerFirstName + ' ' + CustomerLastName AS 'Name'
FROM CustomerDetails.Customers
SELECT 'Query2'
SELECT CustomerFirstName + ' ' + CustomerLastName AS 'AnotherName'
FROM CustomerDetails.Customers
WHERE customerid > 1
DECLARE @return_value int,
@ClearedBalance money,
@UnclearedBalance money
EXEC @return_value = [CustomerDetails].[apf_CustBalances]
@CustId = 1,
@ClearedBalance = @ClearedBalance OUTPUT,
@UnclearedBalance = @UnclearedBalance OUTPUT
SELECT @ClearedBalance