Oracle经典作业与答案

-----------------------day1-----------------------------------1.查询职员表中工资大于1600的员工姓名和工资Selectename,salfromempwheresal1600;2.查询职员表中员工号为7369的员工的姓名和部门号码Selectename,deptnofromempwhereempno=7369;3.选择职员表中工资不在4000到5000的员工的姓名和工资Selectename,salfromempwheresalnotbetween4000and5000;4.选择职员表中在20和30号部门工作的员工姓名和部门号Selectename,deptnofromempwheredeptnoin(20,30);5.选择职员表中没有管理者的员工姓名及职位,按职位排序Selectename,jobfromempwheremgrisnullorderbyjob;6.选择职员表中有奖金的员工姓名，工资和奖金，按工资倒序排列Selectename,sal,comm.Fromempwherecommisnotnullorderbysaldesc;7.选择职员表中员工姓名的第三个字母是A的员工姓名Selectenamefromempwhereenamelike‘__A%’;8.列出部门表中的部门名字和所在城市；selectdname,locfromdept;9.显示出职员表中的不重复的岗位jobselectdistinctjobfromemp;10.连接职员表中的职员名字、职位、薪水，列之间用逗号连接，列头显示成OUT_PUT(提示:使用连接符||、别名)selectename||','||job||','||OUT_PUT(将列名大写)salfromemp;11.查询职员表emp中员工号、姓名、工资，以及工资提高百分之20%后的结果selectempno,ename,sal,sal*1.2salaryfromemp;12.查询员工的姓名和工资数,条件限定为工资数必须大于1200，并对查询结果按入职时间进行排列，早入职排在前面，晚入职排在后面。selectename,salfromempwheresal1200orderbyhiredate;13.列出除了ACCOUNT部门还有哪些部门。selectdeptno,dname,locfromdeptwheredname'ACCOUNT';-----------------------day2-----------------------------------1.将员工的姓名按首字母排序，并列出姓名的长度（length）selectename,length(ename)fromemporderbyename;2.做查询显示下面形式的结果enamenameearnssalmonthlybutwantssal*3例如：DreamSalaryKingearns$5000monthlybutwants$15000selectename||'earns$'||sal||'monthlybutwants$'||sal*3“DreamSalary”fromemp;3.使用decode函数，按照下面的条件：JOBGRADEPRESIDENTAMANAGERBANALYSTCSALESMANDCLERKE产生类似下面形式的结果ENAMEJOBGRADESMITHCLERKESELECTename,job,DECODE(job,'PRESIDENT','A','MANAGER','B','ANALYST','C','SALESMAN','D','CLERK','E')ASGradeFROMEMP;4.查询各员工的姓名ename，并显示出各员工在公司工作的月份数(即:与当前日期比较,该员工已经工作了几个月,用整数表示)。selectename,round(months_between(sysdate,hiredate))hire_monthsfromemp;5.现有数据表Customer，其结构如下所示：cust_idNUMBER(4)PrimaryKey,--客户编码cnameVARCHAR2(25)NotNull,--客户姓名birthdayDATE,--客户生日accountNUMBER.--客户账户余额(1).构造SQL语句，列出Customer数据表中每个客户的信息。如果客户生日未提供，则该列值显示“notavailable”。如果没有余额信息，则显示“noaccount”。(2).构造SQL语句，列出生日在1987年的客户的全部信息。(3).构造SQL语句，列出客户帐户的余额总数。1）selectcust_id,cname,nvl(to_char(birthday,'yyyy-mm-dd'),'notavailable'),nvl(to_char(account,'9999'),'noaccount')fromCustomer;2）select*fromCustomerwhereextract(yearfrombirthday)='1987';3)selectsum(account)fromCustomer;6.按照”2009-4-1120:35:10”格式显示系统时间。selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')nowfromdual;7.构造SQL语句查询员工表emp中员工编码empno，姓名ename，以及月收入(薪水+奖金)，注意有的员工暂时没有奖金。selectempno,ename,sal+nvl(comm,0)month_salaryfromemp;8.查找员工姓名的长度是5个字符的员工信息。select*fromempwherelength(ename)=5;9.查询员工的姓名和工资，按下面的形式显示：（提示：使用lpad函数）NAMESALARY-----------------------------------------------------SMITH$$$$$$$$$$24000selectenamename,lpad(sal,15,'$')salaryfromemp;10.查询薪水大于2000元的员工的姓名和薪水，薪水值显示为’RMB5000.00’这种形式，并对查询结果按薪水的降序方式进行排列；selectename,to_char(sal,'L9999.00')salaryfromempwheresal2000orderbysaldesc;11.构造查询语句，产生类似于下面形式的结果：NAMEHIREDATEREVIEW-----------------------------------------------------------------------------------------SMITH1980-12-171980年12月17日selectenamename,to_char(hiredate,'yyyy-mm-dd')hiredate,to_char(hiredate,'yyyy年mm月dd日')reviewfromemp;12.显示所有员工的姓名ename，部门号deptno和部门名称dname。Selecte.ename,d.deptno,d.dnameFromempejoindeptdone.deptno=d.deptno;13.选择在DALLAS工作的员工的员工姓名、职位、部门编码、部门名字Selecte.ename,d.deptno,d.dnameFromempejoindeptdone.deptno=d.deptnoandd.loc=‘DALLAS’;14.选择所有员工的姓名ename，员工号deptno，以及他的管理者mgr的姓名ename和员工号deptno，结果类似于下面的格式employeesEmp#managerMgr#SMITH7369FORD7902selectwor.enameemployees,wor.empnoEmp#,mag.enamemanager,mag.empnoMgr#fromempwor,empmagwherewor.mgr=mag.empno;15.查询各部门员工姓名和他们所在位置，结果类似于下面的格式DeptnoEnameLoc20SMITHDALLASselectdeptno,e.ename,d.locfromempejoindeptdusing(deptno);16.查询公司员工工资的最大值，最小值，平均值，总和selectmax(sal),min(sal),avg(sal),sum(sal)fromemp;17.列出每个员工的名字，工资、涨薪后工资（涨幅为8%），元为单位进行四舍五入Selectename,sal,round(sal*1.08)fromemp;18.查询出JONES的领导是谁（JONES向谁报告）。selecte1.enamefromempe1,empe2wheree2.mgr=e1.empnoande2.ename='JONES';19.JONES领导谁。（谁向JONES报告）。selecte1.enamefromempe1,empe2wheree1.mgr=e2.empnoande2.ename='JONES';-----------------------day3-----------------------------------1.查询各职位的员工工资的最大值，最小值，平均值，总和selectjob,max(sal),min(sal),avg(sal),sum(sal)fromempgroupbyjob;2.选择具有各个job的员工人数(提示:对job进行分组)selectjob,count(*)fromempgroupbyjob;3.查询员工最高工资和最低工资的差距,列名为DIFFERENCE；selectmax(sal)-min(sal)DIFFERENCEfromemp;4.查询各个管理者属下员工的最低工资，其中最低工资不能低于800，没有管理者的员工不计算在内selectmgr,min(sal)fromempwheremgrisnotnullgroupbymgrhavingmin(sal)=800;5.查询所有部门的部门名字dname，所在位置loc，员工数量和工资平均值；selectdept.dname,dept.loc,COUNT,AVGfromdeptjoin(selectdeptno,count(*)asCOUNT,avg(sal)asAVGfromempgroupbydeptno)using(deptno);6.查询和scott相同部门的员工姓名ename和雇用日期hiredateselectename,hiredatefromempwheredeptno=(selectdeptnofromempwhereemp.ename='SCOTT');7.查询工资比公司平均工资高的所有员工的员工号empno，姓名ename和工资sal。selectempno,ename,salfromempwheresal(selectavg(sal)fromemp);8.查询和姓名中包含字母u的员工在相同部门的员工的员工号empno和姓名enameselectempno,enamefromempwheredeptnoin(selectdeptnofromempwhereenamelike'%U%');9.查询在部门的loc为newYork的部门工作的员工的员工姓名ename，部门名称dname和岗位名称jobselecte.ename,d.dname,e.jobfromempejoindeptdusing(deptno)wheredeptno=(selectdeptnofromdeptwhereloc='NEWYORK