KEMBAR78
Rdbms Basic Programs | PDF | Programming Paradigms | Computer Programming
Scribd
Upload
13 views10 pages

Rdbms Basic Programs

The document contains a series of SQL commands demonstrating the creation and manipulation of database tables, including 'detail', 'employee17', and 'voter'. It shows how to insert, select, update, and delete records, as well as the creation of a function and triggers for handling operations on the 'voter' table. Additionally, it highlights error handling in SQL commands and the use of PL/SQL for procedural programming.

Uploaded by

Dhana Sekaran
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views10 pages

Rdbms Basic Programs

The document contains a series of SQL commands demonstrating the creation and manipulation of database tables, including 'detail', 'employee17', and 'voter'. It shows how to insert, select, update, and delete records, as well as the creation of a function and triggers for handling operations on the 'voter' table. Additionally, it highlights error handling in SQL commands and the use of PL/SQL for procedural programming.

Uploaded by

Dhana Sekaran
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 10

SQL> desc detail;

Name Null? Type


----------------------------------------- -------- ----------------------------

PHONE NUMBER(7)
USER_NAME VARCHAR2(30)
ADDR VARCHAR2(30)

SQL> insert into detail values(2247346,'yyy','bu street');

1 row created.

SQL> insert into detail values(2256780'zzz','21st street');


insert into detail values(2256780'zzz','21st street')
*
ERROR at line 1:
ORA-00917: missing comma

SQL> insert into detail values(2256780,'zzz','21st street');

1 row created.

SQL> insert into detail values(2256781,'xxx','ma street');

1 row created.

SQL> select * from detail;

PHONE USER_NAME ADDR


---------- ------------------------------ ------------------------------
2247346 yyy bu street
2256780 zzz 21st street
2256781 xxx ma street

SQL> set serveroutput on


SQL> create or replace function find_address(pno in number) return varchar is ad
dress varchar(20);
2 begin
3 select addr into address from detail where phone=pno;
4 return address;
5 end;
6 /

Function created.

SQL> set serveroutput on


SQL> declare
2 ad varchar(20);
3 begin
4 select find_address(phone) into ad from detail where phone=2247346;
5 dbms_output.put_line('address is='||ad);
6 end;
7 /
address is=bu street

PL/SQL procedure successfully completed.

SQL> create table employee17(emp_no number(5),emp_name varchar(10),salary number


(5),emp_address varchar(12),city varchar(10),pincode number(6));

Table created.
SQL> desc employee17;
Name Null? Type
----------------------------------------- -------- ----------------------------

EMP_NO NUMBER(5)
EMP_NAME VARCHAR2(10)
SALARY NUMBER(5)
EMP_ADDRESS VARCHAR2(12)
CITY VARCHAR2(10)
PINCODE NUMBER(6)

SQL> insert into employee17 values(11001,'ddd',50000,'bm nagar','vellore',632005


);

1 row created.

SQL> insert into employee17 values(11002,'aaa',48000,'k.k.nagar','arani',632314)


;

1 row created.

SQL> insert into employee17 values(11003,'zzz',45000,'g.r.nagar','vellore',63235


4);

1 row created.

SQL> select * from emloyee17;


select * from emloyee17
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from employee17;

EMP_NO EMP_NAME SALARY EMP_ADDRESS CITY PINCODE


---------- ---------- ---------- ------------ ---------- ----------
11001 ddd 50000 bm nagar vellore 632005
11002 aaa 48000 k.k.nagar arani 632314
11003 zzz 45000 g.r.nagar vellore 632354

SQL> set serveroutput on


