PL I – ASSIGNMENT 1

ASSIGNMENT NO.1

Title:

            DBMS using connections(Client-Data sever, two tier) Oracle/MySQL (ODBC/JDBC), SQL prompt to create data base tables insert, update data values, delete table, use table, select queries with/without where clause.

Requirements:

  1. Computer System with Linux/Open Source Operating System.
  2. Mysql Server
  3. JDK
  4. JDBC Connector
  5. Eclipse

 

Theory:

Two Tier Architecture:

exp1

This assignment provides a tutorial introduction to MySQL by showing how to use the mysql client program to create and use a simple database. mysql (sometimes referred to as the \terminal monitor” or just \monitor”) is an interactive program that allows you to connect to a MySQL server, run queries, and view the results. mysql may also be used in batch mode: you place your queries in a _le beforehand, then tell mysql to execute the contents of the _le. Both ways of using mysql are covered here.

To see a list of options provided by mysql, invoke it with the –help option:

shell> mysql –help

This assignment assumes that mysql is installed on your machine and that a MySQL server is available to which you can connect. If this is not true, contact your MySQL administrator.

(If you are the administrator, you will need to consult other sections of this manual.)

 

 

Connecting to and Disconnecting from the Server:

To connect to the server, you’ll usually need to provide a MySQL user name when you invoke mysql and, most likely, a password. If the server runs on a machine other than the one where you log in, you’ll also need to specify a hostname. Contact your administrator to find out what connection parameters you should use to connect (that is, what host, user name, and password to use). Once you know the proper parameters, you should be able to connect like this:

shell> mysql -h host -u user -p

Enter password: ********

The ******** represents your password; enter it when mysql displays the Enter password: prompt.

If that works, you should see some introductory information followed by a mysql> prompt:

 

shell> mysql -h host -u user -p

Enter password: ********

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 459 to server version: 3.22.20a-log

Type ‘help’ for help.

mysql>

The prompt tells you that mysql is ready for you to enter commands.

Some MySQL installations allow users to connect as the anonymous (unnamed) user to the server running on the local host. If this is the case on your machine, you should be able to connect to that server by invoking mysql without any options:

shell> mysql

After you have connected successfully, you can disconnect any time by typing QUIT at the

mysql> prompt:

mysql> QUIT

Bye

You can also disconnect by pressing Control-D.

Most examples in the following sections assume you are connected to the server. They

indicate this by the mysql> prompt.

Creating and Using a Database:

Suppose you have several pets in your home (your menagerie) and you’d like to keep track of various types of information about them. You can do so by creating tables to hold your data and loading them with the desired information. Then you can answer di_erent sorts of questions about your animals by retrieving data from the tables. This section shows you how to:

  • Create a database
  • Create a table
  • Load data into the table
  • Retrieve data from the table in various ways
  • Use multiple tables

 

Use the SHOW statement to _nd out what databases currently exist on the server:

 

mysql> SHOW DATABASES;

+———-+

| Database |

+———-+

| mysql |

| test    |

| tmp    |

If the test database exists, try to access it:

 

mysql> USE test

Database changed

 

Note that USE, like QUIT, does not require a semicolon. (You can terminate such statements with a semicolon if you like; it does no harm.) The USE statement is special in another way, too: it must be given on a single line.

You can use the test database (if you have access to it) for the examples that follow, but anything you create in that database can be removed by anyone else with access to it. For this reason, you should probably ask your MySQL administrator for permission to use a database of your own. Suppose you want to call yours  enagerie. The administrator needs to execute a command like this:

 

mysql> GRANT ALL ON menagerie.* TO your_mysql_name;

 

where your_mysql_name is the MySQL user name assigned to you.

 

If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:

mysql> CREATE DATABASE menagerie;

 

Under Unix, database names are case-sensitive (unlike SQL keywords), so you must always refer to your database as menagerie, not as Menagerie, MENAGERIE, or some other variant. This is also true for table names. (Under Windows, this restriction does not apply, although you must refer to databases and tables using the same lettercase throughout a given query.)

 

Creating a database does not select it for use; you must do that explicitly. To make menagerie the current database, use this command:

mysql> USE menagerie

Database changed

 

Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a USE statement as shown above. Alternatively, you can select the database on the command-line when you invoke mysql. Just specify its name after any connection parameters that you might need to provide. For example:

 

