一个查询的问题

Question

有这么一个题
显示所有部门信息和人数
表是oracle自带的dept和emp
小弟愚钝没想出来怎么写,只写出了查询每个部门有几个人的语句
select deptno,count(deptno) from emp group by deptno;
部门信息的不知道应该如何加进去...请大家指点一下.谢谢

Answer 1

SQL> select d.department_id,d.department_name,count(e.employee_id) from employees e
  2  full join departments d on e.department_id=d.department_id
  3  group by d.department_id,d.department_name
  4  order by d.department_id;DEPARTMENT_ID DEPARTMENT_NAME                COUNT(E.EMPLOYEE_ID)
------------- ------------------------------ --------------------
           10 Administration                                    1
           20 Marketing                                         2
           30 Purchasing                                        6
           40 Human Resources                                   1
           50 Shipping                                         45
           60 IT                                                5
           70 Public Relations                                  1
           80 Sales                                            34
           90 Executive                                         3
          100 Finance                                           6
          110 Accounting                                        2
          120 Treasury                                          0
          130 Corporate Tax                                     0
          140 Control And Credit                                0
          150 Shareholder Services                              0
          160 Benefits                                          0
          170 Manufacturing                                     0
          180 Construction                                      0
          190 Contracting                                       0
          200 Operations                                        0DEPARTMENT_ID DEPARTMENT_NAME                COUNT(E.EMPLOYEE_ID)
------------- ------------------------------ --------------------
          210 IT Support                                        0
          220 NOC                                               0
          230 IT Helpdesk                                       0
          240 Government Sales                                  0
          250 Retail Sales                                      0
          260 Recruiting                                        0
          270 Payroll                                           0

Answer 2

select d.deptno "部门号", d.dname "部门名称", d.loc "所在地",
count(e.empno) "部门人数"
from dept d left join emp e on d.deptno=e.deptno
group by d.deptno, d.dname, d.loc
order by d.deptno;