From 46651ce6fe013220ed397add242004d764fc0153 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:15:05 +0200 Subject: Adding upstream version 14.5. Signed-off-by: Daniel Baumann --- src/tutorial/basics.source | 198 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 198 insertions(+) create mode 100644 src/tutorial/basics.source (limited to 'src/tutorial/basics.source') diff --git a/src/tutorial/basics.source b/src/tutorial/basics.source new file mode 100644 index 0000000..3e74d71 --- /dev/null +++ b/src/tutorial/basics.source @@ -0,0 +1,198 @@ +--------------------------------------------------------------------------- +-- +-- 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; + +-- 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, w1.temp_hi, + w2.city, w2.temp_lo, w2.temp_hi +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, 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; + + +----------------------------- +-- 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; -- cgit v1.2.3