SQL is an industry-wide, standard set of relational commands that allows users to both define and manipulate data in a database. SQL has been adopted as the common interface for relational data definition and data manipulation functions on all major relational DBMS (Database Management Systems).
This chapter will introduce the reader to SQL and show how SQL commands can be used within a Java application.
After finishing this chapter, you will understand:
- The most commonly used SQL commands and their syntax
- The major SQL components
After finishing this chapter, you will be able to:
- Create schemas, tables, views, and indexes
- Manipulate and retrieve data using SQL
SQL and Relational Database Concepts
SQL uses schemas (also referred to as collections) and tables to store data. You can think of a schema as a folder. (In actuality, a schema is also comprised of journals, a catalog, and cross reference tables. However, these are things a beginning Java programmer does not have to worry about.) Database analysts use schemas to group tables that hold related data. The schema and its tables correspond to what we have been referring to as a database. SQL statements can be used in application programs to both create these SQL constructs and access and manipulate the data stored within. Using SQL within a program provides a big advantage because SQL statements reference the universally accepted SQL objects (e.g., schemas and tables) regardless of what the individual DBMSs call these objects. Because of this, the Java developers did not include any data manipulation keywords in the Java language. Instead they supplied Java classes (such as the Statement and ResultSet classes) to facilitate the use of SQL.
A table is a two-dimensional array of data in rows and columns. Rows are often referred to as records and columns as fields. Individual pieces of data are stored in a single column of a single row. If you are familiar with spreadsheets, this is comparable to a spreadsheet cell.
Within Java applications, SQL statements are passed to a Statement object for execution. For the purposes of initially learning SQL, all examples will only show the SQL commands and required syntax. The Database Access chapter will show how to integrate the commands into a Java class.
Creating
SQL commands are simple, but using them can be complex. For instance, to create a schema, use the keywords create schema followed by the name of the new schema. For example:
CREATE SCHEMA tntdb
Yep, that’s all there is to it. SQL is not case sensitive (yay!) and very forgiving of extra spaces. For instance, specifying the command as follows is also correct:
CREATE SCHEMA tntdb
However, SQL is not forgiving when you forget a space. The general rule is at least one space between keywords and/or parameters. So, the following statements would be incorrect:
CREATESCHEMA tntdb
CREATE SCHEMAtntdb
The SQL create schema statement is simple, but executing it is often not so simple. For instance, to create a schema you usually need a very high level of DBMS authority. Java programmers do not usually have that level of authority. In addition, simple DBMS such as MS Access do not allow you to create schemas from an application. So, if you try to use this command within a Java class, don’t be surprised if it does not work. In addition, when you identify a table in an SQL command, you may or may not have to specify the schema. This depends on how the database connection was defined (which is partially dependent on the DBMS). For instance, MS Access requires the programmer to identify the schema in SQL statements regardless of the connection definition. An Oracle connection defined to a particular table, frees the programmer from specifying the schema in every SQL command. All examples will specify the schema, but if you are having trouble with the commands, try them without specifying the schema.
All DBMS, however, allow applications to create tables. You can probably guess that the first two keywords are create table. These keywords are followed by the name of the schema, the new table name, and definitions of each column/field in the table. When defining a column/field, you need to provide at least a name and the type of data that will be stored in the column. Depending on the data type specified, you may also be required to specify a field size. Unfortunately, the Java data types, the SQL data types, and the various DBMS data types are not the same. There is considerable overlap, however, and sticking with the basic types like char, double, and int will allow your Java applications to work with all DBMS.
The following is an example of a create table statement that will create a table called “employee” in the TNTDB schema.
CREATE TABLE tntdb.employee
(empnum char(10),
empname char(25),
street char(15),
city char(10),
state char(2),
zip char(5),
payrate double,
exempts int)
Notice that when defining a field as char (this corresponds to a Java String) a size was specified. This is not a requirement. If no size is specified, most DBMSs will create the field with a default length. The problem is that each DBMS can have a different default, so it is best to specify the length of character fields. Fields defined as double and int can’t have a size because the sizes are predefined. The extra spaces and lines placed between the commands, keywords, and column names (to make the statement more readable) are helpful but not required. In fact, the command could be entered as a continuous character string on one line. Note that SQL, like Java, ignores the extra spaces.
Defining tables (and schemas) in an application is very unusual. These DB objects are usually defined once by a database analyst, not every time an application is run. Please remember that a table must be created in a schema before an application can access the table.
Manipulating Data in a Table
SQL has many commands to modify and format data in a table. We will concentrate on the four basic commands of insert into, select, update, and delete.
Use the SQL insert into command to enter a row of data into a table. The command is followed by:
- The name of the schema and table
- The values keyword
- The values to be inserted enclosed in parentheses with a comma separating each value
Additionally, character values must be enclosed in single quotes. As an example, the following statement would insert an employee row for Mary Worker:
INSERT INTO tntdb.employee
VALUES('111', 'Mary Worker', '1 Main St.',
'Enid', 'OK', '77777', 17.50, 3)
Remember to be careful about spaces, commas, parentheses, and quotes.
This example inserted data for every field. You can insert data for only some fields, but you must specify which fields (and the database has to be defined to allow partial records).
The following is an example of inserting a row with only four columns of data specified.
INSERT INTO tntdb.employee (empnum,empname,payrate,exempts)
VALUES('222', 'Joe Programmer', 17.50, 2)
Notice that the column names are specified in parentheses, separated by a comma, and at least one space after the table name. There must also be at least one space separating the closing parenthesis and the values keyword.
The select command retrieves data from a table. The programmer can identify specific rows and or columns to be retrieved. The syntax of the select statement requires at least the select keyword followed by the column names to be retrieved (separated by commas, if individually specified), the from keyword, and the table name.
The following statement returns all rows and columns of the employee table:
SELECT * FROM tntdb.employee
The returned result set would look like the following:
111 Mary Worker 1 Main St. Enid OK 77777 17.5 3
222 Joe Programmer 17.5 2
Notice that an asterisk was specified after the select keyword. This means that all columns should be retrieved. If the statement had only specified four fields as in the following:
SELECT empnum, empname, payrate, exempts
FROM tntdb.employee
then only the four columns from each row would be returned as in the following:
111 Mary Worker 17.5 3
222 Joe Programmer 17.5 2
To retrieve only some rows, use the where keyword with a condition. A condition is comprised of two values and a comparison operation (=, >, <, >=, <=, etc.). The values can be a static value or the name of a table field. For instance, both of the following statements:
SELECT empnum, empname, payrate, exempts
FROM tntdb.employee
WHERE state = “OK”
SELECT empnum, empname, payrate, exempts
FROM tntdb.employee
WHERE exempts >= 3
return the following data:
111 Mary Worker 17.5 3
The first statement used the state field and a character string as part of the where condition. Notice that string (character) values must be enclosed in quotes (as in the first statement), but numeric values are not (as shown in the second statement’s where condition).
SQL also provides data manipulation commands that allow users to modify data in a table, and the where keyword can be used with these statements to identify the particular rows to be manipulated.
Data Manipulation
Data manipulation is where SQL really shines. A single SQL update statement can modify many columns in many rows. The update statement syntax is the update keyword followed by:
The schema and table names
The set keyword
For each column to be changed the following three items must be included:
The column name to be modified
An equal sign
The column’s new value
then:
The where keyword
A condition that identifies the row(s) to be modified
For instance, the following update statement would modify the “Joe Programmer” row by adding address information and changing the payrate column:
UPDATE tntdb.employee
SET street = '2 Maple Ave.',
city = 'Enid',
state = 'OK',
zip = '77777',
payrate = 19
WHERE empnum = '222'
If we ran the following statement to return all rows and columns of the employee table:
SELECT * FROM tntdb.employee
the returned data would be:
111 Mary Worker 1 Main St. Enid OK 77777 17.5 3
222 Joe Programmer 2 Maple Ave. Enid OK 77777 19.0 2
In this example, only one row was modified, however, a where condition can identify many rows. For instance, if the zip code for Enid changed to 77778, we could update all the Enid student records with the following single update statement:
UPDATE tntdb.employee
SET zip = '77778'
WHERE city = 'Enid'
Selecting all records and rows would return the following data:
111 Mary Worker 1 Main St. Enid OK 77778 17.5 3
222 Joe Programmer 2 Maple Ave. Enid OK 77778 19.0 2
Beware: if a where condition is not specified in an update statement, all rows will be changed. This is also true for all statements that support where conditions.
Of course, the ultimate modification to a row is to delete it. Deletions are performed by using the delete from keywords followed by the schema and table names and, optionally:
The where keyword, and
A condition that identifies the rows to be deleted.
The following statement would delete all rows from the table:
DELETE FROM tntdb.employee
but the following would only delete the Mary Worker row:
DELETE FROM tntdb.employee WHERE empName = ‘Mary Worker’
Deleting all the rows from a table does not delete the table. For example, after deleting all the rows in a table, a new row could be inserted because the table (though empty) still exists. If you drop the table, all the rows are eliminated and the table is deleted such that no records can be inserted because the table no longer exists. The drop keyword is used to delete all database constructs. So for instance:
DROP TABLE tntdb.employee
would erase all rows from “employee” and delete the employee table. The following statement would delete the schema and all the tables in that schema:
DROP SCHEMA tntdb
Complex Conditions
Complex (or compound) conditions can be specified by using and and/or or keywords in a where condition. For instance, if we wanted to retrieve those employees who made less than $20 and had more than two exemptions, the following where condition would be specified in the select statement:
SELECT * FROM tntdb.employee
WHERE payrate<20 AND exempts>2
This would return only the “Mary Worker” row because only the “Mary Worker” row’s payrate and exempts fields satisfy the conditions. If an or had been specified as follows:
SELECT * FROM tntdb.employee
WHERE payrate<20 OR exempts>2
both rows would be returned because an or only requires that one of the conditions be true for the row to be selected. In this case, Joe Programmer makes less than $20 per hour, therefore his record would be returned.
You can connect three, four or more conditions with ands and ors. However, the logic behind combining ands and ors is somewhat tricky. As a beginner, you should stick with either ands or ors and not combine them in one where condition.
Java was created to run on any type of computer and, therefore, to interface with many different types of databases. Because SQL is a nonspecific relational database language, SQL is a perfect fit with Java. This is why Java provides a rich set of classes to work with SQL. See the “Database Access” chapter for more information on how to use SQL in a Java application.
One last point of interest is the SQL error message “Token blah blah blah was not valid.” This message will become etched into your brain because of its frequent appearance. Tokens are the individual components that comprise a command. In other words, the command, keywords, values, delimiters (parentheses, spaces, commas, single quotes, etc.) are all tokens. Whenever SQL encounters a syntax error (misspelled command, missing a parenthesis, etc.) the error will be “invalid token” with some portion of the command (the blah blah blah in the example) specified as the offending token. If a space or keyword is omitted, the message is somewhat misleading because the token identified as invalid could be a perfectly valid token. In this example, the token is “invalid” because SQL syntax requires a different token at that point in the command.
What the “invalid token” message really means is that the command syntax was correct up to the token that is identified as invalid. Keep this in mind when trying to decipher the messages. Also, the editor makes a “good faith guess” as to what should have been specified by displaying a “Valid tokens:” clause with a list of tokens that can be specified.