User talk:C27family

Assignment:

Procedures and Functions 1. Create 2 tables for employee and department. 1. Employee empid(PK), ename, ? salary, dob, doj, dno(FK) 2. department ? dno(PK), dname, loc 2. Insert 3 rows in the table 1. 1 admin cbe 2 developer cbe 3 manager che 2. 1 Tom 20000 10/10/83 10/07/09 1 2 Raj 25000 10/09/84 10/08/09 2 3 Sam 30000 10/08/85 10/09/09 1 4 John 20000 10/07/86 10/10/09 3 3. Display all the rows from the table 4. Display the oldest employee of the company 5. Display the experience of each employee in terms of days 6. Display the employees along with their department name 7. Display the number of employees in each department whose count is greater than 1 8. Display the employee details in their order of age 9. Create a table allocation that holds the id, name and deptname from the employee and department table along with data 10. Create a view on the employee table to view the id,name and salary for the employee working in department admin. The users are not allowed to modify any other rows and the base table must exists 11. Select the salary of the employee 1 and store it in the local variable. 12. For the above salary check whether the salary is greater than 10000. if yes print greater 13. Now create a function for the above scenario. 14. Update the salary of the employees who are working in admin department 15. Declare a local variable that holds the bonus amount and the department name and do the above update process 16. Create a procedure that accepts the salbonus and deptname to perform the above process 17. For the above procedure make the default salbonus as 5000 18. Modify the above procedure to display the max salary in admin department. 19. For the above procedure If the max salary is lesser than 40000 then again update the salary with another 2000 20. Select the employees working in admin department 21. Write a function for the above select query 22. Make the 5th question as function that will return the experience of each employee 23. Create a function that will accept the empid and display the eid,ename and whether they have increment or not. The increment is given only when the salary is lesser than 20000

__________________________________________________________________

1. create table Employee(ID number(6), Name varchar(20), Designation varchar(5), ProjectDomain varchar(10), Location varchar

(10), status varchar(10));

2. alter table Employee add Salary number(10);

3. alter table Employee modify Designation varchar(10);

4. alter table Employee drop column Salary;

5. alter table Employee rename column ID to EmployeeId; 6. alter table Employee add constraint emp_pk primary key(EmployeeId);

7. select constraint_name, constraint_type from user_constraints;

8. create table Department(Dno number(10), Dname varchar(10), DLOC varchar(10));

9. alter table department add constraint dep_pk primary key(dno);

10. alter table Employee add deptId number(2);

11. alter table Employee add constraint emp_fk foreign key(deptId) references Department(deptId);

_________________________________________________________

drop->drop table table_name;

truncate->truncate table table_name;

rename->rename table_name to newtable_name; __________________________________________________________

.RollBack ......

SQL> commit;

Commit complete.

SQL> delete from Employ;

1 row deleted.

SQL> select * from employ;

no rows selected

SQL> rollback;

Rollback complete. *to set the linesize: set linesize 130;(default is 88)

DML- -- - save point A; - - - save point B;

Rollback to A;

__________________________________________________________

DML: 1.insert,2.update, 3.delete, 4.merge ---

.insert into employ(employeeid, name, designation, projectdomain, location, salary, deptid) values(111, 'Tom', 'ASE',

'Telecom', 'Mumbai', 50000, 11);

.insert into employ values(222, 'Jerry', 'ASE', 'Telecom', 'Mumbai', 50000, 11);

.insert into employ(employeeid, name, designation, projectdomain, location, salary, deptid) values(333','Mickey', 'ASE',

'Telecom', 'Mumbai', 50000, 20);

.insert into employ values(&employeeid, '&name', &salary);

.insert into employ(employeeid, name, designation, projectdomain, location, salary, deptid) values(444, 'Pooh', 'ASE',

'Telecom', 'Mumbai', 50000, 11);

....................................

.update employ set location='mumbai';

.update employ set location='hyderabad' where employeeid=444;

________________________________________________________

.select * from table_name;

.select column1,column2 from table_name;

.select distinct col_name from table_name;

1.between 1 and 5 2.in(1,5);

3.like 's%' '%s%' '-s%'

.select * from employ where name like '__g%';

.select employeeid, name, location from employ;

