Name : Reg.

No

Semester :

Degree : Branch :

Subject : Subject Code:

 BONAFIDE CERTIFICATE

Name of the Student: _

Register No.

This is to certify that this is a bonafide record of the work done by the above student
with Roll No. of Semester B.E
Degree in in
the _ La-
boratory during the academic year 2021 – 2022.

Staff-In-Charge Head of the Dept.

Submitted for the Practical Examination held on

Internal Examiner External Examiner

 INDEX

EX DATE NAME OF THE EXPERIMENT PAGE MARKS STAFF SIGN

NO. NO.
EX DATE NAME OF THE EXPERIMENT PAGE MARKS STAFF SIGN
NO. NO.
 IDENTIFICATION AND INSTALLATION OF DBMS
AIM
To identify and install Database Management System
SQL
SQL is Structured Query Language, which is a computer language for storing, manipulating and
retrieving data stored in a relational database. SQL is the standard language for Relational Database
System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle,
Sybase, Informix and SQL Server use SQL as their standard database language
MYSQL
It allows us to implement database operations on tables, rows, columns, and indexes. It
defines the database relationship in the form of tables (collection of rows and columns), also
known as relations. MYSQL is a relational database management system developed by Oracle that is based
on SQL. ORACLE
Oracle tutorial provides basic and advanced concepts of Oracle. Our Oracle tutorial is
designed for beginners and professionals. Oracle is a relational database management system. It is
widely used in enterprise applications.
DIFFERENCE BETWEEN SQL, MYSQL, ORACLE
PROPERTY SQL MYSQL ORACLE
PURPOSE A relational database A widely used source Hugely used
of Microsoft. database. RDBMS.
SECONDARY MODEL Graph DBMS key-value Key-value store Graph DBMS key-
OF DATABASE store document store. document store. value store
document store
RDF store.
IMPLEMENTATION C++ C and C++ C and C++
LA NGUAGE
DEVELOPE Microsoft. Oracle. Oracle.
BASIC MODEL OF DB RDBMS RDBMS RDBMS
LICENCE Commercial. Open source Commercial.
INSTALLATION OF MYSOL
Version: mysql Community Sever -8.0. 27.0
Step 1:
Download the latest version Mysql Comunity Server from Mysql official website.
Step 2:
Click the Download button on the second one (MYSQL installed-Community)
Step 3:
It will ask your MySqL crdentials to download the msi file. Now you can log in our oracle web account
(on skip.
Step 4:
Go to your downloads folder where the Mysql-installer Community file, right click on that file and click
install option
Step 5:
This window configures the installed, it may ask you for Permissions to configure the installed.
Step 6:
Read the license agreement and accept the license terms.
Step 7:
This window provides you to set up different types of mysql installations. Now select Delaut option
and click on Next
Step 8:
Click on Execute and click the configure the Server
Step 9:
Now I am selecting standalone MySQL Server and click on Next choose Developement Computer and
Click Next.
Step 10:
Now Set our MYSOL root used password and click next and then Execute.
Step 11:
Finally you will get finish button. Click on finish Now Our MYSQL on our windows to operating system.

Result:
Thus the identification and installation of Database management system (MYSQL) Verified Successfully.
Ex. No: 1 DDL, DML AND TCL FOR INSERTING, DELETING, UPDATING, RETRIEVING TABLES
Date:
AIM
To create table and execute data definition command, data manipulation command for inserting,
deleting, updating retrieving tables and transaction control Statement.
DATA DEFINITION COMMAND
DDL Command used to define database schema, it deals with description of dB schema.
 CREATE
 ALTER
 DROP
 TRUNCATE
 RENAME
DATA MANIPULATION COMMAND
DML command deals with manipulation of data Present in a database.
 INSERT
 DELETE
 UPDATE
DATA QUERY COMMAND
Data query Language used to fetch data from tables based on condition.
 SELECT
TRANSACTION CONTROL STATEMENT
It is used to manage transaction in the database TCL allows the student to the group together
in the logical transaction.
 Commit
 Rollback
 Savepoint
QUERIES
mysql> create database userdatabase;
Query OK, 1 row affected (1.50 sec)
mysql> use userdatabase;
Database changed
mysql> create table student (regno int, name varchar(25),age int, department varchar(20));
Query OK, 0 rows affected (1.15 sec)
mysql> insert into student (regno,name,age,department) values (101,'Raju',23,'CSE'),
(102,'Nega',24,'EEE'), (103,'Alex',21,'MECH');
Query OK, 3 rows affected (0.09 sec)
mysql> select * from student;
+-------+------+------+------------+
| regno | name | age | department |
+-------+------+------+------------+
| 101 | Raju | 23 | CSE |
| 102 | Nega | 24 | EEE |
| 103 | Alex | 21 | MECH |
+-------+------+------+------------+
mysql> rename table student to stu;
Query OK, 0 rows affected (0.64
sec) mysql> select * from stu;
+-------+------+------+------------+
| regno | name | age | department |
+-------+------+------+------------+
| 101 | Raju | 23 | CSE |
| 102 | Nega | 24 | EEE |
| 103 | Alex | 21 | MECH |
+-------+------+------+------------+
mysql> alter table stu add cit varchar(20);
mysql> select * from stu;
+-------+------+------+------------+------+
| regno | name | age | department | cit |
+-------+------+------+------------+------+
| 101 | Raju | 23 | CSE | NULL |
| 102 | Nega | 24 | EEE | NULL |
| 103 | Alex | 21 | MECH | NULL |
+-------+------+------+------------+------+
mysql> update stu set cit='Madurai' where regno=101;
mysql> update stu set cit='Theni' where regno=102;
mysql> update stu set cit='Trichy' where regno=103;
mysql> select * from stu;
+-------+------+------+------------+---------+
| regno | name | age | department | cit |
+-------+------+------+------------+---------+
| 101 | Raju | 23 | CSE | Madurai |
| 102 | Nega | 24 | EEE | Theni |
| 103 | Alex | 21 | MECH | Trichy |
+-------+------+------+------------+---------+
mysql> delete from stu where name='Raju';
mysql> select * from stu;
+-------+------+------+------------+--------+
| regno | name | age | department | cit |
+-------+------+------+------------+--------+
| 102 | Nega | 24 | EEE | Theni |
| 103 | Alex | 21 | MECH | Trichy |
+-------+------+------+------------+--------+
mysql> commit;
mysql> start transaction;
mysql> insert into stu values(103,'Durga',21,'CSE','Coimbatore');
mysql> savepoint A;
mysql> insert into stu values(104,'Rani',23,'CSE','Tirunelveli');
mysql> savepoint B;
mysql> select * from stu;
+-------+-------+------+------------+-------------+
| regno | name | age | department | cit |
+-------+-------+------+------------+-------------+
| 102 | Nega | 24 | EEE | Theni |
| 103 | Alex | 21 | MECH | Trichy |
| 103 | Durga | 21 | CSE | Coimbatore |
| 104 | Rani | 23 | CSE | Tirunelveli |
+-------+-------+------+------------+-------------+
mysql> rollback to savepoint A;
mysql> select * from stu;
+-------+-------+------+------------+------------+
| regno | name | age | department | cit |
+-------+-------+------+------------+------------+
| 102 | Nega | 24 | EEE | Theni |
| 103 | Alex | 21 | MECH | Trichy |
| 103 | Durga | 21 | CSE | Coimbatore |
+-------+-------+------+------------+------------+

RESULT
Thus the Data definition command, Data manipulation Command for inserting, deleting, updating and
relieving table and transaction Control Statement was executed successfully.
Ex. No: 2 DATABASE QUERYING – SIMPLE QUERIES, NESTED QUERIES, SUB QUERIES

Date:

AIM
To Create Table and use the statements for Simple Queries, Nested Queries, Sub Queries and Joins
QUERY
SIMPLE QUERIES
mysql> insert into student1 values(1,'Prasanna','CSE');
mysql> insert into student1 values(2,'Mithun','ECE');
mysql> insert into student1 values(3,'Naga','CSE');
mysql> insert into student1 values(4,'shyam','EEE');
mysql> select * from student1;
+-------+----------+------+
| stuid | name | dept |
+-------+----------+------+
| 1 | Prasanna | CSE |
| 2 | Mithun | ECE |
| 3 | Naga | CSE |
| 4 | shyam | EEE |
+-------+----------+------+
mysql> select DISTINCT dept from student1;
+------+
| dept |
+------+
| CSE |
| ECE |
| EEE |
+------+
mysql> select * from student1 where dept='CSE' ORDER BY stuid DESC;
+-------+----------+------+
| stuid | name | dept |
+-------+----------+------+
| 3 | Naga | CSE |
| 1 | Prasanna | CSE |
+-------+----------+------+
mysql> select dept, count(*) from student1 GROUP BY dept;
+------+----------+
| dept | count(*) |
+------+----------+
| CSE | 2|
| ECE | 1|
| EEE | 1|
+------+----------+
mysql> select dept, count(*) from student1 GROUP BY dept HAVING count(dept)>1;
+------+----------+
| dept | count(*) |
+------+----------+
| CSE | 2|
+------+----------+
mysql> select * from student1 where dept='CSE' OR stuid>3;
+-------+----------+------+
| stuid | name | dept |
+-------+----------+------+
| 1 | Prasanna | CSE |
| 3 | Naga | CSE |
| 4 | shyam | EEE |
+-------+----------+------+
mysql> select * from student1 where dept='CSE' AND stuid>3;
Empty set (0.00 sec)
mysql> select * from student1 where dept='CSE' AND stuid>2;
+-------+------+------+
| stuid | name | dept |
+-------+------+------+
| 3 | Naga | CSE |
+-------+------+------+
mysql> select * from student1 where dept IN ('EEE','ECE');
+-------+--------+------+
| stuid | name | dept |
+-------+--------+------+
| 2 | Mithun | ECE |
| 4 | shyam | EEE |
+-------+--------+------+
mysql> select * from student1 where dept NOT IN ('EEE','ECE');
+-------+----------+------+
| stuid | name | dept |
+-------+----------+------+
| 1 | Prasanna | CSE |
| 3 | Naga | CSE |
+-------+----------+------+
mysql> select * from student1 where stuid between 1 and 3;
+-------+----------+------+
| stuid | name | dept |
+-------+----------+------+
| 1 | Prasanna | CSE |
| 2 | Mithun | ECE |
| 3 | Naga | CSE |
+-------+----------+------+
PRIMARY KEY CONSTRAINT
mysql> create table employee (empid int primary key, name varchar(35), salary int);
mysql> insert into employee values(5001, 'AAA',10000);
mysql> insert into employee values(5002, 'BBB',8000);
mysql> insert into employee values(5002, 'ABC',4000);
ERROR 1062 (23000): Duplicate entry '5002' for key 'employee.PRIMARY'
mysql> insert into employee values(5003, 'CCC',9200);
mysql> select * from employee;
+-------+------+--------+
| empid | name | salary |
+-------+------+--------+
| 5001 | AAA | 10000 |
| 5002 | BBB | 8000 |
| 5003 | CCC | 9200 |
+-------+------+--------+
FOREIGN KEY CONSTRAINT
mysql> create table empdetail(id int primary key, depname varchar(20), empid int, foreign
key(empid) references employee(empid));
mysql> insert into empdetail values(101,'Marketing',5001);
mysql> insert into empdetail values(102,'Admin',5002);
mysql> insert into empdetail values(103,'clerk',5004);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`userdatabase`.`empdetail`, CONSTRAINT `empdetail_ibfk_1` FOREIGN KEY (`empid`) REFERENCES
`employee` (`empid`))
mysql> select * from empdetail;
+-----+-----------+-------+
| id | depname | empid |
+-----+-----------+-------+
| 101 | Marketing | 5001 |
| 102 | Admin | 5002 |
+-----+-----------+-------+
NOT NULL CONSTRAINT, UNIQUE CONSTRAINT, CHECK CONSTRAINT
mysql> create table student(regno int UNIQUE, Name varchar(24) NOT NULL, age int
check(age>=18));
mysql> insert into student values (101,'uma',20);
mysql> insert into student values (101,'naga',23);
ERROR 1062 (23000): Duplicate entry '101' for key 'student.regno'

