STUDENT MARK ANALYSIS

STUDENT MARK ANALYSIS



CREATE

SQL> create table student00(rollno number primary key,name varchar(15),age number,sex char(2),address varchar(10),father varchar(15));

Table created.

SQL> create table markls(reg_no number,mark1 number,mark2 number,mark3 number,mark4 number,mark5 num
ber,mark6 number);

Table created.

SQL> create table clg_rcd(rollno number,reg_no number,name varchar(15),branch char(10) not null, year
 number,semester number);

Table created.

SQL> create table results(
reg_no number, percentage number(6,2),result varchar(15),arrears number);

Table created.

SQL> desc student00
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLLNO                                    NOT NULL NUMBER
 NAME                                               VARCHAR2(15)
 AGE                                                NUMBER
 SEX                                                CHAR(2)
 ADDRESS                                            VARCHAR2(10)
 FATHER                                             VARCHAR2(15)

SQL> desc clg_rcd
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLLNO                                             NUMBER
 REG_NO                                             NUMBER
 NAME                                               VARCHAR2(15)
 BRANCH                                    NOT NULL CHAR(10)
 YEAR                                               NUMBER
 SEMESTER                                           NUMBER

SQL> desc markls
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REG_NO                                             NUMBER
 MARK1                                              NUMBER
 MARK2                                              NUMBER
 MARK3                                              NUMBER
 MARK4                                              NUMBER
 MARK5                                              NUMBER
 MARK6                                              NUMBER

SQL> desc results
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REG_NO                                             NUMBER
 PERCENTAGE                                         NUMBER(6,2)
 RESULT                                             VARCHAR2(15)
 ARREARS                                            NUMBER
 
CHECK THE INPUT (TRIGGER)

SQL>ed trigger;
create or replace trigger che_tri before insert on markls for each row
declare
m1 number;
m2 number;
m3 number;
m4 number;
m5 number;
m6 number;
begin
m1:=:new.mark1;
m2:=:new.mark2;
m3:=:new.mark3;
m4:=:new.mark4;
m5:=:new.mark5;
m6:=:new.mark6;
if m1<0 or m1>100 then
raise_application_error(-20011,'error in input');
end if;
if m2<0 or m2>100 then
raise_application_error(-20012,'error in input');
end if;
if m3<0 or m3>100 then
raise_application_error(-20013,'error in input');
end if;
if m4<0 or m4>100 then
raise_application_error(-20014,'error in input');
end if;
if m5<0 or m5>100 then
raise_application_error(-20015,'error in input');
end if;
if m6<0 or m6>100 then
raise_application_error(-20016,'error in input');
end if;
end;

SQL> @ trigger
 35  /

Trigger created.

TO INSERT MARKS AND CALCULATE PERCENTAGE (PROCEDURE)

SQL> ed insmark;

declare
rollno number;
reg_no number;
name varchar(15);
branch char(10);
year number;
semester number;
mark1 number;
mark2 number;
mark3 number;
mark4 number;
mark5 number;
mark6 number;
age number;
sex char;
address varchar(15);
father varchar(15);
result varchar(15);
percentage number(6,2);
arrears number;
begin
arrears:=0;
rollno:=&rollno;
reg_no:=&reg_no;
name:='&name';
branch:='&branch';
mark1:=&mark1;
mark2:=&mark2;
mark3:=&mark3;
mark4:=&mark4;
mark5:=&mark5;
mark6:=&mark6;
if(mark1<50) then
arrears:=arrears+1;
end if;
if(mark2<50) then
arrears:=arrears+1;
end if;
if(mark3<50) then
arrears:=arrears+1;
end if;
if(mark4<50) then
arrears:=arrears+1;
end if;
if(mark5<50) then
arrears:=arrears+1;
end if;
if(mark6<50) then
arrears:=arrears+1;
end if;
percentage:=(mark1+mark2+mark3+mark4+mark5+mark6);
percentage:=percentage/6;
if(percentage>75)then
result:='distinction';
elsif(percentage>60)then
result:='1st class';
elsif(percentage>50)then
result:='2nd class';
end if;
if(arrears!=0) then
result:='fail';
end if;
insert into student00 values(rollno,name,&age,'&sex','&address','&father');
insert into clg_rcd values(rollno,reg_no,name,branch,&year,&semester);
insert into markls values(reg_no,mark1,mark2,mark3,mark4,mark5,mark6);
insert into results values(reg_no,percentage,result,arrears);
end;

SQL> @ insmark;
72    /

Enter value for rollno: 58
old  23: rollno:=&rollno;
new  23: rollno:=58;
Enter value for reg_no: 5058
old  24: reg_no:=&reg_no;
new  24: reg_no:=5058;
Enter value for name: pavithra
old  25: name:='&name';
new  25: name:='pavithra';
Enter value for branch: IT
old  26: branch:='&branch';
new  26: branch:='IT';
Enter value for mark1: 95
old  27: mark1:=&mark1;
new  27: mark1:=95;
Enter value for mark2: 97
old  28: mark2:=&mark2;
new  28: mark2:=97;
Enter value for mark3: 96
old  29: mark3:=&mark3;
new  29: mark3:=96;
Enter value for mark4: 94
old  30: mark4:=&mark4;
new  30: mark4:=94;
Enter value for mark5: 93
old  31: mark5:=&mark5;
new  31: mark5:=93;
Enter value for mark6: 100
old  32: mark6:=&mark6;
new  32: mark6:=100;
Enter value for age: 18
Enter value for sex: f
Enter value for address: chennai
Enter value for father: pandian
old  63: insert into student00 values(rollno,name,&age,'&sex','&address','&father');
new  63: insert into student00 values(rollno,name,18,'f','chennai','pandian');
Enter value for year: 2
Enter value for semester: 4
old  64: insert into clg_rcd values(rollno,reg_no,name,branch,&year,&semester);
new  64: insert into clg_rcd values(rollno,reg_no,name,branch,2,4);

PL/SQL procedure successfully completed.

SQL> /
Enter value for rollno: 100
old  23: rollno:=&rollno;
new  23: rollno:=100;
Enter value for reg_no: 5100
old  24: reg_no:=&reg_no;
new  24: reg_no:=5100;
Enter value for name: vidhya
old  25: name:='&name';
new  25: name:='vidhya';
Enter value for branch: IT
old  26: branch:='&branch';
new  26: branch:='IT';
Enter value for mark1: 94
old  27: mark1:=&mark1;
new  27: mark1:=94;
Enter value for mark2: 97
old  28: mark2:=&mark2;
new  28: mark2:=97;
Enter value for mark3: 92
old  29: mark3:=&mark3;
new  29: mark3:=92;
Enter value for mark4: 83
old  30: mark4:=&mark4;
new  30: mark4:=83;
Enter value for mark5: 94
old  31: mark5:=&mark5;
new  31: mark5:=94;
Enter value for mark6: 100
old  32: mark6:=&mark6;
new  32: mark6:=100;
Enter value for age: 18
Enter value for sex: f
Enter value for address: chennai
Enter value for father: natraj
old  63: insert into student00 values(rollno,name,&age,'&sex','&address','&father');
new  63: insert into student00 values(rollno,name,18,'f','chennai','natraj');
Enter value for year: 2
Enter value for semester: 4
old  64: insert into clg_rcd values(rollno,reg_no,name,branch,&year,&semester);
new  64: insert into clg_rcd values(rollno,reg_no,name,branch,2,4);

PL/SQL procedure successfully completed.

