Oracle习题及答案

1、一. 基于自己创建表的操作1：创建一张学生表student,拥有stuid,stuname,sex,三个字段，英中stuid为主键。create table student(stuid int primary key,stuname VARCHAR(2O)Zsex VARCHAR(20)2：为该表增加一个新列score6alter table student add(score varchar(lO);3：修改该表score列名为stuscoresalter table student rename column score to stuscoree;4：为student表插入5条记录。ins

2、ert into student values(l/张三丰男：80);insert into student values(2/阿悄,，女70);insert into student values(3/陈龙,，男90);insert into student values(4/章子怡，女50);Iinsert into student values(5/张卫健：男60);5：查询student表中的全部数据，其中列名为中文。select STUID as 学号,STUNAME as 姓名 力EX as 性另山STUSCOREE as 分数 from student;6：查询学生姓名和分数，并

3、是査询结果按照学生成绩降序排列。select STUNAME,STUSCOREE from student order by STUSCOREE desc;7：修改所有性别为“男”的学生信息为性别为“male”。update student set SEXmale where SEX=男,;&删除所有记录。delete from student;9：删除 student 表。drop table student;二、基于emp表的操作1：创建一张新表empl,和emp表结构和记录定全一样。create table empl as select*from ;基于empl表的操作：1：选择部门30

4、中的雇员。select*from empl where DEPTNO=30 and JOB=CLERK,;2：列出所有办事员(CLERK)的姓名、编号和部门。select ENAME,EMPNOQEPTNO from empl where JOB=,CLERK,;3：找出佣金高(comm)于薪金的雇员。select*from empl where COMM SAL and JOB二CLERK;4：找岀佣金高于嶄金60%的庭员。select*from empl where COMM* SAL and JOB=,CLERK1;5：找岀部门10中所有经理和部门20中所有办事员的详细资料。select

5、*from empl where DEPTNO=10 and JOB=MANAGER union select*from empl where JOB=,SALESMAN,;6：找出部门10中所有经理和部门20中所有办事员以及既不是经理又不是办事员但苴崭金 大于或等于2000的所有雇员的详细资料。select*from empl where DEPTNO=10 and JOB=MANAGER union select*from empl where JOB=,SALESMAN1 union select*from empl where JOB infCLERK) and SAL=2000;7：

6、找出收取佣金的雇员的不同工作。select distinct JOB from empl where job=CLERK,;&找出不收取佣金或收取佣金低于100的雇员。select *from empl where COMM is null or nvl(COMM,0)12;3：显示只有首字母大写的所有雇员的姓名select UPPER( SUBSTR(ename/l,l) | | L0WER(SUBSTR(ename2LENGTH(enamG)-l) FROM emp where JOB=,CLERK,;4：显示正好为15个字符的雇员姓需select deptno,ename from em

7、p where length(ename)=15 and JOB=,CLERK;5：显示不带有R的雇员姓名select ename from emp where ename not like ,%R%,and JOB=,CLERK;6：显示所有雇员的姓名的前3个字符select substr(ename,l.,3) from emp where JOB=,CLERK;7：显示所有庭员的姓名，用a替换所有的A(select replace(ename,ATa)from emp;&显示所有雇员的姓名以及满10年服务年限的日期select enameiredateddmonths(hiredatez

8、10*12) from emp;9：显示雇员的详细资料，按姓名排序select * from emp order by ename;10：显示雇员姓名，根据其服务年限，将最老的雇员排在最前面select emp no 斥 nam 巳 job,mgr;hiredat 已 sal ,comm,dept no (sysdate hiredatG)/B65 from emp order by (sysdate-hiredate)/365 desc;11：显示所有雇员的姓名、工作和嶄金，按工作内的工作的降序顺序排序，而工作按嶄金排 序select ename job,sal from emp order

9、 by job desczsal asc;12：显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，并将最早年 份的项目排在最前而select enam已to_char(hiredat巳YYYY) yearlochahiredate/MON1) month!, from emp order by yearl asc,monthl;13：显示在一个月为30天的情况下所有雇员的日薪金，忽略小数select round(sal/30z0) as daypay from emp;14：找岀在(任何年份的)2月份受庭的所有屣:员select * from emp where hiredat

10、e like %-2 月 %;15：对于每个雇员，显示其加入公司的天数 select round(sysdate-hiredate) from emp;16：显示姓名字段的任何位置.包含 卞 的所有雇员的姓名select ename from emp where ename like *%A%;17：以年、月和日显示所有雇员的服务年限IselectenameAonumberftocharfsysdate/YYYYJJ-to _nu mbetocharfhiredate/YYYYJJ-l/to _n umber(t o_char(sysdate/mm,)-to_number(to_char(hi

11、redate/,mm)+10,to_number(to_char(sysdate,dcT)to_number(to_char(hirGdat巳dd)+30 from emp;18:列出至少有一个雇员的所有部门select dept no from emp group by dept no havi ng coun t(deptno)0;19：列岀嶄金比SMITH”多的所有雇员SELECT enameal, deptno FROM emp WHERE sal(SELECT sal FROM emp WHERE ename=,SMITH)；20：列出所有雇员的姓名及其部门需select ENAME

12、,DEPTNO from emp where JOBCLERK;21：列出所有入职日期早于其直接上级的所有雇员SELECT as 职 匚 as 经理 FROM emp e left join emp m on 二 where 1500;23：列出嶄金高于公司平均水平的所有雇员select enamez deptno from emp where sal(select avg(sal) from emp);24：列出与“SCOTT”从事相同工作的所有雇员select deptno,ename from emp where job=(select job from emp where ename=

13、lSCOTT,)；25:列出薪金高于在部门30工作的所有雇员的嶄金SELECT ename,sal from emp where sal all(select sal from emp where deptno=30);26：列岀嶄金高于在部门30工作的最髙的嶄金SELECT enamezsal from emp where salall(select max(sal) from emp where dept no=30);27：列出每个部门雇员的数量select dept no，countC) from emp group by dept no;28:列出所有雇员的名称，部门名称和薪金sel

14、ect ENAME,DEPTNO,SAL from emp where JOBCLERK1;29：列出从事同一种工作但不属于同一部门的这些员工select job,dept no from emp group by jobzdeptno;30：列岀个类别工作的最低工资select job.minfsal) from emp group by job;/31：列岀各个部门的经理的最低薪金select DEPTNO/min(sal) from emp where job=MANAGER group by DEPTNO;32：列出按计算的字段排序的所有雇员的年薪select ename,(sal+n

15、vl(comm,0)*1.2 as yearpay from emp order by yearpay desc;33:列岀所有CLERK的姓名及英部门爼称select ENAMEZDEPTNO from ep where JOB=,CLERK,;34：列岀嶄金水平处于前四位的雇员select sal斥name from emp order by sal desc;35:求出所有员工入职了多少年零多少月零多少天selectempno/ename/to_char(floor(to_number(sysdate-hiredate)/365)| |years| |to_char(ceil(month

16、s_between(sysdate,hiredate)-(floor(to_number(sysdate-hiredate)/365)*l 2)| I months from emp;作业要求：将SQL语句复制粘贴在相对应的题目下而。1：找岀各月最后一天受雇的所有雇员select * from emp where hiredate=last_day(hiredate);2：找岀早于12年之前受雇的雇员select*fromempwhereto_number(to_char(sysdat 已yyyy)to _n umber(to_char(hiredate/yyyy,)12;3：显示只有首字母大

17、写的所有雇员的姓名SELECT initcap(ename) FROM emp;SELECT ename from emp where ename=initcap(ename);4：显示正好为15个字符的雇员姓名select ENAME from emp where length(ENAME)=15;5：显示不带有R的雇员姓名select ENAME from emp where ENAME not like %R%;6：显示所有雇员的姓需的前3个字符select substr(ENAMEQ3) from emp;7：显示所有雇员的姓名，用匕替换所有的Aselect replacefENAME

18、/A/a) from emp;(&显示所有雇员的姓轻以及满10年服务年限的日期select ename, add_months(hiredatez120) from emp;9：显示雇员的详细资料，按姓名排序select * from emp order by ename;10：显示廉员姓名，根据其服务年限，将最老的雇员排在最前面select ename from emp order by hiredate;11:显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序排序，而工作按薪金排 序select enamejobal from emp order by job desc, sal d

19、esc;12：显示所有雇员的姓划和加入公司的年份和月份，按雇员受雇日所在月排序，并将最早年 份的项目排在最前而select ename, to_char(hiredat巳yyyy-mm) as 加入年 月 11 from emp order by tochahiredate/mm1) a sc, to_-char(hiredate/yyyy,) asc;13：显示在一个月为30天的情况下所有雇员的日薪金，忽略小数select round(sal/30,0) from emp;14：找岀在(任何年份的)2月份受雇的所有雇员select * from emp where to_char(hired

20、at巳 mnV)二02；$15：对于每个雇员，显示貝加入公司的天数select ename,round(sysdate-hiredatezO)加入公司天数” from emp;16：显示姓名字段的任何位置，包含A的所有雇员的姓爼select ename from emp where ename like %A%；17：以年、月和日显示所有雇员的服务年限selecten amehiredateru nc(mon ths_between(sysdatezhiredate)/12)年,tru nc(mod(m on ths_betwee n(sysdat 巳 hiredate)2)月,truncfs

21、ysdate-addmonthsfhiredateonthsbetween(sysdat巳hiredate) I I from emp;I18:列出至少有一个雇员的所有部门select dept no，count(0) from emp group by dept no havi ng count(0)=l19：列出薪金比“SMITH”多的所有雇员select ename from emp where sal (select sal from emp where ename = SMITH1)；select e2.* from emp el, emp e2 where =SMITH,and ;

22、20：列出所有雇员的姓需及其部门名select, from emp e,dept d where =;select, from emp e join dept d on 二；21：列出所有入职日期早于其直接上级的所有雇员select * from emp el where hiredate1500;23：列出嶄金高于公司平均水平的所有雇员select * from emp where sal(select avg(sal) from emp);24：列岀与“SCOTT”从事相同工作的所有雇员select * from emp where job=(select job from emp whe

23、re ename=SCOTT,);25:列岀薪金高于在部门30工作的所有雇员的崭金 select en amez sal from emp where sal(select max(sal) from emp where dept no=30);select ename, sal from emp where salall(select sal from emp where deptno=30);26：列出嶄金高于在部门30工作的最高的嶄金select en ame, sal from emp where sal(select max(sal) from emp where dept no=30);27：列岀每个部门雇员的数量select d.*, (select count(deptno) from emp e where =雇员人数” from dept d;28:列出所有雇员的名称，部门名称和薪金select ename,(select dname from dept d wher