mysql> insert into student values (102,NULL,43);

ERROR 1048 (23000): Column 'Name' cannot be null

mysql> insert into student values (102,'naga',16);

ERROR 3819 (HY000): Check constraint 'student_chk_1' is violated.
mysql> insert into student values (103,'devi',NULL);
mysql> select * from student;
+-------+------+------+
| regno | Name | age |
+-------+------+------+
| 101 | uma | 20 |
| 103 | devi | NULL |
+-------+------+------+
DEFAULT CONSTRAINT
mysql> create table college (id int NOT NULL, name varchar(20), clgname varchar(20) DEFAULT
'SCE');
mysql> insert into college (id,name) values (1,'Aarthi'),(2,'Harshini'),(3,'Divya');
mysql> select * from college;
+----+----------+---------+
| id | name | clgname |
+----+----------+---------+
| 1 | Aarthi | SCE |
| 2 | Harshini | SCE |
| 3 | Divya | SCE |
+----+----------+---------+
SELECTION OPERATION
mysql> select * from college where id=1;
+----+--------+---------+
| id | name | clgname |
+----+--------+---------+
| 1 | Aarthi | SCE |
+----+--------+---------+
PROJECTION OPERATION
mysql> select id, name from college;
+----+----------+
| id | name |
+----+----------+
| 1 | Aarthi |
| 2 | Harshini |
| 3 | Divya |
+----+----------+
UNION OPERATION
mysql> create table csedept (regno int, name varchar(24));
mysql> create table ecedept(regno int, name varchar(23));
mysql> insert into csedept (regno,name) values (401,'AAA'), (402,'BBB');
mysql> insert into ecedept (regno,name) values (601,'ABC'), (602,'BCD');
mysql> select * from csedept;
+-------+------+
| regno | name |
+-------+------+
| 401 | AAA |
| 402 | BBB |
+-------+------+
mysql> select * from ecedept;
+-------+------+
| regno | name |
+-------+------+
| 601 | ABC |
| 602 | BCD |
+-------+------+
mysql> select * from csedept union select * from ecedept;
+-------+------+
| regno | name |
+-------+------+
| 401 | AAA |
| 402 | BBB |
| 601 | ABC |
| 602 | BCD |
+-------+------+