SQL> /
Enter value for rollno: 97
old  23: rollno:=&rollno;
new  23: rollno:=97;
Enter value for reg_no: 5097
old  24: reg_no:=&reg_no;
new  24: reg_no:=5097;
Enter value for name: suresh
old  25: name:='&name';
new  25: name:='suresh';
Enter value for branch: IT
old  26: branch:='&branch';
new  26: branch:='IT';
Enter value for mark1: 97
old  27: mark1:=&mark1;
new  27: mark1:=97;
Enter value for mark2: 95
old  28: mark2:=&mark2;
new  28: mark2:=95;
Enter value for mark3: 94
old  29: mark3:=&mark3;
new  29: mark3:=94;
Enter value for mark4: 98
old  30: mark4:=&mark4;
new  30: mark4:=98;
Enter value for mark5: 92
old  31: mark5:=&mark5;
new  31: mark5:=92;
Enter value for mark6: 100
old  32: mark6:=&mark6;
new  32: mark6:=100;
Enter value for age: 18
Enter value for sex: m
Enter value for address: chennai
Enter value for father: natraj
old  63: insert into student00 values(rollno,name,&age,'&sex','&address','&father');
new  63: insert into student00 values(rollno,name,18,'m','chennai','natraj');
Enter value for year: 2
Enter value for semester: 4
old  64: insert into clg_rcd values(rollno,reg_no,name,branch,&year,&semester);
new  64: insert into clg_rcd values(rollno,reg_no,name,branch,2,4);

PL/SQL procedure successfully completed.

SQL> /
Enter value for rollno: 77
old  23: rollno:=&rollno;
new  23: rollno:=77;
Enter value for reg_no: 5077
old  24: reg_no:=&reg_no;
new  24: reg_no:=5077;
Enter value for name: swetha
old  25: name:='&name';
new  25: name:='swetha';
Enter value for branch: IT
old  26: branch:='&branch';
new  26: branch:='IT';
Enter value for mark1: 45
old  27: mark1:=&mark1;
new  27: mark1:=45;
Enter value for mark2: 52
old  28: mark2:=&mark2;
new  28: mark2:=52;
Enter value for mark3: 62
old  29: mark3:=&mark3;
new  29: mark3:=62;
Enter value for mark4: 69
old  30: mark4:=&mark4;
new  30: mark4:=69;
Enter value for mark5: 64
old  31: mark5:=&mark5;
new  31: mark5:=64;
Enter value for mark6: 57
old  32: mark6:=&mark6;
new  32: mark6:=57;
Enter value for age: 18
Enter value for sex: f
Enter value for address: chennai
Enter value for father: sundhar
old  63: insert into student00 values(rollno,name,&age,'&sex','&address','&father');
new  63: insert into student00 values(rollno,name,18,'f','chennai','sundhar');
Enter value for year: 2
Enter value for semester: 4
old  64: insert into clg_rcd values(rollno,reg_no,name,branch,&year,&semester);
new  64: insert into clg_rcd values(rollno,reg_no,name,branch,2,4);

PL/SQL procedure successfully completed.

SQL>  select * from clg_rcd;

    ROLLNO     REG_NO       NAME            BRANCH           YEAR     SEMESTER
---------- ---------- --------------- ---------- ---------- ------------------------------------------------
        58              5058             pavithra                IT                         2             4
       100             5100             vidhya                  IT                         2             4
        97             5097              suresh                   IT                         2             4
        66             5066              anitha                   IT                          2            4
        86             5086              hari                      IT                          2            4
        77             5077              swetha                 IT                          2            4

6 rows selected.

SQL>  select * from markls;

    REG_NO      MARK1      MARK2      MARK3      MARK4      MARK5      MARK6
---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------------
      5058                  95              97                96                94                93              100
      5100                  94              97               92                 83               94               100
      5097                  97              95               94                 98               92               100
      5066                  85              96               84                 87               89               99
      5086                  96              98               84                 96               91               99
      5077                   45              52              62                 69               64               57

6 rows selected.

SQL> select * from results;

    REG_NO    PERCENTAGE         RESULT             ARREARS
---------- ---------- --------------- -----------------------------------------------
      5058                 95.83                     distinction              0
      5100                 93.33                     distinction              0
      5097                 96                          distinction              0
      5066                 90                          distinction              0
      5086                 94                          distinction              0
      5077                 58.17                      fail                         1

