SQL

We now provide an overview of SQL in the context of our books sample database. You will be able to use the SQL discussed here in the examples later in the chapter.

The SQL keywords listed in Fig. 25.12 are discussed in the context of complete SQL queries and statements in the next several subsections. Other SQL keywords are beyond the scope of this text. To learn other keywords, you could refer to the SQL reference guide supplied by the vendor of the RDBMS you are using. [Note: For more information on SQL, please refer to the Internet and Web resources in Section 25.12 and the Recommended Readings at the end of this chapter.]

Figure 25.12. SQL query keywords.

SQL keyword

Description

SELECT

Retrieves data from one or more tables.

FROM

Tables involved in the query. Required in every SELECT.

WHERE

Criteria for selection that determine the rows to be retrieved, deleted or updated. Optional in a SQL query or a SQL statement.

GROUP BY

Criteria for grouping rows. Optional in a SELECT query.

ORDER BY

Criteria for ordering rows. Optional in a SELECT query.

INNER JOIN

Merge rows from multiple tables.

INSERT

Insert rows into a specified table.

UPDATE

Update rows in a specified table.

DELETE

Delete rows from a specified table.

 

25.4.1. Basic SELECT Query

Let us consider several SQL queries that extract information from database books. A SQL query "selects" rows and columns from one or more tables in a database. Such selections are performed by queries with the SELECT keyword. The basic form of a SELECT query is


   SELECT * FROM tableName

in which the asterisk (*) indicates that all columns from the tableName table should be retrieved. For example, to retrieve all the data in the authors table, use

 SELECT * FROM authors

Most programs do not require all the data in a table. To retrieve only specific columns from a table, replace the asterisk (*) with a comma-separated list of the column names. For example, to retrieve only the columns authorID and lastName for all rows in the authors table, use the query

 SELECT authorID, lastName FROM authors

This query returns the data listed in Fig. 25.13.

Figure 25.13. Sample authorID and lastName data from the authors table.

(This item is displayed on page 1198 in the print version)

authorID

lastName

1

Deitel

2

Deitel

3

Nieto

4

Santry

Software Engineering Observation 25.2

For most queries, the asterisk (*) should not be used to specify column names. In general, programmers process results by knowing in advance the order of the columns in the resultfor example selecting authorID and lastName from table authors ensures that the columns will appear in the result with authorID as the first column and lastName as the second column. Programs typically process result columns by specifying the column number in the result (starting from number 1 for the first column). Selecting columns by name also avoids returning unneeded columns and protects against changes in the actual order of the columns in the table(s).

Common Programming Error 25.4

If a programmer assumes that the columns are always returned in the same order from a query that uses the asterisk (*), the program may process the result incorrectly. If the column order in the table(s) changes or if additional columns are added at a later time, the order of the columns in the result would change accordingly.

 

25.4.2. WHERE Clause

In most cases, it is necessary to locate rows in a database that satisfy certain selection criteria. Only rows that satisfy the selection criteria (formally called predicates) are selected. SQL uses the optional WHERE clause in a query to specify the selection criteria for the query. The basic form of a query with selection criteria is


   SELECT columnName1, columnName2, ... FROM tableName WHERE criteria

For example, to select the title, editionNumber and copyright columns from table titles for which the copyright date is greater than 2002, use the query

 SELECT title, editionNumber, copyright
 FROM titles
 WHERE copyright > 2002

Figure 25.14 shows the result of the preceding query. The WHERE clause criteria can contain the operators <, >, <=, >=, =, <> and LIKE. Operator LIKE is used for pattern matching with wildcard characters percent (%) and underscore (_). Pattern matching allows SQL to search for strings that match a given pattern.

Figure 25.14. Sampling of titles with copyrights after 2002 from table titles.

title

editionNumber

copyright

The Complete C++ Training Course

4

2003

Java How to Program

5

2003

C How to Program

4

2004

Internet and World Wide Web How to Program

3

2004

Java How to Program

6

2005

C# How to Program

1

2003

A pattern that contains a percent character (%) searches for strings that have zero or more characters at the percent character's position in the pattern. For example, the following query locates the rows of all the authors whose last name starts with the letter D:

 SELECT authorID, firstName, lastName
 FROM authors
 WHERE lastName LIKE 'D%'

The preceding query selects the two rows shown in Fig. 25.15, because two of the four authors in our database have a last name starting with the letter D (followed by zero or more characters). The % in the WHERE clause's LIKE pattern indicates that any number of characters can appear after the letter D in the lastName column. Note that the pattern string is surrounded by single-quote characters.

