Database used in the Assignment:
You will be using the following tables in the exam.
drop table grades;
drop table student;
drop table teacher;
drop table class;
create table student(
sid integer, --- student ID
sname varchar(50), --- student name
primary key (sid));
create table teacher(
tid integer, --- teacher ID
tname varchar(50), ---- teacher name
primary key (tid));
create table class(
cid integer, --- class ID
cname varchar(50),--- class name
year integer, --- year of class
semester varchar(10), -- fall or spring
credit integer, -- number of credit
tid integer, --- teacher id
primary key (cid),
foreign key (tid) references teacher(tid));
create table grades(
sid integer, --- student ID
cid integer, --- product ID
grade integer, --- grade: 4.0:A, 3: B, 2: C, 1:D, 0:F
primary key (sid, cid),
foreign key (sid) references student(sid),
foreign key (cid) references class(cid));
insert into student values (1, 'John');
insert into student values (2, 'Alice');
insert into student values (3, 'Bob');
insert into student values (4, 'Cathy');
insert into student values (5, 'Jeff');
insert into teacher values (1, 'Dr. Chen');
insert into teacher values (2, 'Dr. Smith');
insert into class values(1,'IS 633', 2015, 'fall', 3,1);
insert into class values(2,'IS 633', 2014, 'fall', 3,1);
insert into class values(3,'IS 603', 2015, 'fall', 3,2);
insert into class values(4,'IS 603', 2015, 'spring', 3,2);
insert into grades values(1,1,4);
insert into grades values(2,1,3);
insert into grades values(4,1,2);
insert into grades values(3,2,3);
insert into grades values(5,2,4);
insert into grades values(1,3,4);
insert into grades values(5,3,3);
insert into grades values(2,4,3);
insert into grades values(3,4,2);
insert into grades values(4,4,4);
Problem 1: Please write ONE SQL statement to implement each of the following tasks.
[40 points, 5 points for each task]
[40 points, 5 points for each task]
Task 1: Return names of classes offered in fall 2015.
Task 2: Return names of classes taught by Dr. Chen in fall 2015.
Task 3: Return the total number of courses offered in spring 2015.
Task 4: Return the number of courses taught by each teacher in the year 2015. Please return tid in the result.
Task 5: Return the tid of teachers who have taught at least 2 courses in 2015.
Task 6: Return the names of students who is taking IS 633 in fall 2015.
Task 7: Return the number of students enrolled in each class. Please include class name, semester, and year in the result.
Task 8: Return the classes with at least 3 students enrolled. Please include name of class, semester, and year in the result.
Problem 2: [15 points] Please write a PL/SQL program to compute the sum of even numbers 2, 4, 6, 8, ..., 100.
Problem 3: [20 points] Please write an anonymous PL/SQL program to print out the grade of John in IS 633. Please use implicit cursor and handle exception. You will lose 10 points if you do not use anonymous PL/SQL program. You also cannot hard code student ID and class ID (i.e., your program should work regardless of the rows in the database).
Pblem 4: [25 points] Please write an anonymous PL/SQL program to print out the GPA of John. You will lose 15 points if you do not use anonymous PL/SQL program. You also cannot hard code student ID (i.e., your program should work regardless of the rows in the database).
Hint: GPA = sum of (grade of John in each class * credit of that class) / sum of credit of classes taken by John
