Exercises

Connecting to the Database

To connect to an MS SQL Server you will need the following :

• Either a Windows user account with database login access
• or a an sql user registered with the database
• You will also need the hostname/ip address of the server with the database

Choose a Database

You use the USE statement to select a database to work, e.g

USE AdventureWorks;

Alternatively with SMSS(SQL Server Management Studio), you can choose the database you want to work with from the drop down.

Retrieving Specific Columns

Problem

You have a table or a view. You wish to retrieve data from specific columns.

Solution

Write a SELECT statement. List the columns you wish returned following the SELECT keyword. The following example demonstrates a very simple SELECT against the AdventureWorks database, whereby three columns are returned, along with several rows from the HumanResources.Employee table.

SELECT NationalIDNumber,    LoginID,    JobTitle 
FROM  HumanResources.Employee

Analysis

The FROM clause specifies the data source, which in this example is a table. Notice the two-part name of HumanResources.Employee. The first part (the part before the period) is the schema, and the second part (after the period) is the actual table name. A schema contains the object, and that schema is then owned by a user. Because users own a schema, and the schema contains the object, you can change the owner of the schema without having to modify object ownership.

Retrieving All Columns

Problem

You are writing an ad hoc query. You wish to retrieve all columns from a table or view without having to type all the column names.

Solution

Specify an asterisk (*) instead of a column list. Doing so causes SQL Server to return all columns from the table or view. For example:

SELECT * 
FROM  HumanResources.Employee; 

Analysis

The asterisk symbol (*) returns all columns of the table or view you are querying.

Specifying the Rows to Be Returned

Problem

You do not want to return all rows from a table or a view. You want to restrict query results to only those rows of interest.

Solution

Specify a WHERE clause giving the conditions that rows must meet in order to be returned. For example, the following query returns only rows in which the person’s title is “Ms.”

SELECT Title, FirstName, LastName
FROM Person.Person
WHERE Title = 'Ms.';

You may combine multiple conditions in a WHERE clause through the use of the keywords AND and OR. The following query looks specifically for Ms. Antrim’s data:

SELECT Title, FirstName, LastName
FROM Person.Person
WHERE Title = 'Ms.' AND
LastName = 'Antrim';

Analysis

The WHERE clause provides search conditions that determine the rows returned by the query. Search conditions are written as predicates, which are expressions that evaluate to one of the Boolean results of TRUE, FALSE, or UNKNOWN. Only rows for which the final evaluation of the WHERE clause is TRUE are returned.

Use the OR operator to specify alternate choices. Use parentheses to clarify the order of operations. For example:

WHERE Title = 'Ms.' AND
(LastName = 'Antrim' OR LastName = 'Galvin')

You can write multiple operators (AND, OR, NOT) in a single WHERE clause, but it is important to make your intentions clear by properly embedding your ANDs and ORs in parentheses. The NOT operator takes precedence (is evaluated first) before AND. The AND operator takes precedence over the OR operator. Using both AND and** **OR operators in the same WHERE clause without parentheses can return unexpected results. For example, the following query may return unintended results:

SELECT Title, FirstName, LastName
FROM Person.Person
WHERE Title = 'Ms.' AND
FirstName = 'Catherine' OR
LastName = 'Adams'

Is the intention to return results for all rows with a Title of “Ms.”, and of those rows, only include those with a FirstName of Catherine or a LastName of Adams? Or did the query author wish to search for all people named “Ms.” with a FirstName of Catherine, as well as anyone with a LastName of Adams?

Specifying A Range of Values

Problem

You wish to specify a range of values as a search condition. For example, you are querying a table having a date column. You wish to return rows having dates only in a specified range of interest.

Solution

Write a predicate involving the BETWEEN operator. That operator allows you to specify a range of values, in this case of date values. For example, to find sales orders placed between the dates July 23, 2005 and July 24, 2005:

SELECT SalesOrderID, ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '2005-07-23T00:00:00'
AND '2005-07-24T23:59:59';

Checking for NULL Values

Problem

Some of the values in a column might be NULL. You wish to identify rows having or not having NULL values.

Solution

Make use of the IS NULL and IS NOT NULL tests to identify rows having or not having NULL values in a given column. For example, the following query returns any rows for which the value of the product’s weight is unknown:

SELECT ProductID, Name, Weight
FROM Production.Product
WHERE Weight IS NULL;

Providing a List of Values

Problem

You are searching for matches to a specific list of values. You could write a string of predicates joined by OR operators. But you prefer a more easily readable and maintainable solution.

Solution

Create a predicate involving the IN operator, which allows you to specify an arbitrary list of values. For example, the IN operator in the following query tests the equality of the Color column to a list of expressions:

