Question

Need help writing SQL statements. The questions involved use the following database

Student

SID – Integer – Primary Key

SName - String

Address - String

Classes

ClassID – Integer – Primary Key

ClassName - String

Credits - Integer

Grades

SID – Integer – Primary Key

CID – Integer – Primary Key

Grade - Integer

Q1 – Write a script that creates a view to show all the students and all the classes they took and the grades they got in those classes. The view should create an output result set with the following columns

SName, ClassName, Grade.

The view should be sorted by student name.

Q2 – Update the view in Q3 to also include new students(The students who have NOT TAKEN any classes yet, and thus do not have a grade)

Q3 – Create a Stored procedure that accepts a student name and outputs the student's average grade(Average grade in all the classes the student took). (You can assume no 2 students have the same name).

If the student name is null then return a grade of -100

Answer 1

drop table student cascade constraints;
drop table class cascade constraints;
drop table gradeS cascade constraints;

CREATE TABLE STUDENT (SID INT PRIMARY KEY,
                       SNAME VARCHAR(20),
                       ADDRESS VARCHAR(20));
                      
INSERT INTO STUDENT VALUES(1,'John','1234 Ninth');
INSERT INTO STUDENT VALUES(2,'Joe','1234 Seventh');
INSERT INTO STUDENT VALUES(3,'Harry','1234 Second');
INSERT INTO STUDENT VALUES(4,'Peter','1234 Sixth');

CREATE TABLE CLASS(CLASSID INT PRIMARY KEY,
                   CLASSNAME VARCHAR(20),
                   CREDITS INTEGER);
                  
INSERT INTO CLASS VALUES(11,'DBMS',4);
INSERT INTO CLASS VALUES(12,'C',4);
INSERT INTO CLASS VALUES(13,'C++',4);
INSERT INTO CLASS VALUES(14,'Discrete Maths',3);

CREATE TABLE GRADES (SID INT,CID INT,GRADE INT,
                   FOREIGN KEY(SID) REFERENCES STUDENT(SID),
                   FOREIGN KEY(CID) REFERENCES CLASS(CLASSID));
                  
INSERT INTO GRADES VALUES(1,11,50);
INSERT INTO GRADES VALUES(2,13,90);
INSERT INTO GRADES VALUES(2,12,70);
INSERT INTO GRADES VALUES(3,11,58);

SELECT * FROM STUDENT;
SELECT * FROM CLASS;
SELECT * FROM GRades;

CREATE OR REPLACE VIEW SHOWALLSTUDENTS AS(SELECT SNAME,CLASSNAME,GRADE
                                           FROM STUDENT S,CLASS C, GRADES G
                                           WHERE S.SID=G.SID AND C.CLASSID=G.CID);
                                          
SELECT * FROM SHOWALLSTUDENTS;

CREATE OR REPLACE PROCEDURE STUDENTSGRADE(STUDENT_NAME VARCHAR)
AS
done Number :=1;
CURSOR C1 IS
SELECT AVG(GRADE) avggrade,SNAME,G.SID
FROM GRADES G,STUDENT S
WHERE G.SID=S.SID AND SNAME=STUDENT_NAME
GROUP BY (G.SID,SNAME);
BEGIN
FOR P IN C1
LOOP
   if (P.SNAME = STUDENT_NAME) THEN
       dbms_output.put_line('STUDENT'||' '||P.SNAME||' '||'EXISTS AND HIS AVERAGE GRADE IS '||P.avggrade);
       done:=0;
   else
   dbms_output.put_line('Student '||STUDENT_NAME||' HAS NOT TAKEN ANY CLASSES and thus do not have a grade');
   end if;
END LOOP;
if (done = 1 ) THEN
dbms_output.put_line('Student '||STUDENT_NAME||' HAS NOT TAKEN ANY CLASSES and thus do not have a grade');
end if;
end;
/

EXEC STUDENTSGRADE('Peter');
EXEC STUDENTSGRADE('Joe');

CREATE OR REPLACE PROCEDURE STUDENTS(STUDENT_NAME VARCHAR)
AS
done Number :=1;
CURSOR C1 IS
SELECT AVG(GRADE) avggrade,SNAME,G.SID
FROM GRADES G,STUDENT S
WHERE G.SID=S.SID AND SNAME=STUDENT_NAME
GROUP BY (G.SID,SNAME);
BEGIN
FOR P IN C1
LOOP
   if (P.SNAME = STUDENT_NAME) THEN
       dbms_output.put_line('STUDENT'||' '||P.SNAME||' '||'EXISTS AND HIS AVERAGE GRADE IS '||P.avggrade);
       done:=0;
   end if;
END LOOP;
if (done = 1 ) THEN
dbms_output.put_line('Student NAME IS null hence grade is -100');
end if;
end;
/

EXEC STUDENTS('Peter');
EXEC STUDENTS('Joe');