EX NO:1A DATA DEFINITION LANGUAGE

DATE:
AIM:

To write the SQL queries using DDL Commands with and without constraints.

DDL STATEMENTS

1. CREATE TABLE
2. ALTER TABLE
3. DROP TABLE

SYNTAX:

1. Create Table

The CREATE TABLE statement is used to create a relational table

CREATE TABLE table_name

column_name1 data_type [constraints],

column_name1 data_type [constraints],

column_n ame1 data_type [constraints],

……..

);

2. Alter Table

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

a. To Add a column
ALTER TABLE table_name ADD column_name datatype

b. To delete a column in a table

ALTER TABLE table_name DROP (column_name)

c. To change the data type of a column in a table

ALTER TABLE table_name MODIFY (column_name datatype)

3. Drop Table
Used to delete the table permanently from the storage

DROP TABLE table_name

RENAME
To alter the table name by using rename keyword

ALTER TABLE table_name RENAME TO new table_name ;

 PRINT:
DATA DEFINITION LANGUAGE

Create Table:

Alter Table :

a. ADD

b. DROP

c. MODIFY
RENAME:

DROP:
Result:
Thus the SQL queries using DDL Commands were successfully executed and verified.
EX NO:1B DATA MANIPULATION LANGUAGE
DATE:
AIM:

To write SQL queries using DML commands to manage the database.

DML STATEMENTS
 INSERT
 UPDATE
 DELETE
 SELECT

SYNTAX:
1. Insert

To insert a new row in a table.

INSERT INTO table_name VALUES(value1, value2,value3,..........);

2. Update

To update new data into an existing table .

UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value;

3.Delete

To delete rows in a table.

DELETE FROM table_name WHERE column=values;

4.SELECT:

RETRIEVING TABLES

Any sql SELCECT statements retrieve records from table using clause.

SELECT column 1,column2 FROM table_name WHERE column2=’values’;

 PRINT:

DATA MANIPULATION LANGUAGE

Create table:

1. INSERT:
2. UPDATE:

3. DELETE:

4. SELECT:
RESULT:

Thus the SQL queries using DML Commands were successfully executed and verified.
EXNO:1C TRANSACTION CONTROL LANGUAGE

DATE:

AIM:

To write SQL queries using commands to manage the database.

TCL COMMANDS

1. COMMIT

2. ROLLBACK

3. BEGIN

4. SAVEPOINT

SYNTAX:

1. COMMIT

DML commands saves modification and it permanently saves the transaction.

COMMIT;

2. ROLLBACK

Rollback means the database is restored to the last commited state .

ROLLBACK[TO SAVEPOINT_NAME];

3. BEGIN

It is used at the beginning of a transaction.

BEGIN;

4. SAVEPOINT

Savepoint helps to save the transaction temporarily.

SAVEPOINT[savepoint_names]
 PRINT:

TRANSACTION CONTROL LANGUAGE

Create table

Insert
COMMIT:

ROLLBACK:
SAVEPOINT:
Result:

Thus the SQL queries using TCL Commands were successfully executed and verified.
EXNO:2A AGGREGATE FUNCTIONS

DATE:

AIM:

To write SQL queries to perform aggregate functions.

Create Table

The CREATE TABLE statement is used to create a relational table

CREATE TABLE table_name

column_name1 data_type [constraints],

column_name1 data_type [constraints],

column_n ame1 data_type [constraints],

……..

);

Insert

To insert a new row in a table.

INSERT INTO table_name VALUES(value1, value2,value3,..........);

AGGREGATE FUNCTIONS

1. Count
2. Max
3. Min
4. Sum
5. Avg

SYNTAX:

1. COUNT

Select COUNT <column_name> FROM <table_name>;

2. MAX

Select MAX <column_name> FROM <table_name>;

3. MIN

Select MIN <column_name> FROM <table_name>;

4. SUM
 Select SUM <column_name> FROM <table_name>;

5. AVG

Select AVG <column_name> FROM <table_name>;

 PRINT:

AGGREGATE FUNCTIONS

Create table

Insert
COUNT:

MAX:

MIN:

SUM:
AVG:
RESULT:

Thus the SQL queries using aggregate functions were successfully executed and verified.
 EX NO:2B SQL JOINS

DATE :
AIM:

To write SQL queries using joins to combine two or more tables.

Create Table

The CREATE TABLE statement is used to create a relational table

CREATE TABLE table_name

column_name1 data_type [constraints],

column_name1 data_type [constraints],

column_n ame1 data_type [constraints],

……..

);

Insert

To insert a new row in a table.

INSERT INTO table_name VALUES(value1, value2,value3,..........);

JOIN TYPES:

1. INNER JOIN

2. OUTER JOIN

a. LEFT OUTER JOIN

b. RIGHT OUTER JOIN

c. FULL OUTER JOIN

3. SELF JOIN

4. EQUI JOIN

SYNTAX

1. INNER JOIN

SELECT column_name(S)
FROM table 1
INNER JOIN table 2
ON table1.column_name=
table2.column_name;
2. OUTER JOIN

a. LEFT OUTER JOIN

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

b.RIGHT OUTER JOIN

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

c. FULL OUTER JOIN

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

3. SELF JOIN

SELECT column_name(s)
FROM table1,table2
WHERE condition;

4. EQUI JOIN

SELECT column_name(s)
FROM table1,table2
WHERE condition;
 PRINT:

SQL JOINS

CREATE TABLE:
Table 1:

Table 2:

Insert:
Table 1:
Table 2:
INNER JOIN:

Table 1:

Table 2:
OUTER JOIN:
a. LEFT OUTER JOIN :

b. RIGHT OUTER JOIN :

 c. FULL OUTER JOIN:

EQUI JOIN:
SELF JOIN:

Create Table:

Insert:
SELF JOIN:
RESULT:

