SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SOLVED: Simple average query SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aprilrocks92
Starting Member

United Kingdom
3 Posts

Posted - 12/03/2012 :  17:20:16  Show Profile  Reply with Quote
I have the following relational database:

employee(employee_id (primary key), salary)
manages(manager_name, employee_id (primary key))
guest(guest_id (primary key), loyalty_number, guest_name)
stays(room_id (primary key), guest_id, start_date, end_date)

I also have the following query, "Find the names of all the managers whose salary are $1000 more than the average employee salary").

The query I have written is:

select manager_name
from manages, employee
where salary > (select avg(salary) + 1000
from employee
where employee.employee_id = manages.employee_id)

However, this does not seem to give me the right answer. Any suggestions?

Edited by - aprilrocks92 on 12/03/2012 17:47:43

shilpash
Yak Posting Veteran

90 Posts

Posted - 12/03/2012 :  17:32:59  Show Profile  Reply with Quote
SELECT manager_name
FROM manages
INNER JOIN employee
ON manages.employee_id = employee.employee_id
WHERE salary > (SELECT AVG(salary) + 1000
FROM employee
)

Edited by - shilpash on 12/03/2012 17:35:54
Go to Top of Page

aprilrocks92
Starting Member

United Kingdom
3 Posts

Posted - 12/03/2012 :  17:36:27  Show Profile  Reply with Quote
Thank you. However, it does not seem to work. It generates the following message:
"column 'salary' does not exist".

quote:
Originally posted by shilpash

SELECT manager_name
FROM manages
WHERE salary > (SELECT AVG(salary) + 1000
FROM employee
)

Go to Top of Page

shilpash
Yak Posting Veteran

90 Posts

Posted - 12/03/2012 :  17:36:59  Show Profile  Reply with Quote
try now.I have edited the code
Go to Top of Page

shilpash
Yak Posting Veteran

90 Posts

Posted - 12/03/2012 :  17:38:19  Show Profile  Reply with Quote
Use this--

SELECT manager_name
FROM manages
INNER JOIN employee
ON manages.employee_id = employee.employee_id
WHERE salary > (SELECT AVG(salary) + 1000
FROM employee
)
Go to Top of Page

aprilrocks92
Starting Member

United Kingdom
3 Posts

Posted - 12/03/2012 :  17:45:37  Show Profile  Reply with Quote
Thank you very much, this works!

quote:
Originally posted by shilpash

Use this--

SELECT manager_name
FROM manages
INNER JOIN employee
ON manages.employee_id = employee.employee_id
WHERE salary > (SELECT AVG(salary) + 1000
FROM employee
)

Go to Top of Page

shilpash
Yak Posting Veteran

90 Posts

Posted - 12/03/2012 :  17:48:58  Show Profile  Reply with Quote
You are welcome.

Edited by - shilpash on 12/03/2012 17:49:14
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000