Difference between revisions of "CREATE TABLE"

From SQLZOO
Jump to: navigation, search
m (Reverted edits by 117.212.90.88 (talk) to last revision by Andr3w)
Line 1: Line 1:
 +
DROP TABLE IF EXISTS STUDENT;
 +
DROP TABLE IF EXISTS CAMPUS;
 +
DROP TABLE IF EXISTS SEMESTER;
 +
DROP TABLE IF EXISTS ROOM;
 +
DROP TABLE IF EXISTS GRADES;
 +
DROP TABLE IF EXISTS COURSE;
 +
DROP TABLE IF EXISTS INSTRUCTOR;
 +
DROP TABLE IF EXISTS APPROVED_INSTRUCTOR;
 +
DROP TABLE IF EXISTS CLASS;
 +
DROP TABLE IF EXISTS STUDENT_GRADE;
  
<h1>CREATE TABLE</h1>
+
CREATE TABLE STUDENT(
<p>The table <code>games</code> shows the year and the city hosting the Olympic Games.</p>
+
STUDENT_ID  INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
<table>
+
Last_Name    VARCHAR(30),
<caption align='center'>games</caption>
+
First_Name  VARCHAR(30),
<tr><th align='center'>'''yr'''</th><th align='center'>'''city'''</th></tr>
+
Address      VARCHAR(40),
<tr><td>2004</td><td align='left'>Athens</td></tr>
+
ZIP          VARCHAR(10),
<tr><td>2008</td><td align='left'>Beijing</td></tr>
+
MAJOR        VARCHAR(20),
</table>
+
CLASS      VARCHAR(25),
<div class='ht'>
+
STATUS      VARCHAR(20));
<div class=params>schema:scott</div>
+
 
<source lang=sql class='tidy'> DROP TABLE games</source>
+
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
The CREATE statement builds an empty table to hold rows.
+
VALUES('Mills','Rana','P.O. Box 619, 5808 Semper St.','32641','MATH','2','FAILING');
In this example the column yr is the PRIMARY KEY:
+
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
<source lang='sql' class='def'>
+
VALUES('Snow','Holmes','313-6948 Nunc, Ave','36702','SPANISH','3','SATISFACTORY');
CREATE TABLE games
+
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
(yr  INT NOT NULL PRIMARY KEY
+
VALUES('Woodward','Dustin','Ap #593-8291 Id St.','26151','BUSINESS','1','SATISFACTORY');
,city VARCHAR(20)
+
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Dillon','Casey','328-4572 Elit Rd.','95886','SPANISH','2','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Stevenson','Sydney','Ap #547-7314 Feugiat. Street','42756','PHIL','1','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Alford','Karina','P.O. Box 977, 6922 Sed Street','52838','ENGLISH','1','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Ferrell','Dahlia','Ap #179-4485 Lectus Road','10816','ENGLISH','3','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Strickland','Victor','Ap #878-2252 Curabitur Av.','53175','PHYSICS','3','SATISFACTORY');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Frost','Sasha','Ap #552-758 Convallis St.','56968','FINANCE','3','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Calhoun','Cameron','Ap #145-1961 Sed Street','63397','BUSINESS','3','SATISFACTORY');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Gonzalez','Jakeem','Ap #141-9425 In Road','82622','BUSINESS','4','SATISFACTORY');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Michael','Amethyst','Ap #412-5802 Venenatis Rd.','77696','MARKETING','2','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Cooper','Cole','911-8666 A Rd.','97912','CS','2','SATISFACTORY');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Petersen','Griffin','5759 Nec, Street','23461','SPANISH','3','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Petersen','Lawrence','Ap #642-7393 Ut, Ave','37311','PHIL','1','SATISFACTORY');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Crane','Sheila','8659 Arcu. Avenue','91267','PHYSICS','2','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Gilliam','Oscar','3512 Pellentesque. St.','33481','MATH','4','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Francis','Zoe','285-9157 Proin Road','98674','CS','1','SATISFACTORY');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Cohen','Aurora','P.O. Box 957, 9450 Arcu Street','30350','PHIL','3','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Black','Joshua','3655 Sed St.','95684','FRENCH','2','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Barber','Patience','875-9349 Magnis St.','66498','PSY','4','SATISFACTORY');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Preston','Inez','Ap #829-845 Nisi Av.','27404','PSY','1','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Dunn','Blake','439-1369 Cursus Av.','60644','MARKETING','2','FAILING');
 +
INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS)
 +
