 |
| 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.
3. List total net salary in the CSE department
4. List employee whose net salary less than Rs.10000/-
5. List employees who draw the least net salary in the institution
/* 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));
/* 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.
/* 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 you have any doubts, please leave them down in the comments section.
No comments:
Post a Comment