PL/SQL Query Practice #2

insert into employee values (employee_id, employee_name, department_name , designation_name , date_of_birth , gender , pagex, basic_pay, nature_of_designation);

dbms_output.put_line('Record Inserted');

end;

/

Output

SQL> select * from employee;

EMPID NAME DEPT DESIGNATION date_of_birth G AGE

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

BASICPAY NATUREOFDE

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

1 Deepanshu HR Manager 10-JAN-92 M 23

564861 PERMANENT

2 Hrithik technical General Man 10-JAN-73 M 42

215863 PERMANENT

51 Arvind AAP Leader 13-MAR-74 M 41

13225 TEMPORARY

EMPID NAME DEPT DESIGNATION date_of_birth G AGE

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

BASICPAY NATUREOFDE

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

43A John Cena WWE Wrestler 22-OCT-86 M 28

13225 PERMANENT

H3R Chitrangadha Film Industry Actress 14-NOV-89 F 25

29150 TEMPORARY

50 Pranjul Google Scientist 23-NOV-93 M 21

108900 PERMANENT

6 rows selected.

Delete a Record

declare

Id employee.employee_id%type := &Id;

c number;

begin

select count(*) into c from employee where employee_id = Id;

if c>0 then

delete from employee where employee_id = Id;

dbms_output.put_line('Record Deleted!!');

else

dbms_output.put_line('No record Found');

end if;

end;

/

Update a Record

declare

Id employee.employee_id%type := &Id;

c number;

nam employee.employee_name%type := &nam;

begin

select count(*) into c from employee where employee_id = Id;

if c>0 then

update employee set employee_name = nam where employee_id = Id;

dbms_output.put_line('Record Updated!!');

else

dbms_output.put_line('No record Found');

end if;

end;

/

Write a PL/SQL procedure for

Increase salary of temporary employees
1. 10% for less than equal to 5000
2. 20% for less than equal to 8000
3. 15% for less than equal to 15000
4. 10% for all others
Organize the data in ascending order of employee number
Retrieve the name, basic pay, and designation of an employee whose number is input by the user.
Calculate tax of an employee and to display his/her name and tax
Display employee number, name and basic of 5 highest paid employees
Calculate the total salary of first n records of employee table. The value of n is passed as parameter
Update the salary of employees who earn less than the average salary

Solutions

Increase salary of temporary employees (i) 10% for less than equal to 5000 (ii) 20% for less than equal to 8000 (iii) 15% for less than equal to 15000 (iv) 10% for all others

declare

cursor c is select * from employee;

begin

for Sal in c loop

if Sal.nature_of_designation = 'TEMPORARY' then

if Sal.basic_pay <= 5000 then

Sal.basic_pay := Sal.basic_pay*(1.1);

elsif Sal.basic_pay > 5000 and Sal.basic_pay <= 8000 then

Sal.basic_pay := Sal.basic_pay*(1.2);

elsif Sal.basic_pay > 8000 and Sal.basic_pay <= 15000 then

Sal.basic_pay := Sal.basic_pay*(1.15);

else

Sal.basic_pay := Sal.basic_pay*(1.1);

end if;

update employee set basic_pay = Sal.basic_pay where EMpId = Sal.employee_id;

dbms_output.put_line('Salary Incemented');

end if;

end loop;

end;

/

Organize the data in ascending order of employee number

declare

cursor c is select * from employee order by employee_id;

begin

for tempc in c loop

dbms_output.put_line(chr(10) ||'ID : ' || tempc.employee_id );

dbms_output.put_line('employee_name : ' || tempc.employee_name );

dbms_output.put_line('age : ' || tempc.age );

dbms_output.put_line('gender : ' || tempc.gender );

dbms_output.put_line('Basic Pay : ' || tempc.basic_pay );

dbms_output.put_line('date_of_birth : ' || tempc.date_of_birth );

end loop;

end;

/

Output

/*

ID : 1

employee_name : Deepanshu

age : 23

gender : M

Basic Pay : 564861

date_of_birth : 10-JAN-92

ID : 2

employee_name : Hrithik

age : 42

gender : M

Basic Pay : 215863

date_of_birth : 10-JAN-73

ID : 43A

employee_name : John Cena

age : 28

gender : M

Basic Pay : 13225

date_of_birth : 22-OCT-86

ID : 50

employee_name : Pranjul

age : 21

gender : M

Basic Pay : 108900

date_of_birth : 23-NOV-93

ID : 51

employee_name : Arvind

age : 41

gender : M

Basic Pay : 13225

date_of_birth : 13-MAR-74

ID : H3R

employee_name : Chitrangadha

age : 25

gender : F

Basic Pay : 29150

date_of_birth : 14-NOV-89

*/

Retrieve the name, basic pay, and designation of an employee whose number is input by the user

