-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
57 lines (42 loc) · 2.28 KB
/
schema.sql
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
drop database bugmuncher;
CREATE DATABASE bugmuncher;
\c bugmuncher;
CREATE TABLE defects (
defect_id SERIAL PRIMARY KEY,
defect_title TEXT,
description TEXT,
status TEXT
);
CREATE TABLE engineers (
eng_id SERIAL PRIMARY KEY,
name TEXT,
email TEXT
);
INSERT INTO defects (defect_title, description, status) VALUES ('Big Bug1', 'A big defect', 'Open');
INSERT INTO defects (defect_title, description, status) VALUES ('Big Bug2', 'A big defect', 'Open');
INSERT INTO defects (defect_title, description, status) VALUES ('Big Bug3', 'A big defect', 'Open');
INSERT INTO defects (defect_title, description, status) VALUES ('Big Bug4', 'A big defect', 'Open');
INSERT INTO defects (defect_title, description, status) VALUES ('A massive Bug', 'A really massive defect', 'In-progress');
INSERT INTO defects (defect_title, description, status) VALUES ('Teeny Weeny Bug', 'This is a tiny bug. Fixed in 2 mins', 'Closed');
INSERT INTO defects (defect_title, description, status) VALUES ('Is this bug or spider?', 'Not sure if a bug or something else', 'New');
INSERT INTO engineers (name, email) VALUES ('theHomelessHacker', '[email protected]');
INSERT INTO engineers (name, email) VALUES ('codeWarrior', '[email protected]');
INSERT INTO engineers (name, email) VALUES ('devOpsWizard', '[email protected]');
SELECT status, COUNT (status) from defects group by status;
SELECT status, COUNT (status) from defects group by status UNION ALL select 'Total' Status, COUNT(status) from defects;
ALTER TABLE defects ADD COLUMN eng_id INTEGER;
ALTER TABLE engineers ADD COLUMN password_digest TEXT;
ALTER TABLE engineers ADD COLUMN role TEXT;
drop table reports;
CREATE TABLE reports (
report_id SERIAL PRIMARY KEY,
week TEXT,
points_target INTEGER,
ideal_points INTEGER,
actual_points INTEGER
);
INSERT INTO reports (week, points_target, ideal_points, actual_points) VALUES ('Week1', 400, 45, 40);
INSERT INTO reports (week, points_target, ideal_points, actual_points) VALUES ('Week2', 400, 85, 75);
INSERT INTO reports (week, points_target, ideal_points, actual_points) VALUES ('Week3', 300, 112, 82);
INSERT INTO reports (week, points_target, ideal_points, actual_points) VALUES ('Week4', 300, 146, 130);
INSERT INTO reports (week, points_target, ideal_points, actual_points) VALUES ('Week5', 250, 213, 240);