Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Putting It All Together

In summary, a SQL statement is comprised of tokens, where each token can represent either a keyword, identifier, quoted identifier, constant, or special character symbol. Table 3-7 uses a simple SELECT statement to illustrate a basic, but complete, SQL statement and its components.

Table 3-7. A simple SQL query

SELECT

id, name

FROM

states

Token Type

Keyword

Identifiers

Keyword

Identifier

Description

Command

Id and name columns

Clause

Table name

As shown in the table, the SELECT statement contains the keywords SELECT and FROM. Together, the FROM keyword and states token compose a clause, as they modify and further describe the SELECT command.

The id, name, and states tokens are the identifiers of the statement. The id and name identifiers specify the selected columns, while the states identifier specifies the table name to select from. Therefore, with the preceding SQL query, you are instructing PostgreSQL to display the columns named id and name for each row from the states table. Example 3-13 shows the output this query generates within the booktown database.

Example 3-13. Example SQL query

booktown=# 
SELECT id, name FROM states;

 id |    name
----+------------
 42 | Washington
 51 | Oregon
(2 rows)

booktown=#

Getting more complicated, Table 3-8 and Table 3-9 break down another example statement. This statement uses the UPDATE command, along with SET and WHERE clauses, which respectively specify with what to update the records, and how to find the records to update.

Table 3-8. UPDATE example: the SET clause

UPDATE

states

SET

id

=

51

keyword

identifier

keyword

identifier

operator

integer constant

command

table name

clause

column

assignment

new id value

Table 3-9. UPDATE example: the WHERE clause

WHERE

name

=

'Oregon'

keyword

identifier

operator

string constant

clause

column name

equivalence

string value to match

When executed, this statement examines each record's name column to find matches for the WHERE clause's stated condition (equivalence to the string constant 'Oregon'). Then, for each row which matches that condition, it updates the id column with the value 51.

Breaking it down, this UPDATE statement has three keywords, three identifiers, two operators, and two constants. The keywords are UPDATE (the SQL command), SET (specifies the updates to make), and WHERE (identifies the rows to update). The identifiers are the states table name, the id column name, and the name column name.

The operators are both represented by the = operator. When used with the SET clause, this operator is used for assignment (to assign a new value to an existing record's identified column); this is a special use which is unique to the SET clause. In contrast, when used with the WHERE clause, the = operator is used to check equivalence between values. In this case, this means that the equivalence operator will check the value of a record's name column against a string constant with the value of Oregon .

Finally, the constants in this statement are the integer constant 51 (the new value for the id column), and the string constant Oregon (compared to the name column through the WHERE clause).

Example 3-14 therefore updates the states table by setting the id column to 51 whenever the name column matches the value Oregon . It then checks the results of that UPDATE statement with another SELECT statement.

Example 3-14. A SQL update

booktown=# 
UPDATE states 

booktown-# 
   SET id = 51

booktown-# 
 WHERE name = 'Oregon';

UPDATE 1
booktown=# 
SELECT * FROM states 

booktown-# 
 WHERE name = 'Oregon';

 id |  name  | abbreviation
----+--------+--------------
 51 | Oregon | OR
(1 row)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire