--Department(DeptCode, Name)
Create Table Department
( 
DeptCode nchar(2) Primary Key,
Name nvarchar(100)
)

--Student(SSN, Name, City, DOB, Major, GPA)

Create Table Student
(
SSN nchar(14) primary key,
Name nvarchar(100),
City nVarchar(50),
DOB int,
Major nchar(2) References Department(DeptCode),
GPA float

)

--Course(CrsCode, Name)

Create Table Course
(
CrsCode nchar(10) primary key,
Name nvarchar(100),
)
--Registered(SSN, CrsCode, Semester, Year)

Create Table Registered
(
SSN nChar(14) REFERENCES Student(SSN),
CrsCode nchar(10) REFERENCES Course(CrsCode),
Semester nChar(10),
Year int
primary key (SSN, CrsCode),
)

Insert Into Department values ('IS','Information Systems');
Insert Into Department values ('CS','Computer Science');
Insert Into Department values ('IT','Information Technology');
Insert Into Department values ('DS','Decision Support');

Insert Into Course Values ('CS112','Programming 1');
Insert Into Course Values ('CS213','Programming 2');
Insert Into Course Values ('DS425','Logistics Management');
Insert Into Course Values ('DS241','Modeling and Simulation');
Insert Into Course Values ('IS221','Database Systems1');
Insert Into Course Values ('IS312','Database Systems2');
Insert Into Course Values ('IT111','Electronics 1');
Insert Into Course Values ('IT112','Electronics 2');

Insert Into Student Values ('101','Ahmed','Cairo',1992,'IS',3.2);
Insert Into Student Values ('102','Ayman','Giza',1993,'CS',3.4);
Insert Into Student Values ('103','Ibrahim','Aswan',1994,'IS',3.5);
Insert Into Student Values ('104','Ibrahim','Fayoum',1995,'DS',2.7);
Insert Into Student Values ('105','Mona','Cairo',1993,'IT',2.9);
Insert Into Student Values ('106','Nahla','Giza',1992,'IS',3);
Insert Into Student Values ('107','Sally','Aswan',1994,'CS',3.2);
Insert Into Student Values ('108','Sameh','Alexandria',1992,'CS',3.4);
Insert Into Student Values ('109','Tarek','Giza',1995,'IT',3.5);
Insert Into Student Values ('110','Wafaa','Cairo',1993,'IS',3.7);
Insert Into Student Values ('111','Hala','Alexandria',1996,'CS',3.6);
Insert Into Student Values ('112','Marwa','Fayoum',1995,'CS',3.1);

Insert Into Student Values ('113','Sara','Fayoum',1996,'IT',3.3);
Insert Into Student (SSN, Name, DOB, Major, GPA)  Values ('114','Ali',1995,'CS',3.4);
Insert Into Student (SSN, Name, City, DOB, Major) Values ('115','Laila','Giza',1994,'DS');
Insert Into Student (SSN, Name, DOB, Major, GPA)  Values ('116','Iman',1993,'IS',2.9);
Insert Into Student (SSN, Name, City, DOB, GPA)   Values ('117','Amal','Alexandria',1992, 2.7);
Insert Into Student (SSN, Name, Major, GPA)  Values ('118','Dina','CS',3.1);
Insert Into Student (SSN, Name, city, DOB )Values ('119','Ahmed','Cairo',1995);
Insert Into Student Values ('120','Mohammed','Cairo',1993,'IT',3.1);




Insert Into Registered Values ('101','IS221','Fall',2011);
Insert Into Registered Values ('101','IS312','Spring',2012);
Insert Into Registered Values ('101','CS112','Spring',2011);
Insert Into Registered Values ('102','IS221','Fall',2012);
Insert Into Registered Values ('102','CS112','Spring',2011);
Insert Into Registered Values ('103','IS221','Spring',2012);
Insert Into Registered Values ('103','CS112','Fall',2011);
Insert Into Registered Values ('104','IS221','Fall',2011);
Insert Into Registered Values ('104','DS241','Fall',2011);
Insert Into Registered Values ('105','IS221','Spring',2012);
Insert Into Registered Values ('106','IS221','Summer',2012);
Insert Into Registered Values ('106','CS213','Spring',2011);
Insert Into Registered Values ('109','IT111','Summer',2010);
Insert Into Registered Values ('109','IS221','Fall',2010);
Insert Into Registered Values ('110','IS221','Fall',2012);

Insert Into Registered Values ('120','IS221','Fall',2010);
Insert Into Registered Values ('113','IS221','spring',2009);
Insert Into Registered Values ('114','IS221','summer',2011);
Insert Into Registered Values ('115','IS221','summer',2012);

Insert Into Registered Values ('119','CS213','Spring',2009);
Insert Into Registered Values ('118','CS213','Spring',2010);
Insert Into Registered Values ('117','CS213','Spring',2009);
Insert Into Registered Values ('111','CS213','Spring',2010);
Insert Into Registered Values ('112','CS213','Spring',2011);
Insert Into Registered Values ('113','CS213','Spring',2011);
Insert Into Registered Values ('114','CS213','Spring',2011);