EMPLOYEE-PAYMENT DATABASE

Aim : To create tables and perform queries in an Employee-Payment

scenario.

Database Schema for a Employee-Paymant scenario

employee(emp_id : integer, emp_name: string)
department(dept_id: integer, dept_name:string)
paydetails(emp_id : integer, dept_id: integer, basic: integer,
deductions: integer, additions: integer, DOJ:
date)
payroll(emp_id : integer, pay_date: date).

a) Create the tables with the appropriate integrity constraints

> create table employee(emp_id int(5) primary key,emp_name varchar(20));

> desc employee;

+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_id | int | NO | PRI | NULL | |
| emp_name | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+

> create table department(dept_id int(5) primary key,dept_name

varchar(20));

> desc department;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id | int | NO | PRI | NULL | |
| dept_name | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+

> create table paydetails(emp_id int(5),

foreign key (emp_id) references employee(emp_id),dept_id int(5),
foreign key(dept_id) references department(dept_id),
basic decimal (7,2),deductions decimal(5,2),additions decimal(5,2),doj
date);
> desc paydetails;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| emp_id | int | YES | MUL | NULL | |
| dept_id | int | YES | MUL | NULL | |
| basic | decimal(7,2) | YES | | NULL | |
| deductions | decimal(5,2) | YES | | NULL | |
| additions | decimal(5,2) | YES | | NULL | |
| doj | date | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+

> create table payroll ( emp_id int(5),foreign key( emp_id) references

employee(emp_id), pay_date date );

> desc payroll;

+----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| emp_id | int | YES | MUL | NULL | |
| pay_date | date | YES | | NULL | |
+----------+------+------+-----+---------+-------+

b) Insert around 10 records in each of the tables.

> insert into employee values(240,' vivek ');

> insert into employee values(241,' manu ');

> insert into employee values(242,' meenu ');

> insert into employee values(243,' arun ');

> insert into employee values(244, ‘antony’);

> insert into employee values(245,'varghese');

> insert into employee values(246,'kurian');

> insert into employee values(247,'nithya');

> insert into employee values(248,'lekshmi');

> insert into employee values(249,'sruthi');

> insert into department values(400,'bca');

> insert into department values(401,'b.com');

> insert into department values(402,'bsc.cs');

> insert into department values(403,'maths');

> insert into department values(404,'dbms');

> insert into department values(405,'c++');

> insert into department values(406,'c');

> insert into department values(407,'chemistry');

> insert into department values(408,'physics');

> insert into department values(409,'english');

> insert into paydetails values(240,400,9000,500,500,'2005-01-01');

> insert into paydetails values(241,401,10000,550,600,'2005-06-01');

> insert into paydetails values(242,402,11000,650,600,'2005-12-01');

> insert into paydetails values(243,403,12000,650,650,'2006-01-01');

> insert into paydetails values(244,404,12500,620,650,'2006-05-01');

> insert into paydetails values(245,405,12500,20,50,'2006-06-07');

> insert into paydetails values(246,406,18500,20,50,'2007-06-07');

> insert into paydetails values(247,407,18500,20,550,'2007-06-07');

> insert into paydetails values(248,408,20500,20,550,'2008-06-07');

> insert into paydetails values(249,409,21500,20,540,'2009-06-07');

> insert into payroll values(240,'2013-01-01');

> insert into payroll values(241,'2013-01-01');

> insert into payroll values(242,'2013-01-01');

> insert into payroll values(243,'2013-01-01');

> insert into payroll values(244,'2013-01-01');

> insert into payroll values(245,'2013-01-01');

> insert into payroll values(246,'2013-01-01');

> insert into payroll values(247,'2013-01-01');

> insert into payroll values(248,'2013-01-01');

> insert into payroll values(249,'2013-01-01');

> select * from employee;

+--------+----------+
| emp_id | emp_name |
+--------+----------+
| 240 | vivek |
| 241 | manu |
| 242 | meenu |
| 243 | arun |
| 244 | antony |
| 245 | varghese |
| 246 | kurian |
| 247 | nithya |
| 248 | lekshmi |
| 249 | sruthi |
+--------+----------+

> select * from department;

