SQL Statement Question
Date: Wed, 07 May 2003 17:25:15 +0000
Message-ID: <2851049.1052328315_at_dbforums.com>
to write this in SQL: “For each department with more than one locations,
retrieve the departments name, department number, and the number of
employees who work for the department.” with the following schema:
EMPLOYEE(FNAME,LNAME,SSN,BDATE,ADDRESS,SEX,SALARY,
SUPERSSN,DNO);
DEPARTMENT(DNAME, DNUMBER,MGRSSN,MGRSTARTDATE);
Department relation has department name, department no (key), manager's
ssn and Manager's starting date;
DEPT_LOCATIONS(DNUMBER,DLOCATION)
How to write it? how about this:
Employee relation specifies employee's last name, first name, ssn (key),
birthrate, Address, sex, salary, supervisor's ssn, department he is
working for (dno is the foreign key referring to DNUMBER in Department);
Deptment location relation has department no, and department location.
Since each department can have multiple locations, so DNUMBER and
DLOCTION together are Key.
SELECT DNAME, DNUMBER
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER.DEPARTMENT = DNO.EMPLOYEE
AND WHERE DNUMBER.DEPARTMENT = DLOCATION.DEPARTMENT
GROUP BY DNUMBER.DEPARTMENT
HAVING COUNT OF DNUMBER.DEPARTMENT > 1
-- Posted via http://dbforums.comReceived on Wed May 07 2003 - 19:25:15 CEST