Exercise II
Employee and
department table
SQL> create table employee(empno number(5)primary
key,ename varchar(20),job varchar(20),mgr number(5),doj date,salary
number(7),deptno number(2),age number(3));
Table created.
SQL> create table department(deptno number(2) primary
key,dname varchar(20),location varchar(20));
Table created.
SQL> alter table employee add constraint fk foreign
key(deptno) references department(deptno);
Table altered.
SQL>
SQL> desc employee;
Name Null? Type
-----------------------------------------
-------- ----------------------
EMPNO NOT NULL
NUMBER(5)
ENAME
VARCHAR2(20)
JOB VARCHAR2(20)
MGR
NUMBER(5)
DOJ
DATE
SALARY
NUMBER(7)
DEPTNO NUMBER(2)
AGE
NUMBER(3)
SQL> desc department;
Name
Null? Type
-----------------------------------------
-------- ----------------------
DEPTNO NOT NULL
NUMBER(2)
DNAME
VARCHAR2(20)
LOCATION
VARCHAR2(20)
SQL>
SQL> insert into department
values(&deptno,'&dname','&location');
Enter value for deptno: 1
Enter value for dname: account
Enter value for location: madurai
old 1: insert into
department values(&deptno,'&dname','&location')
new 1: insert into
department values(1,'account','madurai
1 row created.
SQL> /
Enter value for deptno: 2
Enter value for dname: sales
Enter value for location: madurai
old 1: insert into
department values(&deptno,'&dname','&location')
new 1: insert into
department values(2,'sales','madurai
1 row created.
SQL> /
Enter value for deptno: 3
Enter value for dname: production
Enter value for location: sivakasi
old 1: insert into
department values(&deptno,'&dname','&location')
new 1: insert into
department values(3,'production','sivakasi')
1 row created.
SQL> select * from department;
DEPTNO DNAME LOCATION
---------- -------------------- --------------------
1
account madurai
2 sales madurai
3
production sivakasi
SQL>
SQL> insert into employee
values(&empno,'&ename','&job',&mgr,'&doj',&salary,&deptno,&age);
Enter value for empno: 101
Enter value for ename: siva
Enter value for job: manager
Enter value for mgr: 0
Enter value for doj: 01-mar-2005
Enter value for salary: 24000
Enter value for deptno: 1
Enter value for age: 23
old 1: insert into
employee
values(&empno,'&ename','&job',&mgr,'&doj',&salary,&deptno,&age)
new 1: insert into
employee values(101,'siva','manager',0,'01-mar-2005',24000,1,23)
1 row created.
SQL> /
Enter value for empno: 102
Enter value for ename: mani
Enter value for job: clerk
Enter value for mgr: 101
Enter value for doj: 01-sep-2005
Enter value for salary: 10000
Enter value for deptno: 1
Enter value for age: 34
old 1: insert into
employee values(&empno,'&ename','&job',&mgr,'&doj',&salary,&deptno,&age)
new 1: insert into
employee values(102,'mani','clerk',101,'01-sep-2005',10000,1,34)
1 row created.
SQL> /
Enter value for empno: 103
Enter value for ename: sarpa
Enter value for job: manager
Enter value for mgr: 0
Enter value for doj: 23-mar-2006
Enter value for salary: 250000
Enter value for deptno: 3
Enter value for age: 24
old 1: insert into
employee
values(&empno,'&ename','&job',&mgr,'&doj',&salary,&deptno,&age)
new 1: insert into
employee values(103,'sarpa','manager',0,'23-mar-2006',250000,3,24)
1 row created.
SQL> /
Enter value for empno: 104
Enter value for ename: rajan
Enter value for job: supervisor
Enter value for mgr: 103
Enter value for doj: 10-oct-2007
Enter value for salary: 14000
Enter value for deptno: 3
Enter value for age: 30
old 1: insert into
employee
values(&empno,'&ename','&job',&mgr,'&doj',&salary,&deptno,&age)
new 1: insert into
employee values(104,'rajan','supervisor',103,'10-oct-2007',14000,3,30)
1 row created.
SQL> /
Enter value for empno: 105
Enter value for ename: viji
Enter value for job: manager
Enter value for mgr: 0
Enter value for doj: 04-jun-2000
Enter value for salary: 20000
Enter value for deptno: 2
Enter value for age: 25
old 1: insert into
employee values(&empno,'&ename','&job',&mgr,'&doj',&salary,&deptno,&age)
new 1: insert into
employee values(105,'viji','manager',0,'04-jun-2000',20000,2,25)
1 row created.
SQL> /
Enter value for empno: 106
Enter value for ename: ramya
Enter value for job: salesrep
Enter value for mgr: 105
Enter value for doj: 01-jul-2001
Enter value for salary: 14000
Enter value for deptno: 2
Enter value for age: 27
old 1: insert into
employee
values(&empno,'&ename','&job',&mgr,'&doj',&salary,&deptno,&age)
new 1: insert into
employee values(106,'ramya','salesrep',105,'01-jul-2001',14000,2,27)
1 row created.
SQL>
SQL> select * from employee;
EMPNO ENAME JOB MGR DOJ
---------- -------------------- --------------------
---------- ---------
SALARY
DEPTNO AGE
---------- ---------- ----------
101 siva manager 0 01-MAR-05
24000 1 23
102 mani clerk 101 01-SEP-05
10000 1 34
103 sarpa manager 0 23-MAR-06
250000 3 24
EMPNO ENAME JOB MGR DOJ
---------- -------------------- -------------------- ----------
---------
SALARY DEPTNO AGE
---------- ---------- ----------
104 rajan supervisor 103 10-OCT-07
14000 3 30
105 viji manager 0 04-JUN-00
20000 2 25
106 ramya salesrep 105 01-JUL-01
14000 2 27
EMPNO ENAME JOB MGR DOJ
---------- -------------------- --------------------
---------- ---------
SALARY DEPTNO AGE
---------- ---------- ----------
107 kalai worker 103 02-APR-03
1600 2 26
7 rows selected.
SQL>SQL> select ename from employee where salary
between 1500 and 3500;
ENAME
--------------------
kalai
SQL> select ename from employee where mgr in(101,103);
ENAME
--------------------
mani
rajan
kalai
SQL>
SQL> select ename from employee where ename like 'k%' or
ename like 'r%';
ENAME
--------------------
rajan
ramya
kalai
SQL> select distinct job from employee;
JOB
--------------------
clerk
manager
salesrep
supervisor
worker
SQL> select ename,salary,salary+0.15*salary from
employee;
ENAME
SALARY SALARY+0.15*SALARY
-------------------- ---------- ------------------
siva
24000 27600
mani
10000 11500
sarpa
250000 287500
rajan
14000 16100
viji
20000 23000
ramya
14000 16100
kalai
1600 1840
7 rows selected.
SQL> select count(distinct job) from employee;
COUNT(DISTINCTJOB)
------------------
5
SQL>SQL> select max(salary)-min(salary) from employee;
MAX(SALARY)-MIN(SALARY)
-----------------------
248400
SQL>SQL> select ename,salary from employee where job
like 'm%' and salary>(select min(salary) from employee);
ENAME
SALARY
-------------------- ----------
siva
24000
sarpa
250000
viji
20000
SQL> select dname,count(ename) from employee,department
where employee.deptno=department.deptno group by dname;
DNAME
COUNT(ENAME)
-------------------- ------------
account 2
production 2
sales 3
SQL> select ename from employee where job='manager' and
extract(year from doj)>2000;
ENAME
--------------------
siva
sarpa
SQL> select ename from employee where job='manager' and
age>35 and salary>100000;
ENAME
--------------------
Sarpa
Exercise II
Employee, department,
project, workson and dependent
SQL> create table deptmt(depno number(3) primary
key,dname varchar(20),mssn number(4),deptloc varchar(20));
Table created.
SQL> create table emp(essn number(4) primary key,sname
varchar(20),mname varchar(10),lname varchar(20),dob date,address
varchar(25),gender varchar(1),salary number(5),depno number(3) references
deptmt (depno));
Table created.
SQL> create table project(prono number(3) primary
key,proname varchar(20),proloc varchar(20),depno number(3) references deptmt(depno));
Table created.
SQL> create table workson(essn number(4) references
emp(essn),prono number(5) references project,hoursperweek number(2));
Table created.
SQL> create table dependent(essn number(4) references
emp(essn),depname varchar(20),gender varchar(1),dob date,relationship
varchar(20));
Table created.
SQL> insert into deptmt
values(&depno,'&dname',&mssn,'&deptloc');
Enter value for depno: 1
Enter value for dname: research
Enter value for mssn: 1001
Enter value for deptloc: madurai
old 1: insert into
deptmt values(&depno,'&dname',&mssn,'&deptloc')
new 1: insert into
deptmt values(1,'research',1001,'madurai
1 row created.
SQL> /
Enter value for depno: 2
Enter value for dname: sales
Enter value for mssn: 2001
Enter value for deptloc: sivakasi
old 1: insert into
deptmt values(&depno,'&dname',&mssn,'&deptloc')
new 1: insert into
deptmt values(2,'sales',2001,'sivakasi')
1 row created.
SQL> /
Enter value for depno: 3
Enter value for dname: accounts
Enter value for mssn: 3001
Enter value for deptloc: theni
old 1: insert into
deptmt values(&depno,'&dname',&mssn,'&deptloc')
new 1: insert into
deptmt values(3,'accounts',3001,'theni')
1 row created.
SQL> insert into emp
values(&essn,'&sname','&mname','&lname','&dob','&address','&gender',&salary,&de
pno);
Enter value for essn: 1001
Enter value for sname: john
Enter value for mname: b
Enter value for lname: smith
Enter value for dob: 10-jan-1990
Enter value for address: madurai
Enter value for gender: m
Enter value for salary: 20000
Enter value for depno: 1
old 1: insert into
emp
values(&essn,'&sname','&mname','&lname','&dob','&address','&gender',&salary
new 1: insert into
emp values(1001,'john','b','smith','10-jan-1990','madurai
1 row created.
SQL> /
Enter value for essn: 2001
Enter value for sname: sam
Enter value for mname: b
Enter value for lname: raj
Enter value for dob: 18-aug-1981
Enter value for address: sivakasi
Enter value for gender: m
Enter value for salary: 30000
Enter value for depno: 2
old 1: insert into
emp
values(&essn,'&sname','&mname','&lname','&dob','&address','&gender',&salary
new 1: insert into
emp values(2001,'sam','b','raj','18-aug-1981','sivakasi','m',30000,2)
1 row created.
SQL> /
Enter value for essn: 3001
Enter value for sname: siva
Enter value for mname: e
Enter value for lname: raja
Enter value for dob: 10-dec-1992
Enter value for address: theni
Enter value for gender: m
Enter value for salary: 15000
Enter value for depno: 3
old 1: insert into
emp values(&essn,'&sname','&mname','&lname','&dob','&address','&gender',&salary
new 1: insert into
emp values(3001,'siva','e','raja','10-dec-1992','theni','m',15000,3)
1 row created.
SQL> insert into project
values(&prono,'&proname','&proloc',&depno);
Enter value for prono: 101
Enter value for proname: securityservice
Enter value for proloc: madurai
Enter value for depno: 1
old 1: insert into
project values(&prono,'&proname','&proloc',&depno)
new 1: insert into
project values(101,'securityservice','madurai
1 row created.
SQL> /
Enter value for prono: 102
Enter value for proname: renewablesource
Enter value for proloc: sivakasi
Enter value for depno: 1
old 1: insert into
project values(&prono,'&proname','&proloc',&depno)
new 1: insert into
project values(102,'renewablesource','sivakasi',1)
1 row created.
SQL> /
Enter value for prono: 103
Enter value for proname: payrollprocessing
Enter value for proloc: theni
Enter value for depno: 3
old 1: insert into
project values(&prono,'&proname','&proloc',&depno)
new 1: insert into project
values(103,'payrollprocessing','theni',3)
1 row created.
SQL> insert into workson
values(&essn,&prono,&hoursperweek);
Enter value for essn: 1001
Enter value for prono: 101
Enter value for hoursperweek: 5
old 1: insert into
workson values(&essn,&prono,&hoursperweek)
new 1: insert into
workson values(1001,101,5)
1 row created.
SQL> /
Enter value for essn: 1001
Enter value for prono: 102
Enter value for hoursperweek: 8
old 1: insert into
workson values(&essn,&prono,&hoursperweek)
new 1: insert into
workson values(1001,102,8)
1 row created.
SQL> /
Enter value for essn: 3001
Enter value for prono: 103
Enter value for hoursperweek: 7
old 1: insert into
workson values(&essn,&prono,&hoursperweek)
new 1: insert into
workson values(3001,103,7)
1 row created.
SQL> insert into dependent
values(&essn,'&depname','&gender','&dob','&relationship');
Enter value for essn: 1001
Enter value for depname: kalai
Enter value for gender: f
Enter value for dob: 29-jan-1984
Enter value for relationship: daughter
old 1: insert into
dependent
values(&essn,'&depname','&gender','&dob','&relationship')
new 1: insert into
dependent values(1001,'kalai','f','29-jan-1984','daughter')
1 row created.
SQL> /
Enter value for essn: 2001
Enter value for depname: prakash
Enter value for gender: m
Enter value for dob: 02-mar-1990
Enter value for relationship: son
old 1: insert into
dependent
values(&essn,'&depname','&gender','&dob','&relationship')
new 1: insert into
dependent values(2001,'prakash','m','02-mar-1990','son')
1 row created.
SQL> /
Enter value for essn: 3001
Enter value for depname: fathima
Enter value for gender: f
Enter value for dob: 03-june-1976
Enter value for relationship: wife
old 1: insert into
dependent values(&essn,'&depname','&gender','&dob','&relationship')
new 1: insert into
dependent values(3001,'fathima','f','03-june-1976','wife')
1 row created.
SQL> desc deptmt;
Name
Null? Type
-----------------------------------------
-------- --------------------
DEPNO NOT NULL
NUMBER(3)
DNAME
VARCHAR2(20)
MSSN
NUMBER(4)
DEPTLOC VARCHAR2(20)
SQL> desc emp;
Name
Null? Type
-----------------------------------------
-------- --------------------
ESSN NOT NULL
NUMBER(4)
SNAME
VARCHAR2(20)
MNAME
VARCHAR2(10)
LNAME
VARCHAR2(20)
DOB
DATE
ADDRESS
VARCHAR2(25)
GENDER
VARCHAR2(1)
SALARY
NUMBER(5)
DEPNO
NUMBER(3)
SQL> desc project;
Name
Null? Type
-----------------------------------------
-------- --------------------
PRONO NOT NULL
NUMBER(3)
PRONAME VARCHAR2(20)
PROLOC
VARCHAR2(20)
DEPNO
NUMBER(3)
SQL> desc workson;
Name
Null? Type
-----------------------------------------
-------- --------------------
ESSN
NUMBER(4)
PRONO
NUMBER(5)
HOURSPERWEEK
NUMBER(2)
SQL> desc dependent;
Name
Null? Type
-----------------------------------------
-------- --------------------
ESSN
NUMBER(4)
DEPNAME
VARCHAR2(20)
GENDER
VARCHAR2(1)
DOB
DATE
RELATIONSHIP
VARCHAR2(20)
SQL> select * from emp;
ESSN SNAME MNAME LNAME DOB
---------- -------------------- ----------
-------------------- ---------
ADDRESS
G SALARY DEPNO
------------------------- - ---------- ----------
1001 john b smith 10-JAN-50
2001 sam b raj 18-AUG-81
sivakasi
m 30000 2
3001 siva e raja 10-DEC-92
new houston
SQL> select * from deptmt;
DEPNO DNAME MSSN DEPTLOC
---------- -------------------- ----------
--------------------
1
research 1001 madurai
2 sales 2001 sivakasi
3
accounts 3001 theni
SQL> select * from project;
PRONO
PRONAME PROLOC DEPNO
---------- -------------------- -------------------- ----------
101
securityservice madurai
102
productx stafford 1
103
payrollprocessing theni 3
SQL> select * from workson;
ESSN PRONO HOURSPERWEEK
---------- ---------- ------------
1001 101 5
1001 102 8
3001 103 7
SQL> select * from dependent;
ESSN
DEPNAME G DOB RELATIONSHIP
---------- -------------------- - ---------
--------------------
1001 kalai f 29-JAN-84 daughter
2001 sam m 02-MAR-90 son
3001
fathima f 03-JUN-76 wife
1. Retrieve the
birthdate and address of the employee(s) whose name is ‘John B. Smith’
SQL> select dob,address from emp where sname='john' and
mname='b' and lname='smith';
DOB ADDRESS
--------- -------------------------
10-JAN-90 madurai
2. Retrieve the name and address of all employees who work for the
‘Research’ department
SQL> select sname,lname,address from emp,deptmt where
dname='research' and emp.depno=deptmt.depno;
SNAME
LNAME ADDRESS
-------------------- --------------------
-------------------------
john smith madurai
3. For every project located in ‘Stafford ’,
list the project number, the controlling department number, and the department
manager’s last name, address, and birthdate.
SQL> select p.prono,d.depno,e.lname,e.address,e.dob from
emp e,project p,deptmt d where p.proloc='stafford' and e.depno=d.depno and
e.depno=p.depno and d.mssn=e.essn;
PRONO DEPNO LNAME ADDRESS DOB
---------- ---------- --------------------
------------------------- ---------
102 1 smith madurai
4.Retrieve all
employees whose address is in Houston
SQL> select sname,lname from emp where address like '%houston
SNAME
LNAME
-------------------- --------------------
siva
raja
5. Show the resulting salaries if every employee working on the
‘ProductX’ project is given a 10 percent raise.
SQL> select sname,lname,1.1*salary from emp e,workson
w,project p where e.essn=w.essn and p.prono=w.prono and p.proname='productx';
SNAME
LNAME 1.1*SALARY
-------------------- -------------------- ----------
john
smith 22000
6. Retrieve all employees in department 5 whose salary is between
$30,000 and $40,000.
SQL> select * from emp where salary between 30000 and
40000 and depno=5;
ESSN SNAME MNAME LNAME DOB
---------- -------------------- ----------
-------------------- ---------
ADDRESS
G SALARY DEPNO
------------------------- - ---------- ----------
3001 siva e raja 10-DEC-92
new houston
7. Retrieve a list of employees and the projects they are working
on, ordered by department and, within each department, ordered alphabetically
by last name, first name.
SQL> select
d.dname,e.lname,e.sname,p.proname from deptmt d,emp e,workson w,project p where
e.depno=d.depno and e.essn=w.essn and p.prono=w.prono order by
d.dname,e.lname,e.sname;
DNAME
LNAME SNAME
-------------------- --------------------
--------------------
PRONAME
--------------------
accounts
raja siva
payrollprocessing
research
smith john
securityservice
research
smith john
productx
8. Retrieve the name
of each employee who has a dependent with the same first name and same sex as
the employee
SQL> select e.sname,e.lname from emp e where e.essn
in(select essn from dependent where e.sname=depname and e.gender=gender);
SNAME
LNAME
-------------------- --------------------
sam
raj
9.
Find the sum of the salaries of all employees of the ‘Research’ department, as
well as the maximum salary, the minimum salary, and the average salary in this
department.
SQL> select
sum(salary),max(salary),min(salary),avg(salary) from emp,deptmt where
deptmt.depno=emp.depno and deptmt.dname='research';
SUM(SALARY) MAX(SALARY) MIN(SALARY) AVG(SALARY)
----------- ----------- ----------- -----------
20000 20000 20000 20000
10. Count the number
of distinct salary values in the database
SQL> select count(distinct salary) from emp;
COUNT(DISTINCTSALARY)
---------------------
3
11. Retrieve the
total number of employees in the company and the number of employees in the
‘Research’ department.
SQL> select count(*) from emp,deptmt where deptmt.depno=emp.depno
and deptmt.dname='research';
COUNT(*)
----------
1
12. Find all employees who were born during the 1950s
SQL> select sname,lname from emp where extract(year from
dob)=1950;
SNAME
LNAME
-------------------- --------------------
john
smith
Exercise IV ( Triggers)
Create the following table
1.
Books(ISBN,Title,Author,Price,Pub_year)
2.
Stock(ISBN,Stock_quantity,reorderlevel,reorderquantity)
3.
Customers(Custid,custname,address)
4.
Orders(orderno,custid,orderdate)
5.
Orderlist(orderno,ISBN,quantity,totalprice,shipdate)
SQL> create table books(isbn number(5)
primary key,title varchar(25),author varchar(20),price number (5,2),pubyear
number(4));
Table created.
SQL> create table stock(isbn number(5)
references books(isbn));
Table created.
SQL> alter table stock add(stockquantity
number(4),reorderlevel number(3),reorderquantity number(4));
Table altered.
SQL> create table customers(custid
number(5) primary key,cname varchar(25),address varchar(25));
Table created.
SQL> create table orders(orderno
number(4) primary key,custid number(5) references customers(custid) ,orderdate
date);
Table created.
SQL> create table orderlist(orderno
number(4) references orders(orderno),isbn number(5) primary key, quantity
number(5),totalprice number(5,2),shipdate date);
Table created.
SQL> insert into books
values(&isbn,'&title','&author',&price,&pubyear);
Enter value for isbn: 10001
Enter value for title: dbms
Enter value for author: date
Enter value for price: 500
Enter value for pubyear: 2005
old
1: insert into books
values(&isbn,'&title','&author',&price,&pubyear)
new
1: insert into books values(10001,'dbms','date',500,2005)
1 row created.
SQL> /
Enter value for isbn: 10002
Enter value for title: data structure
Enter value for author: weiss
Enter value for price: 700
Enter value for pubyear: 2002
old
1: insert into books
values(&isbn,'&title','&author',&price,&pubyear)
new
1: insert into books values(10002,'data structure','weiss',700,2002)
1 row created.
SQL> /
Enter value for isbn: 10003
Enter value for title: cryptography
Enter value for author: stallings
Enter value for price: 400
Enter value for pubyear: 2006
old
1: insert into books
values(&isbn,'&title','&author',&price,&pubyear)
new
1: insert into books values(10003,'cryptography','stallings',400,2006)
1 row created.
SQL> select * from books;
ISBN TITLE
AUTHOR
PRICE PUBYEAR
---------- -------------------------
-------------------- ---------- ----------
10001 dbms
date
500 2005
10002 data structure
weiss
700 2002
10003 cryptography
stallings
400 2006
SQL>
SQL> insert into stock
values(&isbn,&stockquantity,&reorderlevel,&reorderquantity);
Enter value for isbn: 10001
Enter value for stockquantity: 10
Enter value for reorderlevel: 3
Enter value for reorderquantity: 5
old
1: insert into stock values(&isbn,&stockquantity,&reorderlevel,&reorderquantity)
new
1: insert into stock values(10001,10,3,5)
1 row created.
SQL> /
Enter value for isbn: 10002
Enter value for stockquantity: 20
Enter value for reorderlevel: 10
Enter value for reorderquantity: 10
old
1: insert into stock
values(&isbn,&stockquantity,&reorderlevel,&reorderquantity)
new
1: insert into stock values(10002,20,10,10)
1 row created.
SQL> /
Enter value for isbn: 10003
Enter value for stockquantity: 15
Enter value for reorderlevel: 7
Enter value for reorderquantity: 8
old
1: insert into stock
values(&isbn,&stockquantity,&reorderlevel,&reorderquantity)
new
1: insert into stock values(10003,15,7,8)
1 row created.
SQL> select * from stock;
ISBN STOCKQUANTITY REORDERLEVEL REORDERQUANTITY
---------- ------------- ------------
---------------
10001 10 3 5
10002 20 10 10
10003 15 7 8
SQL>
SQL>
insert into customers values(&custid,'&cname','&address');
Enter value for custid: 1
Enter value for cname: siva
Enter value for address: madurai
old
1: insert into customers
values(&custid,'&cname','&address')
new
1: insert into customers
values(1,'siva','madurai
1 row created.
SQL> /
Enter value for custid: 2
Enter value for cname: raja
Enter value for address: theni
old
1: insert into customers
values(&custid,'&cname','&address')
new
1: insert into customers
values(2,'raja','theni')
1 row created.
SQL> /
Enter value for custid: 3
Enter value for cname: kalai
Enter value for address: salem
old
1: insert into customers
values(&custid,'&cname','&address')
new
1: insert into customers
values(3,'kalai','salem
1 row created.
SQL> select * from customers;
CUSTID CNAME
ADDRESS
---------- -------------------------
-------------------------
1 siva madurai
2 raja theni
3 kalai salem
SQL>
SQL> insert into orders
values(&orderno,&custid,'&orderdate');
Enter value for orderno: 101
Enter value for custid: 1
Enter value for orderdate: 01-jan-2010
old
1: insert into orders values(&orderno,&custid,'&orderdate')
new
1: insert into orders values(101,1,'01-jan-2010')
1 row created.
SQL> /
Enter value for orderno: 102
Enter value for custid: 1
Enter value for orderdate: 05-jun-2010
old
1: insert into orders values(&orderno,&custid,'&orderdate')
new
1: insert into orders values(102,1,'05-jun-2010')
1 row created.
SQL> /
Enter value for orderno: 103
Enter value for custid: 3
Enter value for orderdate: 18-aug-2010
old
1: insert into orders values(&orderno,&custid,'&orderdate')
new
1: insert into orders values(103,3,'18-aug-2010')
1 row created.
SQL> select * from orders;
ORDERNO CUSTID ORDERDATE
---------- ---------- ---------
101 1 01-JAN-10
102 1 05-JUN-10
103 3 18-AUG-10
SQL>
a.
Fire a trigger before insert
whenever the ordered quantity is less than the available stock quantity or the
shipdate is less than the orderdate then display the message “The ordered
quantity is not available” or “shipping date should be greater than order date”
respectively.
SQL> set serveroutput on;
SQL> create or replace trigger trigger1
2 before insert on orderlist for
each row
3 declare
4 qn number(5);
5 dat date;
6 begin
7 select stockquantity into qn
from stock where isbn=:new.isbn;
8 select orderdate into dat from
orders where orderno=:new.orderno;
9 if :new.quantity>qn then
10
dbms_output.put_line('ordered quantity is not available');
11 end
if;
12 if
months_between(:new.shipdate,dat)<0 then
13
dbms_output.put_line('ordered date is greater than shipment date');
14 end
if;
15 end;
16 /
Trigger created.
SQL> insert into orderlist
values(&orderno,&quantity,&totalprice,'&shipdate',&isbn);
Enter value for orderno: 102
Enter value for quantity: 15
Enter value for totalprice: 1000
Enter value for shipdate: 01-mar-2010
Enter value for isbn: 10001
old
1: insert into orderlist
values(&orderno,&quantity,&totalprice,'&shipdate',&isbn)
new
1: insert into orderlist values(102,15,1000,'01-mar-2010',10001)
1 row created.
b.
Fire a trigger after update of
price, when the new price is greater than the old price, update the total price
of the corresponding isbn number.
SQL>
Create or replace trigger trigger3
2 After update on books for each
row
3 When(new.price>old.price)
4 Begin
5 Update orderlist set totalprice=:new.price*quantity
where isbn=:new.isbn;
6 End;
7
8 /
Trigger created.
SQL> update books set price=600 where
isbn=10001;
1 row updated.
SQL> select * from orderlist;
ORDERNO QUANTITY TOTALPRICE
SHIPDATE ISBN
---------- ---------- ---------- ---------
----------
101 3 1800 01-MAR-10 10001
102 15 9000 01-MAR-10 10001
101 2 1200 01-JAN-09 10001
c.
Fire a trigger after delete of
an order from the orders table, which would delete the corresponding order from
the orderlist table.
SQL> Create or replace trigger trigger4
2 After delete on orders for each
row
3 Begin
4 Delete from orderlist where
orderno=:old.orderno;
5 End;
6 /
Trigger created.
SQL> select * from orders;
ORDERNO CUSTID ORDERDATE
---------- ---------- ---------
101 1 01-JAN-10
102 1 05-JUN-10
103 3 18-AUG-10
SQL> delete from orders where orderno=102;
1 row deleted.
SQL> select * from orderlist;
ORDERNO QUANTITY TOTALPRICE
SHIPDATE ISBN
---------- ---------- ---------- ---------
----------
101 3 1800 01-MAR-10 10001
101 2 1200 01-JAN-09 10001
Exercise V
Nested SubQueries
Aim: To perform nested sub queries in book
database.
1.
Find the books whose price is between 300 and 400
SQL> select * from books where price between 300 and 400;
ISBN TITLE AUTHOR PRICE PUBYEAR
---------- ------------------------- --------------------
---------- ----------
10003
cryptography stallings 400 2006
2.
Find the title, author name, stock quantity and price and pub year of the books
which are ordered.
SQL> select * from books b where exists(select * from
orderlist o where b.isbn=o.isbn);
ISBN TITLE AUTHOR PRICE PUBYEAR
---------- ------------------------- --------------------
---------- ----------
10001 dbms date 600 2005
3.
Find the sum of the price of the books published in the year.
SQL> select sum(price) from books group by pubyear;
SUM(PRICE)
----------
700
600
400
4.
Find the sum of the price of the books published in the year and having price
>500
SQL> select sum(price),pubyear from books group by
pubyear having sum(price)>500;
SUM(PRICE) PUBYEAR
---------- ----------
700 2002
600 2005
5.
Find the isbn of the books which may or may not ordered using union operation
SQL> (select isbn from books)union(select isbn from
orderlist);
ISBN
----------
10001
10002
10003
6.
Find the isbn of the books which are ordered using intersect operations
SQL> (select isbn from books)intersect(select isbn from
orderlist);
ISBN
----------
10001
7. Find the customer
name,orderdate,orderno using natural join.
SQL> select c.cname,c.address,o.orderdate,o.orderno from
customers c,orders o where c.custid=o.custid;
CNAME
ADDRESS
ORDERDATE ORDERNO
------------------------- -------------------------
--------- ----------
siva madurai
kalai
salem
8. Retrieve
customername,orderno using left outer join.
SQL> select c.cname,o.orderno from customers c,orders o
where c.custid=o.custid(+);
CNAME
ORDERNO
------------------------- ----------
siva 101
raja
kalai 103
9. Retrieve
orderno,customername using right outer join
SQL> select c.cname,o.orderno from customers c,orders o
where c.custid(+)=o.custid;
CNAME
ORDERNO
------------------------- ----------
siva 101
kalai 103
Result: Thus the
nested sub queries were performed in the books database.
Exercise VI
PL/SQL Procedure and
Function
Aim: To write a
PL/SQL procedure and functions for various operations
a) WRITE
A PL/SQL PROGRAM TO FIND THE SUM OF
DIGITS IN A GIVEN NUMBER
SQL> ed armstrong.sql
declare
a number;
t number;
arm number;
d number;
begin
a:=&a;
t:=a;
arm:=0;
while t>0
loop
d:=mod(t,10);
arm:=arm+power(d,3);
t:=trunc(t/10);
end loop;
if arm=a then
dbms_output.put_line('given no is an armstrong no'|| a);
else
dbms_output.put_line('given no is not an armstrong no');
end if;
end;
/
SQL> @
armstrong.sql
Input truncated to 1 characters
Enter value for a: 2342
old 7: a:=&a;
new 7: a:=2342;
given no is not an armstrong no
PL/SQL procedure successfully completed.
SQL> /
Enter value for a: 153
old 7: a:=&a;
new 7: a:=153;
given no is an armstrong no153
PL/SQL procedure successfully completed.
b) WRITE A PL
SQL> ed reverse.sql
declare
a number;
rev number;
d number;
begin
a:=&a;
rev:=0;
while a>0
loop
d:=mod(a,10);
rev:=(rev*10)+d;
a:=trunc(a/10);
end loop;
dbms_output.put_line('no is'|| rev);
end;
/
SQL> @ reverse.sql
Input truncated to 1 characters
Enter value for a: 34566
old 6: a:=&a;
new 6: a:=34566;
no is66543
PL/SQL procedure successfully completed.
SQL>
c) WRITE A PL/SQL PROGRAM TO CHECK
WHETHER THE GIVEN NUMBER IS PRIME OR NOT
SQL> ed prime.sql
declare
a number;
c number:=0;
i number;
begin
a:=&a;
for i in 1..a
loop
if mod(a,i)=0 then
c:=c+1;
end if;
end loop;
if c=2 then
dbms_output.put_line(a ||'is a prime number');
else
dbms_output.put_line(a ||'is not a prime number');
end if;
end;
/
SQL> @ prime.sql
Input truncated to 1 characters
Enter value for a: 11
old 6: a:=&a;
new 6: a:=11;
11is a prime number
PL/SQL procedure successfully completed.
SQL>
d) WRITE A PL
SQL> ed factorial.sql
declare
n number;
f number:=1;
begin
n:=&n;
for i in 1..n
loop
f:=f*i;
end loop;
dbms_output.put_line('the factorial is'|| f);
end;
/
SQL> @ factorial.sql
Input truncated to 1 characters
Enter value for n: 5
old 5: n:=&n;
new 5: n:=5;
the factorial is120
PL/SQL procedure successfully completed.
SQL>
e) WRITE A PL
SQL> ed fibo.sql
declare
a number;
b number;
c number;
n number;
i number;
begin
n:=&n;
a:=0;
b:=1;
dbms_output.put_line(a);
dbms_output.put_line(b);
for i in 1..n-2
loop
c:=a+b;
dbms_output.put_line(c);
a:=b;
b:=c;
end loop;
end;
/
SQL> @ fibo.sql
Input truncated to 1 characters
Enter value for n: 5
old 8: n:=&n;
new 8: n:=5;
0
1
1
2
3
PL/SQL procedure successfully completed.
SQL>
f) WRITE A FUNCTION
TO FIND FACTORIAL OF A NUMBER
SQL> ed
func.sql
create or
replace function fact(n number)return number
as
fac
number:=1;
begin
for i in
1..n
loop
fac:=fac*i;
end loop;
return fac;
end;
/
SQL> @
func.sql
Input
truncated to 1 characters
Function
created.
SQL>
select fact(7) from dual;
FACT(7)
----------
5040
SQL>
Result: Thus the
PL/SQL procedure and function was created and the results were verified.
Exercise VII
PL/SQL Cursor
Aim : To create
PL/SQL cursor for payroll processing of an employee.
SQL> create table empl(empno number(5),ename
varchar(20),dob date,basicpay number(5),da number(5),allowance number(5),tax
number(5),lic number(5),grosspay number(5));
Table created.
SQL> insert into empl values(&empno,'&ename','&dob',&basicpay,&da,&allowance,
&tax,&lic,&grosspay);
Enter value for empno: 101
Enter value for ename: siva
Enter value for dob: 01-aug-2000
Enter value for basicpay: 10000
Enter value for da: 4000
Enter value for allowance: 2000
Enter value for tax: 1000
Enter value for lic: 1200
Enter value for grosspay: 0
old 1: insert into
empl
values(&empno,'&ename','&dob',&basicpay,&da,&allowance,&tax,&lic,&grosspay
new 1: insert into
empl values(101,'siva','01-aug-2000',10000,4000,2000,1000,1200,0)
1 row created.
SQL> /
Enter value for empno: 102
Enter value for ename: raja
Enter value for dob: 11-mar-2001
Enter value for basicpay: 20000
Enter value for da: 7000
Enter value for allowance: 3000
Enter value for tax: 2000
Enter value for lic: 2600
Enter value for grosspay: 0
old 1: insert into
empl
values(&empno,'&ename','&dob',&basicpay,&da,&allowance,
&tax,&lic,&grosspay
new 1: insert into
empl values(102,'raja','11-mar-2001',20000,7000,3000,2000,2600,0)
1 row created.
SQL> select * from empl;
EMPNO ENAME DOB BASICPAY DA
ALLOWANCE
---------- -------------------- --------- ----------
---------- ----------
TAX
LIC GROSSPAY
---------- ---------- ----------
101 siva 01-AUG-00 10000 4000 2000
1000 1200 0
102 raja 11-MAR-01 20000 7000 3000
2000 2600 0
SQL> declare
2 cursor c is select * from empl order by
grosspay desc;
3 rec empl % rowtype;
4 cnt integer:=0;
5 begin
6 open c;
7 loop
8 fetch c into rec;
9 exit when c% notfound;
10 if(cnt<5) then
11 update empl set
grosspay=(rec.basicpay+rec.da+rec.allowance)-(rec.lic+rec.tax) where
empno=empno;
12 dbms_output.put_line('Employee
no:'||rec.empno);
13 dbms_output.put_line('name:'||rec.ename);
14 dbms_output.put_line('Basic pay:'||rec.basicpay);
15 dbms_output.put_line('DA:'||rec.da);
16
dbms_output.put_line('allowance:'||rec.allowance);
17 dbms_output.put_line('Tax:'||rec.tax);
18 dbms_output.put_line('LIC:'||rec.lic);
19
dbms_output.put_line('Grosspay:'||rec.grosspay);
20 cnt:=cnt+1;
21 end if;
22 end loop;
23 close c;
24 end;
25 /
Employee no:101
name:siva
Basic pay:10000
DA:4000
allowance:2000
Tax:1000
LIC:1200
Grosspay:0
Employee no:102
name:raja
Basic pay:20000
DA:7000
allowance:3000
Tax:2000
LIC:2600
Grosspay:0
PL/SQL procedure successfully completed.
SQL> select * from empl;
EMPNO ENAME DOB BASICPAY DA
ALLOWANCE
---------- -------------------- --------- ----------
---------- ----------
TAX LIC
GROSSPAY
---------- ---------- ----------
101 siva 01-AUG-00 10000 4000 2000
1000 1200
13800
102 raja 11-MAR-01 20000 7000 3000
2000 2600
25400
SQL>
Result:
Thus the PL/SQL
cursor was created and the results were verified.
EX.No.8 PL/SQL FUNCTIONS & PROCEDURES
AIM
To write PL/SQL functions and
procedures to perform certain Constraints.
QUESTIONS
1. a) Create the table account with
attributes accno, date, balance.
b) create the table branch with
attributes branch name,location,assets.
c) create the table customer
with the attributes ID, name, address.
d) create the table depositer
with the attributes accno, custid, bname.
e) create the table employee
with the attributes empno, name, address,
Pay, qualification and
department .
2.
Write a PL/SQL function to display assets if the branch name is given.
3.
Write a PL/SQL function to calculate income tax for given employee
number calculations as
a) If pay is between
0 to 10000 then no income tax is charged.
b) If pay is between
10000 to 20000 then 20% of pay is charged
As tax.
c) If pay is between
21000 to 50000 then 35% of pay is charged
As tax.
d) If pay is greater
than 50,000 then 40% of pay is charged as tax.
4.
Write a PL/SQL function to display the number of accounts in the
Particular branch.
5.
Write a PL/SQL procedure to display the number of accounts opened by
Accounts opened by a
customer.
6. Write a PL/SQL procedure to
display the total amount deposited in branch.
QUERIES
1)
a.i) create table branch( bname varchar(30), loc char(20), assets
varchar(30));
ii) insert into branch
values(‘&bname’,’&loc’,’&assets’);
b.i) create table account(accno
integer, sdate date, balance integer);
ii) insert into account values(&accno,’&sdate’,&balance);
c .i) create table customer(custid
integer, cname char(20), address char(20));
ii) insert into customer
values(&custid.’&cname’,’&address’);
d. i) create table
depositer(accno integer, custid integer, bname char(20));
ii) insert into depositer
values(&accno, &custid, ‘&bname’);
e. i) create table
employee(empno integer,empname char(20), address char(20),
Pay integer,
qualification varchar(30));
ii) insert into employee
values(&empno,’&empname’.’&address’,&pay,
‘&qualification’);
2) create or replace function f1(newbname
varchar)
2 return varchar
3 is
4 newassets varchar(30);
5 BEGIN
6 select assets into newassets from branch
where bname=newbname;
7 return newassets;
8 END;
9 /
Function created
3) SQL> create or replace function
f2(neweno integer)
2 return integer
3 is
4 newitax integer;
5 pay1 integer;
6 BEGIN
7 select pay into pay1 from employee1 where
empno=newwmpno;
8 if(pay<10000)then
9 newitax=0.1*pay1;
10 elsif(10000<pay1<20000)then
11 newitax=0.2*pay1;
12 elsif(20000<pay1<50000)then
13 newitax=0.35*pay1;
14 else(pay>50000)
15 newitax=0.4*pay1;
16 return new tax;
17 Endif;
18 end;
19 /
Function created.
4) create or replace function f3(newbname
varchar)
2 return varchar
3 is
4 newaccno varchar(20);
5 BEGIN
6 select count(accno) into newaccno from
depositer where bname=newbname;
7 return newaccno;
8 END;
9 /
Function created.
5)
SQL> set serveroutput on
SQL> create or replace procedure cusa(vcname
varchar)
2 is
3 vno integer;
4 BEGIN
5 select count(*) into vno from
depositer,customer where
depositer.cid=customer.cid
and customer.cname=vcname;
6 dbms_output.put_line(vno);
7 END;
8 /
Procedure created.
6)
SQL> create or replace procedure p2(newbname varchar)
2 is
3 nwamount integer;
4 BEGIN
5 select sum(balance)into nwamount from
depositer,account2 where
depositer.bname=newbname
and account2.accno=depositer.accno);
6 dbms_output.put_line(nwamount);
7 END;
Procedure created.
OUTPUT
1) a) i. table created.
Ii. Enter value for name: tmb
Enter value for loc:
madurai
Enter value for assets: one
lakh
old 1: insert into branch
values('&name','&loc','&assets')
new 1: insert into branch1 values('tmb','madurai
1 row created.
B.I) table created
Ii) Enter value for accno: 10002
Enter value for sdate:
24-mar-90
Enter value for balance:
30000
old 1: insert into account
values(&accno,'&sdate',&balance)
new 1: insert into account
values(10004,'24-mar-90',30000)
1 row created
C.I) table created.
Ii) Enter value for custid: 2
Enter value for cname:
malar
Enter value for address:
madurai
old 1:
insert into depositer values(&accno,&cid,'&name')
New
1: insert into depositer
values(10002,2,'kala')
1 row created.
D.I) table created.
Ii) Enter value for accno: 10002
Enter value for custid: 2
Enter value for name: kala
Old 1:
insert into depositer values(&accno,&cid,'&name')
new 1:
insert into depositer values(10002,2,'kala')
1 row created.
E. I)
table created.
Ii) Enter value for empno: 2
Enter value for ename:
kala
Enter value for address:
madurai
Enter value for pay:
15000
Enter value for
qualification: M.com
Enter value for
department: Finance
old 1: insert into employee
values(&empno,'&ename',
'&address',&pay,'&qualification','&department'
new 1: insert into employee1 values(4,'heena',
'madurai
1 row created.
2) SQL>
set serveroutput on
SQL> BEGIN
2 dbms_output.put_line(f1('tmb'));
3 END;
4 /
one lakh
PL/SQL procedure
successfully completed.
3) SQL>
set serveroutput on
SQL> BEGIN
2 dbms_output.put_line(f2(15000));
3 END;
4 /
1500
PL/SQL procedure successfully completed.
4) SQL> set serveroutput on
SQL> BEGIN
2 dbms_output.put_line(f3('1b'));
3 END;
4 /
0
PL/SQL procedure successfully
completed.
5) SQL>expc p1(‘malar’);
1
6)
SQL>expc p2(‘SBI’);
40,000
RESULT
Thus the PL/SQL
functions and procedures are created and executed.
Ex.9 VIEWS
SQL>
create view view4 as(select
account.accno,account.balance,depositer.custid,depositer.bname from
account,depositer
where account.accno=depositer.accno);
View
created.
SQL>
select * from view4;
ACCNO BALANCE CUSTID BNAME
----------
---------- ---------- --------------------
1701
17859 111 State Bank
1702
63875 112 TMB
1703 52985
113 IOB
1704
75426 114 Syndicate
1705
42987 115 Baroda
SQL>
create view view5 as select c.custid,c.cname,c.address,d.accno,d.bname from
customer c,depositer d where c.custid=d.custid;
View
created.
SQL>
select * from view5;
CUSTID
CNAME
ADDRESS
ACCNO
----------
-------------------- -------------------- ----------
BNAME
--------------------
111
Sunil
1,Nehru St,Erode
1701
State
Bank
112
Kishore
2,Gandi
St,Mdu
1702
TMB
113
Harish
3,Rajaji St,Cng
1703
IOB
CUSTID
CNAME
ADDRESS
ACCNO
----------
-------------------- -------------------- ----------
BNAME
--------------------
114
Akil
4,Bose
St,Vnr
1704
Syndicate
115
Angel
5,PT
St,Tmm
1705
Baroda
SQL>
create view view3 as select * from emp1 where address='madurai'
2
SQL>
create view view6 as select * from empl where address='madurai';
View
created.
SQL>
select * from view6;
no
rows selected
SQL>
insert into view6
values(&empno,'&empname','&address',&pay,'&qualification');
Enter
value for empno: 1000
Enter
value for empname: Sunil
Enter
value for address: madurai
Enter
value for pay: 12000
Enter
value for qualification: B.Sc.
old
1: insert into view6
values(&empno,'&empname','&address',&pay,'&qualification')
new
1: insert into view6 values(1000,'Sunil','madurai',12000,'B.Sc.')
1
row created.
SQL>
select * from view6;
EMPNO
EMPNAME
ADDRESS
PAY
----------
-------------------- -------------------- ----------
QUALIFICATION
------------------------------
1000
Sunil
madurai
12000
B.Sc.
SQL>
select * from empl;
EMPNO
EMPNAME
ADDRESS
PAY
----------
-------------------- -------------------- ----------
QUALIFICATION
------------------------------
101
Ashok
6,Lalu
St,Mdu
8000
B.com
102
Sharan
7,NGO Nagar,Vnr
7500
B.Ed
103
Tharun
8,KK
Nagar,Ed
12000
B.E.
EMPNO EMPNAME
ADDRESS
PAY
----------
-------------------- -------------------- ----------
QUALIFICATION
------------------------------
104
Shailesh
9,PTN
St,Tmm
18000
B.E.
105
Eashan
5,Raja St,Tmm
42000
B.E.
1000
Sunil
madurai
12000
B.Sc.
6
rows selected.
SQL>
commit;
Commit
complete.
SQL>
alter table account add primary key(accno);
Table
altered.
SQL>
delete from view4 where accno=1704;
1
row deleted.
SQL>
select * from view4;
ACCNO BALANCE CUSTID BNAME
----------
---------- ---------- --------------------
1701
17859 111 State Bank
1702
63875 112 TMB
1703 52985
113 IOB
1705
42987 115 Baroda
SQL>
alter table depositer add constraint dp foreign key(accno)references
account(accno);
Table
altered.
SQL>
alter table customer add primary key (custid);
Table
altered.
SQL>
update view5 set bname='AXIS CHN' where cname='Angel';
1
row updated.
SQL>
select * from view5;
CUSTID
CNAME
ADDRESS
ACCNO
----------
-------------------- -------------------- ----------
BNAME
--------------------
111 Sunil
1,Nehru St,Erode
1701
State
Bank
112
Kishore
2,Gandi
St,Mdu
1702
TMB
113
Harish
3,Rajaji
St,Cng 1703
IOB
CUSTID
CNAME
ADDRESS
ACCNO
----------
-------------------- -------------------- ----------
BNAME
--------------------
115
Angel
5,PT
St,Tmm
1705
AXIS
CHN
SQL>
Ex. 10 DYNAMIC
SQL
COMMAND
PROMPT:
Microsoft
Windows 2000 [Version 5.00.2195]
(C)
Copyright 1985-2000 Microsoft Corp.
C:\Documents
and Settings\2910043>cd ..
C:\Documents
and Settings>cd ..
C:\>cd
jdk1.3\bin
C:\jdk1.3\bin>javac
dsql.java
C:\jdk1.3\bin>java
dsql
DB
connected
1.insert
2.display
3.exit
1
enter
employee id
101
enter
employee name
Angel
enter
address
Madurai
1.insert
2.display
3.exit
1
enter
employee id
102
enter
employee name
Ammu
enter
address
Madurai
1.insert
2.display
3.exit
1
enter
employee id
103
enter
employee name
Geetha
enter
address
Madurai
1.insert
2.display
3.exit
2
empid
name address
101
Angel Madurai
102
Ammu Madurai
103
Geetha Madurai
1.insert
2.display
3.exit
3
C:\jdk1.3\bin>
NOTEPAD:
import
java.io.*;
import
java.sql.*;
class
dsql
{
public static void main(String s[]) throws Exception
{
Connection con;
Statement st;
ResultSet rs;
PreparedStatement ps,ps1;
int ch;
try
{
DriverManager.registerDriver(new
sun.jdbc.odbc.JdbcOdbcDriver());
con=DriverManager.getConnection("jdbc:odbc:ex10","cs2910043","cs2910043");
BufferedReader
br=new BufferedReader(new InputStreamReader(System.in));
System.out.println("DB
connected");
while(true)
{
System.out.println("1.insert\n2.display\n3.exit");
ch=Integer.parseInt(br.readLine());
switch(ch)
{
case 1:
System.out.println("enter employee id");
int
empid=Integer.parseInt(br.readLine());
System.out.println("enter
employee name");
String
name=br.readLine();
System.out.println("enter address");
String
address=br.readLine();
ps=con.prepareStatement("insert
into ex10 values(?,?,?)");
ps1=con.prepareStatement("commit");
ps.setInt(1,empid);
ps.setString(2,name);
ps.setString(3,address);
ps.executeUpdate();
ps1.execute();
break;
case
2:
st=con.createStatement();
rs=st.executeQuery("select
* from ex10");
System.out.println("\nempid\tname\taddress\n");
while(rs.next())
{
System.out.print(rs.getInt(1)+"\t");
System.out.print(rs.getString(2)+"\t");
System.out.print(rs.getString(3)+"\t");
System.out.println();
}
st.close();
break;
case
3:
System.exit(0);
break;
default:
break;
}
}
}
catch(Exception
e)
{
System.out.println(e);
}
}
}
ORACLE:
ORACLE:
SQL>
create table ex10(empid number(3),empname varchar(15),address
varchar(15));
Table
created.
SQL>
commit;
Commit
complete.
No comments:
Post a Comment