New blood and fresh air for tutorial

1 files changed, 98 insertions, 82 deletions

diff --git a/src/tutorial/basics.source b/src/tutorial/basics.source
index e68bf57b4ad..748323eb8e7 100644
--- a/src/tutorial/basics.source
+++ b/src/tutorial/basics.source
@@ -6,22 +6,22 @@
 --
 -- Copyright (c) 1994, Andrew Yu, University of California
 --
--- $Id: basics.source,v 1.3 1999/07/08 15:27:01 momjian Exp $
+-- $Id: basics.source,v 1.4 2001/09/02 23:27:50 petere Exp $
 --
 ---------------------------------------------------------------------------
 
 -----------------------------
--- Creating a table:
---	a CREATE TABLE is used to create base tables. POSTGRES SQL has
---	its own set of built-in types. (Note that keywords are case-
---	insensitive but identifiers are case-sensitive.)
+-- Creating a New Table:
+--	A CREATE TABLE is used to create base tables.  PostgreSQL has
+--	its own set of built-in types.  (Note that SQL is case-
+--	insensitive.)
 -----------------------------
 
 CREATE TABLE weather (
 	city		varchar(80),
 	temp_lo		int,		-- low temperature
 	temp_hi		int,		-- high temperature
-	prcp		float8,		-- precipitation
+	prcp		real,		-- precipitation
 	date		date
 );
 
@@ -30,98 +30,105 @@ CREATE TABLE cities (
 	location	point
 );
 
+
 -----------------------------
--- Inserting data:
---	an INSERT statement is used to insert a new row into a table. There 
+-- Populating a Table With Rows:
+--	An INSERT statement is used to insert a new row into a table.  There 
 --	are several ways you can specify what columns the data should go to.
 -----------------------------
 
--- 1. the simplest case is when the list of value correspond to the order of
+-- 1. The simplest case is when the list of value correspond to the order of
 --    the columns specified in CREATE TABLE.
 
 INSERT INTO weather 
-   VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994');
+    VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
 
 INSERT INTO cities 
-   VALUES ('San Francisco', '(-194.0, 53.0)');
+    VALUES ('San Francisco', '(-194.0, 53.0)');
 
