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
CodePudding user response：
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
;
CodePudding user response：
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;