Payroll Management| Skillpundit | India's No.1 | Learning

Tables Overview

S.No	Table Name	No. of Fields	Demo Data
S.No	Table Name	No. of Fields	Create	Insert
1	attendance	6	Create	Insert
2	benefits	5	Create	Insert
3	cabs	6	Create	Insert
4	cab_members	5	Create	Insert
5	cab_routes	6	Create	Insert
6	employees	12	Create	Insert
7	employee_shifts	5	Create	Insert
8	employee_types	4	Create	Insert
9	salaries	5	Create	Insert
10	shifts	4	Create	Insert

Table Structure: attendance

Filed Name	Data Type
Filed Name	Id	int
employee_id	varchar
attendance	varchar
date	date
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "attendance"

CREATE TABLE attendance (
id int,
employee_id varchar(100),
attendance varchar(100),
date date,
created_at datetime ,
updated_at datetime );

Following SQL inserts 6 records in "attendance"

INSERT INTO attendance (id, employee_id, attendance, date, created_at, updated_at) VALUES
(1, 'e1', '1', '2020-12-01', '2020-12-14 15:09:36', '2020-12-14 15:09:36'),
(2, 'e2', '1', '2020-12-02', '2020-12-14 15:09:36', '2020-12-14 15:09:36'),
(3, 'e3', '1', '2020-12-03', '2020-12-14 15:09:36', '2020-12-14 15:09:36');

Table Structure: benefits

Field name	Data Type
Field name	Id	int
employee_id	varchar
benefits	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "benefits"

