SQL

Now that you have reviewed all the basic components of the database, you can review the basics of SQL, which you might use specifically in the searchSeat() method. SQL enables you to access a database. All major database vendors use SQL because it is an American National Standards Institute (ANSI; http://www.ansi.org) standard language. SQL does not appear on the SCJD assignment or essay exam because of the practical matter of distributing an assignment in a way that includes client/server architecture and includes a database without relying on a non-Sun vendor. At this time, it is too difficult for Sun certification people to figure out a test design that would be fair yet allow the developer to use various databases, such as MySQL, Sybase, Oracle, DB2, or SQL Server. If you look at it from Sun's perspective, you can already hear someone complain about failing the assignment because Sun is biased against Microsoft SQL Server. So, in the current version, Sun wrote its own database engine that everyone must use. This part is understandable, but the unwanted side effect is the loss of SQL. Sun's database packaged with the assignment is too rudimentary to use SQL, but you could choose to add SQL processing capability. You don't have to use much SQL; just use a small portion and argue that it's a smart way to manage interaction between the client and database.

Most popular databases today are based on the relational model, defined in 1970 by Dr. E. F. Codd, a researcher for IBM, who wrote the paper "A Relational Model of Data for Large Shared Data Banks." This paper started a chain of events that gave rise to the RDBMS. In 1974 IBM began the System/R project, which produced Structured English Query Language (SEQUEL). Eventually, in 1983 IBM developed a product based on SQL called DB2. Other vendors did likewise and announced SQL-based products, including Oracle and Sybase.

SQL is quite old and could use some polishing. For example, the statement SELECT * FROM employee; would be understood better as get * from employee . One of SQL's weaknesses is the lack of programming logic, such as loops and objects. Although SQL has a few wrinkles , however, it is the standard way to get information from a database. Naturally, vendors vie for a competitive advantage by adding features to SQL, such as increasingly complex ways to query the database. Of course, these added features created confusion for the poor developers trying to build database applications. However, the government became useful for a change and formed a committee to create a standard. ANSI standardized SQL in 1986 (X3.135) and the International Standards Organization (ISO) standardized it in 1987. In 1989, this standard was revised into what has been commonly known as SQL89 or SQL1. The ANSI organization upgraded it in 1992 to SQL92.

The current standard, also supported by the International Electromechanical Commission (IEC), is SQL99 (ANSI/ISO/IEC 9075-2-1999; also called SQL3). The basic SQL language that conforms to earlier standards (ISO/IEC 9075:1989) also conforms to the latest standard. The technical changes between previous standards and the current SQL99 include improved diagnostic capabilities, support for additional data types and character sets, and additional operations, especially object handling.

Using SQL, you can take the following main types of actions:

• select statement: single row, multiple rows

• insert statement

• delete statement

• update statement

• create, drop, alter table

• create temporary table

• create, drop schema

• create, drop domain

• create, drop view

• commit, rollback statement

• connect, disconnect statement

• grant, revoke statement

• get diagnostics statement

Be careful with data types. Java (actually all languages) has its own data types, which are not equivalent to SQL data types. They might even have the same name , but not the same purpose. You must map Java-to-SQL data types carefully , making sure the two corresponding data types are handled accurately in your code.

Different databases have different data types, and they often vary in representations for values such as dates and Booleans. Java data types also differ from other languages and databases. To work with a database, you must correctly map between the Java and database-specific data types. Table 7.1 shows the mappings between Java, ANSI-SQL, and a few of the popular databases.

Table 7.1. Data Type Mapping Between Java, ANSI-SQL, and Popular Databases

Java	ANSI- SQL	MySQL	DB2	MS SQL	Oracle
Boolean	BIT	TINYINT	SMALLINT	BIT	NUMBER
Short	SMALLINT	SMALLINT	SMALLINT	SMALLINT	SMALLINT
Int	INTEGER	INTEGER	INTEGER	INT	INTEGER
Float	REAL	DOUBLE	FLOAT	FLOAT	FLOAT
Double	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE
BigInteger	BIGINT	BIGINT	DECIMAL	INT	NUMBER
String	VARCHAR	VARCHAR	VARCHAR	VARCHAR	VARCHAR
Date	TIMESTAMP	DATETIME	DATE	DATETIME	DATE

In Java, if you use Java Database Connectivity (JDBC), you have to map Java data types to only the data types used in JDBC. Why they are different is anyone 's guess, but that is the state of affairs. The good news is that driver vendors map data types from JDBC to those used in vendors' databases. So you need to worry about only JDBC, which is the point of this library inspired by the Adapter design pattern.

The following sections demonstrate a few examples of how to use SQL. SQL has been around longer than Java, so it is stable, but the syntax becomes awkward when constructing complicated queries. Some vendors confuse the matter by extending SQL to include their own language constructs. For example, Oracle has made SQL more powerful by adding the capability to write a whole program with PL/SQL, which is a hybrid; however, portability has been lost. The good news is that if you use the basic syntax described in this chapter, your statements will work with most ANSI-compliant versions of SQL. However, if you venture from the basics, alas, portability will suffer.

Table 7.2 lists vendors and their customized languages and shows their proprietary extensions in addition to the functionality in ANSI-SQL.

Table 7.2. Vendor-Specific SQL Implementations

Vendor	SQL Implementation
JDBC	ANSI-SQL
MySQL	MySQL
DB2	CLI
Microsoft SQL	Transact-SQL
Oracle	PL/SQL

Table 7.2 specifies implementations that vendors offer with their products. ANSI-SQL works in all of them, but each vendor adds extensions or provides additional functionality beyond ANSI-SQL. For example, IBM's DB2 Call Level Interface (CLI) is an API that adds functions to application programs to process SQL statements. CLI adds a lot of functionality to the database beyond ANSI-SQL. Part of the competition among vendors to sell more database licenses is to add more functionality than the competitor does. The side effect is vendor-specific SQL that is not portable.

Every vendor implements the basic ANSI-SQL command set, including SELECT, UPDATE, INSERT , and DELETE statements. Vendors diverge when you want to do interesting things, such as leverage built-in functions and fancy stored procedures.

The SELECT Statement

The primary SQL statement is the SELECT statement. Think of it as a print statement. Although it's also used to assign values to variables , it's more often used to print out values returned from querying a database. The following is the simplified syntax:

 SELECT column_names FROM table_references WHERE filter_criteria; 

Here is an example of using SELECT :

 SELECT firstName FROM customer Where id=86; 

In Microsoft SQL Server, say you have the following query on the pubs sample database:

 SELECT 'Total income is', price * ytd_sales AS Revenue, 'for', title_id AS Book# FROM titles ORDER BY Book# ASC; 

This query generates the following results:

 Revenue                    Book# --------------- --------------------- ---- ------ Total income is 81859.0500            for  BU1032 Total income is 46318.2000            for  BU1111 Total income is 55978.7800            for  BU2075 

Likewise, say you have the following query on the pubs sample database:

 SELECT ytd_sales AS Sales,    authors.au_fname + ' '+ authors.au_lname AS Author,    ToAuthor = (ytd_sales * royalty) / 100,    ToPublisher = ytd_sales - (ytd_sales * royalty) / 100 FROM titles INNER JOIN titleauthor    ON titles.title_id = titleauthor.title_id INNER JOIN authors    ON titleauthor.au_id = authors.au_id ORDER BY Sales DESC, Author ASC; 

This query generates the following results:

 Sales       Author                    ToAuthor    ToPublisher ----------- ------------------------- ----------- ----------- 22246       Anne Ringer               5339        16907 22246       Michel DeFrance           5339        16907 18722       Marjorie Green            4493        14229 

In MySQL, the following query is valid:

 SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name; 

And so is this one:

 SELECT * FROM table LIMIT 5,10; 

All vendors provide a tool for accessing their databases. One that is especially powerful, but ugly, is Oracle's SQL*Plus. It understands much more than vanilla SQL, including the keywords ACCEPT (get input from the user ), DEFINE or DEF (declare a variable), and DESCRIBE or DESC (list the attributes of tables and other objects). In Oracle's SQL*Plus, the following query is valid:

 SELECT c.course_name, c.period, e.student_name FROM course c FULL OUTER JOIN enrollment e ON c.course_name = e.course_name; 

This query generates the following results:

 COURSE_NAME         PERIOD STUDENT_NAME --------------- ---------- --------------- English II               4 Michael Spanish I                1 Billy Spanish I                1 Jeff 

The WHERE Clause

The WHERE clause specifies a search condition that filters the returned results. This keyword is how you sift through table data, as shown in this example:

 SELECT Name, Phone, Fax FROM Contacts WHERE Name LIKE 'Pat%'; 

This query returns all rows with a Name field beginning with Pat, so rows in which the Name field is Patricia, Patrick, and Pater are returned. You can combine many conditions in your filter. You can use the following operators to create filters. Note that they are SQL99 compliant and supported by most, but not all, vendors. (Many vendors support all these and many more proprietary operators and keywords, such as !< .)

The difference between = and LIKE is important. = is used to make an exact match, but LIKE allows wildcards, so it's used for approximate matches.

• NOT ” Negates the Boolean result from the given expression.

• AND ” Returns the row if both conditions are TRUE.

• OR ” Returns the row if either condition is TRUE.

• = ” Tests equality between two expressions.

• <> ” Tests the condition of two expressions not being equal to each other.

• != ” Same as <> .

• > ” Tests whether the expression on the left is greater than the expression on the right.

• < ” Tests whether the expression on the right is greater than the expression on the left.

• >= ” Tests whether the expression on the left is greater than or equal to the expression on the right.

• <= ” Tests whether the expression on the right is greater than or equal to the expression on the left.

• between ” Searches for a value between an inclusive range.

• LIKE ” Searches for a pattern.

The ORDER BY Clause

The ORDER BY clause is used to sort -order results, as in this example:

 SELECT employeeName, employeeID FROM employees ORDER BY employeeID, employeeName DESC; 

This query returns all rows in the employees table, and the rows will be ordered by employeeID . In this example, the rows returned from the query are sorted by employeeID first; if there is more than one row for a given employeeID , this subset is then sorted by employeeName in descending order (because the DESC keyword is used). You can also sort in ascending order by using the ASC keyword.

The INSERT INTO Statement

The INSERT INTO statement is used to insert new rows into a table, as shown in this example:

 INSERT INTO customer (firstName, LastName, ID) VALUES ('Kasienne', 'Lauder', 8350); 

This query adds values into the table; value1 goes into column1, value2 into column2, and so forth.

The UPDATE Statement

The UPDATE statement is used to modify data in a table, as shown here:

 UPDATE product SET price = 24.81 WHERE productID = 7; 

This query changes the value in the price column to the new value ( 24.81 ) for all rows that meet the WHERE condition, which is the productID column value equals the specified value of 7 .

The DELETE Statement

The DELETE statement is used to delete rows in a table, as shown in this example:

 DELETE FROM customer WHERE customerid = 35; 

This query removes all rows, and the data in them, that meet the WHERE condition, which is the column ( id ) equals the specified value ( 35 ). If more than one row meets this criteria, all rows are deleted. If the column is a primary key where the keys are unique, a matching record is found, and only one row is removed.