.select distinct location from employ;

. select name from employ where location='hyderabad';

. select name from employ where employeeid between 111 and 333;

. select name from employ where employeeid in (100, 111);

. select name as "macho" from employ where name like 'A%y' or name like 'D%r';

__________________________________________________________________

functions:

1.single row: char, num, date conv, general

2.multi row: MAX, MIN, AVG, Count

char

Upper('str'); Lower('str'); Initcap('str');

num ---

Substr('str',num,num)

1st num=start, 2nd num= length single row

Replace('str', 'char', 'replace', 'char');

Lpad('str', 9, '*');

Rpad('str', 9, '*');

______________________________

round (num, num);		first num=value, second num=pos

trunc(num, num);

mod(num, num)

eg:

1.select round(33.33359)from dual;

ROUND(33.33359) ---            33

2. select round(33.33359, 3) from dual;

ROUND(33.33359,3) -          33.334

3. select trunc(33.234, 2) from dual;

TRUNC(33.234,2) ---         33.23

_______________________________

add_months(date, num);

months_between(date,date);

next_date('friday');

last_day(date);

_______________________________________

view --

1.simple 2.complex

1.create view view_name as (sub query);


 * to create a duplicate table:

create table dup_table_name as(select * from org_table );

create table dup_table_name as (select * from org_table where 1=2);
 * to create a duplicate structure of a table

- to drop the view:

drop view view_name;

_________________________

equijoin

select e.ename, d.dname from emp e, dept d where e.dno=d.dno;

eg:

select e.ename, e.dno from emp e, dept d where e.sal>300 and d.dname='TC';

selfjoining:

eg:

select c.ename as "employee",d.ename as "Manager" from empdetails c, empdetails d where c.managerId=d.empid;

outerjoin: --

e.dno=d.dno(+) //left join

e.dno(+)=d.dno //right join

____________________________________________________________________________________________________

1. create table emp(emp_no number(4), ename varchar(25), design char(4), join_date date, sal number(7,2), perks number(7,2),

dno number(2));

2. create table dept(dno number(2), dname varchar(25), budget number(15,2), manager varchar(25));

3. SQL> select sysdate from dual;

SYSDATE -	24-MAY-11

4.insert into emp values(1, 'Tom', 'CEOF', '21-MAR-11',50000,2000, 1);

insert into emp values(2, 'Jerry', 'LEAD', '25-MAR-11',25000,5000,4); insert into emp values(3, 'Mickey', 'MNGR', '29-MAR-11',29000,3000, 9);

update emp set dno=3 where emp_no=2; update emp set dno=2 where emp_no=3;

insert into dept values(1,'telecome', 20000000, 'Tom'); insert into dept values(2, 'bfs', 100000000, 'Jerry');

insert into dept values(3, 'retail', 50000, 'Mickey');

5.set autocommit on;

_________________________________

1.select ename, join_date from emp;

2.select ename as "employee"from emp where sal>25000;

3.select ename from emp where design not in ('sa', 'asa');

select ename from emp where design in ('ASET');

4. select ename, emp_no from emp order by ename;

select ename, emp_no from emp order by ename desc;

5. select ename from emp where ename like '%s%';

6. select ename, sal from emp where sal>perks;

7. select ename from emp where to_char(join_date,'yyyy')=2011;

8. select ename, design from emp where sal>10000 and dno=(select dno from dept where dname='telecome');

9. select ename, months_between(sysdate, join_date) "months worked" from emp order by "months worked";

select ename, round(months_between(sysdate, join_date),0)"months worked" from emp order by "months worked";

10. select ename, sal, join_date from emp where sal>(7*perks);

select ename,to_char(join_date,'ddth" of "Month","yyyy') from emp;

select ename,to_char(join_date,'ddth" of "Month","yyyy') as "Joining Date" from emp;

11. a. select ename, to_char(join_date, 'Mon ddth yy') as "Joining Date" from emp; b. select ename, to_char(join_date, 'mm/dd/yy') as "Joining Date" from emp;

12.select concat(upper(substr(dname,1,5)),' managed by'),manager from Dept;

13. select max(sal) from emp;

select min(sal) from emp;

*select ename, sal from emp where sal=(select max(sal) from emp); //for selecting the corresponding employ name;

