Student and Teacher Table Having Many to Many Relationship. Consider the Following Entities and Their Relationship. Student(rollno,name,class,totalmarks) Teacher(tno,tname) The Relationship between Student and Teacher is Many to Many with Subject as Descriptive Attricbute. Constraints: Primary Key, Class has to be FY,SY or TY.

SQL> create table student

  2  (rollno number primary key,

  3  name varchar(20),

  4  class varchar(2) check(class in('FY','SY','TY')),

  5  totalmarks number);


Table created.


SQL> create table teacher

  2  (tno number primary key,

  3  tname varchar(20));


Table created.


SQL> create table student_teacher

  2  (rollno number references student,

  3  tno number references teacher,

  4  subject varchar(30));

SQL> select * from student;


    ROLLNO NAME                 CL TOTALMARKS

---------- -------------------- -- ----------

         1 Raj                      FY        400

         2 Ramesh               SY        410

         3 Ganesh                SY        350

         4 Harsh                   FY        350

         5 Dinesh                 FY        450

         6 Chirag                 TY        350

         7 Ashish                 TY        390

         8 Naman                 TY        420


8 rows selected.


SQL> select * from teacher;


       TNO TNAME

---------- --------------------

       101 Prof Mane

       102 Prof Sharma

       103 Prof Verma

       104 Prof Roy

       105 Prof Smith

       106 Prof Bhagat

       107 Prof Nikam

       108 Prof Puri


8 rows selected.


SQL> select * from student_teacher;


    ROLLNO        TNO SUBJECT

---------- ---------- ------------------------------

         1        101 Data Structure

         2        102 Data Structure

         3        103 RDBMS

         4        104 Data Structure

         5        105 Management Accounting

         6        106 Numerical Methods

         7        107 Software Engineering

         8        108 Software Engineering


8 rows selected.


a) Create or Replace a PL/SQL Procedure to Display Details of all Students of Class 'FY'.


SQL> set serveroutput on

SQL> create or replace procedure p9

  2  as

  3  cursor c9 is

  4  select student.rollno,name,class,totalmarks from student

  5  where class='FY';

  6  y c9%rowtype;

  7  begin

  8  dbms_output.put_line('ROLLNO   NAME   CLASS  TOTALMARKS');

  9  dbms_output.put_line('---------------------------------');

 10  for y in c9

 11  loop

 12  dbms_output.put_line(y.rollno ||'   '|| y.name ||'    '|| y.class ||'   '|| y.totalmarks);

 13  end loop;

 14  end p9;

 15  /


Procedure created.


SQL> execute p9;

ROLLNO   NAME   CLASS  TOTALMARKS

---------------------------------

1        Raj       FY   400

4        Harsh    FY   350

5        Dinesh   FY   450


PL/SQL procedure successfully completed.


b) Create or Replace a Trigger That Restricts Insertion or Updation of Students Having TotalMarks Less Than 0.


SQL> create or replace trigger t9

  2  before

  3  insert or update

  4  on student

  5  for each row

  6  begin

  7  if :new.totalmarks<0 then

  8  Raise_Application_Error(-20009,'Total Marks Should Be Greater Than 0');

  9  end if;

 10  end;

 11  /


Trigger created.


SQL> insert into student values(9,'Vivek','TY',-20);

insert into student values(9,'Vivek','TY',-20)

*

ERROR at line 1:

ORA-20009: Total Marks Should Be Greater Than 0

ORA-06512: at "SYSTEM.T9", line 3

ORA-04088: error during execution of trigger 'SYSTEM.T9'