Thus the SQL queries using SQL joins were successfully executed and verified.
EX NO: 2c NESTED QUERIES
DATE:
AIM:

To create and execute nested SQL queries .

Create Table

The CREATE TABLE statement is used to create a relational table

CREATE TABLE table_name

column_name1 data_type [constraints],

column_name1 data_type [constraints],

column_n ame1 data_type [constraints],

……..

);

Insert

To insert a new row in a table.

INSERT INTO table_name VALUES(value1, value2,value3,..........);

NESTED SUBQUERIES

SELECT column_name [ , column_name ]

FROM table1[ , table2 ]
WHERE column_name OPERATOR
(SELECT column_name[ , column_name ]
FROM table 1[ , table2 ]
[WHERE])
 PRINT:

NESTED QUERIES

Create table:

Insert:
Query:
RESULT

Thus all the above SQL nested queries has been executed successfully and the output was verified.
EX NO:3 VIEW SYNONYMS SEQUENCE

DATE:

AIM:

To write SQL queries using views,sequence,synonyms.

SYNTAX

Create Table

The CREATE TABLE statement is used to create a relational table

CREATE TABLE table_name

column_name1 data_type [constraints],

column_name1 data_type [constraints],

column_n ame1 data_type [constraints],

……..

);

Insert

To insert a new row in a table.

INSERT INTO table_name VALUES(value1, value2,value3,..........);

A. VIEW
CREATE VIEW view_name AS<QUERY EXPRESSION>

INSERT VIEW

INSERT INTO view_name values(value1, value2,value3,..........);

UPDATE VIEW

UPDATE view_name WHERE condition;

DROP VIEW

DROP view_name;

B.SYNONYMS

Create Synonym:
 Create synonym syn_name for table_name;

Delete Synonym:

Drop synonym syn_name;

C. SEQUENCE

CREATE SEQUENCE [SEQUENCE NAME]

[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
[{ORDER n | NOORDER}];

INSERT SEQUENCE

INSERT INTO table_name values(seq_num,values,…..);

Delete SEQUENCE:

Drop SEQUENCE seq_num;

 PRINT:
VIEW SYNONYMS SEQUENCE
A. VIEW

Create Table:

Insert:
1. CREATE VIEW:

2. INSERT INTO VIEW:

3. UPDATE VIEW:
DROP VIEW:

B. SYNONYMS
CREATE TABLE & INSERT:
Create Synonym:

Insert into Synonym:

Drop Synonym:

C. SEQUENCE
Create & insert:

CREATE SEQUENCE:
INSERT SEQUENCE:

DELETE SEQUENCE:
RESULT

Thus the SQL queries using views,sequence ,synonyms were successfully executed and verified.
Ex:No:4 TRIGGERS

Date:

AIM:

To write queries to execute triggers in SQL.

TABLE 1

Create Table

The CREATE TABLE statement is used to create a relational table

CREATE TABLE table_name

column_name1 data_type [constraints],

column_name1 data_type [constraints],

column_n ame1 data_type [constraints],

……..

);

Insert

To insert a new row in a table.

INSERT INTO table_name VALUES(value1, value2,value3,..........);

TABLE 2

Create Table2

The CREATE TABLE statement is used to create a relational table

CREATE TABLE table_name

column_name1 data_type [constraints],

column_name1 data_type [constraints],

column_n ame1 data_type [constraints],

……..

);

Insert

To insert a new row in a table.

INSERT INTO table_name VALUES(value1, value2,value3,..........);

TRIGGER

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;

UPDATE TRIGGER

UPDATE table1_name SET <condition>WHERE <CONDITION>;

 PRINT:

TRIGGERS

Create Table 1:

Insert:
Create Table 2:

TRIGGER:

UPDATE TRIGGER:
RESULLT:

Thus the SQL queries to create triggers were successfully executed and verified.
Ex. No. 5 FUNCTIONS AND PROCEDURES

Date:

AIM:

To write PL/SQL queries to implement functions and procedures.

FUNCTIONS

SYNTAX :

CREATE OR REPLACE FUNCTION FUNCTION_NAME(ARGUMENTS)

RETURN DATATYPE IS

BEGIN

END;

PROCEDURE:

SYNTAX

Create Table

The CREATE TABLE statement is used to create a relational table

CREATE TABLE table_name

column_name1 data_type [constraints],

column_name1 data_type [constraints],

column_n ame1 data_type [constraints],

……..

);

PROCEDURE CREATION

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name[IN | OUT |IN OUT ] type[, . . . ])]

{IS|AS}

BEGIN

<procedure_body>

END procedure_name;
 PRINT:
FUNCTIONS AND PROCEDURES

Functions
Create table & Insert:

FUNCTION CREATION:
FUNCTION CALL:

PROCEDURE

Create table & Insert:

PROCEDURE CREATION:
RESULT:

Thus the PL/SQL queries to create functions and procedures were successfully executed and verified
EXNO:6 IMPLICIT AND EXPLICIT CURSOR

DATE:

AIM:

To write queries to execute cursor in SQL.

SYNTAX

Create Table

The CREATE TABLE statement is used to create a relational table

CREATE TABLE table_name

column_name1 data_type [constraints],

column_name1 data_type [constraints],

column_n ame1 data_type [constraints],

……..

);

Insert

To insert a new row in a table.

INSERT INTO table_name VALUES(value1, value2,value3,..........);

IMPLICIT AND EXPLICIT CURSOR

DECLARE cursor_name
CURSOR
FOR SELECT* FROM table_name
BEGIN
OPEN cursor_connection
FETCH
NEXT/FIRST/LAST/PRIOR/ABSOLUTE
n/RELATIVE n FROM cursor_name
CLOSE cursor_name
END
 PRINT:

IMPLICIT AND EXPLICIT CURSOR

Create table & Insert:

RESULT:

