SQL Program 1
[Link] 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);
[Link] 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);
[Link] to look at table STATION in undefined
order:
SELECT * FROM STATION;
Output:-
[Link] to select Northern stations (Northern
latitude > 39.7):
-- selecting only certain rows is called a
"restriction".
SELECT * FROM STATION
WHERE LAT_N > 39.7;
Output:-
[Link] to select only ID, CITY, and STATE
columns:
-- selecting only certain columns is called a
"projection".
SELECT ID, CITY, STATE FROM STATION;
Output:-
[Link] to both "restrict" and "project":
SELECT ID, CITY, STATE FROM STATION
WHERE LAT_N > 39.7;
Output:-
SQL Program 2 :-
[Link] 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));
[Link] 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);
[Link] to look at table STATS in undefined
order:
SELECT * FROM STATS;
Output:-
[Link] 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 [Link] = [Link];
Output:-
[Link] 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;
Output:-
[Link] 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 [Link] = [Link]
ORDER BY TEMP_F;
Output:-
[Link] 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;
Output:-
[Link] (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 [Link] = [Link]);
Output:-
[Link] 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;
10. Query to look at table STATS in a metric
light (through the new view):
SELECT * FROM METRIC_STATS;
Output:-
11. 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;
Output:-