· 6 years ago · Nov 15, 2019, 04:18 AM
1
2-- these scripts will delete the tables if it's already there
3
4DROP TABLE IF EXISTS StudentEnrollments;
5DROP TABLE IF EXISTS Students;
6DROP TABLE IF EXISTS Classrooms;
7
8
9-- create and populate the students table
10CREATE TABLE Students
11(
12 StudentId INTEGER PRIMARY KEY,
13 FirstName VARCHAR(200) NOT NULL,
14 LastName VARCHAR(200) NOT NULL,
15 Nationality VARCHAR(100) NOT NULL,
16 DateOfBirth DATETIME NULL
17);
18
19
20INSERT INTO Students
21 (FirstName, LastName, Nationality, DateOfBirth)
22VALUES
23 ('Mickey', 'Mouse', 'American', '1991-05-02'),
24 ('Donald', 'Duck', 'Japanese', '1992-11-12'),
25 ('Goofy', 'Goof', 'American', '1980-04-15'),
26 ('Daisy', 'Duck', 'French', '1985-02-16'),
27 ('Huey', 'Duck', 'French', '1986-05-19'),
28 ('Scrooge', 'McDuck', 'Japanese', '1983-11-11'),
29 ('Minnie', 'Mouse', 'Canadian', '1983-11-30'),
30 ('Louie', 'Duck', 'French', '1985-09-09');
31
32-- create and populate the classroom table
33CREATE TABLE Classrooms
34(
35 ClassroomId INTEGER PRIMARY KEY,
36 ClassName VARCHAR(200) NOT NULL,
37 Weight DECIMAL NOT NULL
38);
39
40INSERT INTO Classrooms
41 (ClassName, Weight)
42VALUES
43 ('Public Interaction', 0.10),
44 ('Pranks', 0.15),
45 ('Running', 0.15),
46 ('Acting', 0.30),
47 ('Making Jokes', 0.30);
48
49-- create and populate the student enrollment table
50CREATE TABLE StudentEnrollments
51(
52 StudentEnrollmentId INTEGER PRIMARY KEY,
53 StudentId INTEGER NOT NULL,
54 ClassroomId INTEGER NOT NULL,
55 Grade DECIMAL NOT NULL,
56 FOREIGN KEY(StudentId) REFERENCES Students(StudentId),
57 FOREIGN KEY(ClassroomId) REFERENCES Classrooms(ClassroomId)
58);
59
60INSERT INTO StudentEnrollments
61 (StudentId, ClassroomId, Grade)
62VALUES
63 (1, 1, 91),
64 (1, 2, 68),
65 (1, 3, 89),
66 (1, 4, 60),
67 (1, 5, 65),
68 (2, 1, 79),
69 (2, 2, 85),
70 (2, 3, 68),
71 (2, 4, 89),
72 (2, 5, 80),
73 (3, 1, 96),
74 (3, 2, 62),
75 (3, 3, 78),
76 (3, 4, 100),
77 (3, 5, 64),
78 (4, 1, 81),
79 (4, 2, 90),
80 (4, 3, 85),
81 (4, 4, 95),
82 (4, 5, 64),
83 (5, 1, 81),
84 (5, 2, 73),
85 (5, 3, 60),
86 (5, 4, 99),
87 (5, 5, 70),
88 (6, 1, 75),
89 (6, 2, 74),
90 (6, 3, 69),
91 (6, 4, 79),
92 (6, 5, 88),
93 (7, 1, 60),
94 (7, 2, 75),
95 (7, 3, 82),
96 (7, 4, 66),
97 (7, 5, 65),
98 (8, 1, 69),
99 (8, 2, 81),
100 (8, 3, 100),
101 (8, 4, 63),
102 (8, 5, 62);