pdTcSjrk

1#--Project:  Student database file using create and insert
2#--by Debbie Reid
3#---Create and open the database
4
5drop database if exists Class;
6
7CREATE DATABASE Class;
8
9
10#-- Using the database
11
12USE Class;
13
14# create student table
15
16
17DROP TABLE IF EXISTS student;
18
19
20
21CREATE TABLE student
22
23(
24  
25name       VARCHAR(20) NOT NULL,
26  
27gender ENUM('F','M') NOT NULL,
28  
29student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
30  
31PRIMARY KEY (student_id)
32
33); 
34
35# create grade event table
36
37DROP TABLE IF EXISTS grade_event;
38
39
40
41CREATE TABLE grade_event
42
43(
44  
45date     DATE NOT NULL,
46  
47category ENUM('T','Q') NOT NULL,
48  
49event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
50  
51PRIMARY KEY (event_id)
52
53); 
54
55# create score table
56
57
58# The PRIMARY KEY comprises two columns to prevent any combination
59# of event_id/student_id from appearing more than once.
60
61
62DROP TABLE IF EXISTS score;
63
64
65CREATE TABLE score
66
67(
68  
69student_id INT UNSIGNED NOT NULL,
70  
71event_id   INT UNSIGNED NOT NULL,
72  
73score      INT NOT NULL,
74  
75PRIMARY KEY (event_id, student_id),
76  
77INDEX (student_id),
78  
79FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
80  
81FOREIGN KEY (student_id) REFERENCES student (student_id)
82
83); 
84
85# create absence table
86
87
88DROP TABLE IF EXISTS absence;
89
90
91
92CREATE TABLE absence
93
94(
95  
96student_id INT UNSIGNED NOT NULL,
97  
98date       DATE NOT NULL,
99  
100PRIMARY KEY (student_id, date),
101  
102FOREIGN KEY (student_id) REFERENCES student (student_id)
103
104); 
105
106#--Populate the student table
107
108
109INSERT INTO student VALUES ('Megan','F',NULL);
110
111INSERT INTO student VALUES ('Joseph','M',NULL);
112
113INSERT INTO student VALUES ('Kyle','M',NULL);
114
115INSERT INTO student VALUES ('Katie','F',NULL);
116
117INSERT INTO student VALUES ('Abby','F',NULL);
118
119INSERT INTO student VALUES ('Nathan','M',NULL);
120
121INSERT INTO student VALUES ('Liesl','F',NULL);
122
123INSERT INTO student VALUES ('Ian','M',NULL);
124
125INSERT INTO student VALUES ('Colin','M',NULL);
126
127INSERT INTO student VALUES ('Peter','M',NULL);
128
129INSERT INTO student VALUES ('Michael','M',NULL);
130
131INSERT INTO student VALUES ('Thomas','M',NULL);
132
133INSERT INTO student VALUES ('Devri','F',NULL);
134
135INSERT INTO student VALUES ('Ben','M',NULL);
136
137INSERT INTO student VALUES ('Aubrey','F',NULL);
138
139INSERT INTO student VALUES ('Rebecca','F',NULL);
140
141INSERT INTO student VALUES ('Will','M',NULL);
142
143INSERT INTO student VALUES ('Max','M',NULL);
144
145INSERT INTO student VALUES ('Rianne','F',NULL);
146
147INSERT INTO student VALUES ('Avery','F',NULL);
148
149INSERT INTO student VALUES ('Lauren','F',NULL);
150
151INSERT INTO student VALUES ('Becca','F',NULL);
152
153INSERT INTO student VALUES ('Gregory','M',NULL);
154
155INSERT INTO student VALUES ('Sarah','F',NULL);
156
157INSERT INTO student VALUES ('Robbie','M',NULL);
158
159INSERT INTO student VALUES ('Keaton','M',NULL);
160
161INSERT INTO student VALUES ('Carter','M',NULL);
162
163INSERT INTO student VALUES ('Teddy','M',NULL);
164
165INSERT INTO student VALUES ('Gabrielle','F',NULL);
166
167INSERT INTO student VALUES ('Grace','F',NULL);
168
169INSERT INTO student VALUES ('Emily','F',NULL);
170
171
172#--Populate grade event table
173
174INSERT INTO grade_event VALUES('2015-09-03', 'Q', NULL);
175INSERT INTO grade_event VALUES('
1762015-09-06', 'Q', NULL);
177INSERT INTO grade_event VALUES('
1782015-09-09', 'T', NULL);
179INSERT INTO grade_event VALUES('
1802015-09-16', 'Q', NULL);
181INSERT INTO grade_event VALUES(
182'2015-09-23', 'Q', NULL);
183INSERT INTO grade_event VALUES('
1842015-10-01', 'T', NULL);
185
186
187#--Populate the score table
188
189
190INSERT INTO score VALUES (1,1,20);
191
192INSERT INTO score VALUES (3,1,20);
193
194INSERT INTO score VALUES (4,1,18);
195
196INSERT INTO score VALUES (5,1,13);
197
198INSERT INTO score VALUES (6,1,18);
199
200INSERT INTO score VALUES (7,1,14);
201
202INSERT INTO score VALUES (8,1,14);
203
204INSERT INTO score VALUES (9,1,11);
205
206INSERT INTO score VALUES (10,1,19);
207
208INSERT INTO score VALUES (11,1,18);
209
210INSERT INTO score VALUES (12,1,19);
211
212INSERT INTO score VALUES (14,1,11);
213
214INSERT INTO score VALUES (15,1,20);
215
216INSERT INTO score VALUES (16,1,18);
217
218INSERT INTO score VALUES (17,1,9);
219
220INSERT INTO score VALUES (18,1,20);
221
222INSERT INTO score VALUES (19,1,9);
223
224INSERT INTO score VALUES (20,1,9);
225
226INSERT INTO score VALUES (21,1,13);
227
228INSERT INTO score VALUES (22,1,13);
229
230INSERT INTO score VALUES (23,1,16);
231
232INSERT INTO score VALUES (24,1,11);
233
234INSERT INTO score VALUES (25,1,19);
235
236INSERT INTO score VALUES (26,1,10);
237
238INSERT INTO score VALUES (27,1,15);
239
240INSERT INTO score VALUES (28,1,15);
241
242INSERT INTO score VALUES (29,1,19);
243
244INSERT INTO score VALUES (30,1,17);
245
246INSERT INTO score VALUES (31,1,11);
247
248INSERT INTO score VALUES (1,2,17);
249
250INSERT INTO score VALUES (2,2,8);
251
252INSERT INTO score VALUES (3,2,13);
253
254INSERT INTO score VALUES (4,2,13);
255
256INSERT INTO score VALUES (5,2,17);
257
258INSERT INTO score VALUES (6,2,13);
259
260INSERT INTO score VALUES (7,2,17);
261
262INSERT INTO score VALUES (8,2,8);
263
264INSERT INTO score VALUES (9,2,19);
265
266INSERT INTO score VALUES (10,2,18);
267
268INSERT INTO score VALUES (11,2,15);
269
270INSERT INTO score VALUES (12,2,19);
271
272INSERT INTO score VALUES (13,2,18);
273
274INSERT INTO score VALUES (14,2,18);
275
276INSERT INTO score VALUES (15,2,16);
277
278INSERT INTO score VALUES (16,2,9);
279
280INSERT INTO score VALUES (17,2,13);
281
282INSERT INTO score VALUES (18,2,9);
283
284INSERT INTO score VALUES (19,2,11);
285
286INSERT INTO score VALUES (21,2,12);
287
288INSERT INTO score VALUES (22,2,10);
289
290INSERT INTO score VALUES (23,2,17);
291INSERT INTO score VALUES (24,2,19);
292
293INSERT INTO score VALUES (25,2,10);
294
295INSERT INTO score VALUES (26,2,18);
296
297INSERT INTO score VALUES (27,2,8);
298
299INSERT INTO score VALUES (28,2,13);
300
301INSERT INTO score VALUES (29,2,16);
302
303INSERT INTO score VALUES (30,2,12);
304
305INSERT INTO score VALUES (31,2,19);
306
307INSERT INTO score VALUES (1,3,88);
308
309INSERT INTO score VALUES (2,3,84);
310
311INSERT INTO score VALUES (3,3,69);
312
313INSERT INTO score VALUES (4,3,71);
314
315INSERT INTO score VALUES (5,3,97);
316
317INSERT INTO score VALUES (6,3,83);
318
319INSERT INTO score VALUES (7,3,88);
320INSERT INTO score VALUES (8,3,75);
321
322INSERT INTO score VALUES (9,3,83);
323
324INSERT INTO score VALUES (10,3,72);
325INSERT INTO score VALUES (11,3,74);
326
327INSERT INTO score VALUES (12,3,77);
328
329INSERT INTO score VALUES (13,3,67);
330INSERT INTO score VALUES (14,3,68);
331
332INSERT INTO score VALUES (15,3,75);
333
334INSERT INTO score VALUES (16,3,60);
335
336INSERT INTO score VALUES (17,3,79);
337
338INSERT INTO score VALUES (18,3,96);
339
340INSERT INTO score VALUES (19,3,79);
341
342INSERT INTO score VALUES (20,3,76);
343
344INSERT INTO score VALUES (21,3,91);
345
346INSERT INTO score VALUES (22,3,81);
347
348INSERT INTO score VALUES (23,3,81);
349
350INSERT INTO score VALUES (24,3,62);
351
352INSERT INTO score VALUES (25,3,79);
353
354INSERT INTO score VALUES (26,3,86);
355
356INSERT INTO score VALUES (27,3,90);
357
358INSERT INTO score VALUES (28,3,68);
359
360INSERT INTO score VALUES (29,3,66);
361
362INSERT INTO score VALUES (30,3,79);
363
364INSERT INTO score VALUES (31,3,81);
365
366INSERT INTO score VALUES (2,4,7);
367
368INSERT INTO score VALUES (3,4,17);
369
370INSERT INTO score VALUES (4,4,16);
371
372INSERT INTO score VALUES (5,4,20);
373
374INSERT INTO score VALUES (6,4,9);
375
376INSERT INTO score VALUES (7,4,19);
377
378INSERT INTO score VALUES (8,4,12);
379
380INSERT INTO score VALUES (9,4,17);
381
382INSERT INTO score VALUES (10,4,12);
383
384INSERT INTO score VALUES (11,4,16);
385
386INSERT INTO score VALUES (12,4,13);
387
388INSERT INTO score VALUES (13,4,8);
389
390INSERT INTO score VALUES (14,4,11);
391
392INSERT INTO score VALUES (15,4,9);
393
394INSERT INTO score VALUES (16,4,20);
395
396INSERT INTO score VALUES (18,4,11);
397
398INSERT INTO score VALUES (19,4,15);
399
400INSERT INTO score VALUES (20,4,17);
401
402INSERT INTO score VALUES (21,4,13);
403
404INSERT INTO score VALUES (22,4,20);
405
406INSERT INTO score VALUES (23,4,13);
407
408INSERT INTO score VALUES (24,4,12);
409INSERT INTO score VALUES (25,4,10);
410
411INSERT INTO score VALUES (26,4,15);
412
413INSERT INTO score VALUES (28,4,17);
414
415INSERT INTO score VALUES (30,4,11);
416
417INSERT INTO score VALUES (31,4,19);
418
419INSERT INTO score VALUES (1,5,15);
420
421INSERT INTO score VALUES (2,5,12);
422
423INSERT INTO score VALUES (3,5,11);
424
425INSERT INTO score VALUES (5,5,13);
426
427INSERT INTO score VALUES (6,5,18);
428
429INSERT INTO score VALUES (7,5,14);
430
431INSERT INTO score VALUES (8,5,18);
432
433INSERT INTO score VALUES (9,5,13);
434
435INSERT INTO score VALUES (10,5,14);
436
437INSERT INTO score VALUES (11,5,18);
438
439INSERT INTO score VALUES (12,5,8);
440
441INSERT INTO score VALUES (13,5,8);
442
443INSERT INTO score VALUES (14,5,16);
444
445INSERT INTO score VALUES (15,5,13);
446
447INSERT INTO score VALUES (16,5,15);
448
449INSERT INTO score VALUES (17,5,11);
450
451INSERT INTO score VALUES (18,5,18);
452
453INSERT INTO score VALUES (19,5,18);
454INSERT INTO score VALUES (20,5,14);
455
456INSERT INTO score VALUES (21,5,17);
457
458INSERT INTO score VALUES (22,5,17);
459
460INSERT INTO score VALUES (23,5,15);
461
462INSERT INTO score VALUES (25,5,14);
463
464INSERT INTO score VALUES (26,5,8);
465
466INSERT INTO score VALUES (28,5,20);
467
468INSERT INTO score VALUES (29,5,16);
469
470INSERT INTO score VALUES (31,5,9);
471
472INSERT INTO score VALUES (1,6,100);
473
474INSERT INTO score VALUES (2,6,91);
475
476INSERT INTO score VALUES (3,6,94);
477
478INSERT INTO score VALUES (4,6,74);
479
480INSERT INTO score VALUES (5,6,97);
481
482INSERT INTO score VALUES (6,6,89);
483
484INSERT INTO score VALUES (7,6,76);
485
486INSERT INTO score VALUES (8,6,65);
487
488INSERT INTO score VALUES (9,6,73);
489
490INSERT INTO score VALUES (10,6,63);
491
492INSERT INTO score VALUES (11,6,98);
493
494INSERT INTO score VALUES (12,6,75);
495
496INSERT INTO score VALUES (14,6,77);
497
498INSERT INTO score VALUES (15,6,62);
499
500INSERT INTO score VALUES (16,6,98);
501
502INSERT INTO score VALUES (17,6,94);
503
504INSERT INTO score VALUES (18,6,94);
505
506INSERT INTO score VALUES (19,6,74);
507INSERT INTO score VALUES (20,6,62);
508
509INSERT INTO score VALUES (21,6,73);
510
511INSERT INTO score VALUES (22,6,95);
512
513INSERT INTO score VALUES (24,6,68);
514
515INSERT INTO score VALUES (25,6,85);
516
517INSERT INTO score VALUES (26,6,91);
518INSERT INTO score VALUES (27,6,70);
519
520INSERT INTO score VALUES (28,6,77);
521
522INSERT INTO score VALUES (29,6,66);
523
524INSERT INTO score VALUES (30,6,68);
525
526INSERT INTO score VALUES (31,6,76);
527
528
529#--Populate the absence table
530
531INSERT INTO `absence` VALUES (3,'2015-09-03');
532
533INSERT INTO `absence` VALUES (5,'2015-09-03');
534
535INSERT INTO `absence` VALUES (10,'2015-09-06');
536
537INSERT INTO `absence` VALUES (10,'2015-09-09');
538
539INSERT INTO `absence` VALUES (17,'2015-09-07');
540
541INSERT INTO `absence` VALUES (20,'2015-09-07');