Ex.

no: 1 Create a database table, add constraints (primary key, unique, check, not null)
Date: 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 DDL
are

▪ CREATE
▪ ALTER
▪ DROP
▪ TRUNCATE
▪ RENAME
Create Table:

This command is used to create database tables in RDBMS.

Syntax

CREATE TABLE table_name (colname1 datatype, colname2

datatype,colname3 datatype,…colname 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:

Aalim Muhammed Salegh College of Engineering 1|Page

ALTER TABLE <table_name> DROP COLUMN column_name;

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 not
destroy the table's structure. When we use TRUNCATE command on a table its (auto-
increment) primary key is also initialized.

Following is its syntax,

TRUNCATE TABLE table_name

RENAME

RENAME command is used to set a new name for any existing table.

Following is the syntax,

RENAME TABLE old_table_name to new_table_name

DROP

This command is used to remove a relation from an SQL database. This command
deletes notonly 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 insert
a recordinto the database.

Syntax:

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

Aalim Muhammed Salegh College of Engineering 2|Page

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. They
are:

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;

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 WHERE

column 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;

COMMANDS

Aalim Muhammed Salegh College of Engineering 3|Page

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

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

Aalim Muhammed Salegh College of Engineering 4|Page

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 are
working 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”;

Aalim Muhammed Salegh College of Engineering 5|Page

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;

Aalim Muhammed Salegh College of Engineering 6|Page

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 the
database has been implemented and the output was verified.

Aalim Muhammed Salegh College of Engineering 7|Page

Ex.no: 2
Create a set of tables, add foreign key constraints and incorporate referential
Date: 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 PRIMARY
KEY 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 as

primary key)

CREATE TABLE Persons (ID int, LastName varchar(255) NOT NULL, FirstName
varchar(255), Age int, PRIMARY KEY (ID));

2. create a FOREIGN KEY on the "PersonID" column when the "Orders" table is
created

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

Aalim Muhammed Salegh College of Engineering 8|Page

5. Select * from Orders

RESULT:

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

Aalim Muhammed Salegh College of Engineering 9|Page

Ex.no: 3 Query the database tables using different ‘where’ clause conditions and also
Date: implement aggregate functions

AIM:

To create a database table using different “where” clause conditions and also implement
aggregate 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;

Aalim Muhammed Salegh College of Engineering 10 | P a g e

ORDER BY

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

The ORDER BY keyword sorts the records in ascending order by default. To sort the records
in 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 table
Syntax:
DELETE FROM table_name WHERE condition;

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

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

The underscore sign (_) represents one, single character

Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE columnN LIKE pattern;

Aalim Muhammed Salegh College of Engineering 11 | P a g e

AGGREGATE FUNCTIONS:
1. MIN( )
2. MAX( )
3. AVG( )
4. SUM( )
5. AVG( )

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 criterion

The AVG() function returns the average value of a numeric column

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

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

Aalim Muhammed Salegh College of Engineering 12 | P a g e

HAVING

The HAVING clause was added to SQL because the WHERE keyword cannot be used with
aggregate 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 start
with “a”
Select * from employee where ename not like “a%”
4. Display the rows whose salary ranges from 15000 to 30000

Aalim Muhammed Salegh College of Engineering 13 | P a g e

Select * from employee where salary between 15000 and 30000;

5. Calculate total and average salary amount of the employee table

Select sum(salary), avg(salary) from employee;
6. Determine the max and min salary and rename the column as maximum
salary and minimum_salary
Select max(salary) as maximum_salary, min(salary) as minimum_salary from
employee;
7. 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
implement aggregate functions.

Aalim Muhammed Salegh College of Engineering 14 | P a g e

Ex.no: 4 Query the database tables and explore sub queries and simple join operations
Date:

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 Rule:

o A subquery can be placed in a number of SQL clauses like WHERE clause, FROM
clause, HAVING clause.
o You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along
with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
o A subquery is a query within another query. The outer query is known as the main
query, 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 command
can 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. They
are

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;

