MXL30HcT

1drop database if exists university;
2create database university;
3use University;
4
5drop table if exists student;
6drop table if exists faculty;
7drop table if exists class;
8
9create table student(
10stuid varchar(10) PRIMARY KEY,
11lastname varchar(15),
12firstname varchar(15),
13major varchar(15),
14credits int);
15
16create table faculty(
17facid varchar(10) primary key,
18fullname varchar(15), /*name is a keyword*/
19department varchar(15),
20rankNum varchar(15)); /*rank is keyword*/
21
22
23create table class(
24classNumber varchar(15) primary Key,
25facID varchar(15),
26schedul varchar(15),
27room varchar(15),
28foreign key(facID) references faculty(facid)
29);
30
31create table enroll(
32stuid varchar(15) not null,
33classNumber varchar(15) not null,
34grade varchar(2),
35primary key( stuid, classNumber ),
36foreign key( stuid ) references student(stuid),
37foreign key( classNumber ) references class(classNumber)
38);
39
40
41INSERT INTO Student (stuId, lastName, firstName, major, credits)
42VALUES ('S1001','Smith','Tom','History',90);
43INSERT INTO Student (stuId, lastName, firstName, major, credits)
44VALUES ('S1002','Chin','Ann','Math',36);
45INSERT INTO Student (stuId, lastName, firstName, major, credits)
46VALUES ('S1005','Lee','Perry','History',3);
47INSERT INTO Student (stuId, lastName, firstName, major, credits)
48VALUES ('S1010','Burns','Edward','Art',63);
49INSERT INTO Student (stuId, lastName, firstName, major, credits)
50VALUES ('S1013','McCarthy','Owen','Math',0);
51INSERT INTO Student (stuId, lastName, firstName, major, credits)
52VALUES ('S1015','Jones','Mary','Math',42);
53INSERT INTO Student (stuId, lastName, firstName, major, credits)
54VALUES ('S1020','Rivera','Jane','CSC',15);
55INSERT INTO Faculty (facId, fullname, department, rankNum)
56VALUES ('F101','Adams','Art','Professor');
57INSERT INTO Faculty (facId, fullname, department, rankNum)
58VALUES ('F105','Tanaka','CSC','Instructor');
59INSERT INTO Faculty (facId, fullname, department, rankNum)
60VALUES ('F110','Byrne','Math','Assistant');
61INSERT INTO Faculty (facId, fullname, department, rankNum)
62VALUES ('F115','Smith','History','Associate');
63INSERT INTO Faculty (facId, fullname, department, rankNum)
64VALUES ('F221','Smith','CSC','Professor');
65INSERT INTO Class (classNumber, facId, schedul, room)
66VALUES ('ART103A','F101','MWF9','H221');
67INSERT INTO Class (classNumber, facId, schedul, room)
68VALUES ('CSC201A','F105','TuThF10','M110');
69INSERT INTO Class (classNumber, facId, schedul, room)
70VALUES ('CSC203A','F105','MThF12','M110');
71INSERT INTO Class (classNumber, facId, schedul, room)
72VALUES ('HST205A','F115','MWF11','H221');
73INSERT INTO Class (classNumber, facId, schedul, room)
74VALUES ('MTH101B','F110','MTuTh9','H225');
75INSERT INTO Class (classNumber, facId, schedul, room)
76VALUES ('MTH103C','F110','MWF11','H225');
77INSERT INTO Enroll (stuId, classNumber, grade)
78VALUES ('S1001','ART103A','A');
79INSERT INTO Enroll (stuId, classNumber, grade)
80VALUES ('S1001','HST205A','C');
81INSERT INTO Enroll (stuId, classNumber, grade)
82VALUES ('S1002','ART103A','D');
83INSERT INTO Enroll (stuId, classNumber, grade)
84VALUES ('S1002','CSC201A','F');
85INSERT INTO Enroll (stuId, classNumber, grade)
86VALUES ('S1002','MTH103C','B');
87INSERT INTO Enroll (stuId, classNumber)
88VALUES ('S1010','ART103A');
89INSERT INTO Enroll (stuId, classNumber)
90VALUES ('S1010','MTH103C');
91INSERT INTO Enroll (stuId, classNumber, grade)
92VALUES ('S1020','CSC201A','B');
93INSERT INTO Enroll (stuId, classNumber, grade)
94VALUES ('S1020','MTH101B','A');