Notes

CREATE DATABASE NUST;

CREATE TABLE NUST.Student(
snum int NOT NULL,
sname char(30),
major char(25),
level char(2),
CONSTRAINT c_1 PRIMARY KEY(snum));

Alter table NUST.Student
Add age int;

Alter table NUST.student
Modify column sname varchar(30);

Alter table NUST.student
change sname stdname varchar(30);

CREATE TABLE NUST.Faculty(
fid int unsigned not null,
fname char(40),
depid int,
PRIMARY KEY (fid));

Alter table NUST.faculty
Modify column fname varchar(40);

Alter table NUST.faculty
Add constraint notnull check (depit is not null);

Alter table NUST.faculty
change fname Facname varchar(40);

CREATE TABLE NUST.Class(
cname char(40),
meet_at char(20),
room char(10),
fid int unsigned not null,
primary key (cname),
CONSTRAINT c_2 FOREIGN KEY (fid) references Faculty(fid));

Alter table NUST.class
Modify column cname varchar(40);

Alter table NUST.class
Change cname clsname varchar(40);

CREATE TABLE NUST.Enrolled(
snum int Not Null,
cname char(40),
constraint c_3 foreign key (snum) references Student(snum),
constraint c_4 foreign key (cname) references Class(cname));

Alter table NUST.Enrolled
Drop foreign key c_4;

Alter table NUST.Enrolled
Drop foreign key c_3;

Alter table NUST.enrolled



INSERT INTO student values(
(051135593,'Maria White','English','SR',21),
(060839453,'Charles Harris','Architecture','SR',22),
(099354543,'Susan Martin','Law','JR',20),
(112348546,'Joseph Thompson','Computer Science','SO',19),
(115987938,'Christopher Garcia','Computer Science','JR',20),
(132977562,'Angela Martinez','History','SR',20),
(269734834,'Thomas Robinson','Psychology','SO',18),
(280158572,'Margaret Clark','Animal Science','FR',18),
(301221823,'Juan Rodriguez','Psychology','JR',20),
(318548912,'Dorthy Lewis','Finance','FR',18),
(320874981,'Daniel Lee','Electrical Engineering','FR',17),
(322654189,'Lisa Walker','Computer Science','SO',17),
(348121549,'Paul Hall','Computer Science','JR',18),
(351565322,'Nancy Allen','Accounting','JR',19),
(451519864,'Mark Young','Finance','FR',18),
(455798411,'Luis Hernandez','Electrical Engineering','FR',17),
(462156489,'Donald King','Mechanical Engineering','SO',19),
(550156548,'George Wright','Education','SR',21),
(552455318,'Ana Lopez','Computer Engineering','SR',19),
(556784565,'Kenneth Hill','Civil Engineering','SR',21),
(567354612,'Karen Scott','Computer Engineering','FR',18),
(573284895,'Steven Green','Kinesiology','SO',19),
(574489456,'Betty Adams','Economics','JR',20),
(578875478,'Edward Baker','Veterinary Medicine','SR',21)
);

INSERT INTO faculty values(
(142519864,'Ivana Teach',20),
(242518965,'James Smith',68),
(141582651,'Mary Johnson',20),
(011564812,'John Williams',68),
(254099823,'Patricia Jones',68),
(356187925,'Robert Brown',12),
(489456522,'Linda Davis',20),
(287321212,'Michael Miller',12),
(248965255,'Barbara Wilson',12),
(159542516,'William Moore',33)
);

INSERT INTO class values(
('Data Structures','MWF 10','R128',489456522),
('Database Systems','MWF 12:30-1:45','1320 DCL',142519864),
('Operating System Design','TuTh 12-1:20,20 AVW',489456522),
('Archaeology of the Incas','MWF 3-4:15','R128',248965255),
('Aviation Accident Investigation','TuTh 1-2:50','Q3',011564812),
('Air Quality Engineering','TuTh 10:30-11:45',R15,011564812),
('Introductory Latin','MWF 3-4:15','R12',248965255),
('American Political Parties','TuTh 2-3:15','20 AVW',619023588),
('Social Cognition','Tu 6:30-8:40','R15',159542516),
('Perception','MTuWTh 3','Q3',489221823)
);

INSERT INTO enrolled values(
(112348546,'Database Systems'),
(115987938,'Database Systems'),
(348121549,'Database Systems'),
(322654189,'Database Systems'),
(552455318,'Database Systems'),
(455798411,'Operating System Design'),
(552455318,'Operating System Design'),
(567354612,'Operating System Design'),
(112348546,'Operating System Design'),
(115987938,'Operating System Design')
);