Select Data  «Prev  Next»
Lesson 2 Overview of selecting data
Objective Describe how to select data with Transact-SQL.

Selecting Data with Transact-SQL

To retrieve data from a table, you use the SELECT Transact-SQL statement. This page discusses how to retrieve data from a single table. The following two lessons show you how to use joins to retrieve data from multiple tables. You can specify the names of specific columns from which to request data, or you can request data from all columns in the table. The SELECT statement uses the following general syntax:

1) SQL Select 1 2) SQL Select 2 3) SQL Select 3 4) SQL Select 4 5) SQL Select 5 6) SQL Select 6 7) SQL Select 7

  1. select_list is either a comma separated list of columns or the * wildcard character. Also, an expression or calculation can be in the select list, such as ItemQty * 5. Additionally, a string literal or constant expression can be in the select list.
  2. new_table_name is the name of the new table to create with the results of the select statement.
  3. table_source is the name of the table from which you are requesting data.
  4. search_condition is the way you specify the actual rows in the table that are to be retrieved. If you omit the WHERE clause, every row in the table will be retrieved. So specifying a WHERE clause in your queries is generally a good idea.
  5. Group_by_expression is an expression or set of columns that are used with aggregate data.
  6. search_condition is similar to the WHERE clause search_condition, except that it is used for aggregate data
  7. Order_expression specifies one or more columns used to sort data.
Program 1 Program 2 Program 3 Program 4 Program 5 Program 6 Program 7

Select Statement

Select Statement Syntax
For example, to select all data from the employees table for everyone with the last name Smith, you would specify that you want every column returned by using the * wildcard with the following Transact-SQL statement:

SELECT *
FROM employees
WHERE LastName = 'Smith'

Alternatively, you could achieve the same result by explicitly specifying every column name in the table, like this:
SELECT EmployeeID, LastName, FirstName, 
AddressLine1, AddressLine2, City, 
State, Zip, HomePhone, HomeFax  
FROM employees
WHERE LastName = 'Smith'

What if you only wanted to return only specific columns?
You explicitly list the columns that you wish to see. For example, if you want to return only the FirstName, and HomePhone for anyone with the last name of Smith, you would use this Transact-SQL statement:

SELECT FirstName, HomePhone  
FROM employees
WHERE LastName = 'Smith'

Interacting Through the Query Window

This part of the Management Studio takes the place of what was, at one time, a separate tool that was called Query Analyzer.
The Query window is your tool for interactive sessions with a given SQL Server. It is where you can execute statements using Transact-SQL (T-SQL). I pronounce it Tee-Squeal, but it is supposed to be Tee-Sequel. T-SQL is the native language of SQL Server and its a dialect of Structured Query Language (SQL), and is largely compliant withmodern ANSI/ISO SQL standards. You will find that most RDBMS products support basic ANSI/ISO SQL compatibility. Because the Query window is where you will spend a fair amount of time, let us take a more in-depth look at this tool and get familiar with how to use it.
In the next lesson, you will learn how joins are used to retrieve data from multiple tables.