Question 113510 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – sql query question makes me confused please

Given the following two relational schemas, the EMPLOYEE relational schema records the employee number (ssn), age (age) and the department (dno), the primary key is ssn, and the DEPARTMENT relational schema records the department number (dnum ) and name (dname), The primary key is dnum, in which the foreign key "dno" of EMPLOYEE refers to the primary key "dnum" of DEPARTMENT,

please use SQL GROUP BY to write the query ”For each department that has more than two employees, retrieve the department name and the number of its employees who each is more than twenty-five years old)”.

Thank you guys

Tags:

2

Answers


  1. 0

    You can use as approach. It’s not exactly your tables but you can easy match it tot your structure:

    SELECT DEPARTMENT_NAME
FROM DEPARTMENTS D
JOIN EMPLOYEES E USING (DEPARTMENT_ID)
GROUP BY DEPARTMENT_ID, DEPARTMENT_NAME
HAVING COUNT(EMPLOYEE_ID) > 2
;

    format sql online

    Login or Signup to reply.
  2. 0 
    SELECT d.dname, count(e.ssn) as cnt
FROM Employee e JOIN department d 
on e.dno = d.dnum
WHERE e.age> = 25 
Group BY e.dname
HAVING COUNT(ssn) >= 2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search