Thus the SQL queries to create implicit and explicit cursor were successfully executed and verified.
Ex no:7 EXCEPTIONAL HANDLING

Date:

AIM:

To write SQL program to handle exceptions.

SYNTAX FOR EXCEPTIONAL HANDLING:

DECLARE
<declaration section>
BEGIN
<executable command (s)>
EXCEPTION
<exception handling goes here>
WHEN exception 1 THEN
Exception1-handling-statements
WHEN exception 2 THEN
Exception2-handling-statements
WHEN exception 3 THEN
Exception3-handling-statements
…………
WHEN others THEN
Exception3-handling-statements
END;
 PRINT:

EXCEPTIONAL HANDLING

Create table & Insert:

Exceptional handling:
RESULT:

Thus the SQL statement that handles exceptions were successfully executed and verified.
 EX NO:8 ER MODELLING
DATE:

AIM:
To design a database Design using ER modelling and normalization to set various constraints.

PROCEDURE:
 The Attributes in the .Entities:
Bus: (ENTITY)

Bus

RESERVATION: (ENTITY)

TICKET: (ENTITY)
CANCELLATION: (ENTITY)
PRINT:

DATABASE DESIGN USING ER MODELING,

NORMALIZATION AND
IMPLEMENTATION FOR ANY APPLICATION

Create table:

UNIQUE CONSTRAINT VIOLATION:

NULL CONSTRAINT VIOLATION:

CHECK CONSTRAINT VIOLATION:

PRIMARY AND FOREIGN KEY CONSTRAINTS:

PRIMARYKEY CONTRAINT VIOLATION:

FOREIGN KEY CONSTRAINT VIOLATION:

RESULT:
Thus a database is designed using ER modelling and normalization to set various constraints.
 Ex.No:9 DATABASE CONNECTIVITY WITH FRONT END TOOL
Date :
AIM:
To connect a database with front end tool Visual Basic.

ALGORITHM :

1) Create a table “ EMPLOYEE1” using the given attributes.

2) Empid ,ename & basicpay are the input attributes.

3) Hra, da, grspay,esi,pf,deduction &netpay are Derived attributes

calculated from BASICPAY.

4) HRA is calculated by using the formula (BasicPay * 0.08)

5) DA is calculated by using the formula (BasicPay * 0.05)

6) Grosspay is calculated by adding Basicpay,HRA & DA (i.e,

Basicpay+HRA+DA)

7) ESI is calculated by using the formula (BasicPay * 0.07)

8) PF is calculated by using the formula (BasicPay * 0.09)

9) Deduction is calculated by adding ESI & PF (i.e, ESI + PF)

10) Netpay is calculated by subtracting grosspay & deduction (i.e,

GRSPAY-DEDUCTION)

TABLE STRUCTURE :
EMPID NUMBER(4)
ENAME VARCHAR2(17)
BASICPAY NUMBER(7)
HRA NUMBER(7)
DA NUMBER(7)
GRSPAY NUMBER(7)
ESI NUMBER(7)
PF NUMBER(7)
DEDUCTION NUMBER(7) NETPAY
NUMBER(7)
 PRINT:

DATABASE CONNECTIVITY WITH FRONT END TOOL

SQL>create table emp(eno number primary key,enamr varchar(20),age number,addr varchar(20),DOB date,phno
number(10)); Table created.
SQL>create table salary(eno number,edesig varchar(10),basic number,da number,hra number,pf number,mc
number,met number,foreign key(eno) references emp);
Table created.
TRIGGER to calculate DA,HRA,PF,MC
SQL> create or replace trigger employ
2 after insert on salary
3 declare
4 cursor cur is select eno,basic from salary;
5 begin
6 for cur1 in cur loop
7 update salary set
8 hra=basic*0.1,da=basic*0.07,pf=basic*0.05,mc=basic*0.03 where hra=0;
9 end loop;
10 end;
11 / Trigger created

PROGRAM FOR FORM 1

Private Sub emp_Click()
Form 2.Show
End Sub
Private Sub exit_Click()
Unload Me
End Sub
Private Sub salary_Click()
Form3.Show
End Sub
PROGRAM FOR FORM 2
Private Sub add_Click()
Adodc1.Recordset.AddNew MsgBox "Record added"
End Sub
Private Sub clear_Click()
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
End Sub
Private Sub delte_Click()
Adodc1.Recordset.Delete MsgBox "Record Deleted"
If Adodc1.Recordset.EOF = True
Then Adodc1.Recordset.MovePrevious
End If
End Sub
Private Sub exit_Click()
Unload Me
End Sub
 Private Sub main_Click()
Form1.Show
End Sub
Private Sub modify_Click()
Adodc1.Recordset.Update
End Sub
PROGRAM FOR FORM 3
Private Sub add_Click()
Adodc1.Recordset.AddNew MsgBox "Record added"
End Sub
Private Sub clear_Click()
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
End Sub
Private Sub delte_Click()
Adodc1.Recordset.Delete MsgBox "Record Deleted"
If Adodc1.Recordset.EOF = True
Then Adodc1.Recordset.MovePrevious
End If
End Sub
Private Sub exit_Click()
Unload Me
End Sub
Private Sub main_Click()
Form1.Show
End Sub
Private Sub modify_Click()
Adodc1.Recordset.Update
End Sub
Output:
RESULT:

Thus a database in Oracle is connected with front end tool Visual Basic.
 EXNO: 10a CASE STUDY USING REAL LIFE DATABASE APPLICATION
DATE: LIBRARY INFORMATION PROCESSING

AIM:

TO execute a case study using real life database application for processing library information using visual basic forms
connected with sql.
ALGORITHM:

1) Create a table “ LIB” using the given attributes.

2) ‘ Id’ is declared as primary key.

3) ‘ No_days’ is a derived attribute which is calculated by subtracting

return date from current date.

