216 lines
5.9 KiB
Plaintext
216 lines
5.9 KiB
Plaintext
---------------------------------------------------------------------------
|
|
--
|
|
-- 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;
|