ja2QuNrn

1DROP TABLE IF EXISTS student;
2CREATE TABLE student (
3    studentid SERIAL PRIMARY KEY,
4    firstname varchar(20) NOT NULL,
5    lastname varchar(20) NOT NULL
6);
7
8insert into student (firstname, lastname) values ('Netta', 'Klus');
9insert into student (firstname, lastname) values ('Hayes', 'Brodnecke');
10insert into student (firstname, lastname) values ('Lonna', 'Howbrook');
11insert into student (firstname, lastname) values ('Daveta', 'Syde');
12insert into student (firstname, lastname) values ('Hermon', 'Effnert');
13insert into student (firstname, lastname) values ('Krystalle', 'Guild');
14insert into student (firstname, lastname) values ('Valida', 'Henstone');
15insert into student (firstname, lastname) values ('Livy', 'Klemz');
16insert into student (firstname, lastname) values ('Temp', 'Sixsmith');
17insert into student (firstname, lastname) values ('Burk', 'Mellanby');
18insert into student (firstname, lastname) values ('Gaby', 'Lilywhite');
19insert into student (firstname, lastname) values ('Giffy', 'Binion');
20insert into student (firstname, lastname) values ('Madelena', 'Philson');
21insert into student (firstname, lastname) values ('Abbey', 'Spadazzi');
22insert into student (firstname, lastname) values ('Junie', 'Tyndall');
23insert into student (firstname, lastname) values ('Bentlee', 'Myott');
24insert into student (firstname, lastname) values ('Cherish', 'Palumbo');
25insert into student (firstname, lastname) values ('Kayla', 'Ruperti');
26insert into student (firstname, lastname) values ('Harmon', 'Darlasson');
27insert into student (firstname, lastname) values ('Joachim', 'Grewer');
28insert into student (firstname, lastname) values ('Blanca', 'Darcey');
29insert into student (firstname, lastname) values ('Keelia', 'Levay');
30insert into student (firstname, lastname) values ('Ahmad', 'Rault');
31insert into student (firstname, lastname) values ('Addie', 'Exeter');
32insert into student (firstname, lastname) values ('Karina', 'Sheehan');
33
34CREATE TYPE completion as ENUM ('z', 'k', 'zk');
35CREATE TYPE semester_type as ENUM ('spring', 'fall');
36
37DROP TABLE IF EXISTS enrollment;
38CREATE TABLE enrollment (
39    studentid integer REFERENCES student(studentid),
40    courseid varchar(5) NOT NULL,
41    semester semester_type,
42    year int NOT NULL,
43    typeofcompletion completion,
44    grade varchar(3) NULL
45);
46
47insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (1, 'IV001', 'spring', 2016, 'zk', 'FFF');
48insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (1, 'IV001', 'spring', 2017, 'zk', 'FC');
49insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (1, 'MA007', 'fall', 2016, 'zk', 'FB');
50insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (1, 'PA152', 'spring', 2016, 'zk', 'A');
51
52insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (2, 'IV001', 'spring', 2017, 'zk', 'D');
53insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (2, 'MA007', 'fall', 2016, 'z', 'Z');
54insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (2, 'PA152', 'spring', 2017, 'k', 'N'); -- *
55insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (2, 'PA152', 'spring', 2018, 'zk', null); -- **
56
57insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (3, 'IV001', 'spring', 2017, 'zk', 'FA');
58insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (3, 'MA007', 'fall', 2017, 'z', 'N');
59insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (3, 'PA152', 'spring', 2018, 'zk', null);
60
61insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (4, 'IV001', 'spring', 2018, 'k', null);
62insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (4, 'MA007', 'fall', 2017, 'zk', 'FF');
63insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (4, 'PA152', 'spring', 2016, 'z', 'N');
64insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (4, 'PA152', 'spring', 2017, 'k', 'P');
65
66insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (5, 'IV001', 'spring', 2018, 'zk', 'FD');
67insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (5, 'PA152', 'spring', 2018, 'zk', null);
68
69insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (6, 'IV001', 'spring', 2016, 'zk', 'FA');
70insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (6, 'MA007', 'fall', 2016, 'z', 'Z');
71insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (6, 'PA152', 'spring', 2017, 'k', 'N');
72
73insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (7, 'IV001', 'spring', 2016, 'zk', 'FA');
74insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (7, 'MA007', 'fall', 2016, 'z', 'Z');
75insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (7, 'PA152', 'spring', 2017, 'k', 'P');
76
77insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (8, 'PA152', 'spring', 2017, 'zk', 'FFF'); -- *
78insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (8, 'PA152', 'spring', 2018, 'zk', null); -- **
79
80insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (9, 'IV001', 'spring', 2016, 'zk', 'FA');
81insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (9, 'PA152', 'spring', 2018, 'z', null);
82
83insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (10, 'PA152', 'spring', 2016, 'zk', 'FF');
84insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (10, 'PA152', 'spring', 2018, 'zk', null);
85
86insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (11, 'PA152', 'spring', 2016, 'zk', 'F');
87insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (11, 'PA152', 'spring', 2017, 'z', 'Z');
88
89insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (12, 'PA152', 'spring', 2017, 'zk', 'FFF');
90
91insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (13, 'IV001', 'spring', 2017, 'zk', 'FFF');
92insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (13, 'PA152', 'spring', 2017, 'k', 'N');
93
94insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (6, 'MA007', 'fall', 2016, 'z', 'N');
95insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (6, 'MA007', 'fall', 2017, 'k', 'P');
96insert into enrollment (studentid, courseid, semester, year, typeofcompletion, grade) values (14, 'PA152', 'spring', 2017, 'k', 'P');