Office Management Problem | MySQL Queries | Solution | DBMS


Office management problem in MySQL, code by Nived Kannada



 In this post, we are taking a look at a few interesting database-related questions and their solution queries that computer science students might find helpful. 

We will see a simplified real-life problem. That is office database management.

An office database will contain Employee details, Department details, Salary details, and all types of different tables. In this article, we will be making a College database where there are multiple departments and multiple employees.

So, we will be creating 3 different tables namely Emp, Dep, and Salr which refers to Employees, Department, and Salary details respectively. 

The table Emp will have attributes:  empid,name,dob,depid,designation,Place

The table Dep will have the attributes:  depid , name 

The table Salr will have attributes:     empid, depid, bP, da, hra, net

where, da is Dearness Allowance which is calculated as the 60% of base Payment.

bP refers to the base Payment. hra is house rent allowance. 

Given that hra =  1000 /-

net stands for Net salary that the employees get. It is calculated as,

net = bP + da + hra

So, we will be writing all the queries to create these tables, insert some sample data values, and then we will see how to retrieve certain data according to certain situations.




Here are some of the questions 


B    But before we start writing these queries, we have to first create the tables.
    Let's just do that.


/* Creating Table Emp */
create table Emp(empid int auto_increment, name varchar(20) not null,
dob date not null, depid int not null,
designation varchar(25), 
Place varchar(25),primary key(empid),
unique(empid), foreign key(depid)
references Dep(depid));

This code creates the table Emp. In this table, the primary key will be empid.  

/* Creating Table Dep */
create table Dep(depid int not null,
name varchar(20), primary key(depid), unique(depid));

This code creates the table Dep. In this table, the primary key will be depid.

/* Creating Table Salr */
create table Salr(empid int not null, depid int not null,
bP float , 
da float generated always as (0.6 * bP), 
hra float default 1000, 
net float generated always as (bP + da + hra),
foreign key(empid) references Emp(empid),
foreign key(depid) references Dep(depid));


This code creates the table Salr. In this table, empid and depid are foreign keys that reference empid of table Emp and depid of table Dep respectively.

/* Inserting values to Table Emp */
insert into Emp values(null, 'Amarnath', '1996-12-23', 1, 'Professor', 'Payyannur');
insert into Emp values(null, 'Sindhu', '1992-11-18', 1, 'Professor', 'Kasaragod');
insert into Emp values(null, 'Robinson', '1997-07-23', 1, 'Professor', 'Taliparamba');
insert into Emp values(null, 'Mathew', '1995-01-12', 2, 'Professor', 'Kanhangad');
insert into Emp values(null, 'Lakshmi', '1994-05-10', 2, 'Professor', 'Thrikaripur');
insert into Emp values(null, 'Raj Kumar', '1996-02-29', 2, 'Professor', 'Payyannur');
insert into Emp values(null, 'Anjali', '1993-04-01', 3, 'Professor', 'Kozhikode');
insert into Emp values(null, 'Rishikesh', '1994-12-03', 3, 'Professor', 'Ernakulam');
insert into Emp values(null, 'Ahmed', '1996-12-16', 3, 'Professor', 'Alapuzha');

These queries will insert values to the table Emp. These are the Employee details. (Sample given here. You can give whichever data you like). 

/* Inserting Values to Table Dep */
insert into Dep values(1,'CSE');
insert into Dep values(2,'Civil');
insert into Dep values(3,'EC');

These queries will insert 3 department IDs and department names into the table Dep. You can add however many departments as you like.

/* Inserting values to Table Salr */
insert into Salr (empid,depid,bP) values(1000,1,8500);
insert into Salr (empid,depid,bP) values(1001,1,6500);
insert into Salr (empid,depid,bP) values(1002,1,5000);
insert into Salr (empid,depid,bP) values(1003,2,6700);
insert into Salr (empid,depid,bP) values(1004,2,7000);
insert into Salr (empid,depid,bP) values(1005,2,5500);
insert into Salr (empid,depid,bP) values(1006,3,6900);
insert into Salr (empid,depid,bP) values(1007,3,9500);
insert into Salr (empid,depid,bP) values(1008,3,6700);

These queries will insert data into the table Salr. Here we are using a different syntax than usual to insert values since the attributes da, and net are calculated/generated values. So we don't have to insert those values manually. 


Now that we created the 3 tables, we can now find queries to solve the problems according to the 6 questions given above.

1. List all the departments

/* 1. Listing All the Departments */
select * from Dep;

2. List average net salary in each department in the increasing order 

/* 2. Listing Average Net Salary in each department in the increasing order. */
select name,avg(net) from (Dep, Salr) 
where Dep.depid=Salr.depid group by(name) order by(avg(net));

3. List total net salary in the  CSE department

/* 3. Listing Total Net Salary in the CSE Department */
select sum(net) from Salr where depid=1;

4. List employee whose net salary less than Rs.10000/-

/* 4. Listing Employees whose net salary is less than Rs.10000 */
select name, net from (Emp,Salr) where Emp.empid=Salr.empid and net<10000;

5. List employees who draw the least net salary in the institution 

/* 5. Listing employee who draws least net salary in the institution */
select name, net from (Emp,Salr) 
where Emp.empid=Salr.empid and net=(select min(net) from Salr);

6. List employees who draw the highest net salary in each department


/* 6. Listing Employees who Draws highest net salary in each department. */
select Dep.name as Dept, Emp.name as Employee from (Emp,Dep) 
 where empid in (select empid from (select max(net) as salary 
from Salr group by(depid)) as maxsalary join Salr 
where Salr.net=maxsalary.salary) and Dep.depid=Emp.depid;

And It's that simple!

If this code helped you, please share it with your friends and support us. Thank you!
If you have any doubts, please leave them down in the comments section.

No comments:

Post a Comment

Pages

Nanogalaxy is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for website owners to earn advertising fees by advertising and linking to amazon.com and any other website that may be affiliated with Amazon Service LLC Associates Program.