manual -dbms

Ex. No: 1 STUDY OF DDL COMMANDS

AIM: To study the usage of various data definition language commands.

PROCEDURE:

DDL is used to create an object (e.g. table) ,alter the structure of an object and also to drop the object created.

The DDL commands are i) Create tableii) Alter tableiii) Truncate tableiv) Drop table

1. Create Table It is used to create a table with a certain fields.

Syntax create table tablename ( field1 datatype1 ,field2 datatype2,……..);

2. Alter Table It is used to add or delete a field from the table. Syntax

To add a field alter table tablename add fieldname datatype;

To drop a field alter table tablename drop column fieldname;

To modify the datatype alter table tablename modify fieldname datatype;

3. Truncate Table It is used to delete the records from table. Syntax truncate table tablename; 4. Drop Table It is used to delete the table.

Syntax drop table tablename;

OUTPUT

1. Create Table Command

SQL> create table students(sname varchar(10),regno number,sem number);

Table created.

SQL> desc students; Name Null? Type ---------------------------- -------- --------------------------- SNAME VARCHAR2(10) REGNO NUMBER SEM NUMBER

2. Alter Table Command

Add Command

SQL> alter table students add dept varchar(5);

Table altered.

SQL> desc students; Name Null? Type --------------------------------- --------------------------- SNAME VARCHAR2(10) REGNO NUMBER SEM NUMBER DEPT VARCHAR2(5)

Drop Command

SQL> alter table students drop column dept;

Table altered.

Modify Command

SQL> alter table students modify sem varchar(5);

Table altered.

3. Truncate Command

SQL> insert into students values('anitha',5001,'3');

1 row created.

SQL> insert into students values('priya',5002,'5');

1 row created.

SQL> select * from students;

SNAME REGNO SEM---------- ---------- -----anitha 5001 3priya 5002 5

SQL> truncate table students;

Table truncated.

4. Drop Command

SQL> drop table students; Table dropped.

SQL> select * from students;select * from students *ERROR at line 1:ORA-00942: table or view does not exist

RESULT: Thus the DDL commands are studied and executed.

Ex. No: 2 STUDY OF DML COMMANDS

AIM: To study the usage of various data manipulation language commands.

PROCEDURE:

DML are used to query and manipulate existing objects like tables.

The DML commands are i) Insert Commandii) Select Commandiii) Update Commandiv) Delete Command

1. Insert Command It is used to insert records into the table. Syntax insert into tablename values (values…………..);

2. Select Command It is used to display the records from the table which satisfies the condition.

Syntax select * from tablename where condition;

3. Update Command It is used to update the records which are already present in the table.

Syntax update tablename set fieldname = value where condition;

4. Delete Command It is used to delete the records from the table with some condition.

Syntax delete from tablename where condition;

OUTPUT: 1. Insert Command

SQL> desc students; Name Null? Type ----------------------------------------- -------- ---------------------------- SNAME VARCHAR2(10) REGNO NUMBER SEM NUMBER DEPT VARCHAR2(5)

SQL> insert into students values('anitha',5001,5,'cse');

1 row created.

SQL> insert into students values('&name',&regno,&sem,'&dept');Enter value for name: arunEnter value for regno: 5002Enter value for sem: 5Enter value for dept: cseold 1: insert into students values('&name',&regno,&sem,'&dept')new 1: insert into students values('arun',5002,5,'cse')

1 row created.

SQL> /Enter value for name: bharathiEnter value for regno: 5003Enter value for sem: 5Enter value for dept: itold 1: insert into students values('&name',&regno,&sem,'&dept')new 1: insert into students values('bharathi',5003,5,'it')

1 row created.

2. Select Command

SNAME REGNO SEM DEPT---------- --------- ------- --------anitha 5001 5 csearun 5002 5 csebharathi 5003 5 itanitha 5004 3 cse

SQL> select sname , regno from students;

SNAME REGNO---------- ----------anitha 5001arun 5002bharathi 5003anitha 5004

SQL> select distinct sname from students;

SNAME----------anithaarunbharathi

SQL> select * from students where regno >= 5002;

SQL> select * from students where regno >= 5002 and dept = 'cse';

SNAME REGNO SEM DEPT---------- --------- ------- --------arun 5002 5 cseanitha 5004 3 cse3. Update Command

SQL> update students set dept = 'cse' where sname = 'bharathi';

1 row updated.

SQL> update students set sem= 5 where regno = 5004;

1 row updated.

SNAME REGNO SEM DEPT---------- --------- ------- --------anitha 5001 5 csearun 5002 5 csebharathi 5003 5 cseanitha 5004 5 cse

4. Delete Command

SQL> delete from students where regno = 5004;

1 row deleted.

SNAME REGNO SEM DEPT---------- --------- ------- --------anitha 5001 5 csearun 5002 5 csebharathi 5003 5 cse

SQL> delete from students;