SELECT ProductID,
Name,
Color
FROM Production.Product
WHERE Color IN ('Silver', 'Black', 'Red');

Analysis

Use the IN operator any time you have a specific list of values. You can think of IN as shorthand for multiple OR expressions. For example, the following two WHERE clauses are semantically equivalent:

WHERE Color IN ('Silver', 'Black', 'Red')

WHERE Color = 'Silver' OR Color = 'Black' OR Color = 'Red'

Performing Wildcard Searches

Problem

You don’t have a specific value or list of values to find. What you do have is a general pattern, and you want to find all values that match that pattern.

Solution

Make use of the LIKE predicate, which provides a set of basic pattern-matching capabilities. Create a string using so-called wildcards to serve as a search expression.

The following example demonstrates using the LIKE operation with the % wildcard, searching for any product with a name beginning with the letter B:

SELECT ProductID,
Name
FROM Production.Product
WHERE Name LIKE 'B%';

Sorting Your Results

Problem

You are executing a query, and you wish the results to come back in a specific order.

###Solution

Write an ORDER BY clause into your query. Specify the columns on which to sort. Place the clause at the very end of your query.

This next example demonstrates ordering the query results by columns ProductID and EndDate:

SELECT p.Name, h.EndDate, h.ListPrice
FROM Production.Product AS p
INNER JOIN Production.ProductListPriceHistory AS h
ON p.ProductID = h.ProductID
ORDER BY p.Name,
h.EndDate;

Specifying Sort Order

Problem

You do not want the default, ascending-order sort. You want to sort by one or more columns in descending order.

Solution

Make use of the keywords ASC and ASCENDING, or DESC and DESCENDING, to specify the sort direction. Apply these keywords to each sort column as you desire. This next example sorts on the same two columns as Recipe 1-13’s query, but this time in descending order for each of those columns:

SELECT p.Name,
h.EndDate,
h.ListPrice
FROM Production.Product AS p
INNER JOIN Production.ProductListPriceHistory AS h
ON p.ProductID = h.ProductID
ORDER BY p.Name DESC,
h.EndDate DESC;

Sorting by Columns Not Selected

Problem

You want to sort by columns not returned by the query.

Solution

Simply specify the columns you wish to sort by. They do not need to be in your query results. For example, you can return a list of product names sorted by color without returning the colors:

SELECT p.Name
FROM Production.Product AS p
ORDER BY p.Color;

Forcing Unusual Sort Orders

Problem

You wish to force a sort order not directly supported by the data. For example, you wish to retrieve only the colored products, and you further wish to force the color red to sort first.

Solution

Write an expression to translate values in the data to values that will give the sort order you are after. Then order your query results by that expression. Following is one approach to the problem of retrieving colored parts and listing the red ones first:

SELECT p.ProductID,
p.Name,
p.Color
FROM Production.Product AS p
WHERE p.Color IS NOT NULL
ORDER BY CASE p.Color
WHEN 'Red' THEN NULL
ELSE p.Color
END;

Analysis

The solution takes advantage of the fact that SQL Server sorts nulls first. The CASE expression returns NULL for red-colored items, thus forcing those first. Other colors are returned unchanged. The result is all the red items first in the list, and then red is followed by other colors in their natural sort order. You don’t have to rely upon nulls sorting first. Here is another version of the query to illustrate that and one other point:

SELECT p.ProductID,
p.Name,
p.Color
FROM Production.Product AS p
WHERE p.Color IS NOT NULL
ORDER BY CASE LOWER(p.Color)
WHEN 'red' THEN ' '
ELSE LOWER(p.Color)
END;

This version of the query returns the same results as before. The value ‘Red’ is converted into a single space, which sorts before all the spelled-out color names. The CASE expression specifies LOWER(p.Color) to ensure ‘Red’, ‘RED’, ‘red’, and so forth are all treated the same.

Paging Through A Result Set

Problem

You wish to present a result set to an application user N rows at a time.

Solution

Make use of the query paging feature that is brand new in SQL Server 2012. Do this by adding OFFSET and FETCH clauses to your query’s ORDER BY clause. For example, the following query uses OFFSET and FETCH to retrieve the first 10 rows of results:

SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Changing the offset from 0 to 8 will fetch another 10 rows. The offset will skip the first eight rows. There will be a two-row overlap with the preceding result set. Here is the query:

SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 8 ROWS FETCH NEXT 10 ROWS ONLY;

You must specify an ORDER BY clause! OFFSET and FETCH are actually considered as part of that clause. If you don’t specify a sort order, then rows can come back in any order

Consider executing sequences of paging queries from within a transaction providing a snapshot or serializable isolation. You could set an isolation level as follows :

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
… /* Queries go here */
COMMIT;