+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 400 | bca |
| 401 | b.com |
| 402 | bsc.cs |
| 403 | maths |
| 404 | dbms |
| 405 | c++ |
| 406 | c |
| 407 | chemistry |
| 408 | physics |
| 409 | english |
+---------+-----------+

> select * from paydetails;

+--------+---------+----------+------------+-----------+------------+
| emp_id | dept_id | basic | deductions | additions | doj |
+--------+---------+----------+------------+-----------+------------+
| 240 | 400 | 9000.00 | 500.00 | 500.00 | 2005-01-01 |
| 241 | 401 | 10000.00 | 550.00 | 600.00 | 2005-06-01 |
| 242 | 402 | 11000.00 | 650.00 | 600.00 | 2005-12-01 |
| 243 | 403 | 12000.00 | 650.00 | 650.00 | 2006-01-01 |
| 244 | 404 | 12500.00 | 620.00 | 650.00 | 2006-05-01 |
| 245 | 405 | 12500.00 | 20.00 | 50.00 | 2006-06-07 |
| 246 | 406 | 18500.00 | 20.00 | 50.00 | 2007-06-07 |
| 247 | 407 | 18500.00 | 20.00 | 550.00 | 2007-06-07 |
| 248 | 408 | 20500.00 | 20.00 | 550.00 | 2008-06-07 |
| 249 | 409 | 21500.00 | 20.00 | 540.00 | 2009-06-07 |
+--------+---------+----------+------------+-----------+------------+

> select * from payroll;

+--------+------------+
| emp_id | pay_date |
+--------+------------+
| 240 | 2013-01-01 |
| 241 | 2013-01-01 |
| 242 | 2013-01-01 |
| 243 | 2013-01-01 |
| 244 | 2013-01-01 |
| 245 | 2013-01-01 |
| 246 | 2013-01-01 |
| 247 | 2013-01-01 |
| 248 | 2013-01-01 |
| 249 | 2013-01-01 |
+--------+------------+

c) List the employee details department wise.

> select emp_id,dept_id from paydetails order by dept_id asc, emp_id asc;

+--------+---------+
| emp_id | dept_id |
+--------+---------+
| 240 | 400 |
| 241 | 401 |
| 242 | 402 |
| 243 | 403 |
| 244 | 404 |
| 245 | 405 |
| 246 | 406 |
| 247 | 407 |
| 248 | 408 |
| 249 | 409 |
+--------+---------+

d) List all the employee names who joined after particular date.

> select e.emp_name from employee e,paydetails p where e.emp_id=p.emp_id

and p.doj>='2006-03-06';

+----------+
| emp_name |
+----------+
| antony |
| varghese |
| kurian |
| nithya |
| lekshmi |
| sruthi |
+----------+

e) List the details of employees whose basic salary is between 10,000

and 20,000.
> select e.emp_id, e.emp_name, p.basic from employee e, paydetails p
where e.emp_id=p.emp_id and basic between 10000 and 20000;

+--------+----------+----------+
| emp_id | emp_name | basic |
+--------+----------+----------+
| 241 | manu | 10000.00 |
| 242 | meenu | 11000.00 |
| 243 | arun | 12000.00 |
| 244 | antony | 12500.00 |
| 245 | varghese | 12500.00 |
| 246 | kurian | 18500.00 |
| 247 | nithya | 18500.00 |
+--------+----------+----------+

f) Give a count of how many employees are working in each department.

> select count(emp_id),dept_id from paydetails group by dept_id;

+---------------+---------+
| count(emp_id) | dept_id |
+---------------+---------+
| 1 | 400 |
| 1 | 401 |
| 1 | 402 |
| 1 | 403 |
| 1 | 404 |
| 1 | 405 |
| 1 | 406 |
| 1 | 407 |
| 1 | 408 |
| 1 | 409 |
+---------------+---------+

g) Give a names of the employees whose netsalary>10,000

> select emp_name from employee where emp_id in (select emp_id from
paydetails where basic-deductions+additions>10000);

+----------+
| emp_name |
+----------+
| manu |
| meenu |
| arun |
| antony |
| varghese |
| kurian |
| nithya |
| lekshmi |
| sruthi |
+----------+

h) List the details for an employee_id=245.

> select * from employee where emp_id=245;

+--------+----------+
| emp_id | emp_name |
+--------+----------+
| 245 | varghese |
+--------+----------+