Department of Computer Science and Engineering

Vision of the Department

To Excel in the emerging areas of Computer Science and Engineering by imparting knowledge,
relevant practices and inculcating human values to transform the students as potential resources to
contribute innovatively through advanced computing in real time situations.

Mission of the Department

DM1. To provide strong fundamentals and technical skills for Computer Science applications
through effective teaching learning methodologies.
DM2. To transform lives of the students by nurturing ethical values, creativity and novelty to
become Entrepreneurs and establish start-ups.
DM3. To habituate the students to focus on sustainable solutions to improve the quality of life
and the welfare of the society.
DM4. To enhance the fabric of research in computing through collaborative linkages with
industry and academia.
DM5. To inculcate learning of the emerging technologies to pursue higher studies leading to
lifelong learning.
COURSE OBJECTIVES AND OUTCOMES

CS8481 DATABASE MANAGEMENT SYSTEMS LABORATORY

AIM:

The aim of this laboratory is to inculcate the abilities of applying the principles of the database
management systems. This course aims to prepare the students for projects where a proper
implementation of databases will be required.

OBJECTIVES:

To understand data definitions and data manipulation commands

To learn the use of nested and join queries
To understand functions, procedures and procedural extensions of data bases
To be familiar with the use of a front end tool
To understand design and implementation of typical database applications

1. Data Definition Commands, Data Manipulation Commands for inserting, deleting,

updatingand retrieving Tables and Transaction Control statements
2. Database Querying – Simple queries, Nested queries, Sub queries and Joins
3. Views, Sequences, Synonyms
4. Database Programming: Implicit and Explicit Cursors
5. Procedures and Functions
6. Triggers
7. Exception Handling
8. Database Design using ER modeling, normalization and Implementation for any application
9. Database Connectivity with Front End Tools
10. Case Study using real life database applications

OUTCOMES:

Upon completion of the course, the students will be able to:

Use typical data definitions and manipulation commands.

Design applications to test Nested and Join Queries
Implement simple applications that use Views
Implement applications that require a Front-end Tool
INDEX

CO’s
EX NO DATE PO’s & PSO’s SIGN
TITLE MAPPED MARKS
MAPPED

1 DDL, DML & TCL COMMANDS

SIMPLE QUERIES, NESTED

QUERIES, SUB QUERIES AND
2
JOINS

VIEWS, SEQUENCES,
3 SYNONYMS

IMPLICIT AND EXPLICIT

4 CURSORS

PROCEDURES AND
5 FUNCTIONS

TRIGGERS
6

EXCEPTION HANDLING
7
DATABASE DESIGN USING ER
8 MODELING, NORMALIZATION

DATABASE CONNECTIVITY
9 WITH FRONT END TOOLS

CASE STUDY USING REAL LIFE

10 DATABASE APPLICATIONS

CONTENT BEYOND SYLLABUS

CO’s
EX NO DATE PO’s & PSO’s SIGN
TITLE MAPPED MARKS
MAPPED
POPULATING DATABASE IN
11 QUERY

REPORTS USING SQL

12
CALCULATE AREA OF CIRCLE
13 USING PL/SQL PROGRAM
EX NO: 1
DATE: DDL, DML & TCL COMMANDS

AIM:
To design and implement a database in MySQL using Structured Query Language
commands

S YNTAX:

CREATE :(command used for creating tables)

Create table <table name> (column name1 data type (size) constraints, column2 data type (size),..,
column name N data type(size));
DESC :(command used to view the table structure)
Desc<table name>;
DDL COMMANDS:
Create
Alter
Add
Modify
Drop
Rename
Drop
Truncate

ALTER:(command used for modifying the table structure)

ADD:( To add a column in a table)
Alter table <table name> add(column name1 datatype1);
MODIFY:( To change the data type of a column in a table)
Alter table <table name> modify(column name1 datatype1);
DROP:( To delete a column in a table)
Alter table <table name> drop (column name);
RENAME: (command used to change the name of the table)
Rename table tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...
DROP: (command used removing for an existing table)
Drop [TEMPORARY] Table[IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT |CASCADE]
TRUNCATE:( command is used to remove all the records in the table and not the table itself)
Truncate table <table name>;
DML Commands:

Insert – to insert one or more number of rows.

Select – to display one or more rows.

Update – used to alter the column values in a table.

Delete – used to delete one or more rows.

INSERT:

Syntax 1: Insert into <table name> values (‘attributes1’, ’attributes2’……);

Syntax 2: Insert into<table name>(column names)values(list of data values);

Syntax 3: Insert into <table name>values(&columname1,&columnname2…);

SELECT:

Syntax 1:Select column name1,columnname2 from <table name>;

Syntax 2: select * from <tablename>;

Syntax 3: select * from <tablename> where <condition>;

UPDATE:

Update <table name> set <column name>=’values’ where <condition>;

DELETE:

Delete from <table name>;

TCL Commands
COMMIT
Syntax:
Commit:
Syntax:
ROLLBACK TO [SAVEPOINT] savepointname; Where,
SAVEPOINT:is optional and is used to rollback a partial transaction, as far the specified
savepoint.
Savepointname: is a savepoint created in current transaction.
SAVEPOINT
Syntax:
SAVEPOINT savepointname;
PROBLEM STATEMENT:
Customer browse the catalogue of books
Customers place the orders
Customers call the book store and give the ISBN of a book and quantity.
Store prepares a shipment that contains the books customers have ordered
TABLE FROM THE PROBLEM STATEMENT
1) Books
2) Customers
3) Orders
4) Order_list
[root@localhost ~]# service mysqld start
[root@localhost ~]# mysql
mysql> create database sheryl;
Query OK, 1 row affected (0.00 sec)
mysql> use sheryl;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sheryl |
| test |
+--------------------+
4 rows in set (0.00 sec)
Table name: Books
mysql> Create table books(ISBN varchar(10) primary key, title varchar(30),
author char(10),Quan_instock int,price int ,pub_year date);
Query OK, 0 rows affected (0.01 sec)

Description of table
mysql> desc books;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ISBN | varchar(10) | NO | PRI | NULL | |
| title | varchar(30) | YES | | NULL | |
| author | char(10) | YES | | NULL | |
| Quan_instock | int(11) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| pub_year | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
Table name: Customers
mysql> create table customer(cust_id varchar(10) primary key, cust_name
varchar(20), cust_addr varchar(15), card_no varchar(15));
Query OK, 0 rows affected (0.01 sec)

Description of table
mysql> desc customer;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| cust_id | varchar(10) | NO | PRI | NULL | |
| cust_name | varchar(20) | YES | | NULL | |
| cust_addr | varchar(15) | YES | | NULL | |
| card_no | varchar(15) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Table name: Orders

mysql> create table orders(order_no varchar(10) primary key, cust_id
varchar(10),order_date date);
Query OK, 0 rows affected (0.00 sec)

Description of table
mysql> desc orders;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| order_no | varchar(10) | NO | PRI | NULL | |
| cust_id | varchar(10) | YES | | NULL | |
| order_date | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Table name: Order_list

mysql> create table order_list(order_no varchar(20) primary key, ISBN
varchar(10), quantity int,ship_date date);
Query OK, 0 rows affected (0.00 sec)