6 rows selected.

TO GET  INDIVIDUAL  DETAILS  (PROCEDURE)

SQL> ed procstu;

create or replace procedure rollno(reg_num number)as
crec student00%rowtype;
vrec clg_rcd%rowtype;
mrec markls%rowtype;
rrec results%rowtype;
begin
select * into vrec from clg_rcd where reg_no=reg_num;
select * into crec from student00 where rollno=vrec.rollno;
select * into mrec from markls where reg_no=reg_num;
select * into rrec from results where reg_no=reg_num;
dbms_output.put_line('*****personal details*****');
dbms_output.put_line('register no.:'||reg_num);
dbms_output.put_line('name:'||crec.name);
dbms_output.put_line('father name:'||crec.father);
dbms_output.put_line('age:'||crec.age);
dbms_output.put_line('sex:'||crec.sex);
dbms_output.put_line('address:'||crec.address);
dbms_output.put_line('*****mark details******');
dbms_output.put_line('branch:'||vrec.branch);
dbms_output.put_line('semester:'||vrec.semester);
dbms_output.put_line('year:'||vrec.year);
dbms_output.put_line('mark1:'||mrec.mark1);
dbms_output.put_line('mark2:'||mrec.mark2);
dbms_output.put_line('mark3:'||mrec.mark3);
dbms_output.put_line('mark4:'||mrec.mark4);
dbms_output.put_line('mark5:'||mrec.mark5);
dbms_output.put_line('mark6:'||mrec.mark6);
dbms_output.put_line('*****results************');
dbms_output.put_line('percentage:'||rrec.percentage);
dbms_output.put_line('no.of arrears:'||rrec.arrears);
dbms_output.put_line('class:'||rrec.result);
exception
when no_data_found then
dbms_output.put_line('no student in that reg.no');
end;

SQL> @ procstu;
 36  /

Procedure created.

SQL> exec rollno(5100);
*****personal details*****
register no.:5100
name:vidhya
father name:natraj
age:18
sex:f
address:chennai
*****mark details******
branch:IT
semester:4
year:2
mark1:94
mark2:97
mark3:92
mark4:83
mark5:94
mark6:100
*****results************
percentage:93.33
no.of arrears:0
class:distinction
PL/SQL procedure successfully completed..
SQL> exec rollno(5058);
*****personal details*****
register no.:5058
name:pavithra
father name:pandian
age:18
sex:f
address:chennai
*****mark details******
branch:IT
semester:4
year:2
mark1:95
mark2:97
mark3:96
mark4:94
mark5:93
mark6:100
*****results************
percentage:95.83
no.of arrears:0
class:distinction

SQL> exec rollno(5097);
*****personal details*****
register no.:5097
name:suresh
father name:natraj
age:18
sex:m
address:chennai
*****mark details******
branch:IT
semester:4
year:2
mark1:97
mark2:95
mark3:94
mark4:98
mark5:92
mark6:100
*****results************
percentage:96
no.of arrears:0
class:distinction

PL/SQL procedure successfully completed.

TO  GET EACH BRANCH DETAILS

SQL> ed branchstu
create or replace procedure branch(bran in char) as
crec student00%rowtype;
vrec clg_rcd%rowtype;
mrec markls%rowtype;
rrec results%rowtype;
cursor c is select * from clg_rcd where branch=bran;
begin
open c;
dbms_output.put_line('*****mark details of students in branch******'||bran);
loop
fetch c into vrec;
if c%rowcount=0 then
dbms_output.put_line('*****no  students in that branch******');
exit;
else
exit when c%notfound;
select * into mrec from markls where reg_no=vrec.reg_no;
select * into rrec from results where reg_no=vrec.reg_no;
dbms_output.put_line('*****mark details******');
dbms_output.put_line('mark1:'||mrec.mark1);
dbms_output.put_line('mark2:'||mrec.mark2);
dbms_output.put_line('mark3:'||mrec.mark3);
dbms_output.put_line('mark4:'||mrec.mark4);
dbms_output.put_line('mark5:'||mrec.mark5);
dbms_output.put_line('mark6:'||mrec.mark6);
dbms_output.put_line('*****results************');
dbms_output.put_line('percentage:'||rrec.percentage);
dbms_output.put_line('no.of arrears:'||rrec.arrears);
dbms_output.put_line('class:'||rrec.result);
end if;
end loop;
close c;
end;

