Tutorial for Writing SQL Express Queries
SQL Server Express is a relational database management system available free from Microsoft. A relational database allows you to store large amounts of data in tables. Each table contains data about a specific type of object like products or people. Table columns identify properties of the object such as name, address or price. Each table row contains the property values for one object.
You can retrieve and modify information in a SQL Server Express database by issuing Transact Structured Query Language (T-SQL) queries.
Other People Are Reading
Things You'll Need
- Windows 7
- SQL Server 2008 Express Edition with Tools
- AdventureWorks sample database
- User account with administrator permissions
Instructions
-
Execute Queries to Retrieve Data
-
1
Click the "Start" button in the Taskbar to display the Start menu. Choose the "All Programs" menu item. Choose the "Microsoft SQL Server 2008" menu item. Right-click the "SQL Server Management Studio" menu item, and choose "Run as Administrator." Click "Connect."
-
2
Expand the icon that has the same name as your computer, if it is not already expanded. Expand the "Databases" icon by clicking on the "+" next to it. Select "AdventureWorks." Click "New Query" in the toolbar. Now you have a query window open that you can use to execute queries against the AdventureWorks database.
-
-
3
Type the following in the query window, and click the "Execute" button to execute the query you typed:
SELECT * FROM Person.Contact
A SELECT statement retrieves data from a database. The query you just executed will have returned all rows and all columns from a table named "Person.Contact" and displayed them in the "Results" pane beneath the query window. The result of executing a SELECT statement is called a result set.
A SELECT statement has two required parts: a SELECT clause and a FROM clause. The FROM clause identifies the table from which you want to retrieve the data. The SELECT clause identifies which columns you want to retrieve from the table. The asterisk (*) is a wild card character, which returns the values in all columns.
-
4
Modify the text in the query window as follows, and press the F5 key:
SELECT ContactID, Title, FirstName, LastName, EmailPromotion FROM Person.Contact
Pressing the F5 key also executes a query. Notice that the result set includes only the ContactID, Title, FirstName, LastName and EmailPromotion data from the table. Sometimes a table contains a lot of data you do not need to view; you can list column names in the SELECT clause to limit the column values returned to only the data you need to know.
-
5
Modify the text in the query window as follows, and press the F5 key:
SELECT ContactID, Title, FirstName, LastName, EmailPromotion FROM Person.Contact WHERE EmailPromotion = 2
The search will only have returned the rows with an EmailPromotion value of 2. You accomplished this by adding a WHERE clause to the query and specifying a condition of "EmailPromotion = 2." If you need to search for rows that meet a certain condition, you can add a WHERE clause to your SELECT statement. The WHERE clause is optional and follows the FROM clause.
Sort and Summarize Data
-
6
Modify the text in the query window as follows, and press the F5 key:
SELECT ContactID, Title, FirstName, LastName, EmailPromotion FROM Person.Contact ORDER BY EmailPromotion
The search will have returned all rows but sorted them by the value in the EmailPromotion column. You made this happen by using an ORDER BY clause---another optional part of the SELECT statement used to sort the data in the result set.
-
7
Modify the text in the query window as follows, and press the F5 key:
SELECT ContactID, Title, FirstName, LastName, EmailPromotion FROM Person.Contact ORDER BY EmailPromotion DESC
When you use an ORDER BY clause without specifying the sort order, the result set will appear in ascending order (from A to Z, or lowest to highest). You can add "DESC" to the end of the ORDER BY clause to sort the data in descending order (Z to A or highest to lowest).
-
8
Modify the text in the query window as follows, and press the F5 key:
SELECT Count(*) As 'Total' FROM Person.Contact
One of the powerful features of T-SQL is the ability to summarize data. The query you just executed counted all the rows in the Person.Contact table and returned the result of the calculation in a column named Total. SQL Server performed this magic because you used "Count(*)" in the SELECT clause. "Count(*)" is known as an aggregate function because it performs a calculation on data in multiple rows and returns the result of the calculation.
-
9
Modify the line in the query window as follows, and press the F5 key:
SELECT EmailPromotion, Count(*) As 'Total' FROM Person.Contact GROUP BY EmailPromotion
Your results should look similar to the following:
EmailPromotion Total
-------------- -----------
0 11158
1 5044
2 3770The query you just executed grouped all the rows according to the value in the EmailPromotion column and counted the number of rows in each group. It then displayed a result set with two columns: one that contains the value in the EmailPromotion column and the other that contains the number of rows in the table that match that value. The GROUP BY clause is an optional part of the SELECT statement that identifies the name of the column that will separate the data into groups before summarizing it.
-
1
Tips & Warnings
You can download SQL Server 2008 Express Edition and the AdventureWorks database from Microsoft's website.
The steps provided apply to Windows 7. You can use Windows XP Service Pack 2 (or later). However, if you perform the steps on Windows XP, you will not need to choose "Run as Administrator" when opening SQL Server Management Studio.
When you use the GROUP BY clause, your SELECT clause can only include the column names listed in the GROUP BY clause and aggregate functions.
The Transact-SQL keywords such as SELECT and WHERE are not case sensitive. However, some versions of the AdventureWorks database are case sensitive. If the queries give you errors when you execute them, ensure that you have typed them exactly, including capitalization.
References
Resources
- Photo Credit searching image by dinostock from Fotolia.com
