-- these scripts will delete the tables if it's already there
DROP TABLE IF EXISTS StudentEnrollments;
DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Classrooms;
-- create and populate the students table
CREATE TABLE Students
(
StudentId INTEGER PRIMARY KEY,
FirstName VARCHAR(200) NOT NULL,
LastName VARCHAR(200) NOT NULL,
Nationality VARCHAR(100) NOT NULL,
DateOfBirth DATETIME NULL
);
INSERT INTO Students
(StudentId, FirstName, LastName, Nationality, DateOfBirth)
VALUES
('1','Mickey', 'Mouse', 'American', '1991-05-02'),
('2','Donald', 'Duck', 'Japanese', '1992-11-12'),
('3','Goofy', 'Goof', 'American', '1980-04-15'),
('4','Daisy', 'Duck', 'French', '1985-02-16'),
('5','Huey', 'Duck', 'French', '1986-05-19'),
('6','Scrooge', 'McDuck', 'Japanese', '1983-11-11'),
('7','Minnie', 'Mouse', 'Canadian', '1983-11-30'),
('8','Louie', 'Duck', 'French', '1985-09-09');
-- create and populate the classroom table
CREATE TABLE Classrooms
(
ClassroomId INTEGER PRIMARY KEY,
ClassName VARCHAR(200) NOT NULL,
Weight DECIMAL NOT NULL
);
INSERT INTO Classrooms
(ClassroomId, ClassName, Weight)
VALUES
(1, 'Public Interaction', 0.10),
(2, 'Pranks', 0.15),
(3, 'Running', 0.15),
(4, 'Acting', 0.30),
(5, 'Making Jokes', 0.30);
-- create and populate the student enrollment table
CREATE TABLE StudentEnrollments
(
StudentEnrollmentId INTEGER PRIMARY KEY,
StudentId INTEGER NOT NULL,
ClassroomId INTEGER NOT NULL,
Grade DECIMAL NOT NULL,
FOREIGN KEY(StudentId) REFERENCES Students(StudentId),
FOREIGN KEY(ClassroomId) REFERENCES Classrooms(ClassroomId)
);
INSERT INTO StudentEnrollments
(StudentEnrollmentId, StudentId, ClassroomId, Grade)
VALUES
(1, 1, 1, 91),
(2, 1, 2, 68),
(3, 1, 3, 89),
(4, 1, 4, 60),
(5, 1, 5, 65),
(6, 2, 1, 79),
(7, 2, 2, 85),
(8, 2, 3, 68),
(9, 2, 4, 89),
(10, 2, 5, 80),
(11, 3, 1, 96),
(12, 3, 2, 62),
(13, 3, 3, 78),
(14, 3, 4, 100),
(15, 3, 5, 64),
(16, 4, 1, 81),
(17, 4, 2, 90),
(18, 4, 3, 85),
(19, 4, 4, 95),
(20, 4, 5, 64),
(21, 5, 1, 81),
(22, 5, 2, 73),
(23, 5, 3, 60),
(24, 5, 4, 99),
(25, 5, 5, 70),
(26, 6, 1, 75),
(27, 6, 2, 74),
(28, 6, 3, 69),
(29, 6, 4, 79),
(30, 6, 5, 88),
(31, 7, 1, 60),
(32, 7, 2, 75),
(33, 7, 3, 82),
(34, 7, 4, 66),
(35, 7, 5, 65),
(36, 8, 1, 69),
(37, 8, 2, 81),
(38, 8, 3, 100),
(39, 8, 4, 63),
(40, 8, 5, 62);
Comments