2.2. Displaying or SELECTing Rows or Tuples from a
Table
In relational database terminology, a table is called a relation, and is
denoted by the name of the relation followed by the columns (or attributes), as shown here:
Dependent(pno, dname, relationship, sex, age)
An instance of a relation is a row of a relation (table) with values. We will use the
term "row" to refer to a line of output. Although database literature also uses the term "tuple" or
"record" in place of row, we will most often use the word "row," because "row" is more commonly used
in relational databases (and SQL Server is a relational database).
In the previous section, we showed you how to select or display particular columns
from a table, but we did not explain how to select or display specific rows. Usually you would want
to select or display only particular rows from a table. For example, you may want to list all the
dependents who are older than five, or list all the dependents who are female. In such a case, you
want only the rows WHERE the dependents are older than five, or, only the rows
WHERE the dependents are female. That is, you want to display only the rows that meet a
certain condition or criteria.
By using a WHERE clause in a SELECT statement, you can selectively
choose rows that you wish to display based on a criterion. For additional filtering, the
WHERE clause can be used with logical operators like AND and OR, and the
BETWEEN operator and its negation, NOT BETWEEN.
2.2.1. Filtering with WHERE
The WHERE clause is a row filter that is used to restrict the output of rows
(or tuples
) in a result set. When the WHERE clause is used, the SQL Server database engine selects
the rows from the table for the result set that meet the conditions listed in the WHERE
clause. So, as we have previously illustrated, if no WHERE clause is used in a query, the
query will return all rows from the table.
Following is the general syntax of a SELECT statement with a WHERE
clause:
SELECT column-names
FROM Table
WHERE criteria
For example, consider the following query:
SELECT *
FROM Dependent
WHERE sex = 'F'
This query produces 22 rows of output (of which we show the first 10 rows):
PNO DNAME RELATIONSHIP SEX AGE
------ -------------------- ------------ ---- ------
2 Mary Daughter F 9
2 Beena Spouse F 31
10 Shantu Daughter F 5
14 Rani F 3
17 Susan Daughter F 4
34 Susan Daughter F 5
34 Monica Daughter F 1
62 Hillary Daughter F 16
62 Phoebe Daughter F 12
128 Mita Daughter F NULL
.
.
.
(22 row(s) affected)
The output for this query lists all the columns of the Dependent table, but
only the rows WHERE the sex attribute has been assigned a value of F.
The WHERE clause can be used with several comparison operators:
WHERE may be used in a query in addition to ORDER BY. Following is
an example of a query that displays the dname and age from the Dependent table where the
age of the dependent is less than or equal to 5, ordered by age:
SELECT dname, age
FROM Dependent
WHERE age <= 5
ORDER BY age
This query produces 19 rows of output (of which we show the first 11 rows):
dname age
-------------------- ------
Raju 1
Sam 1
Monica 1
Prakash 1
Mithu 1
Nita 2
Rakhi 2
Jake 2
Jon 2
Mahesh 2
Rani 3
.
.
.
(19 row(s) affected)
So far we have shown you how to include only one condition in your WHERE
clause. If you want to include multiple conditions in your WHERE clause, you can use
logical operators like AND and OR, and other operators like BETWEEN and
its negation, NOT BETWEEN. The following sections discuss and illustrate the use of the
AND, OR, and BETWEEN operators, and also the NOT BETWEEN in the
WHERE clause.
2.2.2. The AND Operator
The AND is a way of combining conditions in a WHERE clause. An
AND operator is used in a WHERE clause if more that one condition is required.
Using the AND further restricts the output of rows (tuples) in the result set. For example,
consider the following query:
SELECT *
FROM Dependent
WHERE age <= 5
AND sex = 'F'
which produces the following nine rows of output:
PNO DNAME RELATIONSHIP SEX AGE
------ -------------------- ------------ ---- ------
10 Shantu Daughter F 5
14 Rani F 3
17 Susan Daughter F 4
34 Susan Daughter F 5
34 Monica Daughter F 1
128 Nita Daughter F 2
142 Rakhi Daughter F 2
153 Madhu Daughter F 5
153 Mamta Daughter F 4
(9 row(s) affected)
The output for this query lists all the columns of the Dependent table, but
only the rows WHERE the value of the age attribute is less than or equal to 5
and the sex is female. The AND means that both the criteria, age <= 5 and sex = 'F', have
to be met for the row to be included in the result set. 'F' is in single quotes in this
query because sex was defined as character data (CHAR) when the table was created. Text or
character data has to be in single quotes in SQL Server . Double quotes would not be acceptable in
SQL Server . Numeric data (e.g., age <= 5) should not be in quotes.
 |
