1

EX NO: 1 DATA DEFINITION, TABLE CREATION

Date:

AIM:
To execute and verify the Data Definition Language commands.

PROCEDURE:
STEP 1: Create the table with its essential attributes.
STEP 2: Execute different Commands and extract information from the table.

INTRODUCTION:
Data definition language is a set of commands that defines database objects.
Create (New database object)
Listing a table definition (Display data type and fields)
Alter (Existing database object)
Drop (Remove database object) Constraints

(i) CREATE TABLE COMMAND:

Syntax: Create table tablename [column name datatype (size)…… column name
datatype (size)];
Naming database objects:
Quotation marks are not allowed in names.
Names are not case sensitive.
Names must begin with an alphabet (not a number).
Only alphanumeric character - , $ and # are allowed in names (hyphen not
allowed)
Keywords or reserved words cannot be used to name an object.
Char (n) - Stores a character string of n character used to create fixed length
fields.
Integer -Stores a number of precision p and scales s. scales refers to the
number of digits to right of decimal point.
Date - Stores data n from DD-MM-YY
Long - Stores variable length text upto 2GB.Only one long datatype per table
allowed.
Varchar (n) - Stores variable length string of upto n characters.Maximun length is
2000.

Query:
SQL> create table classa (sid int primary key, sname varchar(10),
sdept varchar (10), total INT);
Table created.
2

(ii) Creating Table from another Table:

Syntax: Create table tablename [(column name, column name)] as select

column name, column name ….. from table name;

Query: SQL>create table classb(id int,name1 char)as select sid,sname from

classa;

Table created.

SQL> desc classb;

Name Null? Type
----------------------------------------- -------- ----------------------------
ID INT
NAME1 VARCHAR(10)

LISTING A TABLE DEFINITION: Used to display the structure of a table.

Syntax: SQL> Desc<table name>
Query: SQL> desc classa;
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NOT NULL INT
SNAME VARCHAR(10)
SDEPT VARCHAR(10)
TOTAL INT

(i) Listing all tables:

To display the names of all tables.

Syntax: Select * from tab;

ALTER TABLE QUERY:

Used to change a table without removing it from the database.The alter
table statements can be used to

(i) Adding new columns to the existing database:

Syntax: Alter table table name ADD [new column name datatype
(size)…new
column name datatype (size)…);

Query: SQL> alter table classa add(grade varchar(10));

Table altered.
3

SQL> desc classa;

Name Null? Type
----------------------------------------- -------- ----------------------------
SID NOT INT
SNAME VARCHAR(10)
SDEPT VARCHAR(10)
TOTAL INT
GRADE VARCHAR(10)

(ii) Redefine the column size:

Syntax: Alter table tablename modify datatype (size);

Query: SQL> SQL> alter table classa modify sname varchar(15);

Table altered.

SQL> desc classa;

Name Null? Type
----------------------------------------- -------- ----------------------------
SID NOT NULL INT
SNAME VARCHAR2(15)
SDEPT VARCHAR2(10)
TOTAL INT
GRADE VARCHAR2(10)

(iii) Delete a Column :

Syntax: Alter table tablename drop column name;

Query: SQL> alter table classa drop grade;

Table altered.

SQL> desc classa;

Name Null? Type
----------------------------------------- -------- ----------------------------
SID NOT NULL INT
SNAME VARCHAR2(15)
SDEPT VARCHAR2(10)
TOTAL INT

DROP TABLE QUERY:

There are several changes to a table structure that are not allowed through use of
alter statements.
Decreasing the size of a column (allowed only if the table is empty).
Changing the name of column.
4

Changing a column datatype.

Changing a table name,

If you need to make these changes to a table you must remove the table
from database and recreate the table with correct parameters. The drop
statements is used to delete entire table.

Syntax: Drop table<table name>

Query: Drop table classb;
Table dropped.
5

RESULT:
Thus the Data Definition Language commands (create, alter, drop) are studied and
executed successfully and output was verified.
6

EX NO: 2 INSERT, SELECT COMMANDS, UPDATE, DELETE and TCL

Date: COMMANDS

AIM:
To execute and verify Data Manipulation Language commands (select, insert,
update and delete) and TCL.

PROCEDURE:
STEP 1: Start
STEP 2: Create the table with its essential attributes.
STEP 3: Insert the record into table
STEP 4: Update the existing records into the table
STEP 5: Delete the records in to the table
STEP 6: Stop

DML COMMANDS
Values are placed in the fields with three manipulation language
Select(to query)

Update(modifying tables)

Insert (adding new records)

Delete (remove existing records)

INSERT:
Adding data to a database is accomplished by SQL insert statements.
Syntax:
Insert into table name [(column name…..n column name)] values
(expression…);

Items inside square bracket are optional. If column list is omitted it is assumed that
values will be inserted into all columns of the table and order of column will be same as
they appear in the table. Unfortunately an insert statement can be used to insert only a
single row. To insert multiple rows, we have to use insert statement multiple times.
Query:
SQL
insert into classa values(1,'john','it',100);
insert into classa values(2,'raj','it',200);
insert into classa values(3,'rahman','it',300);
insert into classa values(4,'joseph','it',400);
insert into classa values(5,'ram','it',500);
7

SQL> select * from classa

# sid, sname, sdept, total
'1', 'john', 'it', '100'
'2', 'raj', 'it', '200'
'3', 'rahman', 'it', '300'
'4', 'joseph', 'it', '400'
'5', 'ram', 'it', '500'

SELECT:
Select query is used for data retrieval.
Syntax:
Select * from table name [where conditions, group by column-list having conditions,
order by column-names];
Example:
Retrieving all rows and columns of the
table.

Select * from <table name>;

Retrieving specific column of the table

Select column1, column2 from <table name>;

Eliminating duplicates from table

Select distinct column-name from table

name;

Specifying condition

Select column from table where

condition;

Sorting record set

Select column1, column2 from table name order by column1 asc,

column2 desc;

Using SQL specific operations

Selecting rows with null values

Select * from table name where column is null/not null;

Between operator
8

Select * from table name where column between values1 and

values2;

In operator-Defines a set in which a given value may or may

not be included.
Select * from table name where column in
(values1…); d. Like operator
„-„ for partial search.
„%‟ for mutiple
character. „_‟ for single
character.
Query: To retrieve tuples in a table using select query:

SQL> select * from classa;

# sid, sname, sdept, total
'1', 'john', 'it', '100'
'2', 'raj', 'it', '200'
'3', 'rahman', 'it', '300'
'4', 'joseph', 'it', '400'
'5', 'ram', 'it', '500'
Retriving specific columns:

SQL> select sid,sname from classa;

# sid, sname
'1', 'john'
'2', 'raj'
'3', 'rahman'
'4', 'joseph'
'5', 'ram'

Eliminating duplicates:
SQL> select distinct sdept from classa;

# sdept
'it'

Specifying condition:
SQL> select sname from classa where total>400;
# sname
'ram'

Sorting record set:

SQL> select sname from classa order by sname asc;
# sname
'john'
'joseph'
9

'rahman'
'raj'
'ram'

SQL> select sname,sdept from classa order by sname asc,sdept desc;

# sname, sdept
'john', 'it'
'joseph', 'it'
'rahman', 'it'
'raj', 'it'
'ram', 'it'

USING SQL SPECIAL OPERATOR:

Rows with null values:
SQL> select * from classa where sdept is null;
# sid, sname, sdept, total

(ii) Between operator

SQL> SELECT * FROM classa WHERE total BETWEEN 100 AND 300;
# sid, sname, sdept, total
'1', 'john', 'it', '100'
'2', 'raj', 'it', '200'
'3', 'rahman', 'it', '300'

(iii) In operator:
SQL> SELECT * FROM classa WHERE sname IN ('ram', 'raj', 'john');
# sid, sname, sdept, total
'1', 'john', 'it', '100'
'2', 'raj', 'it', '200'
'5', 'ram', 'it', '500'

(iv) Like operator

SQL> select * from classa where sname like 'r%';
# sid, sname, sdept, total
'2', 'raj', 'it', '200'
'3', 'rahman', 'it', '300'
'5', 'ram', 'it', '500'

SQL> select * from classa where sname like '%n';

# sid, sname, sdept, total
'1', 'john', 'it', '100'
'3', 'rahman', 'it', '300'

UPDATE:
SQL provides the ability to modify existing data using update statements.
Syntax:
Update table name set column name=expression, column name=expression…where
column name=expression;
10

The where clause is optional. If not specified, update changes in the value of the
specified columns for every row in the table.
Query:

SQL> update classa set total=600 where sid='5';

1 row updated.

SQL> select * from classa;

# sid, sname, sdept, total
'1', 'john', 'it', '100'
'2', 'raj', 'it', '200'
'3', 'rahman', 'it', '300'
'4', 'joseph', 'it', '400'
'5', 'ram', 'it', '600'

DELETE:
Rows are removed from tables through the use of delete statement.
Syntax:
Delete from table name where (condition);

The where clause is optional. If it is not specified, all rows in selected table are
removed.
Query:
SQL> delete from classa where sid='5';
1 row deleted.
SQL> select * from classa;
SID SNAME SDEPT TOTAL
---------- ---------- ---------- ----------
2 aarthi cse 600
3 sakthi it 500
4 vino ece 900
6 kalpana it 300

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

Commit command
Commit command is used to permanently save any transaction into database.
Following is Commit command's syntax,
commit;
11

Rollback command
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;

Savepoint command
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;

Exercise:

Create the table class with the ID & NAME attributes Apply TCL commands and show
the result

SQL> CREATE TABLE CLASS(id int,sname varchar(20));

insert into class values(1,'john');

insert into class values(2,'raj');
insert into class values(3,'rahman');

# id, sname
'1', 'john'
'2', 'raj'
'3', 'rahman'

Let’s use some SQL queries on the above table and see the results.

INSERT into class values(5,'Rahul');

commit;
UPDATE class set name='abhijit' where id='5';
savepoint A;
INSERT into class values(6,'Chris');
savepoint B;
INSERT into class values(7,'Bravo');
savepoint C;
SELECT * from class;
The resultant table will look like,
# id, sname
'1', 'john'
'2', 'raj'
'3', 'rahman'
'5', 'abhijit'
12

'6', 'Chris'
'7', 'Bravo'
Now rollback to savepoint B
rollback to B;
SELECT * from class;
The resultant table will look like
# id, sname
'1', 'john'
'2', 'raj'
'3', 'rahman'
'5', 'abhijit'
'6', 'Chris'

Now rollback to savepoint A

rollback to A;
SELECT * from class;

The result table will look like

# id, sname
'1', 'john'
'2', 'raj'
'3', 'rahman'
'5', 'abhijit'
13

RESULT:
Thus the Data Manipulation Language commands (insert, update, delete,
retrieve) and TCL commands are studied and executed successfully and output
was verified.
14

Ex:no 3 Database Querying – Simple queries, Nested queries, Subqueries