RESULT

Thus the simple query, nested query, sub query have executed successfully.
 EX.NO.3 JOIN OPERATIONS
Date:

i) EQUI JOIN
mysql> create table A(id int, name varchar(20),fees int);
mysql> insert into A (id,name,fees) values(1,'Jeni',10000),(2,'Mike',3000),(3,'Fedo',2300);
mysql> select * from A;
+------+------+-------+
| id | name | fees |
+------+------+-------+
| 1 | Jeni | 10000 |
| 2 | Mike | 3000 |
| 3 | Fedo | 2300 |
+------+------+-------+
mysql> create table B(id int, dept varchar(20),phno int);
mysql> insert into B (id,dept,phno) values(1,'ECE',224435),(3,'EEE',3464560),(4,'MECH',235657);
mysql> select * from B;
+------+------+---------+
| id | dept | phno |
+------+------+---------+
| 1 | ECE | 224435 |
| 3 | EEE | 3464560 |
| 4 | MECH | 235657 |
+------+------+---------+
mysql> select name, phno from A join B on A.id=B.id;
+------+---------+
| name | phno |
+------+---------+
| Jeni | 224435 |
| Fedo | 3464560 |
+------+---------+
ii) LEFT OUTER JOIN
mysql> select * from A left outer join B on A.id=B.id;
+------+------+-------+------+------+---------+
| id | name | fees | id | dept | phno |
+------+------+-------+------+------+---------+
| 1 | Jeni | 10000 | 1 | ECE | 224435 |
| 2 | Mike | 3000 | NULL | NULL | NULL |
| 3 | Fedo | 2300 | 3 | EEE | 3464560 |
+------+------+-------+------+------+---------+
iii) RIGHT OUTER JOIN
mysql> select * from A right outer join B on A.id=B.id;
+------+------+-------+------+------+---------+
| id | name | fees | id | dept | phno |
+------+------+-------+------+------+---------+

| 1 | Jeni | 10000 | 1 | ECE | 224435 |

| 3 | Fedo | 2300 | 3 | EEE | 3464560 |
| NULL | NULL | NULL | 4 | MECH | 235657 |
+------+------+-------+------+------+---------+
NESTED QUERY
mysql> select name,fees from A where id in(select id from B);
+------+-------+
| name | fees |
+------+-------+
| Jeni | 10000 |
| Fedo | 2300 |
+------+-------+
AGGREGATE FUNCTION
mysql> create table stumark(id int, name varchar(20), mark int);
mysql> insert into stumark(id,name,mark) values(1,'AAA',56),(2,'BBB',97),(3,'CCC',78),(4,'DDD',89);
mysql> select * from stumark;
+------+------+------+
| id | name | mark |
+------+------+------+
| 1 | AAA | 56 |
| 2 | BBB | 97 |
| 3 | CCC | 78 |
| 4 | DDD | 89 |
+------+------+------+
mysql> select Avg(mark) from stumark;
+-----------+
| Avg(mark) |
+-----------+
| 80.0000 |
+-----------+

mysql> select sum(mark) from stumark;

+-----------+
| sum(mark) |
+-----------+
| 320 |
+-----------+
mysql> select Max(mark) from stumark;
+-----------+
| Max(mark) |
+-----------+
| 97 |
+-----------+
mysql> select Min(mark) from stumark;
+-----------+
| Min(mark) |
+-----------+
| 56 |
+-----------+
mysql> select count(Name) from stumark;
+-------------+
| count(Name) |
+-------------+
| 4|
+-------------+

RESULT

Thus the Join operations have executed successfully.

Ex. No: 4 VIEWS, SEQUENCES, SYNONYMS
Date:
AIM
To create database and apply Views, Sequences, Synonyms.
VIEWS
Views in SQL are considered as a virtual table. A view also contains rows and columns. To create
the view, we can select the fields from one or more tables present in the database. A view can either have
specific rows based on certain condition or all the rows of a table.
SEQUENCES
Sequences area feature that some DBMS products implement to provide users with a mechanism to
generate unique values. The Sequence ensures that each call to it returns a unique value. This is
particularly important when the Sequence's result is used as a Primary Key.
SYNONYMS
In databases, a synonym is an alias or alternate name for a table, view, sequence, or other schema
object. They are used mainly to make it easy for users to access database objects owned by other users.
QUERY
mysql> use userdatabase;
Database changed
mysql> create table flowers(id int NOT NULL AUTO_INCREMENT,name varchar(20), quantity
int,primary key(ID));
mysql> insert into flowers (name,quantity) values ('jasmine',20),('rose',12),('lilly',30),('lotus',25);
mysql> select * from flowers;
+----+---------+----------+
| id | name | quantity |
+----+---------+----------+
| 1 | jasmine | 20 |
| 2 | rose | 12 |
| 3 | lilly | 30 |
| 4 | lotus | 25 |
+----+---------+----------+
mysql> delete from flowers where id=3;
mysql> select * from flowers;
+----+---------+----------+
| id | name | quantity |
+----+---------+----------+
| 1 | jasmine | 20 |
| 2 | rose | 12 |
| 4 | lotus | 25 |
+----+---------+----------+
3 rows in set (0.01 sec)
mysql> delete from flowers where id=3;
mysql> select * from flowers;
+----+---------+----------+
| id | name | quantity |
+----+---------+----------+
| 1 | jasmine | 20 |
| 2 | rose | 12 |
| 4 | lotus | 25 |
+----+---------+----------+
mysql> insert into flowers values(5,'mullai',45);
mysql> insert into flowers values(6,'Sembaruthi',45);
mysql> insert into flowers values(7,'sunflower',45);
mysql> select * from flowers;
id Name qunatity
1 jasmine 20
2 rose 12
4 lotus 25
5 mullai 45
6 sembaruthi 45
7 sunflower 45

