prompt*********************************************
prompt
prompt DATABASE HOMEWORK#3
prompt
prompt**********************************************


prompt*********************************************
prompt        Create View TotalPoints
prompt*********************************************
CREATE or REPLACE VIEW TotalPoints AS
SELECT hw1, hw2a, hw2b, midterm, hw3, fexam
FROM Rawscores
WHERE SSN='0001';

prompt*********************************************
prompt        Create View Weights
prompt*********************************************
CREATE or REPLACE VIEW Weights AS
SELECT hw1, hw2a, hw2b, midterm, hw3, fexam
FROM Rawscores
WHERE ssn='0002';

prompt*********************************************
prompt        Create View PercSores
prompt*********************************************
CREATE or REPLACE VIEW PercScores AS
SELECT R.ssn,R.lname,R.fname,R.section, 100*R.hw1/T.hw1 hw1, 100*R.hw2a/T.hw2a
hw2a,
        100*R.hw2b/T.hw2b hw2b, 100*R.midterm/T.midterm  midterm,
         100*R.hw3/T.hw3 hw3, 100*R.fexam/T.fexam fexam,
         ROUND(R.hw1*W.hw1 + R.hw2a*W.hw2a + R.hw2b*W.hw2b
         + R.midterm*W.midterm + R.hw3*W.hw3 + R.fexam*W.fexam,2) wtaverage
FROM   rawscores R, TotalPoints T, WtdPts W
WHERE  R.ssn<>'0002' AND R.ssn<>'0001';


prompt*********************************************
prompt        Create View Stats
prompt*********************************************
CREATE or REPLACE VIEW Stats AS
(SELECT * FROM PercScores where section='315')
UNION
(SELECT NULL,'Minimum', NULL,
section,MIN(hw1),MIN(hw2a),MIN(hw2b),MIN(midterm),MIN(hw3),MIN(fexam),
          MIN(wtaverage) 
FROM  PercScores WHERE section='315' GROUP BY section)
UNION
(SELECT NULL,'Mean', NULL,
section,round(AVG(hw1),2),ROUND(AVG(hw2a),2),ROUND(AVG(hw2b),2),
              ROUND(AVG(midterm),2), ROUND(AVG(hw3),2),ROUND(AVG(fexam),2),
                 ROUND(AVG(wtaverage),2) FROM PercScores 
WHERE
section='315' GROUP BY section)
UNION
(SELECT NULL,'Maximum', NULL, section,
MAX(hw1),MAX(hw2a),MAX(hw2b),MAX(midterm),MAX(hw3),MAX(fexam),MAX(wtaverage) 
FROM PercScores WHERE section='315' GROUP BY section)
UNION
(SELECT NULL,'Stddev', NULL,
section,ROUND(stddev(hw1),2),ROUND(stddev(hw2a),2),ROUND(stddev(hw2b),2),
ROUND(stddev(midterm),2),ROUND(stddev(hw3),2),ROUND(stddev(fexam),2),
ROUND(stddev(wtaverage),2) 
FROM PercScores WHERE section='315' GROUP BY section)
UNION
(SELECT * FROM PercScores WHERE section='415')
UNION
(SELECT NULL,'Minimum', NULL,
section,MIN(hw1),MIN(hw2a),MIN(hw2b),MIN(midterm),MIN(hw3),MIN(fexam),
                 MIN(wtaverage)
FROM PercScores WHERE section='415' GROUP BY section)
UNION
(SELECT NULL,'Mean', NULL,
section,ROUND(AVG(hw1),2),ROUND(AVG(hw2a),2),ROUND(AVG(hw2b),2),
  ROUND(AVG(midterm),2), ROUND(AVG(hw3),2),ROUND(AVG(fexam),2),
       ROUND(AVG(wtaverage),2) 
FROM PercScores  WHERE
section='415'GROUP BY section)
UNION
(SELECT NULL,'Maximum', NULL,
section,MAX(hw1),MAX(hw2a),MAX(hw2b),MAX(midterm),MAX(hw3),MAX(fexam),
      MAX(wtaverage)
FROM  PercScores WHERE section='415' GROUP BY section)
UNION
(SELECT NULL,'Stddev', NULL,
section,ROUND(stddev(hw1),2),ROUND(stddev(hw2a),2),ROUND(stddev(hw2b),2),
  ROUND(stddev(midterm),2),ROUND(stddev(hw3),2),ROUND(stddev(fexam),2),
  ROUND(stddev(wtaverage),2)
FROM PercScores WHERE section='415' GROUP BY section);



prompt*********************************************
prompt      (a)  Create Procedure ShowRawScores
prompt*********************************************
CREATE or REPLACE PROCEDURE ShowRawScores(v in VARCHAR2) IS
ignore boolean;
BEGIN
  
ignore:=owa_util.tablePrint('rawscores','BORDER',OWA_UTIL.HTML_TABLE,
 'ssn,lname,fname,section,hw1,hw2a,hw2b,midterm,hw3,fexam','WHERE ssn='||v);
END;
/