3 rows deleted.

no rows selected

RESULT: Thus the DML commands are studied and executed.Ex. No: 3 STUDY OF DCL COMMANDS

AIM: To study the usage of various data control language commands.

PROCEDURE:

DCL provides user with privileges commands. The owner of database objects (E.g. table) has the sole authority over them.

i) Grant Command ii) Revoke Command

1) Grant Command

To grant the privileges on a particular table to another user.

Syntax:

Grant privileges on tablename to user;

2) Revoke Command To revoke the privileges on a particular table from another user. Syntax: Revoke privileges on tablename from user;

3) Create user To create new user.

Syntax: Create user username identified by another username;

OUTPUT:

1) Creating User

SQL> create user system1 identified by system;

User created.

SQL> desc students; Name Null? Type ----------------------------------------- -------- ---------------------------- SNAME VARCHAR2(10) REGNO NUMBER SEM NUMBER DEPT VARCHAR2(5)

1 row created.

SQL> insert into students values('&name',&regno,&sem,'&dept');Enter value for name: arunEnter value for regno: 5002Enter value for sem: 5Enter value for dept: cseold 1: insert into students values('&name',&regno,&sem,'&dept')new 1: insert into students values('arun',5002,5,'cse')

1 row created.

SQL> /Enter value for name: bharathiEnter value for regno: 5003Enter value for sem: 5Enter value for dept: itold 1: insert into students values('&name',&regno,&sem,'&dept')new 1: insert into students values('bharathi',5003,5,'it')

1 row created.

2) Grant Command

SQL> grant select , update on students to system1;

Grant succeeded.

SQL> grant all on students to system1;

Grant succeeded.

3) Revoke Command

SQL> revoke update,select on students from system1;

Revoke succeeded.

SQL> revoke all on students from system1;

Revoke succeeded.

RESULT: Thus the DCL commands are studied and executed.

Ex. No: 4 STUDY OF TCL COMMANDS

AIM: To study the usage of various transaction control language commands.

PROCEDURE:

A transaction is a logical unit of work. All changes made to the database can be referred to as a transaction. Transaction changes can be made permanent to a database only if they are committed.

TCL commands arei) Commitii) Save pointiii) Rollback

1) Commit This command is used to end a transaction. It is used made the transaction changes permanent to the database. This command also erases all save points in the transaction.

Syntax: commit;

2) Save point Save points are like markers to divide a very lengthy transaction to smaller ones. They are used to identify a point in transaction to which user can later rollback.

Syntax: savepoint savepoint_id;

3) Rollback A rollback command is used to undo the work done in the current transaction. User can either rollback the entire transaction so that all changes made by SQL statements are undone , or rollback a transaction to a save point .

Syntax: rollback; or rollback to savepoint _id;

OUTPUT:

1) Savepoint SQL> select * from students;

SNAME REGNO SEM DEPT---------- --------- ------- -------anitha 5001 5 csepriya 5002 5 csekumar 5001 5 cseanita 4001 5 cse

SQL> update students set sname = 'christy' where regno = 4001;

1 row updated.

SQL> insert into students values('abirami',5003,5,'it');

1 row created.

SQL> savepoint mark1;

Savepoint created.

SNAME REGNO SEM DEPT---------- --------- ------- -------anitha 5001 5 csepriya 5002 5 csekumar 5001 5 csechristy 4001 5 cseabirami 5003 5 it

SQL> insert into students values('abirami',5003,5,'cse');

1 row created.

SQL> savepoint mark2;

Savepoint created.

SNAME REGNO SEM DEPT---------- --------- ------- -------anitha 5001 5 csepriya 5002 5 csekumar 5001 5 csechristy 4001 5 cseabirami 5003 5 itabirami 5003 5 cse

6 rows selected.

2) Rollback

SQL> rollback to mark2;

Rollback complete.

5 rows selected.

SQL> rollback;

Rollback complete.

SQL> rollback to mark1;

rollback to mark1*ERROR at line 1:ORA-01086: savepoint 'MARK1' never established

SNAME REGNO SEM DEPT---------- --------- ------- -------anitha 5001 5 csepriya 5002 5 csekumar 5001 5 cseanita 4001 5 cse

SQL> update students set sname = 'christy' where regno = 4001;

1 row updated.

SQL> insert into students values('abirami',5003,5,'it');

1 row created.

3) Commit

SQL> commit;

Commit complete.

SQL> rollback;

Rollback complete.

RESULT: Thus the TCL commands are studied and executed

Ex. No: 5 BUILT IN FUNCTIONS

AIM: To study the usage of various built in functions.

PROCEDURE:

There are five built in functions1) Date functions2) Numeric functions3) Character functions4) Conversion functions5) Miscellaneous functions

1) Date functions It operates on date values producing output which belongs to date datatype. The various date functions are

i) add_months

The add_months date functions returns a date after adding a specified date with the specified number of months.

Syntax: add_months(d,n) where d – date , n – number of months ii) last_day

This functions returns the date corresponding to the last day of the month. Syntax: last_day(d) iii) months_between

This function is used to find out the number of months between two dates. Syntax: months_between(d1,d2)

iv) sysdate It will display system date. v) next_day it will display which is the next corresponding day.

Syntax: next_day(d,day)

2) Numeric functions

Numeric function accepts numeric input and returns numeric values as output.

The various numeric functions are

S.No Function1 abs (n)2 ceil(n)3 cos(n)4 cosh(n)5 exp(n)6 floor(n)7 power(m,n)8 mod(m,n)9 round(m,n)10 trunc(m,n)11 sqrt(n)12 sign(n)13 ln(5)

3) Conversion functions

It converts a value from one data type to another.

S.No Function

1 to_char(‘d ’ ,’dd “of” month yyyy’)

2 to_date(‘d ‘ , ‘mm – dd- yyyy’)

3 To_number(‘n’)

Where d –date , n –number

4) Character functions

Character functions accept character input and return character or number values.

5) Miscellaneous functions

S.No Function1 uid2 user3 vsize

S.No Function1 initcap(char)2 lower(char)3 upper(char)4 ltrim(char,set)5 rtrim(char,set)6 translate(char,from,to)7 replace( char, str , repstr)8 substr(char,m,n)9 chr(n)10 length(char)

OUTPUT:

1) Date function

SQL> select add_months('12-aug-07',3) from dual;

ADD_MONTH---------12-NOV-07

SQL> select sysdate from dual;

SYSDATE---------12-AUG-07

SQL> select last_day('12-aug-07') from dual;

LAST_DAY(---------31-AUG-07

SQL>select months_between('12-dec-06','12-aug-07') from dual;

MONTHS_BETWEEN('12-DEC-07','12-AUG-07')--------------------------------------- 4

2) Numeric functions

SQL> select abs(-5) from dual;

ABS(-5)

---------- 5

SQL> select ceil(44.777) from dual;

CEIL(44.777)------------ 45

SQL> select cos(0) from dual;

COS(0)---------- 1

SQL> select cosh(0) from dual;

COSH(0)---------- 1

SQL> select exp(4) from dual;

EXP(4)---------- 54.59815

SQL> select power(2,2) from dual;

POWER(2,2)---------- 4

SQL> select floor(44.777) from dual;

FLOOR(44.777)------------- 44

SQL> select mod(3,2) from dual;

MOD(3,2)---------- 1

SQL> select round(45.268,2) from dual;

ROUND(45.268,2)--------------- 45.27

SQL> select trunc(45.268,2) from dual;

TRUNC(45.268,2)--------------- 45.26

SQL> select sqrt(2) from dual;

SQRT(2)----------1.41421356

SQL> select ln(5) from dual;

LN(5)----------1.60943791

SQL> select sign(-5) from dual;

SIGN(-5)---------- -1

3) Conversion functions

SQL> select to_char(sysdate,'ddth "of" mm yyyy') from dualTO_CHAR(SYSDATE,----------------12th of 08 2007

SQL> select to_date('january 27 1981','mm-dd-yyyy') from dual;

TO_DATE('---------27-JAN-81

SQL> select to_number('258') from dual;

TO_NUMBER('258')---------------- 258

4) Character functions

SQL> select initcap('data') from dual;

INIT----Data

SQL> select lower('DATA') from dual;

LOWE----data

SQL> select upper('data') from dual;

UPPE----DATA

SQL> select ltrim('database','data') from dual;

LTRI----base

SQL> select rtrim('database','base') from dual;

RTR---dat

SQL> select translate('database','a','e') from dual;

TRANSLAT--------

detebese

SQL> select replace('jack','j','bl') from dual;

REPLA-----black

SQL> select substr('abcdef',3,2) from dual;

SU--cd

SQL> select chr(67) from dual;

SQL> select lpad('dbms',15,'*') from dual;

LPAD('DBMS',15,---------------***********dbms

SQL> select rpad('dbms',15,'*') from dual;

RPAD('DBMS',15,---------------dbms***********

SQL> select length('database') from dual;

LENGTH('DATABASE')------------------ 8

SQL> select * from tab1;

SNAME SNO---------- ---------

anitha 1guna 3lavan 4divesh 6dinesh 8vennila 2man 9

7 rows selected.

SQL>select('The regno of '|| sname || ' is '|| sno) from tab1;

('THEREGNOOF'||SNAME||'IS'||SNO)---------------------------------------------------------------------The regno of anitha is 1The regno of guna is 3The regno of lavan is 4The regno of divesh is 6The regno of dinesh is 8The regno of vennila is 2The regno of man is 9

7 rows selected.

5) Miscellaneous functions

SQL> select uid from dual;

UID--------- 5

SQL> select user from dual;

USER------------------------------SYSTEM

SQL> select vsize('hello') from dual;

VSIZE('HELLO')-------------- 5

RESULT: Thus the various built in functions in SQL are studied and executed.

Ex. No: 6 GROUP FUNCTIOINS & SET OPERATIONS

AIM: To study the usage of various group functions & set operations.

PROCEDURE: Group function

A group function returns a result based on a group of rows.

The group functions arei) Avg functionii) Min functioniii) Max functioniv) Sum functionv) Count function

