sql query qustion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query qustion

Author

Topic

qabtawi212
Starting Member

3 Posts

Posted - 2011-05-27 : 09:54:25

Hi all
i need your help!

my question:

DEPARTMENTS
department_id
emp_id

EMPLOYEES
employee_id
salary
dept_id

these the important attributes to my question
the question:

Retrieve the employee_id , salary ,department_id of the employee works in the department that has the maximum sum of salaries.

____________
i need sql query to do that..this is home work and i try to solve it but ?!?!?!?!
the teacher give me hint to use three select statement using nested query.
____
and i try this.but of curse it is seem error

select emloyee_id , salary , department id

from employee e ,department d

where e.employee_id=d.department_id

and

d.department_id IN (select sum(salary) ,department_id
frome employee e2 ,department d2
where e2.employee_id =d2.department_id
and
sum(salary) = (select max(sum(salary))

any help please?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 10:03:38

d.department_id IN (select sum(salary)
has to be dapaertmentid in (select daprtmentid

You will need a group by to get the max sum salary for a department - do that first
I think the tutor is wrong - you only need one subquery

get department id with max sum salary - that's the group by and top row ordered by the sum descending
get all employees in that department - that's your in clause

I suspect your tutor is expecting the sum to be obtained on it's own then used in a having clause to get the departments - could be a restriction on your sql implementation.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

qabtawi212
Starting Member

3 Posts

Posted - 2011-05-27 : 10:26:32

thnx nigelrivett to reply
but i cant use group by with tow singles rows
so we might use more than sub-query.

MR nigelrivett can you write the total query to me????please?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 11:04:36

Nope

If you can't use a group by then it's a bit more tricky.

Get the sum of the salaries for each department - this will have to be a subquery in the select statement using the department table.
Get the first entry from that ordered by the sum descending
Then get all the employees in that department.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

qabtawi212
Starting Member

3 Posts

Posted - 2011-05-27 : 12:04:00

thank you nigelrivett for your suggestion
its help-full

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-29 : 18:14:51

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. What you did post was wrong. You ne3ed to read at least one book on data modeling.

CREATE TABLE Departments
(department_id CHAR(10) NOT NULL PRIMARY KEY,
..);

CREATE TABLE Job_Assignments -- relationships are in their own tables
(department_id CHAR(10) NOT NULL
REFERENCES Departments(department_id),
emp_id CHAR(10) NOT NULL
REFERENCES Personnel (emp_id),
salary_amt DECIMAL (8,2) NOT NULL,
..
PRIMARY KEY (department_id, emp_id)
);

CREATE TABLE Personnel -- personnel, not employees
(emp_id CHAR(10) NOT NULL,
..);

>> Retrieve the employee_id, salary_amt, department_id of the employees who work in the department that has the maximum sum of salaries. <<

WITH Salary_Tot(department_id, salary_amt_tot)
AS
(SELECT department_id, SUM(salary_amt)
FROM Job_Assignments
GROUP BY department_id),

Big_Department
AS
(SELECT T1.department_id
FROM Salary_Tot AS T1
WHERE salary_amt_tot
= (SELECT MAX(T2.salary_amt_tot)
FROM Salary_Tot AS T2)
)

SELECT J.emp_id, J.salary_amt, J.department_id
FROM Job_Assignments AS J,
Big_Department AS B
WHERE J.department_id = B.department_id;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources