(||). The following command is the PostgreSQL equivalent of the preceding
MySQL command:
Click here to view code image
SELECT (genus||'' ('||species||')') AS TitleYear FROM cd_collection;
Note that the parentheses are optional, but they make the statement easier to
read. Once again, the strings in the middle and at the end (note the space
between the quotes) are used to insert spacing and parentheses between the
title and year.
Of course, more often than not, you do not want a list of every single row in a
database. Rather, you want to find rows that match certain characteristics. For
this, you add the WHERE statement to the SELECT statement. For example,
suppose you want to find all the CDs in the cd_collection table that
have a rating of 5. You would use a statement like the following:
Click here to view code image
SELECT * FROM cd_collection WHERE rating = 5;
Using the table from Figure 28.2, you can see that this query would return the
rows for Trouser Jazz, Life for Rent, and The Two Towers. This is a simple
query, and SQL is capable of handling queries much more complex than this.
You can write complex queries using logical AND and logical OR statements.
For example, to refine the query so it lists only CDs that were not released in
2003, you use a query like the following:
Click here to view code image
SELECT * FROM cd_collection WHERE rating = 5 AND year != 2003;
In SQL, != means “is not equal to.” So, once again looking at the table from
Figure 28.2, you can see that this query returns the rows for Trouser Jazz and
The Two Towers but does not return the row for Life for Rent because it was
released in 2003.
So, what if you want to list all the CDs that have a rating of 3 or 4 except
those released in the year 2000? This time, you combine logical AND and
logical OR statements:
Click here to view code image
SELECT * FROM cd_collection WHERE rating = 3 OR rating = 4 AND year
!= 2000;
This query returns entries for Mind Bomb, Natural Elements, and Combat
Rock. However, it does not return entries for Adiemus 4 because it was
released in 2000.