課后作1SQL按照要求編寫(xiě)SQL語(yǔ)【題目<enamename>earns<sal>monthlybutwantsDreamKingearns$5000monthlybutwants A ENAME 作了幾個(gè)月,用整數(shù)表示)?，F(xiàn)有數(shù)據(jù)表Customer，其結(jié)構(gòu)如下所cust_idNUMBER(4)PrimaryKey,--客戶(hù)編碼cnameVARCHAR2(25)NotNull, birthdayDATE, account 顯示“notavailable”。如果沒(méi)有余額信息，則顯示“noaccount”。構(gòu)造SQL語(yǔ)句查詢(xún)員工表emp中員工編碼empno，ename，以及月收入(薪水+獎(jiǎng)金)， 19801217為選擇具有各個(gè)job員工人數(shù)(提示job為2000，沒(méi)有管理者的員工丌計(jì)算在哪一子句可實(shí)現(xiàn)SELECT5000信息GROUPBYdept_idWHEREAVG(sal)<GROUPBYAVG(sal)HAVINGAVG(sal)<GROUPBYdept_idHAVINGAVG(sal)<GROUPBYAVG(sal)<SELECT100/NVL(ty,0)FROMTYnull值時(shí)，將導(dǎo)致出錯(cuò)，其原因是sid_idnumberstart_datedateend_datedateSELECTTO_CHAR(2000,'$0,000.00')FROMSELECTTO_CHAR(2000,'$9,999.99')FROMSELECTTO_CHAR(2000,'$2,000.00')FROMSELECTempno,enameFROMempWHEREempno=7782ORempnoWHERE句中，可以實(shí)現(xiàn)不OR同功能的操作符是BETWEEN…AND【參考答案selectename,length(ename)fromorderby<enamename>earns<sal>monthlybutwantsDreamKingearns$5000monthlybutwantsselectename||'earns$'||salary||'monthlybutwants$'||salary*3"DreamSalary"fromemp; A ENAME SELECTename,job, )ASFROM查詢(xún)各員工的ename，幵顯示出各員工在公司工作的月份數(shù)(即:不當(dāng)前日期比較,該員工已經(jīng)工作了幾個(gè)月,用整數(shù)表示)。selectename,round(months_between(sysdate,hiredate))hire_monthsfromemp;現(xiàn)有數(shù)據(jù)表Customer，其結(jié)構(gòu)如下所cust_idNUMBER(4)PrimaryKey,--客戶(hù)編碼cnameVARCHAR2(25)NotNull, birthdayDATE, account 顯示“notavailable”。如果沒(méi)有余額信息，則顯示“noaccount”。selectcust_id,nvl(to_char(birthday,'yyyy-mm-dd'),'notavailable'),nvl(to_char(account,'9999'),'noaccount')fromselect*fromCustomerwhereto_char(birthday,'yyyy')=selectsum(account)fromselectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')nowfrom構(gòu)造SQL語(yǔ)句查詢(xún)員工表emp中員工編碼empno，ename，以及月收入(薪水+獎(jiǎng)金)，selectempno,ename,nvl(salary,0)+nvl(bonus,0)month_salaryfromemp;查找員工的長(zhǎng)度是5個(gè)字符的員工信息select*fromempwherelength(ename)= selectenamename,lpad(salary,15,'$')salaryfromselectename,to_char(sal,'$9999.00')salaryfromempwheresal>4000orderbysal 19801217selectenamename,to_char(hiredateyyyy-mm-ddhiredate,to_char(hiredate,'yyyy"年"mm"月"dd"日"')reviewfromselectmax(salary)max_sal,min(salary)min_sal,avg(salary)avg_sal,sum(salary)sum_salfromselectename,salary,round(sal*1.08)fromemp;選擇具有各個(gè)jobjobselectjob,count(*)fromempgroupbyjob;selectmax(salary)-min(salary)"DIFFERENCE"from2000，沒(méi)有管理者的員工丌計(jì)算在selectmgr,min(salary)fromempwheremgrisnotnullgroupbymgrhavingmin(sal)>=哪一子句可實(shí)現(xiàn)SELECT5000信息GROUPBYdept_idWHEREAVG(sal)<GROUPBYAVG(sal)HAVINGAVG(sal)<GROUPBYdept_idHAVINGAVG(sal)<GROUPBYAVG(sal)<SELECT100/NVL(ty,0)FROMTYnull值時(shí)，將導(dǎo)致出錯(cuò)，其原因是sid_idnumberstart_datedateend_datedate列SELECTTO_CHAR(2000,'$0,000.00')FROMSELECTTO_CHAR(2000,'$9,999.99')