Time Required: 60 minutes
Objective: Construct a SQL query to analyze data in a single view or table.
Description: In this activity, you will construct a simple SELECT…FROM query, filter using the WHERE clause, sort using the ORDER BY clause, group using the GROUP BY clause, and filter the grouped set of data using the HAVING clause. When creating these queries, you will gain experience using commonly used SQL functions and operators.
1. If necessary, start your computer and log on using an Administrator account.
2. Click the Start button, point to All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio. In the Connect to Server dialog box, select Database Engine as the server type, type LOCALHOST\SQLSERVERUA in the Server name text box, and then click Windows Authentication in the Authentication list box. Click Connect.
© Cengage Learning
3. Click New Query on the SQL Server Management Studio toolbar to launch a new Query Editor window, and then enter the following text in the Query Editor window:
SELECT ProductCategoryID, Name
FROM AdventureWorks2012.Production.ProductCategory;
This statement retrieves an unsorted list of the names and IDs of all product catego-ries. Note that the ProductCategory table is identified using the database name and schema name to which it belongs.
4. Click the Execute button on the toolbar or press the F5 key to run the query. See Figure 5-1.
Figure 5-1 Creating a simple query
5. Delete the text in the Query Editor window, and then type the following text in the Query Editor window:
USE AdventureWorks2012;
SELECT ProductCategoryID, Name FROM Production.ProductCategory;
6. Click the Execute button on the toolbar or press the F5 key to run the query. This statement retrieves the same result set as the statement in Step 3. However, you changed the database context in the USE statement at the start of the query; therefore, you did not need to specify the database name for the ProductCategory table in the FROM clause. See Figure 5-2.
7. Delete the text in the Query Editor window, and then type the following text:
USE AdventureWorks2012;
SELECT *
FROM Production.Product
WHERE Color = 'Black' AND StandardCost = 0;
Microsoft SQL Server 2012
5
This query instructs the database engine to retrieve all columns from the Product table, and it uses the WHERE clause to filter only on rows where the color is black and the standard cost of the product is zero.
8. Click the Execute button on the toolbar or press the F5 key to run the query. See Figure 5-3.
9. Delete the text in the Query Editor window, and then type the following:
USE AdventureWorks2012;
SELECT DISTINCT FirstName FROM Person.Person
ORDER BY FirstName ASC;
10. This query returns a unique list of first names from the Person table, ordered in ascending alphabetical order. Click the Execute button on the toolbar or press the F5 key to run the query. See Figure 5-4.
Figure 5-3 Adding the WHERE clause to filter the results
Microsoft SQL Server 2012
Figure 5-2 Changing the database context
Microsoft SQL Server 2012
11. Delete the text in the Query Editor window, and then type the following:
USE AdventureWorks2012;
SELECT FirstName + ' ' + LastName as FullName FROM Person.Person
ORDER BY FirstName, LastName ASC;
This query uses the string concatenation operator to combine the values in the First-Name and LastFirst-Name columns with a space in between, and it uses the AS keyword to give the computed column an alias name of FullName. The result set is sorted by first name followed by last name in ascending order.
12. Click the Execute button on the toolbar or press the F5 key to run the query. See Figure 5-5.
13. Delete the text in the Query Editor window, and then type the following:
USE AdventureWorks2012;
SELECT AVG(SubTotal) AS AverageOrderSubTotal FROM Sales.SalesOrderHeader;
Figure 5-5 Use the string concatenation operator to combine values
Microsoft SQL Server 2012
Figure 5-4 Return DISTINCT values only in a sorted list
Microsoft SQL Server 2012
5
This query uses the AVG aggregate function to compute the average value of the SubTotal column in the SalesOrderHeader table. It uses the AS keyword to give the computed column a suitable column alias.
14. Click the Execute button on the toolbar or press the F5 key to run the query. See Figure 5-6.
15. Delete the text in the Query Editor window, and then type the following text:
USE AdventureWorks2012;
SELECT FirstName, LastName, JobTitle, PhoneNumber FROM Sales.vSalesPerson
WHERE JobTitle LIKE '%Manager%' AND PostalCode IN ('98052', '98011') AND PhoneNumber IS NOT NULL;
This query uses a series of logical operators to build a compound condition. The result set will include any records for which the job title contains the word Manager, the postal code is either 98052 or 98011, and the phone number column contains a not null value.
16. Click the Execute button on the toolbar or press the F5 key to run the query. See Figure 5-7.
17. Delete the text in the Query Editor window. Type the following text in the Query Editor window:
USE AdventureWorks2012;
SELECT TOP 5 SalesOrderNumber, SubTotal,
CAST(ShipDate – OrderDate AS int) AS DaysToShip FROM Sales.SalesOrderHeader
WHERE OrderDate < ‘8/1/2005’ AND OrderDate >= '7/1/2005' ORDER BY SubTotal DESC;
Figure 5-6 Use an aggregate function to summarize data in a table
Microsoft SQL Server 2012
This query retrieves the five largest sales orders from July 2005 by sorting the records in descending order by subtotal and then using the TOP keyword to return the top five results. The query also calculates the number of days that the order took to ship.
Note the use of the CAST operator to convert the results of the date difference from a date into a whole number.
18. Click the Execute button on the toolbar or press the F5 key to run the query. See Figure 5-8.
19. Delete the text in the Query Editor window, and then the following text:
USE AdventureWorks2012;
SELECT TOP 5 CustomerID
SUM(SubTotal) as TotalOrders, FROM Sales.SalesOrderHeader
WHERE OrderDate < '1/1/2006' AND OrderDate >= '1/1/2005' GROUP BY CustomerID
Figure 5-8 Use a sorted list to find the five largest items
Microsoft SQL Server 2012
Figure 5-7 Use logical operators to build a compound condition
Microsoft SQL Server 2012
5
HAVING SUM(SubTotal) > 100000 ORDER BY SUM(SubTotal) DESC;
This query groups the sales orders from 2005 by customer—where the order total for the year for a given customer is more than $100,000. By sorting the total in descend-ing order, it can then retrieve the top five largest customers by sales for the year.
20. Click the Execute button on the toolbar or press the F5 key to run the query. See Figure 5-9.
21. Delete the text in the Query Editor window, but leave SQL Server Management Studio and the Query Editor window open for the next activity.