SQL with JDBC

Q: Design at least 10 SQL queries for suitable database application using SQL DML statements:

Insert, Select, Update, Delete Clauses using distinct, count, aggregation on Client-Data sever (three tier).

Building/Testing Tools

• Eclipse (JavaEE) 
• Apache Tomcat

Code

Process.jsp

<%@ page import ="java.sql.*" %> 
<%@ page import ="javax.sql.*" %> 
<%  
String querryNo=request.getParameter("querry");
//out.println(userid);
//out.println(pwd);
try
{
//Register Driver
Class.forName("com.mysql.jdbc.Driver"); 
// create connection
Connection con = DriverManager.getConnection("jdbc:mysql://192.168.4.91:3306/3101db","3101","3101"); //ip,username,pass

 Statement st= con.createStatement();
 if(querryNo.equals("insert")){
  String name=request.getParameter("nameinput");
  String smarks=request.getParameter("marksinput");
  String toExecute=  "insert into studentmarks(student_name,student_marks) values('"+name+"',"+smarks+")";
  st.executeUpdate(toExecute);
  ResultSet rs=st.executeQuery("select * from studentmarks");
  %>
  <table border="1">
  <tr>
  <th>Id</th>
  <th>Name</th>
  <th>Marks</th>
  </tr>
  <%while (rs.next()){%>
  <tr><td><%out.println(rs.getInt(1)); %></td>
  <td><%out.println(rs.getString(2)); %></td>
  <td><%out.println(rs.getInt(3));%></td>
  </tr>
  <% }%>
  </table>
  <%
  
 }
 else if (querryNo.equals("select")){
  String limit=request.getParameter("limitinput");
  String toExecute=  "select * from studentmarks limit "+ limit;
  ResultSet rs=st.executeQuery(toExecute);
  
  
  %>
  <table border="1">
  <tr>
  <th>Id</th>
  <th>Name</th>
  <th>Marks</th>
  </tr>
  <%while (rs.next()){%>
  <tr><td><%out.println(rs.getInt(1)); %></td>
  <td><%out.println(rs.getString(2)); %></td>
  <td><%out.println(rs.getInt(3));%></td>
  </tr>
  <% }%>
  </table>
  <%
   
 }
 
 else if (querryNo.equals("update")){
  String name=request.getParameter("nameinput");
  String smarks=request.getParameter("marksinput");
  out.println("Table contents before execution of querry");
  ResultSet rs=st.executeQuery("select * from studentmarks where student_name=\'"+name+"\'");
  %>
  <table border="1">
  <tr>
  <th>Id</th>
  <th>Name</th>
  <th>Marks</th>
  </tr>
  <%while (rs.next()){%>
  <tr><td><%out.println(rs.getInt(1)); %></td>
  <td><%out.println(rs.getString(2)); %></td>
  <td><%out.println(rs.getInt(3));%></td>
  </tr>
  <% }%>
  </table>
  <%
  String toExecute=  "update studentmarks set student_marks= " + smarks + " where student_name=\'"+name+"\'";
  st.executeUpdate(toExecute);
  out.println("Table contents after execution of querry");
  rs=st.executeQuery("select * from studentmarks where student_name=\'"+name+"\'");
  
  %>
  <table border="1">
  <tr>
  <th>Id</th>
  <th>Name</th>
  <th>Marks</th>
  </tr>
  <%while (rs.next()){%>
  <tr><td><%out.println(rs.getInt(1)); %></td>
  <td><%out.println(rs.getString(2)); %></td>
  <td><%out.println(rs.getInt(3));%></td>
  </tr>
  <% }%>
  </table>
  <%
   
 }
 
 else if (querryNo.equals("delete")){
  String name=request.getParameter("nameinput");
  String toExecute= "Delete from studentmarks where student_name=\'"+name+"\'";
  st.executeUpdate(toExecute);
  ResultSet rs=st.executeQuery("select * from studentmarks");
  
  
  %>
  <table border="1">
  <tr>
  <th>Id</th>
  <th>Name</th>
  <th>Marks</th>
  </tr>
  <%while (rs.next()){%>
  <tr><td><%out.println(rs.getInt(1)); %></td>
  <td><%out.println(rs.getString(2)); %></td>
  <td><%out.println(rs.getInt(3));%></td>
  </tr>
  <% }%>
  </table>
  <%
   
 }
 
 else if (querryNo.equals("selectdistinct")){
  ResultSet rs=st.executeQuery("select distinct student_name from studentmarks");
  %>
  <table border="1">
  <tr>
  <th>Name</th>
  </tr>
  <%while (rs.next()){%>
  <tr><td><%out.println(rs.getString(1)); %></td>
  </tr>
  <% }%>
  </table>
  <%
   
 }
 else if (querryNo.equals("count")){
  ResultSet rs=st.executeQuery("select count(student_name) from studentmarks");
  %>
  <table border="1">
  <tr>
  <th>Number of Students</th>
  </tr>
  <%while (rs.next()){%>
  <tr><td><%out.println(rs.getInt(1)); %></td>
  </tr>
  <% }%>
  </table>
  <%
   
 }
 else if (querryNo.equals("average")){
  ResultSet rs=st.executeQuery("select avg(student_marks) from studentmarks");
  %>
  <table border="1">
  <tr>
  <th>Average Marks</th>
  </tr>
  <%while (rs.next()){%>
  <tr><td><%out.println(rs.getFloat(1)); %></td>
  </tr>
  <% }%>
  </table>
  <%
   
 }
 
 
     
  
}
catch(Exception e)
{
 e.printStackTrace();
}
%>

Ui.html 

<html>
<head>
<title>Form Page</title>
<body>

<form action="process.jsp" method="POST">
<H1>Insert Utility&Update&DeleteUtility</H1>
Name:<input type="text" name="nameinput">
<input type="submit" name=querry value="delete"/>
<br>
Marks:<input type="text" name="marksinput"><br>
<input type="submit" name=querry value="insert"/>
<input type="submit" name=querry value="update"/><br>
<H1>Select Utility</H1>
Number of records:<input type ="text" name="limitinput"><br>
<input type="submit" name=querry value="selectall"/>
<input type="submit" name=querry value="selectdistinct"/><br>
<h1>Aggregation Utilities</h1>
<input type="submit" name=querry value="count"/>
<input type="submit" name=querry value="average"/>
</form>
</body>
</head>

Screenshots