VEERAMMAL ENGINEERING COLLEGE

PVP NAGAR, K.SINGARAKOTTAI (Post),

DINDIGUL-624708.

CS3481- DATABASE MANAGEMENT SYSTEM LABORATORY

RECORD NOTE BOOK

NAME : ……………………………………………………

REGISTER NO : ……………………………………………………

BRANCH : ……………………………………………………

DEGREE/YEAR/SEM : ……………………………………………………
VEERAMMAL ENGINEERING COLLEGE

PVP NAGAR, K.SINGARAKOTTAI (Post),

DINDIGUL-624708.

BONAFIDE CERTIFICATE

NAME :…………………………………………..

REGISTER NO :…………………………………………..

BRANCH/YEAR/SEM :…………………………………………..

SUBJECT :………………………………………….

Certified that this is the bonafide record of work done by the above
student in ........................................................................ Laboratory during the year
20 - 20

Lab-In Charge Head of the Department

Submitted for the Practical Examination held on …………………..

Internal Examiner External Examiner

LIST OF EXPERIMENTS

S.No Date List of Experiments Page No Signature

Create a database table, add constraints

1 26/2/2025 (primary key, unique, check, Not null),insert
rows, update and delete rows using SQL
DDL and DML commands
2 5/3/2025 Create a set of tables, add foreign key
constraints and incorporate referential
integrity
3 5/3/2025 Query the database tables using different
‘where’ clause conditions and also
implement aggregate functions
4 12/3/2025 Query the database tables and explore sub
queries and simple join operations
Query the database tables and explore
5 12/3/2025 natural, equi and outer joins
Write user defined functions and stored
6 19/3/2025 procedures in SQL

7 26/3/2025 Execute complex transactions and realize

DCL and TCL commands
8 9/4/2025
Write SQL Triggers for insert, delete, and
update operations in a database table
9 16/4/2025 Create View and index for database tables
with a large number of records

10 23/4/2025 Create an XML database and validate it using

XML schema
Create Document, column and graph
11 30/4/2025 based data using NOSQL database tools
12 7/5/2025
Develop a simple GUI based database
application
13 14/5/2025
Case Study using any of the real life
database applications
EX.NO. : 1
Create A Database Table, Add Constraints (Primary Key, Unique,
DATE : Check, Not Null) Insert Rows, Update and Delete Rows Using SQL
DDL and DML Commands

AIM:
To create a database table, add constraints and execute SQL DDL and DML commands.

SQL:
SQL stands for Structured Query Language. SQL is composed of commands that
enable users to create database and table structures, perform various types of data
manipulation and data administration, and query the database to extract useful
information.

DATA DEFINITION LANGUAGE (DDL)

The language used to define the database schema is called Data Definition
language. DDL is used to create, update and drop the database views. The
Commands used in DDLare

 CREATE
 ALTER
 DROP
 TRUNCATE
 RENAME
CREATE TABLE:
This command is used to create database tables in RDBMS.

Syntax
CREATE TABLE table_name (colname1 datatype,
colname2datatype,colname3 datatype,…colnamen
datatype);

ALTER TABLE:

This command is used to add or drop or modify the attributes from the existing table.

Syntax:

Adding an attribute:

ALTER TABLE <table_name> ADD column_name datatype;

Dropping An Attribute:

ALTER TABLE <table_name> DROP COLUMN column_name;

Page 1
Page 2
Modifying an attribute:

ALTER TABLE <table_name> MODIFY column_name newdatatype;

TRUNCATE:

TRUNCATE command removes all the records from a table. But this
command will notdestroy the table's structure. When we use
TRUNCATE command on a table its (auto- increment) primary key is
also initialized.

Syntax:

TRUNCATE TABLE table_name;

RENAME

RENAME command is used to set a new name for any

existing table.

Syntax:

RENAME TABLE old_table_name to new_table_name

DROP

This command is used to remove a relation from an SQL database.

This commanddeletes not only the records of the table but also the entire
schema of the database.

Syntax:

DROP TABLE <table_name>;

DATA MANIPULATION LANGUAGE (DML):

 Insert
 Select
 Update
 Delete
INSERT
Insert command is used to insert the values into the table. There are three
ways to inserta record into the database.

Page 3
Syntax:

Insert into table_name values (value1, value 2…);

Entering multiple rows:

Insert into table_name values (value1, value2),(value1,Value)…

SELECT:

The select statement is used to query a database. This statement is used to

retrieve the information from the database. The SELECT statement can
be used in many ways. Theyare:

1. Selecting some columns:

To select specified number of columns from the table the
following command isused.

Syntax

SELECT column name FROM table_name;

2. Query All Columns:

To select all columns from the table * is used instead of

column names.Syntax

SELECT * FROM table_name;

UPDATE:

UPDATE command is used to change a value of a record

in the database.Syntax:

UPDATE table_name

SET column name =new

value WHEREcolumn
name=some value;

DELETE:

The DELETE statement is used to delete

rows in a table.Syntax:

DELETE FROM table_name WHERE column

name=some value;

Page 4
COMMANDS

1. Create a table called employer with following attributes

empno, ename, job,deptno, salary.
2. empno as Primary key Check salary is greater than 100000
3. deptno as unique key
Allow not null for ename and job
Create table employer (empno int (10) primary key, ename varchar(10) not null, job
varchar(10) not null, deptno int (10) unique, salary int(10), check(salary>100000));

2. Add a column experience in employer table

Alter table employer add (experience int(10));

3. Modify the column width in job field

alter table employer modify job varchar(15);

Page 5
4. Insert a record into employer table

insert into employer values(101,”aaa”,”manager”,1,200000,3);

5. Update the employer table to set the salary of all employees to

Rs450000 who areworking as Manager

update employer set salary=450000 where job=”manager”;

6. Select employee name, job from employer table

select ename, job from employer;

7. Delete only those who are working as web designer

delete from employer where job=”web designer”;

Page 6
8. List the records in the employer table orderby salary in ascending order

select * from employer order by salary;

9. List the records in the employer table orderby salary in descending order

select * from employer order by salary desc;

10. Display only those employees whose deptno greater than 3

select * from employer where deptno>3;

11. Display salary from the employer table avoiding the duplicated values

select distinct salary from employer;

Page 7
12. Drop a column experience in employer table

alter table employer drop experience;

13. Truncate the employer table

truncate table employer;

14. Drop the employer table

drop table employer;

RESULT:

Thus, the creation of database and the SQL queries to retrieve information
from thedatabase has been implemented and the output was verified.

Page 8
EX.NO. : 2
Create A Set Of Tables, Add Foreign Key Constraints and Incorporate
DATE : Referential Integrity

AIM:

To create a set of tables, add foreign key constraints and incorporate referential integrity

FOREIGN KEY

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the PRIMARYKEY in another table. The table with the foreign key is called
the child table, and the table with the primary key is called the referenced or
parent table. The FOREIGN KEY constraint prevents invalid data from being
inserted into the foreign key column, because it has to be one of the values
contained in the parent table.

SQL foreign key on create table

1. Create a persons table with personid, firstname, last_name,

age (person id asprimary key)

CREATE TABLE Persons (ID int, LastName varchar(255) NOT

NULL, FirstNamevarchar(255), Age int, PRIMARY KEY (ID));

2. create a FOREIGN KEY on the "PersonID" column when the

"Orders" table iscreated

CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT
NULL, id int,PRIMARY KEY (OrderID), FOREIGN KEY (id)
REFERENCES Persons(ID));

3. Insert a value into persons and Orders table

4. Select * from Persons
5. Select * from Orders

Page 9
RESULT:

Thus, the table is created with foreign key constraints and executed successfully.

Page 10
EX.NO. : 3
Query the Database Tables using different ‘Where’ Clause Conditions
DATE : and also Implement Aggregate Functions

AIM:

To create a database table using different “where” clause conditions and also
implementaggregate functions.

WHERE

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition

Syntax:

Select/update/delete column1, column2, ...

FROM table name
WHERE condition;

The following operators can be used in the WHERE clause

=,>, <, <+, >=, between, like, in
The WHERE clause can be combined with AND, OR, and NOT operators
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

SELECT column1, column2, ...

FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

SELECT column1, column2, ...

FROM table_name
WHERE NOT condition;

ORDER BY
The ORDER BY keyword is used to sort the result-set in ascending or descending order.

Page 11
The ORDER BY keyword sorts the records in ascending order by default.

To sort the recordsin descending order, use the DESC keyword

Syntax:
SELECT column1, column2, ...

FROM table_name

ORDER BY column1, column2, ... ASC|DESC;

UPDATE:
The UPDATE statement is used to modify the existing records in a table.
Syntax:
UPDATE table_name

SET column1 = value1,

column2 = value2, ...WHERE
condition;

DELETE:
The DELETE statement is used to delete existing
records in a tableSyntax:
DELETE FROM table_name WHERE condition;

