Payroll Database Management System Table Creation inserts data employee details department details

Sql through create command we can create the following table Employee details department details Salary Details and there employment office details,
After create table by through desc command we can list out table variable, form the payroll database Management System. Then we will insert data to the specific table like office detail, employment detail, and salary details.
Source code in PL SQL programming Algorithm
SQL> create table emp_detail(emp_no number(5) primary key,emp_name varchar2(30) not null,age number(2) not null,sex char(1) not null constraint emp2 check(sexin('f','m')), address varchar2(30),city varchar2(30));
Table created.

SQL> create table dept_detail(dept_no number(5) primary key,dept_name varchar2(20),location varchar2(20));
Table created.

SQL> create table emp_off(emp_no number(5) not null,emp_name varchar2(20),dept_no number(5),doj date,designation varchar2(20));
Table created.

SQL> create table salary(emp_no number(5),basic float not null,ta float,da float,hra float,tax float,ppf float,net float);
Table created.

DESCRIPTION Output DBMS Lab CS1307 Database Management system
SQL> desc emp_detail;

Name Null? Type
------------------------------- -------- ----
EMP_NO NOT NULL NUMBER(5)
EMP_NAME NOT NULL VARCHAR2(30)
AGE NOT NULL NUMBER(2)
SEX NOT NULL CHAR(1)
ADDRESS VARCHAR2(30)
CITY VARCHAR2(30)

SQL> desc dept_detail;
Name Null? Type
------------------------------- -------- ----
DEPT_NO NOT NULL NUMBER(5)
DEPT_NAME VARCHAR2(20)
LOCATION VARCHAR2(20)

SQL> desc emp_off;

Name Null? Type
------------------------------- -------- ----
EMP_NO NOT NULL NUMBER(5)
EMP_NAME VARCHAR2(20)
DEPT_NO NUMBER(5)
DOJ DATE
DESIGNATION VARCHAR2(20)

SQL> desc salary;
Name Null? Type
------------------------------- -------- ----
EMP_NO NUMBER(5)
BASIC NOT NULL FLOAT(126)
TA FLOAT(126)
DA FLOAT(126)
HRA FLOAT(126)
TAX FLOAT(126)
PPF FLOAT(126)
NET FLOAT(126)

INSERTION Output DBMS Lab CS1307 Database Management system
SQL> declare
2 emp_no number(5);
3 emp_name varchar2(20);
4 dept_no number(5);
5 basic float;
6 ta float;
7 da float;
8 hra float;
9 tax float;
10 ppf float;
11 net float;
12 begin
13 emp_no:=&emp_no;
14 emp_name:='&emp_name';
15 dept_no:=&dept_no;
16 basic:=&basic;
17 ta:=0.10*basic;
18 da:=0.07*basic;
19 hra:=0.15*basic;
20 tax:=0.3*basic;
21 ppf:=0.10*basic;
22 net:=basic+da+ta+hra-tax-ppf;
23 insert into emp_detail values(emp_no,emp_name,&age,'&sex','&address','&city');
24 insert into dept_detail values(dept_no,'&dept_name','&location');
25 insert into emp_off values(emp_no,emp_name,dept_no,'&doj','&designation');
26 insert into salary values(emp_no,basic,ta,da,hra,tax,ppf,net);
27 end;
28 /
Payroll Database Management algorithm
Enter value for emp_no: 1
old 12: emp_no:=&emp_no;
new 12: emp_no:=1;
Enter value for emp_name: mythreye
old 13: emp_name:='&emp_name';
new 13: emp_name:='mythreye';
Enter value for basicsalary: 10000
old 14: basicsalary:=&basicsalary;
new 14: basicsalary:=10000;
Enter value for age: 21
Enter value for sex: f
Enter value for address: 19 vadivalpuram
Enter value for city: chennai
old 21: insert into emp_detail values(emp_no,emp_name,&age,'&sex','&address','&city');
new 21: insert into emp_detail values(emp_no,emp_name,21,'f','19 vadivalpuram','chennai');
Enter value for dept_no: 123
Enter value for doj: 21-aug-2000
Enter value for designation: programer
old 22: insert into emp_off values(emp_no,emp_name,&dept_no,'&doj','&designation');
new 22: insert into emp_off values(emp_no,emp_name,123,'21-aug-2000','programer');
PL/SQL procedure successfully completed.
Payroll Database Management algorithm

SQL> /
Enter value for emp_no: 24
old 12: emp_no:=&emp_no;
new 12: emp_no:=24;
Enter value for emp_name: subramani
old 13: emp_name:='&emp_name';
new 13: emp_name:='subramani';
Enter value for basicsalary: 15000
old 14: basicsalary:=&basicsalary;
new 14: basicsalary:=15000;
Enter value for age: 43
Enter value for sex: m
Enter value for address: 19 ram st
Enter value for city: salem
old 21: insert into emp_detail values(emp_no,emp_name,&age,'&sex','&address','&city');
new 21: insert into emp_detail values(emp_no,emp_name,43,'m','19 ram st','salem');
Enter value for dept_no: 124
Enter value for doj: 02-nov-95
Enter value for designation: manager
old 22: insert into emp_off values(emp_no,emp_name,&dept_no,'&doj','&designation');
new 22: insert into emp_off values(emp_no,emp_name,124,'02-nov-95','manager');
PL/SQL procedure successfully completed.

SQL> /
Enter value for emp_no: 76
old 12: emp_no:=&emp_no;
new 12: emp_no:=76;
Enter value for emp_name: jaya
old 13: emp_name:='&emp_name';
new 13: emp_name:='jaya';
Enter value for basicsalary: 12000
old 14: basicsalary:=&basicsalary;
new 14: basicsalary:=12000;
Enter value for age: 27
Enter value for sex: f
Enter value for address: 12 lok gardens
Payroll Database Management algorithm

