Java Reference
In-Depth Information
The BETWEEN statement also uses AND. The following statement selects
all employees whose last name is between Saunders and Smith:
SELECT * FROM Employees WHERE last BETWEEN 'Saunders' AND 'Smith'
The NOT operator can be used in a WHERE clause to negate a Boolean
expression. The following SELECT statement selects employees whose first
name does not start with an R:
SELECT * FROM Employees WHERE first NOT LIKE 'R%'
By default, the SELECT statement returns all elements that match the
WHERE condition, even if the result set is not unique. For example, searching
the Employees table for all first names that start with an R will return Rich
twice if the database contains a Rich Raposa and a Rich Little. If the DISTINCT
keyword is used, the result set will only contain Rich once:
SELECT DISTINCT first FROM Employees WHERE first LIKE 'R%'
The SELECT statement can contain an optional ORDER BY clause that sorts
the elements in the result set. For example, the following SELECT statement
returns all employees in the database, sorting the result set by last name:
SELECT * FROM Employees ORDER BY last
When using ORDER BY, you can use the ASC to denote ascending order,
which is the default, and DESC to denote descending order. The following
statement returns all employees whose last name starts with R, sorting them in
descending order of their pay rate, but ascending order by their last name:
SELECT * FROM Employees WHERE last LIKE 'R%' ORDER BY payRate DESC, last ASC
Updating Data
The UPDATE statement is used to update data. The syntax for UPDATE is:
UPDATE
table_name
SET
column_name
=
value, column_name
=
value,
...
WHERE
conditions
The following UPDATE statement changes the payRate column of the
employee whose number is 101:
UPDATE Employees SET payRate=40.00 WHERE number=101