mysql> create view infa As select id,name from flowers;

mysql> select * from infa;
id Name
1 jasmine
2 rose
4 lotus
5 mullai
 6 sembaruthi
7 Sunflower

mysql> alter view infa As select id,name,quantity from flowers;

mysql> select * from infa;
id Name Quantity
1 jasmine 20
2 rose 12
4 lotus 25
5 mullai 45
6 sembaruthi 45
7 sunflower 45

mysql> update infa set name='jathimullai' where id=5;

mysql> select * from infa;
id Name Quantity
1 jasmine 20
2 rose 12
4 lotus 25
5 jathimullai 45
6 sembaruthi 45
7 sunflower 45

mysql> delete from infa where id=6;

mysql> select * from infa;
id Name Quantity
1 jasmine 20
2 rose 12
4 lotus 25
5 mullai 45
7 sunflower 45

mysql> drop view infa;

mysql> select * from infa;
Error: userdata.infa does not exist

RESULT
Thus the database created and applied Views, Sequences and Synonyms successfully in a database.
Ex. No: 5 DATABASE PROGRAMMING: IMPLICIT AND EXPLICIT CURSORS
Date:
AIM
To create a database and apply Implicit and Explicit Cursors
IMPLICIT CURSORS
An implicit cursor has attributes that return information about the most recently run SELECT or
DML statement that is not associated with a named cursor. Note: You can use cursor attributes only in
procedural statements, not in SQL statements.
EXPLICIT CURSORS
An explicit cursor is a named pointer to a private SQL area that stores information for processing
a specific query or DML statement—typically, one that returns or affects multiple rows. You can use an
explicit cursor to retrieve the rows of a result set one at a time. Access particular column value from a
table using Explicit Cursor
mysql> use maha;
Database changed
EXPLICIT CURSOR
EXAMPLE 1 - Access particular column value from a table using Explicit Cursor
mysql> select * from table1;
+------+---------+-------+
| id | name | class |
+------+---------+-------+
| 1 | naga | MBA |
| 2 | Anil | MCA |
| 3 | mith | MBBS |
| 4 | sujitha | BCA |
| 5 | Raga | MCA |
+------+---------+-------+
mysql> DELIMITER $$
mysql> CREATE PROCEDURE list_name(INOUT name_list varchar(4000))
-> BEGIN
-> DECLARE is_done INTEGER DEFAULT 0;
-> DECLARE s_name varchar(100) DEFAULT "";
-> DECLARE stud_cursor CURSOR FOR
-> SELECT name FROM table1;
 -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done=1;
-> OPEN stud_cursor;
-> get_list: LOOP
-> FETCH stud_cursor INTO s_name;
-> IF is_done=1 THEN
-> LEAVE get_list;
-> END IF;
-> SET name_list=CONCAT(s_name,";",name_list);
-> END LOOP get_list;
-> CLOSE stud_cursor;
-> END$$
Query ok, 0 rows affected
mysql> SET @name_list="";
-> $$
Query ok, 0 rows affected
mysql> CALL list_name(@name_list);
-> $$
Query ok, 0 rows affected
mysql> SELECT @name_list;
-> $$

@ name_List

Raga;Sujitha;Mith;Anil;Naga;

EXAMPLE 2 - Create a database and a table and insert some rows into it from accessing values
from other table using Explicit Cursor
mysql> select * from table1;
+------+---------+-------+
| id | name | class |
+------+---------+-------+
| 1 | naga | MBA |
| 2 | Anil | MCA |
| 3 | mith | MBBS |
| 4 | sujitha | BCA |
| 5 | Raga | MCA |
+------+---------+-------+
mysql>create table newtable(id int, name varchar(20),class varchar(20));
mysql> select * from newtable;
mysql> DELIMITER $$
mysql> CREATE PROCEDURE firstCursor()
-> BEGIN
-> DECLARE d INT DEFAULT 0;
-> DECLARE c_id INT;
-> DECLARE c_name, c_class VARCHAR(20);
-> DECLARE Get_cur CURSOR FOR SELECT * FROM table1;
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
-> SET d = 1;
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
-> SET d = 1;
-> OPEN Get_cur;
-> lbl: LOOP
-> IF d = 1 THEN
-> LEAVE lbl;
-> END IF;
-> IF NOT d = 1 THEN
-> FETCH Get_cur INTO c_id, c_name, c_class;
-> INSERT INTO newtable VALUES(c_id, c_name, c_class);
-> END IF;
-> END LOOP;
-> CLOSE Get_cur;
-> END$$
Query ok, 0 rows affected
mysql> CALL maha.firstCursor();
-> $$
Query ok, 0 rows affected
mysql> SELECT * FROM newtable;
-> $$

id name class

1 Naga MBA

2 Anil MCA

3 Mith MBBS

4 Sujitha BCA

5 Raga MCA

RESULT
Thus, the Implicit and Explicit Cursors query was executed successfully.
Ex. No: 6 PROCEDURES AND FUNCTIONS
Date:
AIM
To create a database using Procedures and Functions
PROCEDURES
Database Procedures (sometimes referred to as Stored Procedures or Procs) are subroutines that
can contain one or more SQL statements that perform a specific task. They can be used for data validation,
access control, or to reduce network traffic between clients and the DBMS servers
FUNCTIONS
The functions of a DBMS include concurrency, security, backup and recovery, integrity and data
descriptions. Database management systems provide a number of key benefits but can be costly and
time- consuming to implement.
PROCEDURE
mysql> use maha;
Database changed
USER VARIABLE
mysql> DELIMITER $$
mysql> CREATE PROCEDURE User_Variables( )
-> BEGIN
-> SET @x=15;
-> SET @y=10;
-> SELECT @x,@y,@x-@y;
-> END$$
Query ok, 0 rows affected
mysql> CALL User_Variables( );
-> $$
@X @Y @Z

