根據(jù)Oracle數(shù)據(jù)庫(kù)scott模式下的emp表和dept表

題目要求：依據(jù)Oracle數(shù)據(jù)庫(kù)scott模式下的emp表和dept表，達(dá)成以下操作。1）查問(wèn)20號(hào)部門的全部職工信息。select*fromempwheredeptno=20;2）查問(wèn)全部工種為CLERK的職工的工號(hào)、職工名和部門名。selectempno,ename,deptnofromempwherejoblike'CLERK';3）查問(wèn)獎(jiǎng)金（COMM）高于薪資（SAL）的職工信息。select*fromempwherecomm>sal;4）查問(wèn)獎(jiǎng)金高于薪資的20%的職工信息。select*fromempwherecomm>(sal*;5）查問(wèn)10號(hào)部門中工種為MANAGER和20號(hào)部門中工種為CLERK的職工的信息。select*fromempwhere(deptno=10andjoblike'MANAGER')or(deptno=20andjoblike'CLERK');（6）查問(wèn)全部工種不是MANAGER和CLERK，且薪資大于或等于2000的職工的詳盡信息。select*fromempwherejobnotin('MANAGER','CLERK')andsal>=2000;（7）查問(wèn)有獎(jiǎng)金的職工的不一樣工種。selectdistinctjobfromempwherecommisnotnull;（8）查問(wèn)全部職工薪資和獎(jiǎng)金的和。selectename,(sal+nvl(comm,0))salcommfromemp;（9）查問(wèn)沒(méi)有獎(jiǎng)金或獎(jiǎng)金低于100的職工信息。select*fromempwhere(commisnullorcomm<100);（10）查問(wèn)各月倒數(shù)第2天入職的職工信息。select*fromempwherehiredatein(select(last_day(hiredate)-1)fromemp);（11）查問(wèn)職工工齡大于或等于10年的職工信息。select*fromempwhere(sysdate-hiredate)/365>=10;（12）查問(wèn)職工信息，要求以首字母大寫的方式顯示全部職工的姓名。select

upper(substr(ename,1,1))

||lower(substr(ename,2,length(ename)-1))fromemp;（13）查問(wèn)職工名正好為6個(gè)字符的職工的信息。select*fromempwherelength（ename）=6;（14）查問(wèn)職工名字中不包含字母“S”職工。select*fromempwhereenamenotin(selectenamefromempwhereenamelike'%S%');select*fromempwhereenamenotlike‘%S%’;15）查問(wèn)職工姓名的第2個(gè)字母為“M”的職工信息。select*fromempwhereenamelike'_M%';（16）查問(wèn)全部職工姓名的前3個(gè)字符。selectsubstr(ename,1,3)fromemp;（17）查問(wèn)全部職工的姓名，假如包含字母“s”，則用“S”替代。selectreplace(ename,'s','S')fromemp;18）查問(wèn)職工的姓名和入職日期，并按入職日期從先到后進(jìn)行擺列。selectename,hiredatefromemporderbyhiredateasc;19）顯示全部的姓名、工種、薪資和獎(jiǎng)金，按工種降序擺列，若工種同樣則按薪資升序擺列。selectename,job,sal,commfromemporderbyjobdesc,salasc;20）顯示全部職工的姓名、入職的年份和月份，若入職日期所在的月份排序，若月份同樣則按入職的年份排序。selectename,to_char(hiredate,'yyyy')||'-'||to_char(hiredate,'mm')fromemporderbyto_char(hiredate,'mm'),to_char(hiredate,'yyyy');（21）查問(wèn)在

2月份入職的全部職工信息。select*fromempwhereto_char(hiredate,'mm')=2;（22）查問(wèn)全部職工入職以來(lái)的工作限期，用“

**年**

月**

日”的形式表示。selectename,floor((sysdate-hiredate)/365)||''||floor(mod((sysdate-hiredate),365)/30)||'

年月'||cell(mod(mod((sysdate-hiredate),365),30))||'

天'fromemp;（23）查問(wèn)起碼有一個(gè)職工的部門信息。select*fromdeptwheredeptnoin(selectdistinctdeptnofromempwheremgrisnotnull);（24）查問(wèn)薪資比SMITH職工薪資高的全部職工信息。select*fromempwheresal>(selectsalfromempwhereenamelike'SMITH');（25）查問(wèn)全部職工的姓名及其直接上司的姓名。selectstaname,enamesupnamefrom(selectenamestaname,mgrfromemp)tjoinempon=;（26）查問(wèn)入職日期早于其直接上司領(lǐng)導(dǎo)的全部職工信息。select*fromempwhereempnoin(selectstaempnofrom(selectempnostaempno,hiredatestahiredate,mgrfromemp)tjoinempon=andstahiredate<hiredate);27）查問(wèn)全部部門及其職工信息，包含那些沒(méi)有職工的部門。select*fromdeptleftjoinempon=orderby;28）查問(wèn)全部職工及其部門信息，包含那些還不屬于任何部門的職工。29）查問(wèn)全部工種為CLERK的職工的姓名及其部門名稱。selectename,dnamefromempjoindeptonjoblike'CLERK'and=

;（30）查問(wèn)最低薪資大于

2500的各樣工作。selectjobfrom(selectmin(sal)min_sal,jobfromempgroupbyjob)wheremin_sal>2500;（31）查問(wèn)最低薪資低于2000的部門及其職工信息。select*fromempwheredeptnoin(selectdeptnofrom

(select

min(sal)min_sal,deptnofromempgroupbydeptno)wheremin_sal<'2000');（32）查問(wèn)在

SALES部門工作的職工的姓名信息。select

enamefromempwheredeptno=

(selectdeptnofromdeptwherednamelike'SALES');（33）查問(wèn)薪資高于企業(yè)均勻薪資的全部職工信息。select*fromempwheresal>(selectavg(sal)fromemp);（34）查問(wèn)與SMITH職工從事同樣工作的全部職工信息。select*fromempwherejobin(selectjobfromempwhereenamelike'SMITH')andenamenotlike'SMITH';（35）列出薪資等于30號(hào)部門中某個(gè)職工薪資的全部職工的姓名和薪資。selectename,salfromempwheresal=any(selectsalfromempwheredeptno=;36）查問(wèn)薪資高于30號(hào)部門中工作的全部職工的薪資的職工姓名和薪資。selectename,salfromempwheresal>all(selectsalfromempwheredeptno=;37）查問(wèn)每個(gè)部門中的職工數(shù)目、均勻薪資和均勻工作年限。selectdname,count,avg_sal,avg_datefromdeptjoin(selectcount,avg(sal)avg_sal,avg((sysdate-hiredate)/365)avg_date,deptnofromemp

count(*)groupbydeptno)ton=;38）查問(wèn)從事同一種工作但不屬于同一部門的職工信息。selectdistinct,,fromempt1joinempt2onlikeand<>;39）查問(wèn)各個(gè)部門的詳盡信息以及部門人數(shù)、部門均勻薪資。Selectdept.*,person_num,avg_salfromdept,(selectperson_num,avg(sal)avg_sal,deptnofromempgroupbydeptno)twhere=;

count(*)（40）查問(wèn)各樣工作的最低薪資。selectjob,min(sal)fromempgroupbyjob;（41）查問(wèn)各個(gè)部門中的不一樣工種的最高薪資。selectmax(sal),job,deptnofromempgroupbydeptno,joborderbydeptno,job（42）查問(wèn)10號(hào)部門職工以及領(lǐng)導(dǎo)的信息。select*fromempwhereempnoin(selectmgrfromempwheredeptno=10)

；ordeptno=10

；（43）查問(wèn)各個(gè)部門的人數(shù)及均勻薪資。selectdeptno,count(*),avg(sal)fromempgroupbydeptno

；（44）查問(wèn)薪資為某個(gè)部門均勻薪資的職工信息。select*fromempwheresalin(selectavg(sal)avg_salfromempgroupbydeptno);（45）查問(wèn)薪資高于本部門均勻薪資的職工的信息。selectemp.*fromempjoin(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)ton=andsal>avg_sal;（46）查問(wèn)薪資高于本部門均勻薪資的職工的信息及其部門的均勻薪資。selectemp.*,avg_salfromempjoin(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)ton=andsal>avg_sal;（47）查問(wèn)薪資高于20號(hào)部門某個(gè)職工薪資的職工的信息。select*fromempwheresal>any(selectsalfromempwheredeptno=20);（48）統(tǒng)計(jì)各個(gè)工種的人數(shù)與均勻薪資。selectjob,count(*),avg(sal)fromempgroupbyjob;（49）統(tǒng)計(jì)每個(gè)部門中各個(gè)工種的人數(shù)與均勻薪資。select

deptno,job,count(*),avg(sal)

fromempgroupbydeptno,job

order

bydeptno,job;（50）查問(wèn)薪資、獎(jiǎng)金與10號(hào)部門某個(gè)職工薪資、獎(jiǎng)金都同樣的職工的信息。selectemp.*fromempjoin(selectsal,commfromempwheredeptno=10)ton=andnvl,0)=nvl,0)and!=10;（51）查問(wèn)部門人數(shù)大于5的部門的職工的信息。select*fromempwheredeptnoin(selectdeptnofromempgroupbydeptnohavingcount(*)>5);（52）查問(wèn)全部職工薪資都大于1000的部門的信息。select*fromdeptwheredeptnoin(selectdistinctdeptnofromempwheredeptnonotin(selectdistinctdeptnofromempwheresal<1000));（53）查問(wèn)全部職工薪資都大于1000的部門的信息及其職工信息。select*fromempjoindeptonin(selectdistinctdeptnofromempwheredeptnonotin(selectdistinctdeptnofromempwheresal<1000))and=;（54）查問(wèn)全部職工薪資都在900~3000之間的部門的信息。select*fromdeptwheredeptnoin(selectdistinctdeptnofromempwheredeptnonotin(selectdistinctdeptnofromempwheresalnotbetween900and3000));（55）查問(wèn)全部薪資都在900~3000之間的職工所在部門的職工信息。select*fromempwheredeptnoin(selectdistinctdeptnofromempwheredeptnonotin(selectdistinctdeptnofromempwheresalnotbetween900and3000));（56）查問(wèn)每個(gè)職工的領(lǐng)導(dǎo)所在部門的信息。select*from(select,,mno,mname,fromempe1joinempe2on=tjoindepton=;（57）查問(wèn)人數(shù)最多的部門信息。select*fromdeptwheredeptnoin(selectdeptnofrom(selectcount(*)count,deptnofromempgroupbydeptno)wherecountin(selectmax(count)from(selectcount(*)count,deptnofromempgroupbydeptno)));（58）查問(wèn)30號(hào)部門中薪資排序前3名的職工信息。select*fromempwhereempnoin(selectempnofrom(selectempno,salfromempwheredeptno=30orderbysaldesc)whererownum<4);（59）查問(wèn)全部職工中薪資排在5~10名之間的職工信息。select*fromempwhereempnoin(selectempnofrom(selectempno,rownumnumfrom