I have the following SQL query:

SELECT DEPARTMENT.dnumber, COUNT(*)
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.dno = DEPARTMENT.dnumber
GROUP BY DEPARTMENT.dnumber

I had tested this query on the following table instances:

department
+--------------------+------------+-------------+-------------+
| dname | dnumber | mgrssn | mgrstartdate |
+--------------------+------------+-------------+-------------+
| R & D | 5 | 333445555 | 1988-05-22 |
| Administration | 4 | 987654321 | 1995-01-01 |
| Headquarters | 1 | 888665555 | 1981-06-19 |
| security | 2 | 123456789 | 1990-07-15 |
+--------------------+------------+-------------+--------------+

employee
+------------+-----+------------+----------------+-----------------+-------------------------------------+------+----------+-----------------+-----+
| fname | mint | lname | ssn | bdate | address | sex | salary | superssn | dno |
+------------+-----+------------+----------------+-----------------+-------------------------------------+------+----------+-----------------+-----+
| John | B | Smith | 123456789 | 1965-01-09 | 231 Fondren, Houston, TX | M | 30000 | 333445555 | 5 |
| Franklin | T | Wong | 333445555 | 1965-12-18 | 638 Voss, Houston, TX | M | 40000 | 888665555 | 5 |
| Alicia | J | Zelaya | 999887777 | 1968-07-19 | 3321 Castle, Spring, TX | F | 25000 | 987654321 | 4 |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX | F | 43000 | 888665555 | 4 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX | M | 38000 | 333445555 | 5 |
| Joyce | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX | F | 25000 | 333445555 | 5 |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 960 Dalls, Houston, TX | M | 25000 | 987654321 | 4 |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone, Houston, TX | M | 55000 | NULL | 1 |
| Larry | W | Clinton | 777225555 | 1972-03-15 | 100 Main, Houston, TX | M | 50000 | 888665555 | 1 |
+------------+------+-----------+-----------------+----------------+--------------------------------------+------+----------+----------------+-----+
(Note: the field dno is the department number of department the employee woks for)


I got following result:
+------------+--------------+
| dnumber | COUNT(*) |
+------------+--------------+
| 1 | 2 |
| 4 | 3 |
| 5 | 4 |
+------------+--------------+

Although department 2 has no employee. It should show up on the result with the value of count(*) to be 0 according my professor. But it did not show up. I tested the above query in MySQL. Do I need to config something in MySQL in order to let department 2 to show up. Or this is the universal result I will get no matter whether I use MySQL, Oracal, or Microsoft Access. Thanks.

You did an inner join to employee. Your query asked to count how many employees were in departments that had employees. You will have to do a query that will list all departments and then go find how many employees in each. Since this is for homework I will just provide a suggestion or suffer the wrath of others. A sum on a case statement would work for this or you could union the departments with no employees to the departments with employees.

No, this is not for homework. This is a sample in lecture slide. I swell. The original problem statement is "For each department, find the department number and the number of employees of the department. It does not matter whether the department has employee or not".

Professor tried to show that by using SELECT, FROM, WHERE, and GROUP BY alone, the above problem could be solved. But I tested his query (see the first post), the result only contains the number of employees for the departments which have employees. The results does not contain the number of employees for the departments which do not have employees.

See if you can come out a SQL query for above problem. The SQL query should contain SELECT, FROM, WHERE, and GROUP BY only. Or you tell me this is impossible.

of course it's possible

hint: use either a LEFT OUTER JOIN or RIGHT OUTER JOIN

(you can look up which one would be appropriate in your situation)

But we can not use JOIN. The only SQL constructS professor used are SELECT, FROM, WHERE, and GROUP BY.

if you cannot use JOIN, use a subquery

by the way, in the real world, "cannot use JOIN" is a bogus requirement

that's what's wrong with homework assigments, often they are unrealistic

FYI the query you posted in #1 is a join

OK, then show me your SQL query for this problem. THIS IS NOT A HOMEWORK QUESTION.

Did you try to write a query by yourself using the hing Rudy gave you in post #4? If so, how does it look like and what did you get as a result?

I did not try to write a query by myself using the hint Rudy gave me in post #4?

to do it without joins is interesting challenge

you do realize that the query you posted in #1 DOES use a join, don't you?
look ma, no WHERE clause

Yes, it is working. But can you remove UNION from query? With UNION, I can come out my own SQL query without any help.

okay, then do it

good luck with your assignment

Without neither UNION nor JOIN:Even without GROUP BY !

I used Peter's coding to retrieve only those departments without employees.
The database returns an Oracle error. ORA-00904: "COUNTER": invalid identifier. However, when the WHERE keyword is followed by the full code of the made-up column, the SQL is executed okay.
This above is okay. Very curious too me. Any comments?