MCT USE ONLY.
STUDENT USE PROHIBITED
L5-3
2. Highlight the written query and click Execute.
It is important to note that, when you are looking for a NULL, you should use the IS NULL operator,
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 have a NULL only when the row is an outer row (for example, a
NULL originating from the base table). For this purpose, three cases are safe to consider:
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, it 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.
Results: After this exercise, you should be able to filter rows of data from one or more tables by using
WHERE predicates with logical operators.
Exercise 2: Write Queries that Sort Data Using an ORDER BY Clause
Task 1: Write a SELECT Statement Using an ORDER BY Clause
1. In Solution Explorer, double-click 61 - Lab Exercise [Link].
2. In the query pane, highlight the statement USE TSQL;, and click Execute.
3. In the query pane, type the following query after the Task 1 description:
SELECT
[Link], [Link], [Link], [Link]
FROM [Link] AS c
INNER JOIN [Link] AS o ON [Link] = [Link]
WHERE
[Link] >= '20080401'
ORDER BY
[Link] DESC, [Link] ASC;
4. Highlight the written query and click Execute.
Notice the date filter. It uses a literal (constant) of a date. SQL Server recognizes “20080401” as a
character string literal, and not as a date and time literal. However, because the expression involves
two operands of different types, one needs to be implicitly converted to the other’s type. In this
example, the character string literal is converted to the column’s data type (DATETIME) because
character strings are considered lower in terms of data type precedence—with respect to date and
time data types. Data type precedence and working with date values are covered in detail in the next
module.
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.
MCT USE ONLY. STUDENT USE PROHIBITED
L5-4 Querying Data with Transact-SQL
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. Observe the error message:
Invalid column name 'mgrlastname'.
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
[Link], [Link], [Link], [Link], [Link],
[Link] AS mgrlastname, [Link] AS mgrfirstname
FROM [Link] AS e
INNER JOIN [Link] AS m ON [Link] = [Link]
WHERE
[Link] = N'Buck';
4. Highlight the written query and click Execute.
Task 3: Order the Result by the firstname Column
1. Highlight the previous query, and on the Edit menu, click Copy.
2. In the query window, click the line after the Task 3a description, and on the Edit menu, click Paste.
3. Modify the T-SQL statement to remove the WHERE clause, and add an ORDER BY clause that uses the
source column name of [Link]. Your query should look like this:
SELECT
[Link], [Link], [Link], [Link], [Link],
[Link] AS mgrlastname, [Link] AS mgrfirstname
FROM [Link] AS e
INNER JOIN [Link] AS m ON [Link] = [Link]
ORDER BY
[Link];
4. Highlight the written query and click Execute.
5. Highlight the previous query, and on the Edit menu, click Copy.
6. In the query window, click the line after the Task 3b description, and on the Edit menu, click Paste.
7. Modify the ORDER BY clause so that it uses the alias for the same column (mgrfirstname). Your query
should look like this:
SELECT
[Link], [Link], [Link], [Link], [Link],
[Link] AS mgrlastname, [Link] AS mgrfirstname
FROM [Link] AS e
INNER JOIN [Link] AS m ON [Link] = [Link]
ORDER BY
mgrfirstname;
8. Highlight the written query and click Execute.
9. Compare the results for Task 3a and 3b.
10. Why were you equally able to use a source column name or an alias column name?
Results: After this exercise, you should know how to use an ORDER BY clause.
