1--------------------------------------------------------------------------- 2-- 3-- basics.sql- 4-- Tutorial on the basics (table creation and data manipulation) 5-- 6-- 7-- src/tutorial/basics.source 8-- 9--------------------------------------------------------------------------- 10 11----------------------------- 12-- Creating a New Table: 13-- A CREATE TABLE is used to create base tables. PostgreSQL has 14-- its own set of built-in types. (Note that SQL is case- 15-- insensitive.) 16----------------------------- 17 18CREATE TABLE weather ( 19 city varchar(80), 20 temp_lo int, -- low temperature 21 temp_hi int, -- high temperature 22 prcp real, -- precipitation 23 date date 24); 25 26CREATE TABLE cities ( 27 name varchar(80), 28 location point 29); 30 31 32----------------------------- 33-- Populating a Table With Rows: 34-- An INSERT statement is used to insert a new row into a table. There 35-- are several ways you can specify what columns the data should go to. 36----------------------------- 37 38-- 1. The simplest case is when the list of value correspond to the order of 39-- the columns specified in CREATE TABLE. 40 41INSERT INTO weather 42 VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); 43 44INSERT INTO cities 45 VALUES ('San Francisco', '(-194.0, 53.0)'); 46 47-- 2. You can also specify what column the values correspond to. (The columns 48-- can be specified in any order. You may also omit any number of columns, 49-- e.g., unknown precipitation below. 50 51INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) 52 VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29'); 53 54INSERT INTO weather (date, city, temp_hi, temp_lo) 55 VALUES ('1994-11-29', 'Hayward', 54, 37); 56 57 58----------------------------- 59-- Querying a Table: 60-- A SELECT statement is used for retrieving data. The basic syntax is 61-- SELECT columns FROM tables WHERE predicates. 62----------------------------- 63 64-- A simple one would be: 65 66SELECT * FROM weather; 67 68-- You may also specify expressions in the target list. (The 'AS column' 69-- specifies the column name of the result. It is optional.) 70 71SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather; 72 73-- If you want to retrieve rows that satisfy certain condition (i.e., a 74-- restriction), specify the condition in WHERE. The following retrieves 75-- the weather of San Francisco on rainy days. 76 77SELECT * 78 FROM weather 79 WHERE city = 'San Francisco' 80 AND prcp > 0.0; 81 82-- Here is a more complicated one. Duplicates are removed when DISTINCT is 83-- specified. ORDER BY specifies the column to sort on. (Just to make sure the 84-- following won't confuse you, DISTINCT and ORDER BY can be used separately.) 85 86SELECT DISTINCT city 87 FROM weather 88 ORDER BY city; 89 90 91----------------------------- 92-- Joins Between Tables: 93-- queries can access multiple tables at once or access the same table 94-- in such a way that multiple instances of the table are being processed 95-- at the same time. 96----------------------------- 97 98-- The following joins the weather table and the cities table. 99 100SELECT * 101 FROM weather, cities 102 WHERE city = name; 103 104-- This prevents a duplicate city name column: 105 106SELECT city, temp_lo, temp_hi, prcp, date, location 107 FROM weather, cities 108 WHERE city = name; 109 110-- since the column names are all different, we don't have to specify the 111-- table name. If you want to be clear, you can do the following. They give 112-- identical results, of course. 113 114SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location 115 FROM weather, cities 116 WHERE cities.name = weather.city; 117 118-- JOIN syntax 119 120SELECT * 121 FROM weather JOIN cities ON (weather.city = cities.name); 122 123-- Outer join 124 125SELECT * 126 FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name); 127 128-- Suppose we want to find all the records that are in the temperature range 129-- of other records. W1 and W2 are aliases for weather. 130 131SELECT W1.city, W1.temp_lo, W1.temp_hi, 132 W2.city, W2.temp_lo, W2.temp_hi 133FROM weather W1, weather W2 134WHERE W1.temp_lo < W2.temp_lo 135 and W1.temp_hi > W2.temp_hi; 136 137 138----------------------------- 139-- Aggregate Functions 140----------------------------- 141 142SELECT max(temp_lo) 143 FROM weather; 144 145SELECT city FROM weather 146 WHERE temp_lo = (SELECT max(temp_lo) FROM weather); 147 148-- Aggregate with GROUP BY 149SELECT city, max(temp_lo) 150 FROM weather 151 GROUP BY city; 152 153-- ... and HAVING 154SELECT city, max(temp_lo) 155 FROM weather 156 GROUP BY city 157 HAVING max(temp_lo) < 40; 158 159 160----------------------------- 161-- Updates: 162-- An UPDATE statement is used for updating data. 163----------------------------- 164 165-- Suppose you discover the temperature readings are all off by 2 degrees as 166-- of Nov 28, you may update the data as follow: 167 168UPDATE weather 169 SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 170 WHERE date > '1994-11-28'; 171 172SELECT * FROM weather; 173 174 175----------------------------- 176-- Deletions: 177-- A DELETE statement is used for deleting rows from a table. 178----------------------------- 179 180-- Suppose you are no longer interested in the weather of Hayward, then you can 181-- do the following to delete those rows from the table. 182 183DELETE FROM weather WHERE city = 'Hayward'; 184 185SELECT * FROM weather; 186 187-- You can also delete all the rows in a table by doing the following. (This 188-- is different from DROP TABLE which removes the table in addition to the 189-- removing the rows.) 190 191DELETE FROM weather; 192 193SELECT * FROM weather; 194 195 196----------------------------- 197-- Removing the tables: 198-- DROP TABLE is used to remove tables. After you have done this, you 199-- can no longer use those tables. 200----------------------------- 201 202DROP TABLE weather, cities; 203