15 10 5
LOCAL VARIABLE
mysql> DELIMITER $$
mysql> CREATE PROCEDURE Local_Variables( )
-> BEGIN
-> DECLARE a, b, c INT;
-> SET a = 20;
-> SET b = 5;
-> SET c = a * b;
-> SELECT a, b, c;
-> END$$
Query ok, 0 rows affected
mysql> CALL Local_Variables( );
-> $$

a b c

20 5 100

SIMPLE PROCEDURE
mysql> select * from table1;

id name class

1 Naga MBA

2 Anil MCA

3 Mith MBBS

4 Sujitha BCA

5 Raga MCA

mysql> CREATE PROCEDURE TEST( )

-> SELECT * FROM table1;
Query ok, 0 rows affected
mysql> CALL TEST( );
 id name class

1 Naga MBA

2 Anil MCA

3 Mith MBBS

4 Sujitha BCA

5 Raga MCA

PROCEDURE PARAMETER IN
mysql> DELIMITER $$
mysql> CREATE PROCEDURE my_IN (IN var1 INT)
-> BEGIN
-> SELECT * FROM table1 LIMIT var1;
-> END$$
Query ok, 0 rows affected
mysql> CALL my_IN(3);
-> $$

id name class

1 Naga MBA

2 Anil MCA

3 Mith MBBS

PROCEDURE PARAMETER OUT

mysql> DELIMITER $$
mysql> CREATE PROCEDURE my_proc_OUT (OUT coursecount INT)
-> BEGIN
-> SELECT COUNT (class) INTO coursecount FROM table1;
-> END$$
mysql> CALL my_proc_OUT(@coursecount)$$
mysql> SELECT @coursecount$$
 @coursecount

PROCEDURE PARAMETER INOUT

mysql> DELIMITER $$
mysql> CREATE PROCEDURE course_counts1 (IN var1 VARCHAR(10), OUT count INT)
-> BEGIN
-> SELECT COUNT (*) INTO count FROM table1 WHERE class=var1;
-> END$$
Query ok, 0 rows affected
mysql> CALL course_counts1('MCA',@count)$$
mysql> SELECT @count $$

@course

mysql> CALL course_counts1('BCA',@count)$$

Query ok, 0 rows affected
mysql> SELECT @count$$

@course

FUNCTION EXAMPLE
mysql> CREATE FUNCTION hello1 (s CHAR(20))
-> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
-> $$
Query ok, 0 rows affected
mysql> SELECT hello1('world');
-> $$
hello1(‘World’)
HelloWorld!
mysql>create table customer (id int, name varchar(20), age int);
mysql>insert into customer values (101,’pater’,32), (102,’Joseph’,30), (103,’John’,28),
(105,’stephen’,45), (105,’suzi’,26), (106,’Bob’,25);
mysql> select * from customer;

Id name age

101 Pater 32

102 Joseph 30

103 John 28

104 Stephen 45

105 Suzi 26

106 Bob 25

mysql> DELIMITER $$
mysql> CREATE FUNCTION customer_occupation(age int)
-> RETURNS VARCHAR(20)
-> DETERMINISTIC
-> BEGIN
-> DECLARE occupation VARCHAR(20);
-> IF age>35 THEN
-> SET occupation='Scientist';
-> ELSEIF (age<=35 AND age>=30) THEN
-> SET occupation='Engineer';
-> ELSEIF age<30 THEN
-> SET occupation='Actor';
-> END IF;
-> RETURN(occupation);
-> END$$
Query ok, 0 rows affected
mysql> SELECT name,age,customer_occupation(age) FROM customer ORDER BY age;
-> $$
 name age customer_occupation(age)

Bob 25 Actor

Suzi 26 Actor

John 28 Actor

Joseph 30 Engineer

Pater 32 Engineer

Stephen 45 Scietist

PROCEDURE
mysql> DELIMITER $$
mysql> CREATE PROCEDURE updatevalues(IN newid INT)
-> BEGIN
-> UPDATE customer SET name='AAA' WHERE id=newid;
-> END$$
mysql> CALL updatevalues(101)$$
mysql> SELECT * from customer$$

Id name age

101 AAA 32

102 Joseph 30

103 John 28

104 Stephen 45

105 Suzi 26

106 Bob 25

EXAMPLE 2
mysql> DELIMITER $$
mysql> CREATE PROCEDURE updatevalues1(IN newid INT,IN newname varchar(20))
-> BEGIN
-> UPDATE customer SET name=newname WHERE id=newid;
-> END$$
mysql> CALL updatevalues1(103,'NAGA')$$
mysql> SELECT * from customer$$

Id name age

101 AAA 32

102 Joseph 30

103 John 28

104 Stephen 45

105 Suzi 26

106 Bob 25

RESULT
Thus Procedures and Functions for database was executed successfully.
Ex. No: 7 TRIGGERS
Date:
AIM
To Create and implement the database triggers
TRIGGERS
 A trigger is a stored program invoked automatically in response to an event such as insert, update,
or delete that occurs in the associated table. For example, you can define a trigger that is invoked
automatically before a new row is inserted into a table.
 MySQL supports triggers that are invoked in response to the INSERT, UPDATE or DELETE event.
 The SQL standard defines two types of triggers: row-level triggers and statement-level triggers.
 A row-level trigger is activated for each row that is inserted, updated, or deleted.
 A statement-level trigger is executed once for each transaction regardless of how many rows are
inserted, updated, or deleted.
 MySQL supports only row-level triggers. It doesn’t support statement-level triggers.
MANAGING MYSQL TRIGGERS
 Create triggers – describe steps of how to create a trigger in MySQL.
 Drop triggers – show you how to drop a trigger.
 Create a BEFORE INSERT trigger – show you how to create a BEFORE INSERT trigger to
maintain a summary table from another table.
 Create an AFTER INSERT trigger – describe how to create an AFTER INSERT trigger to insert
data into a table after inserting data into another table.
 Create a BEFORE UPDATE trigger – learn how to create a BEFORE UPDATE trigger that validates
data before it is updated to the table.
 Create an AFTER UPDATE trigger – show you how to create an AFTER UPDATE trigger to log the
changes of data in a table.
 Create a BEFORE DELETE trigger – show how to create a BEFORE DELETE trigger.
 Create an AFTER DELETE trigger – describe how to create an AFTER DELETE trigger.
 Create multiple triggers for a table that have the same trigger event and time – MySQL 8.0 allows
you to define multiple triggers for a table that have the same trigger event and time.
 Show triggers – list triggers in a database, table by specific patterns.
