Accessing structured data with SQL is quite different from the full text search of documents on the Web. Structured data in the relational model means data that can be represented in tables -- rows and columns. Each row in a table represents a different object, and the columns represent various "attributes" of the object. The columns have names and integrity constraints that specify valid values.
Since the column values are named and are represented in a consistent format, you can select rows very precisely, based on their contents. This is especially helpful in dealing with numeric data. You can also join together data from different tables, based on matching column values. You can do useful types of analysis, listing objects that are in one table and missing (or present, or have specific attributes) from a related table. You can extract from a large table precisely those rows of interest, regrouping them and generating simple statistics on them.
This document contains examples of:
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;
ID | CITY | STATE | LAT_N | LONG_W |
---|---|---|---|---|
13 | Phoenix | AZ | 33 | 112 |
44 | Denver | CO | 40 | 105 |
66 | Caribou | ME | 47 | 68 |
SELECT * FROM STATION
WHERE LAT_N > 39.7;
ID | CITY | STATE | LAT_N | LONG_W |
---|---|---|---|---|
44 | Denver | CO | 40 | 105 |
66 | Caribou | ME | 47 | 68 |
SELECT ID, CITY, STATE FROM STATION;
ID | CITY | STATE |
---|---|---|
13 | Phoenix | AZ |
44 | Denver | CO |
66 | Caribou | ME |
SELECT ID, CITY, STATE FROM STATION
WHERE LAT_N > 39.7;
ID | CITY | STATE |
---|---|---|
44 | Denver | CO |
66 | Caribou | ME |
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;
ID | MONTH | TEMP_F | RAIN_I |
---|---|---|---|
13 | 1 | 57.4 | .31 |
13 | 7 | 91.7 | 5.15 |
44 | 1 | 27.3 | .18 |
44 | 7 | 74.8 | 2.11 |
66 | 1 | 6.7 | 2.1 |
66 | 7 | 65.8 | 4.52 |
SELECT * FROM STATION, STATS
WHERE STATION.ID = STATS.ID;
ID | CITY | ST | LAT_N | LONG_W | ID | MONTH | TEMP_F | RAIN_I |
---|---|---|---|---|---|---|---|---|
13 | Phoenix | AZ | 33 | 112 | 13 | 1 | 57.4 | .31 |
13 | Phoenix | AZ | 33 | 112 | 13 | 7 | 91.7 | 5.15 |
44 | Denver | CO | 40 | 105 | 44 | 1 | 27.3 | .18 |
44 | Denver | CO | 40 | 105 | 44 | 7 | 74.8 | 2.11 |
66 | Caribou | ME | 47 | 68 | 66 | 1 | 6.7 | 2.1 |
66 | Caribou | ME | 47 | 68 | 66 | 7 | 65.8 | 4.52 |
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;
MONTH | ID | RAIN_I | TEMP_F |
---|---|---|---|
1 | 66 | 2.1 | 6.7 |
1 | 13 | .31 | 57.4 |
1 | 44 | .18 | 27.3 |
7 | 13 | 5.15 | 91.7 |
7 | 66 | 4.52 | 65.8 |
7 | 44 | 2.11 | 74.8 |
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;
LAT_N | CITY | TEMP_F |
---|---|---|
47 | Caribou | 65.8 |
40 | Denver | 74.8 |
33 | Phoenix | 91.7 |
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;
MAX(TEMP_F) | MIN(TEMP_F) | AVG(RAIN_I) | ID |
---|---|---|---|
91.7 | 57.4 | 2.73 | 13 |
74.8 | 27.3 | 1.145 | 44 |
65.8 | 6.7 | 3.31 | 66 |
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);
ID | CITY | ST | LAT_N | LONG_W |
---|---|---|---|---|
13 | Phoenix | AZ | 33 | 112 |
44 | Denver | CO | 40 | 105 |
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;
ID | MONTH | TEMP_C | RAIN_C |
---|---|---|---|
13 | 1 | 14.1111111 | .122047 |
13 | 7 | 33.1666667 | 2.027555 |
44 | 1 | -2.6111111 | .070866 |
44 | 7 | 23.7777778 | .830707 |
66 | 1 | -14.055556 | .82677 |
66 | 7 | 18.7777778 | 1.779524 |
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;
ID | MONTH | TEMP_C | RAIN_C |
---|---|---|---|
44 | 1 | -2.6111111 | .070866 |
66 | 1 | -14.055556 | .82677 |
UPDATE STATS SET RAIN_I = RAIN_I + 0.01;
and take a look:
SELECT * FROM STATS;
ID | MONTH | TEMP_F | RAIN_I |
---|---|---|---|
13 | 1 | 57.4 | .32 |
13 | 7 | 91.7 | 5.16 |
44 | 1 | 27.3 | .19 |
44 | 7 | 74.8 | 2.12 |
66 | 1 | 6.7 | 2.11 |
66 | 7 | 65.8 | 4.53 |
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;
ID | MONTH | TEMP_F | RAIN_I |
---|---|---|---|
13 | 1 | 57.4 | .32 |
13 | 7 | 91.7 | 5.16 |
44 | 1 | 27.3 | .19 |
44 | 7 | 74.9 | 2.12 |
66 | 1 | 6.7 | 2.11 |
66 | 7 | 65.8 | 4.53 |
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;
ID | MONTH | TEMP_F | RAIN_I |
---|---|---|---|
13 | 1 | 57.4 | .32 |
13 | 7 | 91.7 | 5.16 |
44 | 1 | 27.3 | 4.5 |
44 | 7 | 74.9 | 4.5 |
66 | 1 | 6.7 | 2.11 |
66 | 7 | 65.8 | 4.53 |
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;
ID | MONTH | TEMP_F | RAIN_I |
---|---|---|---|
13 | 1 | 57.4 | .32 |
13 | 7 | 91.7 | 5.16 |
44 | 1 | 27.3 | .19 |
44 | 7 | 74.9 | 2.12 |
66 | 1 | 6.7 | 2.11 |
66 | 7 | 65.8 | 4.53 |
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;
ID | MONTH | TEMP_F | RAIN_I |
---|---|---|---|
13 | 1 | 57.4 | .32 |
13 | 7 | 91.7 | 5.16 |
44 | 1 | 27.3 | .19 |
44 | 7 | 74.9 | 4.5 |
66 | 1 | 6.7 | 2.11 |
66 | 7 | 65.8 | 4.53 |
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;
ID | CITY | ST | LAT_N | LONG_W |
---|---|---|---|---|
13 | Phoenix | AZ | 33 | 112 |
44 | Denver | CO | 40 | 105 |
ID | MONTH | TEMP_F | RAIN_I |
---|---|---|---|
13 | 1 | 57.4 | .32 |
44 | 1 | 27.3 | .19 |
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;
ID | MONTH | TEMP_C | RAIN_C |
---|---|---|---|
13 | 1 | 14.1111111 | .125984 |
44 | 1 | -2.6111111 | .074803 |
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);
error message |
---|
violation of constraint STATS_FOREIGN1 caused operation to fail |
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;
error message |
---|
violation of constraint STATS_CHECK2 caused operation to fail |
Attempt to insert a row with negative rainfall measurement, outside the range 0 to 100.
INSERT INTO STATS VALUES (44,8,27.4,-.03);
error message |
---|
violation of constraint STATS_CHECK3 caused operation to fail |
Attempt to insert a row with month 13, outside the range of 1 to 12.
INSERT INTO STATS VALUES (44,13,27.4,.19);
error message |
---|
violation of constraint STATS_CHECK1 caused operation to fail |
Attempt to insert a row with a temperature above the range -80 TO 150.
INSERT INTO STATS VALUES (44,8,160,.19);
error message |
---|
violation of constraint STATS_CHECK2 caused operation to fail |
Attempt to insert a row with no constraint violations.
INSERT INTO STATS VALUES (44,8,27.4,.10);
status message |
---|
1 row inserted |
and take a look:
SELECT * FROM STATS;
ID | MONTH | TEMP_F | RAIN_I |
---|---|---|---|
44 | 8 | 27.4 | .10 |
13 | 1 | 57.4 | .32 |
44 | 1 | 27.3 | .19 |
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);
error message |
---|
violation of constraint STATS_PRIMARY_ID_MONTH caused operation to fail |
#include<stdio.h>
long mon; float temp; float rain; char city_name[21]; long SQLCODE; main() { /* the CONNECT statement, if needed, goes here */ strcpy(city_name,"Denver"); EXEC SQL SELECT ID INTO :station_id
WHERE CITY = :city_name;
printf("There is no station for city %s\n",city_name); exit(0); } printf("And here is the weather data:\n"); EXEC SQL DECLARE XYZ CURSOR FOR
FROM STATS WHERE ID = :station_id ORDER BY MONTH; while (SQLCODE != 100) {
if (SQLCODE == 100)
exit(0); } |
For the city Denver, Station ID is 44
|