10774AD ENU LabHandbook 651 814
10774AD ENU LabHandbook 651 814
10774AD ENU LabHandbook 651 814
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
2.
3.
4.
5.
Open the Object Explorer window by selecting Object Explorer on the View menu (or press F8 on
the keyboard).
6.
Open the Solution Explorer window by selecting Solution Explorer on the View menu (or press
Ctrl+Alt+L on the keyboard).
On the Tools menu, select Options to open the Options window in SSMS.
2.
Expand the Environment option and select Fonts and Colors. In the Show settings for box, select
Text Editor and set the font size in the Size box to 14.
3.
In the left pane, expand the Text Editor option, expand the Transact-SQL option, and select
IntelliSense. Under Transact-SQL IntelliSense Settings, clear the Enable IntelliSense check box.
4.
In the left pane, select the Tabs option under Text Editor and Transact-SQL. In the Tab frame,
change the Tab size property to 6.
5.
In the left pane, expand Query Results, expand SQL Server, and select Results to Grid. Enable the
option Include column headers when copying or saving the results by selecting the check box.
6.
2.
In the New Project window, type MyFirstProject in the Name textbox and
F:\10774A_Labs\10774A_01_PRJ in the Location text box. Click the OK button to create the new
project.
3.
In the Solution Explorer window, right-click the Queries folder under MyFirstProject and select
New Query.
4.
Right-click the query file SQLQuery1.sql under the Queries folder, choose Rename, and type
MyFirstQueryFile.sql as the new name for the file.
5.
In the Solution Explorer window, right-click the Queries folder under MyFirstProject and select
New Query.
2.
In the Queries folder, right-click the query file SQLQuery1.sql, choose Rename, and type
MySecondQueryFile.sql as the new name for the file.
3.
Open Windows Explorer by clicking Start, expanding All Programs, expanding Accessories, and
clicking Windows Explorer.
4.
5.
In the Solution Explorer window in SSMS, right-click the query file MySecondQueryFile.sql and
select Remove. When the confirmation dialog appears, click the Remove button.
6.
In Windows Explorer, press F5 to refresh the Windows Explorer window and notice that the file
MySecondQueryFile.sql is still there.
7.
In the Solution Explorer window in SSMS, right-click the query file MyFirstQueryFile.sql and select
Remove. When the confirmation dialog appears, click the Delete button.
8.
In Windows Explorer, press F5 to refresh the Windows Explorer window. Notice that the file
MyFirstQueryFile.sql was deleted from the file system.
2.
On the File menu, select Exit to close the project and SSMS.
3.
Click Start, expand All Programs, expand Microsoft SQL Server 2012, and click SQL Server
Management Studio.
4.
5.
On the File menu, click Open and click Project / Solution. In the Open Project window, select the
project F:\10774A_Labs\10774A_01_PRJ\MyFirstProject\MyFirstProject.ssmssln.
6.
Click Open.
7.
8.
On the virtual machine, click Start, expand All Programs, expand Microsoft SQL Server 2012,
expand Documentation & Community, and click SQL Server Documentation.
2.
In the Microsoft Help Viewer window, click the Help Library Manager icon.
3.
In the Help Library Manager window, select Choose online or local help.
4.
Under Set your preferred help experience, click I want to use local help and click the OK button
to confirm.
5.
Click the Exit button to leave the Help Library Manager window.
2.
Type SELECT in the text box on the top left side, then find and click the entry SELECT statement
[SQL Server].
3.
Browse the SELECT statements definition. Click the SELECT Examples (Transact-SQL) link under
Reference in the See Also section. Under the topic A. Using SELECT to retrieve rows and columns,
click the link Copy to Clipboard on the Transact-SQL tab.
4.
In the Solution Explorer window in SSMS, right-click the Queries folder under MyFirstProject and
select New Query.
5.
In the Queries folder, right-click the query file SQLQuery1.sql, choose Rename, and type
MyThirdQueryFile.sql as the new name for the file.
6.
Click inside the query window of the file MyThirdQueryFile.sql and select Paste on the Edit menu.
Close Microsoft Help Viewer by clicking the close icon for the window.
2.
In the query window in SSMS, highlight the ORDER BY clause in the file MyThirdQueryFile.sql and
press the F1 key.
3.
4.
5.
L3-7
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
13. On the File menu, click Open and click Project / Solution. In the Project Open window, select
project F:\10774A_Labs\10774A_03_PRJ\10774A_03_PRJ.ssmssln.
14. Create and populate database TSQL2012:
For on-premise, database TSQL2012 is already created and populated in the VM so no further
steps are needed. However, if the database was damaged and you would like to create it from
scratch, follow the steps below.
For Microsoft SQL Azure, follow the steps below if you havent done so already in module 2.
15. In Solution Explorer, double-click 00 - Setup.sql. (If Solution Explorer is not visible, select Solution
Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
16. When the query window opens, follow the instructions inside the 00 Setup.sql to setup the
TSQL2012 database and populate it with sample data.
17. Close SQL Server Management Studio.
On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
2.
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
3.
Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database. If the TSQL2012 database is not visible please look at the
steps 10 to 16 under Lab Setup.
4.
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
5.
On the File menu, click Open and click Project / Solution. In the Project Open window, select
project F:\10774A_Labs\10774A_03_PRJ\10774A_03_PRJ.ssmssln.
6.
In Solution Explorer, double-click 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible, select
Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
When the query window opens, click the Execute button on the toolbar (or press F5 on the
keyboard).
2.
If you are using SQL Azure you will get an error, because the USE statement is not supported and you
must manually set the database context using the Available Databases box.
3.
For on-premises Microsoft SQL Server you will notice that the TSQL2012 database is selected in the
Available Databases box. The Available Databases box displays the current database context under
which the T-SQL script will run. This information is also visible on the status bar.
L3-10
To highlight it, you can move the pointer over the statement while pressing the left mouse button or
use the arrow keys to move the pointer while pressing the Shift key.
2.
Click Execute (or press F5). It is very important to understand that you can highlight a specific part of
the code inside the T-SQL script and execute only that part. If you click Execute without selecting any
part of the code, the whole T-SQL script will be executed. If you highlight a specific part of the code
by mistake, the SQL Server will attempt to run only that part.
2.
On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
3.
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
4.
Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database. If the TSQL2012 database is not visible please look at the
steps 10 to 16 under Lab Setup.
5.
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
6.
On the File menu, click Open and click Project / Solution. In the Project Open window, select
project F:\10774A_Labs\10774A_03_PRJ\10774A_03_PRJ.ssmssln.
7.
8.
9.
Task 2: Apply the needed changes and execute the T-SQL script
1.
In the Available Databases box, select TSQL2012 to change the database context.
2.
Click Execute.
3.
Notice that the result from the SELECT statement returns fewer rows than the one in exercise 1. That
is because it has a predicate in the WHERE clause to filter out all rows that do not have the value USA
in the column country. Only rows for which the logical expression evaluates to TRUE are returned by
the WHERE phase to the subsequent logical query processing phase.
L3-12
2.
Delete the first two characters, so that the line looks like this:
USE TSQL2012;
By deleting these two characters, you have removed the comment mark. Now the line will not be
ignored by SQL Server.
3.
4.
On the File menu, click Close. This will close the T-SQL script.
5.
6.
Click Execute. Again notice that you got an error if using SQL Azure.
7.
If using on-premises SQL Server, observe the results. Why did the script executed with no errors? The
script now includes the uncommented USE TSQL2012; statement. When you execute the whole T-SQL
script, the USE statement applies the database context to the TSQL2012 database. The next statement
in the T-SQL script then executes against the TSQL2012 database.
2.
Click Execute.
3.
Notice that the result window is empty. All the statements inside the T-SQL script are commented
out, so SQL Server ignores all the statements inside the T-SQL script.
2.
Delete the two characters before the USE statement. The line should now look like this:
USE TSQL2012;
3.
Locate the block comment start element /* after the task 1 description and delete it.
4.
Locate the block comment end element */ and delete it. Any text residing within a block starting with
/* and ending with */ is treated as a block comment and is ignored by SQL Server.
5.
Click Execute. The database context is now changed to the TSQL2012 database.
6.
Click Execute.
7.
Observe the result and notice that the rows are sorted by the lastname column in ascending order.
L3-14
L4-15
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, click the Cancel button.
L4-16
In Object Explorer, click Connect and select Database Engine. (If Object Explorer is not visible, select
Object Explorer on the View menu or press F8 on the keyboard.)
2.
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box (ask your
instructor for a current list of Microsoft SQL Azure enabled labs).
3.
Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
4.
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
5.
In Object Explorer, expand the server Proseware (if using an on-premises Microsoft SQL Server
instance) or expand the Azure SQL server, expand Databases, expand the database TSQL2012, and
expand Tables.
6.
Under Tables, notice that there are four table objects in the Sales schema:
Sales.Customers
Sales.OrderDetails
Sales.Orders
Sales.Shippers
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
L4-17
It is very important to understand that you can highlight a specific part of the code inside the T-SQL
script and execute it. If you click Execute on the toolbar without selecting any part of the code, the
whole T-SQL script will be executed.
Tip One way to highlight a portion of code is to hold down the Alt key while drawing a
rectangle around it with your mouse. The code inside the drawn rectangle will be selected.
5.
In the query pane, type the following query after the task 2 description:
SELECT
*
FROM Sales.Customers;
Note that when writing production queries, the best practice is to avoid using the * argument to
return all columns and instead specify only the needed columns. However, since you are learning to
write queries, it is fine to use * for your first query. Later on, you should use * only when you are
doing some testing or ad-hoc querying. You should never use * in production code.
One way to enumerate all columns using SQL Server Management Studio is to expand the SQL2012
database in Object Explorer, expand the Sales.Customers table, drag the Columns folder into the
query window, and drop it right after the SELECT clause. Your query would then look like this:
SELECT
custid, companyname, contactname, contacttitle, address, city, region, postalcode,
country, phone, fax
FROM Sales.Customers;
6.
7.
In the query pane, type the following code after the first query:
SELECT
*
FROM
8.
In Object Explorer, select the Sales.Customers table under Proseware (if using an on-premises
Microsoft SQL Server instance) or under connected Azure SQL server, TSQL2012, Tables. Using the
mouse, drag the selected table to the query pane and drop it after the FROM clause. Add a semicolon
to the end of the SELECT statement. It is important to terminate all of your T-SQL statements with a
semicolon. This is considered a best practice, is a requirement of standard SQL, and will likely become
mandatory for all T-SQL statements in a future version of Microsoft SQL Server. Your result should
look like this:
SELECT
*
FROM [Sales].[Customers];
9.
L4-18
In Object Explorer, expand the Sales.Customers table under Proseware (if using an on-premises
Microsoft SQL Server instance) or under connected Azure SQL server, TSQL2012, Tables.
2.
Expand Columns and observe all the columns in the Sales.Customers table.
3.
In the query pane, type the following query after the task 3 description:
SELECT
contactname, address, postalcode, city, country
FROM Sales.Customers;
4.
5.
Observe the result. How many rows are affected by the last query? There are multiple ways to answer
this question using SQL Server Management Studio. One way is to select the previous query and click
Execute. The total number of rows affected by the executed query is written in the Results pane
under the Messages tab:
(91 row(s) affected)
Another way is to look at the status bar displayed below the Results pane. On the left side of the
status bar, there is a message stating Query executed successfully. On the right side, the total
number of rows affected by the current query is displayed (91 rows).
L4-19
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
country
FROM Sales.Customers;
4.
5.
Observe that you have multiple rows with the same values. This occurs because the Sales.Customers
table has multiple rows with the same value for the country column.
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 2 description. On the toolbar, click Edit and then
Paste. You have now copied the previous query to the same query window after the task 2
description.
3.
Modify the query by typing DISTINCT after the SELECT clause. Your query should look like this:
SELECT DISTINCT
country
FROM Sales.Customers;
4.
5.
Under which circumstances do the following queries against the Sales.Customers table return the
same result?
SELECT city, region FROM Sales.Customers;
SELECT DISTINCT city, region FROM Sales.Customers;
Both queries would return the same number of rows if all combinations of values in the city and
region columns in the Sales.Customers table are unique. If they are not unique, the first query
would return more rows than the second one with the DISTINCT clause.
L4-20
Is the DISTINCT clause applied to all columns specified in the query or just the first column?
The DISTINCT clause is always applied to all columns specified in the SELECT list. It is very
important to remember that the DISTINCT clause does not apply to just the first column in the
list.
L4-21
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
c.contactname, c.contacttitle
FROM Sales.Customers AS c;
Tip To use the IntelliSense feature when entering column names in a SELECT statement,
you can use keyboard shortcuts. To enable IntelliSense, press Ctrl+Q+I. To list all of the alias
members, position your pointer after the alias and dot (e.g., after c.) and press Ctrl+J.
4.
In the query pane, type the following query after the task 2 description:
SELECT
c.contactname AS Name, c.contacttitle AS Title, c.companyname AS [Company Name]
FROM Sales.Customers AS c;
Observe that the column alias [Company Name] is enclosed in square brackets. Column names and
column aliases that have embedded spaces or reserved keywords must be delimited. This example
uses square brackets as the delimiter, but you can also use the ANSI SQL standard delimiter of double
quotes, as in Company Name.
2.
Task 3: Write a SELECT statement that uses a table alias and a column alias
1.
In the query pane, type the following query after the task 3 description:
SELECT
p.productname AS [Product Name]
FROM Production.Products AS p;
2.
L4-22
Highlight the written query under the task 4 description and click Execute.
2.
Observe the result. Note that only one column is retrieved. The problem is that the developer forgot
to add a comma after the first column name, so SQL Server treated the second word after the first
column name as an alias. For this reason, it is a best practice to always use AS when specifying aliases.
That way, it is easier to spot such errors.
3.
Correct the query by adding a comma after the first column name. The corrected query should look
like this:
SELECT
city, country
FROM Sales.Customers;
L4-23
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
p.categoryid, p.productname
FROM Production.Products AS p;
4.
In the query pane, type the following query after the task 2 description:
SELECT
p.categoryid, p.productname,
CASE
WHEN p.categoryid = 1
WHEN p.categoryid = 2
WHEN p.categoryid = 3
WHEN p.categoryid = 4
WHEN p.categoryid = 5
WHEN p.categoryid = 6
WHEN p.categoryid = 7
WHEN p.categoryid = 8
ELSE 'Other'
END AS categoryname
FROM Production.Products AS p;
THEN
THEN
THEN
THEN
THEN
THEN
THEN
THEN
'Beverages'
'Condiments'
'Confections'
'Dairy Products'
'Grains/Cereals'
'Meat/Poultry'
'Produce'
'Seafood'
This query uses a CASE expression to add a new column. Note that when you have a dynamic list of
possible values, you usually store them in a separate table. However, for this example, a static list of
values is being supplied.
2.
L4-24
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 3 description. On the toolbar, click Edit and then
Paste. You have now copied the previous query to the same query window after the task 3
description.
3.
Add a new column using an additional CASE expression. Your query should look like this:
SELECT
p.categoryid, p.productname,
CASE
WHEN p.categoryid = 1 THEN 'Beverages'
WHEN p.categoryid = 2 THEN 'Condiments'
WHEN p.categoryid = 3 THEN 'Confections'
WHEN p.categoryid = 4 THEN 'Dairy Products'
WHEN p.categoryid = 5 THEN 'Grains/Cereals'
WHEN p.categoryid = 6 THEN 'Meat/Poultry'
WHEN p.categoryid = 7 THEN 'Produce'
WHEN p.categoryid = 8 THEN 'Seafood'
ELSE 'Other'
END AS categoryname,
CASE
WHEN p.categoryid IN (1, 7, 8) THEN 'Campaign Products'
ELSE 'Non-Campaign Products'
END AS iscampaign
FROM Production.Products AS p;
4.
5.
In the result, observe that the first CASE expression uses the simple form whereas the second uses the
searched form.
L5-25
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
L5-26
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L5-27
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
In the query pane, type the following query after the task 1 description:
SELECT
p.productname, c.categoryname
FROM Production.Products AS p
INNER JOIN Production.Categories AS c ON p.categoryid = c.categoryid;
6.
7.
Which column did you specify as a predicate in the ON clause of the join? Why?
In this query, the categoryid column is the predicate. By intuition, most people would say that it
is the predicate because this column exists in both input tables. By the way, using the same name
for columns that contain the same data but in different tables is a good practice in data
modeling. Another possibility is to check for referential integrity trough primary and foreign key
information using SQL Server Management Studio. If there are no primary or foreign key
constraints, you will have to acquire information about the data model from the developer.
Let us say that there is a new row in the Production.Categories table and this new product
category does not have any products associated with it in the Production.Products table. Would
this row be included in the result of the SELECT statement written under the task 1 description?
No, because an inner join retrieves only the matching rows based on the predicate from both
input tables. Since the new value for the categoryid is not present in the categoryid column in the
Production.Products table, there would be no matching rows in the result of the SELECT
statement.
L5-28
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
Highlight the written query under the task 1 description and click Execute.
4.
Task 2: Apply the needed changes and execute the T-SQL statement
1.
Add the column prefix Customers to the existing query so that it looks like this:
SELECT
Customers.custid, contactname, orderid
FROM Sales.Customers
INNER JOIN Sales.Orders ON Customers.custid = Orders.custid;
2.
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 3 description. On the toolbar, click Edit and then
Paste. You have now copied the previous query to the same query window after the task 3
description.
3.
Modify the T-SQL statement to use table aliases. Your query should look like this:
SELECT
c.custid, c.contactname, o.orderid
FROM Sales.Customers AS c
INNER JOIN Sales.Orders AS o ON c.custid = o.custid;
4.
Modify the T-SQL statement to include a full source table name as the column prefix. Your query
should now look like this:
SELECT
Customers.custid, Customers.contactname, Orders.orderid
FROM Sales.Customers AS c
INNER JOIN Sales.Orders AS o ON c.custid = o.custid;
5.
6.
L5-29
7.
Modify the SELECT statement so that it uses the correct table aliases. Your query should look like this:
SELECT
c.custid, c.contactname, o.orderid
FROM Sales.Customers AS c
INNER JOIN Sales.Orders AS o ON c.custid = o.custid;
In the query pane, type the following query after the task 4 description:
SELECT
c.custid, c.contactname, o.orderid, d.productid, d.qty, d.unitprice
FROM Sales.Customers AS c
INNER JOIN Sales.Orders AS o ON c.custid = o.custid
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid;
2.
3.
Observe the result. Remember that when you have a multiple-table inner join, the logical query
processing is different from the physical implementation. In this case, it means that you cannot
guarantee the order in which the SQL Server optimizer will process the tables. For example, you
cannot guarantee that the Sales.Customers table will be joined first with the Sales.Orders table and
then with the Sales.OrderDetails table.
L5-30
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid
FROM HR.Employees AS e;
4.
5.
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 2 description. On the toolbar, click Edit and then
Paste. You have now copied the previous query to the same query window after the task 2
description.
3.
Modify the query by adding a self-join to get information about the managers. The query should look
like this:
SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid,
m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
INNER JOIN HR.Employees AS m ON e.mgrid = m.empid;
4.
5.
Observe that the query retrieved eight rows and answer these questions:
Is it mandatory to use table aliases when writing a statement with a self-join? Can you use a full
source table name as an alias?
You must use table aliases. You cannot use the full source table name as an alias when
referencing both input tables. Eventually, you could use a full source table name as an alias for
one input table and some other alias for the second input table.
Why did you get fewer rows in the result from the T-SQL statement under the task 2 description
compared to result from the T-SQL statement under the task 1 description?
In task 2s T-SQL statement, the inner join used an ON clause based on manager information
(column mgrid). The employee who is the CEO has a missing value in the mgrid column. Thus,
this row is not included in the result.
L5-31
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
c.custid, c.contactname, o.orderid
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o ON c.custid = o.custid;
4.
5.
Inspect the result. Notice that the custid 22 and custid 57 rows have a missing value in the orderid
column. This is because there are no rows in the Sales.Orders table for these two values of the custid
column. In business terms, this means that there are currently no orders for these two customers.
L5-32
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
Highlight the T-SQL code under the task 1 description and click Execute. Do not worry if you do not
understand the provided T-SQL code, as it is used here to provide a more realistic example for a cross
join in the next task.
In the query pane, type the following query after task 2 description:
SELECT
e.empid, e.firstname, e.lastname, c.calendardate
FROM HR.Employees AS e
CROSS JOIN HR.Calendar AS c;
2.
3.
Observe that the query retrieved 3285 rows and that there are 9 rows in the HR.Employees table.
Because a cross join produces a Cartesian product of both inputs, it means that there are 365 (3285/9)
rows in the HR.Calendar table.
Highlight the written query under the task 3 description and click Execute.
L6-33
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
L6-34
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L6-35
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
In the query pane, type the following query after the task 1 description:
SELECT
custid, companyname, contactname, address, city, country, phone
FROM Sales.Customers
WHERE
country = N'Brazil';
Notice the use of the N prefix for the character literal. This prefix is used because the country column
is a Unicode data type. When expressing a Unicode character literal, you need to specify the character
N (for National) as a prefix. You will learn more about data types in the next module.
6.
Task 2: Write a SELECT statement that uses an IN predicate in the WHERE clause
1.
In the query pane, type the following query after the task 2 description:
SELECT
custid, companyname, contactname, address, city, country, phone
FROM Sales.Customers
WHERE
country IN (N'Brazil', N'UK', N'USA');
2.
Task 3: Write a SELECT statement that uses a LIKE predicate in the WHERE clause
1.
In the query pane, type the following query after the task 3 description:
SELECT
custid, companyname, contactname, address, city, country, phone
FROM Sales.Customers
WHERE
contactname LIKE N'A%';
Remember that the percent sign (%) wildcard represents a string of any size (including an empty
string), whereas the underscore (_) wildcard represents a single character.
2.
L6-36
Highlight the T-SQL statement provided under the task 4 description and click Execute.
2.
Highlight the provided T-SQL statement. On the toolbar, click Edit and then Copy.
3.
In the query window, click the line after the task 4 description. On the toolbar, click Edit and then
Paste. You have now copied the previous query to the same query window after the task 4
description.
4.
5.
6.
Observe the result. Is it the same as the result of the first SQL statement? The result is not the same.
When you specify the predicate in the ON clause, the left outer join preserves all the rows from the
left table (Sales.Customers) and adds only the matching rows from the right table (Sales.Orders) based
on the predicate in the ON clause. This means that all the customers will show up in the output, but
only the customers from Paris will have matching orders. When you specify the predicate in the
WHERE clause, the query will filter only the customers from Paris. So, be aware that when you use an
outer join, the result of a query in which the predicate is specified in the ON clause can differ from
the result of a query in which the predicate is specified in the WHERE clause. (When using an inner
join, the results are always the same.) This is because the ON predicate is a matching predicateit
defines which rows from the nonpreserved side to match to the rows from the preserved side. The
WHERE predicate is a filtering predicateif a row from either side doesnt satisfy the WHERE
predicate, the row is filtered out.
L6-37
In the query pane, type the following query after the task 5 description:
SELECT
c.custid, c.companyname
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o ON c.custid = o.custid
WHERE o.custid IS NULL;
It is important to note that when you are looking for a NULL, you should use the IS NULL operator
and not the equality operator. The equality operator will always return UNKNOWN when you
compare something to a NULL. It will even return UNKNOWN when you compare two NULLs.
The choice of which attribute to filter from the nonpreserved side of the join is also important. You
should choose an attribute that can only have a NULL when the row is an outer row (e.g., a NULL
originating from the base table). For this purpose, three cases are safe to consider:
2.
A primary key column. A primary key column cannot be NULL. Therefore, a NULL in such a
column can only mean that the row is an outer row.
A join column. If a row has a NULL in the join column, that row is filtered out by the second
phase of the join. So, a NULL in such a column can only mean that it is an outer row.
A column defined as NOT NULL. A NULL in a column that is defined as NOT NULL can only mean
that the row is an outer row.
L6-38
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
c.custid, c.contactname, o.orderid, o.orderdate
FROM Sales.Customers AS c
INNER JOIN Sales.Orders AS o ON c.custid = o.custid
WHERE
o.orderdate >= '20080401'
ORDER BY
o.orderdate DESC, c.custid ASC;
Notice the date filter. It uses a literal (constant) of a date. SQL Server recognizes the literal '20080401'
as a character string literal and not as a date and time literal, but because the expression involves two
operands of different types, one operand needs to be implicitly converted to the others type. In this
example, the character string literal is converted to the columns data type (DATETIME) because
character strings are considered lower in terms of data type precedence with respect to date and time
data types.
Also notice that the character string literal follows the format 'yyyymmdd'. Using this format is a best
practice because SQL Server knows how to convert it to the correct date, regardless of the language
settings.
4.
Task 2: Apply the needed changes and execute the T-SQL statement
1.
Highlight the written query under the task 2 description and click Execute.
2.
3.
This error occurred because the WHERE clause is evaluated before the SELECT clause and, at that
time, the column did not have an alias. To fix this problem, you must use the source column name
with the appropriate table alias. Modify the T-SQL statement to look like this:
SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid,
m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
INNER JOIN HR.Employees AS m ON e.mgrid = m.empid
WHERE
m.lastname = N'Buck';
4.
L6-39
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 3 description. On the toolbar, click Edit and then
Paste. You have now copied the previous query to the same query window after the task 3
description.
3.
Modify the T-SQL statement to include an ORDER BY clause that uses the source column name of
m.firstname. Your query should look like this:
SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid,
m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
INNER JOIN HR.Employees AS m ON e.mgrid = m.empid
ORDER BY
m.firstname;
4.
5.
Modify the ORDER BY clause so that it uses the alias for the same column (mgrfirstname). Your query
should look like this:
SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid,
m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
INNER JOIN HR.Employees AS m ON e.mgrid = m.empid
ORDER BY
mgrfirstname;
6.
7.
Observe the result. Why were you able to use a source column name or an alias column name? You
can use either one because the ORDER BY clause is evaluated after the SELECT clause and the alias for
the column name is known.
L6-40
Exercise 3: Writing Queries That Filter Data Using the TOP Clause
Task 1: Write a SELECT statement to retrieve last 20 orders
1.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT TOP (20)
orderid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;
4.
In the query pane, type the following query after the task 2 description:
SELECT
orderid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC
OFFSET 0 ROWS FETCH FIRST 20 ROWS ONLY;
Remember that the OFFSET-FETCH clause is a new functionality in SQL Server 2012. Unlike the TOP
clause, the OFFSET-FETCH clause must be used with the ORDER BY clause.
2.
In the query pane, type the following query after the task 3 description:
SELECT TOP (10) PERCENT
productname, unitprice
FROM Production.Products
ORDER BY unitprice DESC;
Implementing this task with the OFFSET-FETCH clause is possible. However, it is not easy because,
unlike TOP, OFFSET-FETCH does not support a PERCENT option.
2.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
custid, orderid, orderdate
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 0 ROWS FETCH FIRST 20 ROWS ONLY;
4.
In the query pane, type the following query after the task 2 description:
SELECT
custid, orderid, orderdate
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;
2.
Solution: OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY.
L6-41
L6-42
L7-43
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
L7-44
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
In the query pane, type the following query after the task 1 description:
SELECT
CURRENT_TIMESTAMP AS currentdatetime,
CAST(CURRENT_TIMESTAMP AS DATE) AS currentdate,
CAST(CURRENT_TIMESTAMP AS TIME) AS currenttime,
YEAR(CURRENT_TIMESTAMP) AS currentyear,
MONTH(CURRENT_TIMESTAMP) AS currentmonth,
DAY(CURRENT_TIMESTAMP) AS currentday,
DATEPART(week, CURRENT_TIMESTAMP) AS currentweeknumber,
DATENAME(month, CURRENT_TIMESTAMP) AS currentmonthname;
This query uses the CURRENT_TIMESTAMP function to return the current date and time. You can also
use the SYSDATETIME function to get a more precise time element compared to the
CURRENT_TIMESTAMP function.
Note that you cannot use the alias currentdatetime as the source in the second column calculation
because SQL Server supports a concept called all-at-once operations. This means that all expressions
that appear in the same logical query processing phase are evaluated as if they occurred at the same
point in time. This concept explains why, for example, you cannot refer to column aliases assigned in
the SELECT clause within the same SELECT clause, even if it seems intuitive that you should be able to.
6.
In the query pane, type the following queries after the task 2 description:
SELECT DATEFROMPARTS(2011, 12, 11) AS somedate;
SELECT CAST('20111211' AS DATE) AS somedate;
SELECT CONVERT(DATE, '12/11/2011', 101) AS somedate;
The first query uses SQL Server 2012s new DATEFROMPARTS function.
2.
L7-46
Task 3: Write a SELECT statement that uses different date and time functions
1.
In the query pane, type the following query after the task 3 description:
SELECT
DATEADD(month, 3, CURRENT_TIMESTAMP) AS threemonths,
DATEDIFF(day, CURRENT_TIMESTAMP, DATEADD(month, 3, CURRENT_TIMESTAMP)) AS
diffdays,
DATEDIFF(week, '19920404', '20110916') AS diffweeks,
DATEADD(day, -DAY(CURRENT_TIMESTAMP) + 1, CURRENT_TIMESTAMP) AS firstday;
2.
Highlight the written query under the task 4 description and click Execute.
2.
In the query pane, type the following queries after the task 4 description:
SELECT
isitdate,
CASE WHEN ISDATE(isitdate) = 1 THEN CONVERT(DATE, isitdate) ELSE NULL END AS
converteddate
FROM Sales.Somedates;
-- Uses the new TRY_CONVERT function in SQL Server 2012
SELECT
isitdate,
TRY_CONVERT(DATE, isitdate) AS converteddate
FROM Sales.Somedates;
The second query uses the TRY_CONVERT function, which is new in SQL Server 2012. This function
returns a value casted to the specified data type if the casting succeeds; otherwise, it returns NULL. Do
not worry if you do not recognize the type conversion functions. They will be covered in the next
module.
3.
4.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT DISTINCT
custid
FROM Sales.Orders
WHERE
YEAR(orderdate) = 2008
AND MONTH(orderdate) = 2;
4.
5.
Note that you could also write a query that uses a range format, which would better utilize indexing.
The query would then look like this:
SELECT DISTINCT
custid
FROM Sales.Orders
WHERE
orderdate >= '20080201'
AND orderdate < '20080301';
Task 2: Write a SELECT statement to calculate the first and last day of the month
1.
In the query pane, type the following query after the task 2 description:
SELECT
CURRENT_TIMESTAMP AS currentdate,
DATEADD (day, 1, EOMONTH(CURRENT_TIMESTAMP, -1)) AS firstofmonth,
EOMONTH(CURRENT_TIMESTAMP) AS endofmonth;
This query uses the EOMONTH function, which is new in SQL Server 2012.
2.
Task 3: Write a SELECT statement to retrieve the orders placed in the last five days of
the ordered month
1.
In the query pane, type the following query after the task 3 description:
SELECT
orderid, custid, orderdate
FROM Sales.Orders
WHERE
DATEDIFF(
day,
orderdate,
EOMONTH(orderdate)
) < 5;
2.
L7-48
Task 4: Write a SELECT statement to retrieve all distinct products sold in the first 10
weeks of the year 2007
1.
In the query pane, type the following query after the task 4 description:
SELECT DISTINCT
d.productid
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
WHERE
DATEPART(week, orderdate) <= 10
AND YEAR(orderdate) = 2007;
2.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
contactname + N' (city: ' + city + N')' AS contactwithcity
FROM Sales.Customers;
4.
In the query pane, type the following query after the task 2 description:
SELECT
contactname + N' (city: ' + city + N', region: ' + COALESCE(region, '') + N')' AS
fullcontact
FROM Sales.Customers;
This query uses the COALESCE function to replace a NULL with an empty string. The next module will
include more examples of how to handle a NULL.
2.
3.
Note that you can also use SQL Server 2012s new CONCAT function to concatenate strings. It also
replaces a NULL with an empty string. The query using the CONCAT function would look like this:
SELECT
CONCAT(contactname, N' (city: ', city,
fullcontact
FROM Sales.Customers;
Task 3: Write a SELECT statement to retrieve all customers based on the first
character in the contact name
1.
In the query pane, type the following query after the task 3 description:
SELECT contactname, contacttitle
FROM Sales.Customers
WHERE contactname LIKE N'[A-G]%'
ORDER BY contactname;
2.
L7-50
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
contactname,
SUBSTRING(contactname, 0, CHARINDEX(N',', contactname)) AS lastname
FROM Sales.Customers;
4.
In the query pane, type the following query after the task 2 description:
SELECT
REPLACE(contactname, ',', '') AS newcontactname,
SUBSTRING(contactname, CHARINDEX(N',', contactname)+1, LEN(contactname)CHARINDEX(N',', contactname)+1) AS firstname
FROM Sales.Customers;
2.
In the query pane, type the following query after the task 3 description:
SELECT
custid,
N'C' + RIGHT(REPLICATE('0', 5) + CAST(custid AS VARCHAR(5)), 5) AS custnewid
FROM Sales.Customers;
2.
3.
Note that you can also use SQL Server 2012s new FORMAT function. The query would then look
like this:
SELECT
FORMAT(custid, N'\C00000')
FROM Sales.Customers;
In the query pane, type the following query after the task 4 description:
SELECT
contactname,
LEN(contactname) - LEN(REPLACE(contactname, 'a', '')) AS numberofa
FROM Sales.Customers
ORDER BY numberofa DESC;
This elegant solution first returns the number of characters in the contact name and then subtracts
the number of characters in the contact name without the character a. The result is stored in a new
column named numberofa.
2.
L7-52
L8-53
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
L8-54
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L8-55
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
In the query pane, type the following query after the task 1 description:
SELECT N'The unit price for the ' + productname + N' is ' + CAST(unitprice AS
NVARCHAR(10)) + N' $.' AS productdesc
FROM Production.Products;
This query uses the CAST function rather than the CONVERT function. It is better to use the CAST
function because it is an ANSI SQL standard. You should use the CONVERT function only when you
need to apply a specific style during a conversion.
6.
Task 2: Write a SELECT statement to filter rows based on specific date information
1.
In the query pane, type the following query after the task 2 description:
SELECT orderid, orderdate, shippeddate, COALESCE(shipregion, 'No region') AS
shipregion
FROM Sales.Orders
WHERE
orderdate >= CONVERT(DATETIME, '4/1/2007', 101)
AND orderdate <= CONVERT(DATETIME, '11/30/2007', 101)
AND shippeddate > DATEADD(DAY, 30, orderdate);
2.
3.
Note that you could also write a solution using the new PARSE function. The query would look
like this:
SELECT orderid, orderdate, shippeddate, COALESCE(shipregion, 'No region') AS
shipregion
FROM Sales.Orders
WHERE
orderdate >= PARSE('4/1/2007' AS DATETIME USING 'en-US')
AND orderdate <= PARSE('11/30/2007' AS DATETIME USING 'en-US')
AND shippeddate > DATEADD(DAY, 30, orderdate);
L8-56
In the query pane, type the following query after the task 3 description:
SELECT
CONVERT(INT, REPLACE(REPLACE(REPLACE(REPLACE(phone, N'-', N''), N'(', ''), N')',
''), ' ', '')) AS phonenoasint
FROM Sales.Customers;
This query is trying to use the CONVERT function to convert phone numbers that include characters
such as hyphens and parentheses into an integer value.
2.
3.
4.
Modify the query to use the TRY_CONVERT function. The query should look like this:
SELECT
TRY_CONVERT(INT, REPLACE(REPLACE(REPLACE(REPLACE(phone, N'-', N''), N'(', ''),
N')', ''), ' ', '')) AS phonenoasint
FROM Sales.Customers;
5.
Highlight the written query and click Execute. Observe the result. The rows that could not be
converted have a NULL.
L8-57
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
IIF(country = N'Mexico' AND contacttitle = N'Owner', N'Target group', N'Other') AS
segmentgroup, custid, contactname
FROM Sales.Customers;
The IIF function is new in SQL Server 2012. It was added mainly to support migrations from Microsoft
Access to SQL Server. You can always use a CASE expression to achieve the same result.
4.
In the query pane, type the following query after the task 2 description:
SELECT
IIF(contacttitle = N'Owner' OR region IS NOT NULL, N'Target group', N'Other') AS
segmentgroup, custid, contactname
FROM Sales.Customers;
2.
In the query pane, type the following query after the task 3 description:
SELECT CHOOSE(custid % 4 + 1, N'Group One', N'Group Two', N'Group Three', N'Group
Four') AS segmentgroup, custid, contactname
FROM Sales.Customers;
2.
L8-58
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT contactname, COALESCE(fax, N'No information') AS faxinformation
FROM Sales.Customers;
This query uses the COALESCE function to retrieve customers fax information.
4.
5.
In the query pane, type the following query after the previous query:
SELECT contactname, ISNULL(fax, N'No information') AS faxinformation
FROM Sales.Customers;
This query uses the ISNULL function. What is the difference between the ISNULL and COALESCE
functions? COALESCE is a standard ANSI SQL function and ISNULL is not. So, you should use the
COALESCE function.
6.
Highlight the query provided under the task 2 description and click Execute.
2.
Highlight the previous query. On the toolbar, click Edit and then Copy.
3.
In the query window, click the line after the task 2 description. On the toolbar, click Edit and then
Paste. You have now copied the previous query to the same query window after the task 2
description.
4.
5.
6.
Test the modified query by setting the @region parameter to N'WA'. The T-SQL expression should
look like this:
DECLARE @region AS NVARCHAR(30) = N'WA';
SELECT
custid, region
FROM Sales.Customers
WHERE region = @region OR (region IS NULL AND @region IS NULL);
7.
Task 3: Write a SELECT statement to return all the customers that do not have a
two-character abbreviation for the region
1.
In the query pane, type the following query after the task 3 description:
SELECT custid, contactname, city, region
FROM Sales.Customers
WHERE
region IS NULL
OR LEN(region) <> 2;
2.
L8-59
L8-60
L9-61
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
L9-62
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click Login tab, select SQL Server Authentication in the Authentication
list, type your login name in the Login text box and the password in the Password text box, and
click Connect.
L9-63
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
In the query pane, type the following query after the task 1 description:
SELECT
o.custid, c.contactname
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c ON c.custid = o.custid
WHERE o.empid = 5
GROUP BY o.custid, c.contactname;
6.
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 2 description. On the toolbar, click Edit and
then Paste.
3.
Modify the T-SQL statement so that it adds an additional column. Your query should look like this:
SELECT
o.custid, c.contactname, c.city
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c ON c.custid = o.custid
WHERE o.empid = 5
GROUP BY o.custid, c.contactname;
4.
5.
L9-64
6.
Modify the SQL statement to include the city column in the GROUP BY clause. Your query should look
like this:
SELECT
o.custid, c.contactname, c.city
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c ON c.custid = o.custid
WHERE o.empid = 5
GROUP BY o.custid, c.contactname, c.city;
7.
Task 3: Write a SELECT statement to retrieve the customers with orders for each year
1.
In the query pane, type the following query after the task 3 description:
SELECT
custid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE empid = 5
GROUP BY custid, YEAR(orderdate)
ORDER BY custid, orderyear;
2.
In the query pane, type the following query after the task 4 description:
SELECT
c.categoryid, c.categoryname
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
INNER JOIN Production.Products AS p ON p.productid = d.productid
INNER JOIN Production.Categories AS c ON c.categoryid = p.categoryid
WHERE orderdate >= '20080101' AND orderdate < '20090101'
GROUP BY c.categoryid, c.categoryname;
2.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
o.orderid, o.orderdate, SUM(d.qty * d.unitprice) AS salesamount
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.orderid, o.orderdate
ORDER BY salesamount DESC;
4.
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 2 description. On the toolbar, click Edit and
then Paste.
3.
Modify the T-SQL statement so that it adds additional columns. Your query should look like this:
SELECT
o.orderid, o.orderdate,
SUM(d.qty * d.unitprice) AS salesamount,
COUNT(*) AS noofoderlines,
AVG(d.qty * d.unitprice) AS avgsalesamountperorderline
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.orderid, o.orderdate
ORDER BY salesamount DESC;
4.
Task 3: Write a SELECT statement to retrieve the sales amount value per month
1.
In the query pane, type the following query after the task 3 description:
SELECT
YEAR(orderdate) * 100 + MONTH(orderdate) AS yearmonthno,
SUM(d.qty * d.unitprice) AS saleamountpermonth
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY YEAR(orderdate), MONTH(orderdate)
ORDER BY yearmonthno;
2.
L9-65
L9-66
Task 4: Write a SELECT statement to list all customers, with the total sales amount
and number of order lines added
1.
In the query pane, type the following query after the task 4 description:
SELECT
c.custid, c.contactname,
SUM(d.qty * d.unitprice) AS totalsalesamount,
MAX(d.qty * d.unitprice) AS maxsalesamountperorderline,
COUNT(*) AS numberofrows,
COUNT(o.orderid) AS numberoforderlines
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o ON o.custid = c.custid
LEFT OUTER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY c.custid, c.contactname
ORDER BY totalsalesamount;
2.
3.
Observe the result. Notice that the values in the numberofrows and numberoforderlines columns are
different. Why? All aggregate functions ignore NULLs except COUNT(*), which is why you received
the value 1 for the numberofrows column. When you used the orderid column in the COUNT
function, you received the value 0 because the orderid is NULL for customers without an order.
L9-67
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
4.
Observe the result. Notice that the number of orders is the same as the number of customers. Why?
You are using the aggregate COUNT function on the orderid and custid columns and since every
order has a customer, the COUNT function returns the same value. It does not matter if there are
multiple orders for the same customer because you are not using a DISTINCT clause inside the
aggregate function. If you want to get the correct number of distinct customers, you have to modify
the provided T-SQL statement to include a DISTINCT clause.
5.
Modify the provided T-SQL statement to include a DISTINCT clause. The query should look like this:
SELECT
YEAR(orderdate) AS orderyear,
COUNT(orderid) AS nooforders,
COUNT(DISTINCT custid) AS noofcustomers
FROM Sales.Orders
GROUP BY YEAR(orderdate);
6.
In the query pane, type the following query after the task 2 description:
SELECT
SUBSTRING(c.contactname,1,1) AS firstletter,
COUNT(DISTINCT c.custid) AS noofcustomers,
COUNT(o.orderid) AS nooforders
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o ON o.custid = c.custid
GROUP BY SUBSTRING(c.contactname,1,1)
ORDER BY firstletter;
2.
In the query pane, type the following query after the task 3 description:
SELECT
c.categoryid, c.categoryname,
SUM(d.qty * d.unitprice) AS totalsalesamount,
COUNT(DISTINCT o.orderid) AS nooforders,
SUM(d.qty * d.unitprice) / COUNT(DISTINCT o.orderid) AS avgsalesamountperorder
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
INNER JOIN Production.Products AS p ON p.productid = d.productid
INNER JOIN Production.Categories AS c ON c.categoryid = p.categoryid
WHERE orderdate >= '20080101' AND orderdate < '20090101'
GROUP BY c.categoryid, c.categoryname;
2.
L9-68
Exercise 4: Writing Queries That Filter Groups with the HAVING Clause
Task 1: Write a SELECT statement to retrieve the top 10 customers
1.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT TOP (10)
o.custid,
SUM(d.qty * d.unitprice) AS totalsalesamount
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.custid
HAVING SUM(d.qty * d.unitprice) > 10000
ORDER BY totalsalesamount DESC;
4.
In the query pane, type the following query after the task 2 description:
SELECT
o.orderid,
o.empid,
SUM(d.qty * d.unitprice) as totalsalesamount
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
WHERE o.orderdate >= '20080101' AND o.orderdate < '20090101'
GROUP BY o.orderid, o.empid;
2.
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 3 description. On the toolbar, click Edit and then
Paste.
3.
Modify the T-SQL statement to apply additional filtering. Your query should look like this:
SELECT
o.orderid,
o.empid,
SUM(d.qty * d.unitprice) as totalsalesamount
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
WHERE o.orderdate >= '20080101' AND o.orderdate < '20090101'
GROUP BY o.orderid, o.empid
HAVING SUM(d.qty * d.unitprice) >= 10000;
4.
5.
L9-69
Modify the T-SQL statement to include an additional filter to retrieve only orders handled by the
employee whose ID is 3. Your query should look like this:
SELECT
o.orderid,
o.empid,
SUM(d.qty * d.unitprice) as totalsalesamount
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
WHERE
o.orderdate >= '20080101' AND o.orderdate <= '20090101'
AND o.empid = 3
GROUP BY o.orderid, o.empid
HAVING SUM(d.qty * d.unitprice) >= 10000;
In this query, the predicate logic is applied in the WHERE clause. You could also write the predicate
logic inside the HAVING clause. Which do you think is better? Unlike with orderdate filtering, with
empid filtering, the result is going to be correct either way because you are filtering by an element
that appears in the GROUP BY list. Conceptually, it seems more intuitive to filter as early as possible.
Thus, this query applies the filtering in the WHERE clause because it will be logically applied before
the GROUP BY clause. Do not forget, though, that the actual processing in the SQL Server engine
could be different.
6.
In the query pane, type the following query after the task 4 description:
SELECT
o.custid,
MAX(orderdate) AS lastorderdate,
SUM(d.qty * d.unitprice) AS totalsalesamount
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.custid
HAVING COUNT(DISTINCT o.orderid) > 25;
2.
L9-70
L10-71
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L10-73
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
In the query pane, type the following query after the task 1 description:
SELECT MAX(orderdate) AS lastorderdate
FROM Sales.Orders;
6.
Task 2: Write a SELECT statement to retrieve all orders placed on the last order date
1.
In the query pane, type the following query after the task 2 description:
SELECT
orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE
orderdate = (SELECT MAX(orderdate) FROM Sales.Orders);
2.
Highlight the provided T-SQL statement under the task 3 description and click Execute.
2.
Modify the query to filter customers whose contact name starts with the letter B. Your query should
look like this:
SELECT
orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE
custid =
(
SELECT custid
FROM Sales.Customers
WHERE contactname LIKE N'B%'
);
3.
4.
5.
Modify the query so that it uses the IN operator. Your query should look like this:
SELECT
orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE
custid IN
(
SELECT custid
FROM Sales.Customers
WHERE contactname LIKE N'B%'
);
6.
Task 4: Write a SELECT statement to analyze each orders sales as a percentage of the
total sales amount
1.
In the query pane, type the following query after the task 4 description:
SELECT
o.orderid,
SUM(d.qty * d.unitprice) AS totalsalesamount,
SUM(d.qty * d.unitprice) /
(
SELECT SUM(d.qty * d.unitprice)
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
WHERE o.orderdate >= '20080501' AND orderdate < '20080601'
) * 100. AS salespctoftotal
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
WHERE o.orderdate >= '20080501' AND orderdate < '20080601'
GROUP BY o.orderid;
2.
L10-75
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
productid, productname
FROM Production.Products
WHERE
productid IN
(
SELECT productid
FROM Sales.OrderDetails
WHERE qty > 100
);
4.
In the query pane, type the following query after the task 2 description:
SELECT
custid, contactname
FROM Sales.Customers
WHERE custid NOT IN
(
SELECT custid
FROM Sales.Orders
);
2.
3.
Observe the result. Notice that there are two customers without an order.
Task 3: Add a row and rerun the query that retrieves those customers without orders
1.
Highlight the provided T-SQL statement under the task 3 description and click Execute. This code
inserts an additional row that has a NULL in the custid column of the Sales.Orders table.
2.
Highlight the query in task 2. On the toolbar, click Edit and then Copy.
3.
In the query window, click the line after the provided T-SQL statement. On the toolbar, click Edit and
then Paste.
4.
5.
Notice that you have an empty result despite getting two rows in the result when you first ran the
query in task 2. Why did you get an empty result this time? There is an issue with the NULL in the new
row you added because the custid column is the only column that is part of the subquery. The IN
operator supports three-valued logic (TRUE, FALSE, UNKNOWN). Before you apply the NOT operator,
the logical meaning of UNKNOWN is that you cant tell for sure whether the customer ID appears in
the set, because the NULL could represent that customer ID as well as anything else. As a more
tangible example, consider the expression 22 NOT IN (1, 2, NULL). If you evaluate each individual
expression in the parentheses to its truth value, you will get NOT (FALSE OR FALSE OR UNKNOWN),
which translates to NOT UNKNOWN, which evaluates to UNKNOWN. The tricky part is that negating
UNKNOWN with the NOT operator still yields UNKNOWN, and UNKNOWN is filtered out in a query
filter. In short, when you use the NOT IN predicate against a subquery that returns at least one NULL,
the outer query always returns an empty set.
6.
To solve this problem, modify the T-SQL statement so that the subquery does not return NULLs. Your
query should look like this:
SELECT
custid, contactname
FROM Sales.Customers
WHERE custid NOT IN
(
SELECT custid
FROM Sales.Orders
WHERE custid IS NOT NULL
);
7.
L10-77
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
c.custid, c.contactname,
(
SELECT MAX(o.orderdate)
FROM Sales.Orders AS o
WHERE o.custid = c.custid
) AS lastorderdate
FROM Sales.Customers AS c;
4.
Task 2: Write a SELECT statement that uses the EXISTS predicate to retrieve those
customers without orders
1.
In the query pane, type the following query after the task 2 description:
SELECT c.custid, c.contactname
FROM Sales.Customers AS c
WHERE
NOT EXISTS (SELECT * FROM Sales.Orders AS o WHERE o.custid = c.custid);
2.
3.
Notice that you got the same result as the modified query in exercise 2 task 3, but without a filter to
exclude NULLs. Why didnt you need to explicitly filter out NULLs? The EXISTS predicate uses twovalued logic (TRUE, FALSE) and checks only if the rows specified in the correlated subquery exists.
Another benefit of using the EXISTS predicate is better performance. The SQL Server engine knows
that it is enough to determine whether the subquery returns at least one row or none, so it doesnt
need to process all qualifying rows.
In the query pane, type the following query after the task 3 description:
SELECT c.custid, c.contactname
FROM Sales.Customers AS c
WHERE
EXISTS (
SELECT *
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
WHERE o.custid = c.custid
AND d.unitprice > 100.
AND o.orderdate >= '20080401'
);
2.
Task 4 (challenge): Write a SELECT statement to display the total sales amount and
the running total sales amount for each order year
1.
In the query pane, type the following query after the task 4 description:
SELECT
YEAR(o.orderdate) as orderyear,
SUM(d.qty * d.unitprice) AS totalsales,
(
SELECT SUM(d2.qty * d2.unitprice)
FROM Sales.Orders AS o2
INNER JOIN Sales.OrderDetails AS d2 ON d2.orderid = o2.orderid
WHERE YEAR(o2.orderdate) <= YEAR(o.orderdate)
) AS runsales
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY YEAR(o.orderdate)
ORDER BY orderyear;
2.
L11-79
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L11-81
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
In the query pane, type the following query after the task 1 description:
SELECT
productid, productname, supplierid, unitprice, discontinued
FROM Production.Products
WHERE categoryid = 1;
6.
7.
Modify the query to include the provided CREATE VIEW statement. The query should look like this:
CREATE VIEW Production.ProductsBeverages AS
SELECT
productid, productname, supplierid, unitprice, discontinued
FROM Production.Products
WHERE categoryid = 1;
8.
In the query pane, type the following query after the task 2 description:
SELECT
productid, productname
FROM Production.ProductsBeverages
WHERE supplierid = 1;
2.
Highlight the provided T-SQL statement under the task 3 description and click Execute.
2.
3.
Modify the previous T-SQL statement by including the TOP (100) PERCENT option. The query should
look like this:
ALTER VIEW Production.ProductsBeverages AS
SELECT TOP(100) PERCENT
productid, productname, supplierid, unitprice, discontinued
FROM Production.Products
WHERE categoryid = 1
ORDER BY productname;
4.
5.
Observe the result. If you now write a query against the Production.ProductsBeverages view, will it be
guaranteed that the retrieved rows will be sorted by productname? If you do not specify the ORDER
BY clause in the T-SQL statement against the view, there is no guarantee that the retrieved rows will
be sorted. It is important to remember that any order of the rows in the output is considered valid,
and no specific order is guaranteed. Therefore, when querying a table expression, you should not
assume any order unless you specify an ORDER BY clause in the outer query.
Highlight the provided T-SQL statement under the task 4 description and click Execute.
2.
3.
Modify the T-SQL statement to include the column name pricetype. The query should look like this:
ALTER VIEW Production.ProductsBeverages AS
SELECT
productid, productname, supplierid, unitprice, discontinued,
CASE WHEN unitprice > 100. THEN N'high' ELSE N'normal' END AS pricetype
FROM Production.Products
WHERE categoryid = 1;
4.
Highlight the provided T-SQL statement under the task 5 description and click Execute.
L11-83
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
p.productid, p.productname
FROM
(
SELECT
productid, productname, supplierid, unitprice, discontinued,
CASE WHEN unitprice > 100. THEN N'high' ELSE N'normal' END AS pricetype
FROM Production.Products
WHERE categoryid = 1
) AS p
WHERE p.pricetype = N'high';
4.
Task 2: Write a SELECT statement to calculate the total and average sales amount
1.
In the query pane, type the following query after the task 2 description:
SELECT
c.custid,
SUM(c.totalsalesamountperorder) AS totalsalesamount,
AVG(c.totalsalesamountperorder) AS avgsalesamount
FROM
(
SELECT
o.custid, o.orderid, SUM(d.unitprice * d.qty) AS totalsalesamountperorder
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails d ON d.orderid = o.orderid
GROUP BY o.custid, o.orderid
) AS c
GROUP BY c.custid;
2.
Task 3 (challenge): Write a SELECT statement to retrieve the sales growth percentage
1.
In the query pane, type the following query after the task 3 description:
SELECT
cy.orderyear,
cy.totalsalesamount AS curtotalsales,
py.totalsalesamount AS prevtotalsales,
(cy.totalsalesamount - py.totalsalesamount) / py.totalsalesamount * 100. AS
percentgrowth
FROM
(
SELECT
YEAR(orderdate) AS orderyear, SUM(val) AS totalsalesamount
FROM Sales.OrderValues
GROUP BY YEAR(orderdate)
) AS cy
LEFT OUTER JOIN
(
SELECT
YEAR(orderdate) AS orderyear, SUM(val) AS totalsalesamount
FROM Sales.OrderValues
GROUP BY YEAR(orderdate)
) AS py ON cy.orderyear = py.orderyear + 1
ORDER BY cy.orderyear;
2.
L11-85
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
WITH ProductsBeverages AS
(
SELECT
productid, productname, supplierid, unitprice, discontinued,
CASE WHEN unitprice > 100. THEN N'high' ELSE N'normal' END AS pricetype
FROM Production.Products
WHERE categoryid = 1
)
SELECT
productid, productname
FROM ProductsBeverages
WHERE pricetype = N'high';
4.
Task 2: Write a SELECT statement to retrieve the total sales amount for each customer
1.
In the query pane, type the following query after the task 2 description:
WITH c2008 (custid, salesamt2008) AS
(
SELECT
custid, SUM(val)
FROM Sales.OrderValues
WHERE YEAR(orderdate) = 2008
GROUP BY custid
)
SELECT
c.custid, c.contactname, c2008.salesamt2008
FROM Sales.Customers AS c
LEFT OUTER JOIN c2008 ON c.custid = c2008.custid;
2.
Task 3 (challenge): Write a SELECT statement to compare the total sales amount for
each customer over the previous year
1.
In the query pane, type the following query after the task 3 description:
WITH c2008 (custid, salesamt2008)
(
SELECT
custid, SUM(val)
FROM Sales.OrderValues
WHERE YEAR(orderdate) = 2008
GROUP BY custid
),
c2007 (custid, salesamt2007) AS
(
SELECT
custid, SUM(val)
FROM Sales.OrderValues
WHERE YEAR(orderdate) = 2007
GROUP BY custid
)
SELECT
c.custid, c.contactname,
c2008.salesamt2008,
c2007.salesamt2007,
COALESCE((c2008.salesamt2008 AS percentgrowth
FROM Sales.Customers AS c
LEFT OUTER JOIN c2008 ON c.custid
LEFT OUTER JOIN c2007 ON c.custid
ORDER BY percentgrowth DESC;
2.
AS
L11-87
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
custid, SUM(val) AS totalsalesamount
FROM Sales.OrderValues
WHERE YEAR(orderdate) = 2007
GROUP BY custid;
4.
5.
Create an inline table-valued function using the provided code. Add the previous query, putting it
after the functions RETURN clause. In the query, replace the order date of 2007 with the functions
input parameter @orderyear. The resulting T-SQL statement should look like this:
CREATE FUNCTION dbo.fnGetSalesByCustomer
(@orderyear AS INT) RETURNS TABLE
AS
RETURN
SELECT
custid, SUM(val) AS totalsalesamount
FROM Sales.OrderValues
WHERE YEAR(orderdate) = @orderyear
GROUP BY custid;
This T-SQL statement will create an inline table-valued function named dbo.fnGetSalesByCustomer.
6.
In the query pane, type the following query after the task 2 description:
SELECT
custid, totalsalesamount
FROM dbo.fnGetSalesByCustomer(2007);
2.
Task 3: Write a SELECT statement to retrieve the top three products based on the
total sales value for a specified customer
1.
In the query pane, type the following query after the task 3 description:
SELECT TOP(3)
d.productid,
MAX(p.productname) AS productname,
SUM(d.qty * d.unitprice) AS totalsalesamount
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
INNER JOIN Production.Products AS p ON p.productid = d.productid
WHERE custid = 1
GROUP BY d.productid
ORDER BY totalsalesamount DESC;
2.
3.
Create an inline table-valued function using the provided code. Add the previous query, putting it
after the functions RETURN clause. In the query, replace the constant custid value of 1 with the
functions input parameter @custid. The resulting T-SQL statement should look like this:
CREATE FUNCTION dbo.fnGetTop3ProductsForCustomer
(@custid AS INT) RETURNS TABLE
AS
RETURN
SELECT TOP(3)
d.productid,
MAX(p.productname) AS productname,
SUM(d.qty * d.unitprice) AS totalsalesamount
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
INNER JOIN Production.Products AS p ON p.productid = d.productid
WHERE custid = @custid
GROUP BY d.productid
ORDER BY totalsalesamount DESC;
4.
To test the inline table-valued function, add the following query after the CREATE FUNCTION
statement:
SELECT
p.productid,
p.productname,
p.totalsalesamount
FROM dbo.fnGetTop3ProductsForCustomer(1) AS p;
5.
Highlight the CREATE FUNCTION statement and the written query, and click Execute.
L11-89
Task 4 (challenge): Write a SELECT statement to compare the total sales amount for
each customer over the previous year using inline table-valued functions
1.
In the query pane, type the following query after the task 4 description:
SELECT
c.custid, c.contactname,
c2008.totalsalesamount AS salesamt2008,
c2007.totalsalesamount AS salesamt2007,
COALESCE((c2008.totalsalesamount - c2007.totalsalesamount) /
c2007.totalsalesamount * 100., 0) AS percentgrowth
FROM Sales.Customers AS c
LEFT OUTER JOIN dbo.fnGetSalesByCustomer(2007) AS c2007 ON c.custid = c2007.custid
LEFT OUTER JOIN dbo.fnGetSalesByCustomer(2008) AS c2008 ON c.custid = c2008.custid;
2.
Highlight the provided T-SQL statement under the task 5 description and click Execute.
L12-91
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L12-93
Exercise 1: Writing Queries That Use the UNION Set Operator and UNION
ALL Multi-Set Operator
Task 1: Write a SELECT statement to retrieve specific products
1.
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
In the query pane, type the following query after the task 1 description:
SELECT
productid, productname
FROM Production.Products
WHERE categoryid = 4;
6.
Highlight the written query and click Execute. Observe that the query retrieved 10 rows.
Task 2: Write a SELECT statement to retrieve all products with more than $50,000
total sales amount
1.
In the query pane, type the following query after the task 2 description:
SELECT
d.productid, p.productname
FROM Sales.OrderDetails d
INNER JOIN Production.Products p ON p.productid = d.productid
GROUP BY d.productid, p.productname
HAVING SUM(d.qty * d.unitprice) > 50000;
2.
Highlight the written query and click Execute. Observe that the query retrieved four rows.
In the query pane, type the following query after the task 3 description:
SELECT
productid, productname
FROM Production.Products
WHERE categoryid = 4
UNION
SELECT
d.productid, p.productname
FROM Sales.OrderDetails d
INNER JOIN Production.Products p ON p.productid = d.productid
GROUP BY d.productid, p.productname
HAVING SUM(d.qty * d.unitprice) > 50000;
2.
3.
Observe the result. What is the total number of rows in the result? If you compare this number with
an aggregate value of the number of rows from task 1 and task 2, is there any difference? The total
number of rows retrieved by the query is 12. This is 2 rows less than the aggregate value of rows from
the query in task 1 (10 rows) and task 2 (4 rows).
4.
Highlight the previous query. On the toolbar, click Edit and then Copy.
5.
In the query window, click the line after the written T-SQL statement. On the toolbar, click Edit and
then Paste.
6.
Modify the T-SQL statement by replacing the UNION operator with the UNION ALL operator. The
query should look like this:
SELECT
productid, productname
FROM Production.Products
WHERE categoryid = 4
UNION ALL
SELECT
d.productid, p.productname
FROM Sales.OrderDetails d
INNER JOIN Production.Products p ON p.productid = d.productid
GROUP BY d.productid, p.productname
HAVING SUM(d.qty * d.unitprice) > 50000;
7.
8.
Observe the result. What is the total number of rows in the result? What is the difference between the
UNION and UNION ALL operators? The total number of rows retrieved by the query is 14. It is the
same as the aggregate value of rows from the queries in task 1 and task 2. This is because UNION ALL
is a multi-set operator that returns all rows that appear in any of the inputs, without really comparing
rows and without eliminating duplicates. The UNION set operator removes the duplicate rows and
the result consists of only distinct rows.
So, when should you use UNION ALL and when should you use UNION when unifying two inputs? If a
potential exists for duplicates and you need to return the duplicates, use UNION ALL. If a potential
exists for duplicates but you need to return distinct rows, use UNION. If no potential exists for
duplicates when unifying the two inputs, UNION and UNION ALL are logically equivalent. However, in
such a case, using UNION ALL is recommended because it removes the overhead of SQL Server
checking for duplicates.
L12-95
Task 4: Write a SELECT statement to retrieve the top 10 customers by sales amount
for January 2008 and February 2008
1.
In the query pane, type the following query after the task 4 description:
SELECT
c1.custid, c1.contactname
FROM
(
SELECT TOP (10)
o.custid, c.contactname
FROM Sales.OrderValues AS o
INNER JOIN Sales.Customers AS c ON c.custid = o.custid
WHERE o.orderdate >= '20080101' AND o.orderdate < '20080201'
GROUP BY o.custid, c.contactname
ORDER BY SUM(o.val) DESC
) AS c1
UNION
SELECT c2.custid, c2.contactname
FROM
(
SELECT TOP (10)
o.custid, c.contactname
FROM Sales.OrderValues AS o
INNER JOIN Sales.Customers AS c ON c.custid = o.custid
WHERE o.orderdate >= '20080201' AND o.orderdate < '20080301'
GROUP BY o.custid, c.contactname
ORDER BY SUM(o.val) DESC
) AS c2;
2.
Exercise 2: Writing Queries That Use the CROSS APPLY and OUTER APPLY
Operators
Task 1: Write a SELECT statement that uses the CROSS APPLY operator to retrieve the
last two orders for each product
1.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
p.productid, p.productname, o.orderid
FROM Production.Products AS p
CROSS APPLY
(
SELECT TOP(2)
d.orderid
FROM Sales.OrderDetails AS d
WHERE d.productid = p.productid
ORDER BY d.orderid DESC
) o
ORDER BY p.productid;
4.
Task 2: Write a SELECT statement that uses the CROSS APPLY operator to retrieve the
top three products based on sales revenue for each customer
1.
Highlight the provided T-SQL code after the task 2 description and click Execute.
2.
In the query pane, type the following query after the provided T-SQL code:
SELECT
c.custid, c.contactname, p.productid, p.productname, p.totalsalesamount
FROM Sales.Customers AS c
CROSS APPLY dbo.fnGetTop3ProductsForCustomer (c.custid) AS p
ORDER BY c.custid;
Highlight the written query and click Execute. The query retrieved 265 rows.
Highlight the previous query in task 2. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 3 description. On the toolbar, click Edit and
then Paste.
3.
L12-97
Modify the T-SQL statement by replacing the CROSS APPLY operator with the OUTER APPLY
operator. The query should look like this:
SELECT
c.custid, c.contactname, p.productid, p.productname, p.totalsalesamount
FROM Sales.Customers AS c
OUTER APPLY dbo.fnGetTop3ProductsForCustomer (c.custid) AS p
ORDER BY c.custid;
4.
5.
Notice that the query retrieved 267 rows, which is two more rows than the previous query. If you
observe the result, you will notice two rows with NULL in the columns from the inline table-valued
function.
Highlight the previous query in task 3. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 4 description. On the toolbar, click Edit and
then Paste.
3.
Modify the T-SQL statement to filter only rows that have the productid NULL. The query should look
like this:
SELECT
c.custid, c.contactname, p.productid, p.productname, p.totalsalesamount
FROM Sales.Customers AS c
OUTER APPLY dbo.fnGetTop3ProductsForCustomer (c.custid) AS p
WHERE p.productid IS NULL;
4.
5.
Observe the result. What is the difference between the CROSS APPLY and OUTER APPLY operators?
The CROSS APPLY operator implements one logical query processing phase - it applies the right table
expression to each row from the left table, and produces a result table with the unified result sets. In
contrast, the OUTER APPLY operator returns all rows from the left table expression, even when the
right table expression returns an empty set. The OUTER APPLY operator adds a second logical phase
in which it:
Identifies rows from the left side for which the right table expression returns an empty set.
Adds those rows to the result table as outer rows with NULLs in the right sides attributes as
placeholders.
In a sense, this phase is similar to the phase that adds outer rows in a left outer join.
Highlight the provided T-SQL statement after task 5 description and click Execute.
Exercise 3: Writing Queries That Use the EXCEPT and INTERSECT Operators
Task 1: Write a SELECT statement to return all customers that bought more than 20
distinct products
1.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
o.custid
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.custid
HAVING COUNT(DISTINCT d.productid) > 20;
4.
Task 2: Write a SELECT statement to retrieve all customers from the USA, except
those that bought more than 20 distinct products
1.
In the query pane, type the following query after the task 2 description:
SELECT
custid
FROM Sales.Customers
WHERE country = 'USA'
EXCEPT
SELECT
o.custid
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.custid
HAVING COUNT(DISTINCT d.productid) > 20;
2.
Task 3: Write a SELECT statement to retrieve customers that spent more than $10,000
1.
In the query pane, type the following query after the task 3 description:
SELECT
o.custid
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.custid
HAVING SUM(d.qty * d.unitprice) > 10000;
2.
L12-99
Task 4: Write a SELECT statement that uses the EXCEPT and INTERSECT operators
1.
Highlight the query from task 2. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 4 description. On the toolbar, click Edit and
then Paste.
3.
Modify the first SELECT statement so that it selects all customers and not just those from the USA and
include the INTERSECT operator and adding the query from task 3. The query should look like this:
SELECT
c.custid
FROM Sales.Customers AS c
EXCEPT
SELECT
o.custid
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.custid
HAVING COUNT(DISTINCT d.productid) > 20
INTERSECT
SELECT
o.custid
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.custid
HAVING SUM(d.qty * d.unitprice) > 10000;
4.
5.
Observe that the total number of rows is 59. Can you explain in business terms which customers are
part of the result? Because the INTERSECT operator is evaluated before the EXCEPT operator, the
result consists of all customers, except those that bought more than 20 different products and spent
more than $10,000.
Highlight the previous query in task 4. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 5 description. On the toolbar, click Edit and
then Paste.
3.
Modify the T-SQL statement by adding a set of parentheses around the first two SELECT statements.
The query should look like this:
(
SELECT
c.custid
FROM Sales.Customers AS c
EXCEPT
SELECT
o.custid
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.custid
HAVING COUNT(DISTINCT d.productid) > 20
)
INTERSECT
SELECT
o.custid
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.custid
HAVING SUM(d.qty * d.unitprice) > 10000;
4.
5.
Observe that the total number of rows is nine. Is that result different from the result of the query in
task 4? Yes, because when you added the parentheses, the SQL Server engine first evaluated the
EXCEPT operation and then the INTERSECT operation. In business terms, this query retrieved all
customers that did not buy more than 20 distinct products and that spent more than $10,000.
What is the precedence among the set operators? SQL defines the following precedence among the
set operations: INTERSECT precedes UNION and EXCEPT, while UNION and EXCEPT are considered
equal. In a query that contains multiple set operations, first INTERSECT operations are evaluated, and
then operations with the same precedence are evaluated based on appearance order. Remember that
set operations in parentheses precede all.
L13-101
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
L13-102 Module 13: Using Window Ranking, Offset and Aggregate Functions
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L13-103
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
In the query pane, type the following query after the task 1 description:
SELECT
orderid,
orderdate,
val,
ROW_NUMBER() OVER (ORDER BY orderdate) AS rowno
FROM Sales.OrderValues;
6.
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 2 description. On the toolbar, click Edit and
then Paste.
3.
Modify the T-SQL statement by adding an additional calculated column. The query should look
like this:
SELECT
orderid,
orderdate,
val,
ROW_NUMBER() OVER (ORDER BY orderdate) AS rowno,
RANK() OVER (ORDER BY orderdate) AS rankno
FROM Sales.OrderValues;
4.
5.
Observe the results. What is the difference between the RANK and ROW_NUMBER functions? The
ROW_NUMBER function provides unique sequential integer values within the partition. The RANK
function assigns the same ranking value to rows with the same values in the specified sort columns
when the ORDER BY list is not unique. Also, the RANK function skips the next number if there is a tie
in the ranking value.
L13-104 Module 13: Using Window Ranking, Offset and Aggregate Functions
In the query pane, type the following query after the task 3 description:
SELECT
orderid,
orderdate,
custid,
val,
RANK() OVER (PARTITION BY custid ORDER BY val DESC) AS orderrankno
FROM Sales.OrderValues;
2.
Task 4: Write a SELECT statement to rank orders, partitioned by customer and order
year, and ordered by the order value
1.
In the query pane, type the following query after the task 4 description:
SELECT
custid,
val,
YEAR(orderdate) as orderyear,
RANK() OVER (PARTITION BY custid, YEAR(orderdate) ORDER BY val DESC) AS
orderrankno
FROM Sales.OrderValues;
2.
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 5 description. On the toolbar, click Edit and
then Paste.
3.
4.
L13-105
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
WITH OrderRows AS
(
SELECT
orderid,
orderdate,
ROW_NUMBER() OVER (ORDER BY orderdate, orderid) AS rowno,
val
FROM Sales.OrderValues
)
SELECT
o.orderid,
o.orderdate,
o.val,
o2.val as prevval,
o.val - o2.val as diffprev
FROM OrderRows AS o
LEFT OUTER JOIN OrderRows AS o2 ON o.rowno = o2.rowno + 1;
4.
In the query pane, type the following query after the provided T-SQL code:
SELECT
orderid,
orderdate,
val,
LAG(val) OVER (ORDER BY orderdate, orderid) AS prevval,
val - LAG(val) OVER (ORDER BY orderdate, orderid) AS diffprev
FROM Sales.OrderValues;
2.
L13-106 Module 13: Using Window Ranking, Offset and Aggregate Functions
Highlight the provided T-SQL code after the task 3 description and click Execute.
2.
In the query pane, type the following query after the provided T-SQL code:
WITH SalesMonth2007 AS
(
SELECT
MONTH(orderdate) AS monthno,
SUM(val) AS val
FROM Sales.OrderValues
WHERE orderdate >= '20070101' AND orderdate < '20080101'
GROUP BY MONTH(orderdate)
)
SELECT
monthno,
val,
(LAG(val, 1, 0) OVER (ORDER BY monthno) + LAG(val, 2, 0) OVER (ORDER BY monthno) +
LAG(val, 3, 0) OVER (ORDER BY monthno)) / 3 AS avglast3months,
val - FIRST_VALUE(val) OVER (ORDER BY monthno ROWS UNBOUNDED PRECEDING) AS
diffjanuary,
LEAD(val) OVER (ORDER BY monthno) AS nextval
FROM SalesMonth2007;
3.
L13-107
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
custid,
orderid,
orderdate,
val,
100. * val / SUM(val) OVER (PARTITION BY custid) AS percoftotalcust
FROM Sales.OrderValues
ORDER BY custid, percoftotalcust DESC;
4.
Highlight the previous query. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 2 description. On the toolbar, click Edit and
then Paste.
3.
Modify the T-SQL statement by adding an additional calculated column. The query should look
like this:
SELECT
custid,
orderid,
orderdate,
val,
100. * val / SUM(val) OVER (PARTITION BY custid) AS percoftotalcust,
SUM(val) OVER (PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS runval
FROM Sales.OrderValues;
4.
L13-108 Module 13: Using Window Ranking, Offset and Aggregate Functions
Task 3: Analyze the year-to-date sales amount and average sales amount for the last
three months
1.
In the query pane, type the following query after the task 3 description:
WITH SalesMonth2007 AS
(
SELECT
MONTH(orderdate) AS monthno,
SUM(val) AS val
FROM Sales.OrderValues
WHERE orderdate >= '20070101' AND orderdate < '20080101'
GROUP BY MONTH(orderdate)
)
SELECT
monthno,
val,
AVG(val) OVER (ORDER BY monthno ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS
avglast3months,
SUM(val) OVER (ORDER BY monthno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS ytdval
FROM SalesMonth2007;
2.
L14-109
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. In the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L14-111
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
6.
7.
In the query pane, type the following query after the provided T-SQL code:
SELECT
custid,
custgroup,
country
FROM Sales.CustGroups;
8.
9.
Modify the written T-SQL code by applying the PIVOT operator. The query should look like this:
SELECT
country,
p.A,
p.B,
p.C
FROM Sales.CustGroups
PIVOT (COUNT(custid) FOR custgroup IN (A, B, C)) AS p;
2.
Click Execute. This code modifies the view by adding two additional columns.
3.
Highlight the last query in task 1. On the toolbar, click Edit and then Copy.
4.
In the query window, click the line after the provided T-SQL code. On the toolbar, click Edit and then
Paste. The query should look like this:
SELECT
country,
p.A,
p.B,
p.C
FROM Sales.CustGroups
PIVOT (COUNT(custid) FOR custgroup IN (A, B, C)) AS p;
5.
6.
Observe the result. Is this result the same as the result from the query in task 1? The result is not the
same. More rows were returned after you modified the view.
7.
Modify the copied T-SQL statement to include additional columns from the view. The query should
look like this:
SELECT
country,
city,
contactname,
p.A,
p.B,
p.C
FROM Sales.CustGroups
PIVOT (COUNT(custid) FOR custgroup IN (A, B, C)) AS p;
8.
9.
Notice that you received the same result as the previous query. Why did you get the same number of
rows? The PIVOT operator assumes that all the columns except the aggregation element and the
spreading element are part of the grouping columns.
L14-113
Task 3: Use a common table expression (CTE) to specify the grouping element for the
PIVOT operator
1.
In the query pane, type the following query after the task 3 description:
WITH PivotCustGroups AS
(
SELECT
custid,
country,
custgroup
FROM Sales.CustGroups
)
SELECT
country,
p.A,
p.B,
p.C
FROM PivotCustGroups
PIVOT (COUNT(custid) FOR custgroup IN (A, B, C)) AS p;
2.
3.
Observe the result. Is it the same as the result of the last query in task 1? Can you explain why? The
result is the same. In this task, the CTE has provided three possible columns to the PIVOT operator. In
task 1, the view also provided three columns to the PIVOT operator.
4.
Why do you think it is beneficial to use a CTE when using the PIVOT operator? When using the PIVOT
operator, you cannot directly specify the grouping element since SQL Server automatically assumes
that all columns should be used as grouping elements, with the exception of the spreading and
aggregation elements. With a CTE, you can specify the exact columns and therefore control which
columns to use for the grouping.
Task 4: Write a SELECT statement to retrieve the total sales amount for each customer
and product category
1.
In the query pane, type the following query after the task 4 description:
WITH SalesByCategory AS
(
SELECT
o.custid,
d.qty * d.unitprice AS salesvalue,
c.categoryname
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON o.orderid = d.orderid
INNER JOIN Production.Products AS p ON p.productid = d.productid
INNER JOIN Production.Categories AS c ON c.categoryid = p.categoryid
WHERE o.orderdate >= '20080101' AND o.orderdate < '20090101'
)
SELECT
custid,
p.Beverages,
p.Condiments,
p.Confections,
p.[Dairy Products],
p.[Grains/Cereals],
p.[Meat/Poultry],
p.Produce,
p.Seafood
FROM SalesByCategory
PIVOT (SUM(salesvalue) FOR categoryname
IN (Beverages, Condiments, Confections, [Dairy Products], [Grains/Cereals],
[Meat/Poultry], Produce, Seafood)) AS p;
2.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
4.
5.
In the query pane, type the following query after the provided T-SQL code:
SELECT
country, A, B, C
FROM Sales.PivotCustGroups;
6.
Task 2: Write a SELECT statement to retrieve a row for each country and
customer group
1.
In the query pane, type the following query after the T-SQL code:
SELECT
custgroup,
country,
numberofcustomers
FROM Sales.PivotCustGroups
UNPIVOT (numberofcustomers FOR custgroup IN (A, B, C)) AS p;
2.
L14-115
Exercise 3: Writing Queries That Use the GROUPING SETS, CUBE, and
ROLLUP Subclauses
Task 1: Write a SELECT statement that uses the GROUPING SETS subclause to return
the number of customers for different grouping sets
1.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
country,
city,
COUNT(custid) AS noofcustomers
FROM Sales.Customers
GROUP BY
GROUPING SETS
(
(country, city),
(country),
(city),
()
);
4.
Task 2: Write a SELECT statement that uses the CUBE subclause to retrieve grouping
sets based on yearly, monthly, and daily sales values
1.
In the query pane, type the following query after the task 2 description:
SELECT
YEAR(orderdate) AS orderyear,
MONTH(orderdate) AS ordermonth,
DAY(orderdate) AS orderday,
SUM(val) AS salesvalue
FROM Sales.OrderValues
GROUP BY
CUBE (YEAR(orderdate), MONTH(orderdate), DAY(orderdate));
2.
Task 3: Write the same SELECT statement using the ROLLUP subclause
1.
In the query pane, type the following query after the task 3 description:
SELECT
YEAR(orderdate) AS orderyear,
MONTH(orderdate) AS ordermonth,
DAY(orderdate) AS orderday,
SUM(val) AS salesvalue
FROM Sales.OrderValues
GROUP BY
ROLLUP (YEAR(orderdate), MONTH(orderdate), DAY(orderdate));
2.
3.
L14-117
Observe the result. What is the difference between the ROLLUP and CUBE subclauses of the GROUP
BY clause? Like the CUBE subclause, the ROLLUP subclause provides an abbreviated way to define
multiple grouping sets. However, unlike CUBE, ROLLUP doesnt produce all possible grouping sets
that can be defined based on the input membersit produces a subset of those. ROLLUP assumes a
hierarchy among the input members and produces all grouping sets that make sense considering the
hierarchy. In other words, while CUBE(a, b, c) produces all eight possible grouping sets out of the
three input members, ROLLUP(a, b, c) produces only four grouping sets, assuming the hierarchy
a>b>c. ROLLUP(a, b, c) is the equivalent of specifying GROUPING SETS( (a, b, c), (a, b), (a), () ).
Which is the more appropriate subclause to use in this example? Since year, month, and day form a
hierarchy, the ROLLUP clause is more suitable. There is probably not much interest in showing
aggregates for a month irrespective of year, but the other way around is interesting.
In the query pane, type the following query after the task 4 description:
SELECT
GROUPING_ID(YEAR(orderdate), MONTH(orderdate)) as groupid,
YEAR(orderdate) AS orderyear,
MONTH(orderdate) AS ordermonth,
SUM(val) AS salesvalue
FROM Sales.OrderValues
GROUP BY
ROLLUP (YEAR(orderdate), MONTH(orderdate))
ORDER BY groupid, orderyear, ordermonth;
2.
L15-119
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L15-121
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard).
5.
In the query pane, type the following query after the task 1 description:
SELECT name, dbid, crdate
FROM sys.sysdatabases;
6.
Highlight the written query and click Execute. Observe that the query retrieved 8 rows (note that
using SQL Azure you can get a different result).
In the query pane, type the following query after the task 2 description:
SELECT
object_id, name, schema_id, type, type_desc, create_date, modify_date
FROM sys.objects;
2.
3.
Highlight the previous query. On the toolbar, click Edit and then Copy.
4.
In the query window, click the line after the written T-SQL statement. On the toolbar, click Edit and
then Paste.
5.
Modify the T-SQL statement to retrieve all distinct values for the columns type and type_desc. The
query should look like this:
SELECT DISTINCT
type, type_desc
FROM sys.objects
ORDER BY type_desc;
6.
7.
Highlight the first query. On the toolbar, click Edit and then Copy.
8.
In the query window, click the line after the written T-SQL statement. On the toolbar, click Edit and
then Paste.
9.
Modify the T-SQL statement to filter only user-defined tables. The query should look like this:
SELECT
object_id, name, schema_id, type, type_desc, create_date, modify_date
FROM sys.objects
WHERE type = N'U';
In the query pane, type the following query after the task 3 description:
SELECT
object_id, name, SCHEMA_NAME(schema_id) AS schemaname, type, type_desc,
create_date, modify_date
FROM sys.tables;
2.
3.
In the query pane, type the following query after the previous query:
SELECT
object_id, name, SCHEMA_NAME(schema_id) AS schemaname, type, type_desc,
create_date, modify_date
FROM sys.views;
4.
In the query pane, type the following query after the task 4 description:
SELECT
c.name AS columnname, c.column_id, c.system_type_id, c.max_length, c.precision,
c.scale, c.collation_name
FROM sys.columns AS c
WHERE object_id = OBJECT_ID('Sales.Customers')
ORDER BY c.column_id;
2.
L15-123
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
DB_ID() AS databaseid,
DB_NAME(DB_ID()) AS databasename,
USER_NAME() as currusername;
4.
Task 2: Write a SELECT statement to retrieve the object name and schema name
1.
In the query pane, type the following query after the task 2 description:
SELECT
name,
OBJECT_NAME(object_id) AS tablename,
OBJECT_SCHEMA_NAME(object_id) AS schemaname
FROM sys.columns;
2.
Task 3: Write a SELECT statement to retrieve all the columns from the user-defined
tables that contain the word name in the column name
1.
In the query pane, type the following query after the task 3 description:
SELECT
c.name AS columnname,
OBJECT_NAME (c.object_id) AS tablename,
OBJECT_SCHEMA_NAME(c.object_id) AS schemaname
FROM sys.columns AS c
WHERE
c.name LIKE N'%name%'
AND OBJECTPROPERTY(c.object_id, N'IsUserTable') = 1;
2.
In the query pane, type the following query after the task 4 description:
SELECT OBJECT_DEFINITION(OBJECT_ID(N'Sales.CustOrders'));
2.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following query after the task 1 description:
SELECT
session_id, login_time, host_name, language, date_format
FROM
sys.dm_exec_sessions;
4.
2.
Click Execute.
In the query pane, type the following query after the task 3 description:
SELECT
total_physical_memory_kb,
available_physical_memory_kb,
total_page_file_kb,
available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
2.
L16-125
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L16-127
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If using SQL Azure select the TSQL2012 database in the
Available Databases drop-down box.
5.
6.
7.
In the query pane, type the following T-SQL code after the previous T-SQL code:
EXECUTE Sales.GetTopCustomers;
8.
Highlight the written T-SQL code and click Execute. You have executed the stored procedure.
2.
3.
In the query pane, type the following T-SQL code after the previous T-SQL code:
EXECUTE Sales.GetTopCustomers;
4.
Highlight the written T-SQL code and click Execute. You have executed the modified stored
procedure.
5.
Compare both the code and the result of the two versions of the stored procedure. What is the
difference between them? In the modified version, the TOP option has been replaced with the
OFFSET-FETCH option. Despite this change, the result is the same.
If some applications had been using the stored procedure in task 1, would they still work properly
after the change you applied in task 2? Yes, since the result from the stored procedure is still the
same. This demonstrates a huge benefit of using stored procedures as an additional layer between
the database and the application/middle tier: Even if you change the underlying T-SQL code, the
application would work properly without any changes. There are also other benefits of using stored
procedures in terms of performance (e.g., caching and reuse of plans) and security (e.g., preventing
SQL injections).
L16-129
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
4.
Click Execute. You have modified the Sales.GetTopCustomers stored procedure to accept the
parameter @orderyear. Notice that the modified stored procedure uses a predicate in the WHERE
clause that isnt a search argument. This predicate was used to keep things simple. The best practice is
to avoid such filtering because it does not allow efficient use of indexing. A better approach would be
to use the DATETIMEFROMPARTS function to provide a search argument for orderdate:
WHERE o.orderdate >= DATETIMEFROMPARTS(@orderyear, 1, 1, 0, 0, 0, 0)
AND o.orderdate < DATETIMEFROMPARTS(@orderyear + 1, 1, 1, 0, 0, 0, 0)
5.
In the query pane, type the following T-SQL code after the previous T-SQL code:
EXECUTE Sales.GetTopCustomers @orderyear = 2007;
Notice that you are passing the parameter by namethis is considered the best practice. There is also
support for passing parameters by position. For example, the following EXECUTE statement would
retrieve the same result as the T-SQL code you just typed:
EXECUTE Sales.GetTopCustomers 2007;
6.
7.
After the previous T-SQL code, type the following T-SQL code to execute the stored procedure for the
order year 2008:
EXECUTE Sales.GetTopCustomers @orderyear = 2008;
8.
9.
After the previous T-SQL code, type the following T-SQL code to execute the stored procedure
without specifying a parameter:
EXECUTE Sales.GetTopCustomers;
Task 2: Modify the stored procedure to have a default value for the parameter
1.
2.
Click Execute. You have modified the Sales.GetTopCustomers stored procedure to have a default
value (NULL) for the @orderyear parameter. You have also added an additional logical expression to
the WHERE clause.
3.
In the query pane, type the following T-SQL code after the previous T-SQL code:
EXECUTE Sales.GetTopCustomers;
This code tests the modified stored procedure by executing it without specifying a parameter.
4.
5.
Observe the result. How do the changes to the stored procedure in task 2 influence the MyCustomers
application and the design of future applications? The changes enable the MyCustomers application
to use the modified stored procedure; no changes need to be made to the application. The changes
add new possibilities for future applications because the modified stored procedure accepts the order
year as a parameter.
L16-131
2.
Click Execute. You have modified the Sales.GetTopCustomers stored procedure to have an additional
parameter named @n. You can use this parameter to specify how many customers to retrieve. The
default value is 10.
3.
After the previous T-SQL code, type the following T-SQL code to execute the modified stored
procedure:
EXECUTE Sales.GetTopCustomers;
4.
5.
After the previous T-SQL code, type the following T-SQL code to retrieve the top five customers for
the year 2008:
EXECUTE Sales.GetTopCustomers @orderyear = 2008, @n = 5;
6.
7.
After the previous T-SQL code, type the following T-SQL code to retrieve the top 10 customers for
the year 2007:
EXECUTE Sales.GetTopCustomers @orderyear = 2007;
8.
9.
After the previous T-SQL code, type the following T-SQL code to retrieve the top 20 customers:
EXECUTE Sales.GetTopCustomers @n = 20;
Task 4: Return the result from a stored procedure using the OUTPUT clause
1.
2.
Click Execute.
3.
After the DECLARE statement, add code that uses the OUTPUT clause to return the stored procedures
result as a variable named @outcustomername. Your code together with the provided DECLARE
statement should look like this:
DECLARE @outcustomername nvarchar(30);
EXECUTE Sales.GetTopCustomers @customerpos = 1, @customername = @outcustomername
OUTPUT;
SELECT @outcustomername AS customername;
5.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following T-SQL code after the task 1 description:
EXEC sys.sp_help;
4.
5.
In the query pane, type the following T-SQL code after the previous T-SQL code:
EXEC sys.sp_help N'Sales.Customers';
6.
In the query pane, type the following T-SQL code after the task 2 description:
EXEC sys.sp_helptext N'Sales.GetTopCustomers';
2.
In the query pane, type the following T-SQL code after the task 3 description:
EXEC sys.sp_columns @table_name = N'Customers', @table_owner = N'Sales';
2.
Highlight the provided T-SQL statement under the task 4 description and click Execute.
L16-133
L17-135
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the
Action menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L17-137
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard).
5.
In the query pane, type the following T-SQL code after the task 1 description:
DECLARE @num int = 5;
SELECT @num AS mynumber;
6.
7.
In the query pane, type the following T-SQL code after the previous T-SQL code:
DECLARE
@num1 int,
@num2 int;
SET @num1 = 4;
SET @num2 = 6;
SELECT @num1 + @num2 AS totalnum;
8.
In the query pane, type the following T-SQL code after the task 2 description:
DECLARE @empname nvarchar(30);
SET @empname = (SELECT firstname + N' ' + lastname FROM HR.Employees WHERE empid =
1);
SELECT @empname AS employee;
2.
3.
Observe the result. What would happen if the SELECT statement would return more than one row?
You would get an error because the SET statement requires you to use a scalar subquery to pull data
from a table. Remember that a scalar subquery fails at runtime if it returns more than one value.
In the query pane, type the following T-SQL code after the task 3 description:
DECLARE
@empname nvarchar(30),
@empid int;
SET @empid = 5;
SET @empname = (SELECT firstname + N' ' + lastname FROM HR.Employees WHERE empid =
@empid);
SELECT @empname AS employee;
2.
Highlight the T-SQL code in task 3. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 4 description. On the toolbar, click Edit and then
Paste.
3.
In the code you just copied, add the batch delimiter GO before this statement:
SELECT @empname AS employee;
4.
5.
6.
L17-139
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following T-SQL code after the task 1 description:
DECLARE
@i int = 8,
@result nvarchar(20);
IF @i < 5
SET @result =
ELSE IF @i <= 10
SET @result =
ELSE if @i > 10
SET @result =
ELSE
SET @result =
4.
5.
This code uses a CASE expression and only one SET expression to get the same result as the previous
T-SQL code. Remember to use a CASE expression when it is a matter of returning an expression.
However, if you need to execute multiple statements, you cannot replace IF with CASE.
6.
In the query pane, type the following T-SQL code after the task 2 description:
DECLARE
@birthdate date,
@cmpdate date;
SET @birthdate = (SELECT birthdate FROM HR.Employees WHERE empid = 5);
SET @cmpdate = '19700101';
IF @birthdate < @cmpdate
PRINT 'The person selected was born before January 1, 1970'
ELSE
PRINT 'The person selected was born on or after January 1, 1970';
2.
2.
Click Execute. You have created a stored procedure named Sales.CheckPersonBirthDate. It has two
parameters: @empid, which you use to specify an employee ID, and @cmpdate, which you use as a
comparison date.
3.
In the query pane, type the following T-SQL code after the provided T-SQL code:
EXECUTE Sales.CheckPersonBirthDate @empid = 3, @cmpdate = '19900101';
4.
In the query pane, type the following T-SQL code after the task 4 description:
DECLARE @i int = 1;
WHILE @i <= 10
BEGIN
PRINT @i;
SET @i = @i + 1;
END;
2.
2.
Click Execute.
L17-141
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following T-SQL code after the task 1 description:
DECLARE @SQLstr nvarchar(200);
SET @SQLstr = N'SELECT empid, firstname, lastname FROM HR.Employees';
EXECUTE sys.sp_executesql @statement = @SQLstr;
4.
Highlight the T-SQL code in task 1. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 2 description. On the toolbar, click Edit and
then Paste.
3.
4.
L17-143
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
In the query pane, type the following T-SQL code after the task 1 description:
CREATE SYNONYM dbo.Person
FOR AdventureWorks2008R2.Person.Person;
4.
Highlight the written T-SQL code and click Execute. You have created a synonym named dbo.Person.
5.
In the query pane, type the following SELECT statement after the previous T-SQL code:
SELECT FirstName, LastName
FROM dbo.Person;
6.
2.
Click Execute.
L18-145
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the
Action menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L18-147
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard). If you are using SQL Azure you will get an error, because the
USE statement is not supported and you must manually set the database context using the Available
Databases box.
5.
6.
7.
Write a TRY / CATCH construct. Your T-SQL code should look like this:
BEGIN TRY
SELECT CAST(N'Some text' AS int);
END TRY
BEGIN CATCH
PRINT 'Error';
END CATCH;
8.
2.
Click Execute. Notice that you did not get an error because you used the TRY / CATCH construct.
3.
Modify the T-SQL code by adding two PRINT statements. The T-SQL code should look like this:
DECLARE @num varchar(20) = '0';
BEGIN TRY
PRINT 5. / CAST(@num AS numeric(10,4));
END TRY
BEGIN CATCH
PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));
PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH;
4.
5.
6.
Highlight the T-SQL code and click Execute. Notice that you get a different error number and
message.
7.
8.
Highlight the T-SQL code and click Execute. Notice that you get a different error number and
message.
Highlight the T-SQL code in task 2, step 3. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 3 description. On the toolbar, click Edit and then
Paste.
3.
4.
5.
6.
L18-149
2.
3.
Highlight the T-SQL code in task 2, step 3. On the toolbar, click Edit and then Copy.
4.
In the query window, click the line after the written stored procedure. On the toolbar, click Edit and
then Paste.
5.
6.
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
Highlight the T-SQL code in exercise 1, task 4, step 5. On the toolbar, click Edit and then Copy.
4.
In the query window, click the line after the task 1 description. On the toolbar, click Edit and
then Paste.
5.
6.
Highlight the T-SQL code in task 1. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 2 description. On the toolbar, click Edit and
then Paste.
3.
4.
L18-151
2.
After the provided code, add a THROW statement. The completed T-SQL code should look like this:
DECLARE @msg AS varchar(2048);
SET @msg = 'You are doing the exercise for Module 18 on ' + FORMAT(CURRENT_TIMESTAMP,
'MMMM d, yyyy', 'en-US') + '. It''s not an error but it means that you are near the
final module!';
THROW 50001, @msg, 1;
3.
Highlight the provided T-SQL statement under the task 4 description and click Execute.
L19-153
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the Action
menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L19-155
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not
visible, select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard).
5.
Modify the T-SQL code under the task 1 description by adding the BEGIN TRAN and COMMIT TRAN
statements. Your T-SQL code should look like this:
BEGIN TRAN;
INSERT INTO HR.Employees (lastname, firstname, title, titleofcourtesy, birthdate,
hiredate, address, city, region, postalcode, country, phone, mgrid)
VALUES (N'Johnson', N'Test 1', N'Sales Manager', N'Mr.', '19700101', '20110101',
N'Some Address 18', N'Ljubljana', NULL, N'1000', N'Slovenia', N'(386) 113322', 2);
INSERT INTO HR.Employees (lastname, firstname, title, titleofcourtesy, birthdate,
hiredate, address, city, region, postalcode, country, phone, mgrid)
VALUES (N'Robertson', N'Test 2', N'Sales Representative', N'Mr.', '19850101',
'20110601', N'Some Address 22', N'Ljubljana', NULL, N'1000', N'Slovenia', N'(386)
553344', 10);
COMMIT TRAN;
6.
7.
In the query pane, type the following query after the previous T-SQL code:
SELECT empid, lastname, firstname
FROM HR.Employees
ORDER BY empid DESC;
8.
Task 2: Delete the previously inserted rows from the HR.Employees table
1.
2.
Click Execute.
Modify the T-SQL code under the task 3 description by adding the BEGIN TRAN statement. Your
T-SQL code should look like this:
BEGIN TRAN;
INSERT INTO HR.Employees (lastname, firstname, title, titleofcourtesy, birthdate,
hiredate, address, city, region, postalcode, country, phone, mgrid)
VALUES (N'Johnson', N'Test 1', N'Sales Manager', N'Mr.', '19700101', '20110101',
N'Some Address 18', N'Ljubljana', NULL, N'1000', N'Slovenia', N'(386) 113322', 2);
INSERT INTO HR.Employees (lastname, firstname, title, titleofcourtesy, birthdate,
hiredate, address, city, region, postalcode, country, phone, mgrid)
VALUES (N'Robertson', N'Test 2', N'Sales Representative', N'Mr.', '19850101',
'20110601', N'Some Address 22', N'Ljubljana', NULL, N'1000', N'Slovenia', N'(386)
553344', 10);
2.
3.
In the query pane, type the following query after the previous T-SQL code:
SELECT empid, lastname, firstname
FROM HR.Employees
ORDER BY empid DESC;
4.
5.
In the query pane, type the following statement after the SELECT statement:
ROLLBACK TRAN;
6.
7.
Again highlight the SELECT statement shown in step 3 and click Execute.
2.
Click Execute.
L19-157
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
Highlight only the following SELECT statement under the task 1 description:
SELECT empid, lastname, firstname
FROM HR.Employees
ORDER BY empid DESC;
4.
Click Execute.
5.
In the provided T-SQL code, highlight the code between the BEGIN TRAN and COMMIT TRAN
statements. Your highlighted T-SQL code should look like this:
BEGIN TRAN;
INSERT INTO HR.Employees (lastname, firstname, title, titleofcourtesy, birthdate,
hiredate, address, city, region, postalcode, country, phone, mgrid)
VALUES (N'Johnson', N'Test 1', N'Sales Manager', N'Mr.', '19700101', '20110101',
N'Some Address 18', N'Ljubljana', NULL, N'1000', N'Slovenia', N'(386) 113322', 2);
INSERT INTO HR.Employees (lastname, firstname, title, titleofcourtesy, birthdate,
hiredate, address, city, region, postalcode, country, phone, mgrid)
VALUES (N'Robertson', N'Test 2', N'Sales Representative', N'Mr.', '19850101',
'10110601', N'Some Address 22', N'Ljubljana', NULL, N'1000', N'Slovenia', N'(386)
553344', 10);
COMMIT TRAN;
6.
Click Execute. Notice that you get a conversion error in the second INSERT statement.
7.
Again highlight the SELECT statement shown in step 3 and click Execute.
2.
Click Execute.
Modify the T-SQL code under the task 3 description to look like this:
BEGIN TRY
BEGIN TRAN;
INSERT INTO HR.Employees (lastname, firstname, title, titleofcourtesy, birthdate,
hiredate, address, city, region, postalcode, country, phone, mgrid)
VALUES (N'Johnson', N'Test 1', N'Sales Manager', N'Mr.', '19700101', '20110101',
N'Some Address 18', N'Ljubljana', NULL, N'1000', N'Slovenia', N'(386) 113322', 2);
INSERT INTO HR.Employees (lastname, firstname, title, titleofcourtesy, birthdate,
hiredate, address, city, region, postalcode, country, phone, mgrid)
VALUES (N'Robertson', N'Test 2', N'Sales Representative', N'Mr.', '19850101',
'10110601', N'Some Address 22', N'Ljubljana', NULL, N'1000', N'Slovenia', N'(386)
553344', 10);
PRINT 'Commit the transaction...';
COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
PRINT 'Rollback the transaction...';
ROLLBACK TRAN;
END
END CATCH;
2.
3.
In the query pane, type the following query after the modified T-SQL code:
SELECT empid, lastname, firstname
FROM HR.Employees
ORDER BY empid DESC;
4.
2.
Click Execute.
L20-159
On the host computer, click Start, point to Administrative Tools, and click Hyper-V Manager.
2.
3.
4.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
In the Virtual Machine Connection window, wait until the Press CTRL+ALT+DELETE to log on
message appears, and then close the Virtual Machine Connection window.
5.
In the Virtual Machine Connection window, click the Revert toolbar icon.
6.
If you are prompted to confirm that you want to revert, click Revert. Wait for the revert action to
complete.
7.
In the Virtual Machine Connection window, click the Ctrl-Alt-Delete menu item on the
Action menu.
Password: Pa$$w0rd
8.
In the Virtual Machine Connection window, click Full Screen Mode on the View menu.
9.
If the Server Manager window appears, check the Do not show me this console at logon check
box and close the Server Manager window.
10. On the virtual machine, click Start, click All Programs, click Microsoft SQL Server 2012, and click
SQL Server Management Studio.
11. In the Connect to Server window, depending on the type of deployment (ask your instructor for a
current list of Microsoft SQL Azure enabled labs):
For an on-premises Microsoft SQL Server instance, type Proseware in the Server name text box.
For Windows Azure, type <4 part name of Azure server> in the Server Name text box.
12. Click the Options button. Under Connection Properties, select <Browse server> in the Connect to
database list. Choose Yes when prompted for the connection to the database. Under User
Databases, select the TSQL2012 database.
13. Choose the authentication type, depending on the type of deployment:
For an on-premises Microsoft SQL Server instance, click the Login tab, select Windows
Authentication in the Authentication list, and click Connect.
For Windows Azure, click the Login tab, select SQL Server Authentication in the
Authentication list, type your login name in the Login text box and the password in the
Password text box, and click Connect.
L20-161
2.
3.
In Solution Explorer, double-click the query 51 - Lab Exercise 1.sql. (If Solution Explorer is not visible,
select Solution Explorer on the View menu or press Ctrl+Alt+L on the keyboard.)
4.
When the query window opens, highlight the statement USE TSQL2012; and click Execute on the
toolbar (or press F5 on the keyboard).
5.
In the query pane, highlight the T-SQL code after the task 1 description and click Execute.
In the query pane, type the following query after the task 2 description:
SELECT orderid, custid, orderdate
FROM Sales.TempOrders;
2.
Highlight the written query and click Display Estimated Execution Plan.
3.
In the Results pane, click the Execution plan tab. Hover your mouse pointer over the Table Scan
operator and look at the properties displayed in the yellow tooltip box.
4.
Position your mouse pointer over the arrow between the SELECT operator and the Table Scan
operator in the execution plan. You should see three properties: Estimated Number of Rows,
Estimated Data Size, and Estimated Row Size.
5.
Right-click the SELECT operator and click Properties in the context menu.
6.
7.
8.
In the Results pane, click the Execution plan tab and observe the actual execution plan.
Highlight the previous query in task 2. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 3 description. On the toolbar, click Edit and
then Paste.
3.
In the query pane, alter the copied query to look like this:
SELECT TOP (1) orderid, custid, orderdate
FROM Sales.TempOrders;
4.
Highlight the altered query and click Display Estimated Execution Plan.
5.
Compare this tasks execution plan with the execution plan in the previous task. Which operator is
new? The TOP operator is new.
Highlight the query in task 2. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 4 description. On the toolbar, click Edit and
then Paste.
3.
Highlight the query in task 3. On the toolbar, click Edit and then Copy.
4.
In the query window, click the line after the copied SELECT statement. On the toolbar, click Edit and
then Paste.
5.
Highlight both SELECT statements and click Display Estimated Execution Plan.
6.
7.
L20-163
2.
When the query window opens, highlight the statement USE TSQL2012; and click Execute.
3.
Highlight the provided T-SQL code after the task 1 description and click Execute.
4.
In the query pane, type the following query after the provided T-SQL code:
SELECT orderid, custid, orderdate
FROM Sales.TempOrders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 6;
5.
6.
Highlight the written query and click Display Estimated Execution Plan.
In the query pane, type the following T-SQL statement after the task 2 description:
SET STATISTICS IO ON;
2.
3.
Highlight the query in task 1. On the toolbar, click Edit and then Copy.
4.
In the query window, click the line after the written T-SQL statement. On the toolbar, click Edit and
then Paste.
5.
6.
In the Results pane, click the Messages tab and observe the number of logical reads.
Task 3: Modify the SELECT statement to use a search argument in the WHERE clause
1.
Highlight the query in task 1. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 3 description. On the toolbar, click Edit and
then Paste.
3.
4.
5.
Highlight the modified query and click Display Estimated Execution Plan.
Highlight the query in task 1. On the toolbar, click Edit and then Copy.
2.
In the query window, click the line after the task 4 description. On the toolbar, click Edit and
then Paste.
3.
Highlight the query in task 3. On the toolbar, click Edit and then Copy.
4.
In the query window, click the line after the copied SELECT statement. On the toolbar, click Edit and
then Paste.
5.
6.
Highlight both SELECT statements and click Display Estimated Execution Plan.
7.
Compare the execution plans for the two queries. Why is the SELECT statement from task 3 so much
faster? This SELECT statement efficiently uses the created clustered index and does a clustered index
seek operation. The SELECT statement from task 1 does a clustered index scan (i.e., table scan).