2YLhPbyd

1DROP DATABASE IF EXISTS University;
2CREATE DATABASE IF NOT EXISTS University;
3USE University;
4DROP TABLE IF EXISTS Student;
5DROP TABLE IF EXISTS Faculty;
6DROP TABLE IF EXISTS Class;
7DROP TABLE IF EXISTS Enroll;
8CREATE TABLE Student (
9stuId VARCHAR(10) PRIMARY KEY,
10lastName VARCHAR(15),
11firstName VARCHAR(15),
12major VARCHAR(15),
13credits INT);
14CREATE TABLE Faculty (
15facId VARCHAR(10) PRIMARY KEY,
16name VARCHAR(15),
17department VARCHAR(15),
18rank VARCHAR(15));
19CREATE TABLE Class (
20classNumber VARCHAR(10) PRIMARY KEY,
21facId VARCHAR(10),
22schedule VARCHAR(10),
23room VARCHAR(10),
24FOREIGN KEY(facId) REFERENCES Faculty(facId));
25CREATE TABLE Enroll (
26stuId VARCHAR(10) NOT NULL,
27classNumber VARCHAR(10) NOT NULL,
28grade VARCHAR(2),
29PRIMARY KEY(stuId, classNumber),
30FOREIGN KEY(stuId) REFERENCES Student(stuId),
31FOREIGN KEY(classNumber) REFERENCES Class(classNumber));
32INSERT INTO Student (stuId, lastName, firstName, major, credits)
33VALUES ('S1001','Smith','Tom','History',90);
34INSERT INTO Student (stuId, lastName, firstName, major, credits)
35VALUES ('S1002','Chin','Ann','Math',36);
36INSERT INTO Student (stuId, lastName, firstName, major, credits)
37VALUES ('S1005','Lee','Perry','History',3);
38INSERT INTO Student (stuId, lastName, firstName, major, credits)
39VALUES ('S1010','Burns','Edward','Art',63);
40INSERT INTO Student (stuId, lastName, firstName, major, credits)
41VALUES ('S1013','McCarthy','Owen','Math',0);
42INSERT INTO Student (stuId, lastName, firstName, major, credits)
43VALUES ('S1015','Jones','Mary','Math',42);
44INSERT INTO Student (stuId, lastName, firstName, major, credits)
45VALUES ('S1020','Rivera','Jane','CSC',15);
46INSERT INTO Faculty (facId, name, department, rank)
47VALUES ('F101','Adams','Art','Professor');
48INSERT INTO Faculty (facId, name, department, rank)
49VALUES ('F105','Tanaka','CSC','Instructor');
50INSERT INTO Faculty (facId, name, department, rank)
51VALUES ('F110','Byrne','Math','Assistant');
52INSERT INTO Faculty (facId, name, department, rank)
53VALUES ('F115','Smith','History','Associate');
54INSERT INTO Faculty (facId, name, department, rank)
55VALUES ('F221','Smith','CSC','Professor');
56INSERT INTO Class (classNumber, facId, schedule, room)
57VALUES ('ART103A','F101','MWF9','H221');
58INSERT INTO Class (classNumber, facId, schedule, room)
59VALUES ('CSC201A','F105','TuThF10','M110');
60INSERT INTO Class (classNumber, facId, schedule, room)
61VALUES ('CSC203A','F105','MThF12','M110');
62INSERT INTO Class (classNumber, facId, schedule, room)
63VALUES ('HST205A','F115','MWF11','H221');
64INSERT INTO Class (classNumber, facId, schedule, room)
65VALUES ('MTH101B','F110','MTuTh9','H225');
66INSERT INTO Class (classNumber, facId, schedule, room)
67VALUES ('MTH103C','F110','MWF11','H225');
68INSERT INTO Enroll (stuId, classNumber, grade)
69VALUES ('S1001','ART103A','A');
70INSERT INTO Enroll (stuId, classNumber, grade)
71VALUES ('S1001','HST205A','C');
72INSERT INTO Enroll (stuId, classNumber, grade)
73VALUES ('S1002','ART103A','D');
74INSERT INTO Enroll (stuId, classNumber, grade)
75VALUES ('S1002','CSC201A','F');
76INSERT INTO Enroll (stuId, classNumber, grade)
77VALUES ('S1002','MTH103C','B');
78INSERT INTO Enroll (stuId, classNumber)
79VALUES ('S1010','ART103A');
80INSERT INTO Enroll (stuId, classNumber)
81VALUES ('S1010','MTH103C');
82INSERT INTO Enroll (stuId, classNumber, grade)
83VALUES ('S1020','CSC201A','B');
84INSERT INTO Enroll (stuId, classNumber, grade)
85VALUES ('S1020','MTH101B','A');