STRUCTURED QUERY LANGUAGE 889

To make a constraint part of the database, open the table that contains the field on which you want to impose a constraint, in design view. Then right-click somewhere on the table and select Property Pages. On the dialog box that appears, select the Check Constraints tab, as shown in Figure 20.12. This figure shows one of the constraints of the Products table. To view another constraint, expand the Selected Constraint drop-down list and select the name of another constraint. The names of the constraints start with the CK prefix, followed by an underscore, the name of the table, then another underscore and finally the name of the field on which the constraint applies. The CK_Products_UnitPrice constraint is the expression that appears in the Constraint Expression box: the UnitPrice field must be positive. Constraints have a syntax similar to the syntax of SQL restrictions (I’ll get into SQL in the following section) and are quite trivial.

So far, you should have a good idea about how databases are organized, what the relationships are for, and why they’re so critical for the integrity of the data stored in the tables. Now we’re going to look at ways to retrieve data from a database. To specify the rows and columns you want to retrieve from one or more tables, you must use SQL statements, which is the topic of the following section.

Note There are visual tools for specifying the information you want to retrieve from a database, and these are the tools of choice for many developers. The visual tools are nothing more than a user-friendly interface for specifying SQL statements. In the background, they generate the appropriate SQL statement, and you will get the most out of these tools if you understand the basics of SQL. I will start with an overview of SQL; after that I’ll show you how to use the Query Builder utility to specify a few advanced queries.

Structured Query Language

SQL (Structured Query Language) is a universal language for manipulating tables, and every database management system (DBMS) supports it, so you should invest the time and effort to learn it. You can generate SQL statements with point-and-click operations (the Query Builder is a visual tool for generating SQL statements), but this is no substitute for understanding SQL and writing your own statements.

SQL is a nonprocedural language. This means that SQL doesn’t provide traditional programming structures like IF statements or loops. Instead, it’s a language for specifying the operation you want to perform at an unusually high level. The details of the implementation are left to the DBMS. This is good news for nonprogrammers, but many programmers new to SQL wish it had the structure of a more traditional language. You will get used to SQL and soon be able to combine the best of both worlds: the programming model of VB and the simplicity of SQL.

Tip SQL is not case-sensitive, but it’s customary to use uppercase for the SQL statements and keywords. In the examples of this book, I use uppercase for SQL statements.

To retrieve all the company names from the Customers table of the Northwind database, you issue a statement like this one:

SELECT CompanyName

FROM Customers

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

890 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTS

If the Customers table happens to have multiple rows that refer to the same company, you can request that the query return unique names by using the DISTINCT keyword:

SELECT DISTINCT CompanyName

FROM Customers

To select customers from a specific country, you issue the following statement:

SELECT CompanyName

FROM Customers

WHERE Country = ‘Germany’

The DBMS will retrieve and return the rows you requested. As you can see, this is not how you’d retrieve rows with Visual Basic. With a procedural language, like VB, you’d have to specify the statements to scan the entire table, examine the value of the Country column, and either select or reject the row. Then you would display the selected rows. With SQL you don’t have to specify how the selection operation will take place. You simply specify what you want the database to do for you— not how to do it.

SQL statements are categorized into two major categories, which are actually considered separate languages: the statements for manipulating the data, which form the Data Manipulation Language (DML); and the statements for defining database objects, such as tables or their indexes, which form the Data Definition Language (DDL). The DDL is not of interest to every database developer, and we will not discuss it in this book. The DML is covered in depth, because you’ll use these statements to retrieve data, insert new data to the database, and edit or delete existing data.

The statements of the DML part of the SQL language are also known as queries, and there are two types of queries: selection queries and action queries. Selection queries retrieve information from the database. The queries return a set of rows with identical structure. The columns may come from different tables, but all the rows returned by the query have the same number of columns. Action queries modify the database’s objects, or create new objects and add them to the database (new tables, relationships and so on).

Executing SQL Statements

If you are not familiar with SQL, I suggest that you follow the examples in this chapter and modify them to perform similar operations. To follow these examples, you have two options, the Query Analyzer and the Query Builder. The Query Analyzer executes SQL statements you design. The Query Builder lets you build the statements with visual tools. After a quick overview of the SQL statements, I will describe the Query Builder and show you how to use its interface to build fairly elaborate queries.

Using the Query Analyzer

One of the applications installed with SQL Server is the Query Analyzer. To start it, select Start Programs SQL Server Query Analyzer. Initially, its window will be empty. First, select the desired database’s name in the Database drop-down list and then enter the SQL statement you want to execute in the upper pane. The SQL statement will be executed against the selected database when you press Ctrl+E, or click the Run button (the button with the green arrow on the toolbar).

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

