Re: SQL Statement Question

Elmasri text? Why don't you just give us the professor's email address, and we'll submit the naswers directly?

"convo" <member29171_at_dbforums.com> wrote in message news: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);
> 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);
>
> 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)
> Deptment location relation has department no, and department location.
> Since each department can have multiple locations, so DNUMBER and
> DLOCTION together are Key.
>
> How to write it? how about this:
> SELECT DNAME, DNUMBER
> COUNT
> 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.com
Received on Fri May 09 2003 - 16:13:35 CEST