O'Reilly logo

Java Enterprise in a Nutshell, Third Edition by William Crawford, Jim Farley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required