declare

cursor c is select * from employee;

Id employee.employee_id%type := &Id;

begin

for Sal in c loop

if Sal.employee_id = Id then

dbms_output.put_line(chr(10) || 'NAME : ' || Sal.employee_name || chr(10));

dbms_output.put_line('BASIC PAY : ' || Sal.basic_pay || chr(10));

dbms_output.put_line('DESIGNATION : ' || Sal.designation_name || chr(10));

end if;

end loop;

end;

/

Calculate tax of an employee and to display his/her name and tax

declare

cursor c is select * from employee;

DA number;

HRA number;

CCA number;

GP number;

AP number;

temp number;

Tax number;

begin

for Sal in c loop

temp := Sal.basic_pay;

DA := temp*(0.51);

HRA := temp*(0.20);

CCA := 500;

GP := temp + DA + HRA + CCA;

AP := GP*12;

if Sal.age < 60 then

if AP <= 200000 then

Tax := 0;

elsif AP > 200000 and AP <= 500000 then

Tax := AP*(0.1);

elsif AP > 500000 and AP <= 1000000 then

Tax := AP*(0.2);

else

Tax := AP*(0.3);

end if;

else

if AP <= 250000 then

Tax := 0;

elsif AP > 250000 and AP <= 500000 then

Tax := AP*(0.1);

elsif AP > 500000 and AP <= 1000000 then

Tax := AP*(0.2);

else

Tax := AP*(0.3);

end if;

dbms_output.put_line(chr(10) || 'NAME : ' || Sal.employee_name || chr(10)|| 'TAX : ' || Tax);

end loop;

end;

/

Output

/*

NAME : Deepanshu

TAX : 3479084.316

NAME : Hrithik

TAX : 1330652.628

NAME : Arvind

TAX : 27737.7

NAME : John Cena

TAX : 27737.7

NAME : Chitrangadha

TAX : 120831.6

NAME : Pranjul

TAX : 672188.4

*/

Display employee number, name and basic of 5 highest paid employees

declare

cursor c is select * from employee order by basic_pay desc;

x number;

begin

x := 0;

for sal in c loop

if x < 5 then

dbms_output.put_line(chr(10) || 'Employee Number : ' || sal.employee_id);

dbms_output.put_line('employee_name : ' || sal.employee_name);

dbms_output.put_line('Basic Pay : ' || sal.basic_pay);

x := x+1;

end if;

end loop;

end;

/

Calculate the total salary of first n records of employee table. The value of n is passed as parameter

declare

cursor c is select * from employee;

n number := &n;

x number;

z number;

DA number;

HRA number;

CCA number;

GP number;

AP number;

temp number;

PF number;

NP number;

Tax number;

begin

z := 0;

select count(*) into x from employee;

if n > x then

dbms_output.put_line('Less Recordes available then number entered');

return;

end if;

for Sal in c loop

z := z+1;

if z>n then

exit;

end if;

temp := Sal.basic_pay;

DA := temp*(0.51);

HRA := temp*(0.20);

CCA := 500;

GP := temp + DA + HRA + CCA;

AP := GP*12;

PF := temp*(0.25);

if Sal.age < 60 then

if AP <= 200000 then

Tax := 0;

elsif AP > 200000 and AP <= 500000 then

Tax := AP*(0.1);

elsif AP > 500000 and AP <= 1000000 then

Tax := AP*(0.2);

else

Tax := AP*(0.3);

end if;

else

if AP <= 250000 then

Tax := 0;

elsif AP > 250000 and AP <= 500000 then

Tax := AP*(0.1);

elsif AP > 500000 and AP <= 1000000 then

Tax := AP*(0.2);

else

Tax := AP*(0.3);

end if;

NP := AP - PF - Tax;

dbms_output.put_line(chr(10) || 'NAME : ' || Sal.employee_name || chr(10)|| 'SALARY : ' || NP);

end loop;

end;

Update the salary of employees who earn less than the average salary

declare

cursor c is select * from employee;

t number;

begin

select avg(basic_pay) into t from employee;

for Sal in c loop

if Sal.basic_pay < t then

if Sal.basic_pay <= 5000 then

Sal.basic_pay := Sal.basic_pay*(1.1);

elsif Sal.basic_pay > 5000 and Sal.basic_pay <= 8000 then

Sal.basic_pay := Sal.basic_pay*(1.2);

elsif Sal.basic_pay > 8000 and Sal.basic_pay <= 15000 then

Sal.basic_pay := Sal.basic_pay*(1.15);

else

Sal.basic_pay := Sal.basic_pay*(1.1);

end if;

update employee set basic_pay = Sal.basic_pay where EMpId = Sal.employee_id;

dbms_output.put_line('Salary Updated');

end if;

end loop;

end;