PL I – ASSIGNMENT 2

ASSIGNMENT NO.2

Title:

DBMS using connections(Client-application server-Data sever, three tier) Oracle/MySQL (ODBC/JDBC), SQL Joints, prompt.

Requirements:

  1. Computer System with Linux/Open Source Operating System.
  2. Mysql Server
  3. JDK
  4. JDBC Connector
  5. Eclipse
  6. Web server(Apache Tomcat 6.0)
  7. HTML/JSP

 

Theory:

Three Tier Architecture:

exp2-1

Joins:

The purpose of a join concept is to combine data spread across tables. A join is actually performed by the “where‟ clause which combines specified rows of tables.

Syntax:  select columns from table1, table2 where logical expression;

Types of Joins

  1. Simple Join
  2. Self Join
  3. Outer Join
  4. Inner Join

Types of JOIN:

  • Inner Join: Also known as equi join.

Statements generally compare two columns from two columns with the equivalence operator =. This type of join can be used in situations where selecting only those rows that have values in common in the columns specified in the ON clause, is required.

Syntax:

SELECT<columnname1>, <columnname2> <columnNameN> FROM <tablename1> INNER JOIN <tablename2>ON <tablename1>.<columnname> = <tablename2>.<columnname> WHERE <condition> ORDER BY <columnname1>;

SELECT<columnname1>, <columnname2> <columnNameN> FROM <tablename1>, <tablename2> WHERE <tablename1>.<columnname> = <tablename2>.<columnname> AND <condition> ORDER BY <columnname1>;

  • Outer Join

Outer joins are similar to inner joins, but give a little bit more flexibility when selecting data from related tables. This type of joins can be used in situations where it is desired, to select all rows from the table on left (or right, or both) regardless of whether the other table has values in common & (usually) enter NULL where data is missing.

Syntax:

SELECT<columnname1>, <columnname2> <columnNameN> FROM <tablename1> LEFT OUTER JOIN <tablename2>ON <tablename1>.<columnname> = <tablename2>.<columnname> WHERE <condition> ORDER BY <columnname1>;

 

  • Right outer Join

