Database Management

• git - everybodies friendly repo
• MySQL -- Basic MySQL commands.

Usually the superuser is user 'postgres'

• $ su - postgres
• $ psql

Some commands to use by superuser:

• createuser, dropuser, createdb, dropdb

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

INSERT INTO weather
    VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO cities
    VALUES ('San Francisco', '(-194.0, 53.0)');
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);

SELECT * FROM weather;

results:

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 43 | 57 | 0 | 1994-11-29 Hayward | 37 | 54 | | 1994-11-29 (3 rows)

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

results:

city | temp_avg | date

---------------+----------+------------ San Francisco | 48 | 1994-11-27 San Francisco | 50 | 1994-11-29 Hayward | 45 | 1994-11-29 (3 rows)

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

results:

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 (1 row)

SELECT DISTINCT city
    FROM weather
    ORDER BY city;

results:

city

Hayward San Francisco (2 rows)

SELECT *
    FROM weather, cities
    WHERE city = name;

results:

city | temp_lo | temp_hi | prcp | date | name | location

---------------+---------+---------+------+------------+---------------+----------- 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) (2 rows)

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

results:

city | temp_lo | temp_hi | prcp | date | location

---------------+---------+---------+------+------------+----------- San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53) San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53) (2 rows)

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

results:

city | temp_lo | temp_hi | prcp | date | location

---------------+---------+---------+------+------------+----------- San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53) San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53) (2 rows)

SELECT *
    FROM weather JOIN cities ON (weather.city = cities.name);
     city      | temp_lo | temp_hi | prcp |    date    |     name      | location  

---------------+---------+---------+------+------------+---------------+----------- 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) (2 rows)

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

city | temp_lo | temp_hi | prcp | date | name | location

---------------+---------+---------+------+------------+---------------+----------- 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) Hayward | 37 | 54 | | 1994-11-29 | | (3 rows)

SELECT W1.city, W1.temp_lo, W1.temp_hi,
       W2.city, W2.temp_lo, W2.temp_hi
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
   and W1.temp_hi > W2.temp_hi;

results:

city | temp_lo | temp_hi | city | temp_lo | temp_hi

---------------+---------+---------+---------------+---------+--------- San Francisco | 43 | 57 | San Francisco | 46 | 50 Hayward | 37 | 54 | San Francisco | 46 | 50 (2 rows)

SELECT max(temp_lo)
    FROM weather;

46 (1 row)

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

city

San Francisco (1 row)

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city;

city | max

---------------+----- Hayward | 37 San Francisco | 46 (2 rows)

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

city | max ---------+----- Hayward | 37 (1 row)

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

UPDATE 2

SELECT * FROM weather;

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 41 | 55 | 0 | 1994-11-29 Hayward | 35 | 52 | | 1994-11-29 (3 rows)

DELETE FROM weather WHERE city = 'Hayward';

DELETE 1

SELECT * FROM weather;

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 41 | 55 | 0 | 1994-11-29 (2 rows)

DELETE FROM weather;

DELETE 2

SELECT * FROM weather;

city | temp_lo | temp_hi | prcp | date ------+---------+---------+------+------ (0 rows)

DROP TABLE weather, cities;

DROP TABLE