Payroll Database Management System Display employee details Selection

By through select sql command we can list out detail from the table form payroll database management system. It will list out employee details, department details, employement office details, and the salary details
SQL> select * from emp_detail;

EMP_NO EMP_NAME AGE S ADDRESS CITY
------------ ---------------- ----- -- ----------------- ------------
1 mythreye 21 f 19 vadivalpuram chennai
24 subramani 43 m 19 ram st salem
76 jaya 27 f 12 lok gardens madurai
87 ravi 56 m 12 harbour road tuticorin
40 john 30 m 21 sampath st coimbatore
6 jana 25 m 12 raj st chennai
6 rows selected.

SQL> select * from dept_detail;
DEPT_NO DEPT_NAME LOCATION
------------- ------------------ ------------------
123 technical bangalore
124 accounts salem
126 marketing madurai
125 export & import tuticorin
128 research coimbatore

5 rows selected.
Source code in PL SQL programming Algorithm
SQL> select * from emp_off;
EMP_NO EMP_NAME DEPT_NO DOJ DESIGNATION
----------- ---------------- ------------- ------------- ------------
1 mythreye 123 21-AUG-00 programer
24 subramani 124 02-NOV-95 manager
76 jaya 126 05-DEC-99 sales officer
87 ravi 125 06-JAN-93 assit.gm
40 john 128 07-FEB-99 qcmanager
6 jana 123 06-AUG-00 manager
6 rows selected.

Source code in PL SQL programming Algorithm
SQL> select * from salary ;
EMP_NO BASIC TA DA HRA AX PPF NET
--------- ----------- --------- --------- --------- --------- --------- ---------
1 10000 1000 700 1500 3000 1000 9200
24 15000 1500 1050 2250 4500 1500 13800
76 12000 1200 840 1800 3600 1200 11040
87 20000 2000 1400 3000 6000 2000 18400
40 11000 1100 770 1650 3300 1100 10120
6 12000 1200 840 1800 3600 1200 11040
6 rows selected.


DISPLAYING A PERTICULAR EMPLOYEE DETAIL Payroll management System
Source code in PL SQL programming Algorithm
SQL> create or replace procedure emp_disp(empnumber in number) as
2 emp_per emp_detail%rowtype;
3 emp_off_det emp_off%rowtype;
4 dept_det dept_detail%rowtype;
5 salary_det salary%rowtype;
6 begin
7 select * into emp_per from emp_detail where emp_no=empnumber;
8 select * into emp_off_det from emp_off where emp_no=empnumber;
9 select * into dept_det from dept_detail where dept_no=emp_off_det.dept_no;
10 select * into salary_det from salary where emp_no=empnumber;
11 dbms_output.put_line('*************personal detail*************');
12 dbms_output.put_line('name:'||emp_per.emp_name);
13 dbms_output.put_line('age:'||emp_per.age);
14 dbms_output.put_line('sex:'||emp_per.sex);
15 dbms_output.put_line('address:'||emp_per.address);
16 dbms_output.put_line('city:'||emp_per.city);
17 dbms_output.put_line('*************office detail************');
18 dbms_output.put_line('id:'||emp_per.emp_no);
19 dbms_output.put_line('department:'||dept_det.dept_name||' at '||dept_det.location);
20 dbms_output.put_line('designation:'||emp_off_det.designation);
21 dbms_output.put_line('date of joining:'||emp_off_det.doj);
22 dbms_output.put_line('*************salary detail************');
23 dbms_output.put_line('basic salary:'||salary_det.basic);
24 dbms_output.put_line('net salary:'||salary_det.net);
25 exception
26 when no_data_found then
27 dbms_output.put_line('no maching data');
28 end;
29 /
Procedure created.

Source code in PL SQL programming Algorithm
SQL> exec emp_disp(40);
*************personal detail*************
name:john
age:30
sex:f
address:21 sampath st
city:coimbatore
*************office detail************
id:40
department:research at coimbatore
designation:qcmanager
date of joining:07-FEB-99
*************salary detail************
basic salary:11000
net salary:10120
PL/SQL procedure successfully completed.
SQL> exec emp_disp(2);
No matching data
PL/SQL procedure successfully completed.

DISPLAYING THE EMPLOYEE DETAIL OF A PERTICULAR DEPARTMENT_NUMBER payroll Management System
Source code in PL SQL programming Algorithm

SQL> create or replace procedure dept_disp(deptnumber in number) as
2 emp_per emp_detail%rowtype;
3 emp_off_det emp_off%rowtype;
4 dept_det dept_detail%rowtype;
5 salary_det salary%rowtype;
6 cursor emp_cur is select * from emp_off where dept_no=deptnumber;
7 begin
8 open emp_cur;
9 loop
10 fetch emp_cur into emp_off_det;
11 if emp_cur%rowcount=0 then
12 dbms_output.put_line('no matchimg detail');
13 exit;
14 else
15 exit when emp_cur%notfound;
16 select * into dept_det from dept_detail where dept_no=deptnumber;
17 select * into emp_per from emp_detail where emp_no=emp_off_det.emp_no;
18 select * into salary_det from salary where emp_no=emp_off_det.emp_no;
19 dbms_output.put_line('*************personal detail*************');
20 dbms_output.put_line('name:'||emp_per.emp_name);
21 dbms_output.put_line('age:'||emp_per.age);
22 dbms_output.put_line('sex:'||emp_per.sex);
23 dbms_output.put_line('address:'||emp_per.address);
24 dbms_output.put_line('city:'||emp_per.city);
25 dbms_output.put_line('*************office detail************');
26 dbms_output.put_line('id:'||emp_per.emp_no);
27 dbms_output.put_line('department:'||dept_det.dept_name||' at '||dept_det.location);
28 dbms_output.put_line('designation:'||emp_off_det.designation);
29 dbms_output.put_line('date of joining:'||emp_off_det.doj);
30 end if;
31 end loop;
32 close emp_cur;
33 end;
34 /

Procedure created.
Source code in PL SQL programming Algorithm
SQL> exec dept_disp(123);
*************personal detail*************
name:mythreye
age:21
sex:f
address:19 vadivalpuram
city:chennai
*************office detail************
id:1
department:technical at bangalore
designation:programer
date of joining:21-AUG-00

*************personal detail*************
name:jana
age:25
sex:m
address:12 raj st
city:chennai
*************office detail************
id:6
department:technical at bangalore
designation:mgr
date of joining:06-AUG-00
PL/SQL procedure successfully completed.
SQL> exec dept_disp(122);
no matchimg detail
PL/SQL procedure successfully completed.