Hw

create table student(  
StdNo char(5) primary key, 
Lastname varchar(25) not null,
Givenname varchar(25) not null, 
Dept char(4));  
insert into student(StdNo, Lastname, Givenname, Dept) values('S0001', 'Khaled', 'Marwan Ali', 'CS');  
insert into student(StdNo, Lastname, Givenname, Dept) values('S0711', 'Alsaleh', 'AbdulazizSaleh', 'CS');  
insert into student(StdNo, Lastname, Givenname, Dept) values('S0201', 'Mosa', 'Ahmad Majd', 'IT');  
insert into student(StdNo, Lastname, Givenname, Dept) values('S0421', 'Mohammad', 'Omar Riyadh', 'IT'); 
insert into student(StdNo, Lastname, Givenname, Dept) values('S0211', 'Alnasser', 'Ameen', 'CS'); 


create table Course(  
CourseID char(8) primary key,  
CourseTitle varchar(50) unique not null,  
Cost decimal(6,2) check (cost>=0),  
Credits int default 2 check (Credits between 0 and 200));  
insert into Course(CourseID, CourseTitle, Cost, Credits) values('IT125', 'Database', 6435.50, '3');  
insert into Course(CourseID, CourseTitle, Cost, Credits)values('CSC153', 'Object Oriented Programming', 8480.00, '4'); 
insert into Course(CourseID, CourseTitle, Cost, Credits) values('CSC152', 'C Programming', 8200.50, '4');  
insert into Course(CourseID, CourseTitle, Cost, Credits) values('IT224', 'Java Programming', 7680.00, '3');  


create table Register(  
StdNo char(5) references Student (stdNo),  
CourseID char(8) references Course (courseID), 
SemesterID char(5) references semester (semesterID),
Grade char(2),  
Mark decimal(4,2) check (Mark between 0 and 100),
primary key (stdNo,courseID,semesterID));
insert into Register(StdNo, CourseID, SemesterID, Grade, Mark) values('S0001', 'CSC152', 'SM02', 'A', 92);  
insert into Register(StdNo, CourseID, SemesterID, Grade, Mark) values('S0201', 'IT224', 'SM01', 'A', 92);  
insert into Register(StdNo, CourseID, SemesterID, Grade, Mark) values('S0711', 'CSC152', 'SM01', 'F', 55);  
insert into Register(StdNo, CourseID, SemesterID, Grade, Mark) values('S0421', 'IT125', 'SM02', 'B', 84);  
insert into Register(StdNo, CourseID, SemesterID, Grade, Mark) values('S0201', 'CSC153', 'SM01', null, null);  
insert into Register(StdNo, CourseID, SemesterID, Grade, Mark) values('S0201', 'IT125', 'SM02', 'B+', 87);  
insert into Register(StdNo, CourseID, SemesterID, Grade, Mark) values('S0711', 'CSC152', 'SM03', 'C+', 77);  
insert into Register(StdNo, CourseID, SemesterID, Grade, Mark) values('S0421', 'IT224', 'SM02', 'F', 57);  
insert into Register(StdNo, CourseID, SemesterID, Grade, Mark) values('S0211', 'CSC153', 'SM01', null, null);  
insert into Register(StdNo, CourseID, SemesterID, Grade, Mark) values('S0001', 'IT125', 'SM01', 'A+', 96);


create table semester(
SemesterID char(5) primary key,
SemesterCode int check (semesterCode between 1 and 4), Year int check (Year between 2000 and 9999));
insert into semester(SemesterID, SemesterCode, Year) values('SM01', '1', '2012');
insert into semester(SemesterID, SemesterCode, Year) values('SM02', '2', '2012');
insert into semester(SemesterID, SemesterCode, Year) values('SM03', '1', '2013');
insert into semester(SemesterID, SemesterCode, Year) values('SM04', '2', '2013');

SELECT DISTINCT(stdNo)
FROM Register
WHERE courseID IN  (SELECT courseID
FROM Register R, student S
WHERE R.stdNo = S.stdNo AND S.Givenname = 'Marwan Ali');

UPDATE Course
SET Cost = Cost * 1.10
WHERE courseID IN  (SELECT courseID
FROM Register
GROUP BY courseID
HAVING COUNT(stdNo) >= 2);

SELECT S.SemesterID, S.year, COUNT(R.stdNo) students
FROM semester S, Register R
WHERE S.SemesterID = R.SemesterID
GROUP BY S.SemesterID, S.year
HAVING COUNT(courseID) >= 2;

SELECT Course.courseID, courseTitle, Cost
FROM Course, Register
WHERE Course.courseID = Register.courseID AND Credits = 1
 GROUP BY Course.courseID, courseTitle, Cost
ORDER BY COUNT(stdNo);

SELECT courseTitle, Cost
FROM Course
WHERE courseID IN  (SELECT courseID
FROM Register
WHERE stdNo IN (SELECT stdNo
FROM student
WHERE Dept = 'IT') and Register.semesterid='SM02') order by cost ;