4) ‘ Penalty’ is calculated based on the ‘ no_days’ attribute.

TABLE STRUCTURE :
ID NUMBER(3)
NAME VARCHAR2(15)
DEPT VARCHAR2(10)
YEAR NUMBER(4)
BNO NUMBER(5)
BNAME VARCHAR2(20)
AUTHOR VARCHAR2(15)
COPIES NUMBER(2)
ISSUE DATE
 PRINT:
CASE STUDY USING REAL LIFE DATABASE APPLICATION
LIBRARY INFORMATION PROCESSING

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cd As ADODB.Command
Private Sub Add_click()
Dim sql As String
Set cd = New ADODB.Command
sql ="insert into lib values(" & Val(Text1.Text) & ",'" & (Text2.Text) & "','" & (Text3.Text) & "'," &
Val(Text4.Tex) & "," & Val(Text5.Text) & ",'" & (Text6.Text) & "','" & (Text7.Text) & "'," &
Val(Text8.Text) & ",'" & (Text9.Text) & "','" & (Text10.Text) & "','" & (Text11.Text) & "'," &
Val(Text12.Text) & "," & Val(Text13.Text) & ")"
cd.ActiveConnection = cn
cd.CommandText = sql
cd.Execute
Set rs = New ADODB.Recordset
rs.Open "Select * from LIB", cn, adOpenKeyset, adLockOptimistic
MsgBox "Record Added"
End Sub
Private Sub Delete_click()
Dim cd As New ADODB.Command
Dim n As Integer
Dim sql As String
n = Val(InputBox("Enter the ID No. to delete :","input"))
Set cd = New ADODB.Command
sql ="DELETE FROM LIB WHERE ID =" & n
cd.ActiveConnection = cn
cd.CommandText = sql
cd.Execute
MsgBox "Successfully deleted"
Set rs = New ADODB.Recordset
rs.Open "Select * from LIB", cn, adOpenKeyset, adLockOptimistic
End Sub
Private Sub EXIT_Click()
End
End Sub
Private Sub Report_Click()
DataReport1.Show
End Sub
Private Sub search_click()
Dim n As Integer
Dim rs As ADODB.Recordset
On Error GoTo err
n = Val(InputBox("Enter the ID No. to search","input"))
Set rs = New ADODB.Recordset
rs.Open "Select *From LIB where ID=" & n, cn, adOpenKeyset, adLockOptimistic
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
Text5.Text = rs(4)
Text6.Text = rs(5)
Text7.Text = rs(6)
Text8.Text = rs(7)
Text9.Text =
rs(8)
Text10.Text = rs(9)
Text11.Text = rs(10)
Text12.Text = rs(11)
Text13.Text = rs(12)
Exit Sub
err: MsgBox "not found"
End Sub
Private Sub modify_click()
Dim sql As String
Dim n As Integer
n = Val(InputBox("Enter the ID No. to update : ","input"))
Set cd = New ADODB.Command
sql ="Update LIB set ID =" & Val(Text1.Text) & ",NAME='" & (Text2.Text) & "',DEPT='" & (Text3.Text)
& "', YEAR=" & Val(Text4.Text) & ",BNO=" & Val(Text5.Text) & ",BNAME='" & (Text6.Text) &
"',AUTHOR='" & (Text7.Text) & "',COPIES=" & Val(Text8.Text) & ",ISSUE='" & (Text9.Text) &"',RETURN='" &
(Text10.Text) & "',CUR_DATE='" & (Text11.Text) & "', NO_DAYS=" & Val(Text12.Text)& ",PENALTY=" &
Val(Text13.Text) & " where ID&n
cd.ActiveConnection = cn
cd.CommandText = sql
cd.Execute
MsgBox "Modified"
Set rs = New ADODB.Recordset
rs.Open "Select * from LIB", cn, adOpenKeyset, adLockOptimistic
End Sub
Private Sub first_click()
rs.MoveFirst
Call give
End Sub
Private Sub Next_click()
rs.MoveNext
If rs.EOF = True Then
rs.MoveLast
MsgBox ("Reached Last Record
") End If
Call give
End Sub
Private Sub Last_click()
rs.MoveLast
Call give
End Sub
Private Sub previous_click()
rs.MovePrevious
If rs.BOF = True Then
rs.MoveFirst
MsgBox ("Reached First Record ")
End If
Call give
End Sub
Private Sub clear_click()
Text1.Text =" "
Text2.Text =" "
Text3.Text =" "
Text4.Text =" "
Text5.Text =" "
Text6.Text =" "
Text7.Text =" "
Text8.Text =" "
Text9.Text =" "
Text10.Text =" "
Text11.Text =" "
Text12.Text =" "
Text13.Text =" "
End Sub
Private Sub Form_load()
Set cn = New ADODB.Connection
cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Persist Security Info=True"
Set rs = New ADODB.Recordset
rs.Open " Select * from LIB", cn, adOpenKeyset, adLockOptimistic
Call give
End Sub
Private Sub give()
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
Text5.Text = rs(4)
Text6.Text = rs(5)
Text7.Text = rs(6)
Text8.Text = rs(7)
Text9.Text = rs(8)
Text10.Text = rs(9)
Text11.Text = rs(10)
Text12.Text = rs(11)
Text13.Text = rs(12)
End Sub
Private Sub text9_Gotfocus()
Text9.Text = Format(Text9.Text,"Medium date")
End Sub
Private Sub text10_Gotfocus()
Text10.Text = Format(Text10.Text,"medium date")
End Sub
Private Sub text11_Gotfocus()
a$ = Format(Now,"medium date")
Text11.Text = a$
End Sub
Private Sub text12_click()
Dim n As Integer
n = Val(Text11.Text)- Val(Text10.Text)
Text12.Text = n
If n < 0 Then
Text12.Text = 0
MsgBox ("Book is Received before the Due Date ...")
End If
End Sub
Private Sub text13_click()
If Val(Text11.Text) < Val(Text10.Text) Then
Text13.Text = 0
MsgBox ("No Penalty !!!")
Else
Text13.Text = Val(Text12.Text) * 2
End If
End Sub
Output:

Report:
RESULT:
Thus the program for library information processing has been created and generated report successfully.
 Ex.no:10b CASE STUDY USING REAL LIFE DATABASE APPLICATION
Date: STUDENT MARKSHEET PROCESSING USING IMAGES

AIM:
To execute a casestudy using real life database application for student marksheet processing using visual basic
forms connected with sql.

ALGORITHM:

1) Create a table “ STUDENT” using the given attributes.