Figure 25.15. Authors whose last name starts with D from the authors table.

authorID

firstName

lastName

1

Harvey

Deitel

2

Paul

Deitel

Portability Tip 25.1

See the documentation for your database system to determine whether SQL is case sensitive on your system and to determine the syntax for SQL keywords (i.e., should they be all uppercase letters, all lowercase letters or some combination of the two?).

Portability Tip 25.2

Read your database system's documentation carefully to determine whether your system supports the LIKE operator.

Portability Tip 25.3

Some databases use the * character in place of the % character in a pattern.

An underscore (_) in the pattern string indicates a single wildcard character at that position in the pattern. For example, the following query locates the rows of all the authors whose last names start with any character (specified by _), followed by the letter i, followed by any number of additional characters (specified by %):

 SELECT authorID, firstName, lastName
 FROM authors
 WHERE lastName LIKE '_i%'

The preceding query produces the row shown in Fig. 25.16, because only one author in our database has a last name that contains the letter i as its second letter.

Figure 25.16. The only author from the authors table whose last name contains i as the second letter.

(This item is displayed on page 1200 in the print version)

authorID

firstName

lastName

3

Tem

Nieto

Portability Tip 25.4

Some database systems use the ? character in place of the _ character in a pattern.

25.4.3. ORDER BY Clause

The rows in the result of a query can be sorted into ascending or descending order by using the optional ORDER BY clause. The basic form of a query with an ORDER BY clause is


   SELECT columnName1, columnName2, ... FROM tableName ORDER BY column ASC
   SELECT columnName1, columnName2, ... FROM tableName ORDER BY column DESC

where ASC specifies ascending order (lowest to highest), DESC specifies descending order (highest to lowest) and column specifies the column on which the sort is based. For example, to obtain the list of authors in ascending order by last name (Fig. 25.17), use the query

 SELECT authorID, firstName, lastName
 FROM authors
 ORDER BY lastName ASC

Figure 25.17. Sample data from table authors in ascending order by lastName.

authorID

firstName

lastName

1

Harvey

Deitel

2

Paul

Deitel

3

Tem

Nieto

4

Sean

Santry

Note that the default sorting order is ascending, so ASC is optional. To obtain the same list of authors in descending order by last name (Fig. 25.18), use the query

 SELECT authorID, firstName, lastName
 FROM authors
 ORDER BY lastName DESC

Figure 25.18. Sample data from table authors in descending order by lastName.

authorID

firstName

lastName

4

Sean

Santry

3

Tem

Nieto

1

Harvey

Deitel

2

Paul

Deitel

Multiple columns can be used for sorting with an ORDER BY clause of the form


  ORDER BY column1 sortingOrder, column2 sortingOrder, ...

where sortingOrder is either ASC or DESC. Note that the sortingOrder does not have to be identical for each column. The query

 SELECT authorID, firstName, lastName
 FROM authors
 ORDER BY lastName, firstName

sorts all the rows in ascending order by last name, then by first name. If any rows have the same last name value, they are returned sorted by first name (Fig. 25.19).

Figure 25.19. Sample data from authors in ascending order by lastName and firstName.

authorID

firstName

lastName

1

Harvey

Deitel

2

Paul

Deitel

3

Tem

Nieto

4

Sean

Santry

The WHERE and ORDER BY clauses can be combined in one query. For example, the query

 SELECT isbn, title, editionNumber, copyright, price
 FROM titles
 WHERE title LIKE '%How to Program'
 ORDER BY title ASC

returns the isbn, title, editionNumber, copyright and price of each book in the titles table that has a title ending with "How to Program" and sorts them in ascending order by title. A portion of the query results are shown in Fig. 25.20.

Figure 25.20. Sampling of books from table titles whose titles end with How to Program in ascending order by title.

(This item is displayed on page 1202 in the print version)

isbn

title

editionNumber

copyright

price

0130895601

Advanced Java 2 Platform How to Program

1

2002

69.95

0131426443

C How to Program

4

2004

85.00

0130384747

C++ How to Program

4

2003

85.00

013028419x

e-Business and e-Commerce How to Program

1

2001

69.95

0131450913

Internet and World Wide Web How to Program

3

2004

85.00

0130284181

Perl How to Program

1

2001

69.95

0134569555

Visual Basic 6 How to Program

1

1999

69.95

0130284173

XML How to Program

1

2001

69.95

 

