sql

/* Departments */ insert into Departments (departmentid, name, chairperson) values (1, 'Science', 'Shaine'); insert into Departments (departmentid, name, chairperson) values (2, 'Fine Arts', 'Demetris'); insert into Departments (departmentid, name, chairperson) values (3, 'Business', 'Pietra'); /* Buildings */ insert into Buildings (buildingid, name, address) values (1, 'Woodburn Hall', '5 Sugar Parkway'); insert into Buildings (buildingid, name, address) values (2, 'Cedar Hall', '999 Goodland Avenue'); insert into Buildings (buildingid, name, address) values (3, 'Spruce Hall', '78421 Red Cloud Circle'); /* rooms */ insert into Rooms (roomnum, buildingid, empid, type) values (1, 1, 1, 'Lecture Hall'); insert into Rooms (roomnum, buildingid, empid, type) values (2, 2, 2, 'Lecture Hall'); insert into Rooms (roomnum, buildingid, empid, type) values (3, 3, 3, 'Computer Lab'); insert into Rooms (roomnum, buildingid, empid, type) values (4, 4, 4, 'Collaboration Room'); insert into Rooms (roomnum, buildingid, empid, type) values (5, 5, 5, 'Studio'); insert into Rooms (roomnum, buildingid, empid, type) values (6, 6, 6, 'Computer Lab'); insert into Rooms (roomnum, buildingid, empid, type) values (7, 7, 7, 'Lecture Hall'); insert into Rooms (roomnum, buildingid, empid, type) values (8, 8, 8, 'Lecture Hall'); /* Faculty */ insert into Faculty (departmentid, empid, fname, mname, lname, title_of_job, SSN, hire_date) values (1, 1, 'Jerry', 'John', 'Goode', 'Geological Engineer', '724-58-7581', '2/2/2001'); insert into Faculty (departmentid, empid, fname, mname, lname, title_of_job, SSN, hire_date) values (2, 2, 'Leslie', 'Anna', 'Knope', 'Accounting Assistant IV', '770-50-8895', '3/14/1993'); insert into Faculty (departmentid, empid, fname, mname, lname, title_of_job, SSN, hire_date) values (3, 3, 'Michael', 'Gary', 'Scott', 'Geologist III', '552-06-6318', '3/5/1991'); insert into Faculty (departmentid, empid, fname, mname, lname, title_of_job, SSN, hire_date) values (4, 4, 'Dwight', 'Kurt', 'Schrute', 'Safety Technician II', '892-97-0149', '9/20/2000'); insert into Faculty (departmentid, empid, fname, mname, lname, title_of_job, SSN, hire_date) values (5, 5, 'Jim', 'Allen', 'Halpert', 'Registered Nurse', '205-26-3806', '6/8/2004'); insert into Faculty (departmentid, empid, fname, mname, lname, title_of_job, SSN, hire_date) values (6, 6, 'Angela', 'Rene', 'Martin', 'Research Assistant II', '369-94-0249', '12/2/2007'); insert into Faculty (departmentid, empid, fname, mname, lname, title_of_job, SSN, hire_date) values (7, 7, 'Phillis', 'Lisa', 'Vance', 'Senior Cost Accountant', '755-64-5393', '7/23/1993'); insert into Faculty (departmentid, empid, fname, mname, lname, title_of_job, SSN, hire_date) values (8, 8, 'Creed', 'Florence', 'Bratton', 'Desktop Support Technician', '626-70-3528', '11/25/1990'); insert into Faculty (departmentid, empid, fname, mname, lname, title_of_job, SSN, hire_date) values (9, 9, 'Kevin', 'Dean', 'Malone', 'Media Manager IV', '659-63-2100', '10/11/2014'); insert into Faculty (departmentid, empid, fname, mname, lname, title_of_job, SSN, hire_date) values (10, 10, 'Gabe', 'Mary', 'Lewis', 'Account Executive', '838-95-8303', '3/8/2000'); /* Faculty_Phone */ insert into Faculty_Phone (empid, phonenum, type) values (1, '673-108-0894', 'Cellphone'); insert into Faculty_Phone (empid, phonenum, type) values (2, '195-384-8249', 'Home'); insert into Faculty_Phone (empid, phonenum, type) values (3, '988-791-6039', 'Home'); insert into Faculty_Phone (empid, phonenum, type) values (4, '125-334-0628', 'Cellphone'); insert into Faculty_Phone (empid, phonenum, type) values (5, '854-373-7237', 'Home'); insert into Faculty_Phone (empid, phonenum, type) values (6, '677-156-4393', 'Cellphone'); insert into Faculty_Phone (empid, phonenum, type) values (7, '266-352-9079', 'Home'); insert into Faculty_Phone (empid, phonenum, type) values (8, '161-113-7415', 'Home'); insert into Faculty_Phone (empid, phonenum, type) values (9, '753-810-9284', 'Cellphone'); insert into Faculty_Phone (empid, phonenum, type) values (10, '680-673-4640', 'Cellphone'); /* Faculty_email */ insert into Faculty_Email (empid, email, type) values (1, '[email protected]', 'work'); insert into Faculty_Email (empid, email, type) values (2, '[email protected]', 'work'); insert into Faculty_Email (empid, email, type) values (3, '[email protected]', 'personal'); insert into Faculty_Email (empid, email, type) values (4, '[email protected]', 'work'); insert into Faculty_Email (empid, email, type) values (5, '[email protected]', 'work'); insert into Faculty_Email (empid, email, type) values (6, '[email protected]', 'personal'); insert into Faculty_Email (empid, email, type) values (7, '[email protected]', 'work'); insert into Faculty_Email (empid, email, type) values (8, '[email protected]', 'work'); insert into Faculty_Email (empid, email, type) values (9, '[email protected]', 'work'); insert into Faculty_Email (empid, email, type) values (10, '[email protected]', 'personal'); /* Courses */ insert into Courses (coursenum, departmentid, course_name, course_credit) values (1, 1, 'I-101', 2); insert into Courses (coursenum, departmentid, course_name, course_credit) values (2, 2, 'A-203', 2); insert into Courses (coursenum, departmentid, course_name, course_credit) values (3, 3, 'G-234', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (4, 4, 'I-403', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (5, 5, 'I-222', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (6, 6, 'J-101', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (7, 7, 'P-308', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (8, 8, 'G-344', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (9, 9, 'C-210', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (10, 10, 'C-403', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (11, 11, 'S-224', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (12, 12, 'A-201', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (13, 13, 'P-201', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (14, 14, 'I-308', 2); insert into Courses (coursenum, departmentid, course_name, course_credit) values (15, 15, 'I-408', 2); insert into Courses (coursenum, departmentid, course_name, course_credit) values (16, 16, 'B-343', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (17, 17, 'B-210', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (18, 18, 'W-100', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (19, 19, 'F-101', 3); insert into Courses (coursenum, departmentid, course_name, course_credit) values (20, 20, 'W-100', 2); /* Students */ insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (1, 'Hermione', 'Karrah', 'Granger', '32076 Stoughton Lane', '104-535-6164'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (2, 'Harry', 'Jeff', 'Potter', '162 Jenna Terrace', '189-170-7576'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (3, 'Draco', 'Will', 'Malfoy', '6 Lukken Place', '317-658-1684'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (4, 'Ron', 'Rudolf', 'Weasley', '1708 Kings Point', '115-202-3406'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (5, 'Edna', 'Wilbur', 'Mode', '7 Sauthoff Terrace', '319-799-8073'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (6, 'Norman', 'William', 'Bates', '14555 Karstens Terrace', '590-948-5633'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (7, 'Wednesday', 'Rae', 'Addams', '5652 Anthes Place', '315-832-6060'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (8, 'Donnie', 'Eli', 'Darko', '15396 Arrowood Park', '251-591-6060'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (9, 'Tony', 'Cori', 'Montana', '403 Maryland Point', '589-132-9638'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (10, 'Katniss', 'Bailie', 'Everdeen', '405 Myrtle Center', '936-703-0453'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (11, 'Ace', 'Jim', 'Ventura', '272 John Wall Park', '535-365-3685'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (12, 'Luke', 'Carl', 'Skywalker', '930 Badeau Place', '816-340-8676'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (13, 'Amy', 'Shawna', 'Dunn', '2767 Hooker Pass', '211-374-8459'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (14, 'Lou', 'Dale', 'Bloom', '00 Dovetail Avenue', '437-327-0254'); insert into Students (studentid, fname, mname, lname, address, emergency_contact) values (15, 'Hannibal', 'Wye', 'Lecter', '70 Johnson Place', '341-843-6655'); /* Students_Phone */ insert into Students_Phone (studentid, studentphone, type) values (1, '695-120-8746', 'Cellphone'); insert into Students_Phone (studentid, studentphone, type) values (2, '697-114-2763', 'Cellphone'); insert into Students_Phone (studentid, studentphone, type) values (3, '764-153-9879', 'Home'); insert into Students_Phone (studentid, studentphone, type) values (4, '238-314-7079', 'Home'); insert into Students_Phone (studentid, studentphone, type) values (5, '563-260-1391', 'Cellphone'); insert into Students_Phone (studentid, studentphone, type) values (6, '545-523-9882', 'Home'); insert into Students_Phone (studentid, studentphone, type) values (7, '501-984-8642', 'Cellphone'); insert into Students_Phone (studentid, studentphone, type) values (8, '113-515-3072', 'Cellphone'); insert into Students_Phone (studentid, studentphone, type) values (9, '528-913-5017', 'Cellphone'); insert into Students_Phone (studentid, studentphone, type) values (10, '421-875-2095', 'Home'); insert into Students_Phone (studentid, studentphone, type) values (11, '528-129-8451', 'Cellphone'); insert into Students_Phone (studentid, studentphone, type) values (12, '439-641-5979', 'Home'); insert into Students_Phone (studentid, studentphone, type) values (13, '776-184-3343', 'Home'); insert into Students_Phone (studentid, studentphone, type) values (14, '132-416-2162', 'Cellphone'); insert into Students_Phone (studentid, studentphone, type) values (15, '808-631-7390', 'Cellphone'); /* Students_email */ insert into Students_Email (studentid, studentemail, type) values (1, '[email protected]', 'school'); insert into Students_Email (studentid, studentemail, type) values (2, '[email protected]', 'school'); insert into Students_Email (studentid, studentemail, type) values (3, '[email protected]', 'work'); insert into Students_Email (studentid, studentemail, type) values (4, '[email protected]', 'personal'); insert into Students_Email (studentid, studentemail, type) values (5, '[email protected]', 'work'); insert into Students_Email (studentid, studentemail, type) values (6, '[email protected]', 'personal'); insert into Students_Email (studentid, studentemail, type) values (7, '[email protected]', 'school'); insert into Students_Email (studentid, studentemail, type) values (8, '[email protected]', 'work'); insert into Students_Email (studentid, studentemail, type) values (9, '[email protected]', 'personal'); insert into Students_Email (studentid, studentemail, type) values (10, '[email protected]', 'work'); insert into Students_Email (studentid, studentemail, type) values (11, '[email protected]', 'school'); insert into Students_Email (studentid, studentemail, type) values (12, '[email protected]', 'school'); insert into Students_Email (studentid, studentemail, type) values (13, '[email protected]', 'work'); insert into Students_Email (studentid, studentemail, type) values (14, '[email protected]', 'work'); insert into Students_Email (studentid, studentemail, type) values (15, '[email protected]', 'personal'); /* Parents */ insert into Parents (parentphone, paddress) values ('931-357-3827', '61 Trailsway Center'); insert into Parents (parentphone, paddress) values ('662-454-7919', '100 Prairieview Way'); insert into Parents (parentphone, paddress) values ('287-265-9340', '9 Gateway Alley'); insert into Parents (parentphone, paddress) values ('732-160-1738', '35 Banding Pass'); insert into Parents (parentphone, paddress) values ('513-564-0505', '89431 Fuller Center'); insert into Parents (parentphone, paddress) values ('857-587-8678', '156 Jay Center'); insert into Parents (parentphone, paddress) values ('663-314-7104', '90 Annamark Park'); insert into Parents (parentphone, paddress) values ('151-952-7800', '605 8th Street'); insert into Parents (parentphone, paddress) values ('831-985-1312', '00033 Goodland Terrace'); insert into Parents (parentphone, paddress) values ('597-765-8439', '29 Forest Dale Circle'); insert into Parents (parentphone, paddress) values ('387-962-1747', '895 Washington Court'); insert into Parents (parentphone, paddress) values ('289-931-1149', '70 Judy Plaza'); insert into Parents (parentphone, paddress) values ('743-626-4973', '1474 Hanson Point'); insert into Parents (parentphone, paddress) values ('779-457-0362', '8553 Jana Parkway'); insert into Parents (parentphone, paddress) values ('261-763-4614', '70 Lillian Center'); /* major */ insert into majors (studentid, departmentid, name, school, requirements) values (1, 1, 'Business', 'Kelley', '15'); insert into majors (studentid, departmentid, name, school, requirements) values (2, 2, 'Biology', 'Arts & Sciences', '15'); insert into majors (studentid, departmentid, name, school, requirements) values (3, 3, 'Informatics', 'Luddy', '15'); insert into majors (studentid, departmentid, name, school, requirements) values (4, 4, 'Art-Education', 'School of Education', '15'); /* Semester */ insert into semester (semesterid, term, year) values ('FA18', 'Fall', 2018); insert into semester (semesterid, term, year) values ('SP18', 'Spring', 2018); insert into semester (semesterid, term, year) values ('FA19', 'Fall', 2019); insert into semester (semesterid, term, year) values ('SP19', 'Spring', 2019); /* Sections */ insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (1, 1, 1, 1, 'FA18', 'MW', '09-00-00', '10-30-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (2, 2, 2, 2, 'FA18', 'TuTh', '09-00-00', '10-30-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (3, 3, 3, 3, 'FA18', 'MW', '11-00-00', '11-55-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (4, 4, 4, 4, 'FA18', 'TuTh', '11-00-00', '11-55-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (5, 5, 5, 5, 'SP18', 'MW', '09-00-00', '10-30-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (6, 6, 6, 6, 'SP18', 'TuTh', '09-00-00', '10-30-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (7, 7, 7, 7, 'FA19', 'TuTh', '09-00-00', '10-30-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (8, 8, 8, 8, 'FA19', 'MW', '09-00-00', '10-30-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (9, 9, 9, 9, 'FA19', 'MW', '11-00-00', '11-55-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (10, 10, 10, 'SP19', 'TuTh', '11-00-00', '11-55-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (11, 11, 11, 'SP19', 'MW', '09-00-00', '10-30-00'); insert into Sections (sectionid, coursenum, empid, roomnum, semesterid, day, startime, endtime) values (12, 12, 12, 'SP19', 'TuTh', '09-00-00', '10-30-00'); /* GPA?? */ insert into GPA (letter_grade, credit_points) values ('A', 4); insert into GPA (letter_grade, credit_points) values ('A', 4); insert into GPA (letter_grade, credit_points) values ('B', 3); insert into GPA (letter_grade, credit_points) values ('B', 3); insert into GPA (letter_grade, credit_points) values ('B', 3); insert into GPA (letter_grade, credit_points) values ('C', 2); insert into GPA (letter_grade, credit_points) values ('C', 2); insert into GPA (letter_grade, credit_points) values ('A', 4); insert into GPA (letter_grade, credit_points) values ('A', 4); insert into GPA (letter_grade, credit_points) values ('A', 4); insert into GPA (letter_grade, credit_points) values ('B', 3); insert into GPA (letter_grade, credit_points) values ('B', 3); insert into GPA (letter_grade, credit_points) values ('B', 3); insert into GPA (letter_grade, credit_points) values ('C', 2); insert into GPA (letter_grade, credit_points) values ('C', 2); /* Grades */ insert into Grades (studentid, sectionid, letter_grade) values (1, 1, 'A'); insert into Grades (studentid, sectionid, letter_grade) values (2, 2, 'B'); insert into Grades (studentid, sectionid, letter_grade) values (3, 3, 'A'); insert into Grades (studentid, sectionid, letter_grade) values (4, 4, 'B'); insert into Grades (studentid, sectionid, letter_grade) values (5, 5, 'C'); insert into Grades (studentid, sectionid, letter_grade) values (6, 6, 'C'); insert into Grades (studentid, sectionid, letter_grade) values (7, 7, 'B'); insert into Grades (studentid, sectionid, letter_grade) values (8, 8, 'C'); insert into Grades (studentid, sectionid, letter_grade) values (9, 9, 'B'); insert into Grades (studentid, sectionid, letter_grade) values (10, 10, 'B'); insert into Grades (studentid, sectionid, letter_grade) values (11, 11, 'C'); insert into Grades (studentid, sectionid, letter_grade) values (12, 12, 'A'); insert into Grades (studentid, sectionid, letter_grade) values (13, 13, 'A'); insert into Grades (studentid, sectionid, letter_grade) values (14, 14, 'B'); insert into Grades (studentid, sectionid, letter_grade) values (15, 15, 'B');