1 files changed, 215 insertions, 0 deletions
diff --git a/src/tutorial/basics.source b/src/tutorial/basics.source
new file mode 100644
index 0000000..d09ff50
--- /dev/null
+++ b/src/tutorial/basics.source
@@ -0,0 +1,215 @@
+---------------------------------------------------------------------------
+--
+-- basics.sql-
+--    Tutorial on the basics (table creation and data manipulation)
+--
+--
+-- src/tutorial/basics.source
+--
+---------------------------------------------------------------------------
+
+-----------------------------
+-- 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		real,		-- precipitation
+	date		date
+);
+
+CREATE TABLE cities (
+	name		varchar(80),
+	location	point
+);
+
+
+-----------------------------
+-- 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
+--    the columns specified in CREATE TABLE.
+
+INSERT INTO weather
+    VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
+
+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,
+--    e.g., unknown precipitation below.
+
+INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
+    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
+
+INSERT INTO weather (date, city, temp_hi, temp_lo)
+    VALUES ('1994-11-29', 'Hayward', 54, 37);
+
+
+-----------------------------
+-- 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:
+
+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 (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;
+
+-- You can request that the results of a query be returned in sorted order:
+
+SELECT * FROM weather
+    ORDER BY city, temp_lo;
+
+-- 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;
+
+
+-----------------------------
+-- 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.
+-----------------------------
+
+-- The following joins the weather table and the cities table.
+
+SELECT * FROM weather JOIN cities ON city = name;
+
+-- This prevents a duplicate city name column:
+
+SELECT city, temp_lo, temp_hi, prcp, date, location
+    FROM weather JOIN cities ON city = name;
+
+-- 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 weather.city, weather.temp_lo, weather.temp_hi,
+       weather.prcp, weather.date, cities.location
+    FROM weather JOIN cities ON weather.city = cities.name;
+
+-- Old join syntax
+
+SELECT *
+    FROM weather, cities
+    WHERE city = 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 AS low, w1.temp_hi AS high,
+       w2.city, w2.temp_lo AS low, w2.temp_hi AS high
+FROM weather w1 JOIN weather w2
+    ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;
+
+
+-----------------------------
+-- Aggregate Functions
+-----------------------------
+
+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, count(*), max(temp_lo)
+    FROM weather
+    GROUP BY city;
+
+-- ... and HAVING
+SELECT city, count(*), max(temp_lo)
+    FROM weather
+    GROUP BY city
+    HAVING max(temp_lo) < 40;
+
+-- We can filter rows before aggregating them:
+SELECT city, count(*), max(temp_lo)
+    FROM weather
+    WHERE city LIKE 'S%'
+    GROUP BY city;
+
+-- Another way is the FILTER clause, which operates per-aggregate:
+SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
+    FROM weather
+    GROUP BY city;
+
+
+-----------------------------
+-- Updates:
+--	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 > '1994-11-28';
+
+SELECT * FROM weather;
+
+
+-----------------------------
+-- Deletions:
+--	A DELETE statement is used for deleting rows from a 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;
+
+-- 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;