Date: and Joins

Aim:

To implement and execute simple, nested, sub & join queries in mysql database.

Simple Queries

The SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you
only want to list the different (distinct) values.

SELECT DISTINCT column1, column2, ...FROM table_name;

SELECT DISTINCT Examples

SELECT DISTINCT sdept FROM classa;

# sdept
'it'

The SQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one
condition:

 The AND operator displays a record if all the conditions separated by AND
are TRUE.
 The OR operator displays a record if any of the conditions separated by OR is
TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

SELECT column1, column2, ...

FROM table_name
WHERE condition1 AND condition2 AND condition3 ..
15

OR Syntax

SELECT column1, column2, ...

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

NOT Syntax

SELECT column1, column2, ...

FROM table_name
WHERE NOT condition;

AND Example

SELECT * FROM classa WHERE sdept='it' AND sid=1;

# sid, sname, sdept, total

'1', 'john', 'it', '100'

OR Example

SELECT * FROM classa WHERE sdept='it' or sid=1;

# sid, sname, sdept, total

'1', 'john', 'it', '100'

'2', 'raj', 'it', '200'

'3', 'rahman', 'it', '300'

'4', 'joseph', 'it', '400'

'5', 'ram', 'it', '600'

NOT Example

SELECT * FROM classa WHERE NOT sid=1;

# sid, sname, sdept, total

'2', 'raj', 'it', '200'

'3', 'rahman', 'it', '300'

'4', 'joseph', 'it', '400'

'5', 'ram', 'it', '600'

16

The SQL ORDER BY Keyword

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.

ORDER BY Syntax

SELECT column1, column2, ...

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

ORDER BY Example

SELECT * FROM classa ORDER BY sid;

# sid, sname, sdept, total

'1', 'john', 'it', '100'

'2', 'raj', 'it', '200'

'3', 'rahman', 'it', '300'

'4', 'joseph', 'it', '400'

'5', 'ram', 'it', '600'

ORDER BY DESC Example

SELECT * FROM classa ORDER BY sid desc;

# sid, sname, sdept, total

'5', 'ram', 'it', '600'

'4', 'joseph', 'it', '400'

'3', 'rahman', 'it', '300'

'2', 'raj', 'it', '200'

'1', 'john', 'it', '100'

The SQL MIN() and MAX() Functions

17

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

MIN() Syntax

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX() Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition;

MIN() Example

SELECT MIN(sid) AS SmallestID FROM classa;

# SmallestID

'1'

MAX() Example

SELECT max(sid) AS biggestID FROM classa;

# biggestID

'5'

The SQL COUNT(), AVG() and SUM() Functions

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

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

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

COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG() Syntax
18

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM() Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

COUNT() Example

SELECT COUNT(sid) FROM classa;

# COUNT(sid)
'5'

AVG() Example

SELECT avg(sid) FROM classa;

# avg(sid)
'3.0000'

SUM() Example

SELECT sum(sid) FROM classa;

# sum(sid)
'15'

The SQL LIKE Operator

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

There are two wildcards often used in conjunction with the LIKE operator:

 % - The percent sign represents zero, one, or multiple characters

 _ - The underscore represents a single character

LIKE Syntax

SELECT column1, column2, ...

FROM table_name
WHERE columnN LIKE pattern;
19

Description
LIKE Operator
WHERE CustomerName LIKE
Finds any values that start with "a"
'a%'
WHERE CustomerName LIKE
Finds any values that end with "a"
'%a'
WHERE CustomerName LIKE
Finds any values that have "or" in any position
'%or%'
WHERE CustomerName LIKE
Finds any values that have "r" in the second position
'_r%'
WHERE CustomerName LIKE Finds any values that start with "a" and are at least
'a_%_%' 3 characters in length
WHERE ContactName LIKE Finds any values that start with "a" and ends with
'a%o' "o"

SQL LIKE Examples

SELECT * FROM classa WHERE sname LIKE 'a%';

# sid, sname, sdept, total

SELECT * FROM classa WHERE sname LIKE '%a';

# sid, sname, sdept, total

The SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT * FROM classa WHERE sname IN ('ram', 'raj');

# sid, sname, sdept, total

'2', 'raj', 'it', '200'

'5', 'ram', 'it', '600'

20

The SQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be
numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

BETWEEN Example

SELECT * FROM classa WHERE sid BETWEEN 1 AND 3;

# sid, sname, sdept, total

'1', 'john', 'it', '100'

'2', 'raj', 'it', '200'

'3', 'rahman', 'it', '300'

The SQL GROUP BY Statement

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.

GROUP BY Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

SQL GROUP BY Examples

SELECT COUNT(sid), sdept FROM classa

# COUNT(sid), sdept

'5', 'it'

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
21

NOTE

Refer below website for example tables

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join_inner

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

 (INNER) JOIN: Returns records that have matching values in both tables
 LEFT (OUTER) JOIN: Return all records from the left table, and the matched
records from the right table
 RIGHT (OUTER) JOIN: Return all records from the right table, and the
matched records from the left table
 FULL (OUTER) JOIN: Return all records when there is a match in either left
or right table

SQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

SQL INNER JOIN Example

The following SQL statement selects all orders with customer information:

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the
matched records from the right table (table2). The result is NULL from the right side,
if there is no match.

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
22

SQL LEFT JOIN Example

The following SQL statement will select all customers, and any orders they might
have:

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the
matched records from the left table (table1). The result is NULL from the left side,
when there is no match.

RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