14* select e.ename, d.dname, d.dno, d.manager from emp e, dept d where d.dno=e.dno;

15. select dNo,count(emp_no)from emp group by(dNo);

16.insert into emp values(4,'xyz','xxxx','11-feb-88',35000,10000,2);

17.

______________________________________________________________________________________

Assignment1

1.create table book(bookid number(5), bookname varchar(20), bAuthor varchar(20), bedition varchar(4), bprice number(5));

2.create table member(mid number(6), mname varchar(20), emailid varchar(20), age number(3), address varchar(30));

3.create table transaction( bid number(5), mid number(6), issuedate date);

4.alter table book add constraint book_pk primary key(bookid);

alter table member add constraint member_pk primary key(mid);

alter table transaction add constraint transaction_fk1 foreign key(bid) references book(bookid); alter table transaction add constraint transaction_fk2 foreign key(mid) references member(mid);

5. alter table book add(publication varchar(20));

6. alter table book add constraint check_price check(bprice>100);

7. alter table member add(membertype varchar(10));

alter table member add constraint member_chk check(membertype in ('student','faculty'));

8. alter table book rename column bprice to bookprice;

9. alter table book modify bedition default '3rd';

10.insert into member values(100,'Tom','Tom@tcs.com', 23,'mumbai','student');

update member set address='chennai' where membertype='student';

11.create table duplicate_book as(select * from book);

12.delete from transaction where to_date(issuedate)=to_date(sysdate);

_________________________________________________________________

Assignment2:

1.  alter table member add(dob date);

2.  update member set dob='29-jun-87' where mid=100;

3.  select round((to_date(sysdate)-to_date(dob))/365) from member;

4.  insert into member values(200,'Jerry', 'jerry@yahoo.com',22,'mumbai','student','01-Jan-89');

select mname from member where emailid like '%yahoo%';

5.  select mname from member where mname like 'j%';

6.  delete from book where bauthor like '%a' and bauthor like 's%';


 * 7. select m.mname, m.mid from member m,transaction t,book b where m.mid=t.mid and b.bookid=t.bid and b.bookname like 'the

alchemist';

8.  select * from member where to_char(dob,'mm') in (4,5,6,7,8,9);

9.  select * from transaction where to_char(issuedate,'yyyy')=2011;

10. select * from book where bookprice=(max(bookprice));

11. select sum(bookprice) as thetotalprice from book;

12. select concat(bookname,concat(' written by',concat(bauthor,concat(' and book edition is:',bedition)))) from book;

13. select * from member ,

________________________________________________________________________________________________________

Objects: -

-table

-View

-Sequence

-Index

-Synonym

Sequence: create sequence sq_name increament by 2

start with 1

maxvalue 200

minvalue 1

cycle

seq_name.currval

seq_name.nextval

eg:

1.create sequence sequencetest 2 increment by 2 3 start with 1 4 maxvalue 200 5 minvalue 1 6 cycle 7 cache 5;//cache value should be less than no of elements in the sequence

2. create sequence s1 increment by 2 start with 6 MAXvalue 10;

-

Index: --

index is used for faster access.

4 type:

1.uni

2.non-uni

3.bitmap

4.function

-create index index_name on table_name(column name);

Synonym:

create synonym syn_name for object;

________________________________________

A-automicity

C-consistent

I-Isolation

D-durable

| |  |  v DBM(DataBase Manager) | |  |  v Locking

Logging

Security

Optimization

Integrity

--- -grant all on emp to c2704;//to grant the access of my emp table

-select * from c2704.customers;//to access the granted customer table from c2704;

-show all;

-

-merge into dup_table d using org_table o on(d.id=o.id) when matched then update set d.dname=o.dname when not matched then

insert into values(d.id, d.....);

PL/SQL: _______

-make sure that:

>set serveroutput on;

Declare

>var_name varchar2(size);

>var_name tablename column%rowtype;

>vemp_no <-- emp.emp_no%datatype

>vemp_row <--emp%rowtype