VALUES('Ashley','Vaughan','7513 Vitae Street','61245','MARKETING','2','FAILING');
 +
 
 +
 
 +
CREATE TABLE CAMPUS
 +
(
 +
CAMPUS_NAME  VARCHAR(45)  NOT NULL,
 +
Address      VARCHAR(40),
 +
ZIP          VARCHAR(10),
 +
PHONE_NO    VARCHAR(20),
 +
UNIQUE(CAMPUS_NAME),
 +
PRIMARY KEY(CAMPUS_NAME));
 +
 
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Newport Campus','405-6298 In Street','62840','1-639-674-3520');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Colli a Volturno Campus','820-7808 Risus, Rd.','51852','1-824-430-8362');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Lens-Saint-Remy Campus','505-182 Luctus, Ave','21345','1-460-439-2331');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Largs Campus','P.O. Box 215, 1469 Eu Rd.','52681','1-448-789-7971');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Liverpool Campus','631-5139 Leo. St.','29402','1-282-625-5720');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Dawson Creek Campus','6161 Nostra, Road','83643','1-547-459-9473');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Vremde Campus','9309 Cras Road','23421','1-680-673-7298');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Minto Campus','P.O. Box 878, 7621 Donec Road','73261','1-755-573-0468');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Werzburg Campus','P.O. Box 406, 9600 Odio Ave','32802','1-721-423-4315');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Motherwell Campus','7574 Semper St.','21211','1-583-233-9026');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Liedekerke Campus','6939 Ligula. St.','75022','1-941-394-7241');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Gentbrugge Campus','7422 Vel Avenue','63763','1-604-359-7505');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Forbach Campus','P.O. Box 328, 5280 Donec Rd.','75068','1-865-208-5487');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Boorsem Campus','788-7924 Tellus. Street','75088','1-260-552-0600');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Valpelline Campus','518-7627 Ac Avenue','89090','1-262-155-0231');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Gagliato Campus','921-4825 Nullam Rd.','23117','1-324-211-7180');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Varsenare Campus','Ap #202-6418 Eleifend Ave','54674','1-663-374-3235');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Tuscaloosa Campus','927-3569 Id Road','71661','1-315-710-9509');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Champorcher Campus','8637 Ullamcorper Rd.','10001','1-224-161-4524');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Lasne-Chapelle-Saint-Lambert Campus','P.O. Box 169, 7139 Arcu Rd.','83588','1-789-125-9687');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Sint-Gillis-bij-Dendermonde Campus','744-3904 Vulputate, Road','37762','1-640-455-9785');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Curridabat Campus','2849 At Ave','40882','1-299-696-4483');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Notre-Dame-du-Nord Campus','P.O. Box 785, 7330 Urna. St.','55794','1-789-114-1794');
 +
INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO)
 +
VALUES('Kurnool Campus','172-3924 At Ave','31619','1-111-206-2900');
 +
 
 +
 
 +
CREATE TABLE SEMESTER
 +
(
 +
SEMESTER_NAME ENUM('FALL', 'SPRING', 'SUMMER'));
 +
 
 +
DROP TABLE IF EXISTS ROOM CASCADE;
 +
 
 +
CREATE TABLE ROOM
 +
(
 +
Campus_Name  VARCHAR(45),
 +
ROOM_NO      VARCHAR(10),
 +
FACILITY    VARCHAR(45),
 +
CAPACITY    VARCHAR(10)
 
);
 
);
INSERT INTO games(yr,city) VALUES (2004,'Athens');
 