SQL RIGHT JOIN Example

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName

FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword return all records when there is a match in either
left (table1) or right (table2) table records.

Note: FULL OUTER JOIN can potentially return very large result-sets!

FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

SQL FULL OUTER JOIN Example

The following SQL statement selects all customers, and all orders:

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers
23

FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID

ORDER BY Customers.CustomerName;

Subqueries

A MySQL subquery is a query nested within another query such as SELECT,

INSERT, UPDATE or DELETE. In addition, a MySQL subquery can be nested inside
another subquery.

A MySQL subquery is called an inner query while the query that contains the
subquery is called an outer query. A subquery can be used anywhere that
expression is used and must be closed in parentheses.

Example SubQueries

1. SELECT lastName, firstName FROM employees WHERE officeCode IN

(SELECT officeCode FROM offices WHERE country = 'USA');

In this example:

 The subquery returns all office codes of the offices located in the USA.
 The outer query selects the last name and first name of employees who work
in the offices whose office codes are in the result set returned by the
subquery.

2. Select max(sid) from classa where sid <( select max(sid) from classa)

# max(sid)

'4'

Nested Queries
SQL provides a mechanism for the nesting of subqueries.

A subquery is a select-from-where expression that is nested within another query.

Examples:
Return only a Single value
Example:
Select sname from classa where total > (select total from classa where sname='raj');

Ouput:
# sname
24

'rahman'
'joseph'
'ram'
Returning Several
values Example:
Select sname from classa where total > all (select total from classa where
sid=1);
Ouput:
# sname
'raj'
'rahman'
'joseph'
'ram'
25

Result:
Thus to implement and execute the simple, nested & join queries in mysql
database is written and executed successfully.
26

EX NO: 4 Views, Sequences and Synonyms

Date:

AIM
To execute and verify the SQL commands for Views,Sequences and Synonyms.

PROCEDURE
STEP 1: Start
STEP 2: Create the table with its essential attributes.
STEP 3: Insert attribute values into the table.
STEP 4: Create the view from the above created table.
STEP 5: Execute different Commands and extract information from the View.
STEP 6: Stop

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 as <query expression>

Where query expression is any legal query expression and view name is represented
by v.
Query:
SQL> select * from classa;
SID SNAME SDEPT TOTAL
---------- --------------- ---------- ------------ -----------------
1 aarthi IT 450
2 ezhil ECE 590
3 sakthi IT 900
4 vino ECE 600
7 viji IT 900
6 sumathi ECE 890

6 rows selected.

SQL> select * from classb;

ID NAME GRADE
---------- ---------- ----------
1 aarthi b
2 ezhil b
27

6 sumathi a
7 viji a

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.

Query for defining query:

SQL> select classa.sid, classa.sname, classa.sdept, classb.name1 from classa, classb
where classa.sid=classb.id order by classa.sid;
SID SNAME SDEPT Name1

SQL> create view classa_b as select classa.sid, classa.sname, classa.sdept,

classb.grade from classa, classb where classa.sid=classb.id order by classa.sid;
View created.
SQL> select * from classa_b;
SID SNAME SDEPT GRADE
---------- --------------- ---------- ----------
1 aarthi IT B
2 ezhil ECE B
6 sumathi ECE A
7 viji IT A

SID SNAME SDEPT

---------- -------------- ----------
1 aarthi IT
7 viji IT

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

Query:
SQL> create view classxy12(id,dept) as select sid,sdept
from classa;
View created.
SQL> select * from classxy12;
ID DEPT
---------- ----------
IT
ECE
IT
ECE
IT
ECE
6 rows selected.

USING AGGREGATION FUNCTIONS WITH VIEWS:

Aggregate function that take a collection of value as input and return a single
value .SQL offers five built in aggregate function such as count, sum ,average,
minimum and maximum. Count function will count all the rows including duplicates
or null. Sum function calculates the sum of all values in specified column. The
average function produces arithmetic mean of all the selected values or fields. The
maximum function produces max values of all the selected values of a given fields.
The minimum function produces min values of all the selected values of a given
fields.

Query:
SQL> select * from classa
# sid, sname, sdept, total
'1', 'john', 'it', '100'
'2', 'raj', 'it', '200'
'3', 'rahman', 'it', '300'
'4', 'joseph', 'it', '400'
'5', 'ram', 'it', '600'
SQL> create view student_count_min(sno,count) as select
min(total),count(total)from classa;
View created.
SQL> select *from student_count_min;
# sno, count
'100', '5'
SQL> create view student_sum_avg(tot,avgtotal)as select sum(total),avg(total)
from classa;
View created.
SQL> select * from student_sum_avg;
# tot, avgtotal
'1600', '320.0000'
29

SQL> create view stud_max(tot) as select max(total) from classa;

View created.
SQL> select * from stud_max;
# tot
'600'

SYNONYMS:
Use the CREATE SYNONYM statement to create a synonym, which is an
alternative name for a table, view, sequence, procedure, stored function, package,
materialized view, Java class schema object, user-defined object type, or another
synonym. Synonyms provide both data independence and location transparency.
Synonyms permit applications to function without modification regardless of which
user owns the table or view and regardless of which database holds the table or
view.
Synonyms in DML statements: SELECT, INSERT, UPDATE, DELETE,
FLASHBACK TABLE, EXPLAIN PLAN, and LOCK TABLE. Synonyms in DDL
statements: AUDIT, NOAUDIT, GRANT, REVOKE, and COMMENT.
Synonyms example:

use information_schema;

call sys.syonym_db('information_schema','is');