Description of table
mysql> desc order_list;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| order_no | varchar(20) | NO | PRI | NULL | |
| ISBN | varchar(10) | YES | | NULL | |
| quantity | int(11) | YES | | NULL | |
| ship_date | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1.Write a query for creating new table from existing table with all fields.
mysql> create table cust as select * from customer;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

Description of table
mysql> desc cust;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| cust_id | varchar(10) | NO | | NULL | |
| cust_name | varchar(20) | YES | | NULL | |
| cust_addr | varchar(15) | YES | | NULL | |
| card_no | varchar(15) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2.Write a query for creating new table from existing table with selected fields.
mysql> create table cust1 as select cust_id,cust_name from cust;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Description of table
mysql> desc cust1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| cust_id | varchar(10) | NO | | NULL | |
| cust_name | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3.Write a query to create new table from existing table without any record.
mysql> create table cust2 as select * from cust where 1>2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

Description of table
mysql> desc cust2;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| cust_id | varchar(10) | NO | | NULL | |
| cust_name | varchar(20) | YES | | NULL | |
| cust_addr | varchar(15) | YES | | NULL | |
| card_no | varchar(15) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
4. Alter the table books by increasing the field width of ISBN to 15.
mysql> desc books;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ISBN | varchar(10) | NO | PRI | NULL | |
| title | varchar(30) | YES | | NULL | |
| author | char(10) | YES | | NULL | |
| Quan_instock | int(11) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| pub_year | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> Alter table books modify ISBN varchar(15);

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc books;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ISBN | varchar(15) | NO | PRI | NULL | |
| title | varchar(30) | YES | | NULL | |
| author | char(10) | YES | | NULL | |
| quan_instock | int(11) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| pub_year | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

5. Drop the primary key from orders table.

mysql> desc orders;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| order_no | varchar(10) | NO | PRI | NULL | |
| cust_id | varchar(10) | YES | | NULL | |
| order_date | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table orders drop primary key;

Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc orders;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| order_no | varchar(10) | NO | | NULL | |
| cust_id | varchar(10) | YES | | NULL | |
| order_date | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
6. Add the primary key to orders table.

mysql> alter table orders add primary key (order_no);

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc orders;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| order_no | varchar(10) | NO | PRI | NULL | |
| cust_id | varchar(10) | YES | | NULL | |
| order_date | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

7. Add new column to book table.

mysql> desc books;

+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ISBN | varchar(15) | NO | PRI | NULL | |
| title | varchar(30) | YES | | NULL | |
| author | char(10) | YES | | NULL | |
| quan_instock | int(11) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| pub_year | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table books add edition varchar(15);

Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc books;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ISBN | varchar(15) | NO | PRI | NULL | |
| title | varchar(30) | YES | | NULL | |
| author | char(10) | YES | | NULL | |
| quan_instock | int(11) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| pub_year | date | YES | | NULL | |
| edition | varchar(15) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

8. Drop the column from book table.

mysql> alter table books drop edition;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc books;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ISBN | varchar(15) | NO | PRI | NULL | |
| title | varchar(30) | YES | | NULL | |
| author | char(10) | YES | | NULL | |
| quan_instock | int(11) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| pub_year | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

9. Rename the customers table as customers_1 table.

mysql> alter table cust rename to customers_1;
Query OK, 0 rows affected (0.00 sec)
mysql> desc cust;
ERROR 1146 (42S02): Table 'sheryl.cust' doesn't exist
mysql> desc customers_1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| cust_id | varchar(10) | NO | | NULL | |
| cust_name | varchar(20) | YES | | NULL | |
| cust_addr | varchar(15) | YES | | NULL | |
| card_no | varchar(15) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec

============================================================================
10. Drop customer_1 table.

mysql> drop table customer_1;

Query OK, 0 rows affected (0.00 sec)

11. Inserting records in all the four created tables:

mysql> insert into books values('1-101','Bold','Raja',150,200,'1990-12-10');
mysql> select * from books;
+-------+-------------+----------+--------------+-------+------------+
| ISBN | title | author | quan_instock | price | pub_year |
+-------+-------------+----------+--------------+-------+------------+
| 1-101 | Bold |Raja | 200 | 120 | 0000-00-00 |
+-------+-------------+----------+--------------+-------+------------+
1 rows in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> insert into customer values('10','Geetha','Medavakkam','C10');
Query OK, 1 row affected (0.00 sec)
mysql> insert into customer values('11','Raja','Siruseri','C11');
Query OK, 1 row affected (0.00 sec)
mysql> select * from customer;
+---------+-----------+------------+---------+
| cust_id | cust_name | cust_addr | card_no |
+---------+-----------+------------+---------+
| 10 | Geetha | Medavakkam | C10 |
| 11 | Raja | Siruseri | C11 |
+---------+-----------+------------+---------+
2 rows in set (0.00 sec)

mysql> insert into orders values('N21','10','2017-01-12');

Query OK, 1 row affected (0.00 sec)
mysql> insert into orders values('N22','11','2017-02-16');
Query OK, 1 row affected (0.00 sec)
mysql> select * from orders;
+----------+---------+------------+
| order_no | cust_id | order_date |
+----------+---------+------------+
| N21 | 10 | 2017-01-12 |
| N22 | 11 | 2017-02-16 |
+----------+---------+------------+
2 rows in set (0.00 sec)

mysql> insert into order_list values('N21','1-101',100,'2017-01-12');

Query OK, 1 row affected (0.00 sec)
mysql> insert into order_list values('N22','1-102',105,'2017-02-16');
Query OK, 1 row affected (0.00 sec)
mysql> select * from order_list;
+----------+-------+----------+------------+
| order_no | ISBN | quantity | ship_date |
+----------+-------+----------+------------+
| N21 | 1-101 | 100 | 2017-01-12 |
| N22 | 1-102 | 105 | 2017-02-16 |
+----------+-------+----------+------------+
2 rows in set (0.00 sec)

12. Find the names of all authors in books relation.

mysql> select author from books;
+----------+
| author |
+----------+
| John |
| John |
| David |
| James |
| Benjamin |
+----------+
5 rows in set (0.00 sec)

13. Update the cust_addr as guindy for customer name raja in the Customer table.
mysql> select * from customer;
+---------+-----------+------------+---------+
| cust_id | cust_name | cust_addr | card_no |
+---------+-----------+------------+---------+
| 10 | Geetha | Medavakkam | C10 |
| 11 | Raja | Siruseri | C11 |
+---------+-----------+------------+---------+
2 rows in set (0.00 sec)
mysql> update customer set cust_addr='Guindy' where cust_name='Raja';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from customer;

+---------+-----------+------------+---------+
| cust_id | cust_name | cust_addr | card_no |
+---------+-----------+------------+---------+
| 10 | Geetha | Medavakkam | C10 |
| 11 | Raja |Guindy | C11 |
+---------+-----------+------------+---------+
3 rows in set (0.00 sec)

14. Show the effect of rollback command with an example.

