Doubts about sub queries-CodePudding

Topic: staff list each department on the number of employees (employee number must be greater than 3), and department name,

My statement: select a. d. eptno, a.c mount (*) from (select e. *, d.d name from emp e, d dept where e.d eptno=d.d eptno) a
Group by a. d. eptno
Having a.c mount (*) & gt; 3;

System tip: lack of expression;

Standard answer: select d. *, Ed. The cou from dept d, (select deptno, count (*) cou from emp group by deptno having the count (*) & gt; 3) Ed
Where d.d eptno=Ed. Deptno;

Does my child query logic error? Please feel free to comment!!!!! thank you

CodePudding user response:

refer to the original poster BANKER HU response:

title: staff list each department on the number of employees (employee number must be greater than 3), and department name,

My statement: select a. d. eptno, a.c mount (*) from (select e. *, d.d name from emp e, d dept where e.d eptno=d.d eptno) a
Group by a. d. eptno
Having a.c mount (*) & gt; 3;

System tip: lack of expression;

Standard answer: select d. *, Ed. The cou from dept d, (select deptno, count (*) cou from emp group by deptno having the count (*) & gt; 3) Ed
Where d.d eptno=Ed. Deptno;

Does my child query logic error? Please feel free to comment!!!!! Thank you

You the subquery logic of no problem, the SQL statement is true, your answer and the standard answer is a grouping after connection, the other one is after the first group connection, but there is a problem, that is the somebody else is to department name and department number, the original poster is number, department number, and department are given you don't need such fundamental connection; Standard answer, but take out all the fields and departments of the department table number, should be only names and departments number is enough,

CodePudding user response:

Thanks elder give directions, thank you very much