Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition

people in your own department on a day-to-day basis. In exactly the same way,

you could always refer to a column in a database using the Table.Column form,

but it’s only necessary when there’s the potential for confusion.

As for the join itself, the code is fairly clear: we’re joining the Departments table

and the Employees table into a single, virtual table by matching the values in the

Departments.DepartmentID column with those in the Employees.DepartmentID

column. From this virtual table, we’re only interested in the names of the employ-

ees whose records match the filter Departments.Department LIKE '%Engineer-

ing'.

By eliminating the WHERE clause and adding the department’s name to the column

list, we could generate a list that contained all the employees and their associated

departments. Try this query:

SELECT Employees.Name, Departments.Department

FROM Departments

INNER JOIN Employees ON Departments.DepartmentID =

Employees.DepartmentID

The results are as you’d expect:

Name Department

-------------------------------- ---------------------------------

Zak Ruvalcaba Executive

Jessica Ruvalcaba Marketing

Ted Lindsey Engineering

Shane Weebe Engineering

David Levinson Marketing

Geoff Kim Accounting

(6 row(s) affected)

Expressions and Operators

In the wonderful world of programming, an expression is any piece of code that,

once evaluated, results in a value. For instance, 1 + 1 is a very simple expression.

In SQL, expressions work in much the same way, though they don’t necessarily

have to be mathematical. For a simple example, let’s create a list that contains

employees and their cities as single strings. Try this query:

SELECT EmployeeID, Name + ', ' + City AS NameAndCity

FROM Employees

310

Chapter 8: Speaking SQL

The results are shown below:

EmployeeID NameAndCity

---------- -------------------------------------------------------

1 Zak Ruvalcaba, San Diego

2 Jessica Ruvalcaba, San Diego

3 Ted Lindsey, San Diego

4 Shane Weebe, San Diego

5 David Levinson, San Diego

6 Geoff Kim, San Diego

(6 row(s) affected)

Note that the results of the expression are used to create a virtual column. This

column doesn’t exist in reality, but is calculated using the values of other columns.

We give this column the name NameAndCity using the AS keyword.

Expressions would be quite useless if we didn’t have operators. Over the course

of the previous sections, you’ve seen the operators =, AND, >=, <=, LIKE and IN at

work. The following is a list of operators that you’ll need to know to use SQL

effectively.

+

The addition operator adds two numbers or combines two strings.

–

The subtraction operator subtracts one number from another.

*

The multiplication operator multiplies one number with another.

/

The division operator divides one number by another.

>

The greater-than operator is used in WHERE clauses to determine whether the

first value is greater than the second. For example, the following query would

return all the records from the table whose EmployeeID is greater than ten

(i.e. 11 and up).

SELECT Name

FROM Employees

WHERE EmployeeID > 10

311

Expressions and Operators

<

The less-than operator is used in WHERE clauses to determine whether the

first value is less than the second. The result of the following query would

return from the table all records whose EmployeeID is less than ten (i.e. nine

and lower).

SELECT Name

FROM Employees

WHERE EmployeeID < 10

>=

The greater-than or equal-to operator is used in WHERE clauses to determine

whether the first value is greater than, or equal to, the second. The following

query would return the record with EmployeeID of ten, and every one after

that.

SELECT Name

FROM Employees

WHERE EmployeeID >= 10

<=

The less-than or equal-to operator is used in WHERE clauses to determine

whether the first value is less than, or equal to, the second. The result of the

following query would be the record with EmployeeID of ten, and every one

before that.

SELECT Name

FROM Employees

WHERE EmployeeID <= 10

<>, !=

This operator is used to check whether a value is not equal to a second.

OR

This operator is used with the WHERE clause in the SELECT statement. The OR

operator can be used when a certain condition needs to be met, or when only

one of two conditions needs to be met. For example, the following query’s

results would return the employees with employee IDs of 1 or 2.

SELECT Name

FROM Employees

WHERE EmployeeID = 1 OR EmployeeID = 2

312

Chapter 8: Speaking SQL