forked from anthonydb/practical-sql
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathChapter_15.sql
More file actions
321 lines (253 loc) · 8.24 KB
/
Chapter_15.sql
File metadata and controls
321 lines (253 loc) · 8.24 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
--------------------------------------------------------------
-- Practical SQL: A Beginner's Guide to Storytelling with Data
-- by Anthony DeBarros
-- Chapter 15 Code Examples
--------------------------------------------------------------
-- VIEWS
-- Listing 15-1: Creating a view that displays Nevada 2010 counties
CREATE OR REPLACE VIEW nevada_counties_pop_2010 AS
SELECT geo_name,
state_fips,
county_fips,
p0010001 AS pop_2010
FROM us_counties_2010
WHERE state_us_abbreviation = 'NV'
ORDER BY county_fips;
-- Listing 15-2: Querying the nevada_counties_pop_2010 view
SELECT *
FROM nevada_counties_pop_2010
LIMIT 5;
-- Listing 15-3: Creating a view showing population change for US counties
CREATE OR REPLACE VIEW county_pop_change_2010_2000 AS
SELECT c2010.geo_name,
c2010.state_us_abbreviation AS st,
c2010.state_fips,
c2010.county_fips,
c2010.p0010001 AS pop_2010,
c2000.p0010001 AS pop_2000,
round( (CAST(c2010.p0010001 AS numeric(8,1)) - c2000.p0010001)
/ c2000.p0010001 * 100, 1 ) AS pct_change_2010_2000
FROM us_counties_2010 c2010 INNER JOIN us_counties_2000 c2000
ON c2010.state_fips = c2000.state_fips
AND c2010.county_fips = c2000.county_fips
ORDER BY c2010.state_fips, c2010.county_fips;
-- Listing 15-4: Selecting columns from the county_pop_change_2010_2000 view
SELECT geo_name,
st,
pop_2010,
pct_change_2010_2000
FROM county_pop_change_2010_2000
WHERE st = 'NV'
LIMIT 5;
-- Listing 15-5: Creating a view on the employees table
CREATE OR REPLACE VIEW employees_tax_dept AS
SELECT emp_id,
first_name,
last_name,
dept_id
FROM employees
WHERE dept_id = 1
ORDER BY emp_id
WITH LOCAL CHECK OPTION;
SELECT * FROM employees_tax_dept;
-- Listing 15-6: Successful and rejected inserts via the employees_tax_dept view
INSERT INTO employees_tax_dept (first_name, last_name, dept_id)
VALUES ('Suzanne', 'Legere', 1);
INSERT INTO employees_tax_dept (first_name, last_name, dept_id)
VALUES ('Jamil', 'White', 2);
-- optional:
SELECT * FROM employees_tax_dept;
SELECT * FROM employees;
-- Listing 15-7: Updating a row via the employees_tax_dept view
UPDATE employees_tax_dept
SET last_name = 'Le Gere'
WHERE emp_id = 5;
SELECT * FROM employees_tax_dept;
-- Bonus: This will fail because the salary column is not in the view
UPDATE employees_tax_dept
SET salary = 100000
WHERE emp_id = 5;
-- Listing 15-8: Deleting a row via the employees_tax_dept view
DELETE FROM employees_tax_dept
WHERE emp_id = 5;
-- FUNCTIONS
-- https://www.postgresql.org/docs/current/static/plpgsql.html
-- Listing 15-9: Creating a percent_change function
-- To delete this function: DROP FUNCTION percent_change(numeric,numeric,integer);
CREATE OR REPLACE FUNCTION
percent_change(new_value numeric,
old_value numeric,
decimal_places integer DEFAULT 1)
RETURNS numeric AS
'SELECT round(
((new_value - old_value) / old_value) * 100, decimal_places
);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
-- Listing 15-10: Testing the percent_change() function
SELECT percent_change(110, 108, 2);
-- Listing 15-11: Testing percent_change() on Census data
SELECT c2010.geo_name,
c2010.state_us_abbreviation AS st,
c2010.p0010001 AS pop_2010,
percent_change(c2010.p0010001, c2000.p0010001) AS pct_chg_func,
round( (CAST(c2010.p0010001 AS numeric(8,1)) - c2000.p0010001)
/ c2000.p0010001 * 100, 1 ) AS pct_chg_formula
FROM us_counties_2010 c2010 INNER JOIN us_counties_2000 c2000
ON c2010.state_fips = c2000.state_fips
AND c2010.county_fips = c2000.county_fips
ORDER BY pct_chg_func DESC
LIMIT 5;
-- Listing 15-12: Adding a column to the teachers table and seeing the data
ALTER TABLE teachers ADD COLUMN personal_days integer;
SELECT first_name,
last_name,
hire_date,
personal_days
FROM teachers;
-- Listing 15-13: Creating an update_personal_days() function
CREATE OR REPLACE FUNCTION update_personal_days()
RETURNS void AS $$
BEGIN
UPDATE teachers
SET personal_days =
CASE WHEN (now() - hire_date) BETWEEN '5 years'::interval
AND '10 years'::interval THEN 4
WHEN (now() - hire_date) > '10 years'::interval THEN 5
ELSE 3
END;
RAISE NOTICE 'personal_days updated!';
END;
$$ LANGUAGE plpgsql;
-- To run the function:
SELECT update_personal_days();
-- Listing 15-14: Enabling the PL/Python procedural language
CREATE EXTENSION plpythonu;
-- Listing 15-15: Using PL/Python to create the trim_county() function
CREATE OR REPLACE FUNCTION trim_county(input_string text)
RETURNS text AS $$
import re
cleaned = re.sub(r' County', '', input_string)
return cleaned
$$ LANGUAGE plpythonu;
-- Listing 15-16: Testing the trim_county() function
SELECT geo_name,
trim_county(geo_name)
FROM us_counties_2010
ORDER BY state_fips, county_fips
LIMIT 5;
-- TRIGGERS
-- Listing 15-17: Creating the grades and grades_history tables
CREATE TABLE grades (
student_id bigint,
course_id bigint,
course varchar(30) NOT NULL,
grade varchar(5) NOT NULL,
PRIMARY KEY (student_id, course_id)
);
INSERT INTO grades
VALUES
(1, 1, 'Biology 2', 'F'),
(1, 2, 'English 11B', 'D'),
(1, 3, 'World History 11B', 'C'),
(1, 4, 'Trig 2', 'B');
CREATE TABLE grades_history (
student_id bigint NOT NULL,
course_id bigint NOT NULL,
change_time timestamp with time zone NOT NULL,
course varchar(30) NOT NULL,
old_grade varchar(5) NOT NULL,
new_grade varchar(5) NOT NULL,
PRIMARY KEY (student_id, course_id, change_time)
);
-- Listing 15-18: Creating the record_if_grade_changed() function
CREATE OR REPLACE FUNCTION record_if_grade_changed()
RETURNS trigger AS
$$
BEGIN
IF NEW.grade <> OLD.grade THEN
INSERT INTO grades_history (
student_id,
course_id,
change_time,
course,
old_grade,
new_grade)
VALUES
(OLD.student_id,
OLD.course_id,
now(),
OLD.course,
OLD.grade,
NEW.grade);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Listing 15-19: Creating the grades_update trigger
CREATE TRIGGER grades_update
AFTER UPDATE
ON grades
FOR EACH ROW
EXECUTE PROCEDURE record_if_grade_changed();
-- Listing 15-20: Testing the grades_update trigger
-- Initially, there should be 0 records in the history
SELECT * FROM grades_history;
-- Check the grades
SELECT * FROM grades;
-- Update a grade
UPDATE grades
SET grade = 'C'
WHERE student_id = 1 AND course_id = 1;
-- Now check the history
SELECT student_id,
change_time,
course,
old_grade,
new_grade
FROM grades_history;
-- Listing 15-21: Creating a temperature_test table
CREATE TABLE temperature_test (
station_name varchar(50),
observation_date date,
max_temp integer,
min_temp integer,
max_temp_group varchar(40),
PRIMARY KEY (station_name, observation_date)
);
-- Listing 15-22: Creating the classify_max_temp() function
CREATE OR REPLACE FUNCTION classify_max_temp()
RETURNS trigger AS
$$
BEGIN
CASE
WHEN NEW.max_temp >= 90 THEN
NEW.max_temp_group := 'Hot';
WHEN NEW.max_temp BETWEEN 70 AND 89 THEN
NEW.max_temp_group := 'Warm';
WHEN NEW.max_temp BETWEEN 50 AND 69 THEN
NEW.max_temp_group := 'Pleasant';
WHEN NEW.max_temp BETWEEN 33 AND 49 THEN
NEW.max_temp_group := 'Cold';
WHEN NEW.max_temp BETWEEN 20 AND 32 THEN
NEW.max_temp_group := 'Freezing';
ELSE NEW.max_temp_group := 'Inhumane';
END CASE;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Listing 15-23: Creating the temperature_insert trigger
CREATE TRIGGER temperature_insert
BEFORE INSERT
ON temperature_test
FOR EACH ROW
EXECUTE PROCEDURE classify_max_temp();
-- Listing 15-24: Inserting rows to test the temperature_update trigger
INSERT INTO temperature_test (station_name, observation_date, max_temp, min_temp)
VALUES
('North Station', '1/19/2019', 10, -3),
('North Station', '3/20/2019', 28, 19),
('North Station', '5/2/2019', 65, 42),
('North Station', '8/9/2019', 93, 74);
SELECT * FROM temperature_test;