· 8 years ago · Apr 19, 2018, 03:56 AM
1--AdventureWorksLT sample database
2
3
4/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
5
6
7--1-Introduction to T-SQL
8
9
10--Demo 1-Select
11SELECT Name, StandardCost, ListPrice
12FROM SalesLT.Product;
13
14SELECT Name, ListPrice - StandardCost
15FROM SalesLT.Product;
16
17SELECT Name, ListPrice - StandardCost AS Markup
18FROM SalesLT.Product;
19
20SELECT ProductNumber, Color, Size, Color + ', ' + Size AS ProductDetails
21FROM SalesLT.Product;
22
23SELECT ProductID + ': ' + Name
24FROM SalesLT.Product;
25
26
27--Demo 2-Converting Data Types
28SELECT CAST(ProductID AS varchar(5)) + ': ' + Name AS ProductName
29FROM SalesLT.Product;
30
31SELECT CONVERT(varchar(5), ProductID) + ': ' + Name AS ProductName
32FROM SalesLT.Product;
33
34SELECT SellStartDate,
35 CONVERT(nvarchar(30), SellStartDate) AS ConvertedDate,
36 CONVERT(nvarchar(30), SellStartDate, 126) AS ISO8601FormatDate
37FROM SalesLT.Product;
38
39SELECT Name, CAST (Size AS Integer) AS NumericSize
40FROM SalesLT.Product; --(note error - some sizes are incompatible)
41
42SELECT Name, TRY_CAST (Size AS Integer) AS NumericSize
43FROM SalesLT.Product; --(note incompatible sizes are returned as NULL)
44
45
46--Demo 3-NULLs and Expressions
47SELECT Name, ISNULL(TRY_CAST(Size AS Integer),0) AS NumericSize
48FROM SalesLT.Product;
49
50SELECT ProductNumber, ISNULL(Color, '') + ', ' + ISNULL(Size, '') AS ProductDetails
51FROM SalesLT.Product;
52
53SELECT Name, NULLIF(Color, 'Multi') AS SingleColor
54FROM SalesLT.Product;
55
56SELECT Name, COALESCE(DiscontinuedDate, SellEndDate, SellStartDate) AS FirstNonNullDate
57FROM SalesLT.Product;
58
59--Searched case
60SELECT Name,
61 CASE
62 WHEN SellEndDate IS NULL THEN 'On Sale'
63 ELSE 'Discontinued'
64 END AS SalesStatus
65FROM SalesLT.Product;
66
67--Simple case
68SELECT Name,
69 CASE Size
70 WHEN 'S' THEN 'Small'
71 WHEN 'M' THEN 'Medium'
72 WHEN 'L' THEN 'Large'
73 WHEN 'XL' THEN 'Extra-Large'
74 ELSE ISNULL(Size, 'n/a')
75 END AS ProductSize
76FROM SalesLT.Product;
77
78
79/*
80Challenge 1: Retrieve Customer Data
81Adventure Works Cycles sells directly to retailers, who then sell products to consumers. Each retailer that is an Adventure Works customer has provided a named contact for all communication from Adventure Works. The sales manager at Adventure Works has asked you to generate some reports containing details of the company's customers to support a direct sales campaign.
82
831. Retrieve customer details
84Familiarize yourself with the Customer table by writing a Transact-SQL query that retrieves all columns for all customers.
85
862. Retrieve customer name data
87Create a list of all customer contact names that includes the title, first name, middle name (if any), last name, and suffix (if any) of all customers.
88
893. Retrieve customer names and phone numbers
90Each customer has an assigned salesperson. You must write a query to create a call sheet that lists:
91• The salesperson
92• A column named CustomerName that displays how the customer contact should be greeted (for example, "Mr Smith")
93• The customer's phone number.
94
95
96Challenge 2: Retrieve Customer and Sales Data
97As you continue to work with the Adventure Works customer data, you must create queries for reports that have been requested by the sales team.
98
991. Retrieve a list of customer companies
100You have been asked to provide a list of all customer companies in the format <Customer ID> : <Company Name> - for example, 78: Preferred Bikes.
101
1022. Retrieve a list of sales order revisions
103The SalesLT.SalesOrderHeader table contains records of sales orders. You have been asked to retrieve data for a report that shows:
104• The sales order number and revision number in the format <Order Number> (<Revision>) – for example SO71774 (2).
105• The order date converted to ANSI standard format (yyyy.mm.dd – for example 2015.01.31).
106
107
108Challenge 3: Retrieve Customer Contact Details
109Some records in the database include missing or unknown values that are returned as NULL. You must create some queries that handle these NULL fields appropriately.
110
1111. Retrieve customer contact names with middle names if known
112You have been asked to write a query that returns a list of customer names. The list must consist of a single field in the format <first name> <last name> (for example Keith Harris) if the middle name is unknown, or <first name> <middle name> <last name> (for example Jane M. Gates) if a middle name is stored in the database.
113
1142. Retrieve primary contact details
115Customers may provide adventure Works with an email address, a phone number, or both. If an email address is available, then it should be used as the primary contact method; if not, then the phone number should be used. You must write a query that returns a list of customer IDs in one column, and a second column named PrimaryContact that contains the email address if known, and otherwise the phone number.
116
117IMPORTANT: The sample data in AdventureWorksLT may not have customer records without an email address. Therefore, to verify that your query works as expected, run the following UPDATE statement to remove some existing email addresses before creating your query.
118UPDATE SalesLT.Customer
119SET EmailAddress = NULL
120WHERE CustomerID % 7 = 1;
121
1223. Retrieve shipping status
123You have been asked to create a query that returns a list of sales order IDs and order dates with a column named ShippingStatus that contains the text "Shipped" for orders with a known ship date, and "Awaiting Shipment" for orders with no ship date.
124IMPORTANT: In the sample data provided in AdventureWorksLT, there are no sales order header records without a ship date. Therefore, to verify that your query works as expected, run the following UPDATE statement to remove some existing ship dates before creating your query.
125UPDATE SalesLT.SalesOrderHeader
126SET ShipDate = NULL
127WHERE SalesOrderID > 71899;
128*/
129
130
131--Solution 1-Customer Data
132
133--Display all columns for all customers
134SELECT * FROM SalesLT.Customer;
135
136--Display customer name fields
137SELECT Title, FirstName, MiddleName, LastName, Suffix
138FROM SalesLT.Customer;
139
140--Display title and last name with phone number
141SELECT Salesperson, Title + ' ' + LastName AS CustomerName, Phone
142FROM SalesLT.Customer;
143
144
145--Solution 2-Customer and Sales Data
146--Customer Companies
147SELECT CAST(CustomerID AS varchar) + ': ' + CompanyName AS CustomerCompany
148FROM SalesLT.Customer;
149
150--Sales Order Revisions
151SELECT SalesOrderNumber + ' (' + STR(RevisionNumber, 1) + ')' AS OrderRevision,
152 CONVERT(nvarchar(30), OrderDate, 102) AS OrderDate
153FROM SalesLT.SalesOrderHeader;
154
155
156--Solution 3-Customer Contact Details
157--Get middle names if known
158SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '')+ LastName AS CustomerName
159FROM SalesLT.Customer;
160
161--Get primary contact details
162UPDATE SalesLT.Customer
163SET EmailAddress = NULL
164WHERE CustomerID % 7 = 1;
165
166SELECT CustomerID, COALESCE(EmailAddress, Phone) AS PrimaryContact
167FROM SalesLT.Customer;
168
169--Get shipping status
170UPDATE SalesLT.SalesOrderHeader
171SET ShipDate = NULL
172WHERE SalesOrderID > 71899;
173
174SELECT SalesOrderID, OrderDate,
175 CASE
176 WHEN ShipDate IS NULL THEN 'Awaiting Shipment'
177 ELSE 'Shipped'
178 END AS ShippingStatus
179FROM SalesLT.SalesOrderHeader;
180
181
182/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
183
184
185--2-Querying Tables with SELECT
186
187
188--Demo 1-Eliminating Duplicates and Sorting Results
189--Display a list of product colors
190SELECT Color FROM SalesLT.Product;
191
192--Display a list of product colors with the word 'None' if the value is null
193SELECT DISTINCT ISNULL(Color, 'None') AS Color FROM SalesLT.Product;
194
195--Display a list of product colors with the word 'None' if the value is null sorted by color
196SELECT DISTINCT ISNULL(Color, 'None') AS Color FROM SalesLT.Product ORDER BY Color;
197
198--Display a list of product colors with the word 'None' if the value is null and a dash if the size is null sorted by color
199SELECT DISTINCT ISNULL(Color, 'None') AS Color, ISNULL(Size, '-') AS Size FROM SalesLT.Product ORDER BY Color;
200
201
202--Display the top 100 products by list price
203SELECT TOP 100 Name, ListPrice FROM SalesLT.Product ORDER BY ListPrice DESC;
204
205--Display the first ten products by product number
206SELECT Name, ListPrice FROM SalesLT.Product ORDER BY ProductNumber OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
207
208--Display the next ten products by product number
209SELECT Name, ListPrice FROM SalesLT.Product ORDER BY ProductNumber OFFSET 10 ROWS FETCH FIRST 10 ROW ONLY;
210
211
212--List information about product model 6
213SELECT Name, Color, Size FROM SalesLT.Product WHERE ProductModelID = 6;
214
215--List information about products that have a product number beginning FR
216SELECT productnumber,Name, ListPrice FROM SalesLT.Product WHERE ProductNumber LIKE 'FR%';
217
218--Filter the previous query to ensure that the product number contains two sets of two didgets
219SELECT Name, ListPrice FROM SalesLT.Product WHERE ProductNumber LIKE 'FR-_[0-9][0-9]_-[0-9][0-9]';
220
221--Find products that have no sell end date
222SELECT Name FROM SalesLT.Product WHERE SellEndDate IS NOT NULL;
223
224--Find products that have a sell end date in 2006
225SELECT Name FROM SalesLT.Product WHERE SellEndDate BETWEEN '2006/1/1' AND '2006/12/31';
226
227--Find products that have a category ID of 5, 6, or 7.
228SELECT ProductCategoryID, Name, ListPrice FROM SalesLT.Product WHERE ProductCategoryID IN (5, 6, 7);
229
230--Find products that have a category ID of 5, 6, or 7 and have a sell end date
231SELECT ProductCategoryID, Name, ListPrice, SellEndDate FROM SalesLT.Product WHERE ProductCategoryID IN (5, 6, 7) AND SellEndDate IS NULL;
232
233--Select products that have a category ID of 5, 6, or 7 and a product number that begins FR
234SELECT Name, ProductCategoryID, ProductNumber FROM SalesLT.Product WHERE ProductNumber LIKE 'FR%' OR ProductCategoryID IN (5,6,7);
235
236
237--Demo 2-Filtering with Predicates
238--List information about product model 6
239SELECT Name, Color, Size FROM SalesLT.Product WHERE ProductModelID = 6;
240
241--List information about products that have a product number beginning FR
242SELECT productnumber,Name, ListPrice FROM SalesLT.Product WHERE ProductNumber LIKE 'FR%';
243
244--Filter the previous query to ensure that the product number contains two sets of two didgets
245SELECT Name, ListPrice FROM SalesLT.Product WHERE ProductNumber LIKE 'FR-_[0-9][0-9]_-[0-9][0-9]';
246
247--Find products that have no sell end date
248SELECT Name FROM SalesLT.Product WHERE SellEndDate IS NOT NULL;
249
250--Find products that have a sell end date in 2006
251SELECT Name FROM SalesLT.Product WHERE SellEndDate BETWEEN '2006/1/1' AND '2006/12/31';
252
253--Find products that have a category ID of 5, 6, or 7.
254SELECT ProductCategoryID, Name, ListPrice FROM SalesLT.Product WHERE ProductCategoryID IN (5, 6, 7);
255
256--Find products that have a category ID of 5, 6, or 7 and have a sell end date
257SELECT ProductCategoryID, Name, ListPrice, SellEndDate FROM SalesLT.Product WHERE ProductCategoryID IN (5, 6, 7) AND SellEndDate IS NULL;
258
259--Select products that have a category ID of 5, 6, or 7 and a product number that begins FR
260SELECT Name, ProductCategoryID, ProductNumber FROM SalesLT.Product WHERE ProductNumber LIKE 'FR%' OR ProductCategoryID IN (5,6,7);
261
262
263/*
264Challenge 1: Retrieve Data for Transportation Reports
265The logistics manager at Adventure Works has asked you to generate some reports containing details of the company's customers to help to reduce transportation costs.
2661. Retrieve a list of cities
267Initially, you need to produce a list of all of you customers' locations. Write a Transact-SQL query that queries the Address table and retrieves all values for City and StateProvince, removing duplicates.
2682. Retrieve the heaviest products
269Transportation costs are increasing and you need to identify the heaviest products. Retrieve the names of the top ten percent of products by weight.
2703. Retrieve the heaviest 100 products not including the heaviest ten
271The heaviest ten products are transported by a specialist carrier, therefore you need to modify the previous query to list the heaviest 100 products not including the heaviest ten.
272Challenge 2: Retrieve Product Data
273The Production Manager at Adventure Works would like you to create some reports listing details of the products that you sell.
2741. Retrieve product details for product model 1
275Initially, you need to find the names, colors, and sizes of the products with a product model ID 1.
2762. Filter products by color and size
277Retrieve the product number and name of the products that have a color of 'black', 'red', or 'white' and a size of 'S' or 'M'.
2783. Filter products by product number
279Retrieve the product number, name, and list price of products whose product number begins 'BK-'.
2804. Retrieve specific products by product number
281Modify your previous query to retrieve the product number, name, and list price of products whose product number begins 'BK-' followed by any character other than 'R', and ends with a '-' followed by any two numerals.
282*/
283
284
285--Solution 1-Transportation Reports
286--Retrieve City List
287SELECT DISTINCT City, StateProvince
288FROM SalesLT.Address
289
290--Retrieve Heaviest Products
291SELECT TOP 10 PERCENT Name FROM SalesLT.Product ORDER BY Weight DESC;
292
293--Retrieve the Heaviest 100 Products Not Including the Heaviest Ten
294SELECT Name FROM SalesLT.Product ORDER BY Weight DESC
295OFFSET 10 ROWS FETCH NEXT 100 ROWS ONLY;
296
297
298--Solution 2-Product Data
299--Retrieve Product Details
300SELECT Name, Color, Size
301FROM SalesLT.Product
302WHERE ProductModelID = 1;
303
304--Retrieve Products by Color and Size
305SELECT ProductNumber, Name
306FROM SalesLT.Product
307WHERE Color IN ('Black','Red','White') and Size IN ('S','M');
308
309--Retrieve Products by Product Number
310SELECT ProductNumber, Name, ListPrice
311FROM SalesLT.Product
312WHERE ProductNumber LIKE 'BK-%';
313
314--Retrieve Specific Products by Product Number
315SELECT ProductNumber, Name, ListPrice
316FROM SalesLT.Product
317WHERE ProductNumber LIKE 'BK-[^R]%-[0-9][0-9]';
318
319
320/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
321
322
323--3-Querying Multiple Tables With Joins
324
325
326--Demo 1-Inner Joins
327--Basic inner join
328SELECT SalesLT.Product.Name As ProductName, SalesLT.ProductCategory.Name AS Category
329FROM SalesLT.Product
330INNER JOIN SalesLT.ProductCategory
331ON SalesLT.Product.ProductCategoryID = SalesLT.ProductCategory.ProductCategoryID;
332
333--Table aliases
334SELECT p.Name As ProductName, c.Name AS Category
335FROM SalesLT.Product AS p
336JOIN SalesLT.ProductCategory As c
337ON p.ProductCategoryID = c.ProductCategoryID;
338
339--Joining more than 2 tables
340SELECT oh.OrderDate, oh.SalesOrderNumber, p.Name As ProductName, od.OrderQty, od.UnitPrice, od.LineTotal
341FROM SalesLT.SalesOrderHeader AS oh
342JOIN SalesLT.SalesOrderDetail AS od
343ON od.SalesOrderID = oh.SalesOrderID
344JOIN SalesLT.Product AS p
345ON od.ProductID = p.ProductID
346ORDER BY oh.OrderDate, oh.SalesOrderID, od.SalesOrderDetailID;
347
348--Multiple join predicates
349SELECT oh.OrderDate, oh.SalesOrderNumber, p.Name As ProductName, od.OrderQty, od.UnitPrice, od.LineTotal
350FROM SalesLT.SalesOrderHeader AS oh
351JOIN SalesLT.SalesOrderDetail AS od
352ON od.SalesOrderID = oh.SalesOrderID
353JOIN SalesLT.Product AS p
354ON od.ProductID = p.ProductID AND od.UnitPrice = p.ListPrice --Note multiple predicates
355ORDER BY oh.OrderDate, oh.SalesOrderID, od.SalesOrderDetailID;
356
357
358--Demo 2-Outer Joins
359--Get all customers, with sales orders for those who've bought anything
360SELECT c.FirstName, c.LastName, oh.SalesOrderNumber
361FROM SalesLT.Customer AS c
362LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
363ON c.CustomerID = oh.CustomerID
364ORDER BY c.CustomerID;
365
366--Return only customers who haven't purchased anything
367SELECT c.FirstName, c.LastName, oh.SalesOrderNumber
368FROM SalesLT.Customer AS c
369LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
370ON c.CustomerID = oh.CustomerID
371WHERE oh.SalesOrderNumber IS NULL
372ORDER BY c.CustomerID;
373
374
375--More than 2 tables
376SELECT p.Name As ProductName, oh.SalesOrderNumber
377FROM SalesLT.Product AS p
378LEFT JOIN SalesLT.SalesOrderDetail AS od
379ON p.ProductID = od.ProductID
380LEFT JOIN SalesLT.SalesOrderHeader AS oh --Additional tables added to the right must also use a left join
381ON od.SalesOrderID = oh.SalesOrderID
382ORDER BY p.ProductID;
383
384
385SELECT p.Name As ProductName, c.Name AS Category, oh.SalesOrderNumber
386FROM SalesLT.Product AS p
387LEFT OUTER JOIN SalesLT.SalesOrderDetail AS od
388ON p.ProductID = od.ProductID
389LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
390ON od.SalesOrderID = oh.SalesOrderID
391INNER JOIN SalesLT.ProductCategory AS c --Added to the left, so can use inner join
392ON p.ProductCategoryID = c.ProductCategoryID
393ORDER BY p.ProductID;
394
395
396--Demo 3-Cross Join
397--Call each customer once per product
398SELECT p.Name, c.FirstName, c.LastName, c.Phone
399FROM SalesLT.Product as p
400CROSS JOIN SalesLT.Customer as c;
401
402
403--Demo 4-Self Join
404--note there's no employee table, so we'll create one for this example
405CREATE TABLE SalesLT.Employee
406(EmployeeID int IDENTITY PRIMARY KEY,
407EmployeeName nvarchar(256),
408ManagerID int);
409GO
410--Get salesperson from Customer table and generate managers
411INSERT INTO SalesLT.Employee (EmployeeName, ManagerID)
412SELECT DISTINCT Salesperson, NULLIF(CAST(RIGHT(SalesPerson, 1) as INT), 0)
413FROM SalesLT.Customer;
414GO
415UPDATE SalesLT.Employee
416SET ManagerID = (SELECT MIN(EmployeeID) FROM SalesLT.Employee WHERE ManagerID IS NULL)
417WHERE ManagerID IS NULL
418AND EmployeeID > (SELECT MIN(EmployeeID) FROM SalesLT.Employee WHERE ManagerID IS NULL);
419GO
420
421--Here's the actual self-join demo
422SELECT e.EmployeeName, m.EmployeeName AS ManagerName
423FROM SalesLT.Employee AS e
424LEFT JOIN SalesLT.Employee AS m
425ON e.ManagerID = m.EmployeeID
426ORDER BY e.ManagerID;
427
428
429/*
430Challenge 1: Generate Invoice Reports
431Adventure Works Cycles sells directly to retailers, who must be invoiced for their orders. You have been tasked with writing a query to generate a list of invoices to be sent to customers.
4321. Retrieve customer orders
433As an initial step towards generating the invoice report, write a query that returns the company name from the SalesLT.Customer table, and the sales order ID and total due from the SalesLT.SalesOrderHeader table.
4342. Retrieve customer orders with addresses
435Extend your customer orders query to include the Main Office address for each customer, including the full street address, city, state or province, postal code, and country or region
436Tip: Note that each customer can have multiple addressees in the SalesLT.Address table, so the database developer has created the SalesLT.CustomerAddress table to enable a many-to-many relationship between customers and addresses. Your query will need to include both of these tables, and should filter the join to SalesLT.CustomerAddress so that only Main Office addresses are included.
437Challenge 2: Retrieve Sales Data
438As you continue to work with the Adventure Works customer and sales data, you must create queries for reports that have been requested by the sales team.
4391. Retrieve a list of all customers and their orders
440The sales manager wants a list of all customer companies and their contacts (first name and last name), showing the sales order ID and total due for each order they have placed. Customers who have not placed any orders should be included at the bottom of the list with NULL values for the order ID and total due.
4412. Retrieve a list of customers with no address
442A sales employee has noticed that Adventure Works does not have address information for all customers. You must write a query that returns a list of customer IDs, company names, contact names (first name and last name), and phone numbers for customers with no address stored in the database.
4433. Retrieve a list of customers and products without orders
444Some customers have never placed orders, and some products have never been ordered. Create a query that returns a column of customer IDs for customers who have never placed an order, and a column of product IDs for products that have never been ordered. Each row with a customer ID should have a NULL product ID (because the customer has never ordered a product) and each row with a product ID should have a NULL customer ID (because the product has never been ordered by a customer).
445*/
446
447
448--Solution 1-Invoice Reports
449--Customer Orders
450SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDue
451FROM SalesLT.Customer AS c
452JOIN SalesLT.SalesOrderHeader AS oh
453ON oh.CustomerID = c.CustomerID;
454
455--Customer Orders with Addresses
456SELECT c.CompanyName, a.AddressLine1, ISNULL(a.AddressLine2, '') AS AddressLine2,
457 a.City, a.StateProvince, a.PostalCode, a.CountryRegion, oh.SalesOrderID, oh.TotalDue
458FROM SalesLT.Customer AS c
459JOIN SalesLT.SalesOrderHeader AS oh
460ON oh.CustomerID = c.CustomerID
461JOIN SalesLT.CustomerAddress AS ca
462ON c.CustomerID = ca.CustomerID AND AddressType = 'Main Office'
463JOIN SalesLT.Address AS a
464ON ca.AddressID = a.AddressID;
465
466
467--Solution 2-Sales Reports
468--All customers and their orders
469SELECT c.CompanyName, c.FirstName, c.LastName, oh.SalesOrderID, oh.TotalDue
470FROM SalesLT.Customer AS c
471LEFT JOIN SalesLT.SalesOrderHeader AS oh
472ON c.CustomerID = oh.CustomerID
473ORDER BY oh.SalesOrderID DESC;
474
475--Customers with no address
476SELECT c.CompanyName, c.FirstName, c.LastName, c.Phone
477FROM SalesLT.Customer AS c
478LEFT JOIN SalesLT.CustomerAddress AS ca
479ON c.CustomerID = ca.CustomerID
480WHERE ca.AddressID IS NULL;
481
482--Customers and products for which there are no orders
483SELECT c.CustomerID, p.ProductID
484FROM SalesLT.Customer AS c
485FULL JOIN SalesLT.SalesOrderHeader AS oh
486ON c.CustomerID = oh.CustomerID
487FULL JOIN SalesLT.SalesOrderDetail AS od
488ON od.SalesOrderID = oh.SalesOrderID
489FULL JOIN SalesLT.Product AS p
490ON p.ProductID = od.ProductID
491WHERE oh.SalesOrderID IS NULL
492ORDER BY ProductID, CustomerID;
493
494
495/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
496
497
498--4-Using Set Operators
499
500
501--Demo 1-Union
502--Setup
503CREATE VIEW [SalesLT].[Customers]
504as
505select distinct firstname,lastname
506from saleslt.customer
507where lastname >='m'
508or customerid=3;
509GO
510CREATE VIEW [SalesLT].[Employees]
511as
512select distinct firstname,lastname
513from saleslt.customer
514where lastname <='m'
515or customerid=3;
516GO
517
518--Union example
519SELECT FirstName, LastName
520FROM SalesLT.Employees
521UNION
522SELECT FirstName, LastName
523FROM SalesLT.Customers
524ORDER BY LastName;
525
526
527--Demo 2-Intersect
528SELECT FirstName, LastName
529FROM SalesLT.Customers
530INTERSECT
531SELECT FirstName, LastName
532FROM SalesLT.Employees;
533
534
535--Demo 3-Except
536SELECT FirstName, LastName
537FROM SalesLT.Customers
538EXCEPT
539SELECT FirstName, LastName
540FROM SalesLT.Employees;
541
542
543/*
544Challenge 1: Retrieve Customer Addresses
545Customers can have two kinds of address: a main office address and a shipping address. The accounts department want to ensure that the main office address is always used for billing, and have asked you to write a query that clearly identifies the different types of address for each customer.
5461. Retrieve billing addresses
547Write a query that retrieves the company name, first line of the street address, city, and a column named AddressType with the value 'Billing' for customers where the address type in the SalesLT.CustomerAddress table is 'Main Office'.
5482. Retrieve shipping addresses
549Write a similar query that retrieves the company name, first line of the street address, city, and a column named AddressType with the value 'Shipping' for customers where the address type in the SalesLT.CustomerAddress table is 'Shipping'.
5503. Combine billing and shipping addresses
551Combine the results returned by the two queries to create a list of all customer addresses that is sorted by company name and then address type.
552Challenge 2: Filter Customer Addresses
553You have created a master list of all customer addresses, but now you have been asked to create filtered lists that show which customers have only a main office address, and which customers have both a main office and a shipping address.
5541. Retrieve customers with only a main office address
555Write a query that returns the company name of each company that appears in a table of customers with a 'Main Office' address, but not in a table of customers with a 'Shipping' address.
5562. Retrieve only customers with both a main office address and a shipping address
557Write a query that returns the company name of each company that appears in a table of customers with a 'Main Office' address, and also in a table of customers with a 'Shipping' address.
558*/
559
560--Solution 1-Customer Addresses
561--Billing addresses
562SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType
563FROM SalesLT.Customer AS c
564JOIN SalesLT.CustomerAddress AS ca
565ON c.CustomerID = ca.CustomerID
566JOIN SalesLT.Address AS a
567ON ca.AddressID = a.AddressID
568WHERE ca.AddressType = 'Main Office';
569
570--Shipping addresses
571SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
572FROM SalesLT.Customer AS c
573JOIN SalesLT.CustomerAddress AS ca
574ON c.CustomerID = ca.CustomerID
575JOIN SalesLT.Address AS a
576ON ca.AddressID = a.AddressID
577WHERE ca.AddressType = 'Shipping';
578
579--All customer addresses
580SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType
581FROM SalesLT.Customer AS c
582JOIN SalesLT.CustomerAddress AS ca
583ON c.CustomerID = ca.CustomerID
584JOIN SalesLT.Address AS a
585ON ca.AddressID = a.AddressID
586WHERE ca.AddressType = 'Main Office'
587UNION ALL
588SELECT c.CompanyName, a.AddressLine1, a.City, 'Shipping' AS AddressType
589FROM SalesLT.Customer AS c
590JOIN SalesLT.CustomerAddress AS ca
591ON c.CustomerID = ca.CustomerID
592JOIN SalesLT.Address AS a
593ON ca.AddressID = a.AddressID
594WHERE ca.AddressType = 'Shipping'
595ORDER BY c.CompanyName, AddressType;
596
597
598--Solution 2-Filtering Customer Addresses
599--Customers with only a main office address
600SELECT c.CompanyName
601FROM SalesLT.Customer AS c
602JOIN SalesLT.CustomerAddress AS ca
603ON c.CustomerID = ca.CustomerID
604JOIN SalesLT.Address AS a
605ON ca.AddressID = a.AddressID
606WHERE ca.AddressType = 'Main Office'
607EXCEPT
608SELECT c.CompanyName
609FROM SalesLT.Customer AS c
610JOIN SalesLT.CustomerAddress AS ca
611ON c.CustomerID = ca.CustomerID
612JOIN SalesLT.Address AS a
613ON ca.AddressID = a.AddressID
614WHERE ca.AddressType = 'Shipping'
615ORDER BY c.CompanyName;
616
617--Only customers with both a main office and a shipping address
618SELECT c.CompanyName
619FROM SalesLT.Customer AS c
620JOIN SalesLT.CustomerAddress AS ca
621ON c.CustomerID = ca.CustomerID
622JOIN SalesLT.Address AS a
623ON ca.AddressID = a.AddressID
624WHERE ca.AddressType = 'Main Office'
625INTERSECT
626SELECT c.CompanyName
627FROM SalesLT.Customer AS c
628JOIN SalesLT.CustomerAddress AS ca
629ON c.CustomerID = ca.CustomerID
630JOIN SalesLT.Address AS a
631ON ca.AddressID = a.AddressID
632WHERE ca.AddressType = 'Shipping'
633ORDER BY c.CompanyName;
634
635
636/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
637
638
639--5 - Using Functions and Aggregating Data
640
641
642--Demo 1-Functions
643--Scalar functions
644SELECT YEAR(SellStartDate) SellStartYear, ProductID, Name
645FROM SalesLT.Product
646ORDER BY SellStartYear;
647
648SELECT YEAR(SellStartDate) SellStartYear, DATENAME(mm,SellStartDate) SellStartMonth,
649 DAY(SellStartDate) SellStartDay, DATENAME(dw, SellStartDate) SellStartWeekday,
650 ProductID, Name
651FROM SalesLT.Product
652ORDER BY SellStartYear;
653
654SELECT DATEDIFF(yy,SellStartDate, GETDATE()) YearsSold, ProductID, Name
655FROM SalesLT.Product
656ORDER BY ProductID;
657
658SELECT UPPER(Name) AS ProductName
659FROM SalesLT.Product;
660
661SELECT CONCAT(FirstName + ' ', LastName) AS FullName
662FROM SalesLT.Customer;
663
664SELECT Name, ProductNumber, LEFT(ProductNumber, 2) AS ProductType
665FROM SalesLT.Product;
666
667SELECT Name, ProductNumber, LEFT(ProductNumber, 2) AS ProductType,
668 SUBSTRING(ProductNumber,CHARINDEX('-', ProductNumber) + 1, 4) AS ModelCode,
669 SUBSTRING(ProductNumber, LEN(ProductNumber) - CHARINDEX('-', REVERSE(RIGHT(ProductNumber, 3))) + 2, 2) AS SizeCode
670FROM SalesLT.Product;
671
672
673--Logical functions
674SELECT Name, Size AS NumericSize
675FROM SalesLT.Product
676WHERE ISNUMERIC(Size) = 1;
677
678SELECT Name, IIF(ProductCategoryID IN (5,6,7), 'Bike', 'Other') ProductType
679FROM SalesLT.Product;
680
681SELECT Name, IIF(ISNUMERIC(Size) = 1, 'Numeric', 'Non-Numeric') SizeType
682FROM SalesLT.Product;
683
684SELECT prd.Name AS ProductName, cat.Name AS Category,
685 CHOOSE (cat.ParentProductCategoryID, 'Bikes','Components','Clothing','Accessories') AS ProductType
686FROM SalesLT.Product AS prd
687JOIN SalesLT.ProductCategory AS cat
688ON prd.ProductCategoryID = cat.ProductCategoryID;
689
690
691--Window functions
692SELECT TOP(100) ProductID, Name, ListPrice,
693 RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
694FROM SalesLT.Product AS p
695ORDER BY RankByPrice;
696
697SELECT c.Name AS Category, p.Name AS Product, ListPrice,
698 RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
699FROM SalesLT.Product AS p
700JOIN SalesLT.ProductCategory AS c
701ON p.ProductCategoryID = c.ProductcategoryID
702ORDER BY Category, RankByPrice;
703
704
705--Aggregate Functions
706SELECT COUNT(*) AS Products, COUNT(DISTINCT ProductCategoryID) AS Categories, AVG(ListPrice) AS AveragePrice
707FROM SalesLT.Product;
708
709SELECT COUNT(p.ProductID) BikeModels, AVG(p.ListPrice) AveragePrice
710FROM SalesLT.Product AS p
711JOIN SalesLT.ProductCategory AS c
712ON p.ProductCategoryID = c.ProductCategoryID
713WHERE c.Name LIKE '%Bikes';
714
715
716--Demo 2-Group By
717SELECT Salesperson, COUNT(CustomerID) Customers
718FROM SalesLT.Customer
719GROUP BY Salesperson
720ORDER BY Salesperson;
721
722SELECT c.Name AS Category, COUNT(p.ProductID) AS Products
723FROM SalesLT.Product AS p
724JOIN SalesLT.ProductCategory AS c
725ON p.ProductCategoryID = c.ProductCategoryID
726GROUP BY c.Name
727ORDER BY Category;
728
729SELECT c.Salesperson, SUM(oh.SubTotal) SalesRevenue
730FROM SalesLT.Customer c
731JOIN SalesLT.SalesOrderHeader oh
732ON c.CustomerID = oh.CustomerID
733GROUP BY c.Salesperson
734ORDER BY SalesRevenue DESC;
735
736SELECT c.Salesperson, ISNULL(SUM(oh.SubTotal), 0.00) SalesRevenue
737FROM SalesLT.Customer c
738LEFT JOIN SalesLT.SalesOrderHeader oh
739ON c.CustomerID = oh.CustomerID
740GROUP BY c.Salesperson
741ORDER BY SalesRevenue DESC;
742
743SELECT c.Salesperson, CONCAT(c.FirstName +' ', c.LastName) AS Customer, ISNULL(SUM(oh.SubTotal), 0.00) SalesRevenue
744FROM SalesLT.Customer c
745LEFT JOIN SalesLT.SalesOrderHeader oh
746ON c.CustomerID = oh.CustomerID
747GROUP BY c.Salesperson, CONCAT(c.FirstName +' ', c.LastName)
748ORDER BY SalesRevenue DESC, Customer;
749
750
751--Demo 3-Having
752--Try to find salespeople with over 150 customers (fails with error)
753SELECT Salesperson, COUNT(CustomerID) Customers
754FROM SalesLT.Customer
755WHERE COUNT(CustomerID) > 100
756GROUP BY Salesperson
757ORDER BY Salesperson;
758
759--Need to use HAVING clause to filter based on aggregate
760SELECT Salesperson, COUNT(CustomerID) Customers
761FROM SalesLT.Customer
762GROUP BY Salesperson
763HAVING COUNT(CustomerID) > 100
764ORDER BY Salesperson;
765
766
767/*
768Challenge 1: Retrieve Product Information
769Your reports are returning the correct records, but you would like to modify how these records are displayed.
7701. Retrieve the name and approximate weight of each product
771Write a query to return the product ID of each product, together with the product name formatted as upper case and a column named ApproxWeight with the weight of each product rounded to the nearest whole unit.
7722. Retrieve the year and month in which products were first sold
773Extend your query to include columns named SellStartYear and SellStartMonth containing the year and month in which Adventure Works started selling each product. The month should be displayed as the month name (for example, 'January').
7743. Extract product types from product numbers
775Extend your query to include a column named ProductType that contains the leftmost two characters from the product number.
7764. Retrieve only products with a numeric size
777Extend your query to filter the product returned so that only products with a numeric size are included.
778Challenge 2: Rank Customers by Revenue
779The sales manager would like a list of customers ranked by sales.
7801. Retrieve companies ranked by sales totals
781Write a query that returns a list of company names with a ranking of their place in a list of highest TotalDue values from the SalesOrderHeader table.
782Challenge 3: Aggregate Product Sales
783The product manager would like aggregated information about product sales.
7841. Retrieve total sales by product
785Write a query to retrieve a list of the product names and the total revenue calculated as the sum of the LineTotal from the SalesLT.SalesOrderDetail table, with the results sorted in descending order of total revenue.
7862. Filter the product sales list to include only products that cost over $1,000
787Modify the previous query to include sales totals for products that have a list price of more than $1000.
7883. Filter the product sales groups to include only total sales over $20,000
789Modify the previous query to only include only product groups with a total sales value greater than $20,000.
790*/
791
792
793--Solution 1-Product Data
794--Retrieve the nname and approximate weight of each product
795SELECT ProductID,
796 UPPER(Name) AS ProductName,
797 ROUND(Weight, 0) AS ApproxWeight
798FROM SalesLT.Product;
799
800--Retrieve the month and year products were first sold
801SELECT ProductID,
802 UPPER(Name) AS ProductName,
803 ROUND(Weight, 0) AS ApproxWeight,
804 YEAR(SellStartDate) as SellStartYear,
805 DATENAME(m, SellStartDate) as SellStartMonth
806FROM SalesLT.Product;
807
808--Extract type from product number
809SELECT ProductID,
810 UPPER(Name) AS ProductName,
811 ROUND(Weight, 0) AS ApproxWeight,
812 YEAR(SellStartDate) as SellStartYear,
813 DATENAME(m, SellStartDate) as SellStartMonth,
814 LEFT(ProductNumber, 2) AS ProductType
815FROM SalesLT.Product;
816
817--Filter to include only products with numeric sizes
818SELECT ProductID,
819 UPPER(Name) AS ProductName,
820 ROUND(Weight, 0) AS ApproxWeight,
821 YEAR(SellStartDate) as SellStartYear,
822 DATENAME(m, SellStartDate) as SellStartMonth,
823 LEFT(ProductNumber, 2) AS ProductType
824FROM SalesLT.Product
825WHERE ISNUMERIC(Size)=1;
826
827
828--Solution 2-Ranked Customers
829--Retrieve Companies Ranked by Revenue
830SELECT CompanyName,
831 TotalDue AS Revenue,
832 RANK() OVER (ORDER BY TotalDue DESC) AS RankByRevenue
833FROM SalesLT.SalesOrderHeader AS SOH
834JOIN SalesLT.Customer AS C
835ON SOH.CustomerID=C.CustomerID;
836
837
838--Solution 3-Product Sales
839--Retrieve Total Sales by Product
840SELECT Name,SUM(LineTotal) AS TotalRevenue
841FROM SalesLT.SalesOrderDetail AS SOD
842JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
843GROUP BY P.Name
844ORDER BY TotalRevenue DESC;
845
846--Only products that cost over $1,000
847SELECT Name,SUM(LineTotal) AS TotalRevenue
848FROM SalesLT.SalesOrderDetail AS SOD
849JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
850WHERE P.ListPrice > 1000
851GROUP BY P.Name
852ORDER BY TotalRevenue DESC;
853
854--Only groupings with sales totals over $20,000
855SELECT Name,SUM(LineTotal) AS TotalRevenue
856FROM SalesLT.SalesOrderDetail AS SOD
857JOIN SalesLT.Product AS P ON SOD.ProductID=P.ProductID
858WHERE P.ListPrice > 1000
859GROUP BY P.Name
860HAVING SUM(LineTotal) > 20000
861ORDER BY TotalRevenue DESC;
862
863
864/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
865
866
867--6-Using Subqueries and APPLY
868
869--Demo 1-Scalar Subquery
870--Display a list of products whose list price is higher than the highest unit price of items that have sold
871
872SELECT MAX(UnitPrice) FROM SalesLT.SalesOrderDetail
873
874SELECT * from SalesLT.Product
875WHERE ListPrice >
876
877
878SELECT * from SalesLT.Product
879WHERE ListPrice >
880(SELECT MAX(UnitPrice) FROM SalesLT.SalesOrderDetail)
881
882
883--Demo 2-Multi-Valued Subquery
884--List products that have an order quantity greater than 20
885
886SELECT Name FROM SalesLT.Product
887WHERE ProductID IN
888(SELECT ProductID from SalesLT.SalesOrderDetail
889WHERE OrderQty>20)
890
891SELECT Name
892FROM SalesLT.Product P
893JOIN SalesLT.SalesOrderDetail SOD
894ON P.ProductID=SOD.ProductID
895WHERE OrderQty>20
896
897
898--Demo 3-Correlated Subquery
899--For each customer list all sales on the last day that they made a sale
900
901SELECT CustomerID, SalesOrderID, OrderDate
902FROM SalesLT.SalesOrder AS SO1
903ORDER BY CustomerID,OrderDate
904
905SELECT CustomerID, SalesOrderID, OrderDate
906FROM SalesLT.SalesOrder AS SO1
907WHERE orderdate =
908(SELECT MAX(orderdate)
909FROM SalesLT.SalesOrder)
910
911
912SELECT CustomerID, SalesOrderID, OrderDate
913FROM SalesLT.SalesOrder AS SO1
914WHERE orderdate =
915(SELECT MAX(orderdate)
916FROM SalesLT.SalesOrder AS SO2
917WHERE SO2.CustomerID = SO1.CustomerID)
918ORDER BY CustomerID
919
920
921--Demo 4-CROSS APPLY
922--Setup
923CREATE FUNCTION SalesLT.udfMaxUnitPrice (@SalesOrderID int)
924RETURNS TABLE
925AS
926RETURN
927SELECT SalesOrderID,Max(UnitPrice) as MaxUnitPrice FROM
928SalesLT.SalesOrderDetail
929WHERE SalesOrderID=@SalesOrderID
930GROUP BY SalesOrderID;
931
932--Display the sales order details for items that are equal to
933--the maximum unit price for that sales order
934SELECT * FROM SalesLT.SalesOrderDetail AS SOH
935CROSS APPLY SalesLT.udfMaxUnitPrice(SOH.SalesOrderID) AS MUP
936WHERE SOH.UnitPrice=MUP.MaxUnitPrice
937ORDER BY SOH.SalesOrderID;
938
939
940/*
941Challenge 1: Retrieve Product Price Information
942Adventure Works products each have a standard cost price that indicates the cost of manufacturing the product, and a list price that indicates the recommended selling price for the product. This data is stored in the SalesLT.Product table. Whenever a product is ordered, the actual unit price at which it was sold is also recorded in the SalesLT.SalesOrderDetail table. You must use subqueries to compare the cost and list prices for each product with the unit prices charged in each sale.
9431. Retrieve products whose list price is higher than the average unit price
944Retrieve the product ID, name, and list price for each product where the list price is higher than the average unit price for all products that have been sold.
9452. Retrieve Products with a list price of $100 or more that have been sold for less than $100
946Retrieve the product ID, name, and list price for each product where the list price is $100 or more, and the product has been sold for less than $100.
9473. Retrieve the cost, list price, and average selling price for each product
948Retrieve the product ID, name, cost, and list price for each product along with the average unit price for which that product has been sold.
9494. Retrieve products that have an average selling price that is lower than the cost
950Filter your previous query to include only products where the cost price is higher than the average selling price.
951Challenge 2: Retrieve Customer Information
952The AdventureWorksLT database includes a table-valued user-defined function named dbo.ufnGetCustomerInformation. You must use this function to retrieve details of customers based on customer ID values retrieved from tables in the database.
9531. Retrieve customer information for all sales orders
954Retrieve the sales order ID, customer ID, first name, last name, and total due for all sales orders from the SalesLT.SalesOrderHeader table and the dbo.ufnGetCustomerInformation function.
9552. Retrieve customer address information
956Retrieve the customer ID, first name, last name, address line 1 and city for all customers from the SalesLT.Address and SalesLT.CustomerAddress tables, and the dbo.ufnGetCustomerInformation function.
957*/
958
959
960--Solution 1-Product Price Data
961--Retrieve Products whose list price is higher than the average unit price in the SalesOrderDetail table
962SELECT ProductID, Name, ListPrice from SalesLT.Product
963WHERE ListPrice >
964(SELECT AVG(UnitPrice) FROM SalesLT.SalesOrderDetail)
965ORDER BY ProductID;
966
967--Retrieve products that are priced $100 or more
968--but have sold for a unit price of less than $100
969SELECT ProductID, Name, ListPrice FROM SalesLT.Product
970WHERE ProductID IN
971(SELECT ProductID from SalesLT.SalesOrderDetail
972 WHERE UnitPrice < 100.00)
973AND ListPrice >= 100.00
974ORDER BY ProductID;
975
976--Retrieve cost, list price, and average selling price for each product
977SELECT ProductID, Name, StandardCost, ListPrice,
978 (SELECT AVG(UnitPrice)
979 FROM SalesLT.SalesOrderDetail AS SOD
980 WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
981FROM SalesLT.Product AS P
982ORDER BY P.ProductID;
983
984--Find products where the average selling price is less than cost
985SELECT ProductID, Name, StandardCost, ListPrice,
986(SELECT AVG(UnitPrice)
987 FROM SalesLT.SalesOrderDetail AS SOD
988 WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
989FROM SalesLT.Product AS P
990WHERE StandardCost >
991(SELECT AVG(UnitPrice)
992 FROM SalesLT.SalesOrderDetail AS SOD
993 WHERE P.ProductID = SOD.ProductID)
994ORDER BY P.ProductID;
995
996
997--Solution 2-Customer Information
998--Retrieve sales order data with customer information from a function
999SELECT SOH.SalesOrderID, SOH.CustomerID, CI.FirstName, CI.LastName, SOH.TotalDue
1000FROM SalesLT.SalesOrderHeader AS SOH
1001CROSS APPLY dbo.ufnGetCustomerInformation(SOH.CustomerID) AS CI
1002ORDER BY SOH.SalesOrderID;
1003
1004--Retrieve addresses with customer information from a function
1005SELECT CA.CustomerID, CI.FirstName, CI.LastName, A.AddressLine1, A.City
1006FROM SalesLT.Address AS A
1007JOIN SalesLT.CustomerAddress AS CA
1008ON A.AddressID = CA.AddressID
1009CROSS APPLY dbo.ufnGetCustomerInformation(CA.CustomerID) AS CI
1010ORDER BY CA.CustomerID;
1011
1012
1013/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
1014
1015
1016--7-Using Table Expressions
1017
1018
1019--Demo 1-Views
1020--Create a view
1021CREATE VIEW SalesLT.vCustomerAddress
1022AS
1023SELECT C.CustomerID, FirstName, LastName, AddressLine1, City, StateProvince
1024FROM
1025SalesLT.Customer C JOIN SalesLT.CustomerAddress CA
1026ON C.CustomerID=CA.CustomerID
1027JOIN SalesLT.Address A
1028ON CA.AddressID=A.AddressID
1029
1030--Query the view
1031SELECT CustomerID, City
1032FROM SalesLT.vCustomerAddress
1033
1034--Join the view to a table
1035SELECT c.StateProvince, c.City, ISNULL(SUM(s.TotalDue), 0.00) AS Revenue
1036FROM SalesLT.vCustomerAddress AS c
1037LEFT JOIN SalesLT.SalesOrderHeader AS s
1038ON s.CustomerID = c.CustomerID
1039GROUP BY c.StateProvince, c.City
1040ORDER BY c.StateProvince, Revenue DESC;
1041
1042
1043--Demo 2-Temp Tables and Variables
1044--Temporary table
1045CREATE TABLE #Colors
1046(Color varchar(15));
1047
1048INSERT INTO #Colors
1049SELECT DISTINCT Color FROM SalesLT.Product;
1050
1051SELECT * FROM #Colors;
1052
1053--Table variable
1054DECLARE @Colors AS TABLE (Color varchar(15));
1055
1056INSERT INTO @Colors
1057SELECT DISTINCT Color FROM SalesLT.Product;
1058
1059SELECT * FROM @Colors;
1060
1061--New batch
1062SELECT * FROM #Colors;
1063
1064SELECT * FROM @Colors; --now out of scope
1065
1066
1067--Demo 3-TVFs
1068CREATE FUNCTION SalesLT.udfCustomersByCity
1069(@City AS VARCHAR(20))
1070RETURNS TABLE
1071AS
1072RETURN
1073(SELECT C.CustomerID, FirstName, LastName, AddressLine1, City, StateProvince
1074 FROM SalesLT.Customer C JOIN SalesLT.CustomerAddress CA
1075 ON C.CustomerID=CA.CustomerID
1076 JOIN SalesLT.Address A ON CA.AddressID=A.AddressID
1077 WHERE City=@City);
1078
1079
1080SELECT * FROM SalesLT.udfCustomersByCity('Bellevue')
1081
1082
1083--Demo 4-Derived Tables
1084SELECT Category, COUNT(ProductID) AS Products
1085FROM
1086 (SELECT p.ProductID, p.Name AS Product, c.Name AS Category
1087 FROM SalesLT.Product AS p
1088 JOIN SalesLT.ProductCategory AS c
1089 ON p.ProductCategoryID = c.ProductCategoryID) AS ProdCats
1090GROUP BY Category
1091ORDER BY Category;
1092
1093
1094--Demo 5-CTEs
1095--Using a CTE
1096WITH ProductsByCategory (ProductID, ProductName, Category)
1097AS
1098(
1099 SELECT p.ProductID, p.Name, c.Name AS Category
1100 FROM SalesLT.Product AS p
1101 JOIN SalesLT.ProductCategory AS c
1102 ON p.ProductCategoryID = c.ProductCategoryID
1103)
1104
1105SELECT Category, COUNT(ProductID) AS Products
1106FROM ProductsByCategory
1107GROUP BY Category
1108ORDER BY Category;
1109
1110
1111--Recursive CTE
1112SELECT * FROM SalesLT.Employee
1113
1114--Using the CTE to perform recursion
1115WITH OrgReport (ManagerID, EmployeeID, EmployeeName, Level)
1116AS
1117(
1118 --Anchor query
1119 SELECT e.ManagerID, e.EmployeeID, EmployeeName, 0
1120 FROM SalesLT.Employee AS e
1121 WHERE ManagerID IS NULL
1122
1123 UNION ALL
1124
1125 --Recursive query
1126 SELECT e.ManagerID, e.EmployeeID, e.EmployeeName, Level + 1
1127 FROM SalesLT.Employee AS e
1128 INNER JOIN OrgReport AS o ON e.ManagerID = o.EmployeeID
1129)
1130
1131SELECT * FROM OrgReport
1132OPTION (MAXRECURSION 3);
1133
1134
1135/*
1136Challenge 1: Retrieve Product Information
1137Adventure Works sells many products that are variants of the same product model. You must write queries that retrieve information about these products
11381. Retrieve product model descriptions
1139Retrieve the product ID, product name, product model name, and product model summary for each product from the SalesLT.Product table and the SalesLT.vProductModelCatalogDescription view.
11402. Create a table of distinct colors
1141Create a table variable and populate it with a list of distinct colors from the SalesLT.Product table. Then use the table variable to filter a query that returns the product ID, name, and color from the SalesLT.Product table so that only products with a color listed in the table variable are returned.
11423. Retrieve product parent categories
1143The AdventureWorksLT database includes a table-valued function named dbo.ufnGetAllCategories, which returns a table of product categories (for example 'Road Bikes') and parent categories (for example 'Bikes'). Write a query that uses this function to return a list of all products including their parent category and category.
11444. Retrieve products that have an average selling price that is lower than the cost
1145Filter your previous query to include only products where the cost price is higher than the average selling price.
1146Challenge 2: Retrieve Customer Sales Revenue
1147Each Adventure Works customer is a retail company with a named contact. You must create queries that return the total revenue for each customer, including the company and customer contact names.
11481. Retrieve sales revenue by customer and contact
1149Retrieve a list of customers in the format Company (Contact Name) together with the total revenue for that customer. Use a derived table or a common table expression to retrieve the details for each sales order, and then query the derived table or CTE to aggregate and group the data.
1150*/
1151
1152
1153--Solution 1-Product Data
1154--Retrieve product model descriptions
1155SELECT P.ProductID, P.Name AS ProductName, PM.Name AS ProductModel, PM.Summary
1156FROM SalesLT.Product AS P
1157JOIN SalesLT.vProductModelCatalogDescription AS PM
1158ON P.ProductModelID = PM.ProductModelID
1159ORDER BY ProductID;
1160
1161--Create a table of distinct colors
1162DECLARE @colors AS TABLE (Color nvarchar(15));
1163
1164INSERT INTO @Colors
1165SELECT DISTINCT Color FROM SalesLT.Product;
1166
1167SELECT ProductID, Name, Color
1168FROM SalesLT.Product
1169WHERE Color IN (SELECT Color FROM @Colors);
1170
1171
1172--Retrieve product parent categories from a function
1173SELECT C.ParentProductCategoryName AS ParentCategory,
1174 C.ProductCategoryName AS Category,
1175 P.ProductID, P.Name AS ProductName
1176FROM SalesLT.Product AS P
1177JOIN dbo.ufnGetAllCategories() AS C
1178ON P.ProductCategoryID = C.ProductCategoryID
1179ORDER BY ParentCategory, Category, ProductName;
1180
1181
1182--Solution 2-Customer Sales
1183--Get sales revenue by company and contact (using derived table)
1184SELECT CompanyContact, SUM(SalesAmount) AS Revenue
1185FROM
1186 (SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue
1187 FROM SalesLT.SalesOrderHeader AS SOH
1188 JOIN SalesLT.Customer AS c
1189 ON SOH.CustomerID = c.CustomerID) AS CustomerSales(CompanyContact, SalesAmount)
1190GROUP BY CompanyContact
1191ORDER BY CompanyContact;
1192
1193--Get sales revenue by company and contact (using CTE)
1194WITH CustomerSales(CompanyContact, SalesAmount)
1195AS
1196(SELECT CONCAT(c.CompanyName, CONCAT(' (' + c.FirstName + ' ', c.LastName + ')')), SOH.TotalDue
1197 FROM SalesLT.SalesOrderHeader AS SOH
1198 JOIN SalesLT.Customer AS c
1199 ON SOH.CustomerID = c.CustomerID)
1200SELECT CompanyContact, SUM(SalesAmount) AS Revenue
1201FROM CustomerSales
1202GROUP BY CompanyContact
1203ORDER BY CompanyContact;
1204
1205
1206/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
1207
1208
1209--8-Grouping Sets and Pivoting Data
1210
1211--Demo 1-Grouping Sets
1212SELECT cat.ParentProductCategoryName, cat.ProductCategoryName, count(prd.ProductID) AS Products
1213FROM SalesLT.vGetAllCategories as cat
1214LEFT JOIN SalesLT.Product AS prd
1215ON prd.ProductCategoryID = cat.ProductcategoryID
1216GROUP BY cat.ParentProductCategoryName, cat.ProductCategoryName
1217--GROUP BY GROUPING SETS(cat.ParentProductCategoryName, cat.ProductCategoryName, ())
1218--GROUP BY ROLLUP (cat.ParentProductCategoryName, cat.ProductCategoryName)
1219--GROUP BY CUBE (cat.ParentProductCategoryName, cat.ProductCategoryName)
1220ORDER BY cat.ParentProductCategoryName, cat.ProductCategoryName;
1221
1222
1223--Demo 2-Pivot
1224SELECT * FROM
1225(SELECT P.ProductID, PC.Name,ISNULL(P.Color, 'Uncolored') AS Color
1226 FROM saleslt.productcategory AS PC
1227 JOIN SalesLT.Product AS P
1228 ON PC.ProductCategoryID=P.ProductCategoryID
1229 ) AS PPC
1230PIVOT(COUNT(ProductID) FOR Color IN([Red],[Blue],[Black],[Silver],[Yellow],[Grey], [Multi], [Uncolored])) as pvt
1231ORDER BY Name;
1232
1233--Unpivot
1234CREATE TABLE #ProductColorPivot
1235(Name varchar(50), Red int, Blue int, Black int, Silver int, Yellow int, Grey int , multi int, uncolored int);
1236
1237INSERT INTO #ProductColorPivot
1238SELECT * FROM
1239(SELECT P.ProductID, PC.Name,ISNULL(P.Color, 'Uncolored') AS Color
1240 FROM saleslt.productcategory AS PC
1241 JOIN SalesLT.Product AS P
1242 ON PC.ProductCategoryID=P.ProductCategoryID
1243 ) AS PPC
1244PIVOT(COUNT(ProductID) FOR Color IN([Red],[Blue],[Black],[Silver],[Yellow],[Grey], [Multi], [Uncolored])) as pvt
1245ORDER BY Name;
1246
1247SELECT Name, Color, ProductCount
1248FROM
1249(SELECT Name,
1250[Red],[Blue],[Black],[Silver],[Yellow],[Grey], [Multi], [Uncolored]
1251FROM #ProductColorPivot) pcp
1252UNPIVOT
1253(ProductCount FOR Color IN ([Red],[Blue],[Black],[Silver],[Yellow],[Grey], [Multi], [Uncolored])
1254) AS ProductCounts
1255
1256
1257--Unpivot
1258CREATE TABLE #SalesByQuarter
1259(ProductID int,
1260 Q1 money,
1261 Q2 money,
1262 Q3 money,
1263 Q4 money);
1264
1265INSERT INTO #SalesByQuarter
1266VALUES
1267(1, 19999.00, 21567.00, 23340.00, 25876.00),
1268(2, 10997.00, 12465.00, 13367.00, 14365.00),
1269(3, 21900.00, 21999.00, 23376.00, 23676.00);
1270
1271SELECT * FROM #SalesByQuarter;
1272
1273SELECT ProductID, Period, Revenue
1274FROM
1275(SELECT ProductID,
1276Q1, Q2, Q3, Q4
1277FROM #SalesByQuarter) sbq
1278UNPIVOT
1279(Revenue FOR Period IN (Q1, Q2, Q3, Q4)
1280) AS RevenueReport
1281
1282
1283/*
1284Challenge 1: Retrieve Regional Sales Totals
1285Adventure Works sells products to customers in multiple country/regions around the world.
12861. Retrieve totals for country/region and state/province
1287An existing report uses the following query to return total sales revenue grouped by country/region and state/province.
1288SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue
1289FROM SalesLT.Address AS a
1290JOIN SalesLT.CustomerAddress AS ca ON a.AddressID = ca.AddressID
1291JOIN SalesLT.Customer AS c ON ca.CustomerID = c.CustomerID
1292JOIN SalesLT.SalesOrderHeader as soh ON c.CustomerID = soh.CustomerID
1293GROUP BY a.CountryRegion, a.StateProvince
1294ORDER BY a.CountryRegion, a.StateProvince;
1295
1296You have been asked to modify this query so that the results include a grand total for all sales revenue and a subtotal for each country/region in addition to the state/province subtotals that are already returned.
12972. Indicate the grouping level in the results
1298Modify your query to include a column named Level that indicates at which level in the total, country/region, and state/province hierarchy the revenue figure in the row is aggregated. For example, the grand total row should contain the value 'Total', the row showing the subtotal for United States should contain the value 'United States Subtotal', and the row showing the subtotal for California should contain the value 'California Subtotal'.
12993. Add a grouping level for cities
1300Extend your query to include a grouping for individual cities.
1301Challenge 2: Retrieve Customer Sales Revenue by Category
1302Adventure Works products are grouped into categories, which in turn have parent categories (defined in the SalesLT.vGetAllCategories view). Adventure Works customers are retail companies, and they may place orders for products of any category. The revenue for each product in an order is recorded as the LineTotal value in the SalesLT.SalesOrderDetail table.
13031. Retrieve customer sales revenue for each parent category
1304Retrieve a list of customer company names together with their total revenue for each parent category in Accessories, Bikes, Clothing, and Components.
1305*/
1306
1307
1308--Solution 1-Regional Sales Totals
1309--Initial query
1310SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue
1311FROM SalesLT.Address AS a
1312JOIN SalesLT.CustomerAddress AS ca
1313ON a.AddressID = ca.AddressID
1314JOIN SalesLT.Customer AS c
1315ON ca.CustomerID = c.CustomerID
1316JOIN SalesLT.SalesOrderHeader as soh
1317ON c.CustomerID = soh.CustomerID
1318GROUP BY a.CountryRegion, a.StateProvince
1319ORDER BY a.CountryRegion, a.StateProvince;
1320
1321--With totals
1322SELECT a.CountryRegion, a.StateProvince, SUM(soh.TotalDue) AS Revenue
1323FROM SalesLT.Address AS a
1324JOIN SalesLT.CustomerAddress AS ca
1325ON a.AddressID = ca.AddressID
1326JOIN SalesLT.Customer AS c
1327ON ca.CustomerID = c.CustomerID
1328JOIN SalesLT.SalesOrderHeader as soh
1329ON c.CustomerID = soh.CustomerID
1330GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)
1331ORDER BY a.CountryRegion, a.StateProvince;
1332
1333--Subtotal levels
1334SELECT a.CountryRegion, a.StateProvince,
1335IIF(GROUPING_ID(a.CountryRegion) = 1 AND GROUPING_ID(a.StateProvince) = 1, 'Total', IIF(GROUPING_ID(a.StateProvince) = 1, a.CountryRegion + ' Subtotal', a.StateProvince + ' Subtotal')) AS Level,
1336SUM(soh.TotalDue) AS Revenue
1337FROM SalesLT.Address AS a
1338JOIN SalesLT.CustomerAddress AS ca
1339ON a.AddressID = ca.AddressID
1340JOIN SalesLT.Customer AS c
1341ON ca.CustomerID = c.CustomerID
1342JOIN SalesLT.SalesOrderHeader as soh
1343ON c.CustomerID = soh.CustomerID
1344GROUP BY ROLLUP(a.CountryRegion, a.StateProvince)
1345ORDER BY a.CountryRegion, a.StateProvince;
1346
1347--Including cities
1348SELECT a.CountryRegion, a.StateProvince, a.City,
1349CHOOSE (1 + GROUPING_ID(a.CountryRegion) + GROUPING_ID(a.StateProvince) + GROUPING_ID(a.City), a.City + ' Subtotal', a.StateProvince + ' Subtotal', a.CountryRegion + ' Subtotal', 'Total') AS Level,
1350SUM(soh.TotalDue) AS Revenue
1351FROM SalesLT.Address AS a
1352JOIN SalesLT.CustomerAddress AS ca
1353ON a.AddressID = ca.AddressID
1354JOIN SalesLT.Customer AS c
1355ON ca.CustomerID = c.CustomerID
1356JOIN SalesLT.SalesOrderHeader as soh
1357ON c.CustomerID = soh.CustomerID
1358GROUP BY ROLLUP(a.CountryRegion, a.StateProvince, a.City)
1359ORDER BY a.CountryRegion, a.StateProvince, a.City;
1360
1361
1362--Solution 2-Customer Sales by Category
1363--Customer sales by parent category
1364SELECT * FROM
1365(SELECT cat.ParentProductCategoryName, cust.CompanyName, sod.LineTotal
1366 FROM SalesLT.SalesOrderDetail AS sod
1367 JOIN SalesLT.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
1368 JOIN SalesLT.Customer AS cust ON soh.CustomerID = cust.CustomerID
1369 JOIN SalesLT.Product AS prod ON sod.ProductID = prod.ProductID
1370 JOIN SalesLT.vGetAllCategories AS cat ON prod.ProductcategoryID = cat.ProductCategoryID) AS catsales
1371PIVOT (SUM(LineTotal) FOR ParentProductCategoryName IN ([Accessories], [Bikes], [Clothing], [Components])) AS pivotedsales
1372ORDER BY CompanyName;
1373
1374
1375/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
1376
1377
1378--9-Modifying Data
1379
1380
1381--Demo 1-Inserting Data
1382--Create a table for the demo
1383CREATE TABLE SalesLT.CallLog
1384(
1385 CallID int IDENTITY PRIMARY KEY NOT NULL,
1386 CallTime datetime NOT NULL DEFAULT GETDATE(),
1387 SalesPerson nvarchar(256) NOT NULL,
1388 CustomerID int NOT NULL REFERENCES SalesLT.Customer(CustomerID),
1389 PhoneNumber nvarchar(25) NOT NULL,
1390 Notes nvarchar(max) NULL
1391);
1392GO
1393
1394--Insert a row
1395INSERT INTO SalesLT.CallLog
1396VALUES
1397('2015-01-01T12:30:00', 'adventure-works\pamela0', 1, '245-555-0173', 'Returning call re: enquiry about delivery');
1398
1399SELECT * FROM SalesLT.CallLog;
1400
1401--Insert defaults and nulls
1402INSERT INTO SalesLT.CallLog
1403VALUES
1404(DEFAULT, 'adventure-works\david8', 2, '170-555-0127', NULL);
1405
1406SELECT * FROM SalesLT.CallLog;
1407
1408--Insert a row with explicit columns
1409INSERT INTO SalesLT.CallLog (SalesPerson, CustomerID, PhoneNumber)
1410VALUES
1411('adventure-works\jillian0', 3, '279-555-0130');
1412
1413SELECT * FROM SalesLT.CallLog;
1414
1415--Insert multiple rows
1416INSERT INTO SalesLT.CallLog
1417VALUES
1418(DATEADD(mi,-2, GETDATE()), 'adventure-works\jillian0', 4, '710-555-0173', NULL),
1419(DEFAULT, 'adventure-works\shu0', 5, '828-555-0186', 'Called to arrange deliver of order 10987');
1420
1421SELECT * FROM SalesLT.CallLog;
1422
1423--Insert the results of a query
1424INSERT INTO SalesLT.CallLog (SalesPerson, CustomerID, PhoneNumber, Notes)
1425SELECT SalesPerson, CustomerID, Phone, 'Sales promotion call'
1426FROM SalesLT.Customer
1427WHERE CompanyName = 'Big-Time Bike Store';
1428
1429SELECT * FROM SalesLT.CallLog;
1430
1431--Retrieving inserted identity
1432INSERT INTO SalesLT.CallLog (SalesPerson, CustomerID, PhoneNumber)
1433VALUES
1434('adventure-works\josé1', 10, '150-555-0127');
1435
1436SELECT SCOPE_IDENTITY();
1437
1438SELECT * FROM SalesLT.CallLog;
1439
1440--Overriding Identity
1441SET IDENTITY_INSERT SalesLT.CallLog ON;
1442
1443INSERT INTO SalesLT.CallLog (CallID, SalesPerson, CustomerID, PhoneNumber)
1444VALUES
1445(9, 'adventure-works\josé1', 11, '926-555-0159');
1446
1447SET IDENTITY_INSERT SalesLT.CallLog OFF;
1448
1449SELECT * FROM SalesLT.CallLog;
1450
1451
1452--Demo 2-Updating and Deleting
1453--Update a table
1454UPDATE SalesLT.CallLog
1455SET Notes = 'No notes'
1456WHERE Notes IS NULL;
1457
1458SELECT * FROM SalesLT.CallLog;
1459
1460--Update multiple columns
1461UPDATE SalesLT.CallLog
1462SET SalesPerson = '', PhoneNumber = ''
1463
1464SELECT * FROM SalesLT.CallLog;
1465
1466--Update from results of a query
1467UPDATE SalesLT.CallLog
1468SET SalesPerson = c.SalesPerson, PhoneNumber = c.Phone
1469FROM SalesLT.Customer AS c
1470WHERE c.CustomerID = SalesLT.CallLog.CustomerID;
1471
1472SELECT * FROM SalesLT.CallLog;
1473
1474--Delete rows
1475DELETE FROM SalesLT.CallLog
1476WHERE CallTime < DATEADD(dd, -7, GETDATE());
1477
1478SELECT * FROM SalesLT.CallLog;
1479
1480--Truncate the table
1481TRUNCATE TABLE SalesLT.CallLog;
1482
1483SELECT * FROM SalesLT.CallLog;
1484
1485
1486/*
1487Challenge 1: Inserting Products
1488Each Adventure Works product is stored in the SalesLT.Product table, and each product has a unique ProductID identifier, which is implemented as an IDENTITY column in the SalesLT.Product table. Products are organized into categories, which are defined in the SalesLT.ProductCategory table. The products and product category records are related by a common ProductCategoryID identifier, which is an IDENTITY column in the SalesLT.ProductCategory table.
14891. Insert a product
1490Adventure Works has started selling the following new product. Insert it into the SalesLT.Product table, using default or NULL values for unspecified columns:
1491Name ProductNumber StandardCost ListPrice ProductCategoryID SellStartDate
1492LED Lights LT-L123 2.56 12.99 37 <Today>
1493
1494After you have inserted the product, run a query to determine the ProductID that was generated. Then run a query to view the row for the product in the SalesLT.Product table.
14952. Insert a new category with two products
1496Adventure Works is adding a product category for 'Bells and Horns' to its catalog. The parent category for e new category is 4 (Accessories). This new category includes the following two new products:
1497Name ProductNumber StandardCost ListPrice ProductCategoryID SellStartDate
1498Bicycle Bell BB-RING 2.47 4.99 <The new ID for Bells and Horns> <Today>
1499Bicycle Horn BB-PARP 1.29 3.75 <The new ID for Bells and Horns> <Today>
1500
1501Write a query to insert the new product category, and then insert the two new products with the appropriate ProductCategoryID value.
1502After you have inserted the products, query the SalesLT.Product and SalesLT.ProductCategory tables to verify that the data has been inserted.
1503Challenge 2: Updating Products
1504You have inserted data for a products, but the pricing details are not correct. You must now update the records you have previously inserted to reflect the correct pricing.
15051. Update product prices
1506The sales manager at Adventure Works has mandated a 10% price increase for all products in the Bells and Horns category. Update the rows in the SalesLT.Product table for these products to increase their price by 10%.
15072. Discontinue products
1508The new LED lights you inserted in the previous challenge are to replace all previous light products. Update the SalesLT.Product table to set the DiscontinuedDate to today's date for all products in the Lights category (Product Category ID 37) other than the LED Lights product you inserted previously.
1509Challenge 3: Deleting Products
1510The Bells and Horns category has not been successful, and it must be deleted from the database.
15111. Delete a product category and its products
1512Delete the records foe the Bells and Horns category and its products. You must ensure that you delete the records from the tables in the correct order to avoid a foreign-key constraint violation.
1513*/
1514
1515
1516--Solution 1-Insert Products
1517--Insert a product
1518INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)
1519VALUES
1520('LED Lights', 'LT-L123', 2.56, 12.99, 37, GETDATE());
1521
1522SELECT SCOPE_IDENTITY();
1523
1524SELECT * FROM SalesLT.Product
1525WHERE ProductID = SCOPE_IDENTITY();
1526
1527--Insert a new category with two products
1528INSERT INTO SalesLT.ProductCategory (ParentProductCategoryID, Name)
1529VALUES
1530(4, 'Bells and Horns');
1531
1532INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)
1533VALUES
1534('Bicycle Bell', 'BB-RING', 2.47, 4.99, IDENT_CURRENT('SalesLT.ProductCategory'), GETDATE()),
1535('Bicycle Horn', 'BH-PARP', 1.29, 3.75, IDENT_CURRENT('SalesLT.ProductCategory'), GETDATE());
1536
1537SELECT c.Name As Category, p.Name AS Product
1538FROM SalesLT.Product AS p
1539JOIN SalesLT.ProductCategory as c ON p.ProductCategoryID = c.ProductCategoryID
1540WHERE p.ProductCategoryID = IDENT_CURRENT('SalesLT.ProductCategory');
1541
1542
1543--Solution 2-Update Products
1544--Update product prices
1545UPDATE SalesLT.Product
1546SET ListPrice = ListPrice * 1.1
1547WHERE ProductCategoryID =
1548 (SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');
1549
1550--Discontinue products
1551UPDATE SalesLT.Product
1552SET DiscontinuedDate = GETDATE()
1553WHERE ProductCategoryID = 37
1554AND ProductNumber <> 'LT-L123';
1555
1556
1557--Solution 3-Delete Products
1558--Delete a product category and its products
1559DELETE FROM SalesLT.Product
1560WHERE ProductCategoryID =
1561 (SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');
1562
1563DELETE FROM SalesLT.ProductCategory
1564WHERE ProductCategoryID =
1565 (SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');
1566
1567
1568/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
1569
1570
1571--10-Programming with Transact-SQL
1572
1573
1574--Demo 1-Setup
1575IF OBJECT_ID('SalesLT.DemoTable') IS NOT NULL
1576 BEGIN
1577 DROP TABLE SalesLT.DemoTable
1578 END
1579GO
1580
1581
1582CREATE TABLE SalesLT.DemoTable
1583(ID INT IDENTITY(1,1),
1584Description Varchar(20),
1585CONSTRAINT [PK_DemoTable] PRIMARY KEY CLUSTERED(ID)
1586)
1587GO
1588
1589
1590--Demo 2-Variables
1591--Search by city using a variable
1592DECLARE @City VARCHAR(20)='Toronto'
1593Set @City='Bellevue'
1594
1595
1596
1597Select FirstName +' '+LastName as [Name],AddressLine1 as Address,City
1598FROM SalesLT.Customer as C
1599JOIN SalesLT.CustomerAddress as CA
1600ON C.CustomerID=CA.CustomerID
1601JOIN SalesLT.Address as A
1602ON CA.AddressID=A.AddressID
1603WHERE City=@City
1604
1605--Use a variable as an output
1606DECLARE @Result money
1607SELECT @Result=MAX(TotalDue)
1608FROM SalesLT.SalesOrderHeader
1609
1610PRINT @Result
1611
1612
1613--Demo 3-If Else
1614--Simple logical test
1615If 'Yes'='Yes'
1616Print 'True'
1617
1618--Change code based on a condition
1619UPDATE SalesLT.Product
1620SET DiscontinuedDate=getdate()
1621WHERE ProductID=1;
1622
1623IF @@ROWCOUNT<1
1624BEGIN
1625 PRINT 'Product was not found'
1626END
1627ELSE
1628BEGIN
1629 PRINT 'Product Updated'
1630END
1631
1632
1633--Demo 4-While
1634DECLARE @Counter int=1
1635
1636WHILE @Counter <=5
1637
1638BEGIN
1639 INSERT SalesLT.DemoTable(Description)
1640 VALUES ('ROW '+CONVERT(varchar(5),@Counter))
1641 SET @Counter=@Counter+1
1642END
1643
1644SELECT Description FROM SalesLT.DemoTable
1645
1646
1647--Testing for existing values
1648DECLARE @Counter int=1
1649
1650DECLARE @Description int
1651SELECT @Description=MAX(ID)
1652FROM SalesLT.DemoTable
1653
1654WHILE @Counter <5
1655BEGIN
1656 INSERT SalesLT.DemoTable(Description)
1657 VALUES ('ROW '+CONVERT(varchar(5),@Description))
1658 SET @Description=@Description+1
1659 SET @Counter=@Counter+1
1660END
1661
1662SELECT Description FROM SalesLT.DemoTable
1663
1664
1665--Demo 5-Stored Procedure
1666--Create a stored procedure
1667CREATE PROCEDURE SalesLT.GetProductsByCategory (@CategoryID INT = NULL)
1668AS
1669IF @CategoryID IS NULL
1670 SELECT ProductID, Name, Color, Size, ListPrice
1671 FROM SalesLT.Product
1672ELSE
1673 SELECT ProductID, Name, Color, Size, ListPrice
1674 FROM SalesLT.Product
1675 WHERE ProductCategoryID = @CategoryID;
1676
1677
1678--Execute the procedure without a parameter
1679EXEC SalesLT.GetProductsByCategory
1680
1681--Execute the procedure with a parameter
1682EXEC SalesLT.GetProductsByCategory 6
1683
1684
1685
1686/*
1687Challenge 1: Creating scripts to insert sales orders
1688You want to create reusable scripts that make it easy to insert sales orders. You plan to create a script to insert the order header record, and a separate script to insert order detail records for a specified order header. Both scripts will make use of variables to make them easy to reuse.
16891. Write code to insert an order header
1690Your script to insert an order header must enable users to specify values for the order date, due date, and customer ID. The SalesOrderID should be generated from the next value for the SalesLT.SalesOrderNumber sequence and assigned to a variable. The script should then insert a record into the SalesLT.SalesOrderHeader table using these values and a hard-coded value of 'CARGO TRANSPORT 5' for the shipping method with default or NULL values for all other columns.
1691After the script has inserted the record, it should display the inserted SalesOrderID using the PRINT command.
1692Test your code with the following values:
1693Order Date Due Date Customer ID
1694Today's date 7 days from now 1
1695Note: Support for Sequence objects was added to Azure SQL Database in version 12, which became available in some regions in February 2015. If you are using the previous version of Azure SQL database (and the corresponding previous version of the AdventureWorksLT sample database), you will need to adapt your code to insert the sales order header without specifying the SalesOrderID (which is an IDENTITY column in older versions of the sample database), and then assign the most recently generated identity value to the variable you have declared.
16962. Write code to insert an order detail
1697The script to insert an order detail must enable users to specify a sales order ID, a product ID, a quantity, and a unit price. It must then check to see if the specified sales order ID exists in the SalesLT.SalesOrderHeader table. If it does, the code should insert the order details into the SalesLT.SalesOrderDetail table (using default values or NULL for unspecified columns). If the sales order ID does not exist in the SalesLT.SalesOrderHeader table, the code should print the message 'The order does not exist'. You can test for the existence of a record by using the EXISTS predicate.
1698Test your code with the following values:
1699Sales Order ID Product ID Quantity Unit Price
1700The sales order ID returned by your previous code to insert a sales order header. 760 1 782.99
1701
1702Then test it again with the following values:
1703Sales Order ID Product ID Quantity Unit Price
17040 760 1 782.99
1705
1706Challenge 2: Updating Bike Prices
1707Adventure Works has determined that the market average price for a bike is $2,000, and consumer research has indicated that the maximum price any customer would be likely to pay for a bike is $5,000. You must write some Transact-SQL logic that incrementally increases the list price for all bike products by 10% until the average list price for a bike is at least the same as the market average, or until the most expensive bike is priced above the acceptable maximum indicated by the consumer research.
17081. Write a WHILE loop to update bike prices
1709The loop should:
1710• Execute only if the average list price of a product in the 'Bikes' parent category is less than the market average. Note that the product categories in the Bikes parent category can be determined from the SalesLT.vGetAllCategories view.
1711• Update all products that are in the 'Bikes' parent category, increasing the list price by 10%.
1712• Determine the new average and maximum selling price for products that are in the 'Bikes' parent category.
1713• If the new maximum price is greater than or equal to the maximum acceptable price, exit the loop; otherwise continue.
1714*/
1715
1716
1717--Solution 1-Insert Sales Order Script
1718--Insert sales order header
1719DECLARE @OrderDate datetime = GETDATE();
1720DECLARE @DueDate datetime = DATEADD(dd, 7, GETDATE());
1721DECLARE @CustomerID int = 1;
1722DECLARE @OrderID int;
1723
1724SET @OrderID = NEXT VALUE FOR SalesLT.SalesOrderNumber;
1725
1726INSERT INTO SalesLT.SalesOrderHeader (SalesOrderID, OrderDate, DueDate, CustomerID, ShipMethod)
1727VALUES
1728(@OrderID, @OrderDate, @DueDate, @CustomerID, 'CARGO TRANSPORT 5');
1729
1730PRINT @OrderID;
1731
1732--Insert sales order details
1733DECLARE @SalesOrderID int
1734DECLARE @ProductID int = 760;
1735DECLARE @Quantity int = 1;
1736DECLARE @UnitPrice money = 782.99;
1737
1738SET @SalesOrderID = 0; --test with the order ID generated for the sales order header inserted above
1739
1740IF EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID)
1741BEGIN
1742 INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice)
1743 VALUES
1744 (@SalesOrderID, @Quantity, @ProductID, @UnitPrice)
1745END
1746ELSE
1747BEGIN
1748 PRINT 'The order does not exist'
1749END
1750
1751
1752--Solution 2-Update Bike Prices
1753DECLARE @MarketAverage money = 2000;
1754DECLARE @MarketMax money = 5000;
1755DECLARE @AWMax money;
1756DECLARE @AWAverage money;
1757
1758SELECT @AWAverage = AVG(ListPrice), @AWMax = MAX(ListPrice)
1759FROM SalesLT.Product
1760WHERE ProductCategoryID IN
1761 (SELECT DISTINCT ProductCategoryID
1762 FROM SalesLT.vGetAllCategories
1763 WHERE ParentProductCategoryName = 'Bikes');
1764
1765WHILE @AWAverage < @MarketAverage
1766BEGIN
1767 UPDATE SalesLT.Product
1768 SET ListPrice = ListPrice * 1.1
1769 WHERE ProductCategoryID IN
1770 (SELECT DISTINCT ProductCategoryID
1771 FROM SalesLT.vGetAllCategories
1772 WHERE ParentProductCategoryName = 'Bikes');
1773
1774 SELECT @AWAverage = AVG(ListPrice), @AWMax = MAX(ListPrice)
1775 FROM SalesLT.Product
1776 WHERE ProductCategoryID IN
1777 (SELECT DISTINCT ProductCategoryID
1778 FROM SalesLT.vGetAllCategories
1779 WHERE ParentProductCategoryName = 'Bikes');
1780
1781 IF @AWMax >= @MarketMax
1782 BREAK
1783 ELSE
1784 CONTINUE
1785END
1786PRINT 'New average bike price:' + CONVERT(varchar, @AWAverage);
1787PRINT 'New maximum bike price:' + CONVERT(varchar, @AWMax);
1788
1789
1790/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/
1791
1792
1793--11-Error Handling and Transactions
1794
1795
1796--Demo 1-Raising Errors
1797--View a system error
1798INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount)
1799VALUES
1800(100000, 1, 680, 1431.50, 0.00);
1801
1802--Raise an error with RAISERROR
1803UPDATE SalesLT.Product
1804SET DiscontinuedDate = GETDATE()
1805WHERE ProductID = 0;
1806
1807IF @@ROWCOUNT < 1
1808 RAISERROR('The product was not found - no products have been updated', 16, 0);
1809
1810--Raise an error with THROW
1811UPDATE SalesLT.Product
1812SET DiscontinuedDate = GETDATE()
1813WHERE ProductID = 0;
1814
1815IF @@ROWCOUNT < 1
1816 THROW 50001, 'The product was not found - no products have been updated', 0;
1817
1818
1819--Demo 2-Handling Errors
1820--catch an error
1821BEGIN TRY
1822 UPDATE SalesLT.Product
1823 SET ProductNumber = ProductID / ISNULL(Weight, 0);
1824END TRY
1825BEGIN CATCH
1826 PRINT 'The following error occurred:';
1827 PRINT ERROR_MESSAGE();
1828END CATCH;
1829
1830--Catch and rethrow
1831BEGIN TRY
1832 UPDATE SalesLT.Product
1833 SET ProductNumber = ProductID / ISNULL(Weight, 0);
1834END TRY
1835BEGIN CATCH
1836 PRINT 'The following error occurred:';
1837 PRINT ERROR_MESSAGE();
1838 THROW;
1839END CATCH;
1840
1841--Catch, log, and throw a custom error
1842BEGIN TRY
1843 UPDATE SalesLT.Product
1844 SET ProductNumber = ProductID / ISNULL(Weight, 0);
1845END TRY
1846BEGIN CATCH
1847 DECLARE @ErrorLogID as int, @ErrorMsg AS varchar(250);
1848 EXECUTE dbo.uspLogError @ErrorLogID OUTPUT;
1849 SET @ErrorMsg = 'The update failed because of an error. View error #'
1850 + CAST(@ErrorLogID AS varchar)
1851 + ' in the error log for details.';
1852 THROW 50001, @ErrorMsg, 0;
1853END CATCH;
1854
1855--View the error log
1856SELECT * FROM dbo.ErrorLog;
1857
1858
1859--Demo 3-Transactions
1860--No transaction
1861BEGIN TRY
1862 INSERT INTO SalesLT.SalesOrderHeader (DueDate, CustomerID, ShipMethod)
1863 VALUES
1864 (DATEADD(dd, 7, GETDATE()), 1, 'STD DELIVERY');
1865
1866 DECLARE @SalesOrderID int = SCOPE_IDENTITY();
1867
1868 INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount)
1869 VALUES
1870 (@SalesOrderID, 1, 99999, 1431.50, 0.00);
1871END TRY
1872BEGIN CATCH
1873 PRINT ERROR_MESSAGE();
1874END CATCH;
1875
1876--View orphaned orders
1877SELECT h.SalesOrderID, h.DueDate, h.CustomerID, h.ShipMethod, d.SalesOrderDetailID
1878FROM SalesLT.SalesOrderHeader AS h
1879LEFT JOIN SalesLT.SalesOrderDetail AS d
1880ON d.SalesOrderID = h.SalesOrderID
1881WHERE D.SalesOrderDetailID IS NULL;
1882
1883--Manually delete orphaned record
1884DELETE FROM SalesLT.SalesOrderHeader
1885WHERE SalesOrderID = SCOPE_IDENTITY();
1886
1887--Use a transaction
1888BEGIN TRY
1889 BEGIN TRANSACTION
1890 INSERT INTO SalesLT.SalesOrderHeader (DueDate, CustomerID, ShipMethod)
1891 VALUES
1892 (DATEADD(dd, 7, GETDATE()), 1, 'STD DELIVERY');
1893
1894 DECLARE @SalesOrderID int = SCOPE_IDENTITY();
1895
1896 INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount)
1897 VALUES
1898 (@SalesOrderID, 1, 99999, 1431.50, 0.00);
1899 COMMIT TRANSACTION
1900END TRY
1901BEGIN CATCH
1902 IF @@TRANCOUNT > 0
1903 BEGIN
1904 PRINT XACT_STATE();
1905 ROLLBACK TRANSACTION;
1906 END
1907 PRINT ERROR_MESSAGE();
1908 THROW 50001,'An insert failed. The transaction was cancelled.', 0;
1909END CATCH;
1910
1911--Check for orphaned orders
1912SELECT h.SalesOrderID, h.DueDate, h.CustomerID, h.ShipMethod, d.SalesOrderDetailID
1913FROM SalesLT.SalesOrderHeader AS h
1914LEFT JOIN SalesLT.SalesOrderDetail AS d
1915ON d.SalesOrderID = h.SalesOrderID
1916WHERE D.SalesOrderDetailID IS NULL
1917
1918--Use XACT_ABORT
1919SET XACT_ABORT ON;
1920BEGIN TRY
1921 BEGIN TRANSACTION
1922 INSERT INTO SalesLT.SalesOrderHeader (DueDate, CustomerID, ShipMethod)
1923 VALUES
1924 (DATEADD(dd, 7, GETDATE()), 1, 'STD DELIVERY');
1925
1926 DECLARE @SalesOrderID int = SCOPE_IDENTITY();
1927
1928 INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount)
1929 VALUES
1930 (@SalesOrderID, 1, 99999, 1431.50, 0.00);
1931 COMMIT TRANSACTION
1932END TRY
1933BEGIN CATCH
1934 PRINT ERROR_MESSAGE();
1935 THROW 50001,'An insert failed. The transaction was cancelled.', 0;
1936END CATCH;
1937SET XACT_ABORT OFF;
1938
1939--Check for orphaned orders
1940SELECT h.SalesOrderID, h.DueDate, h.CustomerID, h.ShipMethod, d.SalesOrderDetailID
1941FROM SalesLT.SalesOrderHeader AS h
1942LEFT JOIN SalesLT.SalesOrderDetail AS d
1943ON d.SalesOrderID = h.SalesOrderID
1944WHERE D.SalesOrderDetailID IS NULL
1945
1946
1947/*
1948Challenge 1: Logging Errors
1949You are implementing a Transact-SQL script to delete orders, and you want to handle any errors that occur during the deletion process.
19501. Throw an error for non-existent orders
1951You are currently using the following code to delete order data:
1952DECLARE @SalesOrderID int = <the_order_ID_to_delete>
1953DELETE FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @SalesOrderID;
1954DELETE FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID;
1955This code always succeeds, even when the specified order does not exist. Modify the code to check for the existence of the specified order ID before attempting to delete it. If the order does not exist, your code should throw an error. Otherwise, it should go ahead and delete the order data.
19562. Handle errors
1957Your code now throws an error if the specified order does not exist. You must now refine your code to catch this (or any other) error and print the error message to the user interface using the PRINT command.
1958Challenge 2: Ensuring Data Consistency
1959You have implemented error handling logic in some Transact-SQL code that deletes order details and order headers. However, you are concerned that a failure partway through the process will result in data inconsistency in the form of undeleted order headers for which the order details have been deleted.
19601. Implement a transaction
1961Enhance the code you created in the previous challenge so that the two DELETE statements are treated as a single transactional unit of work. In the error handler, modify the code so that if a transaction is in process, it is rolled back and the error is re-thrown to the client application. If not transaction is in process the error handler should continue to simply print the error message.
1962To test your transaction, add a THROW statement between the two DELETE statements to simulate an unexpected error. When testing with a valid, existing order ID, the error should be re-thrown by the error handler and no rows should be deleted from either table.
1963*/
1964
1965
1966--Solution 1-Logging Errors
1967DECLARE @SalesOrderID int = 0
1968
1969--uncomment the following line to delete an existing record
1970--SELECT @SalesOrderID = MIN(SalesOrderID) FROM SalesLT.SalesOrderHeader;
1971
1972BEGIN TRY
1973 IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader
1974 WHERE SalesOrderID = @SalesOrderID)
1975 BEGIN
1976 --Throw a custom error if the specified order doesn't exist
1977 DECLARE @error varchar(25);
1978 SET @error = 'Order #' + cast(@SalesOrderID as varchar) + ' does not exist';
1979 THROW 50001, @error, 0
1980 END
1981 ELSE
1982 BEGIN
1983 DELETE FROM SalesLT.SalesOrderDetail
1984 WHERE SalesOrderID = @SalesOrderID;
1985
1986 DELETE FROM SalesLT.SalesOrderHeader
1987 WHERE SalesOrderID = @SalesOrderID;
1988 END
1989END TRY
1990BEGIN CATCH
1991 --Catch and print the error
1992 PRINT ERROR_MESSAGE();
1993END CATCH
1994
1995
1996--Solution 2-Ensuring Data Consistency
1997DECLARE @SalesOrderID int = 0
1998
1999--uncomment the following line to specify an existing order
2000--SELECT @SalesOrderID = MIN(SalesOrderID) FROM SalesLT.SalesOrderHeader;
2001
2002BEGIN TRY
2003 IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader
2004 WHERE SalesOrderID = @SalesOrderID)
2005 BEGIN
2006 --Throw a custom error if the specified order doesn't exist
2007 DECLARE @error varchar(25);
2008 SET @error = 'Order #' + cast(@SalesOrderID as varchar) + ' does not exist';
2009 THROW 50001, @error, 0
2010 END
2011 ELSE
2012 BEGIN
2013 BEGIN TRANSACTION
2014 DELETE FROM SalesLT.SalesOrderDetail
2015 WHERE SalesOrderID = @SalesOrderID;
2016
2017 --THROW 50001, 'Unexpected error', 0 --Uncomment to test transaction
2018
2019 DELETE FROM SalesLT.SalesOrderHeader
2020 WHERE SalesOrderID = @SalesOrderID;
2021 COMMIT TRANSACTION
2022 END
2023END TRY
2024BEGIN CATCH
2025 IF @@TRANCOUNT > 0
2026 BEGIN
2027 --Rollback the transaction and re-throw the error
2028 ROLLBACK TRANSACTION;
2029 THROW;
2030 END
2031 ELSE
2032 BEGIN
2033 --Report the error
2034 PRINT ERROR_MESSAGE();
2035 END
2036END CATCH
2037
2038
2039/*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************/