-for printing:dbms_output.put_line(

program:

declare num1 number(2); num2 number(2); add number(4,2); begin num1:=11; num2:=33; add:=num1+num2; dbms_output.put_line('The sum is:'||add); end; /

program ---

declare num1 number(2); num2 number(2); add number(4,2); begin num1:=&num1; num2:=&num2; add:=num1+num2; dbms_output.put_line('The sum is:'||add); end; /

program

declare name varchar2(30); designation varchar2(4); location varchar2(20); age number(2); begin name:='&name'; designation:='&designation'; location:='&location'; age:=&age; dbms_output.put_line('name: '||name); dbms_output.put_line('designation: '||designation); dbms_output.put_line('location'||location); dbms_output.put_line('age: '||age); end; /

declare

program ---

declare deno number(3); begin select dno into deno from emp where sal<15000; dbms_output.put_line(deno); end; /

program ---

declare dno number(3); ro emp%rowtype; begin select * into ro from emp where dno=&dno; dbms_output.put_line(ro.ename); end; /

program:

declare num1 emp.emp_no%type salary emp%rowtype; begin num1:=&num1; select * into salary from emp where emp_no=num1; if(emp.design='MNGR') then update emp set sal=2*sal where emp_no=num1; else update emp set sal=0.5*sal where emp_no=num1; end if; end; /

program:

declare vsal emp.sal%type; begin select sal into vsal from emp where emp_no=1; if(vsal>25000) then update emp set design='mngr' where emp_no=1; else update emp set design='aset' where emp_no=1; end if; end; /

loop: _____

>loop statements Exit when (conditions) end loop;

>for(val_name) in 1..50 loop statements exit when (codition) end loop;

program:

(not working) declare dno number(3); x number(3); counter number(3); ro emp%rowtype; begin x:=&x; counter:=0; loop counter:=counter+1; select * into ro from emp where dno=x; dbms_output.put_line(ro.ename); exit when counter= end loop; end; /

program ---

declare i number(2); name varchar(30); salary number(5); begin for i in 1..5 loop select ename, sal into name,salary from emp where emp_no=i; dbms_output.put_line(name||' is getting salary: '||salary); end loop; end; /

cursor programming ---

declare vename emp.ename%type; vsal emp.sal%type; cursor my_cursor is select ename, sal from emp; begin open my_cursor; loop fetch my_cursor into vename, vsal; exit when my_cursor%notfound; dbms_output.put_line('name: '||vename||',salary: '||vsal); end loop; end; /

with for: -

declare vename emp.ename%type; vsal emp.sal%type; cursor my_cursor is select ename,sal from emp; begin for curr_row in my_cursor loop vename:=curr_row.ename; vsal:=curr_row.sal; exit when my_cursor%notfound; dbms_output.put_line('name: '||vename||',salary: '||vsal); end loop; end; /

declare cursor my_cursor is select ename ,sal from emp; begin for curr_row in my_cursor loop exit when my_cursor%notfound; dbms_output.put_line('name '||curr_row.ename||' salary: '||curr_row.sal); end loop; end; /

PROCEDURE: --

program:

create or replace procedure sum(num1 number, num2 number, sum out number) as begin sum:=num1+num2; end; / variable sum number; execute sum(11,22,:sum); print

program:

create or replace procedure sumofnnumbers(num number, summ out number) as begin summ:=0; for i in 1..num loop summ:= (summ+i); end loop; end; / variable summ number; execute sumofnnumbers(20,:summ); print

create or replace procedure factors(num number) as num1 number:=num; begin for i in 1..num1 loop if(num1%i=0) then dbms_output.put_line(' '||i); end if; end loop; end; /

execute factors(10);

creater or replace procedure insert_user(name varchar2, cond varchar2) as vcond number(2); begin vcond:=insert_cond(cond); if(vcond=1) then insert into temp values(name,cond,'less than 20000'); else insert into temp values(name,cond,'more than 20000'); end if; end; /

create or replce function insert_cond(cond varchar2) return number as vcond number; begin if(cond<20000) then vcond:=1; else vcond:=0; end if; return(vcond); end; /

---

id, name, dob,age, doj, experience, salary, bonus, working hours:

create table e(id number(5), name varchar2(30), dob date, age number(2), doj date, experience number(2), salary number(5),

bonus number(5), workinghoursperday number(5));

create sequence id increment by 1 start with 10000 MAXvalue 10010;

insert into e(id,name, dob,doj) values(10001,'abcd','13-jan-89','29-jun-11'); insert into e(id,name, dob,doj) values(10002,'jklm','15-jul-87','29-jun-2011'); insert into e(id,name, dob,doj) values(10003,'pqrs','13-jan-88','29-jun-2011'); insert into e(id,name, dob,doj) values(10004,'wxyz','13-jan-88','29-jun-2011');

update e set bonus=1000 where id=10001; update e set bonus=2000 where id=10002; update e set bonus=3000 where id=10003; update e set bonus=1000 where id=10004;

select round((to_date(sysdate)-to_date(dob))/365) from e;

select round((to_date(sysdate)-to_date(doj))/365) from e;

=
================update age=============================

create or replace procedure cal_age as cursor dob_cursor is select dob from e; dob1 date; begin open dob_cursor; loop fetch dob_cursor into dob1; exit when dob_cursor%notfound; update e set age=round(months_between(sysdate,dob1)/12) where dob=dob1; end loop; end; /

=
=======update workinghours============================

create or replace procedure cal_wh as cursor dob_cursor1 is select age from e; age1 number; begin open dob_cursor1; loop fetch dob_cursor1 into age1; exit when dob_cursor1%notfound; if (age1>22) then update e set workinghours=9 where age=age1; else update e set workinghours=8 where age=age1; end if; end loop; end; /

=
===update experience======================

create or replace procedure cal_exp as cursor doj_cursor is select doj from e; doj1 date; begin open doj_cursor; loop fetch doj_cursor into doj1; exit when doj_cursor%notfound; update e set experience=round(months_between(sysdate,doj1)/12) where doj=doj1; end loop; end; /

=
update salary=================================

create or replace procedure cal_sal as cursor exp_cursor1 is select experience,workinghours,bonus from e; experience1 number; workinghours1 number; bonus1 number; begin open exp_cursor1; loop fetch exp_cursor1 into experience1,workinghours1,bonus1; exit when exp_cursor1%notfound; if (experience1=0) then update e set salary=20000 where experience=experience1; else update e set salary=20000+workinghours*bonus where experience=experience1; end if; end loop; end; /

exception: --

declare exp1 varchar2(6):='first'; exp2 varchar2(6):='second'; vename varchar2(6); begin select ename into vename from emp where emp_no=111; exception when no_date_found then dbms_output.put_line(exp1); when others then dbms_output.put_line(exp2); end; /

declare my_ename emp.ename%type:='shaan'; vsal number; too_few exception; begin select sal into vsal from emp where ename=my_ename; if vsal<20000 then raise too_few; end if; exception when too_few then insert into temp values values(my_ename, vsal,'less than 20000'); commit; end; /

_________________________________________

Package: -

1.pack declaration.....>procedure, function 2.pack body............>definition procedure ...........>definition function

declaration: ---

create or replace package hr as procedure add(id number,name varchar2,dob date,age number,doj date,experience number,salary number,bonus number,workinghours

number); procedure remove(empid number); valid varchar2(5); end hr;

definition: ---

create or replace package body hr as

function check_cond(age number) return varchar2 as cond varchar2(5); begin dbms_output.put_line(age); cond:='yes'; if age<18 then cond:='no'; end if; return(cond); end;

procedure add(id number,name varchar2,dob date,age number,doj date,experience number,salary number,bonus number,workinghours

number) as begin valid:=check_cond(age); dbms_output.put_line(valid); if valid='yes' then insert into e values(id,name,dob,age,doj,experience,salary,bonus,workinghours); end if; end;

procedure remove(empid number) as begin delete from e where id=empid; end; end hr;

execute hr.add(11, 'dasu', '01-mar-87', 24, '29-mar-11',2,0,0,0); execute hr.remove(10004);

________________________________________________________________________ common/PLSQL assignment: -

create table employee(empid number(5), ename varchar2(30), salary number(6), dob date, doj date, dno number(2));

create table department(dno number(2), dname varchar2(30), loc varchar2(20));

alter table employee add constraint emp1_fk foreign key(dno) references department(dno);

---

employee: --

declaration: ---

create or replace package myPackage as procedure add(empid number,ename varchar2,salary number,dob date,doj date,dno number); end myPackage;

definition: ---

create or replace package body myPackage as

procedure add(empid number,ename varchar2,salary number,dob date,doj date,dno number) as begin insert into employee values(empid,ename,salary,dob,doj,dno); end; end myPackage;

execution: --

execute myPackage.add(1,'Tom',20000,'10-oct-1983','10-jul-2009', 1);

execute myPackage.add(2,'Raj',25000,'10-sep-1984','10-aug-2009', 2);

execute myPackage.add(3,'Sam',30000,'10-aug-1985','10-sep-2009', 1);

execute cal_age; execute myPackage.add(4,'John',20000,'10-jul-1986','10-oct-2009', 3);

department: --

declaration: ---

create or replace package myPackage2 as procedure add(dno number,dname varchar2,loc varchar2); end myPackage2;

definition: ---

create or replace package body myPackage2 as

procedure add(dno number,dname varchar2,loc varchar2) as begin insert into department values(dno,dname,loc); end; end myPackage2;

execution: --

execute myPackage2.add(1,'admin','cbe');

execute myPackage2.add(2,'developer','cbe');

execute myPackage2.add(3,'manager','che');

-

alter table employee add age number(2);

=
2================================

create or replace procedure showemp as rowid employee.empid%type; rowname employee.ename%type; rowsalary employee.ename%type; rowdob employee.ename%type; rowdoj employee.ename%type; rowdno employee.ename%type; cursor showemp_cursor is select empid,ename,salary,dob,doj,dno from employee; begin dbms_output.put_line('empid ename  salary  dob      doj       dno'); open showemp_cursor; loop fetch showemp_cursor into rowid,rowname,rowsalary,rowdob,rowdoj,rowdno ; dbms_output.put_line(rowid||'     '||rowname||'   '||rowsalary||'  '||rowdob||'  '||rowdoj||'  '||rowdno); exit when showemp_cursor%notfound; end loop; end;

=
=3============================ create or replace procedure cal_age as cursor dob_cursor is select dob from employee; age1 date; begin open dob_cursor; loop fetch dob_cursor into age1; exit when dob_cursor%notfound; update employee set age=round(months_between(sysdate,dob)/12) where dob=age1; end loop; end;

=
=4=========================== create or replace procedure highestage as cursor age_cursor is select age from employee; hage number(3):=0; age1 number(3); begin open age_cursor; loop fetch age_cursor into age1; exit when age_cursor%notfound; if (age1>hage) then hage:=age1; end if; end loop; dbms_output.put_line(hage); end;

=
===5=========================

create or replace procedure dayscalculation as cursor day_cursor is select doj from employee; doj1 date; begin open day_cursor; loop fetch day_cursor into doj1; exit when day_cursor%notfound; update employee set experience=trunc(sysdate-doj1) where doj=doj1; end loop; end;

=
==6================================

create or replace procedure empdep as cursor empdep_cursor is select e.ename,d.dname from employee e, department d where e.dno=d.dno; ename1 varchar2(20); dname1 varchar2(20); begin dbms_output.put_line('ename   dname'); open empdep_cursor; loop fetch empdep_cursor into ename1, dname1; exit when empdep_cursor%notfound; dbms_output.put_line(ename1||'    '||dname1); end loop; end;

=
==7=================================

create or replace procedure empno as cursor empno_cursor is select dno,count(empid)from employee group by(dno); dno1 number(2); counter number(2); begin dbms_output.put_line('dno no of employee'); open empno_cursor; loop fetch empno_cursor into dno1,counter; exit when empno_cursor%notfound; dbms_output.put_line(dno1||'    '||counter); end loop; end;

=
=8===================================

create or replace procedure orderemp as cursor orderemp_cursor is select empid,ename,to_number(to_char(sysdate,'yyyy'))-to_number(to_char(dob,'yyyy')) from employee

order by 3; id number; name varchar2(12); age1 number; begin dbms_output.put_line('id name age'); open orderemp_cursor; loop fetch orderemp_cursor into id,name,age1; exit when orderemp_cursor%notfound; dbms_output.put_line(id||' '||name||' '||age1); end loop; end;

=
==9======================================

create table allocation as(select empid, ename, dname from employee e, department d where e.dno=d.dno);

_______________________________________________________

TRIGGER: _________

create table emp_count as(select dno,count(emp_no) as no_of_count from emp group by(dno));

example:

create or replace trigger cnt3 after insert or delete on emp for each row begin if(inserting) then dbms_output.put_line('inserted'); update emp_count set no_of_count=no_of_count+1; elsif(deleting) then dbms_output.put_line('deleted'); update emp_count set no_of_count=no_of_count-1; end if; end;

create or replace trigger update_emp before update of salary on emp for each row declare too_low exception; begin if(:old.salary>=:new.salary) then raise too_low; end if; exception when too_low then dbms_output.put_line('salary too low'); rollback; end;

create or replace trigger update_emp before update of salary on emp for each row begin if(:old.salary>=:new.salary) then raise_application_error(-20502,'salary too low'); end if; end;

______________________________________________________________

assingment: --

create table temp(name varchar(20), address varchar(20), qualification varchar(15), email varchar(20), phoneno number(10),

amount number(15));

create table customer(accountno number(13),password number(10), name varchar(20),address varchar(20), qualification varchar

(15), email varchar(20), phoneno number(10), amount number(15));

create table admin(count_customer number(10), sum_amount number(15), entry date);

--

create or replace procedure update_table as row temp%type; password number; cursor c1 is select * from temp; begin password:=10000; open c1; loop fetch c1 into row; pwd:=pwd+1; insert into customer values(acc_sq.nextval,pwd,row.name,row.address,row.qualification,row.email,row.phoneno,row.amount); dbms_output.put_line('inserted into customer'); exit when c1%notfound; end loop; delete from temp; end;

create or replace trigger update_admin before insert or delete on customer for each row declare counter number; balance number; totalbalance number; begin cursor c1 is select amount from customer; open c1; select count(accountno) into counter from customer; loop fetch c1 into balance; exit when c1%notfound; totalbalance:=totalbalance+balance; dbms_output.put_line('balance'||balance); end loop; if(inserting) then dbms_output.put_line('customer added'); insert into admin values(counter, totalbalance, sysdate); elsif(deleting) then dbms_output.put_line('customer deleted'); insert into admin values(counter, totalbalance, sysdate); end if; end;

TCS Internal Tarzan Book store Automation System Tarzan book store decided to introduce a automated system where its customer can search, reserve or buy books according to their needs. Tarzan Automation System maintains the list of books along with quantity. Administrator should be able to add books to the inventory. Administrator will add books with book name, author name, category (Java, Philosophy, Fiction, OS, self help etc.), price and quantity. Anybody can log on to the system to browse through the books available. It should have a facility to search books by category or author name or book name .Out of stock status needs to be displayed based on the quantity. If anybody decides to buy a book, he needs to give a customer id to do the transaction. If new customer, he needs to register with the system by giving his personal details that are name, age, sex, address and contact number. Customer should have a facility to buy more than one book at one click. Customer can either choose to buy a book or add it in his “my books” list. If he chooses to buy book/books, system needs to get the mode of payment (credit/debit card) and collect the card details. If customer chooses to add book/books to “my books”, the list of selected book need to be added to his “my books” list. Next time, when customer logs in, there should be an option to view “my books”. From there, option is needed to buy the book. Also, customer needs to have an option to see his previously ordered books with buy date. And the system should keep the reports of the entire sale done, in sorted order based on date specified. Deliverables 1. Use case, Sequence diagram and ERD. 2. Class Diagram 3. Unit Test Plan 4. Coding Incorporate the additional features in the case study 1. Scheduling the job using CRONTAB at 10:00 am daily. 2. Log files are to be archived for each run. The log files have to be renamed with the current run time stamp and moved to the archive folder. TCS Internal 3. Purge the Log files in the archive folder which are greater than one month. 4. The purging script for the database tables has to be maintained. Maintain 3 months of data in the tables (2 last months plus 1 current month). 5. Ensure the script is restart-able (driver script). Use Shell script as a driver for executing the loading, ftp, archiving, purging database and log archive and executing C++ code executables. 6. Use make feature to create the executable for the C++ code. 7. Use Oracle as the database instead of flat files.