SQL>  @ branchstu;
 34  /
Procedure created.

SQL> exec branch('IT');
*****mark details of students in branch******IT
*****mark details******
mark1:95
mark2:97
mark3:96
mark4:94
mark5:93
mark6:100
*****results************
percentage:95.83
no.of arrears:0
class:distinction
*****mark details******
mark1:94
mark2:97
mark3:92
mark4:83
mark5:94
mark6:100
*****results************
percentage:93.33
no.of arrears:0
class:distinction

*****mark details******
mark1:97
mark2:95
mark3:94
mark4:98
mark5:92
mark6:100
*****results************
percentage:96
no.of arrears:0
class:distinction
*****mark details******
mark1:85
mark2:96
mark3:84
mark4:87
mark5:89
mark6:99
*****results************
percentage:90
no.of arrears:0
class:distinction
*****mark details******
mark1:96
mark2:98
mark3:84
mark4:96
mark5:91
mark6:99
*****results************
percentage:94
no.of arrears:0
class:distinction
*****mark details******
mark1:45
mark2:52
mark3:62
mark4:69
mark5:64
mark6:57
*****results************
percentage:58.17
no.of arrears:1
class:fail

PL/SQL procedure successfully completed.

SQL> exec branch('ece');
*****mark details of students in branch******ece
*****no  students in that branch******

PL/SQL procedure successfully completed.

TO GET ARREAR STUDENTS DETAILS

SQL> ed arrearstu;
create or replace procedure arr as
crec student00%rowtype;
vrec clg_rcd%rowtype;
mrec markls%rowtype;
rrec results%rowtype;
cursor c is select * from results where arrears!=0order by reg_no;
begin
open c;
dbms_output.put_line('*****students with arrear******');
loop
fetch c into rrec;
if c%rowcount=0 then
dbms_output.put_line('*****no students with arrear****');
exit;
else
exit when c%notfound;
select * into vrec from clg_rcd where reg_no=rrec.reg_no;
select * into mrec from markls where reg_no=rrec.reg_no;
select * into rrec from results where reg_no=rrec.reg_no;
dbms_output.put_line('register no.:'||rrec.reg_no);
dbms_output.put_line('name:'||vrec.name);
dbms_output.put_line('*****mark details******');
dbms_output.put_line('mark1:'||mrec.mark1);
dbms_output.put_line('mark2:'||mrec.mark2);
dbms_output.put_line('mark3:'||mrec.mark3);
dbms_output.put_line('mark4:'||mrec.mark4);
dbms_output.put_line('mark5:'||mrec.mark5);
dbms_output.put_line('mark6:'||mrec.mark6);
dbms_output.put_line('*****results************');
dbms_output.put_line('percentage:'||rrec.percentage);
dbms_output.put_line('no.of arrears:'||rrec.arrears);
dbms_output.put_line('class:'||rrec.result);
end if;
end loop;
close c;
end;

SQL>  @ arrearstu;
 37  /

Procedure created.

SQL> begin
  2  arr;
  3  end;
  4  /
*****students with arrear******
register no.:5077
name:swetha
*****mark details******
mark1:45
mark2:52
mark3:62
mark4:69
mark5:64
mark6:57
*****results************
percentage:58.17
no.of arrears:1
class:fail

PL/SQL procedure successfully completed.

Next
« Prev Post
Previous
Next Post »
Subscribe to: Post Comments (Atom)

Still not found what you are looking for? Try again here.