Set Operations Set operators combine the results of two queries into a single one.

The set operators arei) unionii) union alliii) intersect iv) minus

OUTPUT:

Group functions

SQL> select * from eemp;

ENAME SAL DEPT -------------- --------- ---------Remya 55000 it guna 60000 cseani 650000 ecelav 550000 csekan 40000 cseven 45000 it

6 rows selected.

SQL> select avg(sal) from eemp;

AVG(SAL)---------233333.33

SQL>select min(sal) from eemp;

MIN(SAL)--------- 40000

SQL> select max(sal) from eemp;

MAX(SAL)--------- 650000

SQL> select sum(sal) from eemp;

SUM(SAL)--------- 1400000

Group by clause

SQL> select sum(sal) from eemp group by dept;

SUM(SAL) --------- 650000 650000 100000

Set Operations

SNAME SNO---------- ---------anitha 1guna 3lavan 4divesh 6dinesh 8vennila 2

6 rows selected.

ENAME ENO---------- ---------anitha 1vennila 2guna 3

govind 6divesh 5raja 8

3 rows selected.

SQL> select sname from tab1 union select ename from tab2;

SNAME----------anithadineshdiveshgovindgunalavanrajavennila

8 rows selected.

SQL> select sname from tab1 union all select ename from tab2;

SNAME----------anitha guna lavan divesh dinesh vennila anitha vennila guna govind divesh raja

12 rows selected.

SQL> select sname from tab1 intersect select ename from tab2;

SNAME----------anithadiveshgunavennila

SQL> select sname from tab1 minus select ename from tab2;

SNAME----------dineshlavan

RESULT: Thus the usage of various group functions & set operations are studied & executed.

Ex. No :7 JOINS & SUBQUERIES

AIM: To study the usage of joins and sub queries.

PROCEDURE: Joins Joins are used to combine the data spread across tables

Types of joins

i) Natural joinii) Inner joiniii) Outer join

a) Right outer joinb) Left outer joinc) Full outer join

Sub Queries

A sub query is a query inside another query.

1) Set membership i) in clause ii) not in clause

2) Set comparisoni) some clause

ii) all clause

OUTPUT:

SNAME SNO---------- ---------anitha 1guna 3lavan 4vennila 2

ENAME ENO---------- ---------anitha 1vennila 2guna 3raja 8

NATURAL JOIN

SQL> select * from tab1 natural join tab2;

SNAME SNO ENAME ENO---------- --------- ---------- ---------anitha 1 anitha 1guna 3 anitha 1lavan 4 anitha 1vennila 2 anitha 1anitha 1 vennila 2guna 3 vennila 2lavan 4 vennila 2vennila 2 vennila 2anitha 1 guna 3

guna 3 guna 3lavan 4 guna 3vennila 2 guna 3anitha 1 raja 8guna 3 raja 8lavan 4 raja 8vennila 2 raja 8

16 rows selected.INNER JOIN

SQL> select * from tab1 inner join tab2 on tab1.sname = tab2.ename;

SNAME SNO ENAME ENO---------- --------- --------- ---------anitha 1 anitha 1guna 3 guna 3vennila 2 vennila 2

OUTER JOIN

SQL> select * from tab1 right outer join tab2 on tab1.sname = tab2.ename;

SNAME SNO ENAME ENO---------- --------- ---------- --------anitha 1 anitha 1guna 3 guna 3vennila 2 vennila 2 raja 8

SQL> select * from tab1 left outer join tab2 on tab1.sname = tab2.ename;

SNAME SNO ENAME ENO---------- --------- ---------- ---------anitha 1 anitha 1vennila 2 vennila 2guna 3 guna 3lavan 4

SQL> select * from tab1 full outer join tab2 on tab1.sname = tab2.ename;

SNAME SNO ENAME ENO---------- --------- ---------- ---------

anitha 1 anitha 1vennila 2 vennila 2guna 3 guna 3lavan 4 raja 8

SUB QUERIES

Set membership

SNAME SNO---------- ---------anitha 1guna 3lavan 4vennila 2

ENAME ENO---------- ---------anitha 1vennila 2guna 3raja 8

SQL> select sname from tab1 where sname in (select ename from tab2);

SNAME----------anithagunavennila

not in

SQL> select sname from tab1 where sname not in (select ename from tab2);

SNAME----------lavan

Set comparison

SQL> select * from eemp;

ENAME SAL DEPT---------- ------------ ---------------guna 60000 cseani 650000 ecelav 550000 csekan 40000 cseven 45000 it

SQL>select ename , sal from eemp where sal > some ( select sal from eemp where dept = 'cse');

ENAME SAL------------ - ---------guna 60000ani 650000lav 550000ven 45000

SQL>select ename, sal from eemp where sal > all (select sal from eemp where dept = 'cse');

ENAME SAL---------- ----------ani 650000

RESULT: Thus the usage of joins and sub queries are studied and executed.

Ex. No: 8 PL/SQL

AIM: To study the usage of Procedural Language/ SQL.

PROCEDURE:

PL/SQL extends SQL by adding control structures found in other procedural language. A PL/SQL block can be divided into three parts i) A declarative part ii) An executable part iii) An exception handling part Structure of PL/SQL block

DECLARE Declarations BEGIN Executable statements Exception Handlers END;

Control Structures i) if then ii) simple loop iii) while loop iv) for loop

OUTPUT

Simple Loop

SQL> declare a number:=100; begin loop a :=a+25; exit when a =250; dbms_output.put_line(a); end loop; end; SQL> / 125 150 175 200 225 PL/SQL procedure successfully completed.

While loop

SQL > declare a number:=100; begin while a < 250 loop a:=a+25; dbms_output.put_line(a); end loop; end;SQL> / 125 150 175 200 225 250

PL/SQL procedure successfully completed.

For Loop

SQL> declare a number:=100; begin for i in 1..5 loop a:=a+25; dbms_output.put_line(a); end loop; end;SQL> / 125 150 175 200 225

If then

SQL>declare a number:=100; begin if a > 100 then a := a - 50; else a := a+ 50; end if; dbms_output.put_line(a); end;SQL> / 150

RESULT: Thus the PL/SQL basics are studied and executed.

Ex. No: 9 PROCEDURE

AIM: To execute PL/ SQL procedure in SQL PLUS.

PROCEDURE:

A procedure is a subprogram that performs a specific action.

Syntax To create a procedure

create or replace procedure proc_name( arguments) is local declarations begin executable statements exception exception handler end;

To execute a procedure

SQL> exec proc_name(parameters);

OUTPUT:

SQL>create table store(icode varchar2(20),iname varchar2(20),iqty number,iprice number);Table Created ;

SQL> desc store; Name Null? Type ------------------------------- -------- ---- ICODE VARCHAR2(20) INAME VARCHAR2(20) IQTY NUMBER IPRICE NUMBER

1. INSERT PROCEDURE:

To Create procedure

SQL>ed v;

create or replace procedure iiadd(v1 varchar,v2 varchar,v3 number,v4 number) aspcode varchar(20);pname varchar(20);pqty number;pprice number;beginpcode :=v1;pname :=v2;pqty :=v3;pprice :=v4;insert into store values(pcode,pname,pqty,pprice);end;

SQL> @ v; 13 /

Procedure created.

To Execute procedure:

SQL> exec iiadd('p','book',10,100);

SQL> select * from store;

ICODE INAME IQTY IPRICE-------------------- -------------------- --------- ---------p book 10 100

SQL> exec iiadd('c','pen',5,10);

PL/SQL procedure successfully completed

ICODE INAME IQTY IPRICE-------------------- -------------------- --------- ---------p book 10 100c pen 5 10

SQL> exec iiadd('r','scale',8,3);

PL/SQL procedure successfully completed

ICODE INAME IQTY IPRICE-------------------- -------------------- --------- ---------p book 10 100c pen 5 10r scale 8 3

2. DELETE PROCEDURE

SQL> ed p;create or replace procedure iidel(v1 varchar) aspcode varchar(20);beginpcode :=v1;delete from store where icode =pcode;dbms_output.put_line (‘record deleted’);end;SQL> @ p; 11 /

Procedure created.

SQL> exec iidel(‘c'); record deletedPL/SQL procedure successfully completed.

ICODE INAME IQTY IPRICE-------------------- -------------------- --------- ---------p book 10 100r scale 8 3

3. SELECT &UPDATE PROCEDURE:

SQL> ed u;create or replace procedure iiup(v1 varchar,v2 number)aspcode varchar(20);price number ;prprice number;beginpcode :=v1;price :=v2;select price into prprice from store where icode=pcode;update store set iprice=prprice where icode=pcode;dbms_output.put_line('record updated successfully');

SQL> @ u; 12 /

Procedure created.

SQL> exec iiup('r',12);record updated successfully

PL/SQL procedure successfully completed.SQL> select * from store;

ICODE INAME IQTY IPRICE-------------------- -------------------- --------- ---------p book 10 100r scale 8 12

RESULT: Thus the PL/SQL procedure are studied and executed.

Ex. No: 10 FUNCTIONS

AIM: To execute PL/ SQL function in SQL PLUS.

PROCEDURE: A function is a subprogram that computes a value.

Syntax To create a function create or replace function func_name ( arguments) return datatype is local declarations begin executable statements exception exception handler end;

OUTPUT:

SQL> desc product; Name Null? Type ----------------------------------------- -------- --------------------------- PCODE VARCHAR2(5) PNAME VARCHAR2(10) QTY_HAND NUMBER(5) QTY_MIN NUMBER(5)

SQL> select * from product;

PCODE PNAME QTY_HAND QTY_MIN----- ---------- ---------- ----------rc rice 85 100sg sugar 50 50wh wheat 200 100

SQL> ed reorder;

create or replace function reorder_func(code varchar)return number isreorderqty number;minqty number;handqty number;beginselect qty_hand,qty_min into handqty,minqty from product where pcode=code;if handqty < minqty thenreorderqty := minqty -handqty;return reorderqty;elsereorderqty := 0;return reorderqty;end if;end;

SQL> @ reorder; 17 /

Function created.

Executing the function:

SQL> declare a varchar(5); b number; begin a:=&a; b:=reorder_func(a); dbms_output.put_line('quantity to be ordered is' || b); end; /

Enter value for a: 'sg'old 5: a:=&a;new 5: a:='sg';quantity to be ordered is 0

SQL> /Enter value for a: 'rc'old 5: a:=&a;new 5: a:='rc';quantity to be ordered is 15

RESULT: Thus the PL/SQL functions are studied and executed.

Ex. No: 11 EMBEDDED SQL

Aim:To implement embedded SQL concept using JAVA as Front End and MS-Access

as Back End for Database Manipulation.

Procedure:

1. Create MS-Access table named employee with it’s as emp.

2. Create a data source by establishing JDBC/ODBC connection.

3. Develop a java application mainjdbc.java as

i) Create an object myjdbc mj= new myjdbc();

ii) In ,try catch block

* Declare methods for connection ,executing the query statement and record set.

*Established JDBC-ODBC connection (name,drivermanager).

*Call the query to be executed rs=st.executeQuery(“ select * from mployee” )

4. Run the java application.

5. Stop the execution

SOURCE CODE:

import java.sql.*;

import java.io.*;

class myjdbc

String ename,eid ,esal;

Connection con;

Statement st;

ResultSet rs;

myjdbc()

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

con=DriverManager.getConnection("jdbc:odbc:eemp","","");

st=con.createStatement();

rs=st.executeQuery("select * from employee");

while(rs.next())

System.out.println("empname:" +rs.getString(1));

System.out.println("empid:" +rs.getString(2));

System.out.println("salary:" +rs.getString(3));

System.out.println("\n");

catch(Exception e){}

class mainjdbc

public static void main(String args[])

myjdbc mj=new myjdbc();

OUTPUT: E:\>cd jdk1.5..0

E:\jdk1.5.0>cd bin

E:\jdk1.5.0\bin>javac mainjdbc.java

E:\jdk1.5.0\bin>java mainjdbc

Empname:RaniEmpid:1Salary:4000

Empname:RamEmpid:2Salary:8000

RESULT:

Thus the embedded SQL concept using JAVA as Front End and MS-Access as Back End for Database Manipulation was executed.

Ex. No: 11 TRIGGERS

AIM: To write trigger program to maintain the inventory level.

PROCEDURE: A database trigger is stored procedure that is fixed when an insert, update, or delete statement is issued against the associated table.

Syntax for creating triggers Create or replace trigger trigger_name [before/after] [insert/update/delete] on table_name [for each statement/ for each row ] [ when condition] ;

EXNO:12 CURSORS

To implement cursor for performing payroll processing

PROCEDURE:

1. Create the employee & attendance table with required fields.

2. Enter the values into the table.

3. Create a salary table into which the payroll process is to be

executed.

4. Declare a cursor to fetch values from employee table.

5. Select the workdays,months,years from attendance table for each

employee number in the employee table

6. Calculate the salary for each employee and insert it in the salary

table.

OUTPUT:

SQL> create table employee(eno number,ename varchar2(20),esal number,egrade varchar2(20));Table created.

SQL>insert into employee values(1, arun, 5000, b );1 row created.SQL>insert into employee values( 2,raju, 6522, b); 1 row created.SQL>insert into employee values( 3, balu,10000, a);1 row created. SQL>create table attendence( eno number,workdays number,months number,years number);Table created.

SQL>insert into attendance values(1,15, 6, 2);1 row created.SQL>insert into attendance values(2,56 , 9, 2);1 row created.SQL>insert into attendance values(3, 88,4, 3);1 row created.

SQL>create table salary(eno number,ename varchar2(20),egrade varchar2(20) netsal number,workdays number,months number,years number);Table created.

Implementing Cursor:

SQL>declare cursor emp isselect eno,ename,esal,egrade from employee;empname varchar2(20);empno number(4):=0;empsal number(7,2):=0;empgrade varchar2(1);netsalary number(7,2):=0;wd number(2):=0;m number(2):=0;y number(4):=0;

beginopen emp;loopfetch emp into empno,empname,empsal,empgrade;exit when emp%notfound;select workdays,months,years into wd,m,y from attendence where eno = empno;netsalary :=(empsal/30) * wd;insert into salary1 values(empno,empname,empgrade,netsalary,wd,m,y);end loop;close emp;commit;end;

SQL> select * from employee;

ENO ENAME ESAL EGRADE ------- -------------- -------- ------------- 1 arun 5000 b 2 raju 6522 b 3 balu 10000 a

SQL> select * from attendence;

ENO WORKDAYS MONTHS YEARS --------- --------- --------- --------- 1 15 6 2 2 56 9 2 3 88 4 3

SQL> select * from salary;

ENO ENAME E NETSAL WORKDAYS MONTHS YEARS --------- - ------------ ------ - - --------- --------- -------- ---------- 1 arun b 7611 15 6 2 2 raju b 8524.2 56 9 2 3 balu a 133660.27 88 4 3

3 rows selected.

RESULT:

Thus the cursor is implemented to perform payroll process.

EXNO:13 TRIGGERS

To write a Trigger program to maintain the inventory level.

PROCEDURE:

1. Create a stock table with required fields.

2. Create procedure for inserting values into the table.

3. Insert the values into the table.

4. Create procedure for updating the table.

5. Create a trigger point to catch the illegal entry.

6. Update the table using update procedure.

OUTPUT:

SQL> select * from prod6;

PCODE PNAME QTYHAND QTYSALES----- ------ --------- ---------p1 pen 522 52p2 pencil 855 56

Trigger

SQL> create or replace trigger trr1 before update on prod6 for each row begin if :new.qtysales > :old.qtyhand then raise_application_error ( -20001,'quantity is less'); end if; end; /

SQL> Trigger created.

Update Procedure

SQL> ed t2;

create or replace procedure stock_update1(x1 varchar , x4 number) as tqty number(20); thand number(20); begin select qtyhand,qtysales into thand,tqty from prod6 where pcode = x1; if SQL%FOUND then update prod6 set qtyhand = thand - x4 where pcode = x1; update prod6 set qtysales = tqty + x4 where pcode = x1; dbms_output.put_line('record updated'); end if; end;

SQL> @ t2; 12 /

Procedure created.

SQL> exec stock_update1('p1',5);record updated

SQL> select * from prod6;

PCODE PNAME QTYHAND QTYSALES----- ------ --------- ---------p1 pen 517 57p2 pencil 855 56

SQL> exec stock_update1('p1',600);begin stock_update1('p1',600); end;

*ERROR at line 1:ORA-20001: quantity is lessORA-06512: at "SYSTEM.TRR1", line 3ORA-04088: error during execution of trigger 'SYSTEM.TRR1'ORA-06512: at "SYSTEM.STOCK_UPDATE1", line 8ORA-06512: at line 1

RESULT:

Thus trigger is implemented and executed for maintaining inventory Level.

EXNO: 14 EMBEDDED SQL

AIM:To implement Embedded SQL concept using JAVA as Front End and MS-Access

as Back End for Database Manipulation.

PROCEDURE:

1. Create MS-Access table named employee with it’s as emp.

2. Create a data source by establishing JDBC/ODBC connection.

3. Develop a java application mainjdbc.java as

i) Create an object myjdbc mj= new myjdbc();

ii) In ,try catch block

* Declare methods for connection ,executing the query statement and

record set.

* Established JDBC-ODBC connection (name,drivermanager).

*Call the query to be executed rs=st.executeQuery(“ select * from mployee” )

4. Run the java application.

5. Stop the execution

SOURCE CODE:

import java.sql.*;

import java.io.*;

class myjdbc

String ename,eid ,esal;

Connection con;

Statement st;

ResultSet rs;

myjdbc()

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

con=DriverManager.getConnection("jdbc:odbc:eemp","","");

st=con.createStatement();

rs=st.executeQuery("select * from employee");

while(rs.next())

System.out.println("empname:" +rs.getString(1));

System.out.println("empid:" +rs.getString(2));

System.out.println("salary:" +rs.getString(3));

System.out.println("\n");

catch(Exception e){}

class mainjdbc

public static void main(String args[])

myjdbc mj=new myjdbc();

OUTPUT: E:\>cd jdk1.5..0

E:\jdk1.5.0>cd bin

E:\jdk1.5.0\bin>javac mainjdbc.java

E:\jdk1.5.0\bin>java mainjdbc

Empname:RaniEmpid:1Salary:4000

Empname:RamEmpid:2Salary:8000

RESULT:

Thus Embedded SQL is implemented successfully.

EXNO: 15 BANK MANAGEMENT SYSTEM

To design and implement a banking system using VisualBasic as Front End and

oracle as Back End.

PROCEDURE:

1. Create a database for banking with required fields using SQL.

2. Create forms in VB in required format.

3. Select adodc component and also appropriate control to the form wherever the

control is needed.

4. Write the appropriate coding for each form created in VB.

5. Run the forms for results.

SOURCE CODE FOR FORM1:

Private Sub ACCOUNT_Click()Form2.ShowEnd Sub

Private Sub exit_Click()Unload MeEnd Sub

Private Sub transcation_Click()Form3.ShowEnd Sub

Private Sub INSERT_Click()Adodc1.Recordset.AddNewEnd Sub

Private Sub transa_Click()Form3.ShowEnd Sub

Private Sub UPDATE_Click()Adodc1.Recordset.updateMsgBox "record updated successfully"End Sub

Private Sub DELETE_Click()Adodc1.Recordset.deleteMsgBox "record Deleted"Adodc1.Recordset.MoveNextIf Adodc1.Recordset.EOF = True ThenAdodc1.Recordset.MovePrevious

End IfEnd Sub

Private Sub HOME_Click()Form1.ShowEnd Sub

Private Sub clear_Click()Text1.Text = ""Text2.Text = ""Text3.Text = ""End Sub

Private Sub DEPOSIT_Click()Dim s As Strings = InputBox("enter the amount to be deposited")Text2.Text = Val(Text2.Text) + Val(s)A = Text2.TextMsgBox "CURRENT BALANCE IS Rs." + Str(A)Adodc1.Recordset.UpdateEnd Sub

Private Sub WITHDRAW_Click()Dim s As Strings = InputBox("enter the amount to be deleted")Text2.Text = Val(Text2.Text) - Val(s)A = Text2.TextMsgBox "CURRENT BALANCE IS Rs." + Str(A)Adodc1.Recordset.UpdateEnd Sub

Private Sub ACCOUNT_Click()Form2.ShowEnd Sub

Private Sub HOME_Click()

Form1.ShowEnd Sub

Private Sub clear_Click()Text1.Text = ""Text2.Text = ""End Sub

SCREEN SHOTS:

FORM 1[HOME PAGE]

FORM 2:[ACCOUNT DETAILS]

FORM 3: [Transaction –Deposit]

FORM 3:[Transaction after deposit]

RESULT:

Thus Bank Management system is designed and implemented successfullly

EXNO: 16 PAYROLL PROCESSING SYSTEM

AIM:To design and implement a Payroll Processing System using VisualBasic as Front

End and oracle as Back End.

PROCEDURE:

1. Create a database for payroll processing with required fields using SQL.

2. Create forms in VB in required format.

3. Select adodc component and also appropriate control to the form wherever the

control is needed.

4. Write the appropriate coding for each form created in VB.

5. Run the forms for results.

SOURCE CODE FOR FORM 1:

Private Sub emp_Click()Form2.ShowEnd Sub

Private Sub Exit_Click()Unload MeEnd Sub

Private Sub salary_Click()Form3.ShowEnd Sub

Private Sub add_Click()Adodc1.Recordset.AddNewMsgBox "record added"End Sub

Private Sub clear_Click()Text1.Text = " "Text2.Text = " "Text3.Text = " "Text4.Text = " "Text5.Text = " "End Sub

Private Sub delete_Click()

Adodc1.Recordset.deleteMsgBox "record deleted"Adodc1.Recordset.MoveNextIf Adodc1.Recordset.EOF = True ThenAdodc1.Recordset.MovePreviousEnd If End Sub

Private Sub main_Click()Form1.ShowEnd Sub

Private Sub modify_Click()Adodc1.Recordset.UpdateEnd Sub

Private Sub calc_Click()Dim g, n As Doubleg = (Val(Text2.Text) + Val(Text2.Text) * (Val(Text3.Text) / 100) + Val(Text2.Text) * (Val(Text5.Text) / 100))Text8.Text = gn = g - (Val(Text5.Text) + Val(Text6.Text))Text7.Text = nAdodc1.Recordset.UpdateMsgBox " NET SALARY IS" + Str(n)End Sub

SCREEN SHOTS:

FORM 1:[HOME PAGE]

FORM 2 [EMPLOYEE DETAIL]

FORM 3 :[SALARY CALCULATION]

manual -dbms

Documents

dbms lab manual casestudy

dbms manual

dbms lab manual -...

lab manual for dbms lab - world college of · pdf filedbms...

dbms complete lab manual

dbms lab manual - 1

dbms lab manual 11

dbms lab manual 10csl58

dbms lab manual

dbms lab manual rgpv

lab manual dbms

dbms manual-1

dbms lab manual r09

dbms manual ii it

new dbms lab manual

dbms lab manual 10csl57

dbms new manual

cs2258 dbms manual

dbms final aug2011 de2011 dbms lab manual