STRUCTURED QUERY LANGUAGE 891

Alternatively, you can prefix the SQL statement with the USE statement, which specifies the database against which the statement will be executed. To retrieve all the Northwind customers located in Germany, enter this statement:

USE Northwind

SELECT CompanyName FROM Customers

WHERE Country = ‘Germany’

The USE statement isn’t part of the query; it simply tells the Query Analyzer the database against which it must execute the query. I’m including the USE statement with all the queries so that you know the database used for each example. Then select the Execute command from the Query menu, or press Ctrl+E to execute the statement. The results will appear in the lower pane, as shown in Figure 20.13. For a selection query, like the previous one, you will see the rows selected and their count at the bottom of the Results pane. An action query that updates a table (adds a new row, edits, or deletes an existing row) doesn’t return any rows; it simply displays the number of rows affected.

Figure 20.13

Executing queries with the Query Analyzer

To execute another query, enter another statement in the upper pane or edit the previous statement, and press Ctrl+E again. You can also save SQL statements into files, so that you won’t have to type them again. To do so, open the File menu, select Save As or Save command, and enter the name of the file where the contents of the Query pane will be stored. The statement will be stored in a text file with the extension .sql. The lengthier examples of this chapter can be found in this chapter’s folder on the companion CD. Instead of typing the statements of the examples, you can load the corresponding SQL file from the CD and execute it.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

892 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTS

Selection Queries

We’ll start our discussion of SQL with the SELECT statement. Once you learn how to express the criteria for selecting the desired rows with the SELECT statement, you’ll be able to apply this information to other data-manipulation statements.

The simplest form of the SELECT statement is

SELECT fields

FROM tables

where fields and tables are comma-separated lists of the fields you want to retrieve from the database and the tables they belong to. To select the contact information from all the companies in the Customers table, use this statement:

USE Northwind

SELECT CompanyName, ContactName, ContactTitle

FROM Customers

To retrieve all the fields, use the asterisk (*) or the ALL keyword. The statement

SELECT * FROM Customers

will select all the fields from the Customers table.

WHERE Clause

The unconditional form of the SELECT statement we used in last few examples is quite trivial. You rarely retrieve data from all rows in a table. Usually you specify criteria, such as “All companies in Germany,” “All customers who have placed three or more orders in the last six months,” or even more complicated expressions. To restrict the rows returned by the query, use the WHERE clause of the SELECT statement. The most common form of the SELECT statement is the following:

SELECT fields

FROM tables

WHERE condition

The fields and tables arguments are the same as before. The syntax of the WHERE clause can get quite complicated, so we’ll start with the simpler forms of the selection criteria.

The condition argument can be a relational expression, like the ones you use in VB. To select all the customers from Germany, use the following condition:

WHERE Country = ‘Germany’

To select customers from multiple countries, use the OR operator to combine multiple conditions:

WHERE Country = ‘Germany’ OR

Country = ‘Austria’

You can also combine multiple conditions with the AND operator.

It is also possible to retrieve data from two or more tables with a single statement (this is the most common type of query, actually). When you combine multiple tables in a query, you can use

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

STRUCTURED QUERY LANGUAGE 893

the WHERE clause to specify how the rows of the two tables will be combined. Let’s say you want a list of all product names, along with their categories. The information you need is not contained in a single table. You must extract the product name from the Products table and the category name from the Categories table and specify that the ProductID field in the two tables must match. The statement

USE Northwind

SELECT ProductName, CategoryName

FROM Products, Categories

WHERE Products.CategoryID = Categories.CategoryID

will retrieve the names of all products, along with their category names. Here’s how this statement is executed. For each row in the Products table, the SQL engine locates the matching row in the Categories table and then appends the ProductName and CategoryName fields to the result.

If a product has no category, then it will not be included in the result. If you want all the products, even the ones that don’t belong to a category, you must use the JOIN clause, which is described later in this chapter. Using the WHERE clause to combine rows from multiple tables may lead to unexpected results, because it can only combine rows with matching fields. If the foreign key in the Products table is Null, this product won’t be selected. This is a fine point in combining multiple tables, and many programmers abuse the WHERE clause. As a result, they retrieve fewer rows from the database, and they don’t even know it. See the section “SQL Joins” later in this chapter for more information.

Note When fields in two different tables have the same names, you must prefix them with the table’s name to remove the ambiguity. Also, some field names may contain spaces. These field names must appear in square brackets. The Publishers table of the Pubs sample database contains a field named Publisher Name. To use this field in a query, enclose it in brackets: Publishers.[Publisher Name]. The table prefix is optional (no other table contains a column by that name), but the brackets are mandatory.