Aalim Muhammed Salegh College of Engineering 15 | P a g e

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 it
is 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
WHERE Column 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:

Aalim Muhammed Salegh College of Engineering 16 | P a g e

We can combine one or more conditions in a SELECT statement using the logical
operators 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 relationship
between certain columns in these tables.

• INNER JOIN/ NATURAL JOIN/ JOIN: It is a binary operation that allows us to

combine 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,'HR','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 |

Aalim Muhammed Salegh College of Engineering 17 | P a g e

+------+-----------+--------+
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)
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 |

Aalim Muhammed Salegh College of Engineering 18 | P a g e

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

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 |

Aalim Muhammed Salegh College of Engineering 19 | P a g e

| 444 | madhu | engineer | inventory | hyd |
+------+---------+----------+-----------+--------+
4 rows in set (0.03 sec)
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 |

Aalim Muhammed Salegh College of Engineering 20 | P a g e

| 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 from
sstud2 );

+--------+

| sname |

+--------+

| prajan |

| anand |

| ravi |

+--------+

3 rows in set (0.15 sec)

mysql> select sname from sstud1 where sstud1.sname not in ( select sstud2.sname from
sstud2 );

+-------+

| sname |

+-------+

| kumar |

+-------+

1 row in set (0.06 sec)

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

+--------+

Aalim Muhammed Salegh College of Engineering 21 | P a g e

| sname |

+--------+

| prajan |

+--------+

1 row in set (0.01 sec)

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

+--------+

| sname |

+--------+

| prajan |

| vasu |

+--------+

2 rows in set (0.00 sec)

mysql> select sname from sstud2 where marks > any ( select marks from sstud2 where
dept='cse' );

+--------+

| sname |

+--------+

| prajan |

+--------+

1 row in set (0.00 sec)

mysql> select sname from sstud2 where marks >= any ( select marks from sstud2 where
dept='cse' );

+--------+

Aalim Muhammed Salegh College of Engineering 22 | P a g e

| sname |

+--------+

| prajan |

| vasu |

+--------+

2 rows in set (0.00 sec)

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

Empty set (0.00 sec)

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

+-------+

| sname |

+-------+

| anand |

| ravi |

+-------+

2 rows in set (0.00 sec)

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

+--------+

| sname |

+--------+

| prajan |

Aalim Muhammed Salegh College of Engineering 23 | P a g e

| anand |

| ravi |

+--------+

3 rows in set (0.00 sec)

mysql> select sname from sstud1 where not exists ( select sstud2.sname from sstud2
where 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.

Aalim Muhammed Salegh College of Engineering 24 | P a g e

Ex.no: 5 Query the database tables and explore natural, equi and outer joins
Date:

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 attributes
from 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 any
tuple in the left relation, pads the tuples with null values for all other attributes
from 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 result
of 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
mysql> select * from dept;
+------+-----------+--------+
| dno | dname | loc |
+------+-----------+--------+

Aalim Muhammed Salegh College of Engineering 25 | P a g e

| 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)
mysql> select eno,ename,job,dname,loc from emp2 e,dept d where e.dno=d.dno;
+------+---------+---------+-----------+------+

Aalim Muhammed Salegh College of Engineering 26 | P a g e

| 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;
+------+---------+----------+-----------+--------+
| eno | ename | job | dname | loc |
+------+---------+----------+-----------+--------+
| 222 | sandeep | clerk | inventory | hyd |

Aalim Muhammed Salegh College of Engineering 27 | P a g e

| 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 |
| 444 | madhu | engineer | HR | mumbai |
| 444 | madhu | engineer | finance | bglr |
| 444 | madhu | engineer | inventory | hyd |

Aalim Muhammed Salegh College of Engineering 28 | P a g e