Enter value for city: madurai
old 21: insert into emp_detail values(emp_no,emp_name,&age,'&sex','&address','&city');
new 21: insert into emp_detail values(emp_no,emp_name,27,'f','12 lok gardens','madurai');
Enter value for dept_no: 126
Enter value for doj: 5-dec-99
Enter value for designation: sales officer
old 22: insert into emp_off values(emp_no,emp_name,&dept_no,'&doj','&designation');
new 22: insert into emp_off values(emp_no,emp_name,126,'5-dec-99','sales officer');

PL/SQL procedure successfully completed.
Payroll Database Management algorithm

SQL> /
Enter value for emp_no: 87
old 12: emp_no:=&emp_no;
new 12: emp_no:=87;
Enter value for emp_name: ravi
old 13: emp_name:='&emp_name';
new 13: emp_name:='ravi';
Enter value for basicsalary: 20000
old 14: basicsalary:=&basicsalary;
new 14: basicsalary:=20000;
Enter value for age: 56
Enter value for sex: m
Enter value for address: 12 harbour road
Enter value for city: tuticorin
old 21: insert into emp_detail values(emp_no,emp_name,&age,'&sex','&address','&city');
new 21: insert into emp_detail values(emp_no,emp_name,56,'m','12 harbour road','tuticorin');
Enter value for dept_no: 125
Enter value for doj: 06-jan-1993
Enter value for designation: assist gm
old 22: insert into emp_off values(emp_no,emp_name,&dept_no,'&doj','&designation');
new 22: insert into emp_off values(emp_no,emp_name,125,'06-jan-1993','assist gm');

PL/SQL procedure successfully completed.
SQL> /
Enter value for emp_no: 40
old 12: emp_no:=&emp_no;
new 12: emp_no:=40;
Enter value for emp_name: john
old 13: emp_name:='&emp_name';
new 13: emp_name:='john';
Enter value for basicsalary: 11000
old 14: basicsalary:=&basicsalary;
new 14: basicsalary:=11000;
Enter value for age: 30
Enter value for sex: f
Enter value for address: 21 sampath st
Enter value for city: coimbatore
old 21: insert into emp_detail values(emp_no,emp_name,&age,'&sex','&address','&city');
new 21: insert into emp_detail values(emp_no,emp_name,30,'f','21 sampath st','coimbatore');
Enter value for dept_no: 128
Enter value for doj: 07-feb-1999
Enter value for designation: qcmanager
old 22: insert into emp_off values(emp_no,emp_name,&dept_no,'&doj','&designation');
new 22: insert into emp_off values(emp_no,emp_name,128,'07-feb-1999','qcmanager');
PL/SQL procedure successfully completed.

SQL> /
Enter value for emp_no: 6
old 12: emp_no:=&emp_no;
new 12: emp_no:=6;
Enter value for emp_name: jana
old 13: emp_name:='&emp_name';
new 13: emp_name:='jana';
Enter value for basicsalary: 12000
old 14: basicsalary:=&basicsalary;
new 14: basicsalary:=12000;
Enter value for age: 25
Enter value for sex: m
Enter value for address: 12 raj st
Enter value for city: chennai
old 21: insert into emp_detail values(emp_no,emp_name,&age,'&sex','&address','&city');
new 21: insert into emp_detail values(emp_no,emp_name,25,'m','12 raj st','chennai');
Enter value for dept_no: 123
Enter value for doj: 6-aug-2000
Enter value for designation: manager
old 22: insert into emp_off values(emp_no,emp_name,&dept_no,'&doj','&designation');
new 22: insert into emp_off values(emp_no,emp_name,123,'6-aug-2000','manager');
PL/SQL procedure successfully completed.
Output DBMS Lab CS1307 Database Management system

SQL> begin
2 insert into dept_detail values(&dept_no,'&dept_name','&location');
3 end;
4 /
Enter value for dept_no: 123
Enter value for dept_name: technical
Enter value for location: bangalore
old 2: insert into dept_detail values(&dept_no,'&dept_name','&location');
new 2: insert into dept_detail values(123,'technical','bangalore');
PL/SQL procedure successfully completed.

SQL> /
Enter value for dept_no: 124
Enter value for dept_name: accounts
Enter value for location: salem
old 2: insert into dept_detail values(&dept_no,'&dept_name','&location');
new 2: insert into dept_detail values(124,'accounts','salem');
Payroll Database Management algorithm
PL/SQL procedure successfully completed.

SQL> /
Enter value for dept_no: 126
Enter value for dept_name: marketing
Enter value for location: madurai
old 2: insert into dept_detail values(&dept_no,'&dept_name','&location');
new 2: insert into dept_detail values(126,'marketing','madurai');
PL/SQL procedure successfully completed.

SQL> /
Enter value for dept_no: 125
Enter value for dept_name: export and infort
Enter value for location: tuticorin
old 2: insert into dept_detail values(&dept_no,'&dept_name','&location');
new 2: insert into dept_detail values(125,'export and infort','tuticorin');
PL/SQL procedure successfully completed.
Payroll Database Management algorithm

SQL> /
Enter value for dept_no: 128
Enter value for dept_name: research
Enter value for location: coimbatore
old 2: insert into dept_detail values(&dept_no,'&dept_name','&location');
new 2: insert into dept_detail values(128,'research','coimbatore');
PL/SQL procedure successfully completed.

Related post