prompt*********************************************
prompt      (b)  Create Procedure ShowPercentages
prompt*********************************************
CREATE or REPLACE PROCEDURE ShowPercentages(v in VARCHAR2) IS
    ignore boolean;
    lname varchar2(11);
    fname varchar2(11);
    fscore number;
BEGIN

    ignore :=owa_util.tablePrint
    ('PercScores', 'BORDER', OWA_UTIL.HTML_TABLE,'SSN,
    Lname, Fname,section,hw1,hw2a,hw2b,midterm,hw3,fexam', 'where ssn='||v);
    SELECT lname,fname,wtaverage into lname,fname,fscore
    FROM PercScores
    WHERE ssn=v;
    htp.prints('The cumulative course average for '||fname||' '||
           lname||' is '||fscore);
END;
/

prompt*********************************************
prompt      (c)  Create Procedure AllRawScores
prompt*********************************************
CREATE or REPLACE PROCEDURE AllRawScores (v in VARCHAR2) IS
   pw varchar2;
   ignore boolean;
BEGIN
   pw := NULL;
   SELECT curpasswords INTO pw
   FROM Passwords
   WHERE curpasswords = v

   IF (pw == NULL) THEN htp.print('Invalid password!'); EXIT; END IF;
   ignore := owa_util.tablePrint('rawscores','BORDER', OWA_UTIL.HTML_TABLE,
      '*', 'WHERE ssn>0002'|| 'order by section, lname, fname');
END;
/

prompt*********************************************
prompt       (d)  Create Procedure AllPercentages
prompt*********************************************
CREATE or REPLACE PROCEDURE AllPercentages (v in VARCHAR2) IS
   pw varchar2;
   ignore boolean;
BEGIN
   pw := NULL;
   SELECT curpasswords INTO pw
   FROM Passwords
   WHERE curpasswords = v

   IF (pw == NULL) THEN htp.print('Invalid password!'); EXIT; END IF;
   ignore := tablePrint('percscores','BORDER', OWA_UTIL.HTML_TABLE,'*', 
          'order by section, wtaverage');
END;
/

prompt*********************************************
prompt       (e)  Create Procedure Stats
prompt*********************************************
CREATE or REPLACE PROCEDURE Stats (v in VARCHAR2) IS
pw varchar2;
   ignore boolean;
BEGIN
   pw := NULL;
   SELECT curpasswords INTO pw
   FROM Passwords
   WHERE curpasswords = v

   IF (pw == NULL) THEN htp.print('Invalid password!'); EXIT; END IF;
   ignore := tablePrint('stats','BORDER', OWA_UTIL.HTML_TABLE,'*', 
         'order by section,ssn,lname');
END;
/

prompt*********************************************
prompt       (f)  Create Procedure ChangeeScores
prompt*********************************************
CREATE or REPLACE PROCEDURE ChangeScores (password in VARCHAR2, 
       vssn in VARCHAR2, vassname in VARCHAR2, vscore in NUMBER) IS
   pw varchar2;
   ignore boolean;
BEGIN
   pw := NULL;
   SELECT curpasswords INTO pw
   FROM Passwords
   WHERE curpasswords = password

   IF (pw == NULL) THEN htp.print('Invalid password!'); EXIT; END IF;
   htp.prints ('Before update operation')
   ignore := owa_util.tablePrint('rawscores','BORDER', OWA_UTIL.HTML_TABLE,
         '*','WHERE ssn='|| vssn );
   IF upper(assignment)='HW1' THEN UPDATE rawscores SET hw1=to_number(score);
   ELSE IF upper(assignment)='HW2A' 
           THEN UPDATE rawscores SET hw2a=to_number(score);
   ELSE IF upper(assignment)='HW2B' 
           THEN UPDATE rawscores SET hw2b=to_number(score);
   ELSE IF upper(assignment)='MIDTERM' 
           THEN UPDATE rawscores SET midterm=to_number(score);
   ELSE IF upper(assignment)='HW3' 
           THEN UPDATE rawscores SET hw3=to_number(score);
   ELSE IF upper(assignment)='FEXAM' 
           THEN UPDATE rawscores SET fexam=to_number(score);
   END IF;
   END IF;
   END IF;
   END IF;
   END IF;
   END IF;
   htp.prints ('After update operation')
   ignore :=owa_util.tablePrint('rawscores','BORDER', 
            OWA_UTIL.HTML_TABLE,'*','WHERE ssn='|| vssn );
END;
/

=====================================================================
 (g) no strict format for HTML form
===================================================================== 
 
======================================================================
 (h) a sample perl interface program
====================================================================== 

#!/usr/local/bin/perl

# use the DataBase Interface to connect to Oracle
#use strict;
#use warnings;
use DBI;

# turn off buffering of output
$| = 1;


# set up the proper environment variables for running on 
# dbase
$ENV{ORACLE} = "/home/oracle/oracle";
$ENV{ORACLE_HOME} = "/home/oracle/oracle/OraHome1";
$ENV{ORACLE_BASE} = "/home/oracle/oracle/OraHome1";
$ENV{ORACLE_SID} = "dbase";

# -- first let's connect to the database and set everything up:
# set your $username and $password before making this call:
$username = "*********";
$password = "*********";

print STDERR "Connecting to Oracle.\n";

$Db = DBI->connect("dbi:Oracle:", $username, $password,
                          { AutoCommit => 0, PrintError => 0})
   || die("Can't login to Oracle: $DBI::errstr\n");
#$Db->{LongReadLen} = 4096;
#$Db->{LongTruncOk} = 1;

print STDERR "Connected.. processing input.\n";


# this reads all of the fields from a POST or the arguments
# to a GET command into a hash
#&ReadParse (*req);
my $ret = 0;

system "stty -echo";
print "Please Enter the 600.315/415 Password: ";
    
my $password = <STDIN>;
system "stty echo";
print "\n";
chomp $password;
my $student_ssn = "";
my $final_score = "";
my $Lname = "";
my $Fname = "";

if (&ispasswd($password)) {
    while (1) {
	last if (!&newform($ret));
	print "Do you want insert another final score [y/n]?: ";

	my $option = <STDIN>;
	chomp $option;
	print "\n";
	last if ($option !~ 'y');
    }	
}
else {
    # password not valid
    print "Sorry, your password is not in our database. Try again\n\n";       

}



# disconnect from the database
$Db->disconnect();
# undef the database handle to free resources
$Db = undef;

exit (0);

    sub newform {
    $ret = shift;
    # initialize our variables
    $ssn_student = "";
    $final_score = "";
    $Lname = "";
    $Fname = "";

    print "Please Enter the last 4-digits of the Student's SSN:  ";
    
    $ssn_student = <STDIN>;
    chomp $ssn_student;
    
    my (@result) = &isssn($ssn_student);
    
    if (@result[0]){
	
       
	print "Enter the Final Exam Score for $Fname $Lname: "; 

	
        my $flag = 1; 
        while ($flag){
	    $final_score = <STDIN>;
	    print "\n";
	    chomp $final_score;
	    if ($final_score =~ /\d+/) {
		&update($ssn_student,$final_score);
		$flag = 0;
	    }
	    else {
		print "Type data mismatch. You have to insert a number.\n" ;
		print "Enter the Final Exam Score for $Fname $Lname: "; 
	    }
	}
	$ret = 1; 
    }
    else {
	print "Sorry, this SSN is not present our database.\n\n";
	$ret = 0;
    }	
    return($ret);
}

	
sub update {
    # insert a user into our database, using the values from the form they
    # filled in on the web
    
    $ssn_student = shift;
    $final_score = shift;
    
    $ret = 1;
    # first check if that user is already in our database
    
    my $query_string = qq();
    my $query_string = 
   qq(update rawscores set FExam = '$final_score' where SSN = '$ssn_student');
#    print STDERR "sql: $query_string\n";

    $qry = $Db->prepare($query_string);
    # execute our query
    if (! $qry->execute()) {
	# if we didn't get a response from the query, spit out the error
	my $err = $DBI::errstr;
	# finish our query (frees resources)
	$qry->finish();
	# disconnect from the database
	$Db->disconnect();
	# undef the database handle to free resources
	$Db = undef;
	die("Oracle Error: $err\n");
    }
    print "Your change has been processed\n";
    # otherwise the query went through and the user is in the database
    # finish the query and return 1
    $qry->finish();
    $ret = 1;
    
    return ($ret);
}



sub ispasswd {
    # check to see if a login id is already in our database
    # return 1 if they are in the database, 0 if not
    print "Verifying...\n\n";
    my $ret =0; 
    $password = shift;

    # first create our SQL query.. the qq( .. ) is just a replacement
    # for using quotes, so you don't have to backslash your quotes in
    # the query statement.
    $qry = $Db->prepare(
     qq(select CurPasswords from Passwords where CurPasswords = '$password'));
    # execute our query
   # print "$qry\n";
    if (! $qry->execute()) {
	# if we didn't get a response from the query, spit out the error
	my $err = $DBI::errstr;
	$qry->finish();
	$ret = 0;
    }
    
    @arr = $qry->fetchrow_array();
    if (defined ($arr[0])) {
	# user is in our database
	$ret = 1;
    }
    else {
	$ret = 0;
    }
    $qry->finish();
    
    return ($ret);
}

sub isssn {
    # check to see if a login id is already in our database
    # return 1 if they are in the database, 0 if not
    print "Verifying...\n\n";
    $ssn_student = shift;
    my $ret=0;

    # first create our SQL query.. the qq( .. ) is just a replacement
    # for using quotes, so you don't have to backslash your quotes in
    # the query statement.
    $qry = $Db->prepare(
         qq(select Lname, Fname from rawscores where SSN = '$ssn_student'));
    # execute our query
    if (! $qry->execute()) {
	# if we didn't get a response from the query, spit out the error
	my $err = $DBI::errstr;
	$qry->finish();
	$ret = 0;
    }
   
    @arr = $qry->fetchrow_array();
    
    if (defined ($arr[0])) {
	$Lname = $arr[0];
	$Fname = $arr[1];
	# user is in our database
	$ret = 1;
    }
    else {
	$ret = 0;
    }
    $qry->finish();
    
    return($ret);
}

=============================================================================