SEQUENCE:
Sequence is a feature supported by some database systems to produce
unique values on demand. Some DBMS like MySQL supports AUTO_INCREMENT
in place of sequence. AUTO_INCREMENT is applied on columns. It automatically
increments the column value by 1 each time a new record is entered into the table.
Sequence is also some what similar to AUTO_INCREMENT but its has some extra
features.

Syntax to create sequence

CREATE SEQUENCE sequence_name START with initial_value INCREMENT by
increment_value MAXVALUE maximum_value cycle|nocycle

Example to sequence

create table hanif(id int unsigned not null auto_increment primary key,aname
char(20));
insert into hanif (aname)values('aa');
insert into hanif (aname)values('bb');
insert into hanif (aname)values('cc');
The sql query will be
30

select * from hanif;

Output:

# id, aname
'1', 'aa'
'2', 'bb'
'3', 'cc'
alter table hanif auto_increment=101;
31

RESULT:
Thus the SQL commands for View, sequence and synonyms has been executed
successfully and output was verified.
32

EX. NO: 5 CURSORS

Date:

Aim:
To execute and verify the programs using cursors.

Introduction to MySQL cursor

To handle a result set inside a stored procedure, you use a cursor. A cursor allows
you to iterate a set of rows returned by a query and process each row accordingly.
MySQL cursor is read-only, non-scrollable and asensitive.

Read-only: you cannot update data in the underlying table through the cursor.

Non-scrollable: you can only fetch rows in the order determined by the SELECT
statement. You cannot fetch rows in the reversed order. In addition, you cannot skip
rows or jump to a specific row in the result set.

Asensitive: there are two kinds of cursors: asensitive cursor and insensitive cursor.
An asensitive cursor points to the actual data, whereas an insensitive cursor uses a
temporary copy of the data. An asensitive cursor performs faster than an insensitive
cursor because it does not have to make a temporary copy of data. However, any
change that made to the data from other connections will affect the data that is being
used by an asensitive cursor, therefore, it is safer if you do not update the data that
is being used by an asensitive cursor. MySQL cursor is asensitive.

You can use MySQL cursors in stored procedures, stored functions, and triggers.

Working with MySQL cursor

First, you have to declare a cursor by using the DECLARE statement:

DECLARE cursor_name CURSOR FOR SELECT_statement;

Next, you open the cursor by using the OPEN statement.

OPEN cursor_name;

Then, you use the FETCH statement to retrieve the next row pointed by the cursor
and move the cursor to the next row in the result set.
FETCH cursor_name INTO variables list;

Finally, you call the CLOSE statement to deactivate the cursor and release the
memory associated with it as follows:
CLOSE cursor_name;

MySQL Cursor Example

33

create database curs;

use curs;
create table dem(id int,aname char(20),dept char(10));
insert into dem values(1,'aa','it');
insert into dem values(2,'bb','cse');
insert into dem values(3,'cc','ece');
drop table dem;
drop procedure curdemo;
select *from dem;

Create procedure for cursor

delimiter $$
create procedure curdemo(id1 int)
begin
declare name1 varchar(100);
declare cur1 cursor for select aname from dem where id=1;
open cur1;
fetch cur1 into name1;
select name1;
close cur1;
end $$

Execute procedure

call curdemo(1)

output

# name1
'aa'
34

Result:
Thus the programs using cursor was executed and verified successfully
35

Ex No: 6 PROCEDURES AND FUNCTIONS

DATE:

Aim: To Write a program using procedures and functions

MySQL Stored Function

A stored function is a special kind stored program that returns a single value. You
use stored functions to 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.

MySQL stored function syntax

The following illustrates the simplest syntax for creating a new stored
function:

CREATE FUNCTION function_name(param1,param2,…)

RETURNS datatype
[NOT] DETERMINISTIC
Statements

MySQL stored procedure

MySQL stored procedure using CREATE PROCEDURE statement. In addition, we

will show you how to call stored procedures from SQL statements.

MySQL stored procedure syntax

DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM products;
END //
DELIMITER ;

MySQL stored procedure example

Let’s take a look at an example of using the stored function

use sample1
36

create table cus(cid integer,cname char(20),address varchar(75),salary int,post

varchar(20));
insert into cus values(1,'aa','77,anna salai,arcot',10000,'clerk');
insert into cus values(3,'bb','01,anna salai,chennai',15000,'staff');
insert into cus values(2,'cc','25,rajaji nagar,banglore',15000,'staff');
insert into cus values(4,'dd','02,mettu street,kochin',10000,'secretary');
insert into cus values(5,'ee','21,north street,mumbai',15000,'manager');

select* from cus;

# cid, cname, address, salary, post

'1', 'aa', '77,anna salai,arcot', '10000', 'clerk'
'3', 'bb', '01,anna salai,chennai', '15000', 'staff'
'2', 'cc', '25,rajaji nagar,banglore', '15000', 'staff'
'4', 'dd', '02,mettu street,kochin', '10000', 'secretary'
'5', 'ee', '21,north street,mumbai', '15000', 'manager'

Creating stored procedure

