 Simple
Queries in SQL Server
A sample from
Microsoft SQL Server 2005 A Beginner's Guide
by Dusan Petkovic
This chapter and the next describe the most important Transact-SQL
statement - SELECT. In this chapter you will learn how to use
the SELECT statement to perform simple queries. Every clause
in this statement is described, and numerous examples using
our sample database are given to demonstrate the practical
use of each clause. The second part of this chapter introduces
aggregate functions and the UNION operator.
In this chapter, you will learn about:
SELECT Statement: A Basic Form
The Transact-SQL language has one basic statement for retrieving
information from a database: the SELECT statement. With this
statement, it is possible to query information from
one or more tables of a database (or even from multiple databases).
The result of a SELECT statement is another table, which
is also known as a result set.
The simplest form of the SELECT statement contains both a
SELECT and a FROM clause. This form of the SELECT statement
has the
following syntax:
SELECT [ ALL |DISTINCT] column_list
FROM tab_1 [tab_alias1] [{,tab_2 [tab_alias2]}...]
tab_1, tab_2 ,. are names of tables from which information
is retrieved. tab_alias1, tab_alias2 ,. provide aliases for
the corresponding tables. An alias is another name for the
corresponding table that can be used as a shorthand way of
referring to the table or as a way to refer to two logical
instances of the same physical table. Don't worry; this will
become clearer as examples are presented.
 |
| Note |
 |
| This chapter demonstrates the retrieval of information
from a single table in a database. The next chapter
describes the use of a join operation, and therefore
the query of more than one table in a database. |
|
column_list contains one or more of the following specifications:
- The asterisk symbol (*), which specifies all columns of
the named tables in the FROM clause (or from a single table
when qualified, as in: tab_2.*)
- The explicit specification of column names to be retrieved
- The specification column_name [as] column_heading, which
is a way to replace the name of a column or to assign a new
name to an expression
- An expression
- A system or an aggregate function
|
| Note |
|
| In addition to the specifications listed above,
there are other options that will be partly presented
later in this chapter and in the next chapter. |
|
A SELECT statement can retrieve either certain columns or
rows from a table. The first operation is called projection
(or SELECT list), and the second one is called selection (or
select operation). The combination of both operations is also
possible in a SELECT statement.
|
| Note |
|
| Before
you start to execute queries in this chapter,
please re-create the entire sample database. |
|
EXAMPLE 5.1
Get full details of all departments.
USE sample
SELECT * from department
The result is
| dept_no |
dept_name |
location |
| d1 |
Research |
Dallas |
| d2 |
Accounting |
Seattle |
| d3 |
Marketing |
Dallas |
The SELECT statement
in Example 5.1 retrieves all rows and all columns from the
department table. The symbol * is shorthand
for a list of all column names in the table named in the FROM
clause, in the order in which those columns are defined in
the CREATE TABLE statement for that table. The column names
serve as column headings of the resulting output.
EXAMPLE 5.2
Get full details of all departments.
USE sample
SELECT dept_no, dept_name, location FROM department
The result is
| dept_no |
dept_name |
location |
| d1 |
Research |
Dallas |
| d2 |
Accounting |
Seattle |
| d3 |
Marketing |
Dallas |
The SELECT statement in Example 5.2 is equivalent to the SELECT
statement in Example 5.1. Generally, the FROM clause contains
several options concerning locks. All these options, together
with the notion of the transaction, will be explained in detail
in Chapter 14.
Copyright © 2006 by
McGraw-Hill Osborne Media
|
