Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> SQL Statement Question

SQL Statement Question

From: convo <member29171_at_dbforums.com>
Date: Wed, 07 May 2003 17:25:15 +0000
Message-ID: <2851049.1052328315@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 Wed May 07 2003 - 12:25:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US