Build Your Own ASP.NET 3.5 Website Using C# & VB (59 page)

A better way to write this statement uses the IN operator as follows:

SELECT Name, State

WHERE State IN ('CA', 'IN', 'MD')

Licensed to [email protected]

320

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

If you execute this query, you’ll get the expected results. Since our database only

contains employees living in CA, only those records will be displayed.

Name

State

---------------------------------------------------------------Zak Ruvalcaba

Ca

Jessica Ruvalcaba

Ca

Ted Lindsey

Ca

Shane Weebe

Ca

David Levinson

Ca

Geoff Kim

Ca

(6 row(s) affected)

Sorting Results Using ORDER BY

Unless you specify some sorting criteria, SQL Server can’t guarantee to return the

results in a particular order. We’ll most likely receive the results sorted by the

primary key, because it’s easier for SQL Server to present the results in this way

than any other, but this ordering isn’t guaranteed. This explains why, in some of

the examples we’ve completed so far, the order of the results you see on your machine may differ from what you see in this book. The ORDER BY clause provides you with a quick way to sort the results of your query in either ascending or descending

order. For instance, to retrieve the names of your employees in alphabetical order,

you’d need to execute this command:

SELECT EmployeeID, Name

ORDER BY Name

Looks simple, doesn’t it?

EmployeeID Name

------------------------------------------------------------5

David Levinson

6

Geoff Kim

2

Jessica Ruvalcaba

4

Shane Weebe

3

Ted Lindsey

Licensed to [email protected]

Speaking SQL

321

1

Zak Ruvalcaba

(6 row(s) affected)

Note that the default ordering here is ascending (that is, it runs from A to Z). You

could add the DESC designation (for descending) to the end of the statement, to order

the results backwards:

SELECT EmployeeID, Name

ORDER BY Name DESC

If you execute this query, you’ll get the results we saw above, listed in reverse order.

You could also order the results on the basis of multiple columns—simply add a

comma after the field name and enter a second field name, as follows:

SELECT EmployeeID, Name, City

ORDER BY City, Name

In this case, the results are returned in alphabetical order by city, and any tying records (that is, any records that have the same city) will appear sorted by name.
Limiting the Number of Results with TOP

Another useful SQL keyword is TOP, which can be used together with SELECT to

limit the number of returned rows. For example, if we want to retrieve the first five

departments, and have the list ordered alphabetically, we’d use this command:

SELECT
TOP 5
Department

ORDER BY Department

Here are the results:

Department

-------------------------------------------------Accounting

Administration

Business Development

Licensed to [email protected]

322

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Customer Support

Engineering

(5 row(s) affected)

Reading Data from Multiple Tables

Until now, we’ve primarily focused on extracting data from a single table. Yet in

many real-world applications, you’ll need to extract data from multiple tables simultaneously. To do so, you’ll need to use subqueries or joins. Let’s learn about joins and subqueries by looking closely at a typical example. Say

you’re asked to build a report that displays all the employees in the Engineering

department. To find employee data, you’d normally query the Employees table, and

apply a WHERE filter on the ID of the department. That approach would work fine

in this case, except for one thing: you don’t know the ID of the Engineering department!

The solution? First, execute this query to find the ID of the Engineering department:

FROM Departments

WHERE Department = 'Engineering'

The result of this query will show that the ID of the Engineering department is 6.

Using this data, you can make a new query to find the employees in that department:

WHERE DepartmentID = 6

This query retrieves the same list of employees we saw earlier in this chapter.

So everything’s great … except that you had to execute two queries in order to do

the job! There
is
a better way: SQL is very flexible and allows you to retrieve the

intended results using a single command. You could use either subqueries or joins

to do the job, so let’s take a look at them in turn.

Licensed to [email protected]

Speaking SQL

323

Subqueries

A
subquery
is a query that’s nested inside another query, and can return data that’s

used by the main query. For example, you could retrieve details of all the employees

who work in the Engineering department like this:

FROM Employees

WHERE DepartmentID IN

(
SELECT DepartmentID

FROM Departments

WHERE Department LIKE '%Engineering'
)

In this case, the subquery (highlighted in bold) returns the ID of the Engineering

department, which is then used to identify the employees who work in that department. An embedded SELECT statement is used when you want to perform a second query within the WHERE clause of a primary query.

Note that we’re using the IN operator instead of the equality operator (=). We do so

because our subquery could return a list of values. For example, if we added another

department with the name “Product Engineering,” or accidentally added another

Engineering record to the Departments table, our subquery would return two IDs.

So, whenever we’re dealing with subqueries like this, we should use the IN operator

unless we’re
absolutely certain
that the subquery will return only one record.

Querying Multiple Tables

When using queries that involve multiple tables, it’s useful to take a look at the

database diagram you created in
Chapter 7 to see what columns exist in each table,

and to get an idea of the relationships between the tables.

Table Joins

An
inner join
allows you to read and combine data from two tables between which

a relationship is established. In
Chapter 7
, we created such a relationship between the Employees table and the Departments table using a foreign key.

Let’s make use of this relationship now, to obtain a list of all employees in the engineering department: Licensed to [email protected]

324

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

SELECT Employees.Name

INNER JOIN Employees ON Departments.DepartmentID =

Employees.DepartmentID

WHERE Departments.Department LIKE '%Engineering'

The first thing to notice here is that we qualify our column names by preceding

them with the name of the table to which they belong, and a period character (.).

We use Employees.Name rather than Name, and Departments.DepartmentID instead

of DepartmentID. We need to specify the name of the table whenever the column

name exists in more than one table (as is the case with DepartmentID); in other

cases (such as with Employees.Name), adding the name of the table is optional.

As an analogy, imagine that you have two colleagues at work named John. John

Smith works in the same department as you, and his desk is just across the aisle.

John Thomas, on the other hand, works in a different department on a different

floor. When addressing a large group of colleagues, you’d use John Smith’s full

name, otherwise people could become confused. However, it would quickly become

tiresome if you always used John Smith’s full name when dealing with 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 employees

whose records match the filter Departments.Department LIKE '%Engineering'.

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

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

associated departments. Try this query:

SELECT Employees.Name, Departments.Department

INNER JOIN Employees ON Departments.DepartmentID =

Employees.DepartmentID

The results are as you’d expect:

Licensed to [email protected]

Speaking SQL

325

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

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.

Licensed to [email protected]

326

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

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.

Here’s 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.