import java.io.*;
import java.sql.*;
import java.lang.*;

class hw2{
 
    static Connection DBCon=null;
    String s="";
    ResultSet rs=null;
    Statement stmt=null;
    String upd="";
    BufferedReader br;
    
    public hw2(){
	/*We load the jdbc:odbc bridge driver and open the connection which we'll use for the program*/

	try{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	}catch (Exception E){System.err.println("DRIVER");}
	
	try{
	    DBCon=DriverManager.getConnection("jdbc:odbc:444","yana","password");
	    stmt = DBCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	}catch (Exception E){System.err.println("Connection failed");}
	
        br=new BufferedReader(new InputStreamReader(System.in));
    }

    public static void main(String[] args){
	hw2 my_hwk=new hw2();
	my_hwk.query1();
	my_hwk.update1();
	my_hwk.query2();
	my_hwk.update2();
	my_hwk.query3();
	//clean up
	try{
	if (!DBCon.isClosed())
		    DBCon.close();
	}catch(Exception E){System.err.println("Could not close the connection");E.printStackTrace();}
    }

    void query1(){
	String q0="drop view Q1";
	    
	String q1=" create view Q1 as select distinct F_Name,F_Salary  from Faculty F,Department D,Offering O ";
	q1+="where O.F_SSN=F.F_SSN and D.F_SSN=F.F_SSN and F.F_SSN not in";
	q1+=" (select distinct F.F_SSN from Course C,Faculty F,Department D,Offering O where D.Did=C.Did and O.CourseNo=C.CourseNo and D.F_SSN=F.F_SSN and O.F_SSN=F.F_SSN)";
	
       
	/* this step is not mandatory, just ensures that the program doesn't crash if the view exists*/
	try{
	    stmt.execute(q0);
	}catch(Exception e){}

	try{
	    stmt.execute(q1);
	    rs=stmt.executeQuery("select * from Q1");
	}catch (Exception E){System.err.println("Q1 failed");E.printStackTrace();}
	
	try{
	    System.out.println("for Question 1: ");
	   while (rs.next()){
		System.out.println(rs.getString("F_Name")+" "+rs.getInt("F_Salary"));
	   }
	}catch (Exception E){System.err.println("result set query 1:");E.printStackTrace();}
    }

    void update1(){
	upd="update y_Faculty set F_Salary=1.1*F_Salary where F_Name in (select F_Name from Q1)";
	try{
	     stmt.executeUpdate(upd);
	}catch (Exception E){System.err.println("Update failed");E.printStackTrace();}
	try{
	    rs=stmt.executeQuery("select F.* from y_Faculty F,Q1 where F.F_Name=Q1. F_Name");
	}catch (Exception E){System.err.println(" select from y_Faculty failed");E.printStackTrace();}
	
	try{
	    System.out.println("\nafer update Question 1: ");
	   while (rs.next()){
		System.out.println(rs.getString("F_Name")+" "+rs.getInt("F_Salary"));
	   }
	}catch (Exception E){System.err.println("question 1");E.printStackTrace();}

    }

    void query2(){
	String q0="drop view DeptStats";
	String q1="create view DeptStats as ";
	q1+="select MAX(F.F_Salary)as max_Salary,AVG(F.F_Salary)as avg_Salary,D.Did,D.F_SSN,D.D_Budget";
	q1+=" from Faculty F, Department D ";
	q1+="where F.Did=D.Did group by D.Did,D.F_SSN,D.D_Budget";
	String q2=" create view SadChairs as select F.F_SSN,F.F_Name,F_Salary-avg_Salary as Diff,F.F_Salary,D.D_Budget from Faculty F,DeptStats D ";
	q2+=" where D.F_SSN=F.F_SSN and F.F_Salary>D.avg_Salary and F.F_Salary<D.max_Salary ";
	
	//clean up views from previous runs
	try{
	    stmt.execute(q0);
	    stmt.execute("drop view SadChairs");
	}catch(Exception e){e.printStackTrace();}

	try{
	    stmt.execute(q1);
	    stmt.execute(q2);
	}catch(Exception e){System.err.println("View DeptStats was not created");e.printStackTrace();}


	try{
	    System.out.println("for Question 2: ");
	      rs=stmt.executeQuery("select * from SadChairs");
	      while (rs.next()){
		System.out.println(rs.getString("F_Name")+rs.getInt("Diff")+" "+Math.round(100*((double)rs.getInt("F_Salary")/(double)rs.getInt("D_Budget")))+"%");
	      }
	}catch (Exception E){System.err.println("query2");E.printStackTrace();}
    }

    void update2(){
	

	upd="update y_Faculty set F_Salary=(select max_Salary from DeptStats D ";
	upd+="where D.F_SSN=y_Faculty.F_SSN )";
	upd+=" where y_Faculty.F_SSN in (select SadChairs.F_SSN from SadChairs)";

    try{
	     stmt.executeUpdate(upd);
	}catch (Exception E){System.err.println("Update2 failed");E.printStackTrace();}
	try{
	    rs=stmt.executeQuery("select F.* from y_Faculty F,SadChairs where F.F_SSN=SadChairs.F_SSN");
	}catch (Exception E){System.err.println(" select from y_Faculty failed");E.printStackTrace();}
	
	try{
	    System.out.println("\nafer update Question 2: ");
	   while (rs.next()){
		System.out.println(rs.getString("F_Name")+" "+rs.getInt("F_Salary"));
	   }
	}catch (Exception E){System.err.println("OUCH");E.printStackTrace();}

}
 
    void query3(){
	//this question can be done in a much nicer way via views
	 String q3="select distinct Fac.F_Name as Name,Fac.F_Salary as Salary,Fac.Total as Teaches,Fac.Did as Dep ";
	 q3+=" from ";
	 q3+=" (select F.F_Name,F.F_Salary,F.Did,F.F_SSN,Count(distinct O.CourseNo)as Total from Offering O,Department D,Faculty F where O.F_SSN=F.F_SSN group by D.Did,F.Did,F.F_SSN,F.F_Name,F.F_Salary)as Fac, ";
	 q3+=" (select D.Did,F.F_SSN,Count(distinct O.CourseNo) as Total from Offering O,Department D,y_Faculty F where D.F_SSN=F.F_SSN and O.F_SSN=F.F_SSN group by D.Did,F.F_SSN)as Chair ";
	 q3+=" where Fac.Did=Chair.Did and Fac.F_SSN<>Chair.F_SSN and Fac.Total>=Chair.Total order by Fac.Did,Fac.F_Salary";



try{
	    System.out.println("for Question 3: ");
	    System.out.println("Increasing Order");
	    String s=br.readLine();
	    rs=stmt.executeQuery(q3);
	    //This is 3a
       	    while (rs.next())
		System.out.println(rs.getString("Name")+" "+rs.getString("Salary")+" "+rs.getString("Teaches")+" "+rs.getString("Dep"));
	    
	    System.out.println("\nHit enter for every other");
	    s=br.readLine();
	    rs.first();
	    int i=1;
	    //this is 3b; rs.relative works just as well
	    while (rs.absolute(i)){
		System.out.println(rs.getString("Name")+" "+rs.getString("Salary")+" "+rs.getString("Teaches"));
		i=i+2;
	    }

	    System.out.println("\nHit enter for reverse");
	    s=br.readLine();
	    rs.afterLast();
	    while (rs.previous()){
	    System.out.println(rs.getString("Name")+" "+rs.getString("Salary")+" "+rs.getString("Teaches"));
	    }
	}catch (Exception E){E.printStackTrace();}
	
}		       

}