附件6b.oracle數(shù)據(jù)庫scott用戶四張表經(jīng)典練習(xí)題答案

Oracle數(shù)據(jù)庫scott用戶四張表經(jīng)典練習(xí)題答案的用法selectdnamefromdeptwheredeptnoin(selectdeptnofromempgroupbydeptnohavingcount(*)>1);selectenamefromempwheresal>(selectsalfromempwhereenameselecte.empno雇員,e.ename雇員,m.empno,m.enamefromempe,empmwheree.mgr=m.empno;selecte.empno雇員,e.ename雇員,e.hiredate,m.empno,m.ename,m.hiredatefromempe,empmwheree.mgr=m.empnoandselect*fromdeptdleftjoinempeonCLERK（selecte.ename雇員,e.job雇員職位,d.dname部門名fromempe,deptdwheree.deptno=d.deptnoandjob='CLERK';selecte.ename雇員,e.job雇員職位,d.dname部門fromempejoindeptdone.deptno=d.deptnoandjob='CLERK';selectename雇員,job雇員職位,dname部門fromempjoindeptusing(deptnowherejob='CLERK';selectename雇員,job雇員職位,dname部門fromempnaturaljoindeptwhereselectjobfromempwhere“SALES（selectenamefromempwheredeptno=(selectdeptnofromdeptwhereselectenamefromempwheresal>(selectavg(sal)from ='SCOTT')andename!='SCOTT' deptno=30);selectename,salfromwheresal>all(selectsalfromempwheredeptno=30selectename,salfromwheresal>(selectmax(sal)fromempwheredeptno=30資和平均服務(wù)期限。(經(jīng)典的groupby用法)selectdeptno,count(*),avg(a.sal),avg(sysdate-HIREDATE)fromempagroupby列出所有員工的、部門名稱和工資.(經(jīng)典的兩個表的連接查詢，用具體的名稱替換一個表中的主鍵的id(解決很多人在實際運用中會遇到的不能綁定多列的問題)，也可用where來查詢,5比較)selectename,sal,(selectdnamefromdeptawherea.deptno=b.deptno)asdnamefromempb;列出所有部門的詳細(xì)信息和部門人數(shù)。(因為是*dept和后面臨時表b的全部字段(注意:不只是dept的字段,注意*號))select*fromdeptaleftjoin(selectdeptno,count(*)fromempgroupbydeptno)bona.deptno=b.deptno;selectjob,min(sal)fromempgroupbyjobselectmin(salfromempwh