Query-Sql Developer

Question

I am creating some queries for my project, but I face some difficulties with the follow ones:

A SELECT statement containing a subquery to retrieve a list of Locations (location id and street_address) that have employees with higher salary than the average of their department. The list must contain the number of those employees and their total salary per location. Name these aggregates respectively "emp" and "totalsalary". The locations in the list must be ordered by location_id.

Select LOCATION_ID, STREET_ADDRESS
from HR.LOCATIONS IN
(Select Employee_id
from HR.Employees
Where Salary > round(avg(SALARY)))
order by location_id; 

error: SQL command not properly ended

and the second query is the following

The JOB_HISTORY table can contain more than one entries for an employee who was hired more than once. Create a query to retrieve a list of Employees that were hired more than once. Include the columns EMPLOYEE_ID, LAST_NAME, FIRST_NAME and the aggregate "Times Hired".

SELECT FIRST_NAME,LAST_NAME,EMPLOYEE_ID, 
count (*)as TIMES_HIRED 
from HR.JOB_HISTORY, HR.EMPLOYEES 
where EMPLOYEE_ID= LAST_NAME 
having COUNT(*) >1; 
error: not a single-group

Anjani Kumar Agrawal · Accepted Answer · 2016-07-20 16:30:24Z

1

Try these hope they help. I am making an assumption that employee table has Location_Id column. I am adding Employee_id to Group by to make sure you get correct TotalSalary:

Select LOCATION_ID, STREET_ADDRESS, Count(Employee_id) AS emp, SUM(salary) AS totalsalary
from HR.LOCATIONS INNER JOIN
(Select Employee_id, salary
from HR.Employees
Having Salary > round(avg(SALARY), 0)) AS Emp ON HR.LOCATION_ID = Emp.Location_ID
Group By LOCATION_ID, STREET_ADDRESS, Employee_id
order by location_id;

For the second question:

SELECT FIRST_NAME,LAST_NAME,EMPLOYEE_ID, 
count(Employee_id) as TIMES_HIRED 
from HR.JOB_HISTORY inner join HR.EMPLOYEES On JOB_HISTORY.Employee_id = Employees.Employee_id
Group By FIRST_NAME,LAST_NAME,EMPLOYEE_ID
Having count(Employee_id) >1;

edited Jul 20, 2016 at 16:30

answered Jul 20, 2016 at 15:26

Anjani Kumar Agrawal

3752 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shaq Somersby Over a year ago

At the first one it gives me error: group function is not allowed here

Shaq Somersby Over a year ago

At the second one it gives me error: "TIMES_HIRED": invalid identifier

Anjani Kumar Agrawal Over a year ago

For the first one instead of where Try: Having Salary > round(avg(SALARY), 0)

Anjani Kumar Agrawal Over a year ago

For the second try: Having count(Employee_id) > 1;

Collectives™ on Stack Overflow

Query-Sql Developer

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related