learning postgresql

creattable.sql

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

join.sql

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)

populating.sql

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
COPY weather FROM '/home/user/weather.txt';
COPY weather TO '/home/user/weather.txt';

psql.sh

$ psql -U username 

$ psql djangodb
Password: 
psql (9.1.4)
Type "help" for help.

djangodb=# \d
                        List of relations
 Schema |               Name                |   Type   |  Owner   
--------+-----------------------------------+----------+----------
 public | auth_group                        | table    | djangodb

djangodb=# \du
                              List of roles
 Role name  |                   Attributes                   | Member of 
------------+------------------------------------------------+-----------
 djangodb   | Create DB                                      | {}
 postgres   | Superuser, Create role, Create DB, Replication | {}

djangodb=# \q

querying.sql

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

SELECT DISTINCT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather, cities
    WHERE city = name;

SELECT *
    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

SELECT max(temp_lo) FROM weather;

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';

DELETE FROM weather WHERE city = 'Hayward';