Query
mysql> CREATE TABLE employee(id int, name varchar(20), occupation varchar(20), working_hours
int);
mysql> Insert into employee values(1,”AAA”, “Actor”, 12), (2,”BBB”,”Teacher”,14);
mysql> select * from employee;
------+------+------------+------------------------+
| id | name | occupation | working_hours |
+------+------+------------+----------------------+
| 1 | AAA | Actor | 12 |
| 2 | BBB | Teacher | 14 |
+------+------+------------+----------------------+
BEFORE INSERT TRIGGER
mysql> DELIMITER $$
mysql> Create Trigger before_inserthour
-> BEFORE INSERT ON employee FOR EACH ROW
-> BEGIN
-> IF NEW.working_hours<0 THEN SET NEW.working_hours=0;
-> END IF;
-> END $$
mysql> insert into employee values(4,"DDD","Nurse",-12);
-> $$
mysql> select * from employee;
-> $$
mysql> show triggers;
-> $$
------+------+------------+------------------------+
| id | name | occupation | working_hours |
+------+------+------------+----------------------+
| 1 | AAA | Actor | 12 |
| 2 | BBB | Teacher | 14 |
| 4 | DDD | Nurse | 0 |
+------+------+------------+----------------------+
AFTER INSERT TRIGGER
mysql> create table emp_details1(id int,name varchar(25),occupation varchar(25),working_hours int,
Lastinserted Time);
mysql> DELIMITER $$
mysql> Create Trigger after_insert_details2
 -> AFTER INSERT ON employee FOR EACH ROW
-> BEGIN
-> INSERT INTO emp_details1 VALUES (NEW.id, NEW.name, NEW.occupation, NEW.working_hours,
CURTIME( ));
-> END$$
mysql> INSERT into employee values(8,'MMM','Doctor',23);
-> $$
mysql> select * from employee;
-> $$
------+------+------------+------------------------+
| id | name | occupation | working_hours |
+------+------+------------+----------------------+
| 1 | AAA | Actor | 12 |
| 2 | BBB | Teacher | 14 |
| 4 | DDD | Nurse | 0 |
| 8 | MMM| Doctor | 23 |
+------+------+------------+----------------------+
mysql> select * from emp_details1;
-> $$
------+------+------------+--------------------------------------------+
| id | name | occupation | working_hours | Lastinserted |
+------+------+------------+------------------------------------------+
| 8 | MMM| Doctor | 23 | 21.18.59 |
+------+------+------------+------------------------------------------+
BEFORE UPDATE TRIGGER
mysql> DELIMITER $$
mysql> CREATE TRIGGER before_update2
-> BEFORE UPDATE ON employee FOR EACH ROW
-> BEGIN
-> DECLARE error_msg VARCHAR(200);
-> SET error_msg=('The working hours cannot be greater than 30');
-> IF new.working_hours>30 THEN
-> SIGNAL SQLSTATE '45000'
-> SET MESSAGE_TEXT=error_msg;
-> END IF;
-> END $$
mysql> update employee set working_hours=32 where id=3;
 -> $$
-------------------------------+---------------------+-------------+
| error_msg |
+-----------------------------+---------------------+--------------+
| The working hours cannot be greater than 30 |
+-----------------------------+---------------------+--------------+
AFTER UPDATE TRIGGER
mysql> create table student(id int, name varchar(20), class int);
mysql> insert into student values(101,'AAA',5),(102,'BBB',8),(103,'CCC',4);
mysql> select * from student;
------+------+------------+---+
| id | name | class |
+------+------+------------+-+
| 101 | AAA | 5 |
| 102 | BBB | 8 |
| 103 | CCC | 4 |
+------+------+------------+--+
mysql> create table studentlog(user longtext,desc longtext);
mysql> select * from studentlog;
Empty Set
mysql> CREATE TRIGGER after_updatestu1
-> AFTER UPDATE ON student FOR EACH ROW
-> BEGIN
-> INSERT into studentlog VALUES (user( ), CONCAT('update', OLD.name,'Prev:' ,OLD.class, 'Pres:',
NEW.class));
-> END$$
mysql> UPDATE student SET class = class + 1;
-> $$
mysql> select * from student;
-> $$
------+------+------------+---+
| id | name | class |
+------+------+------------+-+
| 101 | AAA | 6 |
| 102 | BBB | 9 |
| 103 | CCC | 5 |
+------+------+------------+--+
mysql> select * from studentlog;
 -> $$
------+------+------------+-----------------------------+
| user | pass |
+------+------+------------+---------------------------+
| root@localhost | updateAAAPrev:5 Pres:6 |
| root@localhost | updateAAAPrev:8 Pres:9 |
| root@localhost | updateAAAPrev:4 Pres:5 |
+------+------+------------+---------------------------+
BEFORE DELETE TRIGGER
mysql> select * from employee;
-> $$
------+------+------------+------------------------+
| id | name | occupation | working_hours |
+------+------+------------+----------------------+
| 1 | AAA | Actor | 12 |
| 2 | BBB | Teacher | 14 |
| 4 | DDD | Nurse | 0 |
| 8 | MMM| Doctor | 23 |
+------+------+------------+----------------------+
mysql> create table delemp1(id int PRIMARY KEY AUTO_INCREMENT, name varchar(30), occupation
varchar(20), deleted_time TIMESTAMP DEFAULT NOW( ));
mysql> CREATE TRIGGER before_delete1
-> BEFORE DELETE ON employee FOR EACH ROW
-> BEGIN
-> INSERT INTO delemp1(name,occupation) VALUES(OLD.name,OLD.occupation);
-> END$$
mysql> delete from employee where id=8;
-> $$
mysql> select * from employee;
-> $$
------+------+------------+------------------------+
| id | name | occupation | working_hours |
+------+------+------------+----------------------+
| 1 | AAA | Actor | 12 |
| 2 | BBB | Teacher | 14 |
| 4 | DDD | Nurse | 0 |
+------+------+------------+----------------------+
mysql> select * from delemp1;
-> $$
------+------+------------+----------------------------------------+
| id | name | occupation | deleted_time |
+------+------+------------+--------------------------------------+
| 8 | MMM | Doctor | 2022-05-11 21:49:51 |
+------+------+------------+---------------------------------------+
AFTER DELETE TRIGGER
mysql> create table empsalary(empid int, amount int);
-> $$
mysql> insert into empsalary values(101,5000),(102,4000),(103,3000),(104,8000);
-> $$
mysql> select * from empsalary;
-> $$
-------------+-----------+
| empid | amount |
+---------- +------------+
| 101 | 5000 |
| 102 | 4000 |
| 103 | 3000 |
| 104 | 8000 |
+----------+-------------+
mysql> create table totalsalary(total_budget int);
-> $$
mysql> insert into totalsalary(total_budget)
-> SELECT SUM(amount) from empsalary;
-> $$
mysql> select * from totalsalary;
-> $$
------+------+------------+
| total_budget |
+------+------+---------- +
| 20000 |
+------+------+-----------+
mysql> DELIMITER $$
mysql> CREATE TRIGGER after_delete
-> AFTER DELETE ON empsalary FOR EACH ROW
-> BEGIN
-> UPDATE totalsalary SET total_budget=total_budget-OLD.amount;
-> END $$
mysql> delete from empsalary where empid=103;
-> $$
mysql> select * from empsalary;
-> $$
-------------+-----------+
| empid | amount |
+---------- +------------+
| 101 | 5000 |
| 102 | 4000 |
| 104 | 8000 |
+----------+-------------+
mysql> select * from totalsalary;
-> $$
------+------+------------+
| total_budget |
+------+------+---------- +
| 17000 |
+------+------+-----------+
mysql> drop trigger after_delete;
-> $$

