July 7, 2008 at 12:15 pm
employees
emp_name
emp_salary
emp_dept_id(foriegn key to the departments table)
departments
dept_id(primary key)
dept_name
i have two tables and i just want to know how to write a sql query for
1).what would be the select statement to show ,for every department in which the average salary >$50,000,the highest salary paid to the employees of the department.
2).what would be the select statement to show,for every department the name and salry for the highest paid employee of the departmnent.
3)If the employee table has 500 rows and departments table has 20 rows, how many rows would the following statement return.
select * from employees, departments?
July 8, 2008 at 12:29 am
This looks suspiciously like homework. Is that the case?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2008 at 10:18 am
1).what would be the select statement to show ,for every department in which the average salary >$50,000,the highest salary paid to the employees of the department.
Answer
========
select dept_name,max(emp_salary) as MaxSalary from employees inner join departments on departments.dept_id= employees.emp_dept_id group by emp_dept_id,dept_name having (sum(emp_salary)/count(emp_name))>$50000
2).what would be the select statement to show,for every department the name and salry for the highest paid employee of the departmnent.
Answer
=========
select dept_name,max(emp_salary) as highDeptSalary from employees inner join departments on departments.dept_id= employees.emp_dept_id group by emp_dept_id,dept_name
3)If the employee table has 500 rows and departments table has 20 rows, how many rows would the following statement return.
select * from employees, departments?
Answer
=========
500 * 20 = 10,000
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply