子查询练习题

重点：作为计算字段使用子查询
select cust_name,cust_state,(select count(*) from orders where 
orders.cust_id=customers.cust_id) as orders from customers
order by cust_name;


10、查询所有“CLERK”（办事员）的姓名及其部门名称及部门人数  
· 找出所有办事员的姓名及部门编号  
SELECT ename,deptno   FROM emp   WHERE job='CLERK'   

· 如果要找到部门名称，则肯定要使用部门表  
SELECT e.ename,d.dname   FROM emp e,dept d    WHERE job='CLERK' 
AND e.deptno=d.deptno  

· 部门人数肯定要使用分组完成，一旦使用分组，肯定是 GROUP BY 
**重点：把查询的数量作为表，然后count字段作为查询的字段**


SELECT e.ename,d.dname,ed.cou   FROM emp e,dept d,(SELECT 
deptno,COUNT(empno) cou FROM emp GROUP BY deptno) ed  
WHERE job='CLERK' AND e.deptno=d.deptno AND ed.deptno=e.deptno 

-或-  采用SQL-1999标准 
select e.ename,d.dname,c.co    from emp e join dept d 
on(e.deptno=d.deptno)    join (select deptno,count(empno) co
 from emp group by deptno) c on(e.deptno=c.deptno)  
  where job='CLERK'

4、 查询出每个部门的最低工资的员工的信息 
SELECT * FROM emp 
WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno) 

5、 分别使用ANY和ALL完成第4题 

SELECT * FROM emp 
WHERE  sal =ANY(SELECT MIN(sal) FROM emp GROUP BY deptno)  ALL完成不了第4题 


6、 查询至少有一个员工的所有部门信息  
· 求出所有部门的员工数量  
SELECT deptno,COUNT(empno) FROM emp   GROUP BY deptno   

· 列出部门人数大于等于 1 的所有部门编号    
SELECT deptno,COUNT(empno)  FROM emp    GROUP BY deptno   HAVING 
COUNT(empno)>＝1  

·  通过部门表，查询出部门的信息即可
SELECT d.*,ed.cou FROM dept d,  (SELECT deptno,COUNT(empno)  cou 
FROM emp GROUP BY deptno HAVING COUNT(empno) >=1) ed   WHERE 
d.deptno=ed.deptno   


―采用SQL－1999标准― 
select d.deptno,d.dname,ed.cou  from dept d join (select 
deptno,count(empno) cou from emp group by deptno 
having count(empno) >=1) ed on(d.deptno=ed.deptno) 


8、查询受雇日期早于其直接领导的所有员工的编号、姓名、部门名称  
· 自身关联，查找 mgr=empno 的同时还要比较 hiredate，先查询编号、姓名  
SELECT e.empno,e.ename   FROM emp e,emp m  WHERE e.mgr=m.empno 
AND e.hiredate<m.hiredate   

· 如果要加入部门的名称，则肯定应该加入 dept 表，做表关联查询  
SELECT e.empno,e.ename,d.dname  FROM emp e,emp m,dept d  
  WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno   

―SQL－1999标准―  
select e1.empno,e1.ename,d.dname     from emp e1 join emp e2 
on(e1.mgr=e2.empno) join dept d on(e1.deptno=d.deptno) where 
e1.hiredate<e2.hiredate


9、查询部门名称和这些部门的员工的信息，同时列出那些没有员工的部门  
· 左、右关联问题  
SELECT d.deptno,d.dname,e.empno,e.ename   FROM dept d,emp e 
 WHERE d.deptno=e.deptno； 

―SQL－1999标准―  
select d.deptno,d.dname,e.empno,e.ename    from dept d left join
 emp e on(d.deptno=e.deptno)