Java Enterprise in a Nutshell, Second Edition

Data Manipulation Commands

Empty tables aren’t very useful, and, even once they’ve been populated with data, we need some way of getting that data back out. The SQL data manipulation commands allow you to read data from a table and to create, update, and remove existing data.

SELECT

The SELECT statement is the most important statement in SQL and also the most complex. It allows you to retrieve data from a table or a set of tables. Here’s the syntax:

SELECT [ DISTINCT ]
   { summary_function, ... }
   | { data_manipulation_expression, ... }
   | { column_name, ... }
FROM 
   { { table_name [ AS correlation_name ] }
   | { subquery [ AS correlation_name ] }
   | joined_tables}
[ WHERE predicate ]
[ GROUP BY column_name, ... [ HAVING group_selection_predicate ] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] 
     [ CORRESPONDING [ BY (column_name, ...] ]
      select_statement | { TABLE table_name } 
                       | table_value_constructor ]
[ ORDER BY {{output_column [ ASC | DESC ]}, ...}
           | {{positive_integer [ ASC | DESC ]}, ...}]

The simplest possible SELECT, which displays all columns of all rows of a single table, looks like this:

SELECT * FROM BOOKS

If this statement is executed in a command-line SQL interpreter, the output might look like this:

TITLE                | AUTHOR           | EDITION | PRICE
---------------------+------------------+---------+-------
Me                   | Garrison Keillor |       1 | 24.99
Bleak House          | Charles Dickens  |      57 |  8.99
A Tale Of Two Cities | Charles Dickens  |     312 |  4.99

To sort the output by title, we can add an ORDER BY clause to the ...