INSERT INTO games(yr,city) VALUES (2008,'Beijing');
 
SELECT * FROM games;
 
</source>
 
</div>
 
  
<p>See also</p>
+
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
<ul>
+
VALUES('Newport Campus','6','Lloydminster','30');
   <li>[[INSERT_.._VALUES |INSERT]]</li>
+
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
  <li>[[DROP |DROP]]</li>
+
VALUES('Colli a Volturno Campus','19','Erode','32');
</ul>
+
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Lens-Saint-Remy Campus','40','Cantalupo in Sabina','38');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Largs Campus','40','Assiniboia','25');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Liverpool Campus','23','Kessenich','42');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Dawson Creek Campus','47','Stony Plain','46');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Vremde Campus','29','Ulm','44');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Minto Campus','46','Bielefeld','39');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Werzburg Campus','37','Georgia','38');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Motherwell Campus','29','Lummen','30');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Liedekerke Campus','3','Pak Pattan','33');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Gentbrugge Campus','37','South Perth','27');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Forbach Campus','35','Meeuwen','14');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Boorsem Campus','20','Belcarra','23');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Valpelline Campus','5','Jette','27');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Gagliato Campus','24','Milwaukee','18');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Varsenare Campus','23','Afsnee','39');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Champorcher Campus','50','Stamford','26');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Tuscaloosa Campus','5','Stamford','21');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Lasne-Chapelle-Saint-Lambert Campus','40','Morgex','11');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Sint-Gillis-bij-Dendermonde Campus','1','Montague','21');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Curridabat Campus','40','Cumberland','21');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Notre-Dame-du-Nord Campus','4','Harrison Hot Springs','32');
 +
INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY)
 +
VALUES('Kurnool Campus','10','Sainte-Ode','15');
 +
 
 +
 
 +
CREATE TABLE GRADES
 +
(
 +
Grade ENUM('A','B','C','D','F','I','W')
 +
);
 +
 
 +
 
 +
CREATE TABLE COURSE
 +
(
 +
Course_No    Varchar(10)  NOT NULL,
 +
Course_Name  Varchar(25),
 +
CreditHours  VARCHAR(5),
 +
UNIQUE(COURSE_NO),
 +
PRIMARY KEY(COURSE_NO)
 +
);
 +
 
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1043','TECH121','1');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1006','Psy101','3');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1696','Psy101','2');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1291','HUM234','2');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1841','Psy101','3');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1697','ELE102','2');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1292','LIT405','2');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1698','ELE101','3');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1255','Math101','2');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1842','TECH121','3');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1010','Psy101','3');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1810','Psy101','1');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1044','Math131','3');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1293','Math131','1');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1528','CS234','2');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1324','PHY345','2');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1045','Math101','1');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1659','CS211','1');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1294','PHY345','3');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1144','Psy101','2');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1843','ELE102','1');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1569','Math101','3');
 +
INSERT INTO COURSE(Course_No,Course_Name,CreditHours)
 +
VALUES('1811','ENG304','3');
 +
 
 +
 
 +
CREATE TABLE INSTRUCTOR
 +
