Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL queries about an employee file for my assignme

Author  Topic 

Kaboth
Starting Member

1 Post

Posted - 2004-10-05 : 23:00:52
Hi I'm trying to do an assignment for university but I having trouble on a couple of questions. Any help would much appreciated.

(d)List the names of all employees who have two or more dependents.

For the above I have an employee file and a file with dependent info aswell.

SELECT employee_ssn "ssn", COUNT(*) "Number of occurences
FROM Dependent
GROUP BY employee_ssn
HAVING COUNT(*) >=2;

That command has worked well so far but I need the first and last names from the employee file also.

The other 3 questions I have basically no idea :?

(e) Retrieve the name of each employee who works on all the projects controlled by department number 3.
(f) List the names of managers who have no dependents.
(g) For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.
(h) For each department that has more than ten employees, retrieve the department number and the number of its employees who are making more than $50,000.

I'd attach the script but that doesn't seem possible.
Thanks,

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-06 : 00:42:08
SELECT employee_ssn "ssn", COUNT(*) "Number of occurences
FROM Dependent
GROUP BY employee_ssn
HAVING COUNT(*) >=2;

That command has worked well so far but I need the first and last names from the employee file also.

may by not employee "file" but table? How Dependent is joined with employee?

can you show scripts of tables you use? And sample data

basically idea

SELECT employee_ssn "ssn", first_name, last_name, COUNT(*) "Number of occurences"
FROM Dependent join employee on Dependent.employee_ssn=employee.employee_ssn
GROUP BY Dependent.employee_ssn, first_name, last_name
HAVING COUNT(*) >=2
Go to Top of Page
   

- Advertisement -