shell> mysql -h host -u user -p menagerie

Enter password: ********

Required Steps using JDBC:

T here are following steps required to create a new Database using JDBC application:

 

Import the pac kag es . Requires that you include the packag es containing the JDBC classes needed for database prog ramming . Most often, using import java.sql.* will suffice.

 

Register the JDBC driver . Requires that you initialize a driver so you can open a communications channel with the database.

 

Open a connec tion . Requires using the DriverManager.getConnection() method to create a

Connection object, which represents a physical connection with datbase server.

T o create a new database, you need not to g ive any database name while preparing database URL as mentioned in the below example.

 

Execute a query . Requires using an object of type Statement for building and submitting an SQL statement to the database.

 

Clean up the environment . Requires explicitly closing all database resources versus relying on the JVM’s garbag e collection.

 

Sample Code to Create Database:

JDBCExample.java

//STEP 1. Import required packages

import java.sql.*;

public class JDBCExample {

// JDBC driver name and database URL

static final String JDBC_DRIVER = “com.mysql.jdbc.Driver”;

static final String DB_URL = “jdbc:mysql://localhost/”;

// Database credentials

static final String USER = “username”;

static final String PASS = “password”;

 

public static void main(String[] args) {

Connection conn = null;

Statement stmt = null;

try{

//STEP 2: Register JDBC driver

Class.forName(“com.mysql.jdbc.Driver”);

//STEP 3: Open a connection

System.out.println(“Connecting to database…”);

conn = DriverManager.getConnection(DB_URL, USER, PASS);

//STEP 4: Execute a query

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

stmt = conn.createStatement();

String sql = “CREATE DATABASE STUDENTS”;

stmt.executeUpdate(sql);

System.out.println(“Database created successfully…”);

}catch(SQLException se){

//Handle errors for JDBC

se.printStackTrace();

}catch(Exception e){

//Handle errors for Class.forName

e.printStackTrace();

}finally{

//finally block used to close resources

try{

if(stmt!=null)

stmt.close();

}catch(SQLException se2){

}// nothing we can do

try{

if(conn!=null)

conn.close();

}catch(SQLException se){

se.printStackTrace();

}//end finally try

}//end try

System.out.println(“Goodbye!”);

}//end main

}//end JDBCExample

 

 

 

Now  let us compile above example as follows:

C:\>javac JDBCExample.java

C:\>

When you run JDBCExample, it produces following result:

C:\>java JDBCExample

Connecting to database…

Creating database…

Database created successfully…

Goodbye!

C:\>

 

Creating a Table

Creating the database is the easy part, but at this point it’s empty, as SHOW TABLES will tell you:

mysql> SHOW TABLES;

Empty set (0.00 sec)

Use a CREATE TABLE statement to specify the layout of your table:

 

Syntax

create table <table name>

{

fieldname-1 datatype constraints if any,

fieldname-2 datatype constraints if any,

…….

fieldname-n datatype constraints if any,

};

create table <table name> as

(

select(att-list) from <existing table name>

);

 

 

 

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR is a good choice for the name, owner, and species columns because the column values will vary in length. The lengths of those columns need not all be the same, and need not be 20. You can pick any length from 1 to 255, whatever seems most reasonable to you. (If you make a poor choice and it turns out later that you need a longer _eld, MySQL provides an ALTER TABLE statement.)

           

Now that you have created a table, SHOW TABLES should produce some output:

mysql> SHOW TABLES;

+———————+

| Tables in menagerie |

+———————+

| pet |

+———————+

To verify that your table was created the way you expected, use a DESCRIBE statement:

mysql> DESCRIBE pet;

 

| Field       | Type            | Null | Key | Default  | Extra |

 

| name     | varchar(20)  | YES |        | NULL    |          |

| owner    | varchar(20)  | YES |         | NULL   |          |

| species  | varchar(20)  | YES |         | NULL   |          |

| sex         | char(1)        | YES |          | NULL   |          |

| birth        | date             | YES|          | NULL   |          |

| death      | date             | YES|          | NULL   |          |

 

You can use DESCRIBE any time, for example, if you forget the names of the columns in

your table or what types they are.

Sample Code to create table

JdbcCreateTable.java

 

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

//STEP 4: Execute a query

