MCT USE ONLY.
STUDENT USE PROHIBITED
L2-1
Module 2: Introduction to T-SQL Querying
Lab: Introduction to T-SQL Querying
Exercise 1: Executing Basic SELECT Statements
Task 1: Prepare the Lab Environment
1. Ensure that the 20761C-MIA-DC and 20761C-MIA-SQL virtual machines are both running, and then
log on to 20761C-MIA-SQL as ADVENTUREWORKS\Student with the password Pa55w.rd.
2. In the D:\Labfiles\Lab02\Starter folder, right-click Setup.cmd, and then click Run as
administrator.
3. In the User Account Control dialog box, click Yes, and then wait for the script to finish.
4. Press any key to close the command window.
Task 2: Execute the T-SQL Script
1. On the taskbar, click Microsoft SQL Server Management Studio.
2. In the Connect to Server dialog box, in the Server name box, type MIA-SQL, ensure Windows
Authentication is selected, and then click Connect.
3. On the File menu, point to Open, and then click Project/Solution.
4. In the Open Project dialog box, browse to the D:\Labfiles\Lab02\Starter\Project folder, and then
double-click Project.ssmssln.
5. In Solution Explorer, expand Queries, and then double-click 51 - Lab Exercise 1.sql.
6. When the query window opens, click Execute. You will notice that the TSQL 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.
Task 3: Execute a Part of the T-SQL Script
1. Highlight the following text under the Task 2 description:
SELECT firstname
,lastname
,city
,country
FROM HR.Employees;
Note: To highlight it, 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. 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.
3. On the File menu, click Close.
4. Close SQL Server Management Studio, without saving any changes.
MCT USE ONLY. STUDENT USE PROHIBITED
L2-2 Querying Data with Transact-SQL
Results: After this exercise, you should know how to open the T-SQL script and execute the whole script
or just a specific statement inside it.
Exercise 2: Executing Queries That Filter Data Using Predicates
Task 1: Execute the T-SQL Script
1. On the taskbar, click Microsoft SQL Server Management Studio.
2. In the Connect to Server dialog box, in the Server name box, type MIA-SQL, and then click
Options.
3. On the Connection Properties tab, in the Connect to database list, ensure <default> is selected,
and then click Connect.
4. On the File menu, point to Open, and then click Project/Solution.
5. In the Open Project dialog box, browse to the D:\Labfiles\Lab02\Starter\Project folder, and then
double-click Project.ssmssln.
6. In Solution Explorer, expand Queries, and then double-click 61 - Lab Exercise 2.sql.
7. In the query pane, click Execute.
8. Notice that you get the error message:
Msg 208, Level 16, State 1, Line 18
Invalid object name 'HR.Employees'.
Why do you think this happened? This error is very common when you are beginning to learn T-SQL.
9. The message tells you that SQL Server could not find the object HR.Employees. This is because the
current database context is set to the master database (look at the Available Databases box where the
current database is displayed), but the IT department supplied T-SQL scripts to be run against the
TSQL database. So you need to change the database context from master to TSQL. You will learn how
to change the database context in the next task.
Task 2: Change the Database Context with the GUI
1. In the Available Databases list, click TSQL 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 country column. Only rows for which the logical expression evaluates to TRUE are
returned by the WHERE phase to the subsequent logical query processing phase.
Task 3: Change the Database Context with T-SQL
1. In the script 61 - Lab Exercise 2.sql, find the lines:
--USE TSQL;
--Go
2. Delete the first two characters, so that the line looks like this:
USE TSQL;
GO