25.4.4. Merging Data from Multiple Tables: INNER JOIN

Database designers often split related data into separate tables to ensure that a database does not store data redundantly. For example, the books database has tables authors and titles. We use an authorISBN table to store the relationship data between authors and their corresponding titles. If we did not separate this information into individual tables, we would need to include author information with each entry in the titles table. This would result in the database storing duplicate author information for authors who wrote multiple books. Often, it is necessary to merge data from multiple tables into a single result. Referred to as joining the tables, this is specified by an INNER JOIN operator in the query. An INNER JOIN merges rows from two tables by matching values in columns that are common to the tables. The basic form of an INNER JOIN is:


   SELECT columnName1, columnName2,...
   FROM table1
   INNER JOIN table2
      ON table1. columnName = table2. columnName

The ON clause of the INNER JOIN specifies the columns from each table that are compared to determine which rows are merged. For example, the following query produces a list of authors accompanied by the ISBNs for books written by each author:

 SELECT firstName, lastName, isbn
 FROM authors
 INNER JOIN authorISBN
 ON authors.authorID = authorISBN.authorID
 ORDER BY lastName, firstName

The query merges data from the firstName and lastName columns from table authors with the isbn column from table authorISBN, sorting the result in ascending order by lastName and firstName. Note the use of the syntax tableName. columnName in the ON clause. This syntax (called a qualified name) specifies the columns from each table that should be compared to join the tables. The "tableName." syntax is required if the columns have the same name in both tables. The same syntax can be used in any query to distinguish columns in different tables that have the same name. In some systems, table names qualified with the database name can be used to perform cross-database queries.

Software Engineering Observation 25.3

If a SQL statement includes columns from multiple tables that have the same name, the statement must precede those column names with their table names and a dot (e.g., authors.authorID).

Common Programming Error 25.5

In a query, failure to qualify names for columns that have the same name in two or more tables is an error.

As always, the query can contain an ORDER BY clause. Figure 25.21 depicts a portion of the results of the preceding query, ordered by lastName and firstName. [Note: To save space, we split the result of the query into two columns, each containing the firstName, lastName and isbn columns.]

Figure 25.21. Sampling of authors and ISBNs for the books they have written in ascending order by lastName and firstName.

firstName

lastName

isbn

firstName

lastName

isbn

Harvey

Deitel

0130895601

Paul

Deitel

0130895717

Harvey

Deitel

0130284181

Paul

Deitel

0132261197

Harvey

Deitel

0134569555

Paul

Deitel

0130895725

Harvey

Deitel

0139163050

Paul

Deitel

0130829293

Harvey

Deitel

0135289106

Paul

Deitel

0134569555

Harvey

Deitel

0130895717

Paul

Deitel

0130829277

Harvey

Deitel

0130284173

Tem

Nieto

0130161438

Harvey

Deitel

0130829293

Tem

Nieto

013028419x

Paul

Deitel

0130852473

Sean

Santry

0130895601

 

25.4.5. INSERT Statement

The INSERT statement inserts a row into a table. The basic form of this statement is


   INSERT INTO tableName ( columnName1, columnName2, ..., columnNameN )
      VALUES ( value1, value2, ..., valueN )

where tableName is the table in which to insert the row. The tableName is followed by a comma-separated list of column names in parentheses (this list is not required if the INSERT operation specifies a value for every column of the table in the correct order). The list of column names is followed by the SQL keyword VALUES and a comma-separated list of values in parentheses. The values specified here must match the columns specified after the table name in both order and type (se.g., if columnName1 is supposed to be the firstName column, then value1 should be a string in single quotes representing the first name). Always explicitly list the columns when inserting rows. If the order of the columns changes in the table, using only VALUES may cause an error. The INSERT statement

 INSERT INTO authors (firstName, lastName)
 VALUES ( 'Sue', 'Smith' )

inserts a row into the authors table. The statement indicates that values are provided for the firstName and lastName columns. The corresponding values are 'Sue' and 'Smith'. We do not specify an authorID in this example because authorID is an autoincremented column in the authors table. For every row added to this table, MySQL assigns a unique authorID value that is the next value in the autoincremented sequence (i.e., 1, 2, 3 and so on). In this case, Sue Smith would be assigned authorID number 5. Figure 25.22 shows the authors table after the INSERT operation. [Note: Not every database management system supports autoincremented columns. Check the documentation for your DBMS for alternatives to autoincremented columns.]

Figure 25.22. Sample data from table Authors after an INSERT operation.

authorID

