筆記-oracle練習(xí)題day達(dá)內(nèi)IT培訓(xùn)

課后作1SQL按照要求編寫SQL語【題目<enamename>earns<sal>monthlybutwantsDreamKingearns$5000monthlybutwants A ENAME 作了幾個月,用整數(shù)表示)?，F(xiàn)有數(shù)據(jù)表Customer，其結(jié)構(gòu)如下所cust_idNUMBER(4)PrimaryKey,--客戶編碼cnameVARCHAR2(25)NotNull, birthdayDATE, account 顯示“notavailable”。如果沒有余額信息，則顯示“noaccount”。構(gòu)造SQL語句查詢員工表emp中員工編碼empno，ename，以及月收入(薪水+獎金)， 19801217為選擇具有各個job員工人數(shù)(提示job為2000，沒有管理者的員工丌計算在哪一子句可實現(xiàn)SELECT5000信息GROUPBYdept_idWHEREAVG(sal)<GROUPBYAVG(sal)HAVINGAVG(sal)<GROUPBYdept_idHAVINGAVG(sal)<GROUPBYAVG(sal)<SELECT100/NVL(ty,0)FROMTYnull值時，將導(dǎo)致出錯，其原因是sid_idnumberstart_datedateend_datedateSELECTTO_CHAR(2000,'$0,000.00')FROMSELECTTO_CHAR(2000,'$9,999.99')FROMSELECTTO_CHAR(2000,'$2,000.00')FROMSELECTempno,enameFROMempWHEREempno=7782ORempnoWHERE句中，可以實現(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查詢各員工的ename，幵顯示出各員工在公司工作的月份數(shù)(即:不當(dāng)前日期比較,該員工已經(jīng)工作了幾個月,用整數(shù)表示)。selectename,round(months_between(sysdate,hiredate))hire_monthsfromemp;現(xiàn)有數(shù)據(jù)表Customer，其結(jié)構(gòu)如下所cust_idNUMBER(4)PrimaryKey,--客戶編碼cnameVARCHAR2(25)NotNull, birthdayDATE, account 顯示“notavailable”。如果沒有余額信息，則顯示“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語句查詢員工表emp中員工編碼empno，ename，以及月收入(薪水+獎金)，selectempno,ename,nvl(salary,0)+nvl(bonus,0)month_salaryfromemp;查找員工的長度是5個字符的員工信息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;選擇具有各個jobjobselectjob,count(*)fromempgroupbyjob;selectmax(salary)-min(salary)"DIFFERENCE"from2000，沒有管理者的員工丌計算在selectmgr,min(salary)fromempwheremgrisnotnullgroupbymgrhavingmin(sal)>=哪一子句可實現(xiàn)SELECT5000信息GROUPBYdept_idWHEREAVG(sal)<GROUPBYAVG(sal)HAVINGAVG(sal)<GROUPBYdept_idHAVINGAVG(sal)<GROUPBYAVG(sal)<SELECT100/NVL(ty,0)FROMTYnull值時，將導(dǎo)致出錯，其原因是sid_idnumberstart_datedateend_datedate列SELECTTO_CHAR(2000,'$0,000.00')FROMSELECTTO_CHAR(2000,'$9,999.99')