System.out.println(“Creating table in given database…”);

stmt = conn.createStatement();

String sql = “CREATE TABLE REGISTRATION ” +

“(id INTEGER not NULL, ” +

” first VARCHAR(255), ” +

” last VARCHAR(255), ” +

” age INTEGER, ” +

” PRIMARY KEY ( id ))”;

stmt.executeUpdate(sql);

System.out.println(“Created table in given database…”);

}catch(SQLException se){

 

INSERT:

This is used to add one or more rows to a table. The values are separated by commas and

the data types char and date are enclosed in apostrophes. The values must br entered in the same order as

they are defined.

Inserting a single row into a table:

insert into <table name> values(fieldvalue-1,fieldvalue-2,…,fieldvalue-n);

Inserting more than one record using a single insert command:

insert into <table name> values(&fieldname-1,&fieldname-2,…&fieldname-n);

Skipping the fields while inserting:

insert into <tablename(coln names to which datas to b inserted)> values (list of values);

Other way is to give null while passing the values.

insert into <table name>(select(att_list) from <existing table name>);

Sample Code to Insert data in table:

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

//STEP 4: Execute a query

System.out.println(“Inserting records into the table…”);

stmt = conn.createStatement();

String sql = “INSERT INTO Registration ” +

“VALUES (100, ‘Zara’, ‘Ali’, 18)”;

stmt.executeUpdate(sql);

sql = “INSERT INTO Registration ” +

“VALUES (101, ‘Mahnaz’, ‘Fatma’, 25)”;

stmt.executeUpdate(sql);

sql = “INSERT INTO Registration ” +

“VALUES (102, ‘Zaid’, ‘Khan’, 30)”;

stmt.executeUpdate(sql);

sql = “INSERT INTO Registration ” +

“VALUES(103, ‘Sumit’, ‘Mittal’, 28)”;

stmt.executeUpdate(sql);

System.out.println(“Inserted records into the table…”);

}catch(SQLException se){

//Handle errors for JDBC

 

SELECT:

It is used to retrieve information from the table.it is generally refered to as querying the

table. We can either display all columns in a table or only specify column from the table.

SELECT(att_list) FROM <table name> [WHERE <condition/expression>];

Retrieval of all columns from a table:

Select * from tablename; // This query selects all rows from the table.

Retrieval of specific columns from a table:It retrieves the specified columns from the table.

Syntax: Select column_name1, …..,column_namen from table name;

 

Elimination of duplicates from the select clause: It prevents retriving the duplicated values .Distinct

keyword is to be used.

Syntax: Select DISTINCT col1, col2 from table name;

Select command with where clause: To select specific rows from a table we include ‘where’ clause in the select command. It can appear only after the ‘from’ clause.

Syntax: Select column_name1, …..,column_namen from table name where condition;

Select command with order by clause:

Syntax: Select column_name1, …..,column_namen from table name where condition order

by colmnname;

Select command to create a table:

Syntax: create table tablename as select * from existing_tablename;

Select command to insert records:

Syntax: insert into tablename ( select columns from existing_tablename);

 

Sample Code to Select data with/without where clause from table:

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 without condition…”);

String sql = “SELECT id, first, last, age FROM Registration”;

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”);

//Display values

System.out.print(“ID: ” + id);

System.out.print(“, Age: ” + age);

System.out.print(“, First: ” + first);

System.out.println(“, Last: ” + last);

}

// Select all records having ID equal or greater than 101

System.out.println(“Fetching records with condition…”);

sql = “SELECT id, first, last, age FROM Registration” +

” WHERE id >= 101 “;

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”);

//Display values

System.out.print(“ID: ” + id);

System.out.print(“, Age: ” + age);

System.out.print(“, First: ” + first);

System.out.println(“, Last: ” + last);

}

rs.close();

}catch(SQLException se){

//Handle errors for JDBC

 

 

UPDATE – It is used to alter the column values in a table. A single column may be updated or more

than one column could be updated.

update <table name> set(fieldname-1 = value, fieldname-2 = value,…,fieldname-n = value)

[WHERE <condition/expression>];

 

Sample Code to Update data with/without where clause from table:

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

//STEP 4: Execute a query

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

stmt = conn.createStatement();

String sql = “UPDATE Registration ” +

“SET age = 30 WHERE id in (100, 101)”;

stmt.executeUpdate(sql);

// Now you can extract all the records

// to see the updated records

sql = “SELECT id, first, last, age FROM Registration”;

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”);

