Executive Summary:
The data stored in a Microsoft SQL Server database is useless without the ability to retrieve and view it. T-SQL's SELECT statement is what you use to get data out of the database and onto your screen. Writing T-SQL queries in SQL Server 2005's Query Editor or SQL Server 2000's Query Analyzer might seem difficult because SELECT statements are usually long and complex. However, if you break the SELECT statement into its basic components, you'll find that writing T-SQL queries isn't that hard after all.
|
Welcome to T-SQL 101. Over the course of
the next 10 lessons, you’ll learn the basic
principles to follow when writing queries
with T-SQL. By the end of the course, you’ll be able to
not only write query statements to view and manipulate
data but also write custom stored procedures and functions.
To start you on this journey, let’s look at what
T-SQL is, the query tools you use with it, and how to
write the SELECT statements that the query tools use
to retrieve the data you need.
What Is T-SQL?
In the early 1970s, IBM developed SQL (short for
Structured Query Language) for use in its original
relational database product named System R. SQL has
one purpose: to query and manipulate data.
T-SQL (short for Transact SQL) is an extension
to SQL that adds extra programming functionality
and control mechanisms. T-SQL is the query language
used by not only Microsoft SQL Server but also other
database products, such as Sybase’s Adaptive Server
Enterprise (ASE).
The Query Tools
Depending on which version of SQL Server you’re
running, you’ll have different query tools available. In
SQL Server 2000, the tools are Query Analyzer and
Enterprise Manager. Query Analyzer’s primary purpose
is to execute the T-SQL commands that you write.
Enterprise Manager is more of an administrative tool.
It has excellent built-in query-building tools, but they
can be overwhelming for uninitiated users.
In SQL Server 2005, SQL Server Management
Studio (SSMS) replaces Query Analyzer and Enterprise
Manager. The component of SSMS that you use
to execute queries is called the Query Editor. All the
T-SQL commands presented in this course will work in
both SQL Server 2005’s Query Editor and SQL Server
2000’s Query Analyzer.
Retrieving Data with SELECT
A database stores data in tables. Data in a table is
broken into columns and rows. An Employee table, for
example, would contain information about employees.
The columns in the Employee table might include
EmployeeNumber, FirstName, LastName, Age, and
Salary. A row of data in the Employee table would
contain data pertaining to a specific employee.
The data stored in a database is useless without
the ability to retrieve and view it. T-SQL’s SELECT
statement is what you use to get data out of the database
and onto your screen. Querying databases with a
SELECT statement is like shopping for groceries with
a grocery list. Think of the database as a grocery store
in which the database tables are the shelves and the data
is the items on the shelves. Your assistant (i.e., Query
Editor or Query Analyzer) will be doing the grocery
shopping for you, so you write a grocery list (i.e., a
SELECT statement) that describes the items you need
and contains instructions on where to find those items
on the shelves and how you want the items delivered.
Your assistant goes to the grocery store and diligently
travels up and down the aisles, searching the shelves for
the items that you specified, carefully following your
instructions. The assistant also follows your instructions
on how to pack the items in a bag (i.e., a result set)
before delivering that bag to you. If all the items were
in stock, your bag will contain everything you need.
Now that you know how the SELECT statement
is used, let’s take a look at what it contains. A basic
SELECT statement consists of two parts and looks like Part1 is where you specify the columns that contain the
data you need. Part2 is where you specify the table or
view containing those columns. The columns that you
select must exist in specified table or view; otherwise,
an error will result.
The simplest form of the SELECT statement tells
the Query Editor or Query Analyzer to give you the data in all the columns in the specified table or view.
For example, if you type
SELECT * FROM sysobjects
in the Query Editor or Query Analyzer, highlight the
code, then press CTRL-E, you’ll see all the data stored in
the sysobjects table in the result pane. The asterisk (*) is a
wildcard that tells the Query Editor or Query Analyzer to
retrieve the data from all the columns in the table.
In the instructions I just gave, note that I had
you highlight the code prior to executing it. Highlighting
a code snippet ensures that only that code
is executed. Otherwise, all the code in the query
window will be executed. Also note that I told you
to press CTRL-E. Alternatively, you can press F5 or
click the Execute button on the toolbar to execute
the code. I prefer using CTRL-E, though, because it
goes hand-in-hand with
CTRL-D and CTRL-T,
which let you put the
results in a grid format or
text format, respectively.
Sysobjects is a system
table that exists in every
SQL Server database
and contains entries for
each database object
within that database. If
you don’t specify a database
when establishing
a connection to SQL
Server, you’re automatically
connected to the
default database, which
is the master database
(unless you changed this default). For the purposes here, I’m assuming you’re
connected to the master database. Typically, you
wouldn’t spend much time querying tables in the
master database. However, since all instances of SQL
Server have a master database, it serves as a good place
to run the sample queries.
Ordering and Renaming
Columns
If you don’t want data from all the columns in a table,
you can specify the column or columns you want.
When you have multiple columns, you need to separate
them with commas. The order of the columns in
the SELECT statement determines the order of the
columns in the result set. For example, when I run
the query
SELECT name, type, crdate
FROM sysobjects
Continue to page 2
Previous
