|
SQL for the Rest of Us
( Part 1 of 5 )
Basics of a Selection Query
Rodney POWELL
Microsoft MVP - Excel |
|
Structured Query Language (SQL), usually pronounced
"sequel", was originally developed by IBM for its mainframes.
It was adopted in 1986 as somewhat of an industry standard language
for querying databases. Virtually all relational database systems on
the market today support SQL. It enables developers to design almost
any user interface as long as SQL is used to communicate with the
database engine. Without SQL, users would be restricted to use
whatever query interface the designer delivered with the database.
If a database product supports SQL, you can build solutions with a
fair a mount of interchangeability using components from different
vendors whose software best meets your requirements. Microsoft has
several database products in its product line, all of which are SQL
compatible.
So, SQL is a language used for querying, updating, and managing
relational databases. It enables you to manipulate data in an
ad hoc manner using a fairly simple set of key words or commands.
SQL can be used to retrieve, sort, and filter specific data from a
database. Additionally, you can add, change, and delete data in a
database using a SQL statement in the form of a string. You can
even use a SQL statement to modify a database structure, such as
table definitions and fields.
The four most common parts of a SQL statement are:
| Purpose |
Example |
| 1. Specify the fields (columns) to retrieve |
SELECT CustID, ShipAddr |
| 2. Specify the source table or query recordset |
FROM Orders |
| 3. Specify the record-selection criteria |
WHERE ShipState = "TX" |
| 4. Specify a sort order |
ORDER BY CompanyName |
A SELECT command is the basic building block of a query
statements for retrieving data from database tables. It instructs
the database engine to search the specified table(s) and extract the
chosen fields. It can then also select rows that meet some criteria
and sort or group the resulting rows into a specified order.
If you need more than one field returned in a result set, specify
the fields by separating the names with commas. The FROM
clause specifies the recordset from which the data will be gathered.
SELECT [First Name],[Last Name] FROM Employees;
The square brackets [ ] around field or table names are only
necessary when they contain spaces. Note also, the semicolon
( ; ) is used to indicate the end of the SQL statement.
You can include all fields from the table by using the *
(asterisk) character instead of writing out the individual
field names.
SELECT * FROM Employees;
In addition to the basic SQL structure that specifies fields and a
table, you may add other types of clauses to the statement. SQL
statements often include the ORDER BY command, variations of JOIN
clauses, INSERT, UPDATE, DELETE, and others. Probably the most
common is the WHERE clause, which we'll examine first here.
The WHERE clause is optional. You can use a WHERE clause to
specify conditions for the query and filter records before
they are returned in the query's result set. It utilizes one or
more comparison statements using operators such as
=,>,<,>=,<=,<> or LIKE (for text
comparisons) and BETWEEN...AND (for a range of
values).
Here are examples of using a WHERE clause that only returns records
with a field matching a particular criteria. Specify multiple
criteria using the AND and OR keywords.
Date literals are always enclosed with pound signs ( # ):
SELECT * FROM Employees
WHERE HireDate >= #1/1/2000# AND Dependents = 0;
And, text data (string literals) need to be enclosed in single
quotation marks. If you use double-quotes within the query string,
the SQL interpreter will stop parsing the query when it reaches those
double-quotes.:
SELECT * FROM Employees
WHERE LastName LIKE 'p*';
- or -
SELECT * FROM Orders
WHERE ShipState = 'TX' OR 'VA';
Notice that because Salary would be a numeric field, not text, the
salaries are not enclosed in quotation marks.
SELECT * FROM Employees
WHERE Salary BETWEEN 20000 AND 40000;
There is also a WHERE clause with an IN operator that can
determine whether the value of an expression is equal to any of
several values in a list.
SELECT * FROM Orders
WHERE ShipState IN ('FL','TX','VA');
Something to add?
Let me know.
|
|
|
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