CREATE TABLE benefits (
id int,
employee_id varchar(100),
benefits varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 5 records in "benefits"

INSERT INTO benefits (id, employee_id, benefits, created_at, updated_at) VALUES
(1, 'e1', 'cab', '2020-12-14 15:10:31', '2020-12-14 15:10:31'),
(2, 'e2', 'cab', '2020-12-14 15:10:31', '2020-12-14 15:10:31'),
(3, 'e3', 'cab', '2020-12-14 15:10:31', '2020-12-14 15:10:31');

Table Structure: cabs

Field Name	Data Type
Field Name	Id	int
cab_number	varchar
registration_number	varchar
driver	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "cabs"

CREATE TABLE cabs (
id int,
cab_number varchar(100),
registration_number varchar(100),
driver varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 6 records in "cabs"

INSERT INTO cabs (id, cab_number, registration_number, driver, created_at, updated_at) VALUES
(1, '001', 'ts16ad5487', 'praveen', '2020-12-14 15:12:07', '2020-12-14 15:12:07'),
(2, '002', 'ts45gh8547', 'ganesh', '2020-12-14 15:12:07', '2020-12-14 15:12:07'),
(3, '003', 'ts96ty2547', 'rakesh', '2020-12-14 15:12:07', '2020-12-14 15:12:14');

Table Structure: cab_members

Field name	Data type
Field name	Id	int
cab_number	varchar
cab_members	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "cab_members"

CREATE TABLE cab_members (
id int,
cab_number varchar(100),
cab_members varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 5 records in "cab_members"

INSERT INTO cab_members (id, cab_number, cab_members, created_at, updated_at) VALUES
(1, '001', 'e1', '2020-12-14 15:12:59', '2020-12-14 15:12:59'),
(2, '002', 'e2', '2020-12-14 15:12:59', '2020-12-14 15:12:59'),
(3, '003', 'e3', '2020-12-14 15:12:59', '2020-12-14 15:12:59');

Table Structure: cab_routes

Field name	Data type
Field name	Id	int
cab_number	varchar
from	varchar
to	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "cab_routes"

CREATE TABLE cab_routes (
id int,
cab_number varchar(100),
from_station varchar(100),
to_station varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 6 records in "cab_routes"

INSERT INTO cab_routes (id, cab_number, from_station, to_station, created_at, updated_at) VALUES
(1, '001', 'office', 'secunderabad', '2020-12-14 15:16:40', '2020-12-14 15:16:40'),
(2, '002', 'office', 'begumpet', '2020-12-14 15:16:40', '2020-12-14 15:16:40'),
(3, '003', 'office', 'gachibowli', '2020-12-14 15:16:40', '2020-12-14 15:16:40');

Table Structure: employees

Field name	Data type
Field name	Id	int
employee_id	varchar
name	varchar
address	varchar
mobile	varchar
alternate_mobile	varchar
age	varchar
gender	varchar
designation	varchar
benefits	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "employees"

CREATE TABLE employees (
id int,
employee_id varchar(100),
name varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
age varchar(100),
gender varchar(100),
designation varchar(100),
benefits varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 12 records in "employees"

INSERT INTO employees (id, employee_id, name, address, mobile, alternate_mobile, age, gender, designation, benefits, created_at, updated_at) VALUES
(1, 'e1', 'pavani', 'secunderabad', '7536984521', '3571596824', '23', 'female', '', 'cab', '2020-12-14 15:19:24', '2020-12-14 15:19:24'),
(2, 'e2', 'nagamani', 'ameerpet', '98745653210', '3698521478', '30', 'female', '', 'cab', '2020-12-14 15:19:24', '2020-12-14 15:19:24'),
(3, 'e3', 'sindhu', 'gachibowli', '69854712362', '3651248754', '25', 'female', '', 'cab', '2020-12-14 15:19:24', '2020-12-14 15:19:24');

Table Structure: employee_shifts

Field name	Data type
Field name	Id	int
shift_id	varchar
employee_id	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "employee_shifts"

CREATE TABLE employee_shifts (
id int,
shift_id varchar(100),
employee_id varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 5 records in "employee_shifts"

INSERT INTO employee_shifts (id, shift_id, employee_id, created_at, updated_at) VALUES
(1, '1', 'e1', '2020-12-14 15:20:14', '2020-12-14 15:20:14'),
(2, '2', 'e2', '2020-12-14 15:20:14', '2020-12-14 15:20:14'),
(3, '1', 'e3', '2020-12-14 15:20:14', '2020-12-14 15:20:14');

Table Structure: employee_types

Field name	Data type
Field name	Id	int
employee_type	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "employee_types"

CREATE TABLE employee_types (
id int,
employee_type varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 4 records in "employee_types"

INSERT INTO employee_types (id, employee_type, created_at, updated_at) VALUES
(1, 'employees', '2020-12-14 15:21:10', '2020-12-14 15:21:10'),
(2, 'manager', '2020-12-14 15:21:10', '2020-12-14 15:21:10'),
(3, 'security guard', '2020-12-14 15:21:10', '2020-12-14 15:21:10');

Table Structure: salaries

Field name	Data type
Field name	Id	int
employee_id	Varchar
salary	Varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "salaries"

CREATE TABLE salaries (
id int,
employee_id varchar(100),
salary varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 5 records in "salaries "

INSERT INTO salaries (id, employee_id, salary, created_at, updated_at) VALUES
(1, 'e1', '15000', '2020-12-14 15:21:51', '2020-12-14 15:21:51'),
(2, 'e2', '10000', '2020-12-14 15:21:51', '2020-12-14 15:21:51'),
(3, 'e3', '18000', '2020-12-14 15:21:51', '2020-12-14 15:21:51');

Table Structure: shifts

Field name	Data type
Field name	Id	int
shift	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "shifts"

CREATE TABLE shifts (
id int,
shift varchar(100),
created_at datetime ,
updated_at datetime );

Following SQL inserts 4records in "shifts"

INSERT INTO shifts (id, shift, created_at, updated_at) VALUES
(1, 'A-shift', '2020-12-14 15:22:16', '2020-12-14 15:22:16'),
(2, 'B-shift', '2020-12-14 15:22:16', '2020-12-14 15:22:16');

Questions

1. Display the list of employees with their details?

A. SELECT * FROM `employees`

2. Display the shift names?

A. SELECTs.id,s.shift FROM `shifts` s

3. Display the age,gender of the employees?

A. SELECT* FROM `employee_types`

4. Display the employee types?

A. SELECT e.name,e.age,e.gender from employees e

5. Dispaly the designation of the employees?

A. SELECT e.id,e.name,e.designation from employees e

6. Display the benefits of employees?

A. SELECTe.id,e.name,e.age,e.gender,e.benefits from employees e

7. Display the address,mobile,alternate mobile of employees?

A. SELECT e.id,e.name,e.mobile,e.alternate_mobile from employees e

8. Display the driver name of the particular cab?

A. SELECTc.id,c.cab_number,c.driver from cabs c where c.cab_number="001"

9. Display the registration numbers of the cabs?

A. SELECT c.id,c.cab_number,c.registration_number from cabs c

10. Display the date time of the employee record?

A. SELECT * FROM employees where DATE(created_at) = DATE('2020-12-14')

11. Display the cab routes based on cab number?

A. SELECT c.id,c.cab_number,c.registration_number,c.driver,cr.from_station,cr.to_station from cabs c left join cab_routes cr on c.cab_number=cr.cab_number

12. Display the employees based on shift wise?

A. SELECT es.id,s.shift,e.name from employee_shifts es LEFT join shifts s on es.shift_id=s.id LEFT join employees e on es.employee_id=e.employee_id

13. Dispaly the attendance based on employee id?

A. SELECTa.id,e.name from attendance a LEFT join employees e on a.employee_id=e.employee_id where a.attendance='1' (Here 1 is employee is present )

14. Display the salaries of the employees?

A. SELECT s.id,e.name,s.salary from salaries s LEFT join employees e on s.employee_id=e.employee_id

15. Display the cab members based on cab number?

A. SELECT cm.id,cm.cab_number,e.name from cab_members cm LEFT join employees e on cm.cab_members=e.employee_id

16. Display the attendance of the employees on a specific date?

A. SELECT a.id,e.name,a.attendance,a.date from attendance a LEFT join employees e on a.employee_id=e.employee_id where date="2020-12-01"

Web Tutorials

</> SQL Tutorial

Payroll Management Project

Database Name: payroll_management

Practical Questions