create a table to store information about weather observation stations: 
-- No duplicate ID fields allowed

CREATE TABLE STATION 
(ID INTEGER PRIMARY KEY, 
CITY CHAR(20), 
STATE CHAR(2), 
LAT_N REAL, 
LONG_W REAL);

populate the table STATION with a few rows:

INSERT INTO STATION VALUES (13, 'Phoenix', 'AZ', 33, 112); 
INSERT INTO STATION VALUES (44, 'Denver', 'CO', 40, 105); 
INSERT INTO STATION VALUES (66, 'Caribou', 'ME', 47, 68);

query to look at table STATION in undefined order:

SELECT * FROM STATION;

query to select Northern stations (Northern latitude > 39.7): 
-- selecting only certain rows is called a "restriction".

SELECT * FROM STATION 
WHERE LAT_N > 39.7;

query to select only ID, CITY, and STATE columns: 
-- selecting only certain columns is called a "projection".

SELECT ID, CITY, STATE FROM STATION;

query to both "restrict" and "project":

SELECT ID, CITY, STATE FROM STATION 
WHERE LAT_N > 39.7;

create another table to store normalized temperature and precipitation data: 
-- ID field must match some STATION table ID 
(so name and location will be known). 
-- allowable ranges will be enforced for other values. 
-- no duplicate ID and MONTH combinations. 
-- temperature is in degrees Fahrenheit. 
-- rainfall is in inches.

CREATE TABLE STATS 
(ID INTEGER REFERENCES STATION(ID), 
MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12), 
TEMP_F REAL CHECK (TEMP_F BETWEEN -80 AND 150), 
RAIN_I REAL CHECK (RAIN_I BETWEEN 0 AND 100), 
PRIMARY KEY (ID, MONTH));

populate the table STATS with some statistics for January and July:

INSERT INTO STATS VALUES (13, 1, 57.4, 0.31); 
INSERT INTO STATS VALUES (13, 7, 91.7, 5.15); 
INSERT INTO STATS VALUES (44, 1, 27.3, 0.18); 
INSERT INTO STATS VALUES (44, 7, 74.8, 2.11); 
INSERT INTO STATS VALUES (66, 1, 6.7, 2.10); 
INSERT INTO STATS VALUES (66, 7, 65.8, 4.52);

query to look at table STATS in undefined order:

SELECT * FROM STATS;

query to look at table STATS, picking up location information by joining with table STATION on the ID column: 
-- matching two tables on a common column is called a "join". 
-- the column names often match, but this is not required. 
-- only the column values are required to match.

SELECT * FROM STATION, STATS 
WHERE STATION.ID = STATS.ID;

query to look at the table STATS, ordered by month and greatest rainfall, with columns rearranged:

SELECT MONTH, ID, RAIN_I, TEMP_F 
FROM STATS 
ORDER BY MONTH, RAIN_I DESC;

query to look at temperatures for July from table STATS, lowest temperatures first, picking up city name and latitude by joining with table STATION on the ID column:

SELECT LAT_N, CITY, TEMP_F 
FROM STATS, STATION 
WHERE MONTH = 7 
AND STATS.ID = STATION.ID 
ORDER BY TEMP_F;

query to show MAX and MIN temperatures as well as average rainfall for each station:

SELECT MAX(TEMP_F), MIN(TEMP_F), AVG(RAIN_I), ID 
FROM STATS 
GROUP BY ID;

query (with subquery) to show stations with year-round average temperature above 50 degrees: 
-- rows are selected from the STATION table based on related values in the STATS table.

SELECT * FROM STATION 
WHERE 50 < (SELECT AVG(TEMP_F) FROM STATS 
WHERE STATION.ID = STATS.ID);

create a view (derived table or persistent query) to convert Fahrenheit to Celsius and inches to centimeters:

CREATE VIEW METRIC_STATS (ID, MONTH, TEMP_C, RAIN_C) AS 
SELECT ID, 
MONTH, 
(TEMP_F - 32) * 5 /9, 
RAIN_I * 0.3937 
FROM STATS;

query to look at table STATS in a metric light (through the new view):

SELECT * FROM METRIC_STATS;

another metric query restricted to January below-freezing (0 Celsius) data, sorted on rainfall:

SELECT * FROM METRIC_STATS 
WHERE TEMP_C < 0 AND MONTH = 1 
ORDER BY RAIN_C;

update all rows of table STATS to compensate for faulty rain gauges known to read 0.01 inches low:

UPDATE STATS SET RAIN_I = RAIN_I + 0.01;

and take a look:

SELECT * FROM STATS;

update one row, Denver's July temperature reading, to correct a data entry error:

UPDATE STATS SET TEMP_F = 74.9 
WHERE ID = 44 
AND MONTH = 7;

and take a look:

SELECT * FROM STATS;

make the above changes permanent: 
-- they were only temporary until now.

COMMIT WORK;

update two rows, Denver's rainfall readings:

UPDATE STATS SET RAIN_I = 4.50 
WHERE ID = 44;

and take a look:

SELECT * FROM STATS;

Oops! We meant to update just the July reading! Undo that update: 
-- undoes only updates since the last COMMIT WORK.

ROLLBACK WORK;

and take a look:

SELECT * FROM STATS;

now update Denver's July rainfall reading and make it permanent:

UPDATE STATS SET RAIN_I = 4.50 
WHERE ID = 44 
AND MONTH = 7;

COMMIT WORK;

and take a look:

SELECT * FROM STATS;

delete July data and East Coast data from both tables: 
-- note that we use longitude values from the related STATION table to determine which STAT stations were east of 90 degrees.

DELETE FROM STATS 
WHERE MONTH = 7 
OR ID IN (SELECT ID FROM STATION 
WHERE LONG_W < 90);

DELETE FROM STATION WHERE LONG_W < 90;

COMMIT WORK;

and take a look:

SELECT * FROM STATION;

SELECT * FROM STATS;

View METRIC_STATS, a Fahrenheit-to-Centigrade and inches-to-centimeters conversion of table STATS, reflects the updates made to the underlying table.

SELECT * FROM METRIC_STATS;

SQL enforces data integrity constraints.

Attempt to insert a row for an unknown observation station. 
-- The ID value of 33 does not match a station ID value in the STATION table. 
-- This is a violation of referential integrity.

INSERT INTO STATS VALUES (33,8,27.4,.19);

Attempt to update a row with a temperature below the range -80 TO 150.

UPDATE STATS SET TEMP_F = -100 WHERE ID = 44 AND MONTH = 1;

Attempt to insert a row with negative rainfall measurement, outside the range 0 to 100.

INSERT INTO STATS VALUES (44,8,27.4,-.03);

Attempt to insert a row with month 13, outside the range of 1 to 12.

INSERT INTO STATS VALUES (44,13,27.4,.19);

Attempt to insert a row with a temperature above the range -80 TO 150.

INSERT INTO STATS VALUES (44,8,160,.19);

Attempt to insert a row with no constraint violations.

INSERT INTO STATS VALUES (44,8,27.4,.10);

and take a look:

SELECT * FROM STATS;

Attempt to insert a second row of August statistics for station 44. 
-- This is a violation of the primary key constraint. 
-- Only one row for each station and month combination is allowed.

INSERT INTO STATS VALUES (44,8,160,.19);