· 8 years ago · May 03, 2018, 01:40 PM
1DROP TABLE IF EXISTS Student;
2DROP TABLE IF EXISTS Exam;
3
4CREATE TABLE Student(
5 studentID TEXT,
6 name TEXT,
7 birthday TEXT,
8 program TEXT CHECK (program IN ('AUT','IT','BIO','TIK','EST','KON','TUTA','CHEM','BIZ','ARTS')),
9 startDate TEXT,
10 endDate TEXT,
11 PRIMARY KEY (studentID)
12);
13
14
15CREATE TABLE Exam (
16 date TEXT,
17 courseCode TEXT,
18 startTime TEXT,
19 endTime TEXT,
20 role INT CHECK (role >= 1 AND role <=3),
21 PRIMARY KEY (date, courseCode),
22 FOREIGN KEY (courseCode) REFERENCES Course(courseCode)
23);
24
25
26CREATE TABLE ExerciseGroup (
27 courseCode TEXT,
28 groupNo INT CHECK (groupNo >= 1 AND groupNo <= 6),
29 startDate TEXT,
30 maxParticipants INT NOT NULL,
31 PRIMARY KEY (courseCode, groupNo, startDate),
32 FOREIGN KEY (courseCode, startDate) REFERENCES Course(courseCode, startDate)
33);
34
35
36
37
38INSERT INTO Student
39VALUES (300000, 'Teemu Teekkari', '01-01-1990', 'AUT', '01-09-2010', '30-08-2020');
40
41INSERT INTO Student
42VALUES (300001, 'Tiina Teekkari', '01-01-1993', 'EST', '01-09-2013', '30-08-2023');
43
44INSERT INTO Student
45VALUES (300002, 'Timi Terkkari', '08-08-1992', 'TUTA', '01-09-2014', '30-08-2021');
46
47INSERT INTO Student
48VALUES (302000, 'Kaija Terkkari', '01-01-1995', 'EST', '10-10-2015', '17-08-2024');
49
50INSERT INTO Student
51VALUES (432075, 'Kalle Anka', '12-12-1998', 'BIO', '15-08-2017', '27-05-2026');
52
53INSERT INTO Student
54VALUES (456345, 'Paul Anka', '01-01-1997', 'TIK', '01-09-2017', '30-08-2024');
55
56INSERT INTO Student
57VALUES (400000, 'Teemu Teekkari', '01-01-1990', 'EST', '01-09-2010', '30-08-2020');
58
59INSERT INTO Student
60VALUES (500000, 'Teemu Teekkari', '01-01-1990', 'BIO', '01-09-2010', '30-08-2020');
61
62INSERT INTO Student
63VALUES (600000, 'Teemu Teekkari', '01-01-1990', 'TIK', '01-09-2010', '30-08-2020');
64
65INSERT INTO Student
66VALUES (700000, 'Teemu Teekkari', '01-01-1990', 'TUTA', '01-09-2010', '30-08-2020');
67
68INSERT INTO Student
69VALUES (400001, 'Teemu Teekkari', '01-01-1990', 'KON', '01-09-2010', '30-08-2020');
70
71INSERT INTO Student
72VALUES (400002, 'Teemu Teekkari', '01-01-1990', 'CHEM', '01-09-2010', '30-08-2020');
73
74INSERT INTO Student
75VALUES (400003, 'Teemu Teekkari', '01-01-1990', 'BIZ', '01-09-2010', '30-08-2020');
76
77INSERT INTO Student
78VALUES (400004, 'Teemu Teekkari', '01-01-1990', 'ARTS', '01-09-2010', '30-08-2020');
79
80INSERT INTO Student
81VALUES (500001, 'Teemu Teekkari', '01-01-1990', 'IT', '01-09-2010', '30-08-2020');
82
83INSERT INTO Student
84VALUES (367894, 'Leena Meriläinen', '26-11-1996', 'ARTS', '01-09-2017', '30-08-2024');
85
86INSERT INTO Student
87VALUES (485268, 'Heikki Virtanen', '18-09-1998', 'BIZ', '01-09-2019', '30-08-2026');
88
89INSERT INTO Student
90VALUES (427367, 'Timo Korhonen', '13-03-1994', 'KON', '01-09-2015', '30-08-2021');
91
92INSERT INTO Student
93VALUES (416869, 'Anni Mäki', '01-06-1993', 'BIO', '01-09-2013', '30-08-2020');
94
95INSERT INTO Student
96VALUES (300004, 'Teemu Kokko', '17-01-1992', 'TIK', '01-09-2011', '30-08-2018');
97
98INSERT INTO Student
99VALUES (355677, 'Iiris Järvinen', '22-04-1995', 'EST', '01-09-2014', '30-08-2021');
100
101INSERT INTO Student
102VALUES (413268, 'Matti Mäki', '17-06-1997', 'AUT', '01-09-2018', '30-08-2025');
103
104INSERT INTO Student
105VALUES (479385, 'Lars Chydenius', '17-01-1992', 'TUTA', '01-09-2011', '30-08-2018');