firstName

lastName

1

Harvey

Deitel

2

Paul

Deitel

3

Tem

Nieto

4

Sean

Santry

5

Sue

Smith

Common Programming Error 25.6

It is an error to specify a value for an autoincrement column.

Common Programming Error 25.7

SQL uses the single-quote (') character as a delimiter for strings. To specify a string containing a single quote (e.g., O'Malley) in a SQL statement, the string must have two single quotes in the position where the single-quote character appears in the string (e.g., 'O''Malley'). The first of the two single-quote characters acts as an escape character for the second. Not escaping singlequote characters in a string that is part of a SQL statement is a SQL syntax error.

 

25.4.6. UPDATE Statement

An UPDATE statement modifies data in a table. The basic form of the UPDATE statement is


   UPDATE tableName
      SET columnName1 = value1, columnName2 = value2, ..., columnNameN = valueN
      WHERE criteria

where tableName is the table to update. The tableName is followed by keyword SET and a comma-separated list of column name-value pairs in the format columnName = value. The optional WHERE clause provides criteria that determine which rows to update. Though not required, the WHERE clause is typically used, unless a change is to be made to every row. The UPDATE statement

 UPDATE authors
 SET lastName = 'Jones'
 WHERE lastName = 'Smith' AND firstName = 'Sue'

updates a row in the authors table. The statement indicates that lastName will be assigned the value Jones for the row in which lastName is equal to Smith and firstName is equal to Sue. [Note: If there are multiple rows with the first name "Sue" and the last name "Smith," this statement will modify all such rows to have the last name "Jones."] If we know the authorID in advance of the UPDATE operation (possibly because we searched for it previously), the WHERE clause could be simplified as follows:

 WHERE AuthorID = 5

Figure 25.23 shows the authors table after the UPDATE operation has taken place.

Figure 25.23. Sample data from table authors after an UPDATE operation.

authorID

firstName

lastName

1

Harvey

Deitel

2

Paul

Deitel

3

Tem

Nieto

4

Sean

Santry

5

Sue

Jones

 

25.4.7. DELETE Statement

A SQL DELETE statement removes rows from a table. The basic form of a DELETE statement is


   DELETE FROM tableName WHERE criteria

where tableName is the table from which to delete. The optional WHERE clause specifies the criteria used to determine which rows to delete. The DELETE statement

 DELETE FROM authors
 WHERE lastName = 'Jones' AND firstName = 'Sue'

deletes the row for Sue Jones in the authors table. If we know the authorID in advance of the DELETE operation, the WHERE clause can be simplified as follows:

 WHERE authorID = 5

Figure 25.24 shows the authors table after the DELETE operation has taken place.

Figure 25.24. Sample data from table authors after a DELETE operation.

authorID

firstName

lastName

1

Harvey

Deitel

2

Paul

Deitel

3

Tem

Nieto

4

Sean

Santry


Introduction to Computers, the Internet and the World Wide Web

Introduction to Java Applications

Introduction to Classes and Objects

Control Statements: Part I

Control Statements: Part 2

Methods: A Deeper Look

Arrays

Classes and Objects: A Deeper Look

Object-Oriented Programming: Inheritance

Object-Oriented Programming: Polymorphism

GUI Components: Part 1

Graphics and Java 2D™

Exception Handling

Files and Streams

Recursion

Searching and Sorting

Data Structures

Generics

Collections

Introduction to Java Applets

Multimedia: Applets and Applications

GUI Components: Part 2

Multithreading

Networking

Accessing Databases with JDBC

Servlets

JavaServer Pages (JSP)

Formatted Output

Strings, Characters and Regular Expressions

Appendix A. Operator Precedence Chart

Appendix B. ASCII Character Set

Appendix C. Keywords and Reserved Words

Appendix D. Primitive Types

Appendix E. (On CD) Number Systems

Appendix F. (On CD) Unicode®

Appendix G. Using the Java API Documentation

Appendix H. (On CD) Creating Documentation with javadoc

Appendix I. (On CD) Bit Manipulation

Appendix J. (On CD) ATM Case Study Code

Appendix K. (On CD) Labeled break and continue Statements

Appendix L. (On CD) UML 2: Additional Diagram Types

Appendix M. (On CD) Design Patterns

Appendix N. Using the Debugger

Inside Back Cover



Java(c) How to Program
Java How to Program (6th Edition) (How to Program (Deitel))
ISBN: 0131483986
EAN: 2147483647
Year: 2003
Pages: 615

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net