LIKE:
The LIKE operator is used in a WHERE clause to search for a
specified pattern in acolumn

The percent sign (%) represents zero, one, or

multiple charactersThe underscore sign (_)

represents one, single character

Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE columnN LIKE pattern;

Page 12
AGGREGATE FUNCTIONS:
1. MIN( )
2. MAX( )
3. AVG( )
4. SUM( )
5. COUNT( )
Syntax:

SELECT COUNT/AVG/MIN/MAX/SUM (column_name)

FROM table_name

WHERE condition;

The MIN() function returns the smallest value of the

selected column.The MAX() function returns the

largest value of the selected column

The COUNT() function returns the number of rows that matches a

specified criterionThe AVG() function returns the average value of a

numeric column

The SUM() function returns the total sum of a numeric column.

GROUP BY:

The GROUP BY statement groups rows that have the same values into
summary rows, like"find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions

(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more
columns

Syntax:

SELECT column_name(s)FROM
table_name WHERE condition

GROUP BY column_name(s);

Page 13
HAVING:

The HAVING clause was added to SQL because the WHERE keyword
cannot be used withaggregate functions

Syntax:

SELECT column_name(s)FROM
table_name WHERE condition

GROUP BY column_name(s)
HAVING condition

ORDER BY column_name(s);

Queries:

1. Create a table
Employee (empno, ename, job, deptno,salary)

2. Diplay all the details of the records whose employees name starts with “a”
select * from employee where ename like “a%”

3. Display all the details of the records whose employees name

does not startwith “a”
Select * from employee where ename not like “a%”
4. Display the rows whose salary ranges from 15000 to 30000
Select * from employee where salary between 15000 and 30000;

Page 14
1. Calculate total and average salary amount of the employee table
Select sum(salary), avg(salary) from employee;
2. Determine the max and min salary and rename the
column as maximumsalary and minimum_salary
Select max(salary) as maximum_salary, min(salary) as
minimum_salary fromemployee;
3. Count the total records in the employee table
Select count(*) from employee;

RESULT:

Thus the database table is created using different ‘where’ clause conditions and also
implements aggregate functions.

Page 15
EX.NO. : 4
Query the Database Tables and Explore Sub Queries and Simple
DATE : Join Operations

AIM:
To create a database tables and implement sub queries and simple join
operations.

SQL Sub Query

A Subquery is a query within another SQL query and embedded within the WHERE clause.

Important Rules:

o A subquery can be placed in a number of SQL clauses like WHERE

clause, FROMclause, HAVING clause.
o You can use Subquery with SELECT, UPDATE, INSERT, DELETE
statements alongwith the operators like =, <, >, >=, <=, IN, BETWEEN,
etc.
o A subquery is a query within another query. The outer query is known
as the mainquery, and the inner query is known as a subquery.
o Subqueries are on the right side of the comparison operator.
o A subquery is enclosed in parentheses.
o In the Subquery, ORDER BY command cannot be used. But GROUP
BY commandcan be used to perform the same function as ORDER BY
command.

SELECT:

The select statement is used to query a database. This statement is used to

retrieve the information from the database. The SELECT statement can be
used in many ways. Theyare,
1. Selecting some columns:
To select specified number of columns from the table the following command is
used.

Syntax:
SELECT column name FROM table_name;

Page 16
Query All Columns:
To select all columns from the table * is used instead of column names.

Syntax:
SELECT * FROM table_name;

Select using IN:

If you want to get the rows, which contain certain values, the
best way to do itis to usethe IN conditional expression.

Syntax:
SELECT column name(s) FROM
table_name WHERE Column_name IN
(value1,value2,……,value n);

Select using BETWEEN:

BETWEEN can be used to get those items that fall

within a range.Syntax:
SELECT column name
FROM table_name
WHEREColumn name
BETWEEN value1 AND
value2;

Creating a alias for a column:

The select statement can be used to rename either a column or the entire table.

Syntax: Renaming a column:

SELECT column name AS new name FROM table_name;

1. To Select NULL values:

We can use the SELECT statement to select the ‘null’ values also. For
retrieving rows where some of the columns have been defined as NULLs
there is a special comparison operator of the form IS[NOT]NULL.

Syntax:

SELECT column name FROM table_name

WHEREColumn name IS NULL;

2. Select using AND, OR, NOT:

We can combine one or more conditions in a SELECT statement

Page 17
using the logicaloperators AND,OR,NOT.

Syntax:
SELECT column name FROM table_name WHERE
Condition1 LOGICAL OPERATOR
condition2;

JOIN:

SQL joins are used to query data from two or more tables, based
on a relationshipbetween certain columns in these tables.

 INNER JOIN/ NATURAL JOIN/ JOIN: It is a binary operation

that allows us tocombine certain selections and a Cartesian product
into one operation.

 INNER JOIN SYNTAX:

SQL>SELECT column_name(s) FROM table_name1 INNER JOIN

table_name2 ON

table_name1.column_name=table_name2.column_name;

Queries:
1. Creating Dept table:
mysql> create table dept(dno int(10),dname varchar(10),loc varchar(10));
Query OK, 0 rows affected, 1 warning (0.23 sec)

mysql> insert into dept

values(10,'inventory','hyd'),(20,'finance','bglr'),(30,'H
R','mumbai'); Query OK, 3 rows affected (0.07 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from dept;

+ + + +
| dno | dname | loc |
+ + + +
| 10 | inventory | hyd |
| 20 | finance | bglr |
| 30 | HR | mumbai |
+ + + +3 rows in set (0.00 sec) Creating emp2 table:

Page 18
mysql> create table emp2(eno int(10),ename varchar(10),job
varchar(10),Mgrint(10),dno int(10));

Query OK, 0 rows affected, 3 warnings (0.19 sec)

mysql> insert into emp2 values(111,'saketh','analyst',444,10);

Query OK, 1 row affected (0.08 sec)

mysql> insert into emp2 values(222,'sandeep','clerk',333,20);

Query OK, 1 row affected (0.35 sec)

mysql> insert into emp2 values(333,'jagan','manager',111,10);

Query OK, 1 row affected (0.06 sec)

mysql> insert into emp2 values(444,'madhu','engineer',222,40);

Query OK, 1 row affected (0.07 sec)

mysql> select * from emp2;

+ + + + + +
| eno | ename | job | Mgr | dno |
+ + + + + +
| 111 | saketh | analyst | 444 | 10 |
| 222 | sandeep | clerk | 333 | 20 |
| 333 | jagan | manager | 111 | 10 |
| 444 | madhu | engineer | 222 | 40 |
+ + + + + +4 rows in set (0.00 sec)
mysql> select eno,ename,job,dname,loc from emp2 e,dept d where
e.dno=d.dno;
+ + + + + +
| eno | ename | job | dname | loc |
+ + + + + +
| 111 | saketh | analyst | inventory | hyd |
| 222 | sandeep | clerk | finance | bglr |
| 333 | jagan | manager | inventory | hyd |
+ + + + + +3 rows in set (0.04 sec)

Page 19
Using Clause:
mysql> select eno,ename,job,dname,loc from emp2 e join dept d using(dno);

+ + + + + +
| eno | ename | job | dname | loc |

+ + + + + +
| 111 | saketh | analyst | inventory | hyd |

| 222 | sandeep | clerk | finance | bglr |

| 333 | jagan | manager | inventory | hyd |
+ + + + + +3 rows in set (0.00 sec)
On Clause:
mysql> select eno,ename,job,dname,loc from emp2 e join dept d
on(e.dno=d.dno);

+ + + + + +
| eno | ename | job | dname | loc |
+ + + + + +
| 111 | saketh | analyst | inventory | hyd |
| 222 | sandeep | clerk | finance | bglr |
| 333 | jagan | manager | inventory | hyd |
+ + + + + +3 rows in set (0.00 sec)

1. Non-Equijoin:
A join which contains an operator other than equal to ‘=’ in the join condition.
mysql> select eno,ename,job,dname,loc from emp2 e,dept d where
e.dno>d.dno;
+ + + + + +
| eno | ename | job | dname | loc |
+ + + + + +
| 222 | sandeep | clerk | inventory | hyd |
| 444 | madhu | engineer | HR | mumbai |
| 444 | madhu | engineer | finance | bglr |
| 444 | madhu | engineer | inventory | hyd |
+ + + + + +

4 rows in set (0.03 se

Page 20
TO CREATE SSTUD1 TABLE
mysql> create table sstud1 ( sname varchar(20) , place varchar(20));

Query OK, 0 rows affected (1.37 sec)

mysql> insert into sstud1 values ( 'prajan','chennai'),(

'anand','chennai'),('kumar','chennai'),( 'ravi','chennai');
Query OK, 4 rows
affected (0.27 sec)
Records: 4 Duplicates: 0
Warnings: 0

mysql> select * from sstud1;

+ + +
| sname | place |
+ + +
| prajan | chennai |
| anand | chennai |
| kumar | chennai |
| ravi | chennai |
+ + +
TO CREATE SSTUD2 TABLE
mysql> create table sstud2 ( sname varchar(20), dept varchar(10), marks int(10));

Query OK, 0 rows affected, 1 warning (0.52 sec)

mysql> insert into sstud2 values

('prajan','cse',700),('anand','it',650),('vasu','cse',680),('
ravi','it',600);Query OK, 4 rows affected (0.03 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from sstud2;

+ + + +
| sname | dept | marks |
+ + + +
| prajan | cse | 700 |

Page 21
| anand | it | 650 |
| vasu | cse | 680 |
| ravi | it | 600 |
+ + + + 4 rows in
set (0.00 sec)

NESTED QUERIES
mysql> select sname from sstud1 where sstud1.sname in ( select
sstud2.sname fromsstud2 );

+ +

| sname |

+ +

| prajan |

| anand |

| ravi |

+ +

3 rows in set (0.15 sec)

mysql> select sname from sstud1 where sstud1.sname not in ( select

sstud2.sname fromsstud2 );

+ +

| sname |

+ +

| kumar |

+ +

1 row in set (0.06 sec)

mysql> select sname from sstud2 where marks > some(select marks from
sstud2 wheredept='cse');

+ +
| sname |
+ +

Page 22
| prajan |
| vasu |

+ +

1 rows in set (0.00 sec)

mysql> select sname from sstud2 where marks > all ( select marks from
sstud2 wheredept='cse' );

Empty set (0.00 sec)

mysql> select sname from sstud2 where marks < all ( select marks from
sstud2 wheredept='cse' );

+ +

| sname |

+ +

| anand |

| ravi |

+ +

2 rows in set (0.00 sec)

mysql> select sname from sstud1 where exists ( select sstud2.sname from
sstud2 wheresstud1.sname=sstud2.sname );

+ +

| sname |

+ +

| prajan |
| anand |

| ravi |

3 rows in set (0.00 sec)

Page 23
mysql> select sname from sstud1 where not exists ( select
sstud2.sname from sstud2where sstud1.sname=sstud2.sname );

+ +
| sname |
| kumar |
+ +
1 row in set (0.00 sec)

RESULT:

Thus, the database tables were created and explore sub queries and simple join

operations.

Page 24
EX.NO. : 5
Query the Database Tables and Explore Natural, Equi And Outer
DATE : Joins

AIM:
To create a database table and explore natural, equi and outer joins.

JOIN OPERATIONS:
 INNER JOIN/ NATURAL JOIN/ JOIN: It is a binary operation that
allows us to combine certain selections and a Cartesian product into one
operation.
 OUTER JOIN: It is an extension of join operation to deal with missing information.
 Left Outer Join: It takes tuples in the left relation that did not
match with any tuple in the right relation, pads the tuples with null
values for all other attributesfrom the right relation and adds them
to the result of the natural join.
 Right Outer Join: It takes tuples in the right relation that did not
match with anytuple in the left relation, pads the tuples with null
values for all other attributesfrom the left relation and adds them to
the result of the natural join.
 Full Outer Join: It combines tuples from both the left and the right
relation and pads the tuples with null values for the missing
attributes and hem to the resultof the natural join.
INNER JOIN SYNTAX:

SQL>SELECT column_name(s) FROM table_name1 OUTER JOIN table_name2

ON table_name1.column_name=table_name2.column_name;

Queries:

1. Creating Dept table:

mysql> create table dept(dno int(10),dname varchar(10),loc varchar(10));
Query OK, 0 rows affected, 1 warning (0.23 sec)
mysql> insert into dept
values(10,'inventory','hyd'),(20,'finance','bglr'),(30,'HR','mumbai');Query OK,
3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0

Page 25
mysql> select * from dept;
+ + + +
| dno | dname | loc |
+ + + +
| 10 | inventory | hyd |
| 20 | finance | bglr |
| 30 | HR | mumbai |
+ + + +3 rows in set (0.00 sec) Creating emp2 table:

mysql> create table emp2(eno int(10),ename varchar(10),job

varchar(10),Mgr
int(10),dno int(10));
Query OK, 0 rows affected, 3 warnings (0.19 sec)

mysql> insert into emp2 values(111,'saketh','analyst',444,10);

Query OK, 1 row affected (0.08 sec)

mysql> insert into emp2 values(222,'sandeep','clerk',333,20);

Query OK, 1 row affected (0.35 sec)

mysql> insert into emp2 values(333,'jagan','manager',111,10);

Query OK, 1 row affected (0.06 sec)

mysql> insert into emp2 values(444,'madhu','engineer',222,40);

Query OK, 1 row affected (0.07 sec)

mysql> select * from emp2;

+ + + + + +
| eno | ename | job | Mgr | dno |
+ + + + + +
| 111 | saketh | analyst | 444 | 10 |
| 222 | sandeep | clerk | 333 | 20 |
| 333 | jagan | manager | 111 | 10 |
| 444 | madhu | engineer | 222 | 40 |
+ + +

4 rows in set (0.00 sec)

Page 26
mysql> select eno,ename,job,dname,loc from emp2 e,dept d where
e.dno=d.dno;
+ + + + + +

| eno | ename | job | dname | loc |

+ + + + + +
| 111 | saketh | analyst | inventory | hyd |
| 222 | sandeep | clerk | finance | bglr |

| 333 | jagan | manager | inventory | hyd |

+ + + + + +3 rows in set (0.04 sec)
Using Clause:
mysql> select eno,ename,job,dname,loc from emp2 e join dept d using(dno);

+ + + + + +
| eno | ename | job | dname | loc |
+ + + + + +
| 111 | saketh | analyst | inventory | hyd |
| 222 | sandeep | clerk | finance | bglr |
| 333 | jagan | manager | inventory | hyd |
+ + + + + +3 rows in set (0.00 sec)
On Clause:
mysql> select eno,ename,job,dname,loc from emp2 e join dept d
on(e.dno=d.dno);

+ + + + + +
| eno | ename | job | dname | loc |
+ + + + + +
| 111 | saketh | analyst | inventory | hyd |
| 222 | sandeep | clerk | finance | bglr |
| 333 | jagan | manager | inventory | hyd |
+ + + + + +3 rows in set (0.00 sec)

2. Non-Equijoin:
A join which contains an operator other than equal to ‘=’ in the join condition.
mysql> select eno,ename,job,dname,loc from emp2 e,dept d where
e.dno>d.dno;

Page 27
+ + + + + +
| eno | ename | job | dname | loc |

+ + + + + +
| 222 | sandeep | clerk | inventory | hyd |
| 444 | madhu | engineer | HR | mumbai |
| 444 | madhu | engineer | finance | bglr |
| 444 | madhu | engineer | inventory | hyd |
+ + + + + +4 rows in set (0.03 sec)
3. Natural Join:
It compares all the common columns.
mysql> select eno,ename,job,dname,loc from emp2 natural join dept;
+ + + + + +
| eno | ename | job | dname | loc |
+ + + + + +
| 111 | saketh | analyst | inventory | hyd |
| 222 | sandeep | clerk | finance | bglr |
| 333 | jagan | manager | inventory | hyd |
+ + + + + +3 rows in set (0.00 sec)
4. Cross Join:
This will give the cross product.
mysql> select eno,ename,job,dname,loc from emp2 cross join dept;
+ + + + + +
| eno | ename | job | dname | loc |
+ + + + + +
| 111 | saketh | analyst | HR | mumbai |
| 111 | saketh | analyst | finance | bglr |
| 111 | saketh | analyst | inventory | hyd |
| 222 | sandeep | clerk | HR | mumbai |
| 222 | sandeep | clerk | finance | bglr |
| 222 | sandeep | clerk | inventory | hyd |
| 333 | jagan | manager | HR | mumbai |
| 333 | jagan | manager | finance | bglr |
| 333 | jagan | manager | inventory | hyd |

Page 28
| 444 | madhu | engineer | HR | mumbai |
| 444 | madhu | engineer | finance | bglr |
| 444 | madhu | engineer | inventory | hyd |
+ + + + + +
12 rows in set (0.00 sec)
5. Outer Join:
It gives the non matching records along with matching records.
Left Outer Join:
This will display the all matching records and the records which are in
left hand sidetable those that are in right hand side table.
mysql> select eno,ename,job,dname,loc from emp2 e left outer join dept
don(e.dno=d.dno);
+ + + + + +
| eno | ename | job | dname | loc |
+ + + + + +
| 111 | saketh | analyst | inventory | hyd |
| 222 | sandeep | clerk | finance | bglr |
| 333 | jagan | manager | inventory | hyd |
| 444 | madhu | engineer | NULL | NULL |
+ + + + + +4 rows in set (0.00 sec)
Right Outer Join:
This will display the all matching records and the records which
are in right handside table those that are not in left hand side table.
mysql> select eno,ename,job,dname,loc from emp2 e right
outer join dept don(e.dno =d.dno);
+ + + + + +
| eno | ename | job | dname | loc |
+ + + + + +
| 333 | jagan | manager | inventory | hyd |
| 111 | saketh | analyst | inventory | hyd |
| 222 | sandeep | clerk | finance | bglr |
| NULL | NULL | NULL | HR | mumbai |
+ + + + + +

4 rows in set (0.00 sec)

Page 29
RESULT:
Thus, the database tables are created and explore natural join, equi join and outer joins.

Page 30
EX.NO. : 6

DATE : Write User Defined Functions and Stored Procedures in SQL

AIM:

To write a program using user defined functions and stored procedures in SQL.

MySQL Stored Function

A stored function is a special kind stored program that returns a single value.
You use stored functionsto encapsulate common formulas or business rules
that are reusable among SQL statements or stored programs. Different from a
stored procedure, you can use a storedfunction in SQL statements wherever an
expression is used. This helps improve the readability and maintainability of
the procedural code.
The following illustrates the simplest syntax for creating a new stored function:
CREATE FUNCTION function_name(parameter 1,parameter 2,…)
RETURNS datatype [NOT] DETERMINISTIC
Statements

Example
Function to concatenate two stringsUSE `sample1`;
DROP function IF
EXISTS `funcon`;
DELIMITER $$
USE `sample1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `funcon`(s CHAR(20))
RETURNSchar(50)
CHARSET utf8mb4DETERMINISTIC

BEGIN
RETURN CONCAT('Hello, ',s,'!!');
RETURN 1;
END$$
DELIMITER ;
Executing function select
funcon('world');#
funcon('world') 'Hello,
world!!’

Page 31
Stored procedure
MySQL stored procedure using CREATE PROCEDURE statement. In
addition, we willshow you how to call stored procedures from SQL
statements.
mysql> use db1;
Database changed
mysql> create table studentnew(rollno int, name varchar(10), marks int,
status varchar(10));Query OK, 0 rows affected (0.79 sec)

mysql> insert into studentnew

values(1,"ram",60,"pass"),(2,"seetha",50,"pass"),(3,"diya",40,"null"),(4,"jo
hn",20,"null");
Query OK, 4 rows
affected (0.08 sec)
Records: 4 Duplicates:
0 Warnings: 0mysql>
select * from
studentnew;
+ + + + +
| rollno | name | marks | status |
+ + + + +
| 1 | ram | 60 | pass |
| 2 | seetha | 50 | pass |
| 3 | diya | 40 | null |
| 4 | john | 20 | null |
+ + + + +4 rows in set (0.00 sec)
mysql> DELIMITER @@
mysql> create procedure updatestatus1(in mark1 int)
-> begin
-> declare mymark int;
-> set mymark=mark1;
-> if mymark>35 then
-> update studentnew set status="pass" where marks=mymark;
-> else
-> update studentnew set status="fails" where marks=mymark;
-> end if;
-> end;
-> @@
Page 32
Query OK, 0 rows affected (0.08 sec)

mysql> call updatestatus1(40);

-> @@

Query OK, 1 row affected (0.08 sec)

mysql> select * from studentnew;

-> @@
+ + + + +
| rollno | name | marks | status |
+ + + + +
| 1 | ram | 60 | pass |
| 2 | seetha | 50 | pass |
| 3 | diya | 40 | pass |
| 4 | john | 20 | null |
+ + + + +

4 rows in set
(0.06 sec)

RESULT:
Thus, the program was created using stored procedures and functions in SQL.

Page 33
EX.NO. : 7
Execute Complex Transactions and Realize DCL and TCL
DATE : Commands

AIM:
To study and execute various Data Control Language and Transaction Control
Languagecommands in SQL.
Procedure:
1: Start
2: Create the table with its essential attributes. 3: Insert the
record into table4: Execute the DCL commands GRANT and
REVOKE
5: Execute the TCL commands COMMIT, SAVEPOINT and
ROLLBACK.6: Stop
DCL Commands.
DCL includes commands such as GRANT and REVOKE which mainly
deal with therights, permissions, and other controls of the database
system.
GRANT:
This command gives users access privileges to the database. For this first we
have to createuser.
MySQL allows us to specify which user account can connect to a database
server. The user account details in MySQL contains two information –
username and host from which the user is trying to connect in the format
username@host-name. If the admin user is connectingthrough localhost then the
user account will be admin@localhost. MySQL stores the user account in the
user grant table of the mysql database. The CREATE USER statement in
MySQL allows us to create new MySQL accounts or in other words, the
CREATE USER statement is used to create a database account that allows the
user to log into the MySQL database.
Syntax:
CREATE USER user_account IDENTIFIED
BY password;NOTE: in our system user is
a@localhost
Syntax:
GRANT privileges_names ON
object TO userParameters Used in
Grant Command
privileges_name: These are the access rights or privileges granted to the
user. object:It isthe name of the database object to which permissions are
being granted. In the case ofgranting privileges on a table, this would be the
Page 34
table name. user:It is the name of the user to whom the privileges would be
granted. Various privileges used are, SELECT, INSERT,DELETE, INDEX,
UPDATE, CREATE, ALTER, DROP, GRANT. 1. Granting SELECT
Privilege to a User in a Table:
To grant Select Privilege to a table named “users” where User Name is root, the following

GRANT statement should be executed. GRANT SELECT ON Users TO a@localhost;

2. Granting more than one Privilege to a User in a Table:
To grant multiple Privileges to a user named “root” in a table “users”, the
following GRANT statement should be executed. GRANT SELECT,
INSERT, DELETE, UPDATEON Users TO a@localhost;
3. Granting All the Privilege to a User in a Table:
To Grant all the privileges to a user named “root” in a table “users”, the
following Grantstatement should be executed. GRANT ALL ON Users
TO a@localhost;
4. SQL Grant command is specifically used to provide privileges to
database objects for auser. This command also allows users to grant
permissions to other users too. Syntax:
grant privilege_name on object_name to {user_name | public | role_name}
REVOKE:
This command withdraws the user’s access privileges given by using the
GRANT command. Revoke command withdraw user privileges on database
objects if any granted. Itdoes operations opposite to the Grant command.
When a privilege is revoked from a particular user U, then the privileges
granted to all other users by user U will be revoked.
Syntax:
REVOKE privilege_name on object_name from {user_name | public | role_name}
Example
REVOKE insert, select on accounts from user
Queries
mysql> connect db1;
Connection id: 12
Current database: db1
mysql> create user a@localhost;
Query OK, 0 rows affected (0.17 sec)
mysql> grant select on schooldetails
to a@localhost;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from schooldetails;
+ + + +
| schoolname | id | no_of_students |

Page 35
+ + + +
| IR | 1| 3400 |
| KWM | 2| 4000 |
| PRT | 4| 5789 |
+ + + +
mysql> grant all on schooldetails
to a@localhost;Query OK, 0 rows
affected (0.04 sec)

mysql> revoke select on schooldetails

from a@localhost;Query OK, 0 rows
affected (1.43 sec)

TCL (Transaction Control Language)

Transaction Control Language(TCL) commands are used to manage
transactions in database.These are used to manage the changes made by DML
statements. It also allows statements to be grouped together into logical
transactions. TCL Commands are
1. Commit
Commit command is used to permanently save any transaction into
database.Following is Commit command's syntax, commit;
2. Rollback
This command restores the database to last commited state. It is also use with
savepoint command to jump to a savepoint in a transaction. Following is
Rollback command's syntax,rollback to savepoint-name;
3. Savepoint
savepoint command is used to temporarily save a transaction so that you can
rollback to thatpoint whenever necessary. Following is savepoint command's
syntax, savepoint savepoint- name;
Example
mysql> create table schooldetails(schoolname varchar(5), id int,
no_of_students int);Query OK, 0 rows affected (0.12 sec)

mysql> insert into

schooldetails("IR",1,3400),("KWM",2,4000),("PRT",4,5789); mysql>
insert into schooldetails values
("IR",1,3400),("KWM",2,4000),("PRT",4,5789);Query OK, 3 rows
affected (0.14 sec)
Records: 3 Duplicates: 0 Warnings: 0

Page 36
mysql> select * from schooldetails;
+ + + +
| schoolname | id | no_of_students |
+ + + +
| IR | 1| 3400 |
| KWM | 2| 4000 |
| PRT | 4| 5789 |
+ + + +3 rows in set (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.01 sec)

mysql> Savepoint s1;

Query OK, 0 rows affected (0.03 sec)

mysql> update schooldetails set

No_of_students=7000 where id=2;Query OK, 1 row
affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from schooldetails;

+ + + +
| schoolname | id | no_of_students |
+ + + +
| IR | 1| 3400 |
| KWM | 2| 7000 |
| PRT | 4| 5789 |
+ + + +3 rows in set (0.00 sec)

mysql> Savepoint S2;

Query OK, 0 rows affected (0.09 sec)

mysql> rollback to S1;

Query OK, 0 rows affected (0.10 sec)

Page 37
mysql> select * from schooldetails;
+ + + +
| schoolname | id | no_of_students |
+ + + +
| IR | 1| 3400 |
| KWM | 2| 4000 |
| PRT | 4| 5789 |
+ + + +3 rows in set (0.00 sec)

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

mysql> rollback to S2;

mysql> select * from schooldetails;
+ + + +
| schoolname | id | no_of_students |
+ + + +
| IR | 1| 3400 |
| KWM | 2| 4000 |
| PRT | 4| 5789 |
+ + + +3 rows in set (0.00 sec)

RESULT:
Thus the DCL and TCL commands are studied and executed successfully and output was
verified.

Page 38
EX.NO. : 8
Write SQL Triggers for Insert, Delete and Update Operations in a
DATE : Database Tables

Aim:
To execute programs for insert, delete, and update operations in a database table
using triggers.
A MySQL trigger is a stored program (with queries) which is executed
automatically to respond to a specific event such as insertion, updation or
deletion occurring in a table. In order to create a new trigger, you use the
CREATE TRIGGER statement. The following illustrates the syntax of the
CREATE TRIGGER statement:
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name
FOR EACHROW
BEGIN
... END;
Let’s examine the syntax above in more detail.
You put the trigger name after the CREATE TRIGGER statement. The trigger
name shouldfollow the naming convention [trigger time]_[table name]_[trigger
event], for example before_employees_update. Trigger activation time can be
BEFORE or AFTER. You must specify the activation time when you define a
trigger. You use the BEFORE keyword if youwant to process action prior to the
change is made on the table and AFTER if you need to process action after the
change is made. The trigger event can be INSERT, UPDATE or DELETE. This
event causes the trigger to be invoked. A trigger only can be invoked by one
event. To define a trigger that is invoked by multiple events, you have to define
multiple triggers, one for each event. A trigger must be associated with a
specific table. Without a table trigger would not exist therefore you have to
specify the table name after the ON keyword. You place the SQL statements
between BEGIN and END block. This is where you define the logic for the
trigger
Example
mysql> Create table account1(acct_num
int,amount int);Query OK, 0 rows affected
(0.27 sec)

mysql> insert into account1

values(1,150);Query OK, 1
row affected (0.03 sec)
mysql> select * from account1;
+ + +
| acct_num | amount |

Page 39
+ + +
| 1 | 150 |
+ + +

1 row in set (0.00 sec)

Trigger for update
DELIMITER $$
mysql> CREATE DEFINER = CURRENT_USER TRIGGER
`db1`.`new_table_BEFORE_UPDATE` BEFORE UPDATE ON
`account1` FOR EACHROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END$$
Query OK, 0 rows affected (0.18 sec)DELIMITER ;
mysql> update account1 set amount=2000 where acct_num=1;
-> $$
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed:
1 Warnings: 0mysql> select
* from account1;
-> $$
+ + +
| acct_num | amount |
+ + +
| 1 | 100 |
+ + +
1 row in set (0.00 sec)
Trigger for insert
mysql> CREATE DEFINER = CURRENT_USER TRIGGER
`db1`.`bank_BEFORE_INSERT` BEFORE INSERT ON `account1` FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN

Page 40
-> SET NEW.amount = 100;
-> END IF;

-> END
-> $$
Query OK, 0 rows affected
(0.08 sec) mysql> insert into
account1 values(2,-100);
-> $$
Query OK, 1 row affected (0.18 sec)

mysql> select * from account1;

-> $$
+ + +
| acct_num | amount |
+ + +
| 1 | 100 |
| 2| 0|
+ + +
2 rows in set (0.00 sec)
Trigger for Delete
CREATE DEFINER = CURRENT_USER TRIGGER
`db1`.`bank_BEFORE_DELETE`BEFORE DELETE ON `account1`
FOR EACH ROW
BEGIN
delete from account1
where acct_num=2;END
$$ DELIMITER ;
SQL> select * from account1
+ + +
| acct_num | amount |
+ + +
| 1 | 100 |
+ + +
1 row in set (0.00 sec)

Page 41
RESULT:
Thus the programs for insert, delete, and update operations in a database
table usingtriggers is created and executed successful.

Page 42
EX.NO. : 9
Create View and Index for Database Tables with A Large Number
DATE : of Records

AIM:
To create and execute the View and Index for the large database and tables.
VIEWS:
SQL includes the ability to create stored queries that they can then be used as a
basis for other queries. These stored queries are also called views. A view is
simply a derived table that is built upon the base tables of the database. Base
tables are the original database tablesthat actually contain data. Views do not
actually store data they are temporary tables. To define a view, we must give
the view a name and state the query that computes the view.
Syntax:
Create view v-name as <query expression>
Where query expression is any legal query expression and view name is
representedby v-name.( can give any name for view)
mysql> select * from employee;
+ + + + + +
| empno | ename | job | deptno | salary |
+ + + + + +
| 101 | anitha | s/w deve | 1 | 15000 |
| 102 | kumar | clerk | 2 | 10000 |
| 103 | riwat | db admin | 2 | 24000 |
| 104 | anjur | manager | 2 | 40000 |
| 105 | vijay | TL | 3 | 25000 |
+ + + + + +5 rows in set (0.00 sec)

mysql> select * from dept;

+ + + +
| dno | dname | loc |
+ + + +
| 10 | inventory | hyd |
| 20 | finance | bglr |
| 30 | HR | mumbai |
+ + + +

3 rows in set (0.09 sec)

Page 43
CREATING A VIEW:
The first step in creating a view is to define the defining query, which is the
query on whichthe view is based. While it is not required that the defining
query be written before creating a view, it is generally a good idea. Any
errors in the query can be caught and corrected before the view is created.
mysql> create view v1 as select
emp2.eno,emp2.ename,emp2.job, dept.loc fromemp2,dept where
emp2.dno=dept.dno;
Query OK, 0 rows affected (0.53 sec)

mysql> select * from v1;

+ + + + +
| eno | ename | job | loc |
+ + + + +
| 111 | saketh | analyst | hyd |
| 222 | sandeep | clerk | bglr |
| 333 | jagan | manager | hyd |
+ + + + +3 rows in set (0.15 sec)
RENAMING COLUMNS IN A VIEW:
Another useful feature available when creating a view is that columns can
be renamed in the CREATE VIEW statement. The new column names only
apply to the views, the columnnames in the base tables do not change.
mysql> create view v2(department_name,location) as select
dname,loc from dept;Query OK, 0 rows affected (0.07 sec)

mysql> select * from v2;

+ + +
| department_name | location |
+ + +
| inventory | hyd |
| finance | bglr |
| HR | mumbai |
+ + +3 rows in set (0.00 sec)
WITH CHECK OPTION CLAUSE
mysql> create or replace view v3 as select dno,dname from dept
where dno=30 withcheck option;

Query OK, 0 rows

affected (0.05 sec)

Page 44
mysql> select * from v3;
+ + +
| dno | dname |
+ + +
| 30 | HR |
+ + +
1 row in set (0.09 sec)
Index
An index is a schema object. It is used by the server to speed up the
retrieval of rows by using a pointer. It can reduce disk I/O(input/output) by
using a rapid path access method to locate data quickly. An index helps to
speed up select queries and where clauses, but it slowsdown data input, with
the update and the insert statements. Indexes can be created or dropped with
no effect on the data. In this article, we will see how to create, delete, and
usesthe INDEX in the database.
Syntax
CREATE INDEX index_name ON table_name (column1, column2, ...);
Example
mysql> select * from sstud2;
+ + + +
| sname | dept | marks |
+ + + +
| prajan | cse | 700 |
| anand | it | 650 |
| vasu | cse | 680 |
| ravi | it | 600 |
+

+4 rows
in set
(0.03
sec)

Page 45
mysql> create index i1 on
sstud2(sname,dept);Query OK,
0 rows affected (0.61 sec)
Records: 0 Duplicates: 0
Warnings: 0

mysql> select * from i1;

ERROR 1146 (42S02): Table
'db1.i1' doesn't existmysql> show
index from sstud2;
+ + + + + + + +
+
+ + + + + + +
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality
| Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
Expression |
+ + + + + + + +
+
+ + + + + + +
| sstud2 | 1 | i1 | 1 | sname |A | 4| NULL | NULL | YES
| BTREE | | | YES | NULL |
| sstud2 | 1 | i1 | 2 | dept |A | 4| NULL | NULL | YES |
BTREE | | | YES | NULL |
+ + + + + + + +
+
+ + + + + + +2 rows in set (0.12 sec)
Unique Indexes:
Unique indexes are used for the maintenance of the integrity of the data
present in the table as well as for fast performance, it does not allow
multiple values to enter into thetable.
Syntax:
CREATE UNIQUE INDEX index ON
TABLE column; mysql> create unique index
i2 on employee(empno,ename);Query OK, 0
rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from employee;

+ + + + + + + +
+ + + + + + + +

Page 46
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
Index_comment | Visible |Expression |
+ + + + + + + +
+ + + + + + + +
| employee | 0 | i2 | 1 | empno |A | 5| NULL | NULL |
YES | BTREE | | | YES | NULL |
| employee | 0 | i2 | 2 | ename |A | 5| NULL | NULL |
YES | BTREE | | | YES | NULL |
+ + + + + + + +
+ + + + + + + +2 rows in set (0.00 sec)
Delete index
Remove an index from the data dictionary by using the DROP INDEX command.
Syntax:
ALTER TABLE TABLE_NAME DROP INDEX index_name;

mysql> alter table employee

drop index i2;

Query OK, 0 rows affected

(0.07 sec) Records: 0
Duplicates: 0 Warnings: 0

mysql> show index

from employee;
Empty set (0.00 sec)

RESULT:
Thus the view and index for a large database and table is created and executed
successfully.

Page 47
EX.NO. : 10

DATE : Create An XML Database and Validate It Using XML Schema

AIM:
Creating XML database and validate it using XML schema.
Procedure XML
 Xml (eXtensible Markup Language) is a mark up language.
 XML is designed to store and transport data.
 Xml was released in late 90’s. it was created to provide an easy to
use and storeself describing data.
 XML became a W3C Recommendation on February 10, 1998.
 XML is not a replacement for HTML.
 XML is designed to be self-descriptive.
 XML is designed to carry data, not to display data.
 XML tags are not predefined. You must define your own tags.
 XML is platform independent and language independent. XML Schema
XML Schema is commonly known as XML Schema Definition (XSD). It is used
to describe and validate the structure and the content of XML data. XML
schema defines the elements, attributes and data types. Schema element
supports Namespaces. It is similar to a databaseschema that describes the data
in a database. How to validate XML against XSD in java:
Java XML Validation API can be used to validate XML against an XSD.
javax.xml.validation.Validator class is used in this program to validate xml file
against xsdfile.Here are the sample XSD and XML files used.
Employee.xsd
<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.journaldev.com/Employee"
xmlns:empns="http://www.journaldev.com/Employee"
elementFormDefault="qualified">
<element name="empRequest" type="empns:empRequest"></element>
<element name="empResponse" type="empns:empResponse"></element>
<complexType name="empRequest">
<sequence>
<element name="id" type="int"></element>
</sequence>
</complexType>
<complexType name="empResponse">

Page 48
<sequence>
<element name="id" type="int"></element>
<element name="role" type="string"></elemen>
<element name="fullName" type="string"></element>
</sequence>
</complexType>
</schema>
Notice that above XSD contains two root element and namespace also, I
have created twosample XML
file from XSD. Employee Request.xml
<?xml version="1.0" encoding="UTF-8"?>
<empns:empRequest
xmlns:empns="http://www.journaldev.com/Employee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.journaldev.com/Employee
Employee.xsd ">
EmployeeResponse.xml
<?xml version="1.0" encoding="UTF-8"?>
<empns:empResponse
xmlns:empns="http://www.journaldev.com/Employee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.journaldev.com/Employee
Employee.xsd ">
<empns:id>1</empns:id>
<empns:role>Developer</empns:role>
<empns:fullName>Pankaj Kumar</empns:fullName>
</empns:empResponse>
Here is another XML file that doesn’t confirms to the
Employee.xsdemployee.xml
<?xml version="1.0"?>
<Employee>
<name>Pankaj</name>
<age>29</age>
<role>Java Developer</role>
<gender>Male</gender>
</Employee>
Here is the program that is used to validate all three XML files against the XSD. The
validateXMLSchema method takes XSD and XML file as argument
and return true ifvalidation is
successful or else returns false.

Page 49
XMLValidatio
n.java package
com.journalde
v.xml;import
java.io.File;
import
java.io.IOExceptio
n; import
javax.xml.XMLCo
nstants;
import
javax.xml.transform.stream.Stream
Source;import
javax.xml.validation.Schema;
import
javax.xml.validation.Schema
Factory;import
javax.xml.validation.Validato
r; import
org.xml.sax.SAXException;
public class XMLValidation {
public static void main(String[] args) {
System.out.println("EmployeeRequest.xml validates against
Employee.xsd? "+validateXMLSchema("Employee.xsd",
"EmployeeRequest.xml"));
System.out.println("EmployeeResponse.xml validates against
Employee.xsd?"+validateXMLSchema("Employee.xsd",
"EmployeeResponse.xml"));
System.out.println("employee.xml validates against
Employee.xsd? "+validateXMLSchema("Employee.xsd",
"employee.xml"));
}
public static boolean validateXMLSchema(String xsdPath,
String xmlPath){try {
SchemaFactory factory =
SchemaFactory.newInstance(XMLConstants.W3C_XML_SCHEMA
_NS_URI); Schema schema = factory.newSchema(new

Page 50
File(xsdPath));
Validator validator =
schema.newValidator();
validator.validate(new StreamSource(new
File(xmlPath)));
} catch (IOException |
SAXException e) {
System.out.println("Exception:
"+e.getMessage());return false;
}
return true;
}
}
Output of the above program is:
EmployeeRequest.xml validates against Employee.xsd? true

EmployeeResponse.xml validates against Employee.xsd? true

Exception: cvc-elt.1: Cannot find the declaration of element 'Employee'.
employee.xmlvalidates against Employee.xsd? false

RESULT:
Thus the XML database created and validates it using XML schema

Page 51
EX.NO. : 11
Create Document, Column and Graph Based Data Using NOSQL
DATE : DatabaseTools.

AIM:
To create the document, columns and graphs based on data by using NoSQL tools.
Procedure :
Document database
A document database (also known as a document-oriented database or a
document store) is a database that stores information in documents. A
document is a record in a document database. A document typically stores
information about one object and any of its related metadata. Documents store
data in field-value pairs. The values can be a variety of types and structures,
including strings, numbers, dates, arrays, or objects. Documents can be stored
in formats like JSON, BSON, and XML.
Below is a JSON document that stores information about a user named Tom. {
“_id”: 1, “first_name”: “Tom”, “email”: “[email protected]”, “cell”: “765-
555-5555”,“likes”: [ “fashion”, “spas”, “shopping”
],”businesses”: [
{
“name”: “Entertainment 1080”, “partner”: “Jean”, “status”: “Bankrupt”, “date_founded”:
{
“$date”: “2012-05-19T04:00:00Z” }
}, {
“name”: “Swag for Tweens”,
“date_founded”: {“$date”: “2012-
11-01T04:00:00Z” }
}
]
}
Collections
A collection is a group of documents. Collections typically store documents
that have similar contents. Continuing with the example above, the
document with information about Tom could be stored in a collection named
users. More documents could be addedto the users collection in order to
store information about other users. For example, the document below that
stores information about Donna could be added to the users collection.
{
“_id”: 2, “first_name”: “Donna”, “email”: “[email protected]”,
“spouse”: “Joe”,“likes”: [ “spas”, “shopping”, “live tweeting”
],”businesses”: [

Page 52
{
“name”: “Castle Realty”, “status”: “Thriving”,
“date_founded”: {“$date”: “2013-11-
21T04:00:00Z” }
}
]
}
Columnar Data Model of NoSQL :
In Columnar Data Model instead of organizing information into rows, it
does in columns. This makesthem function the same way that tables work
in relational databases. This type of data model is much more flexible
obviously because it is a type of NoSQL database.
The below example will help in understanding the
Columnar data model:Row-Oriented Table:
S.No. Name Course Branch ID
01. Tanmay B-Tech Computer 2
02. Abhishek B-Tech Electronics 5
03. Samriddha
B-Tech IT
S.No. Name
Course Branch
ID
04. Aditi B-
Tech E &
TC 8
Column –
Oriented
Table:
S.No. Name ID
01. Tanmay 2
02. Abhishek 5
03. Samriddha 7
04. Aditi 8
Graph Database on NoSQL:
Graph Based Data Model in NoSQL is a type of Data Model which tries to
focus on buildingthe relationship between data elements.
As the name suggests Graph-Based Data Model, each element here is stored
as a node, and the association between these elements is often known as
Links.
Page 53
Association is stored directly as these are the first-class elements of the data
model. These data models give us a conceptual view of the data.
These are the data models which are based on topographical network
structure. Obviously, in graph theory, we have terms like Nodes, edges, and
properties, let’s see what it means here in the Graph-Based data model.

Nodes: These are the instances of data that represent objects which
is to be tracked.Edges: As we already know edges represent
relationships between nodes.
Properties: It represents information associated with nodes. The below
image representsNodes with properties from relationships representation

RESULT:
Thus we studied the various NoSQL database tools to create document,
column and graphsuccessfully

Page 54
EX.NO. : 12

DATE : Simple GUI Application

AIM:
Write a program in Java to create Displaying login page and home page using
Jframe andDatabases (three tier architecture).
Procedure:
Three tier architecture is a very common architecture. A three tier architecture
is typicallysplit into a presentation or GUI tier, an application logic tier, and a
data tier. Presentation tier encapsulates the presentation logic required to
serve clients.
Program:
Login.java package loginpage;
/**** @author 91875*/
public class LoginPage {

/*** @param args the command line arguments */

public static void main(String[] args)
{
// TODO code
application logic here
Login log=new Login();
log.show();
}

}
LoginPage.java:
package loginpage;
import com.mysql.jdbc.Connection;
import static java.lang.Class.forName;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

Page 55
/**** @author 91875*/
public class Login extends javax.swing.JFrame {
/*** Creates new form Login*/
Public Login() {
initComponents();
}

/**This method is called from within the constructor to initialize the form.
* WARNING: Do NOT modify this code. The content of this method is always
* regenerated by the Form Editor.*/
* @SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed"
desc="Generated Code">private void
initComponents() {

jLabel1 = new
javax.swing.JLabel();
jLabel2 = new
javax.swing.JLabel();
edtusername = new
javax.swing.JTextField();
edtpassword = new
javax.swing.JPasswordField();
btnlogin = new
javax.swing.JButton();
btnreset = new javax.swing.JButton();
setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

jLabel1.setText("username");

jLabel2.setText("password");

edtusername.addActionListener(new
java.awt.event.ActionListener() {public void
actionPerformed(java.awt.event.ActionEvent evt) {
edtusernameActionPerformed(evt);
}
});

Page 56
edtpassword.addActionListener(new
java.awt.event.ActionListener() {public void
actionPerformed(java.awt.event.ActionEvent evt) {
edtpasswordActionPerformed(evt);
}
});

btnlogin.setText("login");
btnlogin.addActionListener(new
java.awt.event.ActionListener() {public void
actionPerformed(java.awt.event.ActionEvent evt)
{
btnloginActionPerformed(evt);
}
});

btnreset.setText("reset");
btnreset.addActionListener(new
java.awt.event.ActionListener() {public void
actionPerformed(java.awt.event.ActionEvent
evt) {
btnresetActionPerformed(evt);
}
});

javax.swing.GroupLayout layout = new

javax.swing.GroupLayout(getContentPane());

getContentPane().setLayout(layout);
layout.setHorizontalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(57, 57, 57)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
.addComponent(jLabel2)
.addComponent(jLabel1))

Page 57
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.R
ELATED,
85,Short.MAX_VALUE)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(javax.swing.GroupLayout.Alignme
nt.TRAILING,layout.createSequentialGroup()
.addComponent(btnreset)
.addGap(78, 78, 78))
.addGroup(layout.createSequentialGroup()

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
.addComponent(edtusername,
javax.swing.GroupLayout.PREFERRED_SIZE,135,
javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(edtpassword,
javax.swing.GroupLayout.PREFERRED_SIZE,135,
javax.swing.GroupLayout.PREFERRED_SIZE))
.addGap(66, 66, 66))))
.addGroup(layout.createSequentialGroup()
.addGap(87, 87, 87)
.addComponent(btnlogin)
.addGap(0, 0, Short.MAX_VALUE))
);
layout.setVerticalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignme
nt.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(68, 68, 68)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jLabel1)
.addComponent(edtusername,javax.swing.GroupLayout.
PREFERRED_SIZE,javax.swing.GroupLayout.DEFAULT_SIZE,
javax.swing.GroupLayout.PREFERRED_SIZE))
.addGap(63, 63, 63)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jLabel2)
.addComponent(edtpassword,javax.swing.GroupLayout

Page 58
.PREFERRED_SIZE,30, javax.swing.GroupLayout.PREFERRED_SIZE)) .addGap(41, 41,
41)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(btnlogin)
.addComponent(btnreset))
.addContainerGap(51, Short.MAX_VALUE))
);

pack();
}// </editor-fold>

private void edtusernameActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your handling code here:
}

private void edtpasswordActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your handling code here:
}

private void btnresetActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your
handling code here:
edtusername.setText("")
;
edtpassword.setText("")
;

private void btnloginActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your handling code here:
try
{
Class.
forName("com.mysql.jdbc.Driver");String
driver ="com.mysql.jdbc.Driver";
Class.forName(driver);
try (Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:3306
/mydb?useSSL=false","root","m ysql@123")) {

Page 59
String
username=edtusername.g
etText(); String
password=edtpassword.ge
tText(); Statement
stm=con.createStatement(
);
String sql="select * from login where
username='"+username+"' andpassword='"+password+"'";
ResultSet
rs=stm.executeQuery(
sql);if(rs.next())
{
dispose();
homepage
hpage=new
homepage();
hpage.show();
}
else
{
JOptionPane.showMessageDialog(this,"username or
password wrong...");edtusername.setText("");
edtpassword.setText("");
}
}
}
catch(ClassNotFoundException | SQLException e)
{
System.out.println(e.getMessage());
}

/**
* @param args the command line arguments*/

public static void main(String args[]) {

/* Set the Nimbus look and feel */

Page 60
//<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional)
">
/* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and

feel.
* For details see
http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html
*
t
r
y

{
for
(javax.swing.UIManager.LookAndFeelInfo
info :
javax.swing.UIManager.getInstalledLookAnd
Feels()) {
if ("Nimbus".equals(info.getName())) {
javax.swing.UIManager.setLookAndFeel(info.getClass
Name()); break;
}
}
} catch (ClassNotFoundException ex) {

java.util.logging.Logger.getLogger(Login.class.getName()).log(java.util.loggin
g.Level.SEVE RE, null, ex);
} catch (InstantiationException ex) {

java.util.logging.Logger.getLogger(Login.class.getName()).log(java.util.loggin
g.Level.SEVE RE, null, ex);
} catch (IllegalAccessException ex) {

java.util.logging.Logger.getLogger(Login.class.getName()).log(java.util.loggin
g.Level.SEVE RE, null, ex);
} catch (javax.swing.UnsupportedLookAndFeelException ex) {

java.util.logging.Logger.getLogger(Login.class.getName()).log(java.util.loggin
g.Level.SEVE RE, null, ex);
}
//</editor-fold>
/* Create and display the form */

Page 61
java.awt.EventQueue.invokeLater(() -> {
new Login().setVisible(true);
});
}

// Variables declaration -
do not modifyprivate
javax.swing.JButton
btnlogin; private
javax.swing.JButton
btnreset;
private
javax.swing.JPasswordField
edtpassword;private
javax.swing.JTextField
edtusername; private
javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
// End of variables declaration
}
Homepage.java package
loginpage;

/**
*
* @author 91875
*/
public class homepage extends javax.swing.JFrame {

/**
* Creates new form homepage
*/

public homepage() {
initComponents();
}

Page 62
/**
* This method is called from within the constructor to initialize the form.
* WARNING: Do NOT modify this code. The content of this method is always
* regenerated by the Form Editor.
*/
@SuppressWarnings
("unchecked")
// <editor-fold defaultstate="collapsed"
desc="Generated Code">private void
initComponents() {

jTextField1 = new javax.swing.JTextField();

setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

jTextField1.setText("home page");

javax.swing.GroupLayout layout = new

javax.swing.GroupLayout(getContentPane());
getContentPane().setLayout(layout);
layout.setHorizontalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(javax.swing.GroupLayout.Alignme
nt.TRAILING,layout.createSequentialGroup()
.addContainerGap(131, Short.MAX_VALUE)
.addComponent(jTextField1,
javax.swing.GroupLayout.PREFERRED_SIZE, 140,
javax.swing.GroupLayout.PREFERRED_SIZE)
.addGap(129, 129, 129))
);
layout.setVerticalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(101, 101, 101)
.addComponent(jTextField1,
javax.swing.GroupLayout.PREFERRED_SIZE, 52,

Page 63
javax.swing.GroupLayout.PREFERRED_SIZE)
.addContainerGap(147, Short.MAX_VALUE))
);

pack();
}// </editor-fold>

/**
* @param args the command line arguments
*/
public static void main(String args[]) {
/* Set the Nimbus look and feel */
//<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional)
">
/* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and
feel.
* For details see
http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html
*

t
r
y

{
for
(javax.swing.UIManager.LookAndFeelInfo
info :
javax.swing.UIManager.getInstalledLookAnd
Feels()) {
if ("Nimbus".equals(info.getName())) {
javax.swing.UIManager.setLookAndFeel(info.getClass
Name()); break;
}
}
} catch (ClassNotFoundException ex) {

java.util.logging.Logger.getLogger(homepage.class.getName()).log(java.util.lo
gging.Level.S EVERE, null, ex);
} catch (InstantiationException ex) {

Page 64
java.util.logging.Logger.getLogger(homepage.class.getName()).log(java.util.lo
gging.Level.S EVERE, null, ex);
} catch (IllegalAccessException ex) {

java.util.logging.Logger.getLogger(homepage.class.getName()).log(java.util.lo
gging.Level.S EVERE, null, ex);
} catch (javax.swing.UnsupportedLookAndFeelException ex) {

java.util.logging.Logger.getLogger(homepage.class.getName()).log(java.util.lo
gging.Level.S EVERE, null, ex);
}
//</editor-fold>

/* Create and display the form */

java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new homepage().setVisible(true);
}
});
}

// Variables declaration - do
not modify private
javax.swing.JTextField
jTextField1;
// End of variables declaration
}

Page 65
JFrame(homepage)

JFrame(loginpage)

Page 66
OUTPUT:

RESULT:
Simple GUI Database application and incorporate is Developed Successfully.

Page 67
EX.NO. : 13

DATE : Case Study for Inventory Management for A Emart Grocery Shop

PROBLEM STATEMENT:
INVENTORY MANAGEMENT SYSTEM is a real time application used in the merchant’s
day to day system. this is a database to store the transaction that takes places between the
manufacturer, dealer and the shop keeper that includes stock inward and stock outward with
reference to the dealer. here we assume our self as the dealer and proceed with the transaction
as follows:
The manufacturer is the producer of the items and it contains the necessary information of the
item such as price per item, date of manufacture, best before use, number of item available and
their company address. the dealer is the secondary source of an item and he purchases item
from the manufacturer by requesting the required item with its corresponding company name
and the number of items required. the dealer is only responsible for distribution of the item to
the retailers in the town or city. the shop keeper or retailer is the one who is prime source for
selling items in the market. the customers get item from the shop keeper and not directly
from the manufacturer or the dealer. the stock is the database used in our system which
records all transactions that takes place between the manufacturer and the dealer and the
dealer and the retailer.

ENTITY RELATIONSHIP DIAGRAM:

Page 68
CODING:
FORM1
Dim db As DatabaseDim rs As
Recordset
Private Sub Command1_Click()
Form3.Show
End Sub
Private Sub
Command2_Clic
k()Form4.Show
End Sub
Private Sub
Command3_Clic
k()Form5.Show
End Sub
Private Sub
Command4_Clic
k()End
End Sub
Private Sub Form_Load()
Set db = OpenDatabase("D:\prj789\invent\INVENTORY.MDB")

Set rs = db.OpenRecordset("SYSTEM")
End SubFORM2
Dim db As Database
Dim rs As Recordset
Dim i As Integer

Private Sub Command1_Click()Form1.Show

End Sub
Private Sub
Command2_Click()
rs.MoveFirst
For i = 1 To rs.RecordCount
If rs(0) = Text1.Text Then rs.Edit
If Text7.Text = "" Then
MsgBox "enter the no of items ordered" Else

Page 69
rs(6) = Text7.Text rs(7) = rs(5) * rs(6)
rs(4) = rs(4) + Val(Text7.Text)Text8.Text = rs(7)

Text5.Text = rs(4)Text4.Text = rs(3)rs.Update

GoTo l1End If
End If
rs.Move
NextNext
i
l1: End Sub

Private Sub Command3_Click()

Text1.Text = "" Text2.Text = "" Text3.Text = "" Text4.Text = "" Text5.Text = ""
Text6.Text
= "" Text7.Text = ""
Text8.Text = "" End Sub
Private Sub
Command4_Click()
rs.AddNewrs(0) = Text1.Textrs(1) = Text2.Textrs(2) = Text3.Textrs(3) = Text4.Textrs(4)
= Text5.Textrs(5) = Text6.Textrs(6) = Text7.Textrs(7) = Text8.Textrs.Update
End Sub

Private Sub Form_Load()

Set db =
OpenDatabase("D:\prj789\invent\INVENTORY.
MDB")Set rs = db.OpenRecordset("SYSTEM")
End Sub

Private Sub List1_Click()

Text1.Text = List1.Text
rs.MoveFirst
For i = 1 To rs.RecordCount
If rs(0) = Text1.Text Then
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)

Page 70
Text5.Text = rs(4)
Text6.Text = rs(5)
Text7.Text = ""
Text8.Text = "" End If
rs.MoveNext

Next i
End SubFORM3
Dim db As DatabaseDim rs As RecordsetDim i As Integer
Private Sub Command1_Click()
rs.MoveFirst
For i = 1 To
rs.RecordCou
ntIf rs(0) =
Text1.Text
Then rs.Edit
If Text4.Text = "" Then
MsgBox "Enter the no of
items needed" Elsers(6) =
Text4.Text
If rs(6) <= rs(4) Thenrs(7) = rs(5) * rs(6)
rs(4) = rs(4) - Val(Text4.Text)Text2.Text
= rs(4)
Text5.Text = rs(7)Else
MsgBox " ITEM NOT SUFFICIENT" End If
rs.UpdateGoTo l1 End If
End If
rs.MoveNext
Next i
l1: End Sub
Private Sub Command2_Click()
Form1.Show
End Sub
Private Sub Command3_Click()
Text1.Text = "" Text2.Text = "" Text3.Text = "" Text4.Text = ""
Text5.Text = "" End SubPrivate Sub Form_Load()
Set db = OpenDatabase("D:\prj789\invent\INVENTORY.MDB")
Page 71
Set rs = db.OpenRecordset("SYSTEM")
End Sub
Private Sub List2_Click()
Text1.Text = List2.Text
rs.MoveFirst
For i = 1 To rs.RecordCountIf rs(0)
= Text1.Text Then Text2.Text =
rs(4) Text3.Text = rs(5)
Text4.Text = ""
Text5.Text = "" End If
rs.MoveNext
Next i End SubFORM4
Dim db As Database Dim rs As Recordset Dim
r, i As Integer
Private Sub Command1_Click()
Form1.Show
End Sub
Private Sub Form_Load()
Set db =
OpenDatabase("D:\prj789\invent\INVENTORY.
MDB")Set rs = db.OpenRecordset("SYSTEM")
MSFlexGrid1.FixedRows = 0
MSFlexGrid1.FixedCols = 0
r=0
MSFlexGrid1.ColWidth(0) = 2000
MSFlexGrid1.ColWidth(1) = 2000
MSFlexGrid1.ColWidth(2) = 2000
MSFlexGrid1.ColWidth(3) = 1700
MSFlexGrid1.ColWidth(4) = 1750
MSFlexGrid1.ColWidth(5) = 1650
'MSFlexGrid1.ForeColor = "GREEN" MSFlexGrid1.TextMatrix(0, 0)
= "COMPANYNAME" MSFlexGrid1.TextMatrix(0, 1) =
"COMPANY ADDRESS"
MSFlexGrid1.TextMatrix(0, 2) = "CONTACT NUMBER"
MSFlexGrid1.TextMatrix(0, 3) ="DATE OF ORDER"
MSFlexGrid1.TextMatrix(0, 4) = "ITEMS AVAILABLE"
MSFlexGrid1.TextMatrix(0, 5) =
"PRICE/ITEM"rs.MoveFirst
r=1

Page 72
Do Until rs.EOF MSFlexGrid1.FixedRows = r
MSFlexGrid1.FixedCols = 0

MSFlexGrid1.Text = rs(0)

MSFlexGrid1.FixedRows = r

MSFlexGrid1.FixedCols = 1

MSFlexGrid1.Text = rs(1)

MSFlexGrid1.FixedRows = r

MSFlexGrid1.FixedCols = 2

MSFlexGrid1.Text = rs(2)

MSFlexGrid1.FixedRows = r

MSFlexGrid1.FixedCols = 3

MSFlexGrid1.Text = rs(3)

MSFlexGrid1.FixedRows = r

MSFlexGrid1.FixedCols = 4

MSFlexGrid1.Text = rs(4)

MSFlexGrid1.FixedRows = r

MSFlexGrid1.FixedCols = 5

MSFlexGrid1.Text = rs(5)

MSFlexGrid1.FixedRows = r

MSFlexGrid1.FixedCols = 6

'MSFlexGrid1.Text = rs(6)

'MSFlexGrid1.FixedRows = r
'MSFlexGrid1.FixedCols = 7
'MSFlexGrid1.Text = rs(7)
r=r+1
rs.Move
Next
Loop
End Sub

Page 73
FORMS:

FORM1 : MAIN MENU

FORM2 : PURCHASE DETAILS

Page 74
FORM3 : SALES DETAILS

FORM4 :STOCK DETAILS

Page 75