Write Java JDBC methods to maintain the tot_cred attribute of the student relati

Question

Write Java JDBC methods to maintain the tot_cred attribute of the student relation in the University schema under the following operations: bullet INSERT into the takes table bullet DELETE from the takes table bullet UPDATE of the course table Write a Java JDBC method that computes the histogram of the grades for a given course in a given semester of a year (course title, semester, and year are input parameters). The method returns a map having as key the grade, e.g., A, A-, A+, B, B-, B+, etc., and as value the number of students that got that grade in the input course, year, semester. Consider an employee database with two relations employee (employee_name, street, city) and works (employee_name, company_name, salary). Find the companies whose employees earn a higher salary on average than the average salary of a given company. Provide two solutions: bullet A SQL stored function/procedure that takes the company as an input argument and prints the result companies. bullet A Java JDBC program with a method solving the query.

Explanation / Answer

System.out.println("Inserting records into the table...");

      stmt = conn.createStatement();

     

      String sql = "INSERT INTO takes " +

                   "VALUES (IT, 'firstyear', 2000)";

      stmt.executeUpdate(sql);

3

a answer

SELECT @company-name= company-name FROM works GROUP BY company-name HAVING AVG(salary)> (SELECT AVG(salary) FROM works GROUP BY company-name HAVING company-name = @company-name)

b.answer

SELECT company-name FROM works GROUP BY company-name HAVING AVG(salary)> (SELECT AVG(salary) FROM works GROUP BY company-name HAVING company-name = ‘ibm’)

2

import java.io.*;  

import java.sql.*;  

import javax.servlet.ServletException;  

import javax.servlet.http.*;  

  

public class Search extends HttpServlet {  

  

public void doGet(HttpServletRequest request, HttpServletResponse response)  

            throws ServletException, IOException {  

  

response.setContentType("text/html");  

PrintWriter out = response.getWriter();  

          

String rollno=request.getParameter("coursetitle");

String year=request.getParameter("year");   

String semester=request.getParameter("semester");

String grade=request.getParameter("grade");

char year=Char.valueOf(coursetitle);    

int year=Integer.valueOf(year);

char year=Char.valueOf(semester);  

char year=Char.valueOf(grade);  

          

try{  

Class.forName("oracle.jdbc.driver.OracleDriver");  

Connection con=DriverManager.getConnection(  

"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  

              

PreparedStatement ps=con.prepareStatement("select * from student where grade=?");  

ps.setChar(A,grade);  

              

  

ResultSet rs=ps.executeQuery();  

              

/* Printing column names */  

ResultSetMetaData rsmd=rs.getMetaData();  

int total=rsmd.getColumnCount();  

out.print("<tr>");  

for(int i=1;i<=total;i++)  

{  

out.print("<th>"+rsmd.getColumnName(i)+"</th>");

if(rsmd.getColumnName(i)=’A’)

{

out.print("</tr>");  

              

/* Printing result */  

  

while(rs.next())  

{  

out.print("<tr><td>"+rs.getInt(1)+"</td><td>"+rs.getString(2)+"  

</td><td>"+rs.getString(3)+"</td><td>"+rs.getString(4)+"</td></tr>");  

                  

}  

  

out.print("</table>");  

}

              

}catch (Exception e2) {e2.printStackTrace();}  

          

finally{out.close();}  

  

}  

}  

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.