My Profile
Active Members
TodayLast 7 Days
more...
Awards & Gifts
Online Exams
Fresher Jobs
Our fresher job section is exclusively for fresh graduates! Find jobs for freshers in major Indian
cities including Bangalore, Chennai, Hyderabad, Pune or Kochi
Resources
Find educational articles, blogs, discussion threads and other resources.
Colleges
Find details about any college in India or search for courses.
|
Some sql queries for facing Job interviews.
Posted Date: 02 Jul 2008 Resource Type: Articles/Knowledge Sharing Category: Jobs & Interviews
|
Posted By: Gitolekha Member Level: Gold
Rating:  Points: 3
|
|
|
Q1.Table Structure: EMPLOYEE(EmpName, DeptName, Salary)
1. Write a SQL query to get highest salary in each Department.
2. Write a sql to get the number of employees in each Department.
3. Write a sql to split employee in two columns:
a) Employee above 5000(employee getting salary more than 5000)
b) Employee below 5000(employee getting salary below 5000)
No need to show the employee getting salary = 5000
A.
1. SELECT DeptName, MAX(salary) as "Highest salary"
FROM employee
GROUP BY DeptName
2. select DeptName, count(EmpName)
from Employee
group by DeptName
3a. select Salary, EmpName,
case when salary < 5000 then
'Less than 5000'
else
'Greater than 5000'
end
from employee
OR, alternative way,
3a. select case when salary < 5000 then
EmpName+' '+CAST(salary As Varchar(100))
else
NULL
end 'Less than 5000',
case when salary > 5000 then
EmpName+' '+CAST(salary As Varchar(100))
else
NULL
end 'Greater than 5000'
from employee
3b. select EMPNAME = EmpName,
EMPSAL =
case
when salary < 5000
then CAST(Salary as varchar(100))+ ' '+'Less than 5000'
when salary > 5000
then CAST(salary as varchar(100))+' ' +'Greater than 5000'
when salary = 5000
then CAST(salary as varchar(100))+' ' +'Equal to 5000'
end
from employee1
Q2. Write a sql to get the department name having highest number of employees.
A. Select DeptName From Employee1
Group by DeptName
Having count(EmpName) in(
Select Max(A.COUNTEMP)
From (select DeptName, count(EmpName)as COUNTEMP
from Employee1
group by DeptName) A)
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|
Watch TV Channels
Watch Asianet TV onlineKairali TV in InternetSurya TV onlineAmritha TV Channel
|