RESULT
Thus trigger concept was executed successfully.
Ex. No: 8 EXCEPTION HANDLING
Date:
AIM
To implement the exception handling in database programming.
EXCEPTIONS
An Exception is an error situation, which arises during program execution. When an error occurs
exception is raised, normal execution is stopped and control transfers to exception handling part.
Exception handlers are routines written to handle the exception. The exceptions can be internally defined
(system-defined or pre-defined) or User-defined exception. There are two types of exceptions defined.
They are
1. User defined exception.
2. System defined exceptions.
SYNTAX TO WRITE AN EXCEPTION
WHEN exception THEN statement;
QUERY
Example 1: Write MySQL handler, in a stored procedure, that throws an error message and exit
the execution
mysql> create table stu_details(stuid int PRIMARY KEY, stuname varchar(20),address varchar(20));
mysql> insert into stu_details values(101,'AAA','Madurai'),(103,'BBB','Chennai');
mysql> select * from stu_details;
------+------+------------+--------------+
| stuid | stuname | address |
+------+------+------------+------------+
| 101 | AAA | Madurai |
| 103 | BBB | Chennai |
+------+------+------------+------------+
mysql> DELIMITER $$
mysql> Create Procedure Insert_Studentdetails3(Studentid INT, StudentName Varchar(20), Address
Varchar(20))
-> BEGIN
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'Got an error';
-> INSERT INTO stu_details(stuid,stuname,address) VALUES (Studentid,StudentName,Address);
-> SELECT * FROM stu_details;
-> END$$
mysql> CALL Insert_Studentdetails3(105, 'CCC', 'Chandigarh');
-> $$
------+------+------------+----------------+
| stuid | stuname | address |
+------+------+------------+--------------+
| 101 | AAA | Madurai |
| 103 | BBB | Chennai |
| 105 | CCC | Chandigarh |
+------+------+------------+--------------+
mysql> CALL Insert_Studentdetails3(103, 'DDD', 'Mumbai');
-> $$
------+------+------+
| Got an Error |
+------+------+----+
| Got an Error |
+------+------+----+
Example 2: Write MySQL handler, in a stored procedure, that throws an error message and
continues the execution
mysql> DELIMITER $$
mysql> CREATE PROCEDURE Insertstudent(id INT, name VARCHAR(20), address varchar(20))
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Duplicate Key Found' Message;
-> INSERT INTO stu_details(stuid,stuname,address) VALUES (id,name,address);
-> SELECT COUNT(*) FROM stu_details;
-> END$$
mysql> CALL Insertstudent(103,'DDD','Mumbai');
-> $$
------+------+---------------+
| Message |
+------+------+-------------+
| Duplicate Key Found |
+------+------+-------------+
mysql> CALL Insertstudent(102,'DDD','Mumbai');
-> $$
------+------+---------------+
| Count |
+------+------+-------------+
| 3 |
+------+------+-------------+
RESULT
Thus the exceptional handling for database programming has been implemented and the output was
verified.
Ex. No: 9 DATABASE DESIGN USING ER MODELING, NORMALIZATION AND
Date: IMPLEMENTATION FOR ANY APPLICATION
AIM
To create Database Design Using ER Modeling, Normalization and Implementation for Any Application
Step 1: Create table using mysqlclient
mysql> use Purchase
Database changed
mysql> create table Customer1(Cid int primary key,Cname varchar(20),Caddress varchar(20),Cphone
int);
mysql> create table Order1(Orderid int primary key, Orderdate DATE, Quantity int, Amount int, Cid
int,foreign key(Cid) references Customer1(Cid));
mysql> create table Payments1(Pid int primary key,PaymentDate DATE, Amount int, Cid int,Orderid
int, foreign key(Cid) references Customer1(Cid),foreign key(Orderid) references Order1(Orderid));
Step 2: Open Mysql Workbench
 Step 3: Open Reverse Engineer from Database

Step 4: Click Next

Step 5: Enter password and press ok then click next

Step 6: Select database purchase from the list and press next
Step 7: Press Execute and then Next

RESULT
Thus the Database Design Using ER Modeling, Normalization and Implementation for Any Application
was executed successfully
Ex. No: 10 DATABASE CONNECTIVITY WITH FRONT END TOOLS
Date:
AIM
To create Database Connectivity with Front End Tools for Any Application
PROCEDURE
Step 1: Open Netbeans IDLE 8.2

Step 2: Select New Project from File menu. Then select java application from java and click next.
Give project name (ex: Demo1) and select create main class option then click finish button
Step 3: Right click on project name (Demo1) then click new and then Jframe Form. Give name for
frame and click finish.
Step 4: Design frame using Palette

Step 5: Download jar file using the following link and add this jar file in our java
program. https://jar-download.com/artifacts/mysql/mysql-connector-java/5.1.49/
source-code
Right click on Libraries of java project (Demo1) and select Add JAR/Folder option and add the
downloaded jar file
Step 6: Open Mysql workbench to create database and table. Click Local Instance Mysql80
Step 7: Click Database symbol to Create new database. Give database name and click Apply

Step 8: Extend that database and click table then select create table
Give table name and create 4 columns with data type and then press Apply
Step 9: Right click on Button to select action listener

Step 10: Give the code in required places

