· 9 years ago · Dec 28, 2016, 03:38 PM
1DROP TABLE IF EXISTS students CASCADE;
2CREATE TABLE students(
3 students_id serial UNIQUE PRIMARY KEY,
4 name character varying NOT NULL,
5 surname character varying NOT NULL,
6 bsn character varying NOT NULL,
7 enrollment_year smallint NOT NULL CHECK (enrollment_year >= 1970)
8
9);
10
11DROP TABLE IF EXISTS teachers CASCADE;
12CREATE TABLE teachers(
13 bsn character varying UNIQUE PRIMARY KEY,
14 name character varying NOT NULL,
15 surname character varying NOT NULL,
16 salary real NOT NULL,
17 scale smallint NOT NULL CHECK (scale >= 20 AND scale <= 35)
18);
19
20DROP TABLE IF EXISTS classes;
21CREATE TABLE classes(
22 classes_id serial UNIQUE PRIMARY KEY,
23 teacher_bsn character varying NOT NULL REFERENCES teachers (bsn),
24 students_id integer NOT NULL REFERENCES students (students_id)
25);
26
27DROP TABLE IF EXISTS courses CASCADE;
28CREATE TABLE courses(
29 code character varying NOT NULL UNIQUE PRIMARY KEY,
30 name character varying NOT NULL,
31 study_pts smallint NOT NULL CHECK (study_pts >= 1)
32);
33
34DROP TABLE IF EXISTS courses_teachers;
35CREATE TABLE courses_teachers(
36 courses_teachers_id serial UNIQUE PRIMARY KEY,
37 courses_code character varying REFERENCES courses (code),
38 teacher_bsn character varying REFERENCES teachers (bsn)
39);
40
41DROP TABLE IF EXISTS assignments CASCADE;
42CREATE TABLE assignments(
43 code character varying NOT NULL UNIQUE PRIMARY KEY,
44 week smallint NOT NULL CHECK (week >= 1 AND week <= 52),
45 designer character varying NOT NULL REFERENCES teachers (bsn)
46);
47
48DROP TABLE IF EXISTS assignments_progress;
49CREATE TABLE assignments_progress(
50 assignments_progress_id serial UNIQUE PRIMARY KEY,
51 assignment_code character varying NOT NULL REFERENCES assignments (code),
52 student_id integer NOT NULL REFERENCES students (students_id),
53 completed boolean DEFAULT false
54);
55
56DROP TABLE IF EXISTS required_assignments;
57CREATE TABLE required_assignments(
58 required_assignments_id serial UNIQUE PRIMARY KEY,
59 assignment_code character varying REFERENCES assignments (code)
60);
61
62DROP TABLE IF EXISTS assignments_reviews;
63CREATE TABLE assignments_reviews(
64 assignments_reviews_id serial UNIQUE PRIMARY KEY,
65 assignment_id character varying REFERENCES assignments (code),
66 teacher_bsn character varying REFERENCES teachers (bsn)
67);
68
69DROP TYPE IF EXISTS program_level CASCADE;
70CREATE TYPE program_level AS ENUM('bachelor', 'master');
71
72DROP TABLE IF EXISTS study_programs CASCADE;
73CREATE TABLE study_programs(
74 name character varying NOT NULL UNIQUE PRIMARY KEY,
75 duration smallint CHECK (duration >= 1)
76);
77
78DROP TABLE IF EXISTS completed_study_programs;
79CREATE TABLE completed_study_programs(
80 completed_study_programs_id serial UNIQUE PRIMARY KEY,
81 study_programs_id character varying REFERENCES study_programs (name),
82 student_id integer REFERENCES students (students_id)
83);
84
85DROP TABLE IF EXISTS courses_students;
86CREATE TABLE courses_students(
87 courses_students_id serial UNIQUE PRIMARY KEY,
88 course_code character varying REFERENCES courses (code),
89 students_id serial REFERENCES students (students_id)
90);
91
92
93
94
95INSERT INTO students (student_id, name, surname, bsn, enrollment_year) VALUES (123, 'Sam', '...', '09239', 1994);
96INSERT INTO students (student_id, name, surname, bsn, enrollment_year) VALUES (456, 'Bart', '...', '09234', 1998);
97INSERT INTO study_programs (name, duration) VALUES ('Informatica', 4);
98INSERT INTO teachers (bsn, name, surname, salary, scale) VALUES ('019234', 'Teacher', '#1', 2000, 21);
99INSERT INTO teachers (bsn, name, surname, salary, scale) VALUES ('019235', 'Teacher', '#2', 2000, 21);
100INSERT INTO assignments (code, week, designer) VALUES ('DEV23', 48, '019234');
101INSERT INTO assignments (code, week, designer) VALUES ('DEV24', 44, '019235');
102INSERT INTO assignments (code, week, designer) VALUES ('DEV25', 49, '019235');
103INSERT INTO assignments_progress (assignment_code, student_id, completed) VALUES ('DEV23', 1, false);
104INSERT INTO assignments_progress (assignment_code, student_id, completed) VALUES ('DEV24', 1, true);
105INSERT INTO assignments_progress (assignment_code, student_id, completed) VALUES ('DEV25', 1, false);
106INSERT INTO assignments_progress (assignment_code, student_id, completed) VALUES ('DEV23', 2, true);
107INSERT INTO completed_study_programs (study_programs_id, student_id) VALUES ('Informatica', 1);
108INSERT INTO courses (code, name, study_pts) VALUES ('INFDEV', 'Development', 4);
109INSERT INTO courses (code, name, study_pts) VALUES ('INFANL', 'Analysis', 4);
110INSERT INTO courses_teachers (courses_teachers_id, courses_code, teacher_bsn) VALUES ('9876', 'INFDEV', '019234');
111INSERT INTO courses_teachers (courses_teachers_id, courses_code, teacher_bsn) VALUES ('5432', 'INFANL', 019235');
112INSERT INTO courses_students (courses_students_id, course_code, students_id) VALUES (20110, 'INFDEV', '123');
113INSERT INTO courses_students (courses_students_id, courses_code, students_id) VALUES (20111, 'INFANL', '456');
114
115-- Give a list of students that have not completed a study program
116SELECT s.students_id, s.name, s.surname
117FROM students AS s
118INNER JOIN completed_study_programs AS csp
119ON s.students_id <> csp.student_id;
120
121-- Give a list of teachers that are teaching courses, but do not work on assignments.
122SELECT t.bsn, t.name, t.surname
123FROM teachers AS t
124INNER JOIN assignments_reviews AS ar
125ON ar.teacher_bsn <> t.bsn;
126
127-- Give a list of students that have completed an assignment.
128SELECT s.students_id, s.name, s.surname
129FROM assignments_progress AS ap
130INNER JOIN students AS s
131ON ap.student_id = s.students_id
132WHERE ap.completed = true;
133
134-- Give a list of teachers that are teaching less than two classes or teachers that are teaching more than average.
135SELECT t.bsn, t.name
136FROM teachers AS t, courses_teachers AS ct
137GROUP BY t.bsn
138HAVING COUNT(*) < 2;
139
140-- Give a list of students per course per enrollment year.
141SELECT *
142FROM students AS s, courses AS c
143GROUP BY c.code, s.students_id, s.enrollment_year;
144
145
146
147-- TODO Find the number of designed assignment per teacher. Output the bsn and the number of the assignments.
148
149SELECT designer, COUNT(code)
150FROM assignments
151GROUP BY designer;
152
153
154-- TODO Find the number of students per course taught by each teacher.
155
156SELECT teacher_bsn, courses_code, COUNT(cs.students_id)
157FROM courses_students AS cs INNER JOIN courses_teachers as ct
158ON cs.course_code = ct.courses_code
159GROUP BY teacher_bsn, courses_code;
160
161
162-- TODO Give a list of courses plus number of students per teacher
163SELECT course_code, teacher_bsn, COUNT(students_id)
164FROM courses_teachers AS ct INNER JOIN courses_students AS cs
165ON ct.courses_code = cs.course_code
166GROUP BY course_code, teacher_bsn