//Display values

System.out.print(“ID: ” + id);

System.out.print(“, Age: ” + age);

System.out.print(“, First: ” + first);

System.out.println(“, Last: ” + last);

}

rs.close();

}catch(SQLException se){

//Handle errors for JDBC

 

DELETE

After inserting row in a table we can also delete them if required. The delete command

consists of a from clause followed by an optional where clause.

delete from <table name> [where <condition/expression>];

Sample Code to delete data with/without where clause from table:

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

//STEP 4: Execute a query

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

stmt = conn.createStatement();

String sql = “DELETE FROM Registration ” +

“WHERE id = 101”;

stmt.executeUpdate(sql);

// Now you can extract all the records

// to see the remaining records

sql = “SELECT id, first, last, age FROM Registration”;

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”);

//Display values

System.out.print(“ID: ” + id);

System.out.print(“, Age: ” + age);

System.out.print(“, First: ” + first);

System.out.println(“, Last: ” + last);

}

rs.close();

}catch(SQLException se){

//Handle errors for JDBC

 

Conclusion:

Thus, we implemented DBMS queries using connections(Client-Data sever, two tier) Oracle/MySQL (ODBC/JDBC), SQLprompt to create data base tables insert, update data values, delete table, use table, select queries with/without where clause.

 

*************************** QUERIES ***********************

AIM: DBMS using connections(Client-Data sever, two tier) 
Oracle/MySQL (ODBC/JDBC), SQL prompt to create data base tables insert,
 update data values, delete table, use table, 
select queries with/without where clause.

SQL> create table student_te_a(rno varchar2(10),fname varchar2(10),lname varchar2(10),deptno number(2),percent number(5,2),dob date);
Table created.

SQL> alter table student_te_a add constraint pk_1 primary key(rno);
Table altered.

SQL>create table dept(dname varchar2(10),dno number(2),location varchar2(10));
Table created.

SQL> alter table dept add constraint pk_dept_1 primary key(dno);
Table altered.

SQL> alter table student_te_a add constraint fk_stud_1 foreign key(deptno) references dept(dno);
Table altered.

SQL> insert into student_te_a values(1,'Sandesh','Gangwal',10,60.23,'12/Jul/1992');
ORA-02291: integrity constraint (SYSTEM.FK_STUD_1) violated - parent key not found

SQL> insert into dept  values('Computer',10,'pune');
1 row(s) inserted. 
SQL> insert into student_te_a values(1,'Sandesh','Gangwal',10,60.23,'12/Jul/1992');
1 row(s) inserted.
SQL> select * from dept;

SQL> select * from student_te_a; 

SQL> rollback;

SQL> select * from dept;

No data found

SQL> set autocommit on;

SQL> insert into dept  values('HR',20,'Mumbai');
1 row(s) inserted.

SQL> insert into dept  values('Finance',30,'Delhi');
1 row(s) inserted.

SQL> insert into dept  values('Computer',10,'Pune');
1 row(s) inserted.

SQL>select * from dept;

SQL> insert into student_te_a values(1,'Sandesh','Agrawal',10,60.23,'Pune');
1 row(s) inserted.

SQL> insert into student_te_a values(2,'Mangesh','Patil',10,63,'Pune');
1 row(s) inserted. 

SQL> insert into student_te_a values(3,'Jayesh','Patil',30,63,'Nasik');
1 row(s) inserted. 

SQL> insert into student_te_a values(4,'Ramesh','Pande',20,65.5,'Delhi');
1 row(s) inserted.

SQL> insert into dept values('Quality',40,'Goa');
1 row(s) inserted.

SQL> insert into student_te_a(rno,fname,lname) values(7,'Ashwini','Jangam'); 
1 row(s) inserted.  

SQL> update student_te_a set percent=66.56 where rno=1; 
1 row(s) updated. 

SQL> select rno,deptno from student_te_a where fname like 'S%'; 

SQL> select rno,deptno from student_te_a where fname like '_ _ _ _ _'; 

SQL> select rno,deptno from student_te_a where rno > 1 and deptno = 20; 

SQL> delete from student_te_a where rno > 5;

Leave a Reply

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