+------+---------+----------+-----------+--------+
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 side
table those that are in right hand side table.
mysql> select eno,ename,job,dname,loc from emp2 e left outer 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 |
| 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 hand
side table those that are not in left hand side table.
mysql> select eno,ename,job,dname,loc from emp2 e right outer join dept d
on(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)
RESULT:
Thus, the database tables are created and explore natural join, equi join and outer joins.

Aalim Muhammed Salegh College of Engineering 29 | P a g e

Ex.no: 6 Write user defined functions and stored procedures in SQL
Date:

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 stored
function 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 strings
USE `sample1`;
DROP function IF EXISTS `funcon`;
DELIMITER $$
USE `sample1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `funcon`(s CHAR(20)) RETURNS
char(50)
CHARSET utf8mb4
DETERMINISTIC
BEGIN
RETURN CONCAT('Hello, ',s,'!!');
RETURN 1;
END$$
DELIMITER ;
Executing function
select funcon('world');
# funcon('world')
'Hello, world!!’

Aalim Muhammed Salegh College of Engineering 30 | P a g e

Stored procedure
MySQL stored procedure using CREATE PROCEDURE statement. In addition, we will
show 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,"john",20,"null");
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> 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;
-> @@
Query OK, 0 rows affected (0.08 sec)

Aalim Muhammed Salegh College of Engineering 31 | P a g e

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.

Aalim Muhammed Salegh College of Engineering 32 | P a g e

Ex.no: 7 Execute complex transactions and realize DCL and TCL commands
Date:

AIM:
To study and execute various Data Control Language and Transaction Control Language
commands in SQL.
Procedure:
1: Start
2: Create the table with its essential attributes. 3: Insert the record into table
4: 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 the
rights, permissions, and other controls of the database system.
GRANT:
This command gives users access privileges to the database. For this first we have to create
user.
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 connecting
through 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 user
Parameters Used in Grant Command
privileges_name: These are the access rights or privileges granted to the user. object:It is
the name of the database object to which permissions are being granted. In the case of
granting privileges on a table, this would be the 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

Aalim Muhammed Salegh College of Engineering 33 | P a g e

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, UPDATE
ON 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 Grant
statement should be executed. GRANT ALL ON Users TO a@localhost;
4. SQL Grant command is specifically used to provide privileges to database objects for a
user. 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. It
does 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 |
+------------+------+----------------+
| IR | 1| 3400 |
| KWM | 2| 4000 |
| PRT | 4| 5789 |
+------------+------+----------------+

Aalim Muhammed Salegh College of Engineering 34 | P a g e

3 rows in set (0.00 sec)
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 that
point 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

mysql> select * from schooldetails;

+------------+------+----------------+
| schoolname | id | no_of_students |
+------------+------+----------------+
| IR | 1| 3400 |
| KWM | 2| 4000 |

Aalim Muhammed Salegh College of Engineering 35 | P a g e

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

mysql> select * from schooldetails;

+------------+------+----------------+
| schoolname | id | no_of_students |
+------------+------+----------------+
| IR | 1| 3400 |

Aalim Muhammed Salegh College of Engineering 36 | P a g e

| 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.

Aalim Muhammed Salegh College of Engineering 37 | P a g e

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 EACH
ROW
BEGIN
... END;
Let’s examine the syntax above in more detail.
You put the trigger name after the CREATE TRIGGER statement. The trigger name should
follow 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 you
want 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 |
+----------+--------+
| 1 | 150 |
+----------+--------+

Aalim Muhammed Salegh College of Engineering 38 | P a g e

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 EACH
ROW
-> 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: 0
mysql> 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
-> SET NEW.amount = 100;
-> END IF;
-> END

Aalim Muhammed Salegh College of Engineering 39 | P a g e

-> $$
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)

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

Aalim Muhammed Salegh College of Engineering 40 | P a g e

Ex.no: 9 Create view and index for database tables with a large number of records
Date:

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 tables
that 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 represented
by 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)

Aalim Muhammed Salegh College of Engineering 41 | P a g e

CREATING A VIEW:
The first step in creating a view is to define the defining query, which is the query on which
the 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 from
emp2,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 column
names 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 with
check option;

Aalim Muhammed Salegh College of Engineering 42 | P a g e

Query OK, 0 rows affected (0.05 sec)
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 slows
down 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 uses
the 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)

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 exist
mysql> show index from sstud2;

Aalim Muhammed Salegh College of Engineering 43 | P a g e

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--
------+------+------------+---------+---------------+---------+------------+
| 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 the
table.
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;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+---------+------------+
| 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:

Aalim Muhammed Salegh College of Engineering 44 | P a g e

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.

Aalim Muhammed Salegh College of Engineering 45 | P a g e

Ex.no: 10 Create an XML database and validate it using XML schema
Date:

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 store
self 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 database
schema 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 xsd
file.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>

Aalim Muhammed Salegh College of Engineering 46 | P a g e

<complexType name="empResponse">
<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 two
sample 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.xsd
employee.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 if
validation is
successful or else returns false.

Aalim Muhammed Salegh College of Engineering 47 | P a g e

XMLValidation.java
package com.journaldev.xml;
import java.io.File;
import java.io.IOException;
import javax.xml.XMLConstants;
import javax.xml.transform.stream.StreamSource;
import javax.xml.validation.Schema;
import javax.xml.validation.SchemaFactory;
import javax.xml.validation.Validator;
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 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

Aalim Muhammed Salegh College of Engineering 48 | P a g e

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

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

Aalim Muhammed Salegh College of Engineering 49 | P a g e

Ex.no: 11 Create Document, column and graph based data using NOSQL database
tools.
Date:

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 added
to 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”: [

Aalim Muhammed Salegh College of Engineering 50 | P a g e

{
“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 building
the 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.
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.

Aalim Muhammed Salegh College of Engineering 51 | P a g e

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 represents
Nodes with properties from relationships representation

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

Aalim Muhammed Salegh College of Engineering 52 | P a g e

Ex.no: 12 Simple GUI Application
Date:

Aim:
Write a program in Java to create Displaying login page and home page using Jframe and
Databases (three tier architecture).
Procedure:
Three tier architecture is a very common architecture. A three tier architecture is typically
split 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;

Aalim Muhammed Salegh College of Engineering 53 | P a g e

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;

/**
*
* @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();

Aalim Muhammed Salegh College of Engineering 54 | P a g e

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

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

Aalim Muhammed Salegh College of Engineering 55 | P a g e

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))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 85,
Short.MAX_VALUE)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(javax.swing.GroupLayout.Alignment.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.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(68, 68, 68)

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

Aalim Muhammed Salegh College of Engineering 56 | P a g e

.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.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:

Aalim Muhammed Salegh College of Engineering 57 | P a g e

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")) {
String username=edtusername.getText();
String password=edtpassword.getText();
Statement stm=con.createStatement();
String sql="select * from login where username='"+username+"' and
password='"+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());

Aalim Muhammed Salegh College of Engineering 58 | P a g e

}

/**
* @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
*/
try {
for (javax.swing.UIManager.LookAndFeelInfo info :
javax.swing.UIManager.getInstalledLookAndFeels()) {
if ("Nimbus".equals(info.getName())) {
javax.swing.UIManager.setLookAndFeel(info.getClassName());
break;
}
}
} catch (ClassNotFoundException ex) {

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

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

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

Aalim Muhammed Salegh College of Engineering 59 | P a g e

java.util.logging.Logger.getLogger(Login.class.getName()).log(java.util.logging.Level.SEVE
RE, null, ex);
}
//</editor-fold>

/* Create and display the form */

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

// Variables declaration - do not modify

private 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();
}

Aalim Muhammed Salegh College of Engineering 60 | P a g e

/**
* 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.Alignment.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,
javax.swing.GroupLayout.PREFERRED_SIZE)
.addContainerGap(147, Short.MAX_VALUE))
);

Aalim Muhammed Salegh College of Engineering 61 | P a g e

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
*/
try {
for (javax.swing.UIManager.LookAndFeelInfo info :
javax.swing.UIManager.getInstalledLookAndFeels()) {
if ("Nimbus".equals(info.getName())) {
javax.swing.UIManager.setLookAndFeel(info.getClassName());
break;
}
}
} catch (ClassNotFoundException ex) {

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

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

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

java.util.logging.Logger.getLogger(homepage.class.getName()).log(java.util.logging.Level.S
EVERE, null, ex);

Aalim Muhammed Salegh College of Engineering 62 | P a g e

}
//</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
}
JFrame(homepage)

Aalim Muhammed Salegh College of Engineering 63 | P a g e

JFrame(loginpage)

Output:

RESULT:
Simple GUI Database application and incorporate is Developed Successfully

Aalim Muhammed Salegh College of Engineering 64 | P a g e

Ex.no: 13 Case study for Inventory Management for a EMart Grocery Shop
Date:

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:

Aalim Muhammed Salegh College of Engineering 65 | P a g e

CODING:

FORM1
Dim db As Database
Dim rs As Recordset
Private Sub Command1_Click()
Form3.Show
End Sub
Private Sub Command2_Click()
Form4.Show
End Sub
Private Sub Command3_Click()
Form5.Show
End Sub
Private Sub Command4_Click()
End
End Sub
Private Sub Form_Load()
Set db = OpenDatabase("D:\prj789\invent\INVENTORY.MDB")
Set rs = db.OpenRecordset("SYSTEM")
End Sub
FORM2
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

Aalim Muhammed Salegh College of Engineering 66 | P a g e

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 l1
End If
End If
rs.MoveNext
Next 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.AddNew
rs(0) = Text1.Text
rs(1) = Text2.Text
rs(2) = Text3.Text
rs(3) = Text4.Text
rs(4) = Text5.Text
rs(5) = Text6.Text
rs(6) = Text7.Text
rs(7) = Text8.Text
rs.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

Aalim Muhammed Salegh College of Engineering 67 | P a g e

For i = 1 To rs.RecordCount
If rs(0) = Text1.Text Then
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
Text5.Text = rs(4)
Text6.Text = rs(5)
Text7.Text = "" Text8.Text = "" End If
rs.MoveNext
Next i
End Sub
FORM3
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Private Sub Command1_Click()
rs.MoveFirst
For i = 1 To rs.RecordCount
If rs(0) = Text1.Text Then
rs.Edit
If Text4.Text = "" Then
MsgBox "Enter the no of items needed" Else
rs(6) = Text4.Text
If rs(6) <= rs(4) Then
rs(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.Update
GoTo l1
End If
End If
rs.MoveNext

Aalim Muhammed Salegh College of Engineering 68 | P a g e

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 Sub
Private Sub Form_Load()
Set db = OpenDatabase("D:\prj789\invent\INVENTORY.MDB")
Set rs = db.OpenRecordset("SYSTEM")
End Sub
Private Sub List2_Click()
Text1.Text = List2.Text
rs.MoveFirst
For i = 1 To rs.RecordCount
If rs(0) = Text1.Text Then
Text2.Text = rs(4)
Text3.Text = rs(5)
Text4.Text = "" Text5.Text = "" End If
rs.MoveNext
Next i
End Sub
FORM4
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

Aalim Muhammed Salegh College of Engineering 69 | P a g e

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) = "COMPANY
NAME" 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
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

Aalim Muhammed Salegh College of Engineering 70 | P a g e

'MSFlexGrid1.FixedCols = 7
'MSFlexGrid1.Text = rs(7)
r=r+1
rs.MoveNext
Loop
End Sub
FORMS FORM1 : MAIN MENU

FORM2 : PURCHASE DETAILS

Aalim Muhammed Salegh College of Engineering 71 | P a g e

FORM3 : SALES DETAILS

FORM4 :STOCK DETAILS

Aalim Muhammed Salegh College of Engineering 72 | P a g e