SQL Querying Using Subquery

Question

Department (dNumber, dName)

Employee (SSN, eName, salary, /dNumber/)

Project (pNumber, pName, pLocation)

WorksOn (/SSN/, /pNumber/, hours)

These are the tables I am working with. I want to list all department numbers and names where more than 5 employees work, and count the number of those employees whose salaries are greater than 40,000. I want to practice using subqueries.

Here is what I wrote:

SELECT T.dNumber, T.dName, COUNT(T.SSN)
FROM 
(
SELECT d.dNumber, d.dName, e.SSN
FROM Department d, Employee e
WHERE e.salary > 40000 AND d.dNumber = e.dNo
) as T
GROUP BY dNumber, dName
HAVING COUNT(T.SSN) > 5;

But it looks and feels redundant. It's almost as if I don't really need to use subqueries. Any ideas?

Thank you!

Your query returns only companies, where more than 5 employees getting 40k+. But according to your description you need to display companies with 5+ employees and count those who makes 40k+. Which one is correct? — Uriil
– Uriil, Commented Oct 26, 2016 at 6:55
@Uriil My English might be bad, but I have a feeling you're saying the same thing. — Radu Gheorghiu
– Radu Gheorghiu, Commented Oct 26, 2016 at 6:58
In case this is a homework question: any restrictions on what you are allowed to use (especially: are you allowed to use "case when"/"if")? — Solarflare
– Solarflare, Commented Oct 26, 2016 at 7:00
It is not the same thing. When you have a department with 10 employees that all make 39000, you will not list it using your current query, but your task seems to want to include that department, providing the data "10 employees, 0 above 40000" — Solarflare
– Solarflare, Commented Oct 26, 2016 at 7:05

Uriil · Accepted Answer · 2016-10-26 07:00:47Z

2

I think below query will return you expected result(it's for MySql, in case of MSSQL you will need to replace IF with CASE condition):

SELECT T.dNumber, T.dName, COUNT(T.SSN) AS TotalEmployees, sum(IF(T.salary > 4000, 1, 0)) AS EmployeesOverFourty
FROM Department d
INNER JOIN Employee e ON d.dNumber = e.dNo
GROUP BY dNumber, dName
HAVING TotalEmployees > 5;

answered Oct 26, 2016 at 7:00

Uriil

12.8k11 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

knockout · Accepted Answer · 2016-10-26 07:05:28Z

0


SELECT
    d1.dNumber,
    d1.dName,
    ISNULL (SUM (CASE WHEN e1.SSN IS NOT NULL THEN 1 ELSE 0 END), 0) AS EmployeeCount
FROM
(
    SELECT
        Department.dNumber,
        Department.dName
    FROM
        Employee
    JOIN
        Department
    ON
        Employee.dNumber = Department.dNumber
    GROUP BY
        Employee.dNumber
    HAVING
        COUNT(*) > 5
) AS d
LEFT JOIN
    Employee e1
ON
    e1.dNumber = d1.dNumber AND
    e1.Salary > 40000
GROUP BY
    d1.dNumber,
    d1.dName

answered Oct 26, 2016 at 7:05

knockout

1494 bronze badges

Collectives™ on Stack Overflow

SQL Querying Using Subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related