--

T-SQL Basics: Anatomy of the Select Statement

Relational databases are important not only because of the information they store but, more importantly, for the data we retrieve from them. The select statement allows us to ask the database a question. It’s the way we retrieve information from the database system.

There are two standards that govern SQL (Structured Query Language) — ANSI (American National Standards Institute) and ISO (International Standards Organization). Microsoft SQL Server supports both of these standards. Like other database products, Microsoft SQL Server has its own dialect of SQL. This dialect for SQL Server is called T-SQL or Transact-SQL.

The basics of the select statement are the same from one database to another. However, some options of the statement vary from product to product. We’ll specifically examine SQL Server’s select statement, although it is virtually identical to other select statements.

SQL Server is one of many relational database systems. Relational databases store information in a series of tables (rows and columns) that are “related” to each other.

Let’s say we have a customer database. This database has a few different sub-topics — or entities like customer, order, vendor, product, and employee. Entities can be translated to the topic of the table. We’ll only work with a single table to keep our examples very simple.

Our table has the following structure and records (rows):

CUSTOMER

CustomerID

LastName

FirstName

Address

City

State

Zip

1000

Adams

Susan

101 Main Street

Cary

NC

27513

1001

Johnson

JoAnne

5402 Loop 1

Austin

TX

78752

1002

Smith

Ron

2201 Thomas Dr

Panama City

FL

32401

1003

Baker

Pete

2408 NW 119th

Oklahoma City

OK

73120

1004

Smith

Bill

4407 12th Avenue

Austin

TX

78746

Let’s begin by discussing the parts of the select statement. Then we will select information from one table only.

The select statement is comprised of multiple clauses. These are:

SELECT
FROM
WHERE
GROUP BY
ORDER BY
HAVING

Each of these clauses has information that follows it. These are:

SELECT column(s)
FROM table / view
WHERE condition / expression
GROUP BY column(s)
ORDER BY column(s)
HAVING aggregate function and condition / expression

The only two clauses that are required in a select statement are SELECT and FROM.

The SELECT clause identifies which columns we want to retrieve, and the FROM clause identifies the name of the table we are using. We might have something like this:

Select lastname, firstname
from customer

This gives us the output:

LastName

FirstName

Adams

Susan

Johnson

JoAnne

Smith

Ron

Baker

Pete

Smith

Bill

Notice that the headings above the column data are not title case as they are in the table. This is because my query had them listed as lower case. Our select statement indicates exactly what we want to see in the output.

Now we’ll review the optional clauses.

WHERE

The WHERE clause is a filter for the query results and uses a condition or expression. We use the WHERE clause to specify our request for only particular rows.

If I want to retrieve only those individuals with the last name of Smith then I can add a WHERE clause.

Select lastname, firstname
from customer
where lastname = ‘Smith’

This gives us the output:

CustomerID

LastName

FirstName

Address

City

State

Zip

1002

Smith

Ron

2201 Thomas Dr

Panama City

FL

32401

1004

Smith

Bill

4407 12th Avenue

Austin

TX

78746

GROUP BY

The purpose of the GROUP BY clause is to group or cluster records together based on a column or list of columns. In our example, I also use an aggregate function that counts the number of rows specifying our criteria. I also created an alias for the heading of the column with the function.

Select State, count(state) as ‘Count of Customers by State’
from customer
group by state

This gives us the output:

State

Count of Customers by State

FL

1

NC

1

OK

1

TX

2

HAVING

The HAVING clause works in conjunction with the GROUP BY clause. It’s very much like the WHERE clause since it works as a filter. However, it works as a filter on the aggregate group.

Select State, count(state) as ‘Count of Customers Greater Than 1’
from customer
group by state
having count(state) > 1

This gives us the output:

State

Count of Customers Greater Than 1

TX

2

ORDER BY

The ORDER BY clause indicates the sort order for the query results.

If I want to put my results in alphabetical order by lastname, I can add an ORDER BY clause.

Select lastname, firstname
from customer
order by lastname

This gives us the output:

LastName

FirstName

Adams

Susan

Baker

Pete

Johnson

JoAnne

Smith

Ron

Smith

Bill

Notice that Ron Smith appears before Bill Smith in our example even though “Bill” comes before “Ron” alphabetically. This is because I didn’t order by firstname, only by lastname. To correct this, I can do the following.

Select lastname, firstname
from customer
order by lastname, firstname

This gives us the output:

LastName

FirstName

Adams

Susan

Baker

Pete

Johnson

JoAnne

Smith

Bill

Smith

Ron

We saw the basics of selecting data from a single table. Look for Part II where we’ll explore querying from multiple tables.

Technology is ever changing and very few areas are stable. This makes information technology interesting, but challenging. T-SQL or Transact-SQL is one area that is stable. There is also a...

CONNECT WITH US

CATEGORIES

Global Knowledge is the worldwide leader in IT training and learning services. We empower organizations, teams and individuals with the skills and best practices necessary to leverage the technologies and competencies critical for sustained success. With over 15 corporate offices around the world, our 1,500 employees are helping clients succeed in over 100 countries. Learn more at www.globalknowledge.com.