High School SQL Creation Sample

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    GradeLevel INT
);

CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Tenure INT CHECK (Tenure BETWEEN 0 AND 50),
    Rating INT CHECK (Rating BETWEEN 1 AND 5)
);

CREATE TABLE Classes (
    ClassID INT PRIMARY KEY,
    ClassName VARCHAR(50),
    TeacherID INT,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);

CREATE TABLE StudentClasses (
    StudentID INT,
    ClassID INT,
    GradePercentage DECIMAL(5,2),
    PRIMARY KEY (StudentID, ClassID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
);

CREATE TABLE Extracurriculars (
    ExtracurricularID INT PRIMARY KEY,
    Name VARCHAR(50),
    AdvisorID INT,
    FOREIGN KEY (AdvisorID) REFERENCES Teachers(TeacherID)
);

CREATE TABLE StudentExtracurriculars (
    StudentID INT,
    ExtracurricularID INT,
    PRIMARY KEY (StudentID, ExtracurricularID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (ExtracurricularID) REFERENCES Extracurriculars(ExtracurricularID)
);

INSERT INTO Students VALUES (1, 'John', 'Doe', 9);
INSERT INTO Students VALUES (2, 'Jane', 'Doe', 10);
INSERT INTO Students VALUES (3, 'Jim', 'Smith', 12);
INSERT INTO Students VALUES (4, 'Alice', 'Johnson', 9);
INSERT INTO Students VALUES (5, 'Bob', 'Williams', 12);
INSERT INTO Students VALUES (6, 'Emily', 'Brown', 11);
INSERT INTO Students VALUES (7, 'Michael', 'Taylor', 9);
INSERT INTO Students VALUES (8, 'Olivia', 'Miller', 10);
INSERT INTO Students VALUES (9, 'Daniel', 'Anderson', 11);
INSERT INTO Students VALUES (10, 'Sophia', 'Garcia', 9);
INSERT INTO Students VALUES (11, 'Ethan', 'Martinez', 10);
INSERT INTO Students VALUES (12, 'Ava', 'Jones', 11);
INSERT INTO Students VALUES (13, 'Logan', 'Brown', 9);
INSERT INTO Students VALUES (14, 'Emma', 'Davis', 12);
INSERT INTO Students VALUES (15, 'Liam', 'Smith', 12);
INSERT INTO Students VALUES (16, 'Mia', 'Hernandez', 9);
INSERT INTO Students VALUES (17, 'Noah', 'Gonzalez', 10);
INSERT INTO Students VALUES (18, 'Isabella', 'Jackson', 12);
INSERT INTO Students VALUES (19, 'Lucas', 'White', 9);
INSERT INTO Students VALUES (20, 'Sophie', 'Adams', 10);

INSERT INTO Teachers VALUES (1, 'Mr.', 'Johnson', 10, 3);
INSERT INTO Teachers VALUES (2, 'Mr.', 'Kim', 7, 4);
INSERT INTO Teachers VALUES (3, 'Mr.', 'Koci', 8, 1);
INSERT INTO Teachers VALUES (4, 'Mrs.', 'Smith', 10, 5);
INSERT INTO Teachers VALUES (5, 'Mrs.', 'Link', 0, 5);
INSERT INTO Teachers VALUES (6, 'Ms.', 'Brown', 2, 3);
INSERT INTO Teachers VALUES (7, 'Dr.', 'Williams', 5, 4);
INSERT INTO Teachers VALUES (8, 'Miss', 'Jones', 4, 3);
INSERT INTO Teachers VALUES (9, 'Mr.', 'Taylor', 3, 3);
INSERT INTO Teachers VALUES (10, 'Mrs.', 'Miller', 15, 4);
INSERT INTO Teachers VALUES (11, 'Ms.', 'Garcia', 1, 4);
INSERT INTO Teachers VALUES (12, 'Prof.', 'Anderson', 20, 3);
INSERT INTO Teachers VALUES (13, 'Mrs.', 'Martinez', 16, 5);
INSERT INTO Teachers VALUES (14, 'Mr.', 'Davis', 6, 5);
INSERT INTO Teachers VALUES (15, 'Dr.', 'Hernandez', 6, 2);
INSERT INTO Teachers VALUES (16, 'Miss', 'Gonzalez', 10, 3);
INSERT INTO Teachers VALUES (17, 'Mr.', 'Jackson', 1, 2);
INSERT INTO Teachers VALUES (18, 'Mrs.', 'White', 5, 2);

INSERT INTO Classes VALUES (1, 'Algebra', 1);
INSERT INTO Classes VALUES (2, 'Prealgebra', 2);
INSERT INTO Classes VALUES (3, 'Trigonometry', 3);
INSERT INTO Classes VALUES (4, 'Calculus', 4);
INSERT INTO Classes VALUES (5, 'Calculus II', 5);
INSERT INTO Classes VALUES (6, 'Biology', 6);
INSERT INTO Classes VALUES (7, 'Chemistry', 7);
INSERT INTO Classes VALUES (8, 'Physics', 8);
INSERT INTO Classes VALUES (9, 'World History', 9);
INSERT INTO Classes VALUES (10, 'US History', 10);
INSERT INTO Classes VALUES (11, 'Literature', 11);
INSERT INTO Classes VALUES (12, 'Composition', 12);
INSERT INTO Classes VALUES (13, 'Computer Science', 13);
INSERT INTO Classes VALUES (14, 'Electrical Engineering', 14);
INSERT INTO Classes VALUES (15, 'Robotics', 15);
INSERT INTO Classes VALUES (16, 'Design', 16);
INSERT INTO Classes VALUES (17, 'Philosophy', 17);
INSERT INTO Classes VALUES (18, 'French', 18);
INSERT INTO Classes VALUES (19, 'Physical Education', 1);
INSERT INTO Classes VALUES (20, 'Music', 2);
INSERT INTO Classes VALUES (21, 'Art', 3);
INSERT INTO Classes VALUES (22, 'Spanish', 4);
INSERT INTO Classes VALUES (23, 'Chinese', 5);
INSERT INTO Classes VALUES (24, 'Health', 6);
INSERT INTO Classes VALUES (25, 'Engineering', 7);
INSERT INTO Classes VALUES (26, 'Quantum Mechanics', 8);
INSERT INTO Classes VALUES (27, 'European History', 9);
INSERT INTO Classes VALUES (28, 'East Asian Studies', 10);
INSERT INTO Classes VALUES (29, 'Journalism', 11);
INSERT INTO Classes VALUES (30, 'Poetry', 12);

INSERT INTO StudentClasses VALUES (1, 8, 23.0);
INSERT INTO StudentClasses VALUES (1, 27, 41.3);
INSERT INTO StudentClasses VALUES (1, 30, 86.5);
INSERT INTO StudentClasses VALUES (1, 7, 67.9);
INSERT INTO StudentClasses VALUES (2, 9, 19.4);
INSERT INTO StudentClasses VALUES (2, 4, 71.8);
INSERT INTO StudentClasses VALUES (2, 6, 3.3);
INSERT INTO StudentClasses VALUES (2, 17, 6.5);
INSERT INTO StudentClasses VALUES (3, 1, 62.7);
INSERT INTO StudentClasses VALUES (3, 28, 43.1);
INSERT INTO StudentClasses VALUES (3, 17, 86.6);
INSERT INTO StudentClasses VALUES (3, 15, 31.7);
INSERT INTO StudentClasses VALUES (4, 11, 3.1);
INSERT INTO StudentClasses VALUES (4, 12, 79.6);
INSERT INTO StudentClasses VALUES (4, 14, 27.6);
INSERT INTO StudentClasses VALUES (4, 19, 74.6);
INSERT INTO StudentClasses VALUES (4, 20, 85.2);
INSERT INTO StudentClasses VALUES (4, 21, 64.3);
INSERT INTO StudentClasses VALUES (4, 23, 40.4);
INSERT INTO StudentClasses VALUES (5, 17, 37.4);
INSERT INTO StudentClasses VALUES (5, 28, 39.1);
INSERT INTO StudentClasses VALUES (5, 25, 6.1);
INSERT INTO StudentClasses VALUES (5, 22, 33.7);
INSERT INTO StudentClasses VALUES (6, 9, 31.9);
INSERT INTO StudentClasses VALUES (6, 10, 51.2);
INSERT INTO StudentClasses VALUES (6, 21, 53.8);
INSERT INTO StudentClasses VALUES (6, 15, 10.9);
INSERT INTO StudentClasses VALUES (7, 5, 17.3);
INSERT INTO StudentClasses VALUES (7, 7, 34.6);
INSERT INTO StudentClasses VALUES (7, 11, 23.6);
INSERT INTO StudentClasses VALUES (7, 12, 17.1);
INSERT INTO StudentClasses VALUES (7, 13, 25.4);
INSERT INTO StudentClasses VALUES (8, 2, 72.4);
INSERT INTO StudentClasses VALUES (8, 18, 9.4);
INSERT INTO StudentClasses VALUES (8, 10, 32.3);
INSERT INTO StudentClasses VALUES (8, 20, 67.0);
INSERT INTO StudentClasses VALUES (9, 3, 1.1);
INSERT INTO StudentClasses VALUES (9, 5, 50.6);
INSERT INTO StudentClasses VALUES (9, 9, 33.8);
INSERT INTO StudentClasses VALUES (9, 12, 62.4);
INSERT INTO StudentClasses VALUES (9, 19, 30.9);
INSERT INTO StudentClasses VALUES (9, 25, 43.5);
INSERT INTO StudentClasses VALUES (10, 1, 43.7);
INSERT INTO StudentClasses VALUES (10, 2, 8.1);
INSERT INTO StudentClasses VALUES (10, 16, 2.5);
INSERT INTO StudentClasses VALUES (10, 18, 27.8);
INSERT INTO StudentClasses VALUES (10, 24, 16.3);
INSERT INTO StudentClasses VALUES (10, 25, 88.2);
INSERT INTO StudentClasses VALUES (11, 8, 52.4);
INSERT INTO StudentClasses VALUES (11, 2, 63.0);
INSERT INTO StudentClasses VALUES (11, 18, 19.1);
INSERT INTO StudentClasses VALUES (11, 28, 46.7);
INSERT INTO StudentClasses VALUES (12, 6, 31.2);
INSERT INTO StudentClasses VALUES (12, 9, 66.0);
INSERT INTO StudentClasses VALUES (12, 10, 74.7);
INSERT INTO StudentClasses VALUES (12, 12, 7.1);
INSERT INTO StudentClasses VALUES (12, 16, 67.4);
INSERT INTO StudentClasses VALUES (12, 17, 81.8);
INSERT INTO StudentClasses VALUES (12, 27, 46.5);
INSERT INTO StudentClasses VALUES (13, 2, 35.2);
INSERT INTO StudentClasses VALUES (13, 10, 98.9);
INSERT INTO StudentClasses VALUES (13, 16, 99.5);
INSERT INTO StudentClasses VALUES (13, 21, 45.5);
INSERT INTO StudentClasses VALUES (13, 24, 50.9);
INSERT INTO StudentClasses VALUES (14, 6, 47.1);
INSERT INTO StudentClasses VALUES (14, 7, 55.6);
INSERT INTO StudentClasses VALUES (14, 15, 2.7);
INSERT INTO StudentClasses VALUES (14, 21, 78.7);
INSERT INTO StudentClasses VALUES (14, 25, 80.9);
INSERT INTO StudentClasses VALUES (15, 3, 92.9);
INSERT INTO StudentClasses VALUES (15, 9, 87.2);
INSERT INTO StudentClasses VALUES (15, 13, 98.7);
INSERT INTO StudentClasses VALUES (15, 19, 63.9);
INSERT INTO StudentClasses VALUES (15, 22, 56.1);
INSERT INTO StudentClasses VALUES (15, 23, 63.4);
INSERT INTO StudentClasses VALUES (16, 3, 2.3);
INSERT INTO StudentClasses VALUES (16, 5, 30.9);
INSERT INTO StudentClasses VALUES (16, 9, 68.0);
INSERT INTO StudentClasses VALUES (16, 16, 1.4);
INSERT INTO StudentClasses VALUES (16, 20, 96.5);
INSERT INTO StudentClasses VALUES (16, 22, 12.7);
INSERT INTO StudentClasses VALUES (17, 2, 44.8);
INSERT INTO StudentClasses VALUES (17, 6, 88.7);
INSERT INTO StudentClasses VALUES (17, 8, 81.9);
INSERT INTO StudentClasses VALUES (17, 10, 31.4);
INSERT INTO StudentClasses VALUES (17, 15, 25.0);
INSERT INTO StudentClasses VALUES (18, 9, 98.8);
INSERT INTO StudentClasses VALUES (18, 3, 38.9);
INSERT INTO StudentClasses VALUES (18, 25, 26.1);
INSERT INTO StudentClasses VALUES (18, 7, 59.9);
INSERT INTO StudentClasses VALUES (19, 12, 15.5);
INSERT INTO StudentClasses VALUES (19, 14, 82.1);
INSERT INTO StudentClasses VALUES (19, 22, 93.2);
INSERT INTO StudentClasses VALUES (19, 26, 74.5);
INSERT INTO StudentClasses VALUES (19, 29, 66.7);
INSERT INTO StudentClasses VALUES (20, 8, 89.3);
INSERT INTO StudentClasses VALUES (20, 27, 15.8);
INSERT INTO StudentClasses VALUES (20, 28, 43.4);
INSERT INTO StudentClasses VALUES (20, 4, 21.8);

INSERT INTO Extracurriculars VALUES (1, 'Chess Club', 1);
INSERT INTO Extracurriculars VALUES (2, 'Debate Team', 2);
INSERT INTO Extracurriculars VALUES (3, 'Drama Club', 3);
INSERT INTO Extracurriculars VALUES (4, 'Math Team', 4);
INSERT INTO Extracurriculars VALUES (5, 'Science Club', 5);
INSERT INTO Extracurriculars VALUES (6, 'Art Club', 6);
INSERT INTO Extracurriculars VALUES (7, 'Music Club', 7);
INSERT INTO Extracurriculars VALUES (8, 'Robotics Club', 8);
INSERT INTO Extracurriculars VALUES (9, 'Coding Club', 9);
INSERT INTO Extracurriculars VALUES (10, 'Literature Club', 10);
INSERT INTO Extracurriculars VALUES (11, 'History Club', 11);
INSERT INTO Extracurriculars VALUES (12, 'Geography Club', 12);
INSERT INTO Extracurriculars VALUES (13, 'Physics Club', 13);
INSERT INTO Extracurriculars VALUES (14, 'Biology Club', 14);
INSERT INTO Extracurriculars VALUES (15, 'Chemistry Club', 15);

INSERT INTO StudentExtracurriculars VALUES (1, 12);
INSERT INTO StudentExtracurriculars VALUES (1, 4);
INSERT INTO StudentExtracurriculars VALUES (1, 7);
INSERT INTO StudentExtracurriculars VALUES (2, 10);
INSERT INTO StudentExtracurriculars VALUES (2, 15);
INSERT INTO StudentExtracurriculars VALUES (3, 3);
INSERT INTO StudentExtracurriculars VALUES (4, 15);
INSERT INTO StudentExtracurriculars VALUES (5, 3);
INSERT INTO StudentExtracurriculars VALUES (5, 4);
INSERT INTO StudentExtracurriculars VALUES (8, 1);
INSERT INTO StudentExtracurriculars VALUES (8, 10);
INSERT INTO StudentExtracurriculars VALUES (9, 5);
INSERT INTO StudentExtracurriculars VALUES (10, 6);
INSERT INTO StudentExtracurriculars VALUES (10, 7);
INSERT INTO StudentExtracurriculars VALUES (11, 2);
INSERT INTO StudentExtracurriculars VALUES (11, 10);
INSERT INTO StudentExtracurriculars VALUES (11, 11);
INSERT INTO StudentExtracurriculars VALUES (12, 2);
INSERT INTO StudentExtracurriculars VALUES (12, 12);
INSERT INTO StudentExtracurriculars VALUES (12, 6);
INSERT INTO StudentExtracurriculars VALUES (13, 11);
INSERT INTO StudentExtracurriculars VALUES (13, 12);
INSERT INTO StudentExtracurriculars VALUES (15, 9);
INSERT INTO StudentExtracurriculars VALUES (15, 5);
INSERT INTO StudentExtracurriculars VALUES (16, 9);
INSERT INTO StudentExtracurriculars VALUES (17, 12);
INSERT INTO StudentExtracurriculars VALUES (17, 7);
INSERT INTO StudentExtracurriculars VALUES (19, 12);