Postgres95 1.01 Distribution - Virgin SourcesPG95-1_01

1 files changed, 188 insertions, 0 deletions

diff --git a/src/tutorial/basics.source b/src/tutorial/basics.source
new file mode 100644
index 00000000000..fcda8b3b8c3
--- /dev/null
+++ b/src/tutorial/basics.source
@@ -0,0 +1,188 @@
+---------------------------------------------------------------------------
+--
+-- basics.sql-
+--    Tutorial on the basics (table creation and data manipulation)
+--
+--
+-- Copyright (c) 1994, Andrew Yu, University of California
+--
+-- $Id: basics.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy 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.)
+-----------------------------
+
+CREATE TABLE weather (
+	city		varchar(80),
+	temp_lo		int,		-- low temperature
+	temp_hi		int,		-- high temperature
+	prcp		float8,		-- precipitation
+	date		date
+)
+
+CREATE TABLE cities (
+	name		varchar(80),
+	location	point
+);
+
+-----------------------------
+-- Inserting data:
+--	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
+--    the columns specified in CREATE TABLE.
+
+INSERT INTO weather 
+   VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994')
+
+INSERT INTO cities 
+   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)
+
+INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
+   VALUES ('San Francisco', 43, 57, 0.0, '11/29/1994')
+
+INSERT INTO weather (date, city, temp_hi, temp_lo)
+   VALUES ('11/29/1994', 'Hayward', 54, 37);
+
+
+-----------------------------
+-- Retrieving data:
+--	a SELECT statement is used for retrieving data. The basic syntax is
+--		SELECT columns FROM tables WHERE predicates
+-----------------------------
+
+-- 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.)
+
+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
+-- the weather of San Francisco on rainy days.
+
+SELECT *
+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
+-- following won't confuse you, DISTINCT and ORDER BY can be used separately.)
+
+SELECT DISTINCT city
+FROM weather
+ORDER BY city;
+
+-----------------------------
+-- Retrieving data into other classes:
+--	a SELECT ... INTO statement can be used to retrieve data into
+--	another class.
+-----------------------------
+
+SELECT * INTO TABLE temp 
+FROM weather
+WHERE city = 'San Francisco' 
+   and prcp > 0.0;
+
+SELECT * from temp;
+
+-----------------------------
+-- Aggregates
+-----------------------------
+
+SELECT max(temp_lo)
+FROM weather;
+
+-- Aggregate with GROUP BY
+SELECT city, max(temp_lo)
+FROM weather 
+GROUP BY city;
+
+-----------------------------
+-- 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.
+-----------------------------
+
+-- 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
+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;
+
+-- 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 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. 
+-----------------------------
+
+-- 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';
+
+SELECT * from weather;
+
+
+-----------------------------
+-- Deleting data:
+--	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
+
+DELETE FROM weather WHERE city = 'Hayward';
+
+SELECT * from weather;
+
+-- 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;
+
+-----------------------------
+-- Removing the tables:
+--	DROP TABLE is used to remove tables. After you have done this, you
+--      can no longer use those tables.
+-----------------------------
+
+DROP TABLE weather, cities, temp;