look up, sort, and generate reports on that data. Basic data retrieval is done
with the SELECT statement, which has the following syntax:
Click here to view code image
SELECT column1, column2, column3 FROM table_name WHERE
search_criteria;
The first two parts of the statement—the SELECT and FROM parts—are
required. The WHERE portion of the statement is optional. If it is omitted, all
rows in the table table_name are returned.
The column1, column2, column3 syntax allows you to specify the names of
the columns you want to see. If you want to see all columns, you can also use
the wildcard * to show all the columns that match the search criteria. For
example, the following statement displays all columns from the
cd_collection table:
Click here to view code image
SELECT * FROM cd_collection;
If you want to see only the titles of all the CDs in the table, you use a
statement such as the following:
Click here to view code image
SELECT title FROM cd_collection;
To select the title and year of a CD, you use the following:
Click here to view code image
SELECT title, year FROM cd_collection;
If you want something a little fancier, you can use SQL to print the CD title
followed by the year in parentheses, as is the convention. Both MySQL and
PostgreSQL provide string concatenation functions to handle problems such
as this. However, the syntax is different in the two systems.
In MySQL, you can use the CONCAT() function to combine the title and
year columns into one output column, along with parentheses. The
following statement is an example:
Click here to view code image
SELECT CONCAT(title,"(",year, ")") AS TitleYear FROM cd_collection;
That statement lists both the title and year under one column that has the label
TitleYear. Note that there are two strings in the CONCAT() function
along with the fields; these add white space and the parentheses.
In PostgreSQL, the string concatenation function is simply a double pipe