You can also combine multiple restrictions with logical operators. To retrieve all the titles published by a specific publisher, use a statement like the following:

USE PUBS

SELECT titles.title FROM titles, publishers

WHERE titles.pub_id = publishers.pub_id AND publishers.pub_name = ‘New Moon Books’

This statement combines two tables and selects the titles of a publisher specified by name. To match titles and publisher, it requests that

1.The publisher’s name in the Publishers table is New Moon Books, and

2.The pub_id field in the Titles table matches the pub_id field in the Publishers table.

Notice that we did not specify the publisher’s name (field pub_name) in the SELECT list; all the desired books have the same publisher, so we need not include the publisher’s names in the

result set.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

894 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTS

Knowing WHERE You’re Going

If you specify multiple tables without the WHERE clause, the SQL statement, will return an enormous cursor. If you issue the following statement,

SELECT ProductName, CategoryName FROM Categories, Products

you will not get a line for each product name followed by its category. You will get a cursor with 616 rows, which are all possible combinations of product names and category names. In this example, the Categories table has eight rows and the Products table has 77 rows, so their cross-product contains 616 rows.

AS Keyword

By default, each column of a query is labeled after the actual field name in the output. If a table contains two fields named CustLName and CustFName, you can display them with different labels using the AS keyword. The SELECT statement

SELECT CustLName, CustFName

will produce two columns labeled CustLName and CustFName. The query’s output will look much better if you change the labels of these two columns with a statement like the following one:

SELECT CustLName AS [Last Name],

CustFName AS [First Name]

It is also possible to concatenate two fields in the SELECT list with the concatenation operator. Concatenated fields are not labeled automatically, so you must supply your own header for the combined field. The following statement creates a single column for the customer’s name and labels it

Customer Name:

SELECT CustFName + ‘, ‘ + CustLName AS [Customer Name]

TOP Keyword

Some queries may retrieve a large number of rows, while you’re interested in the top few rows only. The TOP N keyword allows you to select the first N rows and ignore the remaining ones. Let’s say you want to see the list of the 10 most wanted products. Without the TOP keyword, you’d have to calculate how many items from each product have been sold, sort them according to items sold, and examine the first 10 rows returned by the query.

The TOP keyword is used only when the rows are ordered according to some meaningful criteria. Limiting a query’s output to the alphabetically top N rows isn’t very practical. When the rows are sorted according to items sold, revenue generated, and so on, it makes sense to limit the query’s output to N rows. You’ll see many examples of the TOP keyword later in this chapter, after you learn how to order a query’s rows.

DISTINCT Keyword

The DISTINCT keyword eliminates any duplicates from the cursor retrieved by the SELECT statement. Let’s say you want a list of all countries with at least one customer. If you retrieve all country

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

STRUCTURED QUERY LANGUAGE 895

names from the Customers table, you’ll end up with many duplicates. To eliminate them, use the DISTINCT keyword, as shown in the following statement:

USE NORTHWIND

SELECT DISTINCT Country

FROM Customers

LIKE Operator

The LIKE operator uses pattern-matching characters, like the ones you use to select multiple files in DOS. The LIKE operator recognizes several pattern-matching characters (or wildcard characters) to match one or more characters, numeric digits, ranges of letters, and so on; these are listed in Table 20.1.

Table 20.1: SQL Wildcard Characters

Wildcard Character	Description
%	Matches any number of characters. The pattern program% will find program,
	programming, programmer, and so on. The pattern %program% will locate
	strings that contain the words program, programming, nonprogrammer, and
	so on.
_	(Underscore character) Matches any single alphabetic character. The pattern
	b_y will find boy and bay, but not boysenberry.
[ ]	Matches any single character within the brackets. The pattern Santa [YI]nez
	will find both Santa Ynez and Santa Inez.
[^ ]	Matches any character not in the brackets. The pattern %q[^u]% will find words
	that contain the character q not followed by u (they are misspelled words).
[ - ]	Matches any one of a range of characters. The characters must be consecutive in
	the alphabet and specified in ascending order (A to Z, not Z to A). The pattern
	[a-c]% will find all words that begin with a, b, or c (in lowercase or uppercase).
#	Matches any single numeric character. The pattern D1## will find D100 and
	D139, but not D1000 or D10.

You can use the LIKE operator to retrieve all titles about Windows from the Pubs database, with a statement like the following one:

USE PUBS

SELECT titles.title

FROM titles

WHERE titles.title LIKE ‘%WINDOWS%’

The percent signs mean that any character(s) may appear in front of or after the word Windows in the title.

To include a wildcard character itself in your search argument, enclose it in square brackets. The pattern %50[%]% will match any field that contains the string “50%”.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com