USE `sample1`;
DROP procedure IF EXISTS `new_pro`;
DELIMITER $$
USE `sample1`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_pro`()
BEGIN
UPDATE cus
SET salary = salary + 500;
END$$
DELIMITER ;

Executing stored procedure

call new_pro;

5 rows effected

SQL>select* from cus;

# cid, cname, address, salary, post

'1', 'aa', '77,anna salai,arcot', '10500', 'clerk'
'3', 'bb', '01,anna salai,chennai', '15500', 'staff'
'2', 'cc', '25,rajaji nagar,banglore', '15500', 'staff'
'4', 'dd', '02,mettu street,kochin', '10500', 'secretary'
'5', 'ee', '21,north street,mumbai', '15500', 'manager'
37

MySQL stored function 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!!'
38

Result: Thus the programs for procedure and functions are executed successfully.
39

Ex no: 7 Trigger
DATE:

Aim:

To execute and verify the programs using trigger

Introduction to Create Trigger in MySQL

MySQL trigger syntax

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

MySQL trigger example

Trigger for update

Create table account1(acct_num int,amount int)

insert into account1 values(1,150)
select * from account1
40

DELIMITER $$
USE `sample1`$$
CREATE DEFINER = CURRENT_USER TRIGGER
`sample1`.`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$$
DELIMITER ;

update account1 set amount=2000 where acct_num=1

select * from account1

# acct_num, amount
'1', '100.00'

Trigger for insert

