· 7 years ago · Dec 06, 2018, 02:24 PM
1DROP TABLE IF EXISTS Lab9_Stations,Lab9_Pollutant,Lab9_Monitors;
2
3CREATE TABLE Lab9_Stations(
4EIONetCode VARCHAR NOT NULL,
5 Station TEXT NOT NULL,
6 Status TEXT NOT NULL,
7 CONSTRAINT Lab9_Stations_PKEY PRIMARY KEY (EIONetCode)
8 );
9 insert into Lab9_Stations (EIONetCode, Station, Status) values
10 ('IE0145A','Mayo Claremorris','Rural-Remote');
11 insert into Lab9_Stations (EIONetCode, Station, Status) values
12 ('IE0028A','Dublin Rathmines','Urban');
13 insert into Lab9_Stations (EIONetCode, Station, Status) values
14 ('IE0125A','Galway City','Suburban');
15 insert into Lab9_Stations (EIONetCode, Station, Status) values
16 ('IE0111A','Laois Emo Court','Rural-regional');
17 insert into Lab9_Stations (EIONetCode, Station, Status) values
18 ('IE0090A','Monaghan Kilkitt','Rural-regional');
19 insert into Lab9_Stations (EIONetCode, Station, Status) values
20 ('IE0147A','Kilkenny Seville Lodge','Suburban');
21
22
23
24 CREATE TABLE Lab9_Pollutant(
25 EEAPol TEXT NOT NULL,
26 Pollutant TEXT NOT NULL,
27 Notation TEXT NOT NULL,
28 CONSTRAINT Lab9_Pollutant_PKEY PRIMARY KEY (EEAPol)
29 );
30
31 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
32 ('1','Sulphur dioxide (air)','SO2');
33 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
34 ('10','Carbon monoxide (air)','CO');
35 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
36 ('7','Ozone','03 (air)');
37 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
38 ('8','Nitrogen dioxide (air)','NO2');
39 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
40 ('5','Particulate matter<10 (aersol)','PM10');
41 insert into Lab9_Pollutant (EEAPol,Pollutant, Notation) values
42 ('6001','Particulate matter<2.5 (aersol)','PM2.5');
43
44
45 CREATE TABLE Lab9_Monitors (
46 EIONetCode VARCHAR NOT NULL References Lab9_Stations (EIONetCode) ON UPDATE CASCADE ON DELETE CASCADE,
47 EEAPol VARCHAR NOT NULL References Lab9_Pollutant(EEAPol) ON UPDATE CASCADE ON DELETE CASCADE,
48 CONSTRAINT Lab9_Monitors_PK PRIMARY KEY (EIONetCode,EEAPol)
49 );
50
51 --RATHMINES
52 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A','1');
53 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A','10');
54 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A','7');
55 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A','8');
56 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A','5');
57 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0028A','6001');
58
59
60 --Mayo Claremorris
61 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0145A','7');
62 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0145A','8');
63 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0145A','5');
64 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0145A','6001');
65
66
67
68 --Laois Emo Court
69 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0111A','7');
70 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0111A','8');
71 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0111A','5');
72 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0111A','6001');
73
74 --Monaghan Kilkitt
75 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0090A','7');
76 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0090A','8');
77 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0090A','5');
78 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0090A','6001');
79
80 --Galway City
81 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0125A','1');
82 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0125A','8');
83 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0125A','5');
84 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0125A','6001');
85
86
87 --Kilkenny Seville Lodge
88 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0147A','1');
89 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0147A','8');
90 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0147A','5');
91 insert into Lab9_Monitors(EIONetCode,EEAPol) values('IE0147A','6001');
92
93 ALTER TABLE lab9_Monitors
94 ADD frequency TEXT;
95
96 UPDATE Lab9_Monitors
97 SET Frequency= 'Daily'
98 WHERE EEAPol~*'^5$|^6001$';
99
100 UPDATE Lab9_Monitors
101 SET Frequency= 'Hourly'
102 WHERE EEAPol~*'^1$|^10$|^7$|^8$';
103
104
105
106
107 SELECT Lab9_Stations.*, Lab9_Pollutant.*,lab9_Monitors.Frequency
108FROM Lab9_Monitors
109JOIN Lab9_Stations on Lab9_Stations.EIONetCode=Lab9_Monitors.EIONetCode
110JOIN Lab9_Pollutant on Lab9_Pollutant.EEAPol=Lab9_MOnitors.EEAPol
111ORDER BY eeapol ASC