package demo1;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt)
{
try
{
String id=jTextField1.getText();
String name=jTextField2.getText();
String age=jTextField3.getText();
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection ("jdbc:mysql://localhost:3306/mahadb","root","root");
PreparedStatement ps=con.prepareStatement("insert into student (id,name,age,gender)values(?,?,?,?)");
ps.setString(1, id);
ps.setString(2, name);
ps.setString(3, age);
if(jRadioButton1.isSelected())
{
ps.setString(4, jRadioButton1.getText());
}
else
ps.setString(4, jRadioButton2.getText());
ps.executeUpdate();
JOptionPane.showMessageDialog(this,"Inserted Successfully");
}
catch (Exception ex)
{
Logger.getLogger(NewJFrame.class.getName()).log(Level.SEVERE, null, ex);
}
}
Step 11: Run the file to get output
Output
RESULT
Thus the Database Connectivity with Front End Tools was executed successfully
Ex. No: 11 CASE STUDY – RAILWAY TICKET RESERVATION SYSTEM
Date:
AIM
To create Railway Ticket Reservation for Real Life Database Applications
PROCEDURE
Step 1: Open Netbeans IDLE 8.2
Step 2: Select New Project from File menu. Then select java application from java and click next. Give
project name (ex: Demo1) and select create main class option then click finish button.
Step 3: Right click on project name (Demo1) then click new and then Jframe Form. Give name for
frame and click finish. Select multiple Jframe form as per requirement.

Step 4: Design all forms using Palette.

Step 5: Download jar file using the following link and add this jar file in our java program.
Step 6: Open Mysql workbench to create database and table. Create three table such as registration
table (empty table), seats (insert data), book (empty table)
Step 7: Give the code in required places
HOME PAGE (NewJFrame1.java)
DESIGN
SOURCE
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt)
{
NewJFrame jf=new NewJFrame();
jf.show();
dispose();
}
private void jButton2ActionPerformed(java.awt.event.ActionEvent evt)
{
Loginvalidation jf=new Loginvalidation();
jf.show();
dispose(); // TODO add your handling code here:
}
REGISTRATION PAGE (NewFrame.java)
DESIGN

SOURCE
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt)
{
try
{
String id=jTextField1.getText( );
String name=jTextField2.getText( );
String age=jTextField3.getText( );
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mahadb","root","root");
PreparedStatement ps=con.prepareStatement("insert into student (id,name,age,gender)values(?,?,?,?)");
ps.setString(1, id);
ps.setString(2, name);
ps.setString(3, age);
if(jRadioButton1.isSelected( ))
{
ps.setString(4, jRadioButton1.getText( ));
}
else
ps.setString(4, jRadioButton2.getText( ));
ps.executeUpdate( );
JOptionPane.showMessageDialog(this,"Inserted Successfully");
}
catch (Exception ex)
{
Logger.getLogger(NewJFrame.class.getName( )).log(Level.SEVERE, null, ex);
}
}
private void jButton2ActionPerformed(java.awt.event.ActionEvent evt)
{
NewJFrame1 jf=new NewJFrame1( );
 jf.show( );
dispose( );
}
LOGIN VALIDATION (Loginvalidation.java)
DESIGN

SOURCE
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.swing.JoptionPane;
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt)
{
ResultSet rs=null;
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mahadb","root","root");
PreparedStatement ps=con.prepareStatement("select * from student where id=? and name=?");
ps.setString(1, jTextField1.getText( ));
ps.setString(2, jTextField2.getText( ));
rs=ps.executeQuery( );
if(rs.next( ))
{
Login jf=new Login( );
jf.show( );
dispose( );
}
else
JOptionPane.showMessageDialog(this,"Invalid Login");
}
catch (Exception ex)
{
JOptionPane.showMessageDialog(this,ex);
}
}
LOGIN PAGE (Login.java)
DESIGN
SOURCE
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt)
{
Seats jf=new Seats( );
jf.show( );
dispose( );
}
private void jButton2ActionPerformed(java.awt.event.ActionEvent evt)
{
Booking jf=new Booking( );
jf.show( );
dispose( );
}
SEATS AVAILABILITY (seats.java)

SOURCE
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.swing.JoptionPane;
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt)
{
ResultSet rs=null;
try
{
String tnumber=jTextField1.getText( );
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mahadb","root","root");
PreparedStatement ps=con.prepareStatement("select * from seats where trainnumber=?");
ps.setString(1, tnumber);
rs=ps.executeQuery( );
while(rs.next( ))
{
jTextField2.setText(rs.getString("trainname"));
jTextField3.setText(rs.getString("source"));
jTextField4.setText(rs.getString("destination"));
jTextField5.setText(rs.getString("totalseats"));
}
}
catch (Exception ex)
{
JOptionPane.showMessageDialog(this,"No such tain number");
}
}
private void jButton2ActionPerformed(java.awt.event.ActionEvent evt)
{
Login jf=new Login( );
jf.show( );
dispose( );
}
BOOKING (Booking.java)
DESIGN

SOURCE
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.swing.JoptionPane;
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt)
{
try
{
ResultSet rs=null;
String name=jTextField1.getText( );
String phnumber=jTextField2.getText( );
String trainnumber=jTextField3.getText( );
String seats=jTextField4.getText( );
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mahadb","root","root");
PreparedStatement ps=con.prepareStatement("insert into book (name, pnumber, trainnumber,
totalseats) values(?,?,?,?)");
ps.setString(1, name);
ps.setString(2, phnumber);
ps.setString(3, trainnumber);
ps.setString(4, seats);
ps.executeUpdate();
JOptionPane.showMessageDialog(this,"Reserved Successfully");

String trainnumber1=jTextField3.getText( );
Class.forName("com.mysql.jdbc.Driver");
Connection con1=DriverManager.getConnection("jdbc:mysql://localhost:3306/mahadb","root","root");
PreparedStatement ps12=con1.prepareStatement("select * from seats where trainnumber=?");
ps12.setString(1, trainnumber1);
rs=ps12.executeQuery();
String sea = null;
while(rs.next( ))
{
sea=rs.getString("totalseats");
System.out.println(sea);
}
int total;
int sea1=Integer.parseInt(sea);
int seats1=Integer.parseInt(seats);
total = sea1 - seats1;
System.out.println(total);

String trainnumber2=jTextField3.getText( );
Class.forName("com.mysql.jdbc.Driver");
Connection con2=DriverManager.getConnection("jdbc:mysql://localhost:3306/mahadb","root","root");
PreparedStatement ps2=con2.prepareStatement("update seats SET totalseats='"+total+"' where
trainnumber=?");
ps2.setString(1, trainnumber2);
ps2.executeUpdate();
}
catch (Exception ex)
{
Logger.getLogger(NewJFrame.class.getName()).log(Level.SEVERE, null, ex);
}
}
OUTPUT
SEATS AVAILABILITY BEFORE RESERVATION
 SEATS AVAILABILITY AFTER RESERVATION

RESULT
Thus the Railway Ticket Reservation for Real Life Database Applications was created and executed
successfully