· 6 years ago · Mar 25, 2019, 04:56 AM
1==============================================
2 --T-SQL Hands On Project practice_Exam 70-761
3==============================================
4-- Basic SELECT Statement
5
6-- The USE statement sets the context, or database,
7-- that the query will be executed against
8USE AdventureWorks2012;
9
10SELECT
11 LastName, FirstName
12FROM
13 Person.Person
14
15 -- Column Aliasing
16
17USE AdventureWorks2012;
18
19SELECT FirstName AS First, LastName AS Last
20FROM Person.Person
21WHERE LastName = 'Adams' AND FirstName = 'Alex'
22
23SELECT FirstName 'Employee First Name', LastName 'Last Name'
24FROM Person.Person
25WHERE LastName = 'Adams' AND FirstName = 'Alex'
26
27
28-- Concatenation Example
29
30select LastName, FirstName
31from person.person
32
33-- Let's concatenate the Last and First names into one column
34select distinct LastName + ', ' + FirstName AS Employee
35from person.person
36-- order by LastName, FirstName --Note this doesn't work...must use alias!
37order by Employee
38
39
40-- Now, let's concatenate a number with a name
41-- This will fail due to the data type of EmailPromotion
42select distinct FirstName + ' ' + LastName + ' : ' + EmailPromotion
43from person.person
44
45-- We can convert EmailPromotion to a string
46select distinct FirstName + ' ' + LastName + ' : ' + CONVERT(varchar(5),EmailPromotion)
47from person.person
48
49-- We can also use CONCAT which will perform the conversion automatically
50select CONCAT(FirstName,' ',LastName,' : ',EmailPromotion) Employee
51from person.person
52order by lastname, firstname
53
54-- Using DISTINCT
55
56USE AdventureWorks2012;
57
58-- This query returns all columns and all rows
59SELECT * FROM Sales.SalesOrderDetail
60
61-- This query returns all ProductID rows from the table
62-- including duplicate rows
63SELECT ProductID FROM Sales.SalesOrderDetail
64
65-- By using the DISTINCT keyword, we can limit the results
66-- set to only the unique ProductID values in the table
67SELECT DISTINCT ProductID FROM Sales.SalesOrderDetail
68
69
70-- Orders the results by LastName only
71
72select distinct LastName, FirstName, EmailPromotion
73from Person.person
74order by LastName
75
76-- Orders the results by LastName, then FirstName
77select distinct LastName, FirstName, EmailPromotion
78from Person.person
79order by LastName,FirstName
80
81-- Orders the results by LastName in descending order (Z-A), then FirstName
82select distinct LastName, FirstName, EmailPromotion
83from Person.person
84order by LastName DESC,FirstName
85
86-- Orders the results by EmailPromotion
87select distinct LastName, FirstName, EmailPromotion
88from Person.person
89order by EmailPromotion ASC
90
91
92-- WHERE clause example
93
94USE AdventureWorks2012;
95
96SELECT
97 *
98FROM
99 Person.Person
100WHERE
101 LastName = 'Adams' AND FirstName = 'Alex'
102
103 ============================================
104 --SECTION 5:
105 ============================================
106
107--CASE Expressions
108
109select *
110from Production.Product
111
112
113-- Using a simple CASE expression
114-- Adds a column to the result set that contains a
115-- conditional result
116select ProductID, Name, Availability=
117 CASE DaysToManufacture
118 WHEN '0' THEN 'Immediate'
119 WHEN '1' THEN 'Two Business Days'
120 WHEN '2' THEN 'Three Business Days'
121 WHEN '3' THEN 'Four Business Days'
122 Else 'Verify Availability'
123 END
124from Production.Product
125
126
127-- Using a searched CASE expression
128-- Allows the use of comparison values in the CASE expression
129select ProductID, Name, Availability=
130 CASE
131 WHEN DaysToManufacture = '0' THEN 'Immediate'
132 WHEN DaysToManufacture >0 AND DaysToManufacture < '4' THEN 'Four Business Days'
133 WHEN DaysToManufacture = '4' THEN 'Six Business Days'
134 ELSE 'Verify Availability'
135 END
136from Production.Product
137
138--INTERSECT and EXCEPT
139
140-- Here are all the products
141select productid
142from production.product
143order by productid
144
145-- Here are the products that have work orders
146select productid
147from production.workorder
148
149-- Here's records for rows that have the same productid in the tables
150-- on the left and right of the INTERSECT operator
151select productid
152from production.product
153INTERSECT
154select productid
155from production.workorder
156
157
158-- We can use EXCEPT to return all values from the left side of
159-- the INTERSECT operator that do not appear on the right side
160select productid
161from production.product
162EXCEPT
163select productid
164from production.workorder
165
166 --LIKE and IN
167
168-- Select all names with the last name of Adams
169select Lastname + ', ' + Firstname
170from person.person
171where Lastname='Adams'
172
173
174-- Select all last names that begin with A
175select Lastname + ', ' + Firstname
176from person.person
177where Lastname like 'A%'
178
179
180-- Select all names with 'ber' anywhere in the last name
181select Lastname + ', ' + Firstname
182from person.person
183where Lastname like '%ber%'
184
185-- Select all names with last names beginning with a, d, or g
186select Lastname + ', ' + Firstname
187from person.person
188where Lastname LIKE '[adg]%'
189
190-- Select all names with last names beginning with f, g, h, or i
191select Lastname + ', ' + Firstname
192from person.person
193where Lastname LIKE '[f-i]%'
194
195-- Select all names where last name is Adams, Jones, or Smith
196-- and the first name begins with d, e, or f
197select Lastname + ', ' + Firstname
198from person.person
199where Lastname IN ('Adams', 'Jones', 'Smith')
200AND Firstname LIKE '[d-f]%'
201
202
203 -- OFFSET and FETCH Examples
204
205-- This query returns over 500 rows
206SELECT productid, Name, ProductNumber, ListPrice
207FROM Production.Product
208ORDER BY Name ASC
209
210-- We can use OFFSET and FETCH to return the
211-- first 15 rows
212SELECT productid, Name, ProductNumber, ListPrice
213FROM Production.Product
214ORDER BY Name ASC
215OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY;
216
217-- This query uses OFFSET and FETCH to skip the
218-- first 15 rows and return the next 15 rows
219SELECT productid, Name, ProductNumber, ListPrice
220FROM Production.Product
221ORDER BY Name ASC
222OFFSET 15 ROWS FETCH NEXT 15 ROWS ONLY;
223
224
225-- This query uses OFFSET and FETCH to skip the
226-- first 30 rows and return the next 15 rows
227SELECT productid, Name, ProductNumber, ListPrice
228FROM Production.Product
229ORDER BY Name ASC
230OFFSET 30 ROWS FETCH NEXT 15 ROWS ONLY;
231
232 --PIVOT Example
233
234-- Let's create our example table
235CREATE TABLE dbo.PivotTest
236(
237 OrderID INT NOT NULL,
238 OrderDate DATE NOT NULL,
239 SalesID INT NOT NULL,
240 CustID VARCHAR(5) NOT NULL,
241 Qty INT NOT NULL,
242 CONSTRAINT PK_Orders PRIMARY KEY(OrderID)
243);
244
245-- Load the example table with test data
246INSERT INTO dbo.PivotTest(OrderID, OrderDate, SalesID, CustID, Qty)
247VALUES
248 (30001, '20120601', 3, 'A', 9),
249 (10001, '20121018', 2, 'A', 21),
250 (10005, '20121018', 1, 'B', 15),
251 (40001, '20130111', 3, 'A', 26),
252 (10006, '20130107', 1, 'C', 8),
253 (20001, '20130209', 2, 'B', 21),
254 (40005, '20140209', 1, 'A', 15),
255 (20002, '20140222', 1, 'C', 13),
256 (30003, '20140417', 2, 'B', 8),
257 (30004, '20120417', 3, 'C', 7),
258
259
260 --UNION
261
262 -- We need to create an alphabetized list of people for Christmas cards
263
264CREATE TABLE Employees
265(
266 EID int not null,
267 FirstName varchar(20) not null,
268 LastName varchar(25) not null,
269 StreetAddress varchar(50),
270 City varchar(20),
271 State char(2)
272 )
273
274
275 CREATE TABLE Customers
276 (
277 CID int not null,
278 FirstName varchar(20) not null,
279 LastName varchar(25) not null,
280 StreetAddress varchar(50),
281 City varchar(20),
282 State char(2)
283 )
284
285 INSERT INTO Employees
286 VALUES
287 ('1','Jack','Harris','1423 Main Street', 'Jackson', 'TN'),
288 ('2','Mark','James','432 Seashore Street', 'Nashville', 'TN'),
289 ('3','Alice','Johnson','5235 Highway 495', 'Brentwood', 'TN'),
290 ('4','Morgan','Denton','4634 Bluebird Lane', 'Dickson', 'TN'),
291 ('5','Don','Carleson','62346 Williamsburg Court', 'Nolensville', 'TN')
292
293 INSERT INTO Customers
294 VALUES
295 ('101','Jerry','Algood','333 Bullard Ave', 'Chicago', 'IL'),
296 ('102','John','Williams','2523 First Avenue North', 'Atlanta', 'GA'),
297 ('103','Kristin','Addison','26675 East Hwy 33', 'Dallas', 'TX'),
298 ('104','Megan','Wilson','6786 3rd Ave', 'Memphis', 'TN'),
299 ('105','Janet','Greene','99674 Jackson Square', 'Louisville', 'KY')
300
301 -- Here's the Employees
302 select * from employees
303
304 -- Here's the Customers
305 select * from customers
306
307 -- Here are the combined names from both result sets
308 -- Data types for columns in both queries must be similar !!!
309 select Firstname, Lastname, StreetAddress, City, State
310 from customers
311 UNION
312 select Firstname, Lastname, StreetAddress, City, State
313 from employees
314 order by Lastname, Firstname
315
316 =========================================
317 ---SECTION 6
318 ==========================================
319
320 --JOIN
321
322-- Let's take a look at the two tables we will be using
323
324SELECT TOP 5 * from Production.Product -- There are 508 rows in this table
325
326SELECT TOP 5 * from Production.ProductReview -- There are 4 rows in this table
327
328
329
330-- This query will perform an INNER JOIN
331SELECT p.Name, pr.ProductReviewID
332FROM Production.Product p
333JOIN Production.ProductReview pr
334ON p.ProductID = pr.ProductID
335
336
337-- This query will perform a LEFT JOIN
338SELECT p.Name, pr.ProductReviewID
339FROM Production.Product p
340LEFT OUTER JOIN Production.ProductReview pr
341ON p.ProductID = pr.ProductID
342
343
344-- This query will perform a RIGHT JOIN
345SELECT p.Name, pr.ProductReviewID
346FROM Production.Product p
347RIGHT OUTER JOIN Production.ProductReview pr
348ON p.ProductID = pr.ProductID
349
350
351SELECT p.Name, pr.ProductReviewID
352FROM Production.Product p
353CROSS JOIN Production.ProductReview pr
354
355--JOIN Types
356
357-- Let's look at a simple JOIN example
358-- We want each employee's first name, last name, and email address
359
360-- We will get data from the FirstName and LastName
361-- columns in the Person.Person table
362select top 1 * from person.person
363
364-- We will get data from the EmailAddress column
365-- in the Person.EmailAddress table
366select top 1 * from person.emailaddress
367
368-- Here's the basic structure
369select Person.FirstName, Person.LastName, EmailAddress.EmailAddress
370from Person.Person
371JOIN
372Person.EmailAddress
373ON
374Person.BusinessEntityID = EmailAddress.BusinessEntityID
375where LastName like 'b%'
376order by LastName, FirstName
377
378---- Table Aliasing
379
380-- Here's the basic structure of a simple JOIN query
381select Person.FirstName, Person.LastName, EmailAddress.EmailAddress
382from Person.Person
383JOIN
384Person.EmailAddress
385ON
386Person.BusinessEntityID = EmailAddress.BusinessEntityID
387where LastName like 'b%'
388order by LastName, FirstName
389
390
391-- Table aliasing can be used to reduce keystrokes
392-- and make the query easier to read
393select p.FirstName, p.LastName, e.EmailAddress
394from Person.Person p
395JOIN
396Person.EmailAddress e
397ON
398p.BusinessEntityID = e.BusinessEntityID
399where LastName like 'b%'
400order by LastName, FirstName
401
402================================================
403 --SECTION 7:
404================================================
405
406-- Built-in Functions
407
408-- Let's take a look at the Sales.SalesOrderDetail table
409select * from sales.salesorderdetail
410
411-- Let's look at the LineTotal numbers
412select LineTotal
413from sales.SalesOrderDetail
414
415-- We can use the SUM built-in function to generate
416-- the total of all the LineTotal amounts in the table
417select sum(LineTotal) 'Line Totals'
418from sales.SalesOrderDetail
419
420-- We can use a WHERE clause to only sum the LineTotals
421-- that are greater than 25000
422select sum(LineTotal) 'Line Totals over 25000'
423from sales.SalesOrderDetail
424where LineTotal>25000
425
426-- We can use the COUNT function to count how many LineTotals
427-- are over 25000
428select count(LineTotal) '# Line Totals over 25000'
429from sales.SalesOrderDetail
430where LineTotal>25000
431
432
433-- We can use the MAX function to get the largest LineTotal
434-- that is over 25000
435select max(LineTotal) 'Line Totals'
436from sales.SalesOrderDetail
437where LineTotal>25000
438
439-- The MIN function will return the smallest value over 25000
440select min(LineTotal) 'Line Totals'
441from sales.SalesOrderDetail
442where LineTotal>25000
443
444-- The AVG function will return the average of all values over 25000
445select avg(LineTotal) 'Line Totals'
446from sales.SalesOrderDetail
447where LineTotal>25000
448
449-- The AVG function can also return the average LineTotal of all products
450select avg(LineTotal) 'Line Totals'
451from sales.SalesOrderDetail
452
453 -- GROUP BY Examples
454
455-- Here's the table data we will use
456select * from
457sales.SalesOrderDetail
458
459-- Let's look at the productid's and unit price values
460-- in the salesorderdetail table
461select productid, unitprice
462from sales.SalesOrderDetail
463order by productid
464
465-- We would liek to know the total of the unitprice values
466-- for each productid
467-- BUT, this query will return an error...
468-- Productid returns many rows while sum(unitprice) returns a single row value
469select productid, sum(unitprice)
470from sales.SalesOrderDetail
471
472-- We can use GROUP BY to get the summary values we want
473-- Now, productid is grouped to provide a single row value, and
474-- sum(unitprice) returns a single row value
475select productid, sum(unitprice) 'Totals'
476from sales.SalesOrderDetail
477GROUP BY productid
478ORDER BY productid
479
480-- GROUPING SETS Example
481
482-- NOTE! The CUBE and WITH ROLLUP functionalities will be
483-- removed in future SQL Server versions
484select ProductID,SUM(LineTotal) 'Totals'
485from sales.SalesOrderDetail
486GROUP BY productid WITH CUBE
487
488
489-- This query uses the GROUPING SETS syntax
490-- to create a group on the ProductID column
491select ProductID, SUM(LineTotal) 'Totals'
492from sales.SalesOrderDetail
493GROUP BY
494GROUPING SETS ((ProductID))
495
496
497
498-- We can easily add a total aggregation
499-- using the GROUPING SET syntax and add
500-- a blank set of parenthesis to our grouping set
501select ProductID, SUM(LineTotal) 'Totals'
502from sales.SalesOrderDetail
503GROUP BY
504GROUPING SETS ((ProductID),())
505
506
507--GROUPING SETS Example
508
509-- Let's add another column to our query and see
510-- even more flexibility with GROUPING SETS
511-- This syntax groups by ProductID and CarrierTrackingNumber
512-- and provides a total aggregation
513select ProductID, CarrierTrackingNumber, SUM(LineTotal) 'Totals'
514from sales.SalesOrderDetail
515GROUP BY
516GROUPING SETS ((ProductID),(CarrierTrackingNumber),())
517
518-- This syntax groups by ProductID AND CarrierTrackingNumber
519select ProductID, CarrierTrackingNumber, SUM(LineTotal) 'Totals'
520from sales.SalesOrderDetail
521GROUP BY
522GROUPING SETS ((ProductID,CarrierTrackingNumber),())
523
524
525-- HAVING Examples
526
527-- This query includes all LineTotal amounts
528-- grouped by ProductID
529select ProductID, SUM(LineTotal)
530from sales.salesorderdetail
531group by ProductID
532order by ProductID
533
534
535-- This creates LineTotals grouped by ProductID
536-- but only includes individual LineTotals over 2,000
537select ProductID, SUM(LineTotal)
538from sales.salesorderdetail
539where LineTotal > 2000
540group by ProductID
541order by ProductID
542
543-- This creates LineTotals grouped by ProductID
544-- but only inludes LineTotals over 2,000 in the groups
545-- and only includes ProductID groups that total over 25,000
546select ProductID, SUM(LineTotal)
547from sales.salesorderdetail
548where LineTotal > 2000
549group by ProductID
550having SUM(LineTotal)>25000
551order by ProductID
552
553 ==============================================
554 SECTION 8:
555 ==============================================
556 --DELETE Examples
557
558--Let's use SELECT INTO to create a
559--table to run a few DELETE examples
560SELECT TOP 25 LastName, FirstName INTO DeleteExample
561FROM Person.Person
562
563--Take a look at the new table
564select * from DeleteExample
565
566--Let's run a simple, dangerous DELETE query
567DELETE FROM DeleteExample
568--Oh, no! The message says that 25 rows were deleted!
569--We didn't use a WHERE clause...
570
571--Let's reload our table with data using INSERT SELECT
572INSERT INTO DeleteExample
573SELECT TOP 25 LastName, FirstName FROM Person.Person
574
575--Verify the data was loaded into the table
576select * from DeleteExample
577
578--Now let's use DELETE to remove only the records
579--for Kim Abercrombie
580DELETE FROM DeleteExample
581WHERE LastName='Abercrombie' AND FirstName='Kim'
582
583--A trick for using DELETE without being surprised!
584--Write the DELETE statement as a SELECT statement
585--Execute it and see how many rows are returned...
586--THEN convert it to a DELETE and execute it!
587--
588--We want to DELETE records for Ben Adams
589--So we'll first write the command as a SELECT
590SELECT
591LastName, FirstName
592FROM DeleteExample
593WHERE LastName='Adams' AND FirstName='Ben'
594
595--Looks good, now lets make it a DELETE statement
596--Highlight SELECT LastName,FirstName and change it to the following
597--then execute it
598DELETE
599FROM DeleteExample
600WHERE LastName='Adams' AND FirstName='Ben'
601
602
603-- INSERT Examples
604
605-- Let's create a table for our inserts
606-- The SID column will generate an identity number automatically
607-- the State column has a default value of 'TN'
608CREATE TABLE InsertExample
609(
610 SID int IDENTITY,
611 FirstName varchar(25) not null,
612 LastName varchar(25) not null,
613 City varchar(12) null,
614 State char(2) DEFAULT ('TN')
615)
616
617-- We will insert data into FirstName, LastName, and City
618-- The state column will be assigned its default value of 'TN'
619-- The SID column will generate its own value
620INSERT INTO InsertExample
621(FirstName, LastName, City)
622VALUES
623('Bob','Jackson','Nashville')
624
625select * from InsertExample
626
627-- We can provide a value for all columns and omit
628-- the column names
629INSERT INTO InsertExample
630VALUES
631('Jack','Smith','Atlanta','GA')
632
633-- We can insert row data in a different column order
634-- by positionally matching the values and the columns
635INSERT INTO InsertExample
636(LastName,City,FirstName,State)
637VALUES
638('Jones','Dallas','Mary','TX')
639
640
641-- If we omit a column that allow nulls, it will receive
642-- a NULL value
643INSERT INTO InsertExample
644(LastName,FirstName,State)
645VALUES
646('Smith','Jennifer','FL')
647
648
649-- If we omit a column that does not allow nulls
650-- we will receive an error
651INSERT INTO InsertExample
652(LastName,City,State)
653VALUES
654('Smith','Miami','FL')
655
656-- We can also insert multiple rows using a single INSERT statement
657INSERT INTO InsertExample
658(LastName,FirstName,City)
659VALUES
660('Jones','Jack','Nashville'),
661('Marly','Harold','Paris'),
662('Linley','Morgan','Nashville'),
663('Sims','Jill','Franklin')
664
665
666select * from InsertExample
667
668
669-- INSERT SELECT Example
670
671-- Let's create a table for our INSERT SELECT statement
672-- The SID column will generate an identity number automatically
673-- the State column has a default value of 'TN'
674CREATE TABLE InsertSelectExample
675(
676 SID int IDENTITY,
677 FirstName varchar(25) not null,
678 LastName varchar(25) not null,
679 Emailaddress varchar(50),
680)
681
682
683-- Now, let's INSERT data using INSERT SELECT
684INSERT INTO InsertSelectExample
685 SELECT p.Firstname, p.Lastname, e.emailaddress
686 FROM person.person p
687 JOIN person.emailaddress e
688 ON p.businessentityid = e.businessentityid
689
690-- Let's check the contents of the InsertSelectExample table
691select * from InsertSelectExample
692
693-- SELECT INTO Example
694
695-- Here's a look at the two tables we will use for our source data
696select top 1 * from person.person
697select top 1 * from person.emailaddress
698
699
700-- Here is our SELECT INTO statement
701SELECT p.FirstName, p.Lastname, e.EmailAddress
702INTO EmailList
703from person.person p
704JOIN
705person.emailaddress e
706on
707p.BusinessEntityID=e.BusinessEntityID
708
709-- Here is the data in our new table
710select * from EmailList
711
712-- Updating Data
713
714--Let's take a look at the table we will UPDATE
715select * from Person.Address
716
717--A simple UPDATE
718--NOTE what happens when you run it!
719UPDATE Person.Address
720SET ModifiedDate = GETDATE();
721
722--You should always include a WHERE clause...
723--First, determine which data row you want to update
724select * from Person.Address
725
726--We want to update AddressLine1 where it is currently
727--1226 Shoe St.
728UPDATE Person.Address
729SET AddressLine1='1227 Shoe St.'
730WHERE AddressLine1='1226 Shoe St.'
731
732--Verify the change was made
733select * from Person.Address
734
735--We can UPDATE several columns at once
736--Let's look at the table we are going to UPDATE
737select * from Sales.SalesPerson
738
739--Now let's update multiple columns
740UPDATE Sales.SalesPerson
741SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL
742WHERE TerritoryID=1;
743
744--Look at the table and verify UPDATE occurred
745select * from Sales.SalesPerson
746
747===============================================================
748 --SECTION 9:
749===============================================================
750 -- Common Table Expressions (CTEs)
751
752-- Here's a simple CTE
753-- Retrieves the number of Orders per year from the SalesOrderHeader table
754WITH C AS
755(
756 SELECT YEAR(OrderDate) AS OrderYear, SalesOrderID
757 FROM Sales.SalesOrderHeader
758)
759SELECT OrderYear, COUNT(DISTINCT SalesOrderID) AS NbrCustomers
760FROM C
761GROUP BY orderyear;
762
763
764-- Multiple CTEs can be 'chained' together
765-- Each CTE can refer to the previous CTE
766
767WITH C1 AS
768(
769 SELECT YEAR(OrderDate) OrderYear, SalesOrderID
770 FROM Sales.SalesOrderHeader
771),
772C2 AS
773(
774 SELECT OrderYear, COUNT(DISTINCT SalesOrderID) NbrCustomers
775 FROM C1
776 GROUP BY OrderYear
777)
778SELECT OrderYear, NbrCustomers
779FROM C2
780WHERE NbrCustomers>50
781
782
783 --DATA Masking
784
785--Dynamic Data Masking Demo
786
787-- Let's create an email mask on the EmailAddress
788-- column in the Person.EmailAddress table
789ALTER TABLE Person.EmailAddress
790ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()') --NULL);
791
792-- Create a user named Bob to test our masking and
793-- Grant Bob the SELECT permissions on the Person.EmailAddress table
794CREATE USER Bob WITHOUT LOGIN;
795GRANT SELECT ON Person.EmailAddress TO Bob
796
797
798-- Select data from the Person.EmailAddress table
799-- using Bob's account
800EXECUTE AS USER = 'Bob'
801select * from person.emailaddress
802REVERT;
803
804
805-- Now, let's take a look at using a custom string mask
806-- We'll mask the NatitonalIDNumber column in the
807-- HumanResources.Employee table
808
809-- Here's what the NationalIDNumber column looks like unmasked
810-- This query will be executed by my MLong user account with
811-- sysadmin privileges
812select * from HumanResources.Employee
813
814-- Let's add the custom string mask to the NationalIDNumber column
815-- by performing an ALTER TABLE command on the table
816ALTER TABLE HumanResources.Employee
817ALTER COLUMN NationalIDNumber ADD MASKED WITH (FUNCTION = 'partial(1,"xxx",4)')
818
819-- Grant the SELECT permission to our test user Bob
820GRANT SELECT ON HumanResources.Employee TO Bob
821
822-- Execute a SELECT statement as Bob to see the masking
823EXECUTE AS USER = 'Bob'
824select * from HumanResources.Employee
825REVERT;
826
827--Store Procedure
828
829-- Using input parameters with stored procedures
830
831-- Add an input parameter requires the use of an input variable
832-- We add the input parameter BEFORE the AS keyword...
833-- We can provide a default value for the input parameter...
834-- If a parameter is not provided when GetPhone is called,
835-- the default value is called
836-- We will modify the WHERE clause to use LIKE and new variable
837ALTER PROC GetPhone
838@lastname varchar(50)='%' --Note that the default value is optional
839AS
840select p.LastName + ', ' + p.FirstName Name, pp.PhoneNumber
841from person.person p
842join
843person.PersonPhone pp
844on
845p.BusinessEntityID=pp.BusinessEntityID
846where LastName LIKE @lastname
847
848
849--Hightlight 'GetPhone' and include the input parameter, then execute
850--The result of the code in the stored procedure will be returned
851GetPhone 'Jones'
852
853--What about more than one input parameter?
854ALTER PROC GetPhone
855@lastname varchar(50),
856@firstname varchar(50)
857AS
858select p.LastName + ', ' + p.FirstName Name, pp.PhoneNumber
859from person.person p
860join
861person.PersonPhone pp
862on
863p.BusinessEntityID=pp.BusinessEntityID
864where LastName=@lastname and FirstName=@firstname
865
866GetPhone 'Adams', 'Alex'
867
868--STORED PROCEDURE OUTPUT Parameter
869
870--Let's create a stored procedure that
871--utilizes an OUTPUT parameter
872CREATE PROC SPoutput
873@lname varchar(40),
874@numrows int=0 OUTPUT -- This is the output parameter
875as
876select LastName from Person.Person
877where LastName like @lname
878set @numrows=@@rowcount
879
880--Now, execute the stored procedure...
881Declare @retrows int
882exec SPoutput 'B%', @numrows=@retrows OUTPUT;
883select @retrows as 'Rows'
884
885-- ##############################################################
886
887--Let's use an OUTPUT parameter in a little different manner
888CREATE PROC GetNatID
889 @BusEntityID INT,
890 @NationalID INT OUTPUT
891AS
892BEGIN
893 SELECT @NationalID = NationalIDNumber
894 FROM HumanResources.Employee
895 WHERE BusinessEntityID = @BusEntityID
896END
897
898DECLARE @m int
899EXEC GetNatID 5,@m OUTPUT
900Print ' '
901Print 'The NationalID number is: '
902Print @m
903
904
905select * from HumanResources.Employee
906
907
908--STORED PROCEDURE Examples
909-- A simple stored procedure
910
911-- This is a query that we run often
912select p.LastName + ', ' + p.FirstName Name, pp.PhoneNumber
913from person.person p
914join
915person.PersonPhone pp
916on
917p.BusinessEntityID=pp.BusinessEntityID
918order by Lastname, Firstname
919
920
921-- Let's create a stored procedure that will
922-- return this information
923CREATE PROC GetPhone
924AS
925select p.LastName + ', ' + p.FirstName Name, pp.PhoneNumber
926from person.person p
927join
928person.PersonPhone pp
929on
930p.BusinessEntityID=pp.BusinessEntityID
931order by Lastname, Firstname
932
933-- Table Variables Example
934
935--NOTE!
936--To run this example, make sure that nothing is highlighted
937--and click the Execute button
938--All this code must run sequentially and be executed together
939
940--First, let's create our table variable
941DECLARE @MTL as TABLE
942(FirstName varchar(40),
943 LastName varchar(40),
944 Orders int,
945 OrderTot int)
946
947 --Now, let's load data into the table variable
948 --We'll use an INSERT SELECT statement and do some grouping
949 Insert into @MTL
950 (FirstName,LastName,Orders,OrderTot)
951 select p.FirstName, p.LastName, COUNT(*), SUM(soh.TotalDue)
952 from person.person p
953 join
954 sales.SalesOrderHeader soh
955 on
956 p.BusinessEntityID=soh.SalesPersonID
957 group by Firstname, Lastname
958
959
960--Now, let's see the contents of the @MTL table variable
961--after the data has been inserted
962 select * from @MTL
963 order by orders desc
964
965 =============================================================
966 --SECTION 10:
967 =============================================================
968
969 -- Scalar UDF Example
970
971-- We will create a function to provide a more
972-- descriptive result set for the EmailPromotion column
973-- in the Person.EmailAddress table
974SELECT * FROM Person.Person
975
976-- Create a function named fnEmailPromo
977-- It will accept EmailAddress as an input and
978-- return a string value based on the int value
979-- being passed in
980-- We will use the function in a select statement
981CREATE FUNCTION fnEmailPromo (@promo int)
982RETURNS varchar(20)
983AS
984BEGIN
985DECLARE @ret_promo varchar(20)
986select @ret_promo=
987 CASE
988 WHEN @promo=0 then 'None'
989 WHEN @promo=1 then 'Local'
990 WHEN @promo=2 THEN 'National'
991 ELSE 'Not designated'
992 END
993 RETURN @ret_promo
994END
995
996-- Now, let's user our new fnEmailPromo function
997-- Note that to call the new function we must include
998-- dbo. in the name
999SELECT FirstName + ' ' + Lastname Customer, dbo.fnEmailPromo(EmailPromotion) 'Promotion Type'
1000FROM Person.Person
1001
1002-- TVF Example
1003
1004-- Here are the three tables we will be getting our data from
1005select top 1 * from person.person
1006select top 1 * from person.emailaddress
1007select top 1 * from humanresources.employee
1008
1009-- Our TVF will return the names and email addresses of our
1010-- employees based on their gender
1011-- Let's create our TVF
1012
1013CREATE FUNCTION dbo.fnGetEmail(@gender char(1))
1014RETURNS TABLE
1015AS
1016RETURN
1017 SELECT p.FirstName, p.Lastname,e.EmailAddress,em.Gender
1018 FROM person.person p
1019 JOIN person.EmailAddress e on p.BusinessEntityID=e.BusinessEntityID
1020 JOIN HumanResources.Employee em on e.BusinessEntityID=em.BusinessEntityID
1021 WHERE em.Gender=@gender
1022
1023-- Let's use our TVF
1024SELECT * FROM dbo.fnGetEmail('m')
1025
1026
1027-- We can add a WHERE clause to get more creative with the results
1028SELECT * FROM dbo.fnGetEmail('m')
1029WHERE LastName LIKE 'D%'
1030
1031====================================================================
1032 --SECTION 11:
1033====================================================================
1034
1035 --SUBQURIES
1036
1037 -- Scalar Subquery Example
1038
1039-- We want to retrieve the largest order amount
1040-- from the SalesOrderHeader table
1041SELECT * from Sales.SalesOrderHeader
1042
1043
1044-- Could we just use the MAX() function to find it?
1045SELECT MAX(TotalDue)
1046FROM Sales.SalesOrderHeader
1047
1048-- MAX(TotalDue) gives us the largest order, but what is the
1049-- SalesOrderID?
1050-- This query will produce an error and we'll have to add group by
1051SELECT SalesOrderID,MAX(TotalDue)
1052FROM Sales.SalesOrderHeader
1053
1054-- We can add GROUP BY, and use ORDER BY DESC to see the
1055-- largest order but this returns too many results
1056SELECT SalesOrderID,MAX(TotalDue) MaxOrder
1057FROM Sales.SalesOrderHeader
1058GROUP BY SalesOrderID
1059ORDER BY MaxOrder DESC
1060
1061-- A scalar subquery makes this much easier
1062SELECT SalesOrderID, TotalDue
1063FROM Sales.SalesOrderHeader
1064WHERE TotalDue = (SELECT MAX(TotalDue) FROM Sales.SalesOrderHeader);
1065
1066 -- Multi-vaued query examples
1067
1068-- Let's start off with a simple multi-valued query
1069-- that returns a list of values
1070-- We need a list of employees who were born after 1972
1071-- Here' where we can get the data...
1072SELECT TOP 1 * from Person.Person
1073SELECT TOP 1 * from HumanResources.Employee
1074
1075-- We can use a subquery to pull the list of BusinesEntityID's
1076-- for every BirthDate greater than 1972 in the Employee table
1077-- and use that list with the IN keyword to get the Employee names
1078-- from the Person table
1079
1080SELECT Firstname + ' ' + Lastname Employee
1081FROM Person.Person
1082WHERE Person.BusinessEntityID IN
1083 (SELECT BusinessEntityID
1084 FROM HumanResources.Employee
1085 WHERE Year(BirthDate)>1972)
1086ORDER BY LastName, FirstName
1087
1088
1089-- Now, let's use a table-value subquery...also known as a table expression
1090-- to find the last SalesOrderID for each year
1091SELECT OrderYear, MAX(SalesOrderID) LastOrderID
1092FROM
1093 (SELECT SalesOrderID, Year(OrderDate) OrderYear -- Run this inner query separately
1094 FROM Sales.SalesOrderHeader)as D -- to see the table being returned
1095GROUP BY OrderYear
1096ORDER BY OrderYear
1097
1098
1099--Correlated Subquery
1100
1101--First let's look at a join query
1102SELECT DISTINCT p.BusinessEntityID, p.FirstName + ' ' + p.LastName Name
1103FROM
1104Person.Person p
1105JOIN
1106sales.SalesOrderHeader s
1107ON
1108p.BusinessEntityID=s.SalesPersonID
1109WHERE s.TotalDue>150000
1110
1111--Now, let's write this as a correlated subquery
1112--NOTE that the inner query runs first and locates a matching record
1113--The outer query runs using the record found, then the inner query
1114--runs again searching for another match...until all records are found
1115SELECT p.BusinessEntityID, FirstName + ' ' + LastName Salesperson FROM Person.Person p
1116WHERE
1117EXISTS
1118 (SELECT s.SalesPersonID FROM Sales.Salesorderheader s
1119 WHERE TotalDue>150000
1120 and p.BusinessEntityID=s.SalesPersonID)
1121
1122================================================================
1123 --SUMMARY: Practise also on tipics not covered here
1124================================================================