List the employee details with contact details (if any using right outer join. Since the RIGHT JOIN returns all the rows from the second table even if there are no matches in the first table.

Syntax:

(ANSI style)

SELECT<columnname1>, <columnname2> <columnNameN> FROM <tablename1> RIGHT OUTER JOIN <tablename2>ON <tablename1>.<columnname> = <tablename2>.<columnname> WHERE <condition> ORDER BY <columnname1>;

(Theta style)

SELECT<columnname1>, <columnname2> <columnNameN> FROM <tablename1>, <tablename2> WHERE <tablename1>.<columnname> = <tablename2>.<columnname> AND <condition> ORDER BY <columnname1>;

 

  • Cross Join

A cross join returns what known as a Cartesian product. This means that the join combines every row from the left table with every row in the right table. As can be imagined, sometimes this join produces a mess, but under the right circumstances, it can be very useful. This type of join can be used in situation where it is desired, to select all possible combinations of rows & columns from both tables. The kind of join is usually not preferred as it may run for a very long time & produce a huge result set that may not be useful.

Syntax:

SELECT<columnname1>, <columnname2> <columnNameN> FROM <tablename1>, <tablename2>;

  • Self Join

In some situation, it is necessary to join to itself, as though joining 2 separate tables.

This is referred to as self join

Syntax:

SELECT<columnname1>, <columnname2> <columnNameN> FROM <tablename1> INNER JOIN <tablename1>ON <tablename1>.<columnname> = <tablename2>.<columnname> ;

(Theta style)

SELECT<columnname1>, <columnname2> <columnNameN> FROM <tablename1>, <tablename2> WHERE <tablename1>.<columnname> = <tablename1>.<columnname> ;

Sample code using JDBC to implement Join:

System.out.println(“Connected database successfully…”);

//STEP 4: Execute a query

System.out.println(“Creating statement…”);

stmt = conn.createStatement();

// Extract records without any condition.

System.out.println(“Fetching records from two tables using Inner join…”);

String sql = “SELECT id, first, last, age, deptid,deptname FROM Registration Inner join Department where Registration.deptid=Department.deptid”;

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()){

//Retrieve by column name

int id = rs.getInt(“id”);

int age = rs.getInt(“age”);

String first = rs.getString(“first”);

String last = rs.getString(“last”);

int did = rs.getInt(“deptid”);

String dname = rs.getString(“deptname”);

//Display values or pass values on JSP Page

Conclusion:

Thus, we have implemented DBMS queries using connections(Client-application server-Data sever, three tier) Oracle/MySQL (ODBC/JDBC), SQL Joints, prompt.

 

************************** PROGRAM *************************

AIM : DBMS using connections(Client-application server-Data sever, three tier)
  Oracle/MySQL (ODBC/JDBC), SQL Joins.


//JoinsJsp.jsp
<%--
   Document   : JoinsJsp
   Created on : Sep 18, 2014, 9:05:44 AM
    Author     : sonali
--%>


<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>


       <table border="0" width="100%" cellspacing="2" cellpadding="2">
           <tbody>
                    <tr>
                    <td>JOINS</td>
                </tr>
                <tr>
                    <td><a href="#inner">INNER JOIN</a></td>
                </tr>
                <tr>
                    <td><a href="#left">LEFT OUTER JOIN</a></td>
                </tr>
                <tr>
                    <td><a href="#right">RIGHT OUTER JOIN</a></td>
                </tr>
                <tr>
                    <td><a href="#full">FULL OUTER JOIN</a></td>
                </tr>
                <tr>
                    <td><a href="#self">SELF JOIN</a></td>
                </tr>
            </tbody>  
        </table>  
        <HR width="100%">
            <%@ page import="java.io.*"%>
        <%@ page import="java.sql.*"%>
        <a id="inner">INNER JOIN</a>
      <%
           try
            {
               Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
                Statement stmt=con.createStatement();
                ResultSet rs=stmt.executeQuery("select rno,fname,lname,dno,dname,location from student s inner join dept d on s.deptno=d.dno");
                out.println("<table border=1>");
                out.println("<b><tr><td>RollNo</td><td>Fname</td><td>Lname</td><td>DeptNo</td><td>Dept Name</td><td>Location</td></tr></b>");
                while(rs.next())
                {
                 out.println("<td>"+rs.getString(1)+"</td>");
                   out.println("<td>"+rs.getString(2)+"</td>");
                    out.println("<td>"+rs.getString(3)+"</td>");
                    out.println("<td>"+rs.getString(4)+"</td>");
                    out.println("<td>"+rs.getString(5)+"</td>");
                    out.println("<td>"+rs.getString(6)+"</td>");        
                  out.println("</tr>");
                }
                   out.println("</tbody></table>");
                    rs.close();con.close();
           }
                     catch(Exception e)
                     {       
                           out.println(e);      
                     }               
          %>
            <HR width="100%">
            <a id="left">Left Outer Join</a>
        <%
          try
            {
          Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
                Statement stmt=con.createStatement();
                ResultSet rs=stmt.executeQuery("select rno,fname,lname,dno,dname,location from student s left outer join dept d on s.deptno=d.dno");
                out.println("<table border=1>");
                out.println("<b><tr><td>RollNo</td><td>Fname</td><td>Lname</td><td>DeptNo</td><td>Dept Name</td><td>Location</td></tr></b>");
                while(rs.next())
                {
                out.println("<td>"+rs.getString(1)+"</td>");
                    out.println("<td>"+rs.getString(2)+"</td>");
                    out.println("<td>"+rs.getString(3)+"</td>");
                    out.println("<td>"+rs.getString(4)+"</td>");
                    out.println("<td>"+rs.getString(5)+"</td>");
                    out.println("<td>"+rs.getString(6)+"</td>");        
                   out.println("</tr>");
                 }
                    out.println("</tbody></table>");
                    rs.close();con.close();
          }
                    catch(Exception e)
                    {       
                           out.println(e);      
                     }               

          %>
            <HR width="100%">
               <a id="right">Right Outer Join</a>
       <%
          try
            {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
                Statement stmt=con.createStatement();
                ResultSet rs=stmt.executeQuery("select rno,fname,lname,dno,dname,location from student s right outer join dept d on s.deptno=d.dno");
                out.println("<table border=1>");
                out.println("<b><tr><td>RollNo</td><td>Fname</td><td>Lname</td><td>DeptNo</td><td>Dept Name</td><td>Location</td></tr></b>");
                while(rs.next())
                {
                 out.println("<td>"+rs.getString(1)+"</td>");
                   out.println("<td>"+rs.getString(2)+"</td>");
                    out.println("<td>"+rs.getString(3)+"</td>");
                    out.println("<td>"+rs.getString(4)+"</td>");
                    out.println("<td>"+rs.getString(5)+"</td>");
                    out.println("<td>"+rs.getString(6)+"</td>");        
                    out.println("</tr>");
                 }
                    out.println("</tbody></table>");
                    rs.close();con.close();
          }
                    catch(Exception e)
                     {       
                           out.println(e);      
                     }               

          %>
           <HR width="100%">
           <a id="full">Full Outer Join</a>
        <%
          try
            {
Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
                Statement stmt=con.createStatement();
                ResultSet rs=stmt.executeQuery("select rno,fname,lname,dno,dname,location from student s full outer join dept d on s.deptno=d.dno");
                out.println("<table border=1>");
                out.println("<b><tr><td>RollNo</td><td>Fname</td><td>Lname</td><td>DeptNo</td><td>Dept Name</td><td>Location</td></tr></b>");
                while(rs.next())
                {
                   out.println("<td>"+rs.getString(1)+"</td>");
                   out.println("<td>"+rs.getString(2)+"</td>");
                   out.println("<td>"+rs.getString(3)+"</td>");
                   out.println("<td>"+rs.getString(4)+"</td>");
                   out.println("<td>"+rs.getString(5)+"</td>");
                   out.println("<td>"+rs.getString(6)+"</td>");        
                   out.println("</tr>");
                 }
                    out.println("</tbody></table>");
                   rs.close();con.close();
           }
                    catch(Exception e)
                    {       
                          out.println(e);      
                    }               
           %>  
           <HR width="100%">
          <a id="self">Self Join</a>
        <%
          try
            {
Class.forName("oracle.jdbc.driver.OracleDriver");
               Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
               Statement stmt=con.createStatement();
                ResultSet rs=stmt.executeQuery("select s.rno,s.fname,s.lname,s.deptno from student s join student s1 on s.deptno=s1.deptno");
                out.println("<table border=1>");
                out.println("<b><tr><td>RollNo</td><td>Fname</td><td>Lname</td><td>DeptNo</td></tr></b>");
                while(rs.next())
                {
                  out.println("<td>"+rs.getString(1)+"</td>");
                   out.println("<td>"+rs.getString(2)+"</td>");
                   out.println("<td>"+rs.getString(3)+"</td>");
                    out.println("<td>"+rs.getString(4)+"</td>");
                    out.println("</tr>");
                }
                    out.println("</tbody></table>");
                   rs.close();con.close();
            }
                     catch(Exception e)
                     {       
                           out.println(e);      
                     }               
           %>  
           <HR width="100%">
    </body>
</html>

Leave a Reply

Your email address will not be published. Required fields are marked *