[Solved] Having clause trouble

Question

This and all your other questions smell like you’re doing some test or course. Shouldn’t it be time that you at least attempt to solve one of these questions yourself?

select
  d.dname,
  AVG(salary)
from
  department d
  inner join employee e on e.Dno = d.dnumber
group by
  d.dname
having
  avg(salary) > 33000

First of all, I use d and e as aliases for Department and Employee. You can specify aliases after the table name in the query. You can use them to prefix field names, but you don’t have to if that field exists in only one table.

Let’s continue with the inner join. There’s a relation between the tables. It looks like department.dnumber contains the department number. Also, employee.Dno contains the department number of the employees. By joining the table, you combine the data. The query will return a row for each department and for all employees. The information of the department is duplicated for each employee that is returned.

The the aggregation. SQL knows a number of aggregate functions. If you select a field or a couple of fields to ‘group’ by, you can use other fields to ‘aggregate’ over. So in this case, I group by department name. I earlier said it would the return the department information multiple times, one time for each employee of the department. Now, this step groups that information again. It returns the department name only one time, and aggregates the employee information. In this case, I use AVG to calculate the avarage salary, but you could also use SUM(salary) to get the total salary of all employees in the department, or COUNT(*) to count the number of employees per department.

Accepted Answer

This and all your other questions smell like you’re doing some test or course. Shouldn’t it be time that you at least attempt to solve one of these questions yourself?

select
  d.dname,
  AVG(salary)
from
  department d
  inner join employee e on e.Dno = d.dnumber
group by
  d.dname
having
  avg(salary) > 33000

First of all, I use d and e as aliases for Department and Employee. You can specify aliases after the table name in the query. You can use them to prefix field names, but you don’t have to if that field exists in only one table.

Let’s continue with the inner join. There’s a relation between the tables. It looks like department.dnumber contains the department number. Also, employee.Dno contains the department number of the employees. By joining the table, you combine the data. The query will return a row for each department and for all employees. The information of the department is duplicated for each employee that is returned.

The the aggregation. SQL knows a number of aggregate functions. If you select a field or a couple of fields to ‘group’ by, you can use other fields to ‘aggregate’ over. So in this case, I group by department name. I earlier said it would the return the department information multiple times, one time for each employee of the department. Now, this step groups that information again. It returns the department name only one time, and aggregates the employee information. In this case, I use AVG to calculate the avarage salary, but you could also use SUM(salary) to get the total salary of all employees in the department, or COUNT(*) to count the number of employees per department.