mysql>create table product(id int primary key,category char(12),price
decimal(4,1);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>insert into product values(101,’Mixer’,1051);

mysql>insert into product values(102,’Toaster’,2000);
mysql> select * from product;
+-----+----------+-------+
| id | category | price |
+-----+----------+-------+
| 101 | MIXER | 1051 |
| 102 | Toaster | 2000 |
+-----+----------+-------+
2 rows in set (0.00 sec)

mysql> alter table product type=INNODB;

Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> start transaction;

mysql> insert into product values(103,'Slicer',1500);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from product;

+-----+----------+-------+
| id | category | price |
+-----+----------+-------+
| 101 | MIXER | 1051 |
| 102 | Toaster | 2000 |
+-----+----------+-------+
2 rows in set (0.00 sec)

SAVE POINT

mysql>alter table research engine = INNODB;

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into research values(10,'Barath','Network');
Query OK, 1 row affected (0.00 sec)

mysql> savepoint st1;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into research values(11,'Abisha','DBMS');

Query OK, 1 row affected (0.00 sec)

mysql> savepoint st2;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into research values(12,'Durga','Datamining');

Query OK, 1 row affected (0.00 sec)

mysql> select * from research;

+-------+---------+------------+
| regno | name | res_title |
+-------+---------+------------+
| 10 | Barath | Network |
| 11 | Abisha | DBMS |
| 12 | Durga | Datamining |
+-------+---------+------------+
3 rows in set (0.00 sec)

mysql> rollback to st1;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from research;

+-------+---------+------------+
| regno | name | res_title |
+-------+---------+------------+
| 10 | Bharath | Network |
+-------+---------+------------+
3 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from research;

Empty set (0.00 sec)

INFERENCE:

RESULT:
EX NO: 2
DATE: SIMPLE QUERIES, NESTED QUERIES, SUB QUERIES AND JOINS

AIM: To execute database querying with Simple queries, Nested queries, Sub queries and Joins
Syntax:
SELECT:
Syntax 1:Select column name1,columnname2 from <table name>;
Syntax 2: select * from <tablename>;
Syntax 3: select * from <tablename> where <condition>;
Select_clause from_clause [where_clause][groupby_clause][having_clause][orderby_clause]
The SELECT clause defines the types of the objects or values returned by the
query.The FROM clause defines the scope of the query by declaring one or more identification
variables, which can be referenced in the SELECTand WHERE clauses. An identification variable
represents one of the following elements:
The abstract schema name of an entity
An element of a collection relationship
An element of a single-valued relationship
A member of a collection that is the multiple side of a one-to-many relationship
The WHERE clause is a conditional expression that restricts the objects or values retrieved
by the query. Although the clause is optional, most queries have a WHERE clause.
The GROUP BY clause groups query results according to a set of properties.
The HAVING clause is used with the GROUP BY clause to further restrict the query results
according to a conditional expression.
The ORDER BY clause sorts the objects or values returned by the query into a specified
order.
Subqueries with the SELECT Statement
SELECT column_name [, column_name ]FROM table1 [, table2 ]
WHERE column_name OPERATOR(SELECT column_name [, column_name ]
FROM table1 [, table2 ][WHERE])
Subqueries with the INSERT Statement
INSERT INTO table_name [ (column1 [, column2 ]) ]SELECT [ *|column1 [, column2 ]FROM table1 [, tabl
e2 ][ WHERE ]
Subqueries with the UPDATE Statement
UPDATE tableSET column_name = new_value[ WHERE OPERATOR [ VALUE ](SELECT COLUMN_NA
MEFROM TABLE_NAME)[ WHERE) ]
Subqueries with the DELETE Statement
DELETE FROM TABLE_NAME[ WHERE OPERATOR [ VALUE ](SELECT COLUMN_NAME
FROM TABLE_NAME)[ WHERE) ]
JOIN COMMANDS
INNER JOIN command returns the matching rows from the tables that are being joined.
LEFT OUTER JOIN command returns matching rows from the tables being joined and also non-
matching row from the left table in the result and places null values in the attributes that come
from the right side table.
RIGHT OUTER JOIN command returns matching rows from the tables being joined and also non-
matching row from the right table in the result and places null values in the attributes that come
from the left side table.
1. Find the names of all names in book relation eliminate duplicate.
mysql> select distinct author from books;
+----------+
| author |
+----------+
| David |
| James |
+----------+
3 rows in set (0.00 sec)
=============================================================================
2.display in the books table with attribute price multiplied by 10.
mysql> select price*10 from books;
+----------+
| price*10 |
+----------+
| 1200 |
| 1200 |
| 2000 |
+----------+
3 rows in set (0.00 sec)
============================================================================
3. Find all title for books whose author is james and price greater
Than 100.
mysql> select title from books where author='James'and price>100;
+-------------+
| title |
+-------------+
| Fairy Tales |
+-------------+
1 row in set (0.00 sec)
============================================================================
4. Find all isbn for books with book price between 150 and 200.
mysql> select isbn from books where price between 150 and 200;
+-------+
| isbn |
+-------+
| 1-103 |
| 2-203 |
+-------+
3 rows in set (0.00 sec)
=============================================================================
5. Find the names of customer whose name includes the character ‘e’ in the third position.
mysql> select cust_name from customer where cust_name like '__%';
+-----------+
| cust_name |
+-----------+
| Geetha |
+-----------+
1 row in set (0.00 sec)
=============================================================================
6. Find the names of customer whose address starts with substring ‘e’.

mysql> select cust_name from customer where cust_addr like 'E%';

+-----------+
| cust_name |
+-----------+
| Edena |
+-----------+
1 row in set (0.00 sec)
=============================================================================
7.display the entire book tabe in descending order of pub_year.

mysql> select * from books order by pub_year desc;

+-------+-------------+----------+--------------+-------+------------+
| ISBN | title | author | quan_instock | price | pub_year |
+-------+-------------+----------+--------------+-------+------------+
| 1-103 | Beautiful | David | 150 | 200 | 1990-12-10 |
| 2-203 | Fairy Tales | James | 300 | 180 | 1996-04-10 |
+-------+-------------+----------+--------------+-------+------------+
5 rows in set (0.00 sec)
=============================================================================
8. Find total number of customer.

mysql> select count(cust_id) from customer;

+----------------+
| count(cust_id) |
+----------------+
| 2 |
+----------------+
1 row in set (0.00 sec)
=============================================================================
9. Find all the customer number that appears in the customer relationWith null values for card_no.

mysql> insert into customer values('50','Mclen','Tambaram',null);

Query OK, 1 row affected (0.00 sec)
mysql> select cust_name from customer where card_no is null;
+-----------+
| cust_name |
+-----------+
| Mclen |
+-----------+
1 row in set (0.00 sec)
=============================================================================
S UB QUERIES:

SALESMAN TABLE
salesman_id name city commission
----------- ---------- ---------- ----------
5001 James Hoog New York 0.15
5002 Nail Knite Paris 0.13
5005 Pit Alex London 0.11
5006 Mc Lyon Paris 0.14
5003 Lauson Hen San Jose 0.12
5007 Paul Adam Rome 0.13

ORDERS TABLE

ord_no purch_amt ord_date customer_id salesman_id

---------- ---------- ---------- ----------- -----------
70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.4 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.6 2012-04-25 3002 5001

CUSTOMER TABLE

customer_id cust_name city grade salesman_id

----------- ------------ ---------- ---------- -----------
3002 Nick Rimando New York 100 5001
3005 Graham Zusi California 200 5002
3001 Brad Guzan London 5005
3004 Fabian Johns Paris 300 5006
3007 Brad Davis New York 200 5001
3009 Geoff Camero Berlin 100 5003
3008 Julian Green London 300 5002
3003 Jozy Altidor Moscow 200 5007

1.Write a query to display all the orders from the orders table issued by the salesman 'paul adam'.
mysql>SELECT * FROM orders WHERE salesman_id = (select salesman_id FROM salesman
WHEREname='Paul Adam');

2.Write a query to display all the orders which values are greater than the average order value for
10th October 2012.
mysql>SELECT * FROM orders WHERE purch_amt > (select AVG(purch_amt) FROM orders
whereord_date ='10/10/2012');
3. Write a query to find the sums of the amounts from the orders table, grouped by date, eliminating
all those dates where the sum was not at least 1000.00 above the maximum amount for that date.
mysql>SELECT ord_date, SUM (purch_amt) FROM orders a GROUP BY ord_date HAVING SUM
(purch_amt) > (SELECT 1000.00 + MAX(purch_amt) FROM orders b WHERE a.ord_date =
b.ord_date);

4. Write a query to extract the data from the customer table if and only if one or more of the
customers in the customer table are located in London.
mysql>SELECT customer_id,cust_name, city FROM customer WHERE EXISTS (SELECT * FROM
customer WHERE city='London');

5.Write a query to find all the salesmen who worked for only one customer.
mysql>ELECT * FROM salesman WHERE salesman_id IN (SELECT DISTINCT salesman_id FROM
customer a WHERE NOT EXISTS (SELECT * FROM customer b WHERE a.salesman_id =
b.salesmanidAND a.cust_name<>b.cust_name));

6.Write a query to find all those customers who hold a different grade than any customer of the city
Dallas.
mysql>SELECT *FROM customer WHERE NOT grade = ANY (SELECT grade FROM customerWHERE
city='Dallas');

Table: Emp

mysql> create table emp(empno int primary key,ename char(10),job

char(5),deptno int,sal int);
Query OK, 0 rows affected (0.00 sec)

mysql> desc emp;

+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| empno | int(11) | NO | PRI | NULL | |
| ename | char(10) | YES | | NULL | |
| job | char(5) | YES | | NULL | |
| deptno | int(11) | YES | | NULL | |
| sal | int(11) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Table:Depart
mysql> create table depart(deptno int,dname char(12),loc char(12));
Query OK, 0 rows affected (0.01 sec)

mysql> alter table depart add foreign key(deptno) references emp(deptno);

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc depart;

+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| deptno | int(11) | YES | MUL | NULL | |
| dname | char(12) | YES | | NULL | |
| loc | char(12) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Table:Staff
mysql> create table staff(staff_id int primary key,staff_name char(10),expr
int,age int);
Query OK, 0 rows affected (0.00 sec)

mysql> desc staff;

+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| staff_id | int(11) | NO | PRI | NULL | |
| staff_name | char(10) | YES | | NULL | |
| expr | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Table:Book

mysql> create table book(book_id int,book_name char(12),edition int,primary

key(book_id),check(edition in(1,2,3)));
Query OK, 0 rows affected (0.00 sec)

mysql> desc book;

+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| book_id | int(11) | NO | PRI | 0 | |
| book_name | char(12) | YES | | NULL | |
| edition | int(11) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Table:Issue

mysql> create table issue(staff_id int,book_id int,day date,primary

key(staff_id,book_id));
Query OK, 0 rows affected (0.01 sec)

mysql> alter table issue add foreign key(book_id) references book(book_id);

Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table issue add foreign key(staff_id) references

staff(staff_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc issue;

+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| staff_id | int(11) | NO | PRI | 0 | |
| book_id | int(11) | NO | PRI | 0 | |
| day | date | YES | | NULL | |
+----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Inserting records in all the created tables:

mysql> insert into emp values(1,'Arjun','AP',1,10000);

Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(2,'Barath','ASP',2,20000);

Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(3,'Aparna','ASP',2,20000);

Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(4,'Karthik','AP',1,10000);

Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;

+-------+---------+------+--------+-------+
| empno | ename | job | deptno | sal |
+-------+---------+------+--------+-------+
| 1 | Arjun | AP | 1 | 10000 |
| 2 | Barath | ASP | 2 | 20000 |
| 3 | Aparna | ASP | 2 | 20000 |
| 4 | Karthik | AP | 1 | 10000 |
+-------+---------+------+--------+-------+
4 rows in set (0.00 sec)
----------------------------------------------------------------------------
mysql> create table depart(deptno int,dname char(12),loc char(12));
Query OK, 0 rows affected (0.01 sec)

mysql> alter table depart add foreign key(deptno) references emp(deptno);

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into depart values(1,'Accounting','Newyork');

Query OK, 1 row affected (0.00 sec)

mysql> insert into depart values(2,'Research','Dallas');

Query OK, 1 row affected (0.00 sec)

mysql> select * from depart;

+--------+------------+---------+
| deptno | dname | loc |
+--------+------------+---------+
| 1 | Accounting | Newyork |
| 2 | Research | Dallas |
| 30 | Sales | Chicago |
| 40 | Operation | Boston |
+--------+------------+---------+
4 rows in set (0.00 sec)

mysql> insert into staff values(11,'John',8,27);

Query OK, 1 row affected (0.00 sec)

mysql> insert into staff values(12,'Priya',9,29);

Query OK, 1 row affected (0.00 sec)
mysql> select * from staff;
+----------+------------+------+------+
| staff_id | staff_name | expr | age |
+----------+------------+------+------+
| 11 | John | 8 | 27 |
| 12 | Priya | 9 | 29 |
| 13 | Beulah | 10 | 36 |
+----------+------------+------+------+
3 rows in set (0.00 sec)

mysql> insert into book values(1023,'OOPS',3);

Query OK, 1 row affected (0.00 sec)

mysql> insert into book values(1056,'DBMS',2);

Query OK, 1 row affected (0.00 sec)

mysql> select * from book;

+---------+-----------+---------+
| book_id | book_name | edition |
+---------+-----------+---------+
| 1023 | OOPS | 3 |
| 1056 | DBMS | 2 |
| 1036 | CN | 1 |
+---------+-----------+---------+
3 rows in set (0.00 sec)
----------------------------------------------------------------------------
mysql> insert into issue values(11,1023,'11-aug-14');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into issue values(12,1056,'15-dec-14');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from issue;

+----------+---------+------------+
| staff_id | book_id | day |
+----------+---------+------------+
| 11 | 1023 | 0000-00-00 |
| 12 | 1056 | 0000-00-00 |
+----------+---------+------------+
2 rows in set (0.00 sec)

JOIN COMMANDS

EQUI-JOIN
Display the employee details, departments that the departments are same in both the emp and dept.

mysql> select * from emp,depart where emp.deptno=depart.deptno;

+-------+---------+------+--------+-------+--------+------------+---------+
| empno | ename | job | deptno | sal | deptno | dname | loc |
+-------+---------+------+--------+-------+--------+------------+---------+
| 1 | Arjun | AP | 1 | 10000 | 1 | Accounting | Newyork |
| 4 | Karthik | AP | 1 | 10000 | 1 | Accounting | Newyork |
| 2 | Barath | ASP | 2 | 20000 | 2 | Research | Dallas |
| 3 | Aparna | ASP | 2 | 20000 | 2 | Research | Dallas |
+-------+---------+------+--------+-------+--------+------------+---------+
4 rows in set (0.00 sec)
NON EQUI-JOIN
Display the employee details, departments that the departments are not same in both the emp and
dept.
mysql> select * from emp,depart where emp.deptno!=depart.deptno;
+-------+---------+------+--------+-------+--------+------------+---------+
| empno | ename | job | deptno | sal | deptno | dname | loc |
+-------+---------+------+--------+-------+--------+------------+---------+
| 2 | Barath | ASP | 2 | 20000 | 1 | Accounting | Newyork |
| 1 | Arjun | AP | 1 | 10000 | 2 | Research | Dallas |
| 4 | Karthik | AP | 1 | 10000 | 2 | Research | Dallas |
+-------+---------+------+--------+-------+--------+------------+---------+
12 rows in set (0.00 sec)

LEFT OUTER JOIN

Display Ename and location using LEFT OUTER JOIN
mysql> select emp.ename,loc from emp left outer join depart on
emp.deptno=depart.deptno;
+---------+---------+
| ename | loc |
+---------+---------+
| Arjun | Newyork |
| Barath | Dallas |
| Aparna | Dallas |
| Karthik | Newyork |
+---------+---------+
4 rows in set (0.00 sec)

RIGHT OUTER JOIN

Write a Query to display Ename,Job and Sal using Right outer Join
mysql> select emp.ename,job,sal from emp right outer join depart on
depart.deptno=emp.deptno;
+---------+------+-------+
| ename | job | sal |
+---------+------+-------+
| Arjun | AP | 10000 |
| Barath | ASP | 20000 |
| Aparna | ASP | 20000 |
| NULL | NULL | NULL |
| NULL | NULL | NULL |
+---------+------+-------+
6 rows in set (0.00 sec)

SELF JOIN
Write a Query to display employee names using Self Join
mysql> select distinct ename from emp x,depart y where x.deptno=y.deptno;
+---------+
| ename |
+---------+
| Arjun |
| Barath |
| Aparna |
+---------+
4 rows in set (0.00 sec)
SUB-QUERY (query within another query)
Update deptno by adding empno and keep that as deptno for employee 4.
mysql> update emp set deptno=( select sum(empno)from depart) where empno=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from emp;

+-------+---------+------+--------+-------+
| empno | ename | job | deptno | sal |
+-------+---------+------+--------+-------+
| 1 | Arjun | AP | 1 | 10000 |
| 2 | Barath | ASP | 2 | 20000 |
| 3 | Aparna | ASP | 2 | 20000 |
+-------+---------+------+--------+-------+
4 rows in set (0.00 sec)

Display staff name who has the bookid 1023.

mysql> select staff_name from staff where staff_id in(select staff_id from
issue where book_id=1023);
+------------+
| staff_name |
+------------+
| John |
+------------+
1 row in set (0.00 sec)

Display staff_id whose edition is 2.

mysql> select staff_id from issue where book_id in(select book_id from book
where edition=2);
+----------+
| staff_id |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)

INFERENCE:

RESULT:
EX NO: 3
DATE: VIEWS, SEQUENCES, SYNONYMS

AIM:

To implement and execute view, sequence and synonym in MySQL using Structured Query
Language commands

S YNTAX:

VIEWS( is an imaginary table)

CREATE
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name]
AS [SELECT statement]

SEQUENCE (automatic generation of primary unique key integer value)

CREATE TABLE table_name

(
column1 datatype NOT NULL AUTO_INCREMENT,
column2 datatype [ NULL | NOT NULL ],
...
);
INDEXES

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name[index_type]

ON tbl_name (index_col_name,...)[index_type]

index_col_name:
col_name [(length)] [ASC | DESC]

index_type:
USING {BTREE | HASH}

1)VIEWS:
Create a view using aggregate functions to calculate the No of years publish of the customer