2) Roll No. is declared as primary key.

3) D.O.B should be less than System date.

4) Marks should be entered between 0 to 100.

5) TOTAL,AVG,GRADE & RESULT are Derived attributes calculated from the

given subject Marks.

6) TOTAL is calculated by adding the marks of 5 subjects (i.e, Eng +

Hindi+ Mat + Sci +Social)

7) AVG is calculated by dividing the TOTAL by 5 (i.e, TOTAL/5)

8) Grade is calculated based on the Avg.

9) Result is declared based on the subject marks.

TABLE STRUCTURE :
RNO NUMBER(5)
NAME VARCHAR2(17)
DOB DATE
SEX VARCHAR2(7)
ENG NUMBER(3)
HINDI NUMBER(3)
MAT NUMBER(3)
SCI NUMBER(3)
SOCIAL NUMBER(3)
TOTAL NUMBER(5)
AVG NUMBER(5,2)
GRADE VARCHAR2(1)
RESULT VARCHAR2(5)

PRINT:
 CASE STUDY USING REAL LIFE DATABASE APPLICATION

STUDENT MARKSHEET PROCESSING USING IMAGES

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cd As ADODB.Command
Private Sub ADD_Click()
Dim sql As String
Set cd = New ADODB.Command
sql = "INSERT INTO STUDENT VALUES (" & Val(Text1.Text) & ",'" & (Text2.Text) & "','" & (Text3.Text) & "','" &
(Text4.Text) & "'," & Val(Text5.Text) & "," & Val(Text6.Text) & "," & Val(Text7.Text) & "," &
Val(Text8.Text) & "," & Val(Text9.Text) & "," & Val(Text10.Text) & "," & Val(Text11.Text) & ",'" & (Text12.Text) & "','"
& (Text13.Text) & "')"
cd.ActiveConnection = cn
cd.CommandText = sql
cd.Execute
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM STUDENT", cn, adOpenKeyset, adLockOptimistic
MsgBox "Record Successfully Added !!!"
End Sub
Private Sub CLEAR_Click()
Text1.Text = " "
Text2.Text = " "
Text3.Text = " "
Text4.Text = " "
Text5.Text = " "
Text6.Text = " "
Text7.Text = " "
Text8.Text = " "
Text9.Text = " "
Text10.Text = " "
Text11.Text = " "
Text12.Text = " "
Text13.Text = " "
End sub
Private Sub DELETE_Click()
Dim cd As New ADODB.Command
Dim n As Integer
Dim sql As String
n = Val(InputBox("Enter the RollNo. to be deleted : ", "input"))
Set cd = New ADODB.Command
sql = "DELETE FROM STUDENT WHERE RNO=" & n
cd.ActiveConnection = cn
cd.CommandText = sql
cd.Execute
MsgBox ("Record Successfully Deleted !!!")
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM STUDENT", cn, adOpenKeyset, adLockOptimistic
End Sub
Private Sub END_Click()
End End Sub
Private Sub FIRST_Click()
rs.MoveFirst
Call give
End Sub
Private Sub Form_Load()
Set cn = New ADODB.Connection
cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Persist Security Info=True"
Set rs = New ADODB.Recordsetrs.Open "SELECT * FROM STUDENT", cn, adOpenKeyset, adLockOptimistic
MsgBox "Record Successfully Added !!!"
End Sub
Private Sub LAST_Click()
rs.MoveLast
Call give
End Sub
Private Sub MODIFY_Click()
Dim sql As String
Dim n As Integer
n = Val(InputBox("Enter the RollNo. to update : ", "input"))
Set cd = New ADODB.Command
sql = "UPDATE STUDENT SET RNO = " & Val(Text1.Text) & ",NAME='" & (Text2.Text) & "',DOB='" &
(Text3.Text) & "', SEX='" & (Text4.Text) & "', ENG=" & Val(Text5.Text) & ", HINDI=" & Val(Text6.Text) & ",MAT=" &
Val(Text7.Text) & ", SCI=" & Val(Text8.Text) & ",SOCIAL=" & Val(Text9.Text) & ",TOTAL=" & Val(Text10.Text) & ", AVG
=" & Val(Text11.Text) & ", GRADE ='" & (Text12.Text) & "',RESULT='" & (Text13.Text) & "' WHERE RNO =" & n
cd.ActiveConnection = cn
cd.CommandText = sql
cd.Execute
MsgBox ("Record Successfully Modified !!!")
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM STUDENT", cn, adOpenKeyset, adLockOptimistic
End Sub
Private Sub NEXT_Click()
rs.MoveNext
If rs.EOF = True Then
rs.MoveLast
MsgBox ("Reached last Record !!!")
End If
Call give
End Sub
Private Sub PREVIOUS_Click()
rs.MovePrevious
If rs.BOF = True Then
rs.MoveFirst
MsgBox ("Reached First Record !!!") End If
Call give
End Sub
Private Sub REPORT_Click()
DataReport1.Show
End Sub
Private Sub SEARCH_Click()
Dim n As Integer
Dim rs As ADODB.Recordset
On Error GoTo err
n = Val(InputBox("Enter the RollNo. to search : ", "input"))
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM STUDENT WHERE RNO=" & n, cn, adOpenKeyset, adLockOptimistic
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
Text5.Text = rs(4)
Text6.Text =
rs(5)
Text7.Text = rs(6)
Text8.Text = rs(7)
Text9.Text = rs(8)
Text10.Text = rs(9)
Text11.Text = rs(10)
Text12.Text = rs(11)
Text13.Text = rs(12)
Exit Sub
err: MsgBox ("Sorry !! Record Not Found ...")
End Sub
Sub give()
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
Text5.Text = rs(4)
Text6.Text = rs(5)
Text7.Text = rs(6)
Text8.Text = rs(7)
Text9.Text = rs(8)
Text10.Text = rs(9)
Text11.Text = rs(10)
Text12.Text = rs(11)
Text13.Text = rs(12)
End Sub
Private Sub Text10_Click()
Dim m1, m2, m3, m4, m5 As Integer
m1 = Val(Text5.Text)
m2 = Val(Text6.Text)
m3 = Val(Text7.Text)
m4 = Val(Text8.Text)
m5 = Val(Text9.Text)
Text10.Text = m1 + m2 + m3 + m4 + m5
Text11.Text = Val(Text10.Text) / 5
End Sub
Private Sub Text12_click()
If (Val(Text10.Text) > 90) Then
Text12.Text = "O"
ElseIf (Val(Text10.Text) > 80) Then
Text12.Text = "A"
ElseIf (Val(Text10.Text) > 70) Then
Text12.Text = "B"
ElseIf (Val(Text10.Text) > 60) Then
Text12.Text = "C"
ElseIf (Val(Text10.Text) > 50) Then
Text12.Text = "D"
ElseIf (Val(Text10.Text) > 40) Then
Text12.Text = "E"
Else
Text12.Text = "F"
End If
End Sub
Private Sub Text13_Click()
If (Val(Text5.Text) >= 40 And Val(Text6.Text) >= 40 And Val(Text7.Text) >= 40 And
Val(Text8.Text) >= 40 And Val(Text9.Text) >= 40) Then
Text13.Text = "PASS"
Else
Text13.Text =
"FAIL" End If
End Sub