(
 +
INSTRUCTOR_ID VARCHAR(10)   NOT NULL,
 +
LAST_NAME    VARCHAR(30),
 +
FIRST_NAME    VARCHAR(20),
 +
ADDRESS      VARCHAR(40),
 +
ZIP          VARCHAR(10),
 +
OFFICE_NO    VARCHAR(10),
 +
UNIQUE(INSTRUCTOR_ID),
 +
PRIMARY KEY(INSTRUCTOR_ID)
 +
);
 +
 
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('400','Thane','Puckett','P.O. Box 895, 191 Erat Rd.','53841','30');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('737','Jared','Tate','Ap #177-1334 Faucibus St.','88675','21');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('640','Yuli','Hurst','Ap #799-5088 Scelerisque St.','26162','12');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('256','Micah','Pace','P.O. Box 638, 2109 Adipiscing Rd.','40665','12');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('741','Kasper','Duran','565-193 Nunc Avenue','55756','17');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('653','Dolan','Moore','3869 Cursus Road','37236','10');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('688','Ciaran','Blackwell','Ap #754-6723 Faucibus Rd.','83479','17');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('940','Kennedy','Fowler','749-1142 Non Av.','98172','22');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('499','Devin','Acosta','7613 Vel Road','93686','19');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('280','Driscoll','Justice','P.O. Box 113, 3705 Ut, St.','69877','23');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('937',',Cairo','Donaldson','6470 Adipiscing Rd.','63032','26');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('415','Hammett','Glenn','262-2645 At Rd.','84839','22');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('549','Elliott','Donaldson','5470 Dolor. Street','62489','23');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('639','Lamar','Robles','5719 Consectetuer St.','20213','25');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('767','Colin','Riley','P.O. Box 114, 1663 Ultrices. Street','23254','16');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('344','Richard','Thornton','P.O. Box 931, 8187 Lacinia. St.','29349','23');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('581','Raymond','Snow','1032 Est St.','66699','20');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('474','Nero','Rhodes','6050 Ante Ave','37967','28');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('534','Tate','Carr','P.O. Box 213, 1879 Purus. Ave','19803','15');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('521','Lance','Waters','656-7361 Conubia Ave','6315','26');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('413','Abbot','Ross','P.O. Box 859, 9379 Sed Avenue','45051','25');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('891','Dylan','Forbes','225-3164 Fermentum Ave','65642','26');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('879','Arsenio','Singleton','505-6318 Vel, St.','97288','10');
 +
INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO)
 +
VALUES('350','Thor','Copeland','Ap #816-3443 Magna. Avenue','43447','4');
 +
 
 +
 
 +
CREATE TABLE APPROVED_INSTRUCTOR
 +
(
 +
Instructor_ID VARCHAR(10) NOT NULL,
 +
Course_No    VARCHAR(10) NOT NULL
 +
);
 +
 
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('400','1044');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('737','1528');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('640','1324');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('256','1842');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('741','1006');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('653','1431');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('688','1291');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('940','1810');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('499','1841');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('280','1211');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('937','1144');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('415','1255');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('549','1033');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('639','1881');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('767','1526');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('344','1249');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('581','1659');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('474','1298');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('534','1569');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('521','1192');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('413','1722');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('891','1511');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('879','1696');
 +
INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)
 +
VALUES('350','1844');
 +
 
 +
Create TABLE CLASS
 +