mysql> create view book_pubas select ISBN,Title,Author,round((curdate()-

pub_year)/10000) as no_years_publish from books;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from book_pub;
+-------+-------------+----------+------------------+
| ISBN | Title | Author | no_years_publish |
+-------+-------------+----------+------------------+
| 1-102 | Bold | John | 15 |
| 2-203 | Fairy Tales | James | 19 |
| 3-204 | Ben10 | Benjamin | 5 |
+-------+-------------+----------+------------------+
5 rows in set (0.01 sec)
=======================================================================
2)SEQUENCE
Create a sequence and design the student table with the given attributes.
mysql> create table student(student_id int primary key auto_increment,name
varchar(10),result varchar(10))ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> desc student;

+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| student_id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| result | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
Sequence Creation

mysql> insert into student(name,result) values ('Barath',92);

Query OK, 1 row affected (0.00 sec)

mysql> insert into student(name,result) values ('Rajan',86);

Query OK, 1 row affected (0.00 sec)

mysql> select * from student;

+------------+---------+--------+
| student_id | name | result |
+------------+---------+--------+
| 1 | Barath | 92 |
| 2 | Rajan | 86 |
+------------+---------+--------+
2 rows in set (0.00 sec)

3)INDEXES
To create an index on the Last Name column of the student table