--- 2. you can also specify what column the values correspond to. (The columns
---    can be specified in any order. You may also omit any number of columns.
---    eg. unknown precipitation below)
+-- 2. You can also specify what column the values correspond to.  (The columns
+--    can be specified in any order.  You may also omit any number of columns,
+--    e.g., unknown precipitation below.
 
 INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
-   VALUES ('San Francisco', 43, 57, 0.0, '11/29/1994');
+    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
 
 INSERT INTO weather (date, city, temp_hi, temp_lo)
-   VALUES ('11/29/1994', 'Hayward', 54, 37);
+    VALUES ('1994-11-29', 'Hayward', 54, 37);
 
 
 -----------------------------
--- Retrieving data:
---	a SELECT statement is used for retrieving data. The basic syntax is
---		SELECT columns FROM tables WHERE predicates
+-- Querying a Table:
+--	A SELECT statement is used for retrieving data.  The basic syntax is
+--	SELECT columns FROM tables WHERE predicates.
 -----------------------------
 
--- a simple one would be
+-- A simple one would be:
 
 SELECT * FROM weather;
 
--- you may also specify expressions in the target list (the 'AS column'
--- specifies the column name of the result. It is optional.)
+-- You may also specify expressions in the target list.  (The 'AS column'
+-- specifies the column name of the result.  It is optional.)
 
 SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
 
--- if you want to retrieve rows that satisfy certain condition (ie. a
--- restriction), specify the condition in WHERE. The following retrieves
+-- If you want to retrieve rows that satisfy certain condition (i.e., a
+-- restriction), specify the condition in WHERE.  The following retrieves
 -- the weather of San Francisco on rainy days.
 
 SELECT *
-FROM weather
-WHERE city = 'San Francisco' 
-   and prcp > 0.0;
+    FROM weather
+    WHERE city = 'San Francisco' 
+        AND prcp > 0.0;
 
--- here is a more complicated one. Duplicates are removed when DISTINCT is
--- specified. ORDER BY specifies the column to sort on. (Just to make sure the
+-- Here is a more complicated one.  Duplicates are removed when DISTINCT is
+-- specified. ORDER BY specifies the column to sort on.  (Just to make sure the
 -- following won't confuse you, DISTINCT and ORDER BY can be used separately.)
 
 SELECT DISTINCT city
-FROM weather
-ORDER BY city;
+    FROM weather
+    ORDER BY city;
+
 
 -----------------------------
--- Retrieving data into other classes:
---	a SELECT ... INTO statement can be used to retrieve data into
---	another class.
+-- Joins Between Tables:
+--	queries can access multiple tables at once or access the same table
+--	in such a way that multiple instances of the table are being processed
+--	at the same time.
 -----------------------------
 
-SELECT * INTO TABLE mytemp 
-FROM weather
-WHERE city = 'San Francisco' 
-   and prcp > 0.0;
+-- The following joins the weather table and the cities table.
 
-SELECT * from mytemp;
+SELECT *
+    FROM weather, cities
+    WHERE city = name;
 
------------------------------
--- Aggregates
------------------------------
+-- This prevents a duplicate city name column:
 
-SELECT max(temp_lo)
-FROM weather;
+SELECT city, temp_lo, temp_hi, prcp, date, location
+    FROM weather, cities
+    WHERE city = name;
 
--- Aggregate with GROUP BY
-SELECT city, max(temp_lo)
-FROM weather 
-GROUP BY city;
+-- since the column names are all different, we don't have to specify the
+-- table name. If you want to be clear, you can do the following. They give
+-- identical results, of course.
 
------------------------------
--- Joining tables:
---	queries can access multiple tables at once or access the same table
---	in such a way that multiple instances of the table are being processed
---	at the same time.
------------------------------
+SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location
+    FROM weather, cities
+    WHERE cities.name = weather.city;
 
--- suppose we want to find all the records that are in the temperature range
--- of other records. W1 and W2 are aliases for weather.
+-- JOIN syntax
+
+SELECT *
+    FROM weather JOIN cities ON (weather.city = cities.name);
+
+-- Outer join
+
+SELECT *
+    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
+
+-- Suppose we want to find all the records that are in the temperature range
+-- of other records.  W1 and W2 are aliases for weather.
 
 SELECT W1.city, W1.temp_lo, W1.temp_hi, 
        W2.city, W2.temp_lo, W2.temp_hi
@@ -129,60 +136,69 @@ FROM weather W1, weather W2
 WHERE W1.temp_lo < W2.temp_lo 
    and W1.temp_hi > W2.temp_hi;
 
--- let's join two tables. The following joins the weather table
--- and the cities table.
 
-SELECT city, location, prcp, date
-FROM weather, cities
-WHERE name = city;
+-----------------------------
+-- Aggregate Functions
+-----------------------------
 
--- since the column names are all different, we don't have to specify the
--- table name. If you want to be clear, you can do the following. They give
--- identical results, of course.
+SELECT max(temp_lo)
+    FROM weather;
+
+SELECT city FROM weather
+    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
+
+-- Aggregate with GROUP BY
+SELECT city, max(temp_lo)
+    FROM weather 
+    GROUP BY city;
+
+-- ... and HAVING
+SELECT city, max(temp_lo)
+    FROM weather
+    GROUP BY city
+    HAVING max(temp_lo) < 40;
 
-SELECT w.city, c.location, w.prcp, w.date
-FROM weather w, cities c
-WHERE c.name = w.city;
 
 -----------------------------
--- Updating data:
---	an UPDATE statement is used for updating data. 
+-- Updates:
+--	An UPDATE statement is used for updating data.
 -----------------------------
 
--- suppose you discover the temperature readings are all off by 2 degrees as
+-- Suppose you discover the temperature readings are all off by 2 degrees as
 -- of Nov 28, you may update the data as follow:
 
 UPDATE weather
-  SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
-  WHERE date > '11/28/1994';
+    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
+    WHERE date > '1994-11-28';
 
-SELECT * from weather;
+SELECT * FROM weather;
 
 
 -----------------------------
--- Deleting data:
---	a DELETE statement is used for deleting rows from a table.
+-- Deletions:
+--	A DELETE statement is used for deleting rows from a table.
 -----------------------------
 
--- suppose you are no longer interested in the weather of Hayward, you can
--- do the following to delete those rows from the table
+-- Suppose you are no longer interested in the weather of Hayward, then you can
+-- do the following to delete those rows from the table.
 
 DELETE FROM weather WHERE city = 'Hayward';
 
-SELECT * from weather;
+SELECT * FROM weather;
 
--- you can also delete all the rows in a table by doing the following. (This
+-- You can also delete all the rows in a table by doing the following.  (This
 -- is different from DROP TABLE which removes the table in addition to the 
 -- removing the rows.)
 
 DELETE FROM weather;
 
-SELECT * from weather;
+SELECT * FROM weather;
+
 
 -----------------------------
 -- Removing the tables:
---	DROP TABLE is used to remove tables. After you have done this, you
+--	DROP TABLE is used to remove tables.  After you have done this, you
 --      can no longer use those tables.
 -----------------------------
 
-DROP TABLE weather, cities, mytemp;
+DROP TABLE weather, cities;