Sql server tutorial 07 Querying SQL Data - XADAT.COM
15795
post-template-default,single,single-post,postid-15795,single-format-standard,qode-quick-links-1.0,ajax_fade,page_not_loaded,,qode-theme-ver-11.1,qode-theme-bridge,wpb-js-composer js-comp-ver-5.1.1,vc_responsive

Sql server tutorial 07 Querying SQL Data No ratings yet.

Sql server tutorial 07 Querying SQL Data

Key Skills & Concepts

  • Use a SELECT Statement to Retrieve Data
  • Use the WHERE Clause to Define Search Conditions
  • Use the GROUP BY Clause to Group Query Results
  • Use the HAVING Clause to Specify Group Search Conditions
  • Use the ORDER BY Clause to Sort Query Results

Once the objects in a database have been created and the base tables populated with
data, you can submit queries that allow you to retrieve specific information from the
database. These queries, which usually take the form of SELECT statements, can range in
complexity from a simple statement that returns all columns from a table to a statement
that joins multiple tables, calculates values, and defines search conditions that restrict
exactly which rows of data should be returned. The SELECT statement is made up of a
flexible series of clauses that together determine which data will be retrieved. In this
chapter, you will learn how to use each of these clauses to perform basic data retrieval,
define search conditions, group query results, specify group search conditions, and order
search results.

Use a SELECT Statement to Retrieve Data

In Chapter 5, when discussing views, I introduce you to the SELECT statement. As you
might recall, the SELECT statement allows you to form intricate queries that can return
exactly the type of data you want to retrieve. It is one of the most common statements
you’ll be using as an SQL programmer, and it is also one of the most flexible and
extensive statements in the SQL standard.
The SELECT statement is a query expression that begins with the SELECT keyword
and includes a number of elements that form the expression. The basic syntax for the
SELECT statement can be split into several specific clauses that each help to refine the
query so that only the required data is returned. The syntax for the SELECT statement can
be shown as follows:
As you can see, the only required clauses are the SELECT clause and the FROM
clause. All other clauses are optional.
The FROM, WHERE, GROUP BY, and HAVING clauses are referred to as the table

expression. This portion of the SELECT statement is always evaluated first when a
SELECT statement is processed. Each clause within the table expression is evaluated in
the order listed in the syntax. The result of that evaluation is a virtual table that is used in
the subsequent evaluation. In other words, the results from the first clause evaluated are
used in the next clause. The results from that clause are then used in the following clause,
until each clause in the table expression is evaluated. For example, the first clause to be
evaluated in a SELECT statement is the FROM clause. Because this clause is required, it
is always the first clause evaluated. The results from the FROM clause are then used in the
WHERE clause, if a WHERE clause is specified. If the clause is not specified, then the
results of the FROM clause are used in the next specified clause, either the GROUP BY
clause or the HAVING clause. Once the final clause in the table expression is evaluated,
the results are then used in the SELECT clause. After the SELECT clause is evaluated, the
ORDER BY clause is evaluated.

To sum all this up, the clauses of the SELECT statement are applied in the following
order:

  •  FROM clause
  • WHERE clause (optional)
  • GROUP BY clause (optional)
  • HAVING clause (optional)
  • SELECT clause
  • ORDER BY clause (optional)

Having a basic understanding of the order of evaluation is important as you create more
complex SELECT statements, especially when working with joins and subqueries
(discussed in Chapters 11 and 12, respectively). This understanding is also helpful when
discussing each clause individually because it explains how one clause relates to other
clauses. As a result, it is a good idea for you to keep this order of evaluation in mind
throughout this chapter and in subsequent chapters that build upon various aspects of the
SELECT statement.

The SELECT Clause and FROM Clause

Now that you have a basic overview of how the SELECT statement is executed, let’s take
a closer look at the SELECT clause and the FROM clause, the two required clauses in the
statement. I’ll discuss the other clauses in separate sections throughout the remainder of
the chapter.
Let’s begin with the SELECT clause. The SELECT clause includes the optional
DISTINCT and ALL keywords. The DISTINCT keyword is used if you want to eliminate
duplicate rows from the query results, and the ALL keyword is used if you want to return
all rows in the query results. For example, suppose your database includes a table named
PERFORMER_CDS. The table includes the ARTIST_NAME column and the CD_NAME
column. Because a CD can include more than one performer, the CD name can appear
more than one time in the table. Now suppose that you want to query the table for the
name of the CDs only, but you don’t want the names repeated. You can use the DISTINCT

keyword to ensure that your query returns the name of each CD only one time, or you can
use the ALL keyword to specify that all rows be returned, even if there are duplicates. If
you don’t specify either of the keywords, the ALL keyword is assumed.

0 >1>2>3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>

No Comments

Sorry, the comment form is closed at this time.