CREATE INDEX:
mysql> create index lastname on student(name);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

DROP INDEX:
mysql> drop index lastname on student;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

INFERENCE:

RESULT:
EX NO: 4
IMPLICIT AND EXPLICIT CURSORS
DATE:
AIM:

To implement cursors in database programming

SYNTAX:

Cursor DECLARE Syntax

DECLARE cursor_name CURSOR FOR select_statement
Cursor FETCH Syntax
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
Cursor OPEN Syntax
OPEN cursor_name
Cursor CLOSE Syntax
CLOSE cursor_name

Explicit Cursors:

mysql> create database sheryl;

Query OK, 1 row affected (0.00 sec)

mysql> use sheryl;

Database changed
mysql> create table student (stu_id int,stu_name char(10),stu_class int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into student values(1,'david',10);

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(2,'shah',20);

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(3,'mike',30);

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(4,'maze',40);

Query OK, 1 row affected (0.00 sec)

mysql> select * from student;

+--------+----------+-----------+
| stu_id | stu_name | stu_class |
+--------+----------+-----------+
| 1 | david | 10 |
| 2 | shah | 20 |
| 3 | mike | 30 |
| 4 | maze | 40 |
+--------+----------+-----------+
4 rows in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure curdemo (id int)
-> begin
-> declare name char(25);
-> declare cur1 cursor for select stu_name from student where stu_id =id;
-> open cur1;
-> fetch cur1 into name;
-> select name;
-> close cur1;
-> end $$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter $$
mysql> call curdemo(2)
-> $$

+------+
| name |
+------+
| shah |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> create procedure close_cursor(id int)
-> begin
-> declare name char(25);
-> declare cur1 cursor for select stu_name from student where stu_id =id;
-> open cur1;
-> fetch cur1 into name;
-> select name;
-> close cur1;
-> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> call close_cursor(4);
-> $$

+------+
| name |
+------+
| maze |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

INFERENCE:

RESULT:
EX NO: 5
DATE: PROCEDURES AND FUNCTIONS

AIM:

To implement procedure and functions.

SYNTAX

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
Valid SQL procedure statement

FUNTION SYNTAX:

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

func_parameter:
param_name type
FACTORIAL OF GIVEN NUMBER

mysql> delimiter $$
mysql> CREATE PROCEDURE factorial(IN x INT)
-> BEGIN
-> DECLARE result INT;
-> DECLARE i INT;
-> SET result = 1;
-> SET i = 1;
-> WHILE i <= x DO
-> SET result = result * i;
-> SET i = i + 1;
-> END WHILE;
-> SELECT x AS Number, result as Factorial;
-> END;
-> $$
Query OK, 0 rows affected (0.00 sec)

mysql> call factorial(5)

-> $$
+--------+-----------+
| Number | Factorial |
+--------+-----------+
| 5 | 120 |
+--------+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Procedure to get input from user

mysql> select * from depart;

+--------+------------+---------+
| deptno | dname | loc |
+--------+------------+---------+
| 1 | Accounting | Newyork |
| 2 | Research | Dallas |
| 30 | Sales | Chicago |
+--------+------------+---------+
4 rows in set (0.00 sec)
mysql> delimiter //
mysql> create procedure getin(IN loca varchar(12))
-> BEGIN
-> Select * from depart where loc=loca;
-> END //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL getin('Boston');
+--------+-----------+--------+
| deptno | dname | loc |
+--------+-----------+--------+
| 40 | Operation | Boston |
+--------+-----------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Write a stored procedure which returns total salary for a particular JOB category.

mysql> select * from emp;

+-------+---------+------+--------+-------+
| empno | ename | job | deptno | sal |
+-------+---------+------+--------+-------+
| 1 | Arjun | AP | 1 | 10000 |
| 2 | Barath | ASP | 2 | 20000 |
| 3 | Aparna | ASP | 2 | 20000 |
| 4 | Karthik | AP | 16 | 10000 |
+-------+---------+------+--------+-------+
4 rows in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure sumof(
-> IN jobs varchar(10),
-> OUT total int)
-> BEGIN
-> select sum(sal)
-> into total
-> from emp
-> where job=jobs;
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL sumof('ASP',@total);
Query OK, 0 rows affected (0.00 sec)

mysql> select @total;

+--------+
| @total |
+--------+
| 40000 |
+--------+
1 row in set (0.00 sec)

INFERENCE:

RESULT:
EX NO: 6
TRIGGERS
DATE:

AIM:

To implement and execute triggers in MySQL Database using Procedural Language

concepts.

TRIGGERS:

1) Trigger is a special type of procedure that the oracle executes when an insert,
modify or delete operation is performed against a given table.
2) It is a stored sub program associated with a table.
3) It is used to keep an audit trial of a table, to prevent invalid transaction, enforce
complex security authorization, to generate data automatically.