An extensive discussion of data types is presented in the
next chapter.
|
|
2.2.3. The OR Operator
The OR operator is another way of combining conditions in a WHERE
clause. Unlike the AND operator, the OR operator allows the database engine to
select the row to be included in the result set if either of the
conditions in the WHERE clause are met. So, although you could also use the OR
operator with your WHERE clause if you wanted to include more that one condition in your
WHERE clause, either of the conditions in the WHERE clause can be met for a row to
be included in the result set.
Consider the following query:
SELECT *
FROM Dependent
WHERE age >20
OR sex = 'F'
which produces 23 rows of output (of which we are showing the first 10):
PNO DNAME RELATIONSHIP SEX AGE
------ -------------------- ------------ ---- ------
2 Mary Daughter F 9
2 Beena Spouse F 31
10 Shantu Daughter F 5
14 Rani F 3
17 Susan Daughter F 4
34 Susan Daughter F 5
34 Monica Daughter F 1
62 Tom Husband M 45
62 Hillary Daughter F 16
62 Phoebe Daughter F 12
.
.
.
(23 row(s) affected)
This output lists of all dependents who are either greater than 20 years of age or are female. The OR means that either of the criteria,
age > 20 or sex = 'F', has to be met for the row to be included in the output.
2.2.4. The BETWEEN Operator
The BETWEEN operator is yet another way of combining filtering conditions in
a WHERE clause. In SQL Server , the BETWEEN operator allows you to determine
whether a value falls within a given range of values (inclusive). The general syntax of the
BETWEEN operator is:
SELECT...
FROM
WHERE
BETWEEN value1 AND value2
For example, if we want to find all the dependents between the ages of 3 and 5, we
would type the following:
SELECT dname, age
FROM Dependent
WHERE age
BETWEEN 3 AND 5
This query produces the following nine rows of output:
dname age
-------------------- ------
Amit 3
Shantu 5
Rani 3
Susan 4
Susan 5
James 5
Sebastian 4
Madhu 5
Mamta 4
(9 row(s) affected)
|
In SQL Server , value1 in the
BETWEEN clause has to be less than value2. In some
SQL languages (for example, in Access SQL), value1 does not
have to be less than value2.
|
|
Because the operator is inclusive, the end points of the comparison have been
included in the output; that is, the BETWEEN clause takes the values from
value1 and value2.
As we will often point out, SQL statements may be written in several ways. For
example, the BETWEEN that we illustrated earlier may also be written as follows:
SELECT dname, age
FROM Dependent
WHERE age >=3
AND age <=5
This query produces the same output as the previous query. So, BETWEEN can
be considered shorthand for "greater-than-or-equal-to AND less-than-or-equal-to some
value."
2.2.5. Negating the BETWEEN Operator
The BETWEEN operator can be negated by using the keyword NOT before
the BETWEEN operator. NOT BETWEEN allows you to determine whether a value does not
occur within a given range of values. The general syntax of the NOT BETWEEN is:
SELECT...
FROM
WHERE
NOT BETWEEN value1 AND value2
For example, if we want to find all the dependents who are not between the ages of 3
and 15, we would type the following:
SELECT dname, age
FROM Dependent
WHERE age
NOT BETWEEN 3 AND 15
which would give us the following 19 rows:
dname age
-------------------- ------
Beena 31
Raju 1
Sam 1
Monica 1
Tom 45
Hillary 16
Jon 2
Prakash 1
Mithu 1
Nita 2
Barbara 26
Rakhi 2
Susan 22
Susie 22
Xi du 22
Barbara 23
Jake 2
Mahesh 2
Sally 22
(19 row(s) affected)
Here the end points of the comparison are not
included in the result set. The previous NOT BETWEEN query could also be written as
follows:
SELECT sname, class
FROM Student
WHERE class <1
OR class >3
NOT BETWEEN could be considered shorthand for "less-than OR
greater-than some value."
|