DELIMITER $$
USE `sample1`$$
CREATE DEFINER = CURRENT_USER TRIGGER
`sample1`.`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
$$
DELIMITER ;

insert into account1 values(2,-100)

select * from account1

# acct_num, amount
'1', '100.00'
'2', '0.00'
41

Trigger for Delete

DELIMITER $$
USE `sample1`$$
CREATE DEFINER = CURRENT_USER TRIGGER
`sample1`.`bank_BEFORE_DELETE` BEFORE DELETE ON `account1` FOR EACH
ROW
BEGIN
delete from account2 where acct_num=2;
END
$$
DELIMITER ;

select * from account1

# acct_num, amount
'1', '100.00'
'2', '0.00'

select * from account2

# acct_num, amount
'2', '45.00'
'3', '50.00'
'137', '50.00'

delete from account1 where acct_num=2

select * from account1

# acct_num, amount
'1', '100.00'

select * from account2

# acct_num, amount
'3', '50.00'
'137', '50.00'
42

Result:
Thus the programs using trigger was executed and verified successfully
43

Ex no 8 Exception handling
DATE:

AIM

To execute and verify the programs using Exception Handling

Introduction to MySQL Error Handling in Stored Procedures

When an error occurs inside a stored procedure, it is important to handle it

appropriately, such as continuing or exiting the current code block’s execution, and
issuing a meaningful error message.

MySQL provides an easy way to define handlers that handle from general conditions
such as warnings or exceptions to specific conditions e.g., specific error codes.

Declaring a handler

To declare a handler, you use the DECLARE HANDLER statement as follows:

DECLARE action HANDLER FOR condition_value statement;

If a condition whose value matches the condition_value , MySQL will execute the
statement and continue or exit the current code block based on the action .

The action accepts one of the following values:

 CONTINUE : the execution of the enclosing code block ( BEGIN … END )

continues.
 EXIT : the execution of the enclosing code block, where the handler is
declared, terminates.

The condition_value specifies a particular condition or a class of conditions that

activate the handler. The condition_value accepts one of the following values:

 A MySQL error code.

 A standard SQLSTATE value. Or it can be an SQLWARNING , NOTFOUND
or SQLEXCEPTION condition, which is shorthand for the class of SQLSTATE
values. The NOTFOUND condition is used for a cursor or SELECT INTO
variable_list statement.
 A named condition associated with either a MySQL error code or SQLSTATE
value.

MySQL handler example in stored procedures

CREATE TABLE t (s1 INT, PRIMARY KEY (s1));

CREATE TABLE t1 (s1 INT, PRIMARY KEY (s1));
44

Create procedure for exception handling

delimiter $$
CREATE PROCEDURE handlerdemo ()
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t VALUES (1);
SET @x = 2;
INSERT INTO t VALUES (1);
SET @x = 3;
END;

Create procedure for exception handling

delimiter $$
CREATE PROCEDURE handlerdemo1 ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t1 VALUES (1);
SET @x = 2;
INSERT INTO t1 VALUES (1);
SET @x = 3;
END;

select * from t
select * from t1
insert into t values(1)
insert into t1 values(1)

CALL handlerdemo()
SELECT @x

Output
# @x
'1'

CALL handlerdemo1()
SELECT @x
# @x
'3'
45

Result:
Thus the programs using exception handling was executed and verified successfully
46

Ex no: 9 ER Design for bank management system

Date:

Aim
To design ER for bank management system

Introduction to ER Model

The ER or (Entity Relational Model) is a high-level conceptual data model diagram.

Entity-Relation model is based on the notion of real-world entities and the
relationship between them.

ER modeling helps you to analyze data requirements systematically to produce a

well-designed database. So, it is considered a best practice to complete ER
modeling before implementing your database.

Procedure

Construct an ER diagram for a Banking System. Clearly indicate the entities,

relationships, cardinality and the key constraints. Also, derive the un-
normalized relational database tables with the help of this diagram.

The General Things needed in a Banking System are: –

1. Person Opens an Account

2. Person using ATM for Transaction

The person opens an Account in a Bank and gets a account number and ATM card.
The person can make transactions in ATM centres. The Details of the Transaction
has to be maintained Between Three Entitys. i.e. User, Account, ATM

User Table:

User_ID Name (First_Name, Address Contact_Number

Last_Name)

Account Table:

Account_Number User_ID Account_Type (Saving_Account, Balance

Current_Account, Over_draft_Account)

ATM Table:

ATM _Number ATM_Place ATM _Cash_Limit

47

Opening_Account Table:

Date User_ID Account_Number ATM _Number

Transaction Table:

Date User_ID Account_Number ATM Transaction_Type

_Number (Deposit_Cheque,
Deposit_Cash,
Withdraw_Cash)

Adding an EER Diagram in mysql workbench

To create an EER diagram for the your database, first add an EER diagram by
double-clicking the Add Diagram icon in the EER Diagrams panel to create and open
a new EER Diagram editor.

The EER Diagram canvas is where object modeling takes place. To add a table to
the canvas, select the Catalog tab in the middle panel on the right side of the
application to display any schemas that appear in the MySQL Model tab. Find the
your schema and expand the view of its objects by clicking + to the left of the
schema name. Expand the tables list in the same way.

You can add tables to the EER canvas by dragging them from the Catalog panel
dropping them onto the canvas. Drop the table onto the canvas, as the following
figure shows.
48

Constructed Er diagram for Bank management system

Result:
Thus the programs using ER Diagram was Designed and verified successfully
49

EX.NO:10
DATE:

BANK MANAGEMENT SYSTEM USING VB FRONT END TOOLS

Aim:
To study the front end tool Visual Basic

Introduction to VISUAL BASIC

VISUAL BASIC is a high level programming language which was evolved
from the earlier DOS version called BASIC. BASIC means Beginners' All-purpose
Symbolic Instruction Code. VISUAL BASIC is a VISUAL and events driven
Programming Language. These are the main divergence from the old BASIC. In
BASIC, programming is done in a text-only environment and the program is
executed sequentially. In VB, programming is done in a graphical environment. In
the old BASIC, you have to write program codes for each graphical object you wish
to display it on screen, including its position and its color. However, In VB , you just
need to drag and drop any graphical object anywhere on the form, and you can
change its color any time using the properties windows.
On the other hand, because users may click on certain object randomly, so
each object has to be programmed independently to be able to response to those
actions (events). Therefore, a VB Program is made up of many subprograms, each
has its own program codes, and each can be executed independently and at the
same time each can be linked together in one way or another.

FORMS
The Visual Basic form is a primary element of an application that functions as
the visual "window." It can include a lot of code. Setting up a form impacts the way
you choose to develop any application in Visual Basic.

Step 1 Open Microsoft Visual Basic environment and select "New Form."
Step 2 Make the form the size that you want by clicking on the corner and dragging.
Then add any form properties that you will need (like a name: Ex:
frmOpener).
Step 3 Add all of the elements, such as control buttons and text boxes, that you will
need on the form for the user. Arrange them exactly the way you want them
to appear for the user.
Step 4 Double-click on the form to enter the code section. A coding window will open
up where the Form Load command represents the point where the software
will open and begin working.
Step 5 Within the code window, add functions for anything that you want to happen
when the form loads, before the user does anything.
Step 6 Add variables. The Form Load section of your code is a great place to
dimension variables, known as "global" variables, that you will use throughout
50

the program. Add variables by name and specify type: Ex. for an integer to
count clicks, use the command: dim click as integer
Step 7 You're not done yet. Most of the function code should be within user-
generated events. Don't try to program the whole thing within the form code
module. The application will do most of its work through functions that are
called between different objects (command buttons) and the form itself. A
programmer has to know how to "pass" variables. When you do dimension
variables in the form load, think about how they will be passed to various
functions.

Creating Your First Application

First of all, you have to launch Microsoft Visual Basic 6. Normally, a default
form with the name Form1 will be available for you to start your new project. Now,
double click on Form1, the source code window for Form1 as shown below. The top
of the source code window consists of a list of objects and their associated events or
procedures. In following figure1, the object displayed is Form and the associated
procedure is Load.

Add account:
Private Sub Command1_Click()
Form2.Show
End Sub
View account:
Private Sub Command2_Click()
Form3.Show
End Sub
Deposit:
Private Sub Command3_Click()
Form4.Show
End Sub
51

Withdraw:
Private Sub Command4_Click()
Form5.Show
End Sub
Exit:
Private Sub Command5_Click()
End
End Sub
Add account:

Private Sub Command1_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "dsn=bank"
rs.ActiveConnection = cn
With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open "select * from acc_info"
End With
With rs
.AddNew
.Fields(0) = Text1.Text
.Fields(1) = Text4.Text
.Fields(2) = Text2.Text
.Fields(3) = Text3.Text
.Update
End With
MsgBox ("New Record Added Successfully")
Form1.Show
End Sub
52

View account:

Private Sub Command1_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim a As Boolean
a = False
cn.Open "dsn=bank"
rs.ActiveConnection = cn
With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open "select * from acc_info"
End With
With rs
rs.MoveFirst
While Not rs.EOF
If (Val(Text1.Text) = rs(0)) Then
a = True
Text2.Text = rs(3)
Text3.Text = rs(2)
Text4.Text = rs(1)
End If
rs.MoveNext
Wend
If (a = False) Then
MsgBox "Account Not Found!"
End If
End With
End Sub
53

Deposit:

Private Sub Command1_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim a As Boolean
a = False
cn.Open "dsn=bank"
rs.ActiveConnection = cn
With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open "select * from acc_info"
End With
With rs
rs.MoveFirst
While Not rs.EOF
If (Val(Text1.Text) = rs(0)) Then
a = True
.Fields(3) = Val(Text2.Text + rs(3))
MsgBox "Amount Added Successfully "
End If
rs.MoveNext
Wend
If (a = False) Then
MsgBox "Account Not Found!"
End If
End With
End Sub
54

Withdraw:

Private Sub Command1_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim a As Boolean
a = False
cn.Open "dsn=bank"
rs.ActiveConnection = cn
With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open "select * from acc_info"
End With
With rs
rs.MoveFirst
While Not rs.EOF
If (Val(Text1.Text) = rs(0)) Then
a = True
.Fields(3) = Val(rs(3) - Text2.Text)
MsgBox "Amount Deducted Successfully"
End If
rs.MoveNext
Wend
If (a = False) Then
MsgBox "Account not Found!"
End If
End With
End Sub
Bank database:
55

Result:
Thus the front end tool Visual Basic has been studied and created bank
management system
56

Ex.No:11 Case Study on STOCK MAINTENANCE SYSTEMS

Date:

AIM:
To analyze, design and develop code for Stock maintenance system VB and
mysql

1. PROBLEM ANALYSIS AND PROJECT PLANNING:

PROJECT SCOPE
The main scope of the project is to develop a system that effectively manages the
stock and update them regularly through the authenticated users.
OBJECTIVE
The main objective of designing and developing a Stock maintenance System is
to enable
the administrator or any other authenticated person to record, view or update the stock.
PROBLEM STATEMENT
The Stock maintenance system enables the authenticated users to effectively
maintain the
stock.
INFRASTRUCTURE
HARDWARE REQUIREMENTS

: Intel Core i3+55 TC Mother Board

: 500 GB Seagate Sata HDD
: 2 GB DDR3 RAM Transcend
: 18.5”LG LED Monitor
: Zebronics Calyx Cabinet
: TVS Champ Keyboard
: Logitech USB Mouse
SOFTWARE REQUIREMENTS

· mysql
· Visual Basic 6.0

2. SOFTWARE REQUIREMENT ANALYSIS:

MODULE DESCRIPTION:

Login Form: Authenticate the user and administrator.

Selection Form: This form will give the options for selecting the operations.
Add Form: It add the details about the Stock
View Form: This form displays the details about the stock.
Update Form: The added details will get updated.
57

3. DATA MODELING

USECASE DIAGRAM

CLASS DIAGRAM

ACTIVITY DIAGRAM
58

SEQUENCE DIAGRAM
59

COLLABRATION DIAGRAM
60

COMPONENT DIAGRAM

DEPLOYMENT DIAGRAM

4. SOFTWARE DEVELOPMENT AND DEBUGGING

CODING IMPLEMENTATION
Form 1
61

Private Sub Command1_Click()

Dim a As Boolean
a = False
If (Text1.Text = "admin" And Text2.Text = "admin") Then
a = True
Form2.Show
Unload Me
End If
If (a = False) Then
MsgBox ("enter correct username and password")
End If
End Sub
Form 2

Private Sub Command1_Click()

Form3.Show
Unload Me
End Sub

Private Sub Command2_Click()

62

Form4.Show
Unload Me
End Sub

Private Sub Command3_Click()

Form5.Show
Unload Me
End Sub

Private Sub Command4_Click()

Unload Me

End Sub

Form 3

Private Sub Command1_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "dsn=stock"
rs.ActiveConnection = cn
With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open "select * from stock"
End With
With rs
.AddNew
.Fields(0) = Val(Text1.Text)
.Fields(1) = Text2.Text
.Fields(2) = Text3.Text
.Fields(3) = Text4.Text
.Update
End With
MsgBox ("new record added")
Text1.Text = ""
Text2.Text = ""
63

Text3.Text = ""
Text4.Text = ""
End Sub

Private Sub Command2_Click()

Form2.Show
Unload Me
End Sub

Form 4

Private Sub Command1_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim a As Boolean
a = False
cn.Open "dsn=stock"
rs.ActiveConnection = cn
With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open "select * from stock"
End With
rs.MoveFirst
While Not rs.EOF
If (Val(Text1.Text) = rs(0)) Then
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
a = True
End If
rs.MoveNext
Wend
If (a = False) Then
MsgBox ("enter correct ID")
End If
64

End Sub

Private Sub Command2_Click()

Form2.Show
Unload Me
End Sub
Form 5

Private Sub Command1_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim a As Boolean
a = False
cn.Open "dsn=stock"
rs.ActiveConnection = cn
With rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open "select * from stock"
End With
rs.MoveFirst
While Not rs.EOF
If (Val(Text1.Text) = rs(0)) Then
With rs
.Fields(1) = Text1.Text
.Fields(1) = Text2.Text
.Fields(2) = Text3.Text
.Fields(3) = Text4.Text
.Update
End With
MsgBox ("record updated")
End If
rs.MoveNext
Wend
Text1.Text = ""
Text2.Text = ""
65

Text3.Text = ""
Text4.Text = ""
End Sub

Private Sub Command2_Click()

Form2.Show
Unload Me
End Sub

5. SOFTWARE TESTING

FORM NAME INPUT EXPECTED ACTUAL STATUS

OUTPUT OUTPUT

LOGIN FORM User Required Required form Pass

Name&Passwo Form must be was displayed.
rd displayed

STOCK Selection of Required Required form Pass

MAINTANENCE option Form must be was displayed.
FORM displayed

ADD FORM Input Values New Record New Record Pass

tobe added. was added.

VIEW FORM Id No Particular Record was Pass

record must be
displayed displayed

UPDATE Selection of Record must Record was Pass

FORM option be updated updated
66

RESULT

This project was carried out in a sequential manner to design and implement the
“Stock Maintenance System”. Thus the outcome of the project is efficient. The Stock
Maintenance System caters the varied requirements of the user to perform various
options.