Output:

FORM:
RESULT:
Thus the program for student marksheet processing using images has been created and generated report
successfully.
 Ex.no:10c CASE STUDY USING REAL LIFE DATABASE APPLICATION
Date: BANK TRANSACTIONS

AIM:

To execute a casestudy using real life database application for bank transaction using visual basic forms connected with
sql.

ALGORITHM:

1) Create a table “ BANK” using the given attributes.

2) ‘ Accno’ is declared as primary key.

3) ‘ Transaction’ menu is used to perform the transaction i.e., deposit or

withdraw.

4) ‘ No_days’ is calculated by subtracting trans_date from cur_date (i.e.,

cur_date – trans_date).

5) ‘ Intr’
,
‘ tot_int‘ & ‘ tot_amt’ are derived attributes.

6) ‘ Intr’ is calculated based on number of days.

7) ‘ Tot_int‘ is calculated by multiplying initial amount , no. of days and

interest rate (i.e., init_amt * intr*no_days/100).

8) ‘ Tot_amt’ is calculated by adding current balance & total interest (i.e.,

cur_bal + tot_int).

TABLE STRUCTURE :
ACCNO NUMBER(7)
CUS_NAME VARCHAR2(15)
ADDRESS VARCHAR2(15)
PH_NO VARCHAR2(11)
INIT_AMT NUMBER(10,2)
ACC_DATE DATE TRANS_DATE
DATE
DEP_WD NUMBER(10,2)
CUR_BAL NUMBER(10,2)
CUR_DATE DATE
NO_DAYS NUMBER(5)
INTR NUMBER(3)
TOT_INT NUMBER(10,2)
TOT_AMT NUMBER(10,2)
 PRINT:

CASE STUDY USING REAL LIFE DATABASE APPLICATION

BANK TRANSACTIONS

