Chapter: Internet & World Wide Web HOW TO PROGRAM - Rich Internet Application Server Technologies - Database:SQL, MySQL, ADO.NET 2.0 and Java DB

| Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail |

SQL

Let us consider several SQL queries that extract information from database books.

SQL

 

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

 

The next several subsections discuss most of the SQL keywords listed in Fig. 22.10 in the context of SQL queries and statements. Other SQL keywords are beyond this text’s scope. To learn other keywords, refer to the SQL reference guide supplied by the vendor of the RDBMS you are using. [Note: For more information on SQL, refer to the web resources in Section 22.12.]


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 : Combine 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.

Fig. 22.10 | SQL query keywords.

 

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 rows and 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. 22.11.

 



2. WHERE Clause

 

In most cases, it is necessary to retrieve rows in a database that satisfy certain selection cri-teria. 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 year is greater than 2005, use the query

 

SELECT title, editionNumber, copyright

 

FROM titles

 

WHERE copyright > '2005'

 

Figure 22.12 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.

 

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 next 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%'

 

This query selects the two rows shown in Fig. 22.13—two of the four authors 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. Note that the pattern string is surrounded by single-quote characters.

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 o, fol-lowed by any number of additional characters (specified by %):

 

SELECT authorID, firstName, lastName

 

FROM authors

 

WHERE lastName LIKE '_o%'

 

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


 

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 exam-ple, to obtain the list of authors in ascending order by last name (Fig. 22.15), use the query

 

SELECT authorID, firstName, lastName

 

FROM authors

 

ORDER BY lastName ASC

 

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. 22.16), use the query



 

SELECT authorID, firstName, lastName FROM authors

 

ORDER BY lastName DESC

 

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 value in the lastName column, they are returned sorted by firstName (Fig. 22.17).

The WHERE and ORDER BY clauses can be combined in one query, as in

 

SELECT isbn, title, editionNumber, copyright

 

FROM titles

 

WHERE title LIKE '%How to Program'

 

ORDER BY title ASC

 

which returns the isbn, title, editionNumber and copyright of each book in the titles

 

table that has a title ending with "How to Program" and sorts them in ascending order by title. The query results are shown in Fig. 22.18.



 

4. Combining 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. Then the database would store duplicate author information for authors who wrote multiple books. Often, it is necessary to combine data from multiple tables into a single result. Re-ferred to as joining the tables, this is specified by an INNER JOIN operator in the query. An INNER JOIN combines rows from two tables by matching values in columns that are com-mon 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 a condition (often comparing columns from each table) that determines which rows are combined. 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 combines 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. Only rows in which the authorIDs match are combined. 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. As always, the query can contain an ORDER BY clause. Figure 22.19 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.]


 

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 IN-SERT 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 (e.g., if columnName1 is supposed to be the firstName column, then value1 should be a string in single quotes representing the first name).

 

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, the database 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 22.20 shows the authors table after the INSERT operation. [Note: Not every database manage-ment system supports autoincremented columns. Check the documentation for your DBMS for alternatives to autoincremented columns.]


 

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 value can be an expression that yields a 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 can be simplified as follows:

 

WHERE authorID = 5

 

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



7. DELETE Statement

 

A SQL DELETE statement removes rows from a table. The basic form of a DELETE 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. If this clause is omitted, all the table’s rows are deleted. The DELETE statement

 

DELETE FROM authors

 

WHERE lastName = 'Jones' AND firstName = 'Sue'

 

deletes the row (or rows) 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 22.22 shows the authors table after the DELETE operation has taken place.






Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail


Copyright © 2018-2020 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.