src/tutorial/basics.source

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