笔记-oracle练习题day达内IT培训

课后作1SQL按照要求编写SQL语【题目<enamename>earns<sal>monthlybutwantsDreamKingearns$5000monthlybutwants A ENAME 作了几个月,用整数表示)。现有数据表Customer，其结构如下所cust_idNUMBER(4)PrimaryKey,--客户编码cnameVARCHAR2(25)NotNull, birthdayDATE, account 显示“notavailable”。如果没有余额信息，则显示“noaccount”。构造SQL语句查询员工表emp中员工编码empno，ename，以及月收入(薪水+奖金)， 19801217为选择具有各个job员工人数(提示job为2000，没有管理者的员工丌计算在哪一子句可实现SELECT5000信息GROUPBYdept_idWHEREAVG(sal)<GROUPBYAVG(sal)HAVINGAVG(sal)<GROUPBYdept_idHAVINGAVG(sal)<GROUPBYAVG(sal)<SELECT100/NVL(ty,0)FROMTYnull值时，将导致出错，其原因是sid_idnumberstart_datedateend_datedateSELECTTO_CHAR(2000,'$0,000.00')FROMSELECTTO_CHAR(2000,'$9,999.99')FROMSELECTTO_CHAR(2000,'$2,000.00')FROMSELECTempno,enameFROMempWHEREempno=7782ORempnoWHERE句中，可以实现不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，幵显示出各员工在公司工作的月份数(即:不当前日期比较,该员工已经工作了几个月,用整数表示)。selectename,round(months_between(sysdate,hiredate))hire_monthsfromemp;现有数据表Customer，其结构如下所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构造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)>=哪一子句可实现SELECT5000信息GROUPBYdept_idWHEREAVG(sal)<GROUPBYAVG(sal)HAVINGAVG(sal)<GROUPBYdept_idHAVINGAVG(sal)<GROUPBYAVG(sal)<SELECT100/NVL(ty,0)FROMTYnull值时，将导致出错，其原因是sid_idnumberstart_datedateend_datedate列SELECTTO_CHAR(2000,'$0,000.00')FROMSELECTTO_CHAR(2000,'$9,999.99')