SYNTAX:
Syntax:
CREATE[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

Create a trigger to calculate the net salary and average salary of a employee in the employee table.

mysql> create table employees(employeenumber int,lastname

varchar(10),firstname varchar(10),extension varchar(10),email
varchar(20),officecode varchar(10),reportsto int,jobtitle varchar(12));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into employees

values('101','Deepak','Varun','1120','[email protected]','123',1000,'Manager');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employees

values('102','Mclen','Harel','1120','[email protected]','123',1000,'Manager');
Query OK, 1 row affected (0.00 sec)
mysql> select * from employees;
+----------------+----------+-----------+-----------+-----------+-----------
+-----------+----------+
| employeenumber | lastname | firstname | extension | email | officecode
| reportsto | jobtitle |
+----------------+----------+-----------+-----------+-----------+-----------
-+-----------+----------+
| 101 | Deepak | Varun | 1120 | [email protected] | 123
| 1000 | Manager |
| 102 | Mclen | Harel | 1120 | [email protected] | 123
| 1000 | Manager |
+----------------+----------+-----------+-----------+-----------+-----------
-+-----------+----------+

2 rows in set (0.00 sec)

mysql> create table emp_audit(id int not null auto_increment,

-> employeenumber int not null,
-> lastname varchar(10) not null,
-> changedon datetime default null,
-> action varchar(15) default null,
-> primary key(id))
-> ;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter $$
mysql> CREATE TRIGGER before_emp_update
-> BEFORE UPDATE ON employees
-> FOR EACH ROW BEGIN
-> INSERT INTO emp_audit
-> SET action='update',
-> employeenumber=OLD.employeenumber,
-> lastname=OLD.lastname,
-> changedon = NOW();
-> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> update employees set lastname='Ivan' where employeenumber=102;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from emp_audit;

+----+----------------+----------+---------------------+--------+
| id | employeenumber | lastname | changedon | action |
+----+----------------+----------+---------------------+--------+
| 1 | 102 | Ivan | 2015-04-21 12:37:09 | update |
+----+----------------+----------+---------------------+--------+
1 row in set (0.00 sec)

INFERENCE:

RESULT:
EX NO: 7
EXCEPTION HANDLING
DATE:
AIM:

To implement and execute PROCEDURE that handles all types of exceptions in MySQL
Database using Procedural Language concepts.

EXCEPTIONS HANDLER SYNTAX:

DECLARE handler_action HANDLER

FOR condition_value [, condition_value] ...
statement
handler_action:
CONTINUE
| EXIT
| UNDO
condition_value:
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION

The DECLARE….HANDLER statement specifies a handler that deals with one or more conditions. If one of
these conditions occurs, the specified statement executes. statement can be a simple statement such as SET
var_name = value, or a compound statement written using BEGIN and ENDThe handler_action value indicates
what action the handler takes after execution of the handler statement:

CONTINUE: Execution of the current program continues.

EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is
declared. This is true even if the condition occurs in an inner block.
UNDO: Not supported.

The condition_value for DECLARE ... HANDLER indicates the specific condition or class of conditions that
activates the handler:

A MySQL error code (a number) or an SQLSTATE value (a 5-character string literal).

A condition name previously specified with DECLARE ... CONDITION. A condition name can be
associated with a MySQL error code or SQLSTATE value.
SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'.
SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or
'02'.

Some common MySQL error codes and SQLSTATE codes

MySQL SQLSTATE Error message

error code code
1011 HY000 Error on delete of „%s‟ (errno: %d)
Disk full (%s); waiting for someone to free some
1021 HY000
space . . .
1022 23000 Can‟t write; duplicate key in table „%s‟
1027 HY000 „%s‟ is locked against change
1036 HY000 Table „%s‟ is read only
1048 23000 Column „%s‟ cannot be null
1062 23000 Duplicate entry „%s‟ for key %d

1099 HY000 Table „%s‟ was locked with a READ lock and
can‟t be updated
1100 HY000 Table „%s‟ was not locked with LOCK
TABLES
1106 42000 Incorrect parameters to procedure „%s‟
1114 HY000 The table „%s‟ is full
Delayed insert thread couldn‟t get requested
1150 HY000
lock for table %s
1165 HY000 INSERT DELAYED can‟t be used with table
„%s‟ because it is locked with LOCK TABLES
1242 21000 Subquery returns more than 1 row

1263 22004 Column set to default value; NULL supplied to

NOT NULL column „%s‟ at row %ld
1264 22003 Out of range value adjusted for column „%s‟ at
row %ld
1265 1000 Data truncated for column „%s‟ at row %ld
1312 0A000 SELECT in a stored program must have INTO
1317 70100 Query execution was interrupted
1319 42000 Undefined CONDITION: %s
DUPLICATE KEY EXCEPTION
mysql>create table article(art_id int,tag_id int,primary key(art_id,tag_id);
mysql> delimiter $$;

mysql> desc article;

-> $$
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| art_id | int(11) | NO | PRI | 0 | |
| tag_id | int(11) | NO | PRI | 0 | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> CREATE PROCEDURE in_article(IN art_id INT,IN tag_id INT)

-> BEGIN
-> DECLARE CONTINUE HANDLER for 1062
-> SELECT CONCAT('Duplicate Keys(art_id,tag_id)found') AS msg;
-> INSERT INTO article(art_id,tag_id)
-> Values(art_id,tag_id);
-> select COUNT(*) from article;
-> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> CALL in_article(1,1);

-> $$
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL in_article(1,2);

-> $$
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL in_article(1,1);
-> $$
+------------------------------------+
| msg |
+------------------------------------+
| Duplicate Keys(art_id,tag_id)found |
+------------------------------------+
1 row in set (0.00 sec)

+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

INFERENCE:

RESULT:
EX NO: 8 DATABASE DESIGN USING ER MODELING, NORMALIZATION AIM:
DATE:
To implement. database design using ER modeling, normalization and implementation for
any application

ER- Diagram:

It is an Entity –Relationship diagram which is used to represent the relationship between different
entities. An entity is an object in the real world which is distinguishable from other objects. The
overall logical structure of a database can be expressed graphically by an ER diagram, which is built
up from following components.
Rectangles: represent entity sets.
Ellipses: represent attributes.
Diamonds: represent relationships among entity sets.
Lines: link attribute to entity sets and entity sets to relationships.

Mapping Cardinalities:

It expresses the number of entities to which another entity can be associated via a relationship set.
For a binary relationship set R between entity sets A and B. The Mapping Cardinalities must be one
of the following.
One to one

One to many

Many to one

Many to many

Consider the following schema for a Library Database:

BOOK (Book_id, Title, Publisher_Name, Pub_Year)

BOOK_AUTHORS (Book_id, Author_Name)

PUBLISHER (Name, Address, Phone)

BOOK_COPIES (Book_id, Branch_id, No-of_Copies)

BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date)

LIBRARY_BRANCH (Branch_id, Branch_Name, Address)

36
ER DIAGRAM
Produce the Third Normal Form of this document by normalization

0NF
ORDER(order#, customer#, name, address, orderdate(product#, description, quantity, unitprice))

1NF
ORDER(order#, customer#, name, address, orderdate)
ORDER_LINE(order#, product#, description, quantity, unitprice)

2NF
ORDER(order#, customer#, name, address, orderdate)
ORDER_LINE(order#, product#, quantity)
PRODUCT(product#, description, unitprice)

3NF
ORDER(order#, customer#, orderdate)
CUSTOMER(customer#, name, address)
ORDER_LINE(order#, product#, quantity)
PRODUCT(product#, description, unitprice)

INFERENCE:

RESULT:
EX NO: 9 DATABASE CONNECTIVITY WITH FRONT END TOOLS AIM
DATE:
:

To design and implement a database connectivity with front end tools for Book database
using Netbeans and mysql.

Steps:

1.Create a database in Mysql

2.Use the database.
3.Create three tables books,authors and book_by_author with SQL DDL Comands.
4.Insert few records in the tables mentioned above.
5. Open the NetBeans IDE and create a Java Project
6. Add the MySQL JDBC Driver
7. Create an application by connecting to the database and Execute the SQL query
processing statements and generate the query result

Database Creation:

mysql>CREATE testdb;
mysql>USE testdb;
mysql>create table books(isbn varchar(20) primary key,title varchar(50),
edition varchar(20),price float(10,2));
mysql>create table authors(author_id int primary key,author_name
varchar(50));
mysql>create table book_by_author(isbn varchar(20),author_id int,foreign
key(isbn)references books(isbn),foreign key(author_id) references
authors(author_id));
Insertion:
msql>insert into books values('123456','Discrete Math','Second',56.78);
msql>insert into books values('102938','Numerical Methods','Third',98.46);
msql>insert into authors values(1,'CS Liu');
mysql>insert into authors values(2,'N Deo');
mysql>insert into book_by_author values('123456',1);
mysql>insert into book_by_author values('123456',2);

mysql>commit;
Open the NetBeans IDE and create a Java Project

package javaapplication1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JavaApplication1 {

static final String DB_URL =
"jdbc:mysql://localhost:3306/test";
static final String DB_DRV =
"com.mysql.jdbc.Driver";
static final String DB_USER = "Rajan";
static final String DB_PASSWD = "Rajan";

/**
* @param args the command line arguments
*/
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;

try{
connection=DriverManager.getConnection
(DB_URL,DB_USER,DB_PASSWD);
statement=connection.createStatement();
resultSet=statement.executeQuery
("SELECT * FROM books");
while(resultSet.next()){
System.out.printf("%s\t%s\t%s\t%f\n",
resultSet.getString(1),
resultSet.getString(2),
resultSet.getString(3),
resultSet.getFloat(4));
}

}catch(SQLException ex){
}finally{
try {
resultSet.close();
statement.close();
connection.close();
} catch (SQLException ex) {
}
}
}
}
OUTPUT:

INFERENCE:

RESULT:
EX NO: 10
CASE STUDY USING REAL LIFE DATABASE APPLICATIONS
DATE:
AIM:-

To create a Mini project to implement the Operations of a Student Database using JAVA as
front-end and MYSQL as back-end.

DESIGN PLAN

The Design plan consists of the following:

Project Plan
Software requirement Analysis
Implementation and Coding
Software testing
Software Debugging
Conclusion
Tables:-

1)Biodata:-

Fields Type Description

Name Varchar(20) Name of the Student
Rollno Varchar(20) Roll Number of the Student
Regno Varchar(20) Register Number of the Student
DOB date Date Of Birth of the Student

2)Attendence:-

Fields Type Description

Rollno Varchar(20) Roll number of the student
adate date Attendance Date(Absent date)

3)Marks:-

Fields Type Description

Rollno Varchar(20) Roll number of the student
Phy Int(11) Mark 1-Physics
Chem Int(11) Mark 2-Chemistry
DBMS Int(11) Mark 3-DBMS
Steps to Connect Java With MYSQL:-

1) Create a database in MYSQL.

2) Grant all privileges to user by providing password.

3) Import the packages: Requires that you include the packages containing the JDBC classes
needed for database programming. Most often using import java.sql.* will suffice.

4) Register the JDBC driver:- Requires that you initialize a driver so you can open a
communication channel with the database.

5) Open a connection: Requires using the DriverManager.getConnection() method to create a

Connection object, which represents a physical connection with the database.
6) Execute a Query: Requires using an object of type statement for building and submitting an SQl
statement to the database.
7) Extract data from ResultSet: Requires that you use the appropriate ResultSet.getXXX() method
to retrieve the data from the ResultSet
Steps To Import MYSQL Connector Into Your Project:-

