1 ---------------------------------------------------------------------------
4 -- Tutorial on the basics (table creation and data manipulation)
7 -- Copyright (c) 1994, Andrew Yu, University of California
9 -- $Id: basics.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $
11 ---------------------------------------------------------------------------
13 -----------------------------
15 -- a CREATE TABLE is used to create base tables. POSTGRES SQL has
16 -- its own set of built-in types. (Note that keywords are case-
17 -- insensitive but identifiers are case-sensitive.)
18 -----------------------------
20 CREATE TABLE weather (
22 temp_lo int, -- low temperature
23 temp_hi int, -- high temperature
24 prcp float8, -- precipitation
33 -----------------------------
35 -- an INSERT statement is used to insert a new row into a table. There
36 -- are several ways you can specify what columns the data should go to.
37 -----------------------------
39 -- 1. the simplest case is when the list of value correspond to the order of
40 -- the columns specified in CREATE TABLE.
43 VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994')
46 VALUES ('San Francisco', '(-194.0, 53.0)');
48 -- 2. you can also specify what column the values correspond to. (The columns
49 -- can be specified in any order. You may also omit any number of columns.
50 -- eg. unknown precipitation below)
52 INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
53 VALUES ('San Francisco', 43, 57, 0.0, '11/29/1994')
55 INSERT INTO weather (date, city, temp_hi, temp_lo)
56 VALUES ('11/29/1994', 'Hayward', 54, 37);
59 -----------------------------
61 -- a SELECT statement is used for retrieving data. The basic syntax is
62 -- SELECT columns FROM tables WHERE predicates
63 -----------------------------
65 -- a simple one would be
67 SELECT * FROM weather;
69 -- you may also specify expressions in the target list (the 'AS column'
70 -- specifies the column name of the result. It is optional.)
72 SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
74 -- if you want to retrieve rows that satisfy certain condition (ie. a
75 -- restriction), specify the condition in WHERE. The following retrieves
76 -- the weather of San Francisco on rainy days.
80 WHERE city = 'San Francisco'
83 -- here is a more complicated one. Duplicates are removed when DISTINCT is
84 -- specified. ORDER BY specifies the column to sort on. (Just to make sure the
85 -- following won't confuse you, DISTINCT and ORDER BY can be used separately.)
91 -----------------------------
92 -- Retrieving data into other classes:
93 -- a SELECT ... INTO statement can be used to retrieve data into
95 -----------------------------
97 SELECT * INTO TABLE temp
99 WHERE city = 'San Francisco'
104 -----------------------------
106 -----------------------------
111 -- Aggregate with GROUP BY
112 SELECT city, max(temp_lo)
116 -----------------------------
118 -- queries can access multiple tables at once or access the same table
119 -- in such a way that multiple instances of the table are being processed
121 -----------------------------
123 -- suppose we want to find all the records that are in the temperature range
124 -- of other records. W1 and W2 are aliases for weather.
126 SELECT W1.city, W1.temp_lo, W1.temp_hi,
127 W2.city, W2.temp_lo, W2.temp_hi
128 FROM weather W1, weather W2
129 WHERE W1.temp_lo < W2.temp_lo
130 and W1.temp_hi > W2.temp_hi;
132 -- let's join two tables. The following joins the weather table
133 -- and the cities table.
135 SELECT city, location, prcp, date
139 -- since the column names are all different, we don't have to specify the
140 -- table name. If you want to be clear, you can do the following. They give
141 -- identical results, of course.
143 SELECT w.city, c.location, w.prcp, w.date
144 FROM weather w, cities c
145 WHERE c.name = w.city;
147 -----------------------------
149 -- an UPDATE statement is used for updating data.
150 -----------------------------
152 -- suppose you discover the temperature readings are all off by 2 degrees as
153 -- of Nov 28, you may update the data as follow:
156 SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
157 WHERE date > '11/28/1994';
159 SELECT * from weather;
162 -----------------------------
164 -- a DELETE statement is used for deleting rows from a table.
165 -----------------------------
167 -- suppose you are no longer interested in the weather of Hayward, you can
168 -- do the following to delete those rows from the table
170 DELETE FROM weather WHERE city = 'Hayward';
172 SELECT * from weather;
174 -- you can also delete all the rows in a table by doing the following. (This
175 -- is different from DROP TABLE which removes the table in addition to the
176 -- removing the rows.)
180 SELECT * from weather;
182 -----------------------------
183 -- Removing the tables:
184 -- DROP TABLE is used to remove tables. After you have done this, you
185 -- can no longer use those tables.
186 -----------------------------
188 DROP TABLE weather, cities, temp;