Dim cn As ADODB.Connection
Dim cd As ADODB.Command
Dim rs As ADODB.Recordset
Private Sub ADD_Click()
Dim sql As String
Set cd = New ADODB.Command
sql = "INSERT INTO BANK VALUES(" & Val(Text1.Text) & ",'" & (Text2.Text) & "','" & (Text3.Text) & "','" &
(Text4.Text) & "'," & Val(Text5.Text) & ",'" & (Text6.Text) & "','" & (Text7.Text) & "'," &
Val(Text8.Text) & "," & Val(Text9.Text) & ",'" & (Text10.Text) & "'," & Val(Text11.Text) & "," &
Val(Text12.Text) & "," & Val(Text13.Text) & "," & Val(Text14.Text) & ")"
cd.ActiveConnection =cn
cd.CommandText=sql
cd.Execute
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM BANK", cn, adOpenKeyset, adLockOptimistic MsgBox
"Record Successfully Added !!!"
End Sub
Private Sub
CLEAR_Click() Text1.Text
= " " Text2.Text = " "
Text3.Text = " "
Text4.Text = " "
Text5.Text = " "
Text6.Text = " "
Text7.Text = " "
Text8.Text = " "
Text9.Text = " "
Text10.Text = " "
Text11.Text = " "
Text12.Text = " "
Text13.Text = " "
Text14.Text = " "
End Sub
Private Sub DELETE_Click()
Dim n As Integer
Dim sql As String
Dim cd As New ADODB.Command
n = Val(InputBox("Enter the Acc no. to delete : ", "input"))
Set cd = New ADODB.Command
sql = "DELETE FROM BANK WHERE ACCNO=" & n
cd.ActiveConnection = cn
cd.CommandText = sql
cd.Execute
MsgBox "Record Successfully Deleted !!!"
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM BANK", cn, adOpenKeyset, adLockOptimistic
End Sub
Private Sub END_Click()
End
End Sub
Private Sub FIRST_Click()
rs.MoveFirst
Call give
MsgBox "Reached First Record !!!"
End Sub
Private Sub DELETE_Click()
Dim n As Integer
Dim sql As String
Dim cd As New ADODB.Command
n = Val(InputBox("Enter the Acc no. to delete : ", "input"))
Set cd = New ADODB.Command
sql = "DELETE FROM BANK WHERE ACCNO=" & n
cd.ActiveConnection = cn cd.CommandText = sql cd.Execute
MsgBox "Record Successfully Deleted !!!"
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM BANK", cn, adOpenKeyset, adLockOptimistic
End Sub
Private Sub END_Click()
End
End Sub
Private Sub FIRST_Click()
rs.MoveFirst Call give
MsgBox "Reached First Record !!!"
End Sub
Private Sub Form_Load()
Set cn = New ADODB.Connection
cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Persist Security Info=True"
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM BANK", cn, adOpenKeyset, adLockOptimistic Call give
End Sub
Private Sub give()
Text1.Text = rs(0) Text2.Text = rs(1) Text3.Text = rs(2) Text4.Text = rs(3) Text5.Text = rs(4) Text6.Text =
rs(5) Text7.Text = rs(6) Text8.Text = rs(7) Text9.Text = rs(8) Text10.Text = rs(9) Text11.Text = rs(10)
Text12.Text = rs(11) Text13.Text = rs(12) Text14.Text = rs(13)
End Sub
Private Sub LAST_Click()
rs.MoveLast
Call give
MsgBox "Reached Last Record !!!"
End SuB
Private Sub MODIFY_Click()
Dim sql As String
Dim n As Integer
n = Val(InputBox("Enter the acc no. to update : ", "input"))
Set cd = New ADODB.Command
sql = "UPDATE BANK SET ACCNO =" & Val(Text1.Text) & ",CUS_NAME ='" & (Text2.Text) & "',ADDRESS='" &
(Text3.Text) & "',PH_NO='" & (Text4.Text) & "',INIT_AMT=" & Val(Text5.Text) & ",ACC_DATE='" &
(Text6.Text) & "',TRANS_DATE='" & (Text7.Text) & "',DEP_WD=" & Val(Text8.Text) & ", CUR_BAL =" &
Val(Text9.Text) & ",CUR_DATE='" & (Text10.Text) & "',NO_DAYS=" &
Val(Text11.Text) & ",INTR=" & Val(Text12.Text) & ",TOT_INT=" & Val(Text13.Text) & ",TOT_AMT=" &
Val(Text14.Text) & " where accno=" & n
cd.ActiveConnection = cn
cd.CommandText = sql
cd.Execute
MsgBox "Record Successfully Modified !!!"
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM BANK", cn, adOpenKeyset, adLockOptimistic
End Sub

OUTPUT:
Report:
RESULT:
Thus the program for bank transactions has been created and generated report successfully.
Ex.no:10d CASE STUDY USING REAL LIFE DATABASE APPLICATION
Date: PAYROLL PROCESSING

AIM:

To execute a casestudy using real life database application for payroll processing using visual basic forms connected with
sql.

ALGORITHM:

1) Create a table “ EMPLOYEE1” using the given attributes.

2) Empid ,ename & basicpay are the input attributes.

3) Hra, da, grspay,esi,pf,deduction &netpay are Derived attributes

calculated from BASICPAY.

4) HRA is calculated by using the formula (BasicPay * 0.08)

5) DA is calculated by using the formula (BasicPay * 0.05)

6) Grosspay is calculated by adding Basicpay,HRA & DA (i.e,

Basicpay+HRA+DA)

7) ESI is calculated by using the formula (BasicPay * 0.07)

8) PF is calculated by using the formula (BasicPay * 0.09)

9) Deduction is calculated by adding ESI & PF (i.e, ESI + PF)

10) Netpay is calculated by subtracting grosspay & deduction (i.e,

GRSPAY-DEDUCTION)

TABLE STRUCTURE :
EMPID NUMBER(4)
ENAME VARCHAR2(17)
BASICPAY NUMBER(7)
HRA NUMBER(7)
DA NUMBER(7)
GRSPAY NUMBER(7)
ESI NUMBER(7)
PF NUMBER(7)
DEDUCTION NUMBER(7)
NETPAY NUMBER(7)
 PRINT:
Payroll processing
SQL>create table emp(eno number primary key,enamr varchar(20),age number,addr varchar(20),DOB date,phno
number(10)); Table created.
SQL>create table salary(eno number,edesig varchar(10),basic number,da number,hra number,pf number,mc
number,met number,foreign key(eno) references emp);
Table created.
TRIGGER to calculate DA,HRA,PF,MC
SQL> create or replace trigger employ
2 after insert on salary
3 declare
4 cursor cur is select eno,basic from salary;
5 begin
6 for cur1 in cur loop
7 update salary set
8 hra=basic*0.1,da=basic*0.07,pf=basic*0.05,mc=basic*0.03 where hra=0;
9 end loop;
10 end;
11 / Trigger created

PROGRAM FOR FORM 1