1) Click project properties

2) Click on libraries and then ADD Library

3) Click on import,then you may many JARS available

4) Select the MYSQL JDBC driver and import it

Table Creation:-

mysql> use sunil;

Database changed

mysql> create table biodata(Name varchar(20),Rollnovarchar(20) primary key,Re

gnovarchar(20),DOB date);
Query OK, 0 rows affected (0.08 sec)

mysql> create table attendence(rollnovarchar(20),adate date);

Query OK, 0 rows affected (0.07 sec)

mysql> create table marks(rollnovarchar(20),phyint,chemint,dbmsint);

Query OK, 0 rows affected (0.06 sec)

mysql> grant all on sunil to root@localhost identified by 'rooty';

Query OK, 0 rows affected (0.14 sec)
CODE TO CONNECT TO THE DATABASE:-
Importing packages:-

Importjava.sql.*;
Importjavax.swing.JOptionPane;
Importjavax.swing.table.DefaultTableModel;
Button Coding:-
private void insertrecordActionPerformed(java.awt.event.ActionEventevt) {
try
{Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/sunil","root","rooty");
Statement stmt = conn.createStatement();
Class.forName("java.sql.Driver");
String nam = name.getText();
String roll = rollno.getText();
String regn = regno.getText();
String dbt = dob.getText();
stmt.executeUpdate("Insert into biodata values ('"+nam+"','"+roll+"',"+regn+" , '"+dbt+"')");
}
catch(Exception s){
JOptionPane.showMessageDialog(null, s.getMessage());
}
}

