DMSA_CUDIV

package ass1;
import java.sql.*;
public class pp
{



           public static void main(String[] args)
           {
           try
           {

              Class.forName("com.mysql.jdbc.Driver");
              Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/tecomp","root", "tecomp");
              System.out.println("Connected database successfully...");
              Statement stmt = conn.createStatement();


            //**********create Table********************************
              String sql = "CREATE TABLE Registration " +
                           "(id INTEGER not NULL, " +
                           " first VARCHAR(255), " +
                           " last VARCHAR(255), " +
                           " age INTEGER, " +
                           " PRIMARY KEY ( id ))";



              stmt.executeUpdate(sql);





        //**********Inserting records into the table********************************
              System.out.println("Inserting records into the table...");
              sql = "INSERT INTO Registration " +
                           "VALUES (200, 'Anirudha', 'Mane', 21)";
              stmt.executeUpdate(sql);

              sql = "INSERT INTO Registration " +
                           "VALUES (201, 'Anosh', 'Magar', 22)";
              stmt.executeUpdate(sql);

              sql = "INSERT INTO Registration " +
                           "VALUES (202, 'Shraddha', 'Naik', 21)";
              stmt.executeUpdate(sql);

              sql = "INSERT INTO Registration " +
                           "VALUES(203, 'Omkar', 'Mankar', 21)";
              stmt.executeUpdate(sql);


              sql = "SELECT id, first, last, age FROM Registration";


              ResultSet rs = stmt.executeQuery(sql);



            //**********Display Table********************************

              System.out.println("Registration Table After Insertion: ");
             while(rs.next())
             {
                 System.out.print(" "+rs.getInt("id"));
                 System.out.print(" "+rs.getInt("age"));
                 System.out.print(" "+rs.getString("first"));
                 System.out.println(" "+rs.getString("last"));
             };


           //**********Updating  records from the table*******************************
                sql = "UPDATE Registration " +"SET age = 35 WHERE id in (200, 202)";
                stmt.executeUpdate(sql);

                sql = "SELECT id, first, last, age FROM Registration";
                rs = stmt.executeQuery(sql);

          //**********Display Table********************************

                System.out.println("Registration Table After Updation : ");
                  while(rs.next())
                     {
                         System.out.print(" "+rs.getInt("id"));
                         System.out.print(" "+rs.getInt("age"));
                         System.out.print(" "+rs.getString("first"));
                         System.out.println(" "+rs.getString("last"));
                     };
        /*Index*/
           stmt.executeUpdate("create index Reg on Registration(id)");
           System.out.println("Index Has Been Created");
        /*View*/
        stmt.executeUpdate("create view Regi as select first,age from Registration");
        stmt.executeUpdate("select * from Regi");

        //**********Drop Table if already exist***************
                           sql = "DROP TABLE Registration ";
                             stmt.executeUpdate(sql);
                             System.out.println("Table  deleted in given database...");


           }catch(Exception e)
           {

           }
    }
    }