SQL> declare
2 eno employee17.emp_no%type;
3 ena employee17.emp_name%type;
4 esal employee17.salary%type;
5 cursor c1 is select emp_no,emp_name,salary from employee17;
6 begin
7 open c1;
8 fetch c1 into eno,ena,esal;
9 update employee17 set salary=75000 where salary>50000;
10 dbms_output.put_line('Employee no:||eno||'Employee name:'||ena||'the salary
is:'||esal);
11 close c1;
12 end;
13 /
ERROR:
ORA-01756: quoted string not properly terminated

SQL> set serveroutput on


SQL> declare
2 eno employee17.emp_no%type;
3 ena employee17.emp_name%type;
4 esal employee17.salary%type;
5 cursor c1 is select emp_no,emp_name,salary from employee17;
6 begin
7 open c1;
8 fetch c1 into eno,ena,esal;
9
10 update employee17 set salary=75000 where salary>50000;
11 dbms_output.put_line('Employee no:'||eno||'Employee name:'||ena||'the salar
y is:'||esal);
12 close c1;
13 end;
14 /
Employee no:11001Employee name:dddthe salary is:50000

PL/SQL procedure successfully completed.

SQL> create table voter(name varchar(10),age number(5),id number(5),gender varch


ar(10));

Table created.

SQL> desc voter;


Name Null? Type
----------------------------------------- -------- ----------------------------

NAME VARCHAR2(10)
AGE NUMBER(5)
ID NUMBER(5)
GENDER VARCHAR2(10)

SQL> insert into voter values('&name',&age,&id,'&gender');


Enter value for name: pasupathi
Enter value for age: 20
Enter value for id: 1718
Enter value for gender: male
old 1: insert into voter values('&name',&age,&id,'&gender')
new 1: insert into voter values('pasupathi',20,1718,'male')

1 row created.

SQL> /
Enter value for name: pandiyan
Enter value for age: 19
Enter value for id: 1719
Enter value for gender: male
old 1: insert into voter values('&name',&age,&id,'&gender')
new 1: insert into voter values('pandiyan',19,1719,'male')

1 row created.

SQL> /
Enter value for name: xxx
Enter value for age: 18
Enter value for id: 1567
Enter value for gender: female
old 1: insert into voter values('&name',&age,&id,'&gender')
new 1: insert into voter values('xxx',18,1567,'female')

1 row created.
SQL> select * from voter;

NAME AGE ID GENDER


---------- ---------- ---------- ----------
pasupathi 20 1718 male
pandiyan 19 1719 male
xxx 18 1567 female

SQL> create or replace trigger vot1 after update or delete on voter for each row

2 declare
3 name1 varchar2(10);
4 age1 number(3);
5 id1 number(5);
6 open varchar2(25);
7 begin
8 if updating then
9 open:='update operation';
10 else
11 open:='delete operation';
12 end if;
13 id1:=old.name;
14 age1:=:old.name;
15 name1:=:old.age;
16 dbms_output.put_line('the'||oper||'was sucessfully');
17 dbms_output.put_line('operation performed on the following data');
18 dbms_output.put_line('ID:='||id1||'Name:='||name1||'Age:='||age1);
19 end;
20 /
Warning: Trigger created with compilation errors.

SQL> create or replace trigger vot1 after update or delete on voter for each row

2 declare
3 name1 varchar2(10);
4 age1 number(3);
5 id1 number(5);
6 oper varchar2(25);
7 begin
8 if updating then
9 oper:='update operation';
10 else
11 oper:='delete operation';
12 end if;
13 id1:=:old.id;
14 name1:=:old.name;
15 age1:=:old.age;
16 dbms_output.put_line('the'||oper||'was sucessfully');
17 dbms_output.put_line('operation performed on the following data');
18 dbms_output.put_line('ID:='||id1||'Name:='||name1||'Age:='||age1);
19 end;
20 /

Trigger created.

SQL> SQL> create or replace trigger vot1 after update or delete on voter for eac
h row
SP2-0734: unknown command beginning "SQL> creat..." - rest of line ignored.
SQL>
SQL> 2 declare
SQL> 3 name1 varchar2(10);
SQL> 4 age1 number(3);
SQL> 5 id1 number(5);
SQL> 6 oper varchar2(25);
SQL> 7 begin
SQL> 8 if updating then
SQL> 9 oper:='update operation';
SQL> 10 else
SQL> 11 oper:='delete operation';
SQL> 12 end if;
SQL> 13 id1:=:old.id;
SQL> 14 name1:=:old.name;
SQL> 15 age1:=:old.age;
SQL> 16 dbms_output.put_line('the'||oper||'was sucessfully');
SQL> 17 dbms_output.put_line('operation performed on the following data');
SQL> 18 dbms_output.put_line('ID:='||id1||'Name:='||name1||'Age:='||age1);
SQL> 19 end;
SQL> 20 /
SQL>
SQL> Trigger created.
SP2-0734: unknown command beginning "Trigger cr..." - rest of line ignored.
SQL>
SQL> update voter set name='pasu' where id=1718;
theupdate operationwas sucessfully
operation performed on the following data
ID:=1718Name:=pasupathiAge:=20

1 row updated.

SQL> select * from voter;


NAME AGE ID GENDER
---------- ---------- ---------- ----------
pasu 20 1718 male
pandiyan 19 1719 male
xxx 18 1567 female

SQL> delete from voter where id=1718;


thedelete operationwas sucessfully
operation performed on the following data
ID:=1718Name:=pasuAge:=20

1 row deleted.

SQL> select * from voter;

NAME AGE ID GENDER


---------- ---------- ---------- ----------
pandiyan 19 1719 male
xxx 18 1567 female

SQL>

You might also like