Материал готовится,

пожалуйста, возвращайтесь позднее

пожалуйста, возвращайтесь позднее

Using Operators to

Categorize Data

What You’ll Learn in This Hour:

. What is an operator?

. An overview of operators in SQL

. How are operators used singularly?

. How are operators used in combinations?

Operators are used in conjunction with the SELECT command’s WHERE clause

to place extended constraints on data that a query returns. Various operators

are available to the SQL user that support all data querying needs. In

this hour we will show you what operators are available for you to use as

well as how to utilize them properly within the WHERE clause.

What Is an Operator in SQL?

An operator is a reserved word or a character used primarily in an SQL

statement’s WHERE clause to perform operation(s), such as comparisons and

arithmetic operations. Operators are used to specify conditions in an SQL

statement and to serve as conjunctions for multiple conditions in a statement.

The operators discussed during this hour are

. Comparison operators

. Logical operators

. Operators used to negate conditions

. Arithmetic operators

www.it-ebooks.info

116 HOUR 8: Using Operators to Categorize Data

Comparison Operators

Comparison operators test single values in an SQL statement. The comparison

operators discussed consist of =, , .

These operators are used to test

. Equality

. Non-equality

. Less-than values

. Greater-than values

Examples and the meanings of comparison operators are covered in the following

sections.

Equality

The equal operator compares single values to one another in an SQL statement.

The equal sign (=) symbolizes equality. When testing for equality, the

compared values must match exactly, or no data is returned. If two values

are equal during a comparison for equality, the returned value for the comparison

is TRUE; the returned value is FALSE if equality is not found. This

Boolean value (TRUE/FALSE) is used to determine whether data is returned

according to the condition.

You can use the = operator by itself or combine it with other operators.

Remember from the previous chapter that character data comparisons can

either be case sensitive or case insensitive depending on how your relational

database management system (RDBMS) is set up. So remember to check to

ensure that you understand how exactly your values are compared by the

query engine.

The following example shows that salary is equal to 20000:

WHERE SALARY = ‘20000’

The following query returns all rows of data where the PROD_ID is equal to

2345:

SELECT *

FROM PRODUCTS_TBL

WHERE PROD_ID = ‘2345’;

PROD_ID PROD_DESC COST

— — — — — — — — — — — — — — — — — — — — — — — -

2345 OAK BOOKSHELF 59.99

1 row selected.

www.it-ebooks.info

Comparison Operators 117

Non-Equality

For every equality, there are multiple non-equalities. In SQL, the operator

used to measure non-equality is (the less than sign combined with the

greater than sign). The condition returns TRUE if the condition finds nonequality;

FALSE is returned if equality is found.

The following example shows that salary is not equal to 20000:

WHERE SALARY ‘20000’

Options for Non-Equality

Another option comparable to is ! =. Many of the major implementations have

adopted ! = to represent not-equal. Microsoft SQL Server, MySQL, and Oracle support

both versions of the operator. Oracle actually supports a third, ^= , as

another version, but it is rarely used because most people are accustomed to

using the earlier two versions.

Did You

Know?

Categorize Data

What You’ll Learn in This Hour:

. What is an operator?

. An overview of operators in SQL

. How are operators used singularly?

. How are operators used in combinations?

Operators are used in conjunction with the SELECT command’s WHERE clause

to place extended constraints on data that a query returns. Various operators

are available to the SQL user that support all data querying needs. In

this hour we will show you what operators are available for you to use as

well as how to utilize them properly within the WHERE clause.

What Is an Operator in SQL?

An operator is a reserved word or a character used primarily in an SQL

statement’s WHERE clause to perform operation(s), such as comparisons and

arithmetic operations. Operators are used to specify conditions in an SQL

statement and to serve as conjunctions for multiple conditions in a statement.

The operators discussed during this hour are

. Comparison operators

. Logical operators

. Operators used to negate conditions

. Arithmetic operators

www.it-ebooks.info

116 HOUR 8: Using Operators to Categorize Data

Comparison Operators

Comparison operators test single values in an SQL statement. The comparison

operators discussed consist of =, , .

These operators are used to test

. Equality

. Non-equality

. Less-than values

. Greater-than values

Examples and the meanings of comparison operators are covered in the following

sections.

Equality

The equal operator compares single values to one another in an SQL statement.

The equal sign (=) symbolizes equality. When testing for equality, the

compared values must match exactly, or no data is returned. If two values

are equal during a comparison for equality, the returned value for the comparison

is TRUE; the returned value is FALSE if equality is not found. This

Boolean value (TRUE/FALSE) is used to determine whether data is returned

according to the condition.

You can use the = operator by itself or combine it with other operators.

Remember from the previous chapter that character data comparisons can

either be case sensitive or case insensitive depending on how your relational

database management system (RDBMS) is set up. So remember to check to

ensure that you understand how exactly your values are compared by the

query engine.

The following example shows that salary is equal to 20000:

WHERE SALARY = ‘20000’

The following query returns all rows of data where the PROD_ID is equal to

2345:

SELECT *

FROM PRODUCTS_TBL

WHERE PROD_ID = ‘2345’;

PROD_ID PROD_DESC COST

— — — — — — — — — — — — — — — — — — — — — — — -

2345 OAK BOOKSHELF 59.99

1 row selected.

www.it-ebooks.info

Comparison Operators 117

Non-Equality

For every equality, there are multiple non-equalities. In SQL, the operator

used to measure non-equality is (the less than sign combined with the

greater than sign). The condition returns TRUE if the condition finds nonequality;

FALSE is returned if equality is found.

The following example shows that salary is not equal to 20000:

WHERE SALARY ‘20000’

Options for Non-Equality

Another option comparable to is ! =. Many of the major implementations have

adopted ! = to represent not-equal. Microsoft SQL Server, MySQL, and Oracle support

both versions of the operator. Oracle actually supports a third, ^= , as

another version, but it is rarely used because most people are accustomed to

using the earlier two versions.

Did You

Know?

Загрузка...

Выбрать следующее задание

Ты добавил

Выбрать следующее задание

Ты добавил