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 -- You can request that the results of a query be returned in sorted order:
  83
  84 SELECT * FROM weather
  85     ORDER BY city, temp_lo;
  86
  87 -- Here is a more complicated one.  Duplicates are removed when DISTINCT is
  88 -- specified. ORDER BY specifies the column to sort on.  (Just to make sure the
  89 -- following won't confuse you, DISTINCT and ORDER BY can be used separately.)
  90
  91 SELECT DISTINCT city
  92     FROM weather
  93     ORDER BY city;
  94
  95
  96 -----------------------------
  97 -- Joins Between Tables:
  98 --      queries can access multiple tables at once or access the same table
  99 --      in such a way that multiple instances of the table are being processed
 100 --      at the same time.
 101 -----------------------------
 102
 103 -- The following joins the weather table and the cities table.
 104
 105 SELECT * FROM weather JOIN cities ON city = name;
 106
 107 -- This prevents a duplicate city name column:
 108
 109 SELECT city, temp_lo, temp_hi, prcp, date, location
 110     FROM weather JOIN cities ON 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,
 117        weather.prcp, weather.date, cities.location
 118     FROM weather JOIN cities ON weather.city = cities.name;
 119
 120 -- Old join syntax
 121
 122 SELECT *
 123     FROM weather, cities
 124     WHERE city = name;
 125
 126 -- Outer join
 127
 128 SELECT *
 129     FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;
 130
 131 -- Suppose we want to find all the records that are in the temperature range
 132 -- of other records.  w1 and w2 are aliases for weather.
 133
 134 SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
 135        w2.city, w2.temp_lo AS low, w2.temp_hi AS high
 136 FROM weather w1 JOIN weather w2
 137     ON w1.temp_lo < w2.temp_lo 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, count(*), max(temp_lo)
 152     FROM weather
 153     GROUP BY city;
 154
 155 -- ... and HAVING
 156 SELECT city, count(*), max(temp_lo)
 157     FROM weather
 158     GROUP BY city
 159     HAVING max(temp_lo) < 40;
 160
 161 -- We can filter rows before aggregating them:
 162 SELECT city, count(*), max(temp_lo)
 163     FROM weather
 164     WHERE city LIKE 'S%'
 165     GROUP BY city;
 166
 167 -- Another way is the FILTER clause, which operates per-aggregate:
 168 SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
 169     FROM weather
 170     GROUP BY city;
 171
 172
 173 -----------------------------
 174 -- Updates:
 175 --      An UPDATE statement is used for updating data.
 176 -----------------------------
 177
 178 -- Suppose you discover the temperature readings are all off by 2 degrees as
 179 -- of Nov 28, you may update the data as follow:
 180
 181 UPDATE weather
 182     SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
 183     WHERE date > '1994-11-28';
 184
 185 SELECT * FROM weather;
 186
 187
 188 -----------------------------
 189 -- Deletions:
 190 --      A DELETE statement is used for deleting rows from a table.
 191 -----------------------------
 192
 193 -- Suppose you are no longer interested in the weather of Hayward, then you can
 194 -- do the following to delete those rows from the table.
 195
 196 DELETE FROM weather WHERE city = 'Hayward';
 197
 198 SELECT * FROM weather;
 199
 200 -- You can also delete all the rows in a table by doing the following.  (This
 201 -- is different from DROP TABLE which removes the table in addition to the
 202 -- removing the rows.)
 203
 204 DELETE FROM weather;
 205
 206 SELECT * FROM weather;
 207
 208
 209 -----------------------------
 210 -- Removing the tables:
 211 --      DROP TABLE is used to remove tables.  After you have done this, you
 212 --      can no longer use those tables.
 213 -----------------------------
 214
 215 DROP TABLE weather, cities;