This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] expression [ AS output_name ] [, ...] [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ { UNION | INTERSECT | EXCEPT [ALL] } select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] [ FOR UPDATE [ OF tablename [, ...] ] ] LIMIT { count | ALL } [ { OFFSET | , } start ]
You can the following command to select all ( * ) columns from the book table:
SELECT * FROM book;
We can add another relation to our database that contains data about the author of the books. Currently, we only have their author identifier numbers, which makes it easier to access several tables at one time.
The author table has an a_id, which is of type integer. It is a non-null unique primary key. It contains corresponding author numbers to the book table. The last and first name are the names of authors who wrote the books. Lastly, the email column is the e-mail address of each author. Create a table called author and insert the following values into it:
Table 4-5. Author Table Data Values
a_id | lastname | firstname | |
---|---|---|---|
142 | 'Burgeous' | 'Paulette' | 'pburgeous@yahoo.com' |
220 | 'Williams' | 'Margary' | 'mwilliams@goto.com' |
231 | 'Alcott' | 'Luoisa May' | 'lalcott@bestbooks.com' |
136 | 'Brown' | 'Margaret W.' | 'mwbrown@littletykes.com' |
110 | 'Seuss' | '' | 'seuss@childprodigy.com' |
After having created this table, it contains this:
Example 4-18. Author Table
a_id | lastname | firstname | email ------+----------+-------------+------------------------- 136 | Brown | Margaret W. | mwbrown@littletykes.com 220 | Williams | Margary | mwilliams@goto.com 142 | Burgeous | Paulette | pburgeous@yahoo.com 110 | Seuss | | seuss@childprodigy.com 231 | Alcott | Luoisa May | lalcott@bestbooks.com (5 rows)
 
Continue to: