Ex No: 1. Data Definition of Base Tables.
Date:
AIM:
To Create a Data Definition of Base Tables.
i) Create Table Job (job _id, job _title, min _sal, max _sal)
ii) Create table Employee (emp _no, emp _name, emp _sal, emp _comm, dept _no)
iii) Create table deposit (act _no, c name, b name, amount, a _date).
i) Create Table Job (job _id, job _title, min _sal, max _sal)
SQL> create table job(job _id varchar2(20),job _name varchar2(20),min _sal
number(6,2),max_sal number(8,2));
Table created.
SQL> insert into job values ('&job _id',' &job_ name', &min _sal, &max _sal);
Enter value for job _id: it _pro
Enter value for job _name: programmer
Enter value for min _sal: 4000
Enter value for max _sal: 10000
1 row created.
SQL> /
Enter value for job _id: mk _mgr
Enter value for job _name: marketing manager
Enter value for min _sal: 9000
Enter value for max _sal: 15000
1 row created.
SQL> /
Enter value for job _id: finance
Enter value for job _name: finance manager
Enter value for min _sal: 8200
Enter value for max _sal: 12000
1 row created.
SQL> /
Enter value for job _id: let
Enter value for job _name: lecturer
Enter value for min _sal: 6000
Enter value for max _sal: 17000
1 row created.
SQL> /
Enter value for job _id: com pop
Enter value for job _name: computer operator
Enter value for min _sal: 1500
Enter value for max _sal: 3000
1 row created.
ii) Create table Employee ( emp _no, emp _name, emp_ salary_, emp _comm, dept _no)
SQL>create table employee(emp _no number(5),emp _name varchar2(15),emp _salary
number(8,2),emp _comm varchar2(10),dept _no number(5));
Table created.
SQL> insert into employee values (&emp _no, '&emp _name', &emp _salary,
&emp _comm, &dept _no);
Enter value for emp _no: 101
Enter value for emp _name: Smith
Enter value for emp _salary: 800
Enter value for emp _comm: null
Enter value for dept _no: 20
1 row created.
SQL> /
Enter value for emp _no: 102
Enter value for emp _name: Shenan
Enter value for emp _salary: 1600
Enter value for emp _comm: 300
Enter value for dept _no: 25
1 row created.
SQL> /
Enter value for emp _no: 103
Enter value for emp _name: Adama
Enter value for emp _salary: 1100
Enter value for emp _comm: 0
Enter value for dept _no: 20
1 row created.
SQL> /
Enter value for emp _no: 104
Enter value for emp _name: Aman
Enter value for emp _salary: 3000
Enter value for emp _comm: null
Enter value for dept _no: 15
1 row created.
SQL> /
Enter value for emp_no: 105
Enter value for emp _name: Anitha
Enter value for emp _salary: 5000
Enter value for emp _comm: 50000
Enter value for dept _no: 10
1 row created.
SQL> /
Enter value for emp _no: 106
Enter value for emp_name: Sneha
Enter value for emp _salary: 2425
Enter value for emp _comm: 24500
Enter value for dept _no: 10
1 row created.
SQL> /
Enter value for emp _no: 107
Enter value for emp _name: Anemia
Enter value for emp_salary:2975
Enter value for emp _comm: null
Enter value for dept _no: 30
1 row created.
iii) Retrieve all data from employee, jobs and deposit.
SQL> select * from employee
EMP_NO EMP_NAME EMP_SALARY EMP_COMM DEPT_IN
101 Smith 800 20
102 Snehan 1600 300 25
103 Adama 1100 0 20
104 Aman 3000 15
105 Anitha 5000 50000 10
106 Sneha 2425 24500 10
107 Anemia 2975 30
7 rows selected.
SQL> select * from job;
JOB_ID JOB_NAME MAX_SAL MIN_SAL
It _pro programmer 4000 10000
mk_mgr Marketing 9000 15000
manager
fianance Finance manager 8200 12000
Lec Lecturer 6000 17000
com pop Computer operator 1500 3000
SQL> select * from deposit;
ACTNO CNAME BNAME AMOUNT ADATE
100 Anal verse 1000 01-MAR-05
101 Sunil acne 5000 04-JAN-06
102 Maul karthi 3500 17-NOV-05
104 Prod myriad 3000 27-MAR-06
106 Sandy adhere 2000 31-MAR-06
107 Shaven viral 1000 05-SEP-05
108 Kathie nehru place 5000 02-JUL-05
109 Mine power 7000 10-AUG-05
7 rows selected.
RESULT:
Thus the data definition of base created successfully.
Ex No: 2 DDL with Primary key constraints.
Date:
AIM:
To Create a DDL with Primary key constraints.
SQL>Create table Employee (emp _no, emp _name, emp _sal, emp _comm, dept _no)
1 row created.
SQL>create table employee(emp _no number(5),emp _name varchar2(15),emp _salary
number(8,2),emp _comm varchar2(10),dept _no number(5));
Table created.
SQL> insert into employee values (&emp _no, '&emp _name', &emp _salary, &emp _comm
,&dept _no);
Enter value for emp _no: 101
Enter value for emp _name: smith
Enter value for emp _salary: 800
Enter value for emp _comm:0
Enter value for dept _no: 10
1 row created.
SQL> /
Enter value for emp _no: 102
Enter value for emp _name: aman
Enter value for emp _salary: 3000
Enter value for emp _comm: null
Enter value for dept _no: 15
1 row created.
SQL> /
Enter value for emp _no: 103
Enter value for emp _name: anitha
Enter value for emp _salary: 5000
Enter value for emp _comm: 15000
Enter value for dept _no: 15
1 row created.
SQL> /
Enter value for emp _no: 104
Enter value for emp _name: sneha
Enter value for emp _salary: 2425
Enter value for emp _comm: 24500
Enter value for dept _no: 10
1 row created.
SQL> /
Enter value for emp _no: 105
Enter value for emp _name: Anamika
Enter value for emp _salary: 2425
Enter value for emp _comm: 30
Enter value for dept _no: 10
1 row created.
EMP_NO EMP_NAME EMP_SALARY EMP_COMM DEPT_NO
101 smith 800 20 10
102 Aman 3000 10
103 Anita 5000 50000 15
104 Sneha 2975 24500 10
105 Anamika 2425 30 10
SQL> update Employee set emp _salary=800+500 where emp _no=101;
SQL> 1 row updated
SQL>select * form Employee;
EMP_NO EMP_NAME EMP_SALARY EMP_COMM DEPT_NO
101 smith 1300 20 10
102 Aman 3000 10
103 Anita 5000 50000 15
104 Sneha 2975 24500 10
105 Anamika 2425 30 10
RESULT:
Thus the DDL with Primary key constraints implemented successfully.
Ex: No: 3 DDL with constraints and verification by insert command.
Date:
AIM:
To implements the DDL with constraints and verification by insert command in
Oracle.
SQL> create table deposit(act no varchar2(5),c name varchar2(18),b name
varchar2(18),amount number(8,2),a data date);
Table created.
SQL>inset into deposit values(100,’ anil’,’vrce’,1000, ‘01-mar-05’);
SQL>1 row created
SQL>select * form deposit;
ACTNO CNAME BNAME AMOUNT ADATA
100 anil vrce 1000 01-MAR-05
SQL> insert into deposit values ('&act no', '&c name', '&b name', &amount, '&a data');
Enter value for act no: 100
Enter value for c name: anil
Enter value for b name: vrce
Enter value for amount: 1000
Enter value for a data: 01-mar-05
1 row created.
SQL> /
Enter value for act no: 101
Enter value for c name: sunil
Enter value for b name: ajni
Enter value for amount: 5000
Enter value for a date: 04-jan-06
1 row created.
SQL> /
Enter value for act no: 102
Enter value for c name: mehul
Enter value for b name: karolbagh
Enter value for amount: 3500
Enter value for a data: 17-nov-05
1 row created.
SQL> /
Enter value for act no: 104
Enter value for c name: prmod
Enter value for b name: mgroad
Enter value for amount: 3000
Enter value for a data: 27-mar-06
1 row created.
SQL> /
Enter value for act no: 106
Enter value for c name: sandip
Enter value for b name: andheri
Enter value for amount: 2000
Enter value for a data: 31-mar-06
1 row created.
SQL> /
Enter value for act no: 107
Enter value for c name: shivani
Enter value for b name: virar
Enter value for amount: 1000
Enter value for a data: 05-sep-05
1 row created.
SQL> /
Enter value for act no: 108
Enter value for c name: kranit
Enter value for b name: nehru place
Enter value for amount: 5000
Enter value for a data: 02-jul-05
1 row created.
SQL> /
Enter value for act no: 109
Enter value for c name: minu
Enter value for b name: powai
Enter value for amount: 7000
Enter value for a data: 10-aug-05
1 row created.
SQL> desc deposit;
Name Null? Type
ACTNO VARCHAR2(5)
CNAME VARCHAR2(18)
BNAME VARCHAR2(18)
AMOUNT NUMBER(8,2)
ADATA DATE
ACTNO CNAME BNAME AMOUNT ADATE
100 anil vrce 1000 01-MAR-05
101 sunil ajni 5000 04-JAN-06
102 mehul karolbagh 3500 17-NOV-05
104 prmod mgroad 3000 27-MAR-06
106 sandip andheri 2000 31-MAR-06
107 shivani virar 1000 05-SEP-05
108 kranit nehru place 5000 02-JUL-05
109 minu powai 7000 10-AUG-05
RESULT:
Thus the DDL with constraints and verification by insert command implemented
successfully.
Ex NO: 4 Data Manipulation of Base Tables and Views.
Date:
AIM:
To implements the Data Manipulation of Base Tables and Views.
SQL> create table customers (id varchar2(20),name varchar2(20),age varchar2 (20),
address varchar2 (20) salary number(6,2));
Table created.
SQL> insert into customers values (&id, '&name', ‘&age’, &address, &salary);
Enter value for id :1
Enter value for name :Ramesh
Enter value for age :32
Enter value for address :Ahmedabad
Enter value for salary :2000.00
1 row created.
SQL>/
Enter value for id :2
Enter value for name : Khilan
Enter value for age :25
Enter value for address : Delhi
Enter value for salary :1500.00
1 row created.
SQL>/
Enter value for id :3
Enter value for name : kaushik
Enter value for age :23
Enter value for address : Kota
Enter value for salary :2000.00
1 row created.
SQL>/
Enter value for id :4
Enter value for name : Chaitali
Enter value for age :25
Enter value for address : Mumbai
Enter value for salary :6500.00
1 row created.
SQL>/
Enter value for id :5
Enter value for name : Hardik
Enter value for age :27
Enter value for address : Bhopal
Enter value for salary :8500.00
1 row created.
SQL>/
Enter value for id :6
Enter value for name : Kamal
Enter value for age :22
Enter value for address :MP
Enter value for salary :4500.00
1 row created.
SQL>/
Enter value for id :7
Enter value for name : Muffy
Enter value for age :24
Enter value for address : Indore
Enter value for salary :10000.00
1 row created.
SQL>select * from customers;
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Kamal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
SQL > create view customers _view as select name, age from customers;
SQL > select * from customers _view;
Name Age
Ramesh 32
Khilan 25
kaushik 23
Chaitali 25
Hardik 27
Kamal 22
Muffy 24
SQL > delete from customers _view where age = 22;
SQL >select * from customers;
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00
SQL > update customers _view set age = 35 where name = 'Ramesh';
SQL >select * from customers;
ID NAME AGE ADDRESS SALARY
1 Ramesh 35 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Kamal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
RESULT:
Thus the data manipulation of base tables and views implemented successfully.
Ex. No: 5 Demonstrate the Query commands
Date:
AIM:
To write a Demonstrate the Query commands.
Answers:
SQL>create table student(name varchar2(20),class number(10),contact number(10),city
varchar2(20));
Table Created.
SQL>insert into student values (‘&name’, &class, &contact, ’&city’);
Enter value for name : ashu
Enter value for class :10
Enter value for contact : 90000
Enter value for city : Delhi
1 row created.
SQL>/
Enter value for name :smith
Enter value for class :10
Enter value for contact : 90001
Enter value for city : Delhi
1 row created.
SQL>/
Enter value for name : pankaj
Enter value for class :10
Enter value for contact : 90002
Enter value for city : Delhi
1 row created.
SQL>/
Enter value for name : deepak
Enter value for class :10
Enter value for contact : 90003
Enter value for city : Delhi
1 row created.
SQL>select * from student;
name Class contact city
ashu 10 90000 Delhi
santosh 10 90001 Delhi
pankaj 10 90002 Delhi
deepak 10 90003 Delhi
SQL>alter table student drop column city;
name Class contact
ashu 10 90000
santosh 10 90001
pankaj 10 90002
Deepak 10 90003
SQL>alter table student rename column contact to contact _no;
name Class Contact _no
ashu 10 90000
santosh 10 90001
pankaj 10 90002
deepak 10 90003
SQL>update student set contact = 91111. where name =ashu;
name class Contact _no
ashu 10 91111
santosh 10 90001
pankaj 10 90002
deepak 10 90003
SQL> create table product _mast (product varchar2(20), company varchar2(20),qty
number(10),rate number(10),cost number(10));
Table Created.
SQL> insert into product _mast values (‘&product’, ’&company’, &qty, &rate, &cost);
Enter value for product : Item1
Enter value for company : Com1
Enter value for qty :2
Enter value for rate : 10
Enter value for cost : 20
1 row created.
SQL>/
Enter value for product : Item2
Enter value for company : Com2
Enter value for qty :3
Enter value for rate : 25
Enter value for cost : 75
1 row created.
SQL>/
Enter value for product : Item3
Enter value for company : Com1
Enter value for qty :2
Enter value for rate : 30
Enter value for cost : 60
1 row created.
SQL>/
Enter value for product : Item4
Enter value for company : Com3
Enter value for qty :5
Enter value for rate : 10
Enter value for cost : 50
1 row created.
SQL>/
Enter value for product : Item5
Enter value for company : Com2
Enter value for qty :2
Enter value for rate : 20
Enter value for cost : 40
1 row created.
SQL>/
Enter value for product : Item6
Enter value for company : Cpm1
Enter value for qty :3
Enter value for rate : 25
Enter value for cost : 75
1 row created.
SQL>/
Enter value for product : Item7
Enter value for company : Com1
Enter value for qty :5
Enter value for rate : 30
Enter value for cost : 150
1 row created.
SQL>/
Enter value for product : Item8
Enter value for company : Com1
Enter value for qty :3
Enter value for rate : 10
Enter value for cost :3
1 row created.
SQL>/
Enter value for product : Item9
Enter value for company : Com2
Enter value for qty :2
Enter value for rate : 25
Enter value for cost : 50
1 row created.
SQL>/
Enter value for product : Item10
Enter value for company : Com3
Enter value for qty :4
Enter value for rate : 30
Enter value for cost : 120
1 row created.
SQL> select count (*) from product _mast;
Output:
10
SQL> select count (*) from product _mast; where rate>=20;
Output:
7
SQL>select count(distinct company) from product _mast;
Output:
3
SQL>select company, count (*) from product _mast group by company;
Output:
Com1 5
Com2 3
Com3 2
SQL>select company, count (*) from product _mast group by company having count(*)>2;
Output:
Com1 5
Com2 3
SQL> select sum (cost) from product _mast;
Output:
670
SQL> select sum (cost) from product _mast where qty>3;
Output:
320
SQL> select sum (cost) from product _mast where qty>3 group by company;
Output:
Com1 150
Com2 170
SQL>select company, sum (cost) from product _mast group by company having
sum(cost)>=170;
Output:
Com1 335
Com3 170
SQL> select avg (cost) from product _mast;
Output:
67.00
SQL> select max(rate) from product _mast;
Output:
30
SQL>select min(rate) from product _mast;
Output:
10
RESULT:
Thus the Query commands executed successfully.
Ex No: 6.
Date:
Write a PL/SQL code block that will accept an account number from the user and
debit an amount of Rs. 2000 from the account if the account has a minimum balance
of500 after the amount is debited.
AIM:
To write a PL/SQL code for debit an amount from user account if the account
has a minimum balance of Rs.500.
The Process is to fired on the Account table.
SQL> create table Account(Account _id varchar(5), Name varchar(20), Bal
number(7,2));
Table created.
SQL> insert into Account values('Ac512','Ram',2500);
1 row created.
SQL> insert into Account values('Ac513','Gopi',5000);
1 row created.
SQL> insert into Account values('Ac514','Hari',7500);
1 row created.
SQL> insert into Account values('Ac515','Babu',8500);
1 row created.
SQL> insert into Account values('Ac516','Sakar',500);
1 row created.
SQL> select * from account;
ACC_ID NAME BAL
Ac512 Ram 2500
Ac513 Gopi 5000
Ac514 Hari 7500
Ac515 Babu 8500
Ac516 Sankar 500
SQL> ed
Wrote file afiedt.buf
1 Declare
2 acct_balance number(7,2);
3.acct_no varchar2(6);
4 debit_amt number(7,2):=2000.00;
5 min_bal constant number(7,2):=500.00;
6 Begin
7 acct_no:='&acct_no';
8 select bal into acct_balance
9 from account
10 where account_id=acct_no;
11 acct_balance:=acct_balance-debit_amt;
12 if acct_balance>=min_bal then
13update account set bal=bal-debit_amt
14 where account_id=acct_no;
15 end if;
16* end;
SQL> /
Enter value for acct _no: Ac512
old 7: acct_no:='&acct_no';
new 7: acct_no:='Ac512';
PL/SQL procedure successfully completed.
SQL> select * from account;
ACC_ID NAME BAL
Ac512 Ram 2500
Ac513 Gopi 5000
Ac514 Hari 7500
Ac515 Babu 8500
Ac516 Sankar 500
RESULT:
Thus the PL/SQL code for debit an amount from user is implemented
successfully.
Ex No: 7
Date:
Write a PL/SQL code block to calculate the area of the circle for a value
of radius varying from 3 to 7. Store the radius and the corresponding values of
calculated area in atable Areas. Areas – radius, area.
AIM:
To write a PL/SQL code for calculate the area of circle for a values of
radius varying from 3 to 7.
SQL> CREATE TABLE Areas (Radius number(2),Area number(6,2));
Table created.
SQL>CREATE TABLE areas(Radius number(5), area number(14,2));
Table created.
SQL> set serveroutput on;
SQL> declare
2 pi constant number(4,2):=3.14;
3 radius number(5);
4 area number(14,2);
5 begin
6 radius :=3;
7 while radius <=7
8 loop
9 area := pi*power(radius,2);
10 insert into areas
11 values(radius,area);
12 radius := radius+1;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL> select * from areas;
RADIUS AREA
3 28.26
4 50.24
5 78.5
6 113.04
7 153.86
5 rows selected.
RESULT:
Thus the PL/SQL code for calculate the area of circle for a values of radius
varying from 3 to 7 is executed successfully.
Ex No : 8
Date:
Write a PL/SQL block of code for reversing a number.
AIM:
To write a PL/SQL code for reversing a number.
SQL> set serveroutput on;
SQL> declare
2 num varchar(6):='1234';
3 len number(2);
4 rev varchar(6);
5 begin
6 len:=length(num);
7 for cntr in reverse 1..len
8 loop
9 rev:=rev||substr(num,cntr,1);
10 end loop;
11 dbms_output.put_line('The Given Number is'||num);
12 dbms_output.put_line('The Inverted Number is'||rev);
13 end;
14 /
The Given Number is:1234 The Inverted Number is:4321
PL/SQL procedure successfully completed.
RESULT:
Thus the PL/SQL code for reversing a number is executed successfully.
Ex No: 9
Date:
Create a transparent audit system for a table Client_master (client_no, name,
address, Bal_due). The system must keep track of the records that are being
deleted or updated. The functionality being when a record is deleted or
modified the original record details and the date of operation are stored in the
auditclient (client_no, name, bal_due, operation, userid, opdate) table, then the
delete or update is allowed to go through.
AIM:
To create a transparent audit system for a table. The system must keep
track of the records that are being deleted or updated.
SQL> create table client _master (client _no varchar2(15),name
varchar2(15),addressvarchar2(20),bal _due number(10,2));
Table created.
SQL> create table audit client(client _no varchar2(15),name varchar2(15),bal _due
number(10,2),operation varchar2(10),user id varchar2(10),opdate date);
Table created.
SQL> insert into client _master values(‘&client _no’, ’&name’, ’&address’, &bal _due);
Enter value for client_no:C00001
Enter value for name:arun
Enter value for address:dpi
Enter value for bal_due:2100
1 row created.
SQL>/
Enter value for
client_no:C00002Enter
value for name:deepak
Enter value for
address:dpi
Enter value for
bal_due:2000
1row created
SQL>/
Enter value for
client_no:C00003Enter
value for name:balaji
Enter value for
address:dpi
Enter value for
bal_due:2300
1 row created
SQL>/
Enter value for client_no:C00004
Enter value for name:manoj
Enter value for address:dpi
Enter value for bal_due:2300
1 row created
SQL>/
Enter value for client_no:C00005
Enter value for name:dhanu
Enter value for address:dpi
Enter value for bal_due:2400
1 row created
SQL>/
Enter value for client_no:C00006
Enter value for name:babu
Enter value for address:dpi
Enter value for bal_due:2300
1row created
SQL>/
Enter value for client_no:C00007
Enter value for name:naveen
Enter value for address:dpi
Enter value for bal_due:8000
1 row createdSQL>/
Enter value for
client_no:C00008Enter
value for name:nandha
Enter value for
address:dpi
Enter value for
bal_due:2200
1 row created.
SQL>select * from client_master;
CLIENT_NO CLIENT_NAME BAL_DUE
C00001 Arun 2100
C00002 Deepak 2000
C00003 Balaji 2300
C00004 Monaj 2300
C00005 Dhanu 2400
C00006 Babu 2300
C00007 Naveen 8000
C00008 Nandha 2200
8 rows selected.
PL/S
QL:
create or replace trigger audit _trails after update or delete on client
_master for each row.
declare
open varchar2(8);
client _no varchar2(6);
name varchar2(20);
bal _due number(10,2); begin
if updating then open:='update'; end if;
if deleting then open:='delete'; end if;
client _no:=:old.client _no;
name:=
:old.na
me;
bal _due:=:old.bal _due;
insert into audit client values(client _no, name, bal _due, oper, user,
sysdate);
end;
/
Trigger created.
SQL>select * from auditclient;
no rows selected
SQL> UPDATE Client _Master SET Bal _Due = 2000 WHERE Client _No =
'C00008';
1 row updated.
SQL> select * from client_master;
CLIENT_NO CLIENT_NAME BAL_DUE
C00001 Arun 2100
C00002 Deepak 2000
C00003 Balaji 2300
C00004 Monaj 2300
C00005 Dhanu 2400
C00006 Babu 2300
C00007 Naveen 8000
C00008 Nandha 2200
8 rows selected.
SQL>insert into audit client values(‘C00008’,’Arjun’,2000, ‘Update’, ’SYSTEM’, ’25-
FEB-19’);
SQL>select * from audit client;
CLIENT_NO CLIENT_NAME BAL_DUE OPERATION USERID DATE
C00008 Arjun 2000 Update System 25_feb_19
SQL>Select *From Client _Master WHERE Client_Name LIKE '0%';
2 rows deleted.
SQL> select * from client _master;
CLIENT_NO CLIENT_NAME BAL_DUE
C00001 Arun 2100
C00003 Balaji 2300
C00004 Monaj 2300
C00006 Babu 2300
C00007 Naveen 8000
C00008 Nandha 2200
SQL> select * from audit client;
Client Client_ Bal_du Operation User id Date
_no name e
C00008 Arun 2000 Update System 25_feb_
22
C00002 Deepak 2000 Update System 26_feb_
22
C00005 Dhanu 2400 Update System 26_feb_
22
RESULT:
Thus the transparent audit system for a table is successfully created and
executed.
