Employees And Their Department Who Is Top Salary
Dec 4, 2006
i have 2 tables emp and dept
emp has columns:
empid(pk),empname,deptid(fk),salary
dept has columns:
deptid(pk),deptname
now my aim is:
List of the employees and their department who is top salary earner of the department.
wht i can think of is:
select distinct empname,deptname,max(salary) as 'max salary'
from emp e,dept d
where e.deptid=d.deptid
group by empname,deptname
but it gives unexpected result...
help appreciated
cheers
View 13 Replies
ADVERTISEMENT
Mar 23, 2006
create table employee(empid int,empname varchar(20),managerid int notnull, sal int)insert into employee values(1,'ranga',22,5000)insert into employee values(2,'satish',22,8000)insert into employee values(3,'sunil',11,4500)insert into employee values(4,'sridhar',22,2000)insert into employee values(5,'ramesh',33,12000)insert into employee values(6,'srini',22,16000)insert into employee values(7,'sashi',33,54000)insert into employee values(8,'rajani',22,71000)insert into employee values(9,'praveen',11,6060)insert into employee values(10,'bhaskar',22,11120)insert into employee values(11,'baba',33,9000)create table employment (managerid int,managername varchar(20),sal int)insert into employment values(11,'rob',2500)insert into employment values(22,'babu',5000)insert into employment values(33,'ram',6000)now my problem isUpdate the salary of each manager to be double the averagesalary of the employees he/she managespls helpsati
View 3 Replies
View Related
Aug 14, 2014
Let us assume that there are 100 employee in a company. And sum of salary of all employee is 10000. Find list of highest paid employees whose sum of salary is 8000. Remaining employee will fall in 20% bracket.
View 4 Replies
View Related
Jan 17, 2015
I would like to find the average salary for each department which has min salary
In my case I will have 3 departments which have min salary.
select distinct d.department_name, E.SALARY, avg(E.salary)
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
WHERE E.SALARY = (SELECT MIN(E.SALARY)
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID))
GROUP BY D.DEPARTMENT_NAME, E.SALARY;
View 1 Replies
View Related
Sep 14, 2014
I have the employees and department tables(structure below).
Write a sql to get the department name,employee earning maximum salary,employee earning minimum salary for each department
result set:
dname |max_salaried_employee|min_salaried_employee
------------------------------------------------------
Accounts | Blakes | Miller
HR | King | James
Structure :
create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
[Code] .....
View 1 Replies
View Related
Dec 8, 2011
I need to calculate the salary given to all employees in a year
Code:
select sum(emp_total_sal)from emp_salary
How to modify this code to get what i need ?
View 3 Replies
View Related
Aug 16, 2007
I have to Make Department Table.
But this there names can be in multi language.
--------------------------------------------DepartmentGroup--------------------------------------------DepartmentGroupId (P.K.)UniqueNameCreatedDateModifiedDate--------------------------------------------
--------------------------------------------Department--------------------------------------------DepartmentId (P.K.)NativeNameNavigateUrlIsFavoriteLanguageId (F.K.)DepartmentGroupId (F.K.)IsVisibleCreatedDateModifiedDate--------------------------------------------
From this design we can move to same department in different language.
is it right?
View 2 Replies
View Related
Nov 27, 2006
How to display the paper number that is co authored by authors from the same department?
The database structure:
Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(PaNum, AcNum)
Field(FieldNum, ID, Title)
Interest(FieldNum, AcNum, Descrip)
Thanks for help a lot
View 3 Replies
View Related
Sep 28, 2015
I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:
- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.
Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department.
It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.
View 3 Replies
View Related
May 29, 2015
I need to prepare a roster rotating persons within each Department for the available dates
Please refer to the below sample tables and data
CREATE TABLE Roster
(
StartDate Date,
EndDate Date
)
CREATE TABLE Department
[Code] ....
Expected Output
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'A' Department,'P1' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'B' Department,'P3' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'C' Department,'P6' Person
[Code] .....
View 9 Replies
View Related
May 8, 2012
Select * from personnel
where salary > ALL (SELECT AVG (salary) from personnel group by bolno)
And how i find max average salary?
View 8 Replies
View Related
Mar 20, 2004
hi
i have a table employee:
dept ename salary
---- --------- -------
10 A 2500
20 B 3500
30 C 4000
20 D 5500
10 E 4500
30 F 5200
FIRST QUERY:
select dept,sum(salary) from employee group by dept
the above one is working fine..
after working the first query output,
i want to select the dept,max(sum(salary)) from the table...
how?? could any one send me immediately...
thanks in advance
View 7 Replies
View Related
Oct 20, 2005
--Table Empmaster:-
create table Empmaster
(
empid int identity(1,1) constraint pkempid primary key clustered,
empname varchar(10),
empsalary numeric
)
insert Empmaster(empname,empsalary)values('Imran',5000)
insert Empmaster(empname,empsalary)values('Raja',5000)
--Table Salary:-
create table Salary
(
salid int identity(1,1) constraint pksalid primary key clustered,
empid int constraint fkempid foreign key references Empmaster(empid),
dos varchar(10),
salary numeric
)
insert Salary(empid,dos,salary)values('1','2005-08-01','5000')
insert Salary(empid,dos,salary)values('2','2005-08-01','5000')
insert Salary(empid,dos,salary)values('1','2005-09-01','5000')
insert Salary(empid,dos,salary)values('2','2005-09-01','7000')
insert Salary(empid,dos,salary)values('1','2005-10-01','7000')
insert Salary(empid,dos,salary)values('2','2005-10-01','7000')
i have two tables with relations. how do i find out whose salary has been hiked for the 9th month?
View 15 Replies
View Related
Mar 19, 2007
Hi
How to get the maximum salry from a table without using top and
aggreate function
thanks
asm
View 4 Replies
View Related
Sep 26, 2007
i want to write a SQL statement to increment the salary by 10% for technicians who have done three tests on a particular date.
there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.
Employee (EmployeeID,Name,Salary,Tpye)
TestEmployee(TestNo,EmployeeID,Hrs)
Test(TestNo,TestDate,Result)
View 20 Replies
View Related
Apr 18, 2006
employee table having columns employeeid, salary
i want to write a query to get 1st, 2nd and 3rd max salary ?
View 2 Replies
View Related
Aug 29, 2006
I am going into salary negotiationyearly review next month after my week off and I am trying to determine what to ask for. I am thinking another 10K and an extra week off would not be unreasonable, but I wanted to get some idea of what you guys think I should ask for in terms of pay.I have been developing software for seven years and I have been a dba for a little over five. I live in pricey northern Virginia. On a fairly regular basis I do about 55 to 60 hour weeks. On my DBA team, I am the only one who can handle both development and production tasks. The others are strictly developers. Although I have been relieved of most of my customer support tasks by our newbie, the customer support manager still brings the nastier bits to me. It is my perception that the more complex tasks get assigned to me. I get told on a regular basis that I am the best dba this place has ever had and other embarrassing accolades are regularly thrown my way. After a year, other than my boss I am the dba that has been here the longest in high turnover high burnout company. This year as we try to move to a SAP model, it looks like we will be going to 24/7 support on a disaster recovery model I am designing and implementing, so I guess I am getting the pager.So how much money should I be asking for?you can PM me with a number if you want.
View 1 Replies
View Related
Jul 24, 2013
I would like to know how to use a criterion on this example. I want to know only when the total salary is at a certain amount
SELECT SUM (salary) as Total Salary
FROM employees
WHERE Total Salary > 25000; ---this is where i am having issue
View 4 Replies
View Related
Sep 25, 2007
i want to write a database trigger to increment the salary by 10% for technicians who have done three tests on a particular date.
there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.
Employee (EmployeeID,Name,Salary,Tpye)
TestEvent(TestNo,EmployeeID,TestDate)
Hussain
View 2 Replies
View Related
Jan 26, 2008
is there a way to create a SELECT clause which counts the accumulate hours from tw columns in same row (entering hour and leaving hour) and then calculating the total price according to a parameter?
Shimi
View 2 Replies
View Related
Nov 15, 2007
Ok, I have the following table
CREATE TABLE employees (
employee_number char(6) NOT NULL
, known_as char(20)
, surname char(20)
, job_title char(20)
, manager_number char(6) NULL
, unique_identifier char(6) NOT NULL PRIMARY KEY
)
--unique_identifier is in the format 000123456789
Now I have a conundrum when trying to create organization charts in Visio, so I figured I'd try reproduce the format that a bunch of walkthroughs suggest, which is with the first column being an int identity(1,1) column as the employeeID with the managerID being an int column also, referencing the employeeID.
Hope I've not lost you just yet ;)
So here's what I figured - create a new table with the new integer columns, slap my current data into it and then update the managerID as necessary...
Except I can't work out the update statement for this!
CREATE TABLE gvVisioTest (
employeeID int identity(1,1)
, employee_number char(6)
, job_title char(40)
, department_reference char(10)
, managerID int
, manager_number char(6)
)
GO
INSERT INTO gvVisioTest(employee_number, job_title, department_reference, manager_number)
SELECT employee_number, job_title, department_reference, manager_number
FROM employees
GO
--Update managerID with relevant employeeID
GO
DROP TABLE gvVisioTest
Any ideas?
Oh and this is legacy so the design is flawed, modified over time (the manager field is a bodge put in 5 years ago), so yeah unfortunately I have to work with what I've got :(
View 14 Replies
View Related
May 26, 2008
Dear all,
I have a table that contains the following columns:
COMPANY | EMPLOYEE | SALARY
I want to select the TOP 5 PERCENT employees from each company, ordered by salary. Is this possible?! Thanks!
Pedro Martins
View 7 Replies
View Related
Mar 1, 2007
Dear All,i want to know how to get top three salary getters from the employee(eid , ename, salary) table
i tried this select top 3 salary from employee order by salary desc
but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200
how can i do it
please help
thanks
View 1 Replies
View Related
Jan 31, 2008
I am using SqlServer 2000 with asp.net 2.0, I have a table tbl_employees, with fields (empId, empName, empManagerId), with following data...
empId
empName
empManagerId
1
A
2
B
1
3
C
2
4
D
2
5
E
4
Now the question is that what should be the single line query or best solution if i want to get all employess under a perticular manager ?For example; Employees under 'A' are (B,C,D,E) //(C,D,E are also indirectly under A)Emplloyess under 'B' are (C,D & E; E is also under B as his because his managwer 'D' is himself under 'B')
Please advise..Thanks alot.
View 7 Replies
View Related
Apr 5, 2007
I have an employee table with manager id and employee ids , i need to find all the employee ids for a manager id . Each employee can be a manager in turn . So I need to find all the employees under one manager and if any of the employee is in turn a manager , i need to find the employees under him as well .
The table structure is defined and i cannot edit it .
Please let me know if we could have a single query to do this .
Thank you
kishore
View 14 Replies
View Related
Aug 9, 2005
What is the query to find a 5th highest salary.in emp table.i also use top1,top2,..but i don't get a result.what is new in sql server 2005.
View 11 Replies
View Related
May 11, 2006
I have a query that returns a result that looks like this:
amount count
steve 122000 12
jim 145213 13
paul 62325 7
I need to add 2 columns to this query that rank the employees by count and amount with a number, so for example:
amount amount_rank count count_rank
steve 122000 2 12 2
jim 145213 1 13 1
paul 62325 3 7 3
I was going to paste the entire query in but I thought I'd try this first to see if I could get by with a hint. So essentially I need to evaluate with an order by or something and then insert a number as amount_rank and number as count_rank.
Any ideas?
View 3 Replies
View Related
Jun 27, 2013
I am uploading input sample data and desired output data, can get the desire output.
View 4 Replies
View Related
Jul 15, 2013
I have this column Sdate in my Employees table. This value represent the start date working.
I need to get all of the employees that between 8 and 10 month of work from today. How can I do it?
View 3 Replies
View Related
Sep 8, 2006
Hello,I'm facing with a problem though it may look very simple for many ofu it is a bit complicated for me So kindly please help me outi have a table emp with employee id,name and mgridI need to display the managers and the employee working under thatmanager . How should this be done.Thanking uBroken Arrow
View 6 Replies
View Related
Jun 17, 2015
I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd
Test Inside Property2009-08-29 2010-07-31
Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
View 2 Replies
View Related
Aug 30, 2005
i want to calculate the month salary of an employee.which will be calculated on the basis of previous available leaves and present available leave(i.e) 2 per month.
View 2 Replies
View Related