private void clearfieldsActionPerformed(java.awt.event.ActionEventevt) {

name.setText("");
rollno.setText("");
regno.setText("");
dob.setText("");
}
private void searchrollnoActionPerformed(java.awt.event.ActionEventevt) {
try
{Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/sunil","root","rooty");
Statement stmt = conn.createStatement();
Class.forName("java.sql.Driver");
String roll = rollno.getText();
ResultSetrs=stmt.executeQuery("select * from biodata where rollno='"+roll+"'");
rs.next();
name.setText(rs.getString(1));
rollno.setText(rs.getString(2));
regno.setText(rs.getString(3));
dob.setText(rs.getString(4));
}
catch(Exception s){
JOptionPane.showMessageDialog(null, s.getMessage());
}}
private void searchregnoActionPerformed(java.awt.event.ActionEventevt) {
try{
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/sunil","root","rooty");
Statement stmt = conn.createStatement();
Class.forName("java.sql.Driver");
String reg = regno.getText();
ResultSetrs=stmt.executeQuery("select * from biodata where regno='"+reg+"'");
rs.next();
name.setText(rs.getString(1));
rollno.setText(rs.getString(2));
regno.setText(rs.getString(3));
dob.setText(rs.getString(4));
}
catch(Exception s){
JOptionPane.showMessageDialog(null, s.getMessage());
}
}
private void clearActionPerformed(java.awt.event.ActionEventevt) {
rollno1.setText("");
date.setText("");
}
private void searchActionPerformed(java.awt.event.ActionEventevt) {
DefaultTableModel tm = (DefaultTableModel)jTable1.getModel();
tm.setRowCount(0);
try{
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/sunil","root","rooty");
Statement stmt = conn.createStatement();
Class.forName("java.sql.Driver");
String roll = rollno1.getText();
String Query = "Select * from attendence where rollno='"+roll+"'";
ResultSetrs = stmt.executeQuery(Query);
while(rs.next()){
String n=rs.getString("rollno");
String m=rs.getString("adate");
Object[] o ={n,m};
tm.addRow(o);
}
}
catch(Exception s){
JOptionPane.showMessageDialog(null, s.getMessage());
}
}
private void submitActionPerformed(java.awt.event.ActionEventevt) {
try
{Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/sunil","root","rooty");
Statement stmt = conn.createStatement();
Class.forName("java.sql.Driver");
String roll = rollno1.getText();
String adate = date.getText();
stmt.executeUpdate("Insert into attendence values ('"+roll+"', '"+adate+"')");
}
catch(Exception s){
JOptionPane.showMessageDialog(null,s.getMessage());
}
}
private void submit1ActionPerformed(java.awt.event.ActionEventevt) {
try{
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/sunil","root","rooty");
Statement stmt = conn.createStatement();
Class.forName("java.sql.Driver");

String n = rollno2.getText();

int r = Integer.parseInt(phy.getText());

int p= Integer.parseInt(chem.getText());

int d = Integer.parseInt(dbms.getText());

stmt.executeUpdate("Insert into marks values ('"+n+"',"+r+","+p+" , "+d+")");

catch(Exception s){

JOptionPane.showMessageDialog(null, s.getMessage());

private void clearmarksActionPerformed(java.awt.event.ActionEventevt) {

rollno2.setText("");

phy.setText("");

chem.setText("");

dbms.setText("");

private void marksearchActionPerformed(java.awt.event.ActionEventevt) {

try {

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/sunil","root","rooty");

Statement stmt = conn.createStatement();

String roll = rollno2.getText();

ResultSetrs = stmt.executeQuery("select * from marks where rollno='"+roll+"'");

rs.next();

rollno2.setText(rs.getString(1));

phy.setText(Integer.toString(rs.getInt(2)));

chem.setText(Integer.toString(rs.getInt(3)));

dbms.setText(Integer.toString(rs.getInt(4)));

}
catch(Exception e){
JOptionPane.showMessageDialog(null, e.getMessage());

SCREEN SHOTS:
INFERENCE:

RESULT :
CONTENT BEYOND SYLLABUS

EX NO:11 POPULATE DATABASE IN QUERY

DATE:
AIM:
To Write any Populate Database in Query.

INFERENCE

RESULT:
EX NO:14
REPORTS USING SQL
DATE:

AIM:

To prepare reports using database table in SQL.

EXAMPLE 1
SQL> SET LINESIZE 80
SQL> TTITLE 'PIT STAFF'
SQL> select * from emp;

EXAMPLE 2

SQL> ttitle 'Employee Details'

SQL> btitle ''End”
SQL> column mark heading 'salary' format$9999.99
SQL> clear computes
computes cleared
SQL> break on title skip 2
SQL> compute sum of Salary on title
SQL> select * from emp order by empno,ename,designatin,salary;

Employee Details

INFERENCE

RESULT:
EX NO:15
CALCULATE AREA OF CIRCLE USING PL/SQL PROGRAM
DATE:

AIM:

To write a pl/sql code block to calculate the area of a circle for a value of radius varying from
3 to 7. Store the radius and the corresponding values of calculated area in an empty table named
areas, consisting of two columns radius & area .

Procedure:

Step 1: Create a table named areas with radius and area

Step 2: Initialize values to pi, radius and area
Step 3 :Calculate the area using while loop. Display the result.

INFERENCE

RESULT