1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
.pgaw:Help.f.t insert end "SELECT" {bold} " will return rows from one or more tables. Candidates for selection are rows which satisfy the WHERE condition; if WHERE is omitted, all rows are candidates.
" {} "Synopsis" {bold} "
" {} "
SELECT \[ALL|DISTINCT \[ON column\] \]
expression \[ AS
name \] \[, ...\]
\[ INTO \[TEMP\] \[TABLE\] new_table \]
\[ FROM table
\[alias \] \[, ...\] \]
\[ WHERE condition \]
\[ GROUP BY column \[, ...\] \]
\[ HAVING condition \[, ...\] \]
\[ { UNION \[ALL\] | INTERSECT | EXCEPT } select \]
\[ ORDER BY column \[ ASC | DESC \] \[, ...\] \]
\[ FOR UPDATE \[OF class_name...\]\]
\[ LIMIT count \[OFFSET|, count\]\]
" {code} "Usage" {bold} "
To join the table films with the table distributors:
" {} "
SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d, films f
WHERE f.did = d.did
title |did|name | date_prod|kind
-------------------------+---+----------------+----------+----------
The Third Man |101|British Lion |1949-12-23|Drama
The African Queen |101|British Lion |1951-08-11|Romantic
Une Femme est une Femme |102|Jean Luc Godard |1961-03-12|Romantic
Vertigo |103|Paramount |1958-11-14|Action
Becket |103|Paramount |1964-02-03|Drama
48 Hrs |103|Paramount |1982-10-22|Action
War and Peace |104|Mosfilm |1967-02-12|Drama
West Side Story |105|United Artists |1961-01-03|Musical
Bananas |105|United Artists |1971-07-13|Comedy
Yojimbo |106|Toho |1961-06-16|Drama
There's a Girl in my Soup|107|Columbia |1970-06-11|Comedy
Taxi Driver |107|Columbia |1975-05-15|Action
Absence of Malice |107|Columbia |1981-11-15|Action
Storia di una donna |108|Westward |1970-08-15|Romantic
The King and I |109|20th Century Fox|1956-08-11|Musical
Das Boot |110|Bavaria Atelier |1981-11-11|Drama
Bed Knobs and Broomsticks|111|Walt Disney | |Musical
To sum the column len of all films and group the results by kind:
SELECT kind, SUM(len) AS total FROM films GROUP BY kind;
kind |total
----------+------
Action | 07:34
Comedy | 02:58
Drama | 14:28
Musical | 06:42
Romantic | 04:38
To sum the column len of all films, group the results by kind and show those group totals that are less than 5 hours:
SELECT kind, SUM(len) AS total
FROM films
GROUP BY kind
HAVING SUM(len) < INTERVAL '5 hour';
kind |total
----------+------
Comedy | 02:58
Romantic | 04:38
The following two examples are identical ways of sorting the individual results according to the contents of the second column (name):
SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;
did|name
---+----------------
109|20th Century Fox
110|Bavaria Atelier
101|British Lion
107|Columbia
102|Jean Luc Godard
113|Luso films
104|Mosfilm
103|Paramount
106|Toho
105|United Artists
111|Walt Disney
112|Warner Bros.
108|Westward
This example shows how to obtain the union of the tables distributors and actors, restricting the results to those that begin with letter W in each table. Only distinct rows are to be used, so
the ALL keyword is omitted:
-- distributors: actors:
-- did|name id|name
-- ---+------------ --+--------------
-- 108|Westward 1|Woody Allen
-- 111|Walt Disney 2|Warren Beatty
-- 112|Warner Bros. 3|Walter Matthau
-- ... ...
SELECT distributors.name
FROM distributors
WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
FROM actors
WHERE actors.name LIKE 'W%'
name
--------------
Walt Disney
Walter Matthau
Warner Bros.
Warren Beatty
Westward
Woody Allen
" {code} "Compatibility" {bold} "
Extensions
Postgres allows one to omit the FROM clause from a query. This feature was retained from the original PostQuel query language:
" {} "
SELECT distributors.* WHERE name = 'Westwood';
did|name
---+----------------
108|Westward
" {code}
|
