Creating a Select Query Manually

Select queries retrieve a set of records from a database table. These queries are easily recognized by the SELECT and FROM keywords in their syntax. Let's take a look at a couple of examples:

SELECT LastName FROM Employees

Selects the LastName field from the Employees table. If there is a space in the field name, enclose the field name in square brackets: [Last Name].

SELECT FirstName, LastName, PhoneNo FROM Employees

Selects the FirstName, LastName, and PhoneNo fields from the Employees table.

SELECT * FROM Employees

Selects all fields for all records from the Employees table. The asterisk (*) is used to represent all fields.

Creating and Manipulating Databases with ADO

Often the WHERE clause is used with Select queries to specify criteria that determine which records the query will affect. Some examples of using the WHERE clause to restrict records are shown below:

SELECT * FROM Employees

WHERE City IN ('Redmond', 'London')

Selects from the Employees table all fields for all records that have the value Redmond or London in the City field.

SELECT * FROM Employees

ReportsTo LIKE 'Buchanan, Steven'

Selects from the Employees table all fields for all records that have the value Redmond or London in the City field and have a value Buchanan, Steven in the ReportsTo field.

SELECT * FROM Employees

(City='Redmond'))

Selects from the Employees table all fields for all records that have a value less than 1993 in the HireDate field or have the value Redmond in the City field.

Select * FROM Products

WHERE UnitPrice Between 10 and 25

Selects from the Products table all fields for all records that have an amount in the UnitPrice field between $10 and $25.

Select * from Employees WHERE ReportsTo IS NULL

Select from the Employees table all fields for all records that do not have a value in the ReportsTo field.

You can use expressions in WHERE clauses to qualify SQL statements. An SQL expression is a string that is used in SQL statements. Expressions can contain literal values, constants, field names, operators, and functions. Several operators that are often used in expressions are shown in Table 15-1.

Table 15-1: Operators commonly used in expressions

Operator Name

Description/Usage

IN

The IN operator is used to determine whether the value of an expression is equal to any of several values in a specified list. If the expression is found in the list of values, the IN operator returns True; otherwise, it returns False. You can include the NOT logical operator to determine whether the expression is not in the list of values.

For example, you can use NOT IN to determine which employees don't live in Redmond or London:

SELECT * FROM Employees

WHERE City NOT IN ('Redmond', 'London')

LIKE

The LIKE operator compares a string expression to a pattern in an SQL expression. For a pattern, you specify the complete value (for example, LIKE 'Buchanan, Steven'), or you can use wildcard characters to find a range of values (for example, LIKE 'B*'). You can use a number of wildcard characters in the LIKE operator pattern (see Table 15-2).

Part II

Operator Name

Description/Usage

BETWEEN...AND

The BETWEEN. ..AND operator is used to determine whether the value of an expression falls within a specified range of values. If the value of the expression is between valuel and value2 (inclusive), the BETWEEN...AND operator returns True; otherwise, it returns False. You can include the NOT logical operator to evaluate the opposite condition, that is, whether the expression falls outside the range defined by valuel and value2.

For example, you can select all products with the amount in the UnitPrice field less than $10 and greater than $25:

Select * FROM Products

WHERE UnitPrice NOT BETWEEN 10 and 25

IS NULL

The IS NULL operator is used to determine whether the expression value is equal to the Null value. A Null value indicates missing or unknown data. You can include the NOT logical operator to return only records that have values in the specified field. For example, you can extract only the employee records that have a value in the ReportsTo field. Records where the ReportsTo field is blank will not be included:

Select * from Employees WHERE ReportsTo IS NOT NULL

Table 15-2: Wildcard characters used in the LIKE operator patterns

Wildcard

Description

* (asterisk)

Matches any number of characters.

? (question mark)

Matches any single character.

% (percent sign)

Matches any number of characters (used only with the ADO and Jet OLE DB provider; not in the Access user interface).

_ (underscore)

Matches any single character (used only with the ADO and Jet OLE DB provider; not in the Access user interface).

# (number sign)

Matches any single digit.

[] (square brackets)

Match any single character within the list of characters enclosed within brackets.

! (exclamation point)

Matches any single character that is not found in the list enclosed within the square brackets.

- (hyphen)

Matches any one of the range of characters enclosed within the square brackets.

In addition to the WHERE clause, you can use predicates to further restrict the set of records to be retrieved. A predicate is an SQL statement that qualifies the SELECT statement, similar to the WHERE clause; however, the predicate must be placed before the column list. Several popular predicates are shown in Table 15-3.

Creating and Manipulating Databases with ADO

Table 15-3: Commonly used predicates in SQL SELECT statements

Predicate Name

Description/Usage

ALL

The ALL keyword is the default keyword and is used when no predicate is declared in the SQL statement.

The following two examples are equivalent and return all records from the Employees table:

SELECT ALL * FROM Employees ORDER BY EmployeeID;

SELECT *

FROM Employees

ORDER BY EmployeeID

DISTINCT

The DISTINCT keyword eliminates duplicate values from the returned set of records. The values for each field listed in the SELECT statement must be unique.

For example, to return a list of non-duplicate (unique) cities from the Employees table, you can write the following SELECT statement:

SELECT DISTINCT City FROM Employees

Note: The output of a query that uses DISTINCT isn't updatable (it's read-only).

DISTINCTROW

While the DISTINCT keyword is based on duplicate fields, the DISTINCTROW keyword is based on entire rows. It is used only with multiple tables.

For example, if you join the Customers and Orders tables on the CustomerID field, you can find customers that have at least one order. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders.

SELECT DISTINCTROW CompanyName FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID ORDER BY CompanyName;

Note: If you omit DISTINCTROW, this SELECT statement will produce multiple rows for each company that has more than one order. DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table or if you output fields from all tables.

Part II

Predicate Name

Description/Usage

TOP or PERCENT

The TOP keyword returns a certain number of records that fall at the top or bottom of a range specified by an ORDER BY clause. For example, suppose you want to select the five most expensive products:

SELECT TOP 5 * FROM Products ORDER BY UnitPrice DESC

The TOP predicate doesn't choose between equal values. If there are equal values present, the TOP keyword will return all rows that have the equal value.

You can also use the PERCENT keyword to return a percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause.

For example, to return the lowest 10 percent priced products, you can write the following statement:

SELECT TOP 10 PERCENT *

FROM Products

ORDER BY UnitPrice ASC;

Note: If you don't include the ORDER BY clause, the SELECT TOP statement will return a random set of rows.

If you'd like to sort records returned by the SELECT statement, use the ORDER BY clause with the ASC (ascending sort) or DESC (descending sort) keywords, as shown in the following example:

SELECT * FROM Employees ORDER BY Country DESC

Select all records from the Employees table and arrange them in descending order based on the Country field. If no order is specified, the order is ascending (ASC) by default.

By default, records are sorted in ascending order. The fields you want to sort by do not need to be enumerated in the SELECT statement's field list. Instead of sorting by field name, you can sort by field position. For example, the statement:

SELECT * FROM EMPLOYEES ORDER BY 2

will sort the records in ascending order by the second field.

0 0

Post a comment