Please create a PL/SQL procedure that is an explicit cursor that given a student

Question

Please create a PL/SQL procedure that is an explicit cursor that given a student’s ID, print out name of student and the student’s transcript. On the transcript please include the name of classes the student has taken, the year and semester of the class, and the grade of the class. Please handle the case when the student does not exist. Test your procedure with a student ID you have in your student table.

Tables:

Student table with 2 columns: sid (student ID), sname (student name). • Teacher table with 2 columns: tid (teacher ID), tname (teacher name) • Class table with 6 columns: cid (class ID), cname (class name), year (year of class), semester (semester of class, e.g., fall, spring), credit (number of credits), tid (teacher ID).Grades table with 3 columns: sid (student ID), cid (class ID), grade (grade, 4 is A, 3 is B, 2 is C, 1 is D, 0 is F).

Explanation / Answer

The Creation Of Tables From Data

For Student Table

create table student(

sid integer, - student ID

sname varchar(25), - student name

primary key (sid));

For Teacher Table

create table teacher(

tid integer, - teacher ID

tname varchar(25), - teacher name

primary key (tid));

For Class Table

create table class(

cid integer, - class ID

cname varchar(30), - 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));

For Grades Table

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));

Values Insertion To The Tables

For Student Table

insert into student values (1, 'Moses');

insert into student values (2, 'Henry');

insert into student values (3, 'Philips');

insert into student values (4, 'Herg');

insert into student values (5, 'Nelson');

For Teacher Table

insert into teacher values (1, 'Dr. oswald');

insert into teacher values (2, 'Dr. Riaz);

For Class Table

insert into class values(1,'IS 422', 2016, 'fall', 3,1);

insert into class values(2,'IS 422', 2015, 'fall', 3,1);

insert into class values(3,'IS 221', 2016, 'fall', 3,2);

insert into class values(4,'IS 221', 2016, 'spring', 3,2);

For Grades Table

insert into grades values(1,1,4);

insert into grades values(2,1,3);

insert into grades values(4,1,2);

In this programme primary key has given to the id's of student. teacher. class and grades tables because of its uniqueness and no redundency finding of names using right id is simple

for example

select * from student where sid=4;

this query gives the details of the student who has id number as 4.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.