Read Build Your Own ASP.NET 3.5 Website Using C# & VB Online
Authors: Cristian Darie,Zak Ruvalcaba,Wyatt Barnett
Tags: #C♯ (Computer program language), #Active server pages, #Programming Languages, #C#, #Web Page Design, #Computers, #Web site development, #internet programming, #General, #C? (Computer program language), #Internet, #Visual BASIC, #Microsoft Visual BASIC, #Application Development, #Microsoft .NET Framework
Speaking SQL
313
The Number of Affected Rows
As you can see in Figure 8.4
, SQL Server reports the number of records that have been affected by a certain query. This report doesn’t indicate that those records
were modified. Instead, the figure represents the number of rows that were read,
modified, deleted, or inserted by a certain query.
Let’s move on and take a look at some variations of the SELECT query. Then we’ll
see how easy it is to insert, modify, and delete items from the database using other
keywords.
Selecting Certain Fields
If you didn’t want to select all the fields from the database table, you’d include the
names of the specific fields that you wanted in place of the * in your query. For
example, if you’re interested only in the department names—not their IDs—you
could execute the following query:
FROM Departments
This statement would retrieve data from the Department field only. Rather than
specifying the *, which would return all the fields within the database table, we
specify only the fields that we need.
Selecting All Columns Using *
To improve performance in real-world development scenarios, it’s better to ask
only for the columns that are of interest, rather than using *. Moreover, even when
you need all the columns in a table, it’s better to specify them by name, to safeguard
against the possibility that future changes, which cause more columns to be added
to the table, affecting the queries you’re writing now.
It’s important to note that the order of the fields in a table determines the order in
which the data will be retrieved. Take this query, for example:
SELECT DepartmentID, Department
Licensed to [email protected]
314
Build Your Own ASP.NET 3.5 Web Site Using C# & VB
You could reverse the order in which the columns are returned with this query:
SELECT Department, DepartmentID
Executing this query would produce the result set shown in
Figure 8.5
. Figure 8.5. Retrieving department names and their IDs
Try it for yourself!
Selecting Unique Data with DISTINCT
Say you want to find out which cities your employees hail from. Most likely, a query
such as the one shown below would generate multiple results:
FROM Employees
If this query were applied to the Dorknozzle application, the same city location
would appear six times in the results—once for every employee in our database.
Figure 8.6 illustrates this point.
That’s not usually what we want to see in our results. Typically, we prefer to see
the
unique
cities in the list—a task that, fortunately enough, is easy to achieve.
Adding the DISTINCT keyword immediately after the SELECT clause extracts only
Licensed to [email protected]
Speaking SQL
315
Figure 8.6. Reading the employees’ cities
the unique instances of the retrieved data. Take a look at the following SQL statement:
SELECT
DISTINCT
City
This query will produce the result shown in Figure 8.7.
Figure 8.7. Selecting distinct cities
In this case, because only the City column was included within the SQL query,
unique instances within the City column were returned.
Note that the uniqueness condition applies to the whole of the returned rows. If,
for example, we asked for the name of each employee as well, all the rows would
be considered unique (because no two employees have the same name) and no row
would be eliminated by DISTINCT. To see for yourself, execute this query:
Licensed to [email protected]
316
Build Your Own ASP.NET 3.5 Web Site Using C# & VB
SELECT DISTINCT Name, City
The results of this code are pictured in Figure 8.8
. As we expected, the DISTINCT
clause doesn’t have any effect, since each row is unique.
Figure 8.8. Retrieving employees and cities
Row Filtering with WHERE
The WHERE clause is used in conjunction with SQL queries, including the SELECT
query, to deliver more refined search results based on individual field criteria. The
following example could be used to extract all employees that work in the Department whose ID is 6: SELECT Name, DepartmentID
WHERE DepartmentID = 6
This query returns the results shown below:
Name
DepartmentID
-------------------------------------------------------------Ted Lindsey
6
Shane Weebe
6
(2 row(s) affected)
Licensed to [email protected]
Speaking SQL
317
But wait! How do I know the name of the department with the ID of 6? Well, you
could use a similar query to find out. Try this:
FROM Departments
WHERE DepartmentID = 6
Executing this query reveals that the department with the ID of 6 is Engineering:
Department
-------------------------------------------------Engineering
(1 row(s) affected)
Selecting Ranges of Values with BETWEEN
There may be times when you’ll want to search within a database table for rows
that fall within a certain range of values. For instance, if you wanted to retrieve
from the Departments table all departments that have IDs between 2 and 5, you
could use the BETWEEN keyword like so:
SELECT DepartmentID, Department
WHERE DepartmentID BETWEEN 2 AND 5
As we requested, all departments whose IDs are between 2 and 5 are returned. Note
that the range is inclusive, so departments with IDs of 2 and 5 will also be retrieved.
Keep in mind that any conditions that use BETWEEN could easily be rewritten by
combining two “greater than or equal” and “less than or equal” conditions:
SELECT DepartmentID, Department
WHERE DepartmentID >= 2 AND DepartmentID <= 5
We could also use the NOT keyword before the BETWEEN keyword to specify all items
that fall outside the range, as follows:
Licensed to [email protected]
318
Build Your Own ASP.NET 3.5 Web Site Using C# & VB
SELECT DepartmentID, Department
WHERE DepartmentID NOT BETWEEN 2 AND 5
In this example, all rows whose DepartmentIDs are less than 2 or greater than 5 are
returned.
Matching Patterns with LIKE
As we’ve just seen, the WHERE clause allows us to filter results based on criteria that
we specify. The example we discussed earlier filtered rows by comparing two
numbers, but SQL also knows how to handle strings. For example, if we wanted to
search the company’s Employees table for all employees named Zak Ruvalcaba,
we'd use the following SQL statement:
SELECT EmployeeID, Username
WHERE Name = 'Zak Ruvalcaba'
However, we won’t see many such queries in reality. In real-world scenarios, most
record matching is done by matching the primary key of the table to some specific
value. When an arbitrary string such as a name is used (as in the example above),
it’s likely that we’re searching for data based on partially complete information.
A more realistic example is one in which we want to find all employees with the
surname Ruvalcaba. The LIKE keyword allows us to perform pattern matching with
the help of
wildcard characters
. The wildcard characters supported by SQL Server
are the percentage symbol (%), which matches any sequence of zero or more characters, and the underscore symbol (_), which matches exactly one character. If we wanted to find all names within our Employees table with the surname of
Ruvalcaba, we could modify the SQL query using a wildcard, as follows:
SELECT EmployeeID, Name
WHERE Name LIKE '%Ruvalcaba'
With this query, all records in which the Name column ends with Ruvalcaba are
returned, as shown below.
Licensed to [email protected]
Speaking SQL
319
EmployeeID Name
------------------------------------------------------------1
Zak Ruvalcaba
2
Jessica Ruvalcaba
(2 row(s) affected)
As we knew that the last name was Ruvalcaba, we only needed to place a wildcard
immediately before the last name. But what would happen if we didn’t know how
to spell the entire last name? That name
is
fairly difficult to spell! You could solve the problem by modifying your SQL statement to use two wildcards as follows:
SELECT EmployeeID, Name
WHERE Name LIKE '%Ruv%'
In this case, the wildcard is placed before and after the string Ruv. Although this
statement would return the same values we saw in the results table above, it would
also return any employees whose names (first or last) contain the sequence Ruv. As
SQL is case-insensitive, this would include the names Sarah Ruvin, Jonny Noruvitch,
Truvor MacDonald, and so on.
Using the IN Operator
We use the IN operator in SELECT queries primarily to specify a list of values that
we want to match in our WHERE clause. Let’s say we want to find all employees who
live in California, Indiana, and Maryland. You could write the following SQL
statement to accomplish this task:
SELECT Name, State
WHERE State = 'CA' OR State = 'IN' OR State = 'MD'