Private Sub emp_Click()
Form 2.Show
End Sub
Private Sub exit_Click()
Unload Me
End Sub
Private Sub salary_Click()
Form3.Show
End Sub
PROGRAM FOR FORM 2
Private Sub add_Click()
Adodc1.Recordset.AddNew MsgBox "Record added"
End Sub
Private Sub clear_Click()
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
End Sub
Private Sub delte_Click()
Adodc1.Recordset.Delete MsgBox "Record Deleted"
If Adodc1.Recordset.EOF = True
Then Adodc1.Recordset.MovePrevious
End If
End Sub
Private Sub exit_Click()
Unload Me
End Sub
Private Sub main_Click()
Form1.Show
End Sub
Private Sub modify_Click()
Adodc1.Recordset.Update
End Sub
PROGRAM FOR FORM 3
Private Sub add_Click()
Adodc1.Recordset.AddNew MsgBox "Record added"
End Sub
Private Sub clear_Click()
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
End Sub
Private Sub delte_Click()
Adodc1.Recordset.Delete MsgBox "Record Deleted"
If Adodc1.Recordset.EOF = True
Then Adodc1.Recordset.MovePrevious
End If
End Sub
Private Sub exit_Click()
Unload Me
End Sub
Private Sub main_Click()
Form1.Show
End Sub
Private Sub modify_Click()
Adodc1.Recordset.Update
End Sub
Output:
RESULT:
Thus the program for payroll processing has been created and generated report successfu
Ex.no:10e CASE STUDY USING REAL LIFE DATABASE APPLICATION
Date: INVENTORY

AIM:

To execute a casestudy using real life database application for inventory using visual basic forms connected with sql.

ALGORITHM:

1. Start the program.

2. Create a table in oracle for inventory with required fields.

3.Design a form in visual basic 6.0 with required fields.

4. Create a connection using ADODB.connection using ADODB recordset using ADODB.recordset.

5.calculate total and current balance and perform insertion, deletion, modification in form window.

6.Generate general report for inventory system.

7.Stop the program.

TABLE STRUCTURE:
Suppliercode varchar(9)
Suppliername varchar(9)
Itemcode varchar(9)
Itemname varchar(9)
Quantity varchar(9)
Unitprice number(9)
 PRINT:

INVENTORY

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset
Dim sto_c1n As ADODB.Connection
Dim sto_rs As ADODB.Recordset
Private Sub add_Click()
Adodc1.Recordset.AddNew
Set DataGrid1.DataSource = Adodc1
DataGrid1.Refresh
End Sub
Private Sub delete_Click()
Adodc1.Recordset.delete
MsgBox " reocord deleted"
End Sub
Private Sub exit_Click()
If MsgBox("Close Applect?", vbQuestion + vbYesNo, "Confirm") = vbYes Then
Unload Me
End If
End Sub
Private Sub first_Click()
Adodc1.Recordset.MoveFirst
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
End Sub
Private Sub Form1_Load()
Set sto_cn = New ADODB.Connection
sto_cn.Open "cust_dsn", "scott", "tiger"
Set sto_rs = New ADODB.Recordset
sto_rs.Open "select * from stock", sto_cn, adOpenDynamic, adLockPessimistic
Set conn = New ADODB.Connection
conn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=dvora;Persist Security Info=True"
Set rs = New ADODB.Recordset
rs.ActiveConnection = conn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Source = "SELECT * FROM pur_order"
rs.Open
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
End Sub
Private Sub last_Click()
Adodc1.Recordset.MoveLast
DataGrid1.Refresh
End Sub
Private Sub previous_Click()
If Adodc1.Recordset.BOF = True Then
rs.MoveLast
Adodc1.Recordset.MoveLast
Else
Adodc1.Recordset.MovePrevious
End If
DataGrid1.Refresh
End Sub
Private Sub report_Click()
DataReport1.Show
End Sub

Output:
Form 2:

Report:
RESULT:
Thus the program for inventory has been created and generated report successfully.
Ex.no:10f CASE STUDY USING REAL LIFE DATABASE APPLICATION
Date: PURCHASE ORDER PROCESSING
AIM:

To execute a casestudy using real life database application for purchase order processing using visual basic forms connected
with sql.

ALGORITHM:

1. Start the program.

2. Create a table in oracle for purchase order processing with required fields.

3. Design a form in visual basic 6.0 with required fields.

4. Create a connection using ADODB.connection using ADODB recordset using ADODB.recordset.

5. calculate total and current balance and perform insertion, deletion, modification in form window.

6. Generate general report for purchase order processing.

7. Stop the program.

TABLE STRUCTURE:
Suppliercode varchar(9)
Suppliername varchar(9)
Itemcode varchar(9)
Itemname varchar(9)
Quantity varchar(9)
Unitprice number(9)
 PRINT:

Purchase order list

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset
Dim sto_c1n As ADODB.Connection
Dim sto_rs As ADODB.Recordset
Private Sub add_Click()
Adodc1.Recordset.AddNew
Set DataGrid1.DataSource = Adodc1
DataGrid1.Refresh
End Sub
Private Sub delete_Click()
Adodc1.Recordset.delete
MsgBox " reocord deleted"
End Sub
Private Sub exit_Click()
If MsgBox("Close Applect?", vbQuestion + vbYesNo, "Confirm") = vbYes Then
Unload Me
End If
End Sub
Private Sub first_Click()
Adodc1.Recordset.MoveFirst
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
End Sub
Private Sub Form1_Load()
Set sto_cn = New ADODB.Connection
sto_cn.Open "cust_dsn", "scott", "tiger"
Set sto_rs = New ADODB.Recordset
sto_rs.Open "select * from stock", sto_cn, adOpenDynamic, adLockPessimistic
Set conn = New ADODB.Connection
conn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=dvora;Persist Security Info=True"
Set rs = New ADODB.Recordset
rs.ActiveConnection = conn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Source = "SELECT * FROM pur_order"
rs.Open
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
End Sub
Private Sub last_Click()
Adodc1.Recordset.MoveLast
DataGrid1.Refresh
End Sub

Private Sub previous_Click()

If Adodc1.Recordset.BOF = True Then
rs.MoveLast
Adodc1.Recordset.MoveLast
Else
Adodc1.Recordset.MovePrevious
End If
DataGrid1.Refresh
End Sub
Private Sub report_Click()
DataReport1.Show
End Sub

Output form1:
Form2:

Report:
RESULT:
Thus the program for purchase order processing has been created and generated report successfully.