src/tutorial/basics.source

   1 ---------------------------------------------------------------------------
   2 --
   3 -- basics.sql-
   4 --    Tutorial on the basics (table creation and data manipulation)
   5 --
   6 --
   7 -- Copyright (c) 1994, Andrew Yu, University of California
   8 --
   9 -- $PostgreSQL$
  10 --
  11 ---------------------------------------------------------------------------
  12
  13 -----------------------------
  14 -- Creating a New Table:
  15 --      A CREATE TABLE is used to create base tables.  PostgreSQL has
  16 --      its own set of built-in types.  (Note that SQL is case-
  17 --      insensitive.)
  18 -----------------------------
  19
  20 CREATE TABLE weather (
  21         city            varchar(80),
  22         temp_lo         int,            -- low temperature
  23         temp_hi         int,            -- high temperature
  24         prcp            real,           -- precipitation
  25         date            date
  26 );
  27
  28 CREATE TABLE cities (
  29         name            varchar(80),
  30         location        point
  31 );
  32
  33
  34 -----------------------------
  35 -- Populating a Table With Rows:
  36 --      An INSERT statement is used to insert a new row into a table.  There
  37 --      are several ways you can specify what columns the data should go to.
  38 -----------------------------
  39
  40 -- 1. The simplest case is when the list of value correspond to the order of
  41 --    the columns specified in CREATE TABLE.
  42
  43 INSERT INTO weather
  44     VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
  45
  46 INSERT INTO cities
  47     VALUES ('San Francisco', '(-194.0, 53.0)');
  48
  49 -- 2. You can also specify what column the values correspond to.  (The columns
  50 --    can be specified in any order.  You may also omit any number of columns,
  51 --    e.g., unknown precipitation below.
  52
  53 INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
  54     VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
  55
  56 INSERT INTO weather (date, city, temp_hi, temp_lo)
  57     VALUES ('1994-11-29', 'Hayward', 54, 37);
  58
  59
  60 -----------------------------
  61 -- Querying a Table:
  62 --      A SELECT statement is used for retrieving data.  The basic syntax is
  63 --      SELECT columns FROM tables WHERE predicates.
  64 -----------------------------
  65
  66 -- A simple one would be:
  67
  68 SELECT * FROM weather;
  69
  70 -- You may also specify expressions in the target list.  (The 'AS column'
  71 -- specifies the column name of the result.  It is optional.)
  72
  73 SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
  74
  75 -- If you want to retrieve rows that satisfy certain condition (i.e., a
  76 -- restriction), specify the condition in WHERE.  The following retrieves
  77 -- the weather of San Francisco on rainy days.
  78
  79 SELECT *
  80     FROM weather
  81     WHERE city = 'San Francisco'
  82         AND prcp > 0.0;
  83
  84 -- Here is a more complicated one.  Duplicates are removed when DISTINCT is
  85 -- specified. ORDER BY specifies the column to sort on.  (Just to make sure the
  86 -- following won't confuse you, DISTINCT and ORDER BY can be used separately.)
  87
  88 SELECT DISTINCT city
  89     FROM weather
  90     ORDER BY city;
  91
  92
  93 -----------------------------
  94 -- Joins Between Tables:
  95 --      queries can access multiple tables at once or access the same table
  96 --      in such a way that multiple instances of the table are being processed
  97 --      at the same time.
  98 -----------------------------
  99
 100 -- The following joins the weather table and the cities table.
 101
 102 SELECT *
 103     FROM weather, cities
 104     WHERE city = name;
 105
 106 -- This prevents a duplicate city name column:
 107
 108 SELECT city, temp_lo, temp_hi, prcp, date, location
 109     FROM weather, cities
 110     WHERE city = name;
 111
 112 -- since the column names are all different, we don't have to specify the
 113 -- table name. If you want to be clear, you can do the following. They give
 114 -- identical results, of course.
 115
 116 SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location
 117     FROM weather, cities
 118     WHERE cities.name = weather.city;
 119
 120 -- JOIN syntax
 121
 122 SELECT *
 123     FROM weather JOIN cities ON (weather.city = cities.name);
 124
 125 -- Outer join
 126
 127 SELECT *
 128     FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
 129
 130 -- Suppose we want to find all the records that are in the temperature range
 131 -- of other records.  W1 and W2 are aliases for weather.
 132
 133 SELECT W1.city, W1.temp_lo, W1.temp_hi,
 134        W2.city, W2.temp_lo, W2.temp_hi
 135 FROM weather W1, weather W2
 136 WHERE W1.temp_lo < W2.temp_lo
 137    and W1.temp_hi > W2.temp_hi;
 138
 139
 140 -----------------------------
 141 -- Aggregate Functions
 142 -----------------------------
 143
 144 SELECT max(temp_lo)
 145     FROM weather;
 146
 147 SELECT city FROM weather
 148     WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
 149
 150 -- Aggregate with GROUP BY
 151 SELECT city, max(temp_lo)
 152     FROM weather
 153     GROUP BY city;
 154
 155 -- ... and HAVING
 156 SELECT city, max(temp_lo)
 157     FROM weather
 158     GROUP BY city
 159     HAVING max(temp_lo) < 40;
 160
 161
 162 -----------------------------
 163 -- Updates:
 164 --      An UPDATE statement is used for updating data.
 165 -----------------------------
 166
 167 -- Suppose you discover the temperature readings are all off by 2 degrees as
 168 -- of Nov 28, you may update the data as follow:
 169
 170 UPDATE weather
 171     SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
 172     WHERE date > '1994-11-28';
 173
 174 SELECT * FROM weather;
 175
 176
 177 -----------------------------
 178 -- Deletions:
 179 --      A DELETE statement is used for deleting rows from a table.
 180 -----------------------------
 181
 182 -- Suppose you are no longer interested in the weather of Hayward, then you can
 183 -- do the following to delete those rows from the table.
 184
 185 DELETE FROM weather WHERE city = 'Hayward';
 186
 187 SELECT * FROM weather;
 188
 189 -- You can also delete all the rows in a table by doing the following.  (This
 190 -- is different from DROP TABLE which removes the table in addition to the
 191 -- removing the rows.)
 192
 193 DELETE FROM weather;
 194
 195 SELECT * FROM weather;
 196
 197
 198 -----------------------------
 199 -- Removing the tables:
 200 --      DROP TABLE is used to remove tables.  After you have done this, you
 201 --      can no longer use those tables.
 202 -----------------------------
 203
 204 DROP TABLE weather, cities;