(
 +
Class_id      VARCHAR(10) NOT NULL,
 +
Course_No    VARCHAR(10) NOT NULL,
 +
Section      VARCHAR(10),
 +
Semester      Varchar(15),
 +
Year          VARCHAR(4),
 +
Instructor_ID VARCHAR(10) NOT NULL,
 +
Campus        Varchar(15),
 +
Room_No      VARCHAR(10),
 +
Start_Date    Date,
 +
Start_Time    Time,
 +
UNIQUE (CLass_id),
 +
PRIMARY KEY (Class_id)
 +
);
 +
 
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('12408','1044','Fall','499','2014-10-08','8:00','10','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('15716','1006','Other','639','2014-10-08','8:00','17','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('12525','1291',' Other','767','2014-10-08','8:00','18','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('16500','1696','Winter','937','2015-01-03','8:00','19','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('19047','1291','Winter','280','2015-01-03','8:00','14','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('12587','1841','Other','280','2014-08-10','8:00','15','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('17106','1696','Other','400','2014-08-10','8:00','10','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('14331','1291','Other','413','2014-08-10','8:00','14','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('17246','1696','Summer','640','2015-06-12','8:00','12','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('17092','1255','Summer','639','2015-06-12','8:00','10','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('14169','1842','Fall','344','2014-08-10','8:00','13','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('13062','1006','Winter','879','2015-01-03','8:00','12','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('17267','1810','Summer','640','2015-06-12','8:00','15','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('15415','1044','Summer','549','2015-06-12','8:00','15','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('11386','1291','Fall','767','2014-08-10','8:00','10','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('14972','1528','Other','937','2014-08-10','8:00','17','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('15009','1324','Fall','256','2014-08-10','8:00','11','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('18265','1044','Winter','415','2015-01-03','8:00','12','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('16415','1659','Fall','474','2014-08-10','8:00','14','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('19464','1291','Fall','521','2014-08-10','8:00','18','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('12433','1144','Summer','415','2015-06-12','8:00','10','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('19175','1842','Winter','534','2015-01-03','8:00','10','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('19864','1569','Summer','280','2015-06-12','8:00','14','2014');
 +
INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year)
 +
VALUES('17022','1810','Other','940','2014-08-10','8:00','15','2014');
 +
 
 +
 
 +
 
 +
 
 +
CREATE TABLE STUDENT_GRADE
 +
(
 +
Student_ID    MEDIUMINT NOT NULL AUTO_INCREMENT,
 +
Class_ID      VARCHAR(10),
 +
Student_Grade ENUM('A','B','C','D','F','I','W'),
 +
PRIMARY KEY(Student_ID)
 +
);
 +
 
 +
Commit;
 +
 
 +
SHOW TABLES;

Revision as of 18:16, 8 November 2015

DROP TABLE IF EXISTS STUDENT; DROP TABLE IF EXISTS CAMPUS; DROP TABLE IF EXISTS SEMESTER; DROP TABLE IF EXISTS ROOM; DROP TABLE IF EXISTS GRADES; DROP TABLE IF EXISTS COURSE; DROP TABLE IF EXISTS INSTRUCTOR; DROP TABLE IF EXISTS APPROVED_INSTRUCTOR; DROP TABLE IF EXISTS CLASS; DROP TABLE IF EXISTS STUDENT_GRADE;

CREATE TABLE STUDENT( STUDENT_ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, Last_Name VARCHAR(30), First_Name VARCHAR(30), Address VARCHAR(40), ZIP VARCHAR(10), MAJOR VARCHAR(20), CLASS VARCHAR(25), STATUS VARCHAR(20));

INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Mills','Rana','P.O. Box 619, 5808 Semper St.','32641','MATH','2','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Snow','Holmes','313-6948 Nunc, Ave','36702','SPANISH','3','SATISFACTORY'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Woodward','Dustin','Ap #593-8291 Id St.','26151','BUSINESS','1','SATISFACTORY'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Dillon','Casey','328-4572 Elit Rd.','95886','SPANISH','2','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Stevenson','Sydney','Ap #547-7314 Feugiat. Street','42756','PHIL','1','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Alford','Karina','P.O. Box 977, 6922 Sed Street','52838','ENGLISH','1','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Ferrell','Dahlia','Ap #179-4485 Lectus Road','10816','ENGLISH','3','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Strickland','Victor','Ap #878-2252 Curabitur Av.','53175','PHYSICS','3','SATISFACTORY'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Frost','Sasha','Ap #552-758 Convallis St.','56968','FINANCE','3','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Calhoun','Cameron','Ap #145-1961 Sed Street','63397','BUSINESS','3','SATISFACTORY'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Gonzalez','Jakeem','Ap #141-9425 In Road','82622','BUSINESS','4','SATISFACTORY'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Michael','Amethyst','Ap #412-5802 Venenatis Rd.','77696','MARKETING','2','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Cooper','Cole','911-8666 A Rd.','97912','CS','2','SATISFACTORY'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Petersen','Griffin','5759 Nec, Street','23461','SPANISH','3','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Petersen','Lawrence','Ap #642-7393 Ut, Ave','37311','PHIL','1','SATISFACTORY'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Crane','Sheila','8659 Arcu. Avenue','91267','PHYSICS','2','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Gilliam','Oscar','3512 Pellentesque. St.','33481','MATH','4','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Francis','Zoe','285-9157 Proin Road','98674','CS','1','SATISFACTORY'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Cohen','Aurora','P.O. Box 957, 9450 Arcu Street','30350','PHIL','3','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Black','Joshua','3655 Sed St.','95684','FRENCH','2','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Barber','Patience','875-9349 Magnis St.','66498','PSY','4','SATISFACTORY'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Preston','Inez','Ap #829-845 Nisi Av.','27404','PSY','1','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Dunn','Blake','439-1369 Cursus Av.','60644','MARKETING','2','FAILING'); INSERT INTO STUDENT (Last_Name, First_Name, Address, ZIP, MAJOR, CLASS, STATUS) VALUES('Ashley','Vaughan','7513 Vitae Street','61245','MARKETING','2','FAILING');


CREATE TABLE CAMPUS ( CAMPUS_NAME VARCHAR(45) NOT NULL, Address VARCHAR(40), ZIP VARCHAR(10), PHONE_NO VARCHAR(20), UNIQUE(CAMPUS_NAME), PRIMARY KEY(CAMPUS_NAME));

INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Newport Campus','405-6298 In Street','62840','1-639-674-3520'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Colli a Volturno Campus','820-7808 Risus, Rd.','51852','1-824-430-8362'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Lens-Saint-Remy Campus','505-182 Luctus, Ave','21345','1-460-439-2331'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Largs Campus','P.O. Box 215, 1469 Eu Rd.','52681','1-448-789-7971'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Liverpool Campus','631-5139 Leo. St.','29402','1-282-625-5720'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Dawson Creek Campus','6161 Nostra, Road','83643','1-547-459-9473'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Vremde Campus','9309 Cras Road','23421','1-680-673-7298'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Minto Campus','P.O. Box 878, 7621 Donec Road','73261','1-755-573-0468'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Werzburg Campus','P.O. Box 406, 9600 Odio Ave','32802','1-721-423-4315'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Motherwell Campus','7574 Semper St.','21211','1-583-233-9026'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Liedekerke Campus','6939 Ligula. St.','75022','1-941-394-7241'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Gentbrugge Campus','7422 Vel Avenue','63763','1-604-359-7505'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Forbach Campus','P.O. Box 328, 5280 Donec Rd.','75068','1-865-208-5487'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Boorsem Campus','788-7924 Tellus. Street','75088','1-260-552-0600'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Valpelline Campus','518-7627 Ac Avenue','89090','1-262-155-0231'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Gagliato Campus','921-4825 Nullam Rd.','23117','1-324-211-7180'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Varsenare Campus','Ap #202-6418 Eleifend Ave','54674','1-663-374-3235'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Tuscaloosa Campus','927-3569 Id Road','71661','1-315-710-9509'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Champorcher Campus','8637 Ullamcorper Rd.','10001','1-224-161-4524'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Lasne-Chapelle-Saint-Lambert Campus','P.O. Box 169, 7139 Arcu Rd.','83588','1-789-125-9687'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Sint-Gillis-bij-Dendermonde Campus','744-3904 Vulputate, Road','37762','1-640-455-9785'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Curridabat Campus','2849 At Ave','40882','1-299-696-4483'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Notre-Dame-du-Nord Campus','P.O. Box 785, 7330 Urna. St.','55794','1-789-114-1794'); INSERT INTO CAMPUS (CAMPUS_NAME, Address, ZIP, PHONE_NO) VALUES('Kurnool Campus','172-3924 At Ave','31619','1-111-206-2900');


CREATE TABLE SEMESTER ( SEMESTER_NAME ENUM('FALL', 'SPRING', 'SUMMER'));

DROP TABLE IF EXISTS ROOM CASCADE;

CREATE TABLE ROOM ( Campus_Name VARCHAR(45), ROOM_NO VARCHAR(10), FACILITY VARCHAR(45), CAPACITY VARCHAR(10) );

INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Newport Campus','6','Lloydminster','30'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Colli a Volturno Campus','19','Erode','32'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Lens-Saint-Remy Campus','40','Cantalupo in Sabina','38'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Largs Campus','40','Assiniboia','25'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Liverpool Campus','23','Kessenich','42'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Dawson Creek Campus','47','Stony Plain','46'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Vremde Campus','29','Ulm','44'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Minto Campus','46','Bielefeld','39'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Werzburg Campus','37','Georgia','38'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Motherwell Campus','29','Lummen','30'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Liedekerke Campus','3','Pak Pattan','33'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Gentbrugge Campus','37','South Perth','27'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Forbach Campus','35','Meeuwen','14'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Boorsem Campus','20','Belcarra','23'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Valpelline Campus','5','Jette','27'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Gagliato Campus','24','Milwaukee','18'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Varsenare Campus','23','Afsnee','39'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Champorcher Campus','50','Stamford','26'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Tuscaloosa Campus','5','Stamford','21'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Lasne-Chapelle-Saint-Lambert Campus','40','Morgex','11'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Sint-Gillis-bij-Dendermonde Campus','1','Montague','21'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Curridabat Campus','40','Cumberland','21'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Notre-Dame-du-Nord Campus','4','Harrison Hot Springs','32'); INSERT INTO ROOM(Campus_Name,ROOM_NO,FACILITY,CAPACITY) VALUES('Kurnool Campus','10','Sainte-Ode','15');


CREATE TABLE GRADES ( Grade ENUM('A','B','C','D','F','I','W') );


CREATE TABLE COURSE ( Course_No Varchar(10) NOT NULL, Course_Name Varchar(25), CreditHours VARCHAR(5), UNIQUE(COURSE_NO), PRIMARY KEY(COURSE_NO) );

INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1043','TECH121','1'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1006','Psy101','3'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1696','Psy101','2'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1291','HUM234','2'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1841','Psy101','3'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1697','ELE102','2'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1292','LIT405','2'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1698','ELE101','3'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1255','Math101','2'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1842','TECH121','3'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1010','Psy101','3'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1810','Psy101','1'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1044','Math131','3'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1293','Math131','1'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1528','CS234','2'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1324','PHY345','2'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1045','Math101','1'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1659','CS211','1'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1294','PHY345','3'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1144','Psy101','2'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1843','ELE102','1'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1569','Math101','3'); INSERT INTO COURSE(Course_No,Course_Name,CreditHours) VALUES('1811','ENG304','3');


CREATE TABLE INSTRUCTOR ( INSTRUCTOR_ID VARCHAR(10) NOT NULL, LAST_NAME VARCHAR(30), FIRST_NAME VARCHAR(20), ADDRESS VARCHAR(40), ZIP VARCHAR(10), OFFICE_NO VARCHAR(10), UNIQUE(INSTRUCTOR_ID), PRIMARY KEY(INSTRUCTOR_ID) );

INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('400','Thane','Puckett','P.O. Box 895, 191 Erat Rd.','53841','30'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('737','Jared','Tate','Ap #177-1334 Faucibus St.','88675','21'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('640','Yuli','Hurst','Ap #799-5088 Scelerisque St.','26162','12'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('256','Micah','Pace','P.O. Box 638, 2109 Adipiscing Rd.','40665','12'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('741','Kasper','Duran','565-193 Nunc Avenue','55756','17'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('653','Dolan','Moore','3869 Cursus Road','37236','10'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('688','Ciaran','Blackwell','Ap #754-6723 Faucibus Rd.','83479','17'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('940','Kennedy','Fowler','749-1142 Non Av.','98172','22'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('499','Devin','Acosta','7613 Vel Road','93686','19'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('280','Driscoll','Justice','P.O. Box 113, 3705 Ut, St.','69877','23'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('937',',Cairo','Donaldson','6470 Adipiscing Rd.','63032','26'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('415','Hammett','Glenn','262-2645 At Rd.','84839','22'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('549','Elliott','Donaldson','5470 Dolor. Street','62489','23'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('639','Lamar','Robles','5719 Consectetuer St.','20213','25'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('767','Colin','Riley','P.O. Box 114, 1663 Ultrices. Street','23254','16'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('344','Richard','Thornton','P.O. Box 931, 8187 Lacinia. St.','29349','23'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('581','Raymond','Snow','1032 Est St.','66699','20'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('474','Nero','Rhodes','6050 Ante Ave','37967','28'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('534','Tate','Carr','P.O. Box 213, 1879 Purus. Ave','19803','15'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('521','Lance','Waters','656-7361 Conubia Ave','6315','26'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('413','Abbot','Ross','P.O. Box 859, 9379 Sed Avenue','45051','25'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('891','Dylan','Forbes','225-3164 Fermentum Ave','65642','26'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('879','Arsenio','Singleton','505-6318 Vel, St.','97288','10'); INSERT INTO INSTRUCTOR(Instructor_ID,FIRST_NAME,LAST_NAME,ADDRESS,ZIP,OFFICE_NO) VALUES('350','Thor','Copeland','Ap #816-3443 Magna. Avenue','43447','4');


CREATE TABLE APPROVED_INSTRUCTOR ( Instructor_ID VARCHAR(10) NOT NULL, Course_No VARCHAR(10) NOT NULL );

INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('400','1044'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('737','1528'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('640','1324'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('256','1842'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('741','1006'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('653','1431'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('688','1291'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('940','1810'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('499','1841'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('280','1211'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('937','1144'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('415','1255'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('549','1033'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('639','1881'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('767','1526'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('344','1249'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('581','1659'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('474','1298'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('534','1569'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('521','1192'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('413','1722'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('891','1511'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('879','1696'); INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No) VALUES('350','1844');

Create TABLE CLASS ( Class_id VARCHAR(10) NOT NULL, Course_No VARCHAR(10) NOT NULL, Section VARCHAR(10), Semester Varchar(15), Year VARCHAR(4), Instructor_ID VARCHAR(10) NOT NULL, Campus Varchar(15), Room_No VARCHAR(10), Start_Date Date, Start_Time Time, UNIQUE (CLass_id), PRIMARY KEY (Class_id) );

INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('12408','1044','Fall','499','2014-10-08','8:00','10','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('15716','1006','Other','639','2014-10-08','8:00','17','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('12525','1291',' Other','767','2014-10-08','8:00','18','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('16500','1696','Winter','937','2015-01-03','8:00','19','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('19047','1291','Winter','280','2015-01-03','8:00','14','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('12587','1841','Other','280','2014-08-10','8:00','15','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('17106','1696','Other','400','2014-08-10','8:00','10','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('14331','1291','Other','413','2014-08-10','8:00','14','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('17246','1696','Summer','640','2015-06-12','8:00','12','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('17092','1255','Summer','639','2015-06-12','8:00','10','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('14169','1842','Fall','344','2014-08-10','8:00','13','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('13062','1006','Winter','879','2015-01-03','8:00','12','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('17267','1810','Summer','640','2015-06-12','8:00','15','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('15415','1044','Summer','549','2015-06-12','8:00','15','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('11386','1291','Fall','767','2014-08-10','8:00','10','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('14972','1528','Other','937','2014-08-10','8:00','17','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('15009','1324','Fall','256','2014-08-10','8:00','11','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('18265','1044','Winter','415','2015-01-03','8:00','12','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('16415','1659','Fall','474','2014-08-10','8:00','14','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('19464','1291','Fall','521','2014-08-10','8:00','18','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('12433','1144','Summer','415','2015-06-12','8:00','10','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('19175','1842','Winter','534','2015-01-03','8:00','10','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('19864','1569','Summer','280','2015-06-12','8:00','14','2014'); INSERT INTO CLASS(Class_ID,Course_No,Semester,Instructor_ID,Start_Date,Start_Time,Section,Year) VALUES('17022','1810','Other','940','2014-08-10','8:00','15','2014');



CREATE TABLE STUDENT_GRADE ( Student_ID MEDIUMINT NOT NULL AUTO_INCREMENT, Class_ID VARCHAR(10), Student_Grade ENUM('A','B','C','D','F','I','W'), PRIMARY KEY(Student_ID) );

Commit;

SHOW TABLES;