2023年Oracle面试题.pdf

《2023年Oracle面试题.pdf》由会员分享，可在线阅读，更多相关《2023年Oracle面试题.pdf（18页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、连接用户：超 级 用 户：conn sys用户名/密码as sysdba普通用户：conn system用户名/密码创建表空间：CREATE TABLESPACE epet_tablespaceDATAFILE E:appAdministratororadataorcltest.DBFSIZE 100Mautoextend on next 32mmaxsize 2048m删除表空间、对象及数据文献：drop tablespace study including contents and datafiles;为表空间创建用户CREATE USER userIDENTIFIED BY passwo

2、rdDEFAULT TABLESPACE tablespaceCONNECT:临时用户RESOURCE:更为可靠和正式的用户DBA:数据库管理员角色，拥有管理数据库的最高权限#分派权限或角色GRANT privileges or role TO user;#撤消权限或角色REVOKE privileges or role FROM user;CONNECT角色：一是授予最终用户的典型权利，最基本的CREATE SESSION 一建立会话RESOURCE角 色：一是授予开发人员的CREATE CLUSTER 一建立聚簇CREATE PROCEDURE 一建立过程CREATE SEQUENCE 一

3、建立序歹(JCREATE TABLE-建表CREATE TRIGGER 一建立触发器CREATE TYPE 一建立类型数据查询语言(DQL:Data Query Language)用于检索数据库表中存储的行。可以使用SQL的SELECT语句编写查询语句。数据操作语言(DML:Data Manipulation Language)用于修改表的内容。DML语句有三种,分别为 Insert,Update,Deleteo事务控制语言(TCL:Transaction Control Language)用于将对行所作的修改永久性的存储到表中，或者取消这些修改操作。TC语句共有3种：Commit永久性的保存

4、对行所作的修改。Rollback取消对行所作的修改。SavePoint设立一个 保存点 ，可以将对行所作的修改回滚到此处。数据定义语言(DDL:Data Definition Language)用于定义构成数据库的数据结构，例如表。DDL语句有5种基本类型：分别为Create创建数据库结构。Alter修改数据库结构。Drop删除数据库结构。数据控制语言(DCL:Data Control Language)用于修改数据库结构的操作权限。DCL语句有两种：Grant授予其他用户对数据库结构(例如表)的访问权限。REVOKE防治其他用户访问数据库结构dual是一个虚拟表，用来构成select的语法规

5、则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，如 下：1、查看当前用户，可以在SQL Plus中执行下面语句select user from dual;2、用来调用系统函数select to_char(sysdate,yyyy-mm-dd hh24:mi:ss)from dual;-获得当前系统时间select SYS_CONTEXT(USERENV,TERMINAL)from dual;-获得主机名select dbms_random.random from du al;-获得一个随机数3、得到序列的下一个值或当前值，用下面语句select your_seque

6、nce.nextval from dual;-获得序列 your_sequence 的下个值select your_sequence.currval from dual;一获得序歹Iyour_sequence 的当前值4、可以用做计算器select 7*9 from dual;多表联查SELECTS.SName AS 姓名，CS.CourseName AS 课程,C.Score AS 成绩FROM Students AS SINNER JOIN Score AS C ON(S.SCode=C.StudentID)INNER JOIN Course AS CS ON(CS.CourselD=C.

7、CourselD)优先级1算术运算符 2连接符3 比较符4 IS NOT NULL,LIKE,NOT IN5 NOT BETWEEN6 NOT 7AND8OR伪 列 分 页：SELECT*FROM(SELECT A.*,ROWNUM RNFROM student A WHERE ROWNUM 0创建索引：create unique/bitmap index 索弓I名字 on 表名(字段)创建同义词：CREATE PUBLIC SYNONYM synonym FOR object;WITH子句使 用WITH子句，可 以 避 免 在SELECT语句中反复书写相同的语句块WITH子句将该子句中的语句

8、块执行一次并存储到用户的临时表空间中使 用WITH子句可以提高查询效率l.not null非 空【假如在列上定义了 not null,那么插入数据时必须为该列提供数 据，否则插不进去。】2.unique唯一键【当定义了唯一约束以后，该列值是不能反复的，但是可认为null3.primary key主 键【用于唯一的标记表行的数据，当定义主键约束后，该列不仅不能反复并且不能为null说 明：一张表最多只能有一个主键，但是可以有多个unique约束。联合主键：多列联合起来作为主键。】4.foreign key外 键【用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须有主键约束或uniq

9、ue约束，当定义外键约束后，规定外键列数据必须在主表的主键列存在或是为null5.check检 查【用于强制行数据必须满足的条件，假定在score列上定义了check约束，并规定score歹I 值在0-100之间，假如不在此区间内就提醒错误。】DROP CONSTRAINT 删除约束DISABLE CONSTRAINT 无效化约束ENABLE CONSTRAINT 激活约束查询约束 SELECT constraint_name,constraint_type,search_conditionFROM user_constraintsWHERE table_name=EMPLOYEES;UNIO

10、N操 作 符：合并数据INTERSECT操 作 符：交集MINUS操 作 符：补集回 滚：使 用SAVEPOINT语句在当前事务中创建保存点。使 用ROLLBACK TO SAVEPOINT语句回滚到创建的保存点视 图：CREATE V正W stud_viewAS SELECT studno,studname,subnoFROM Stud_detai Is;1.查询员工表所有数据，并说明使用*的缺陷答 案：select*from emp;使用*的缺陷有：查询出了不必要的列；效率上不如直接指定列名。2.查询职位(JOB)为PRESIDENT的员工的工资答 案：select*from emp wh

11、ere job=PRESIDENT;3.查询佣金(COMM)为0或 为NULL的员工信息答 案：select*from emp where comm=0 or comm is null;4.查询入职日期在1981-5-1到1981-12-31之间的所有员工信息彦 宝select*from emp where hiredatebetween to_date(1981-5-l,yyyy-mm-dd)and to_date(1981-12-31,yyyy-mm-dd)；5.查询所有名字长度为4的员工的员工编号，姓名生 室select*from emp where length(ename)=4;6.

12、显示1 0号部门的所有经理(MANAGER)和20号部门的所有职工(CLERK)的具体信息林口 塞select*from emp where deptno=10 and job=MANAGER or deptno=20 and job=CLERK;7.显示姓名中没有L字的员工的具体信息或具有SM字的员工信息生 室select*from emp where ename not like%L%or ename like%SM%,;8.显示各个部门经理(MANAGER)的工资林 塞select sal from emp where job=MANAGER;9.显示佣金(COMM)收入比工资(SAL)

13、高的员工的具体信息答 案：select*from emp where comm sal;10.把hiredate列看做是员工的生日，求本月过生日的员工分室1=1 木select*from emp where to_char(hiredate,mm)=to_char(sysdate,mm)；11.把hiredate列看做是员工的生日，求下月过生日的员工生口 室select*from emp where to_char(hiredate,mm)=to_char(add_months(sysdate,l),mm)；12.求1982年入职的员工林 塞select*from emp where to_c

14、har(hiredate,yyyy)=1982;13.求1981年下半年入职的员工圣 宗1=1 select*from emp where hiredatebetween to_date(1981-7-l,yyyy-mm-dcl)and to_date(1982-l-l,yyyy-mm-dd)-1;14.求1981年各个月入职的的员工个数林口 塞select count(*),to_char(trunc(hiredate,month),yyyy-mm)from emp where to_char(hiredate,yyyy)=1981group by trunc(hiredate,month)

15、order by trunc(hiredate,month)；15.查询各个部门的平均工资分室1=1 木select deptno,avg(sal)from emp group by deptno;16.显示各种职位的最低工资圣 宗1=1 select job,min(sal)from emp group by job;17.按照入职日期由新到旧排列员工信息较 宏select*from emp order by hiredate desc;18.查询员工的基本信息，附加其上级的姓名宏 奈select e.*,e2.ename from emp e,emp e2 where e.mgr=e2.e

16、mpno;19.显示工资比ALLEN高的所有员工的姓名和工资生口 室select*from emp where sal (select sal from emp where ename=ALLEN);20.显示与SCOTT从事相同工作的员工的具体信息林 塞select*from emp where job=(select*from emp where ename=SCOTT);21.显示销售部(SALES)员工的姓名冬 宓U卡select ename from emp e,dept d where e.deptno=d.deptno andd.dname=SALES;22.显示与30号部门MA

17、RTIN员工工资相同的员工的姓名和工资左 宝U木select ename,sal from empwhere sal=(select sal from emp where deptno=30 and ename=MARTIN);23.查询所有工资高于平均工资(平均工资涉及所有员工)的销售人员(SALESMAN)林口 塞select*from emp where job=SALESMAN and sal (select avg(sal)from emp);24.显示所有职工的姓名及其所在部门的名称和工资生口 室select ename,job,dname from emp e,dept d wh

18、ere e.deptno=d.deptno;25.查询在研发部(RESEARCH)工作员工的编号，姓名，工作部门，工作所在地宏 奈select empno,ename,dname,loc from emp e,dept dwhere e.deptno=d.deptno and danme=RESEARCH;26.查询各个部门的名称和员工人数答 案：select*from(select count(*)c,deptno from emp group by deptno)einner join dept d on e.deptno=d.deptno;27.查询各个职位员工工资大于平均工资(平均工资

19、涉及所有员工)的人数和员工职位答 案：select job,count(*)from emp where sal (select avg(sal)from emp)group by job;28.查询工资相同的员工的工资和姓名答 案：select*from emp e where(select count(*)from emp where sal=e.sal group by sal)1;29.查询工资最高的3名员工信息答 案：select*from(select*from emp order by sal desc)where rownum e.sal)+l rank fromemp e o

20、rder byrank;31.求入职日期相同的(年月日相同)的员工分室1=1 木select*from emp e where(select count(*)from emp where e.hiredate=hiredate)l;32.查询每个部门的最高工资生 室select deptno,max(sal)maxsal from emp group by deptno order bydeptno;33.查询每个部门，每种职位的最高工资林口 塞select deptno,job,max(sal)from emp group by deptno,job orderby deptno,job;3

21、4.查询每个员工的信息及工资级别生口 室select e.*,sg.grade from emp e,salgrade sg where sal between losaI and hisal;35.查询工资最高的第6-10名员工林 塞select*from(select e.*,rownum rn from(select*from emp order by sal desc)ewhere rownum 5;36.查询各部门工资最高的员工信息圣 宝1=1 木select*from emp e where e.sal=(select max(sal)from emp where(deptno=e

22、.deptno);37.查询每个部门工资最高的前2名员工彦 宏select*from emp e where(select count(*)from emp where sal e.sal and e.deptno=deptno)2;39.查询所有大于本部门平均工资的员工信息彦 宝select*from emp e where sal(select avg(sal)from emp where(deptno=e.deptno)order by deptno;40.查询平均工资最高的部门信息冬U卡宓select d.*,avgsal from dept d,(select avg(sal)avg

23、sal,deptno from emp group bydeptno)sewhere avgsal=(select max(avg(sal)from emp group by deptno)and d.deptno=se.deptno;41.查询大于各部门总工资的平均值的部门信息生口 室select d.*,sumsal from dept d,(select sum(sal)sumsal,deptno fromemp group bydeptno)sewhere sumsal(select avg(sum(sal)from emp group by deptno)and se.deptno=

24、d.deptno;42.查询大于各部门总工资的平均值的部门下的员工信息生口 奈select e.*,sumsal from emp e,(select sum(sal)sumsal,deptno fromemp group bydeptno)sewhere sumsal(select avg(sum(sal)from emp group by deptno)and se.deptno=e.deptno;43.查询没有员工的部门信息林口 塞select d.*from dept d left join emp e on(e.deptno=d.deptno)where empno is null;

25、44.查询当前月有多少天答 案：select trunc(add_months(sysdate,l),month)-trunc(sysdate,month)from dual;45.列出最低薪金大于1500的各种工作及此从事此工作的所有雇员人数答 案：SELECT job,COUNT(empno)FROM empGROUP BY job HAVING MIN(sal)1500;46.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级答 案：SELECT e.empno,e.ename,d.dname,m.ename,s.gradeFROM emp e,dept d,emp

26、m,salgrade sWHERE sal(SELECT AVG(sal)FROM emp)AND e.mgr=m.empno AND d.deptno=e.deptno(+)AND e.sal BETWEEN s.losal AND s.hisal;47.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称答 案：SELECT e.ename,e.sal,d.dname FROM emp e,dept dWHERE sal ALL(SELECT sal FROM emp WHERE deptno=30)ANDe.deptno=d.deptno;48.歹I 出所有部门的具体信息

27、和部门人数答 案：SELECT d.dname,d.loc,dt.countFROM dept d,(SELECT deptno,COUNT(*)count FROM emp GROUP BY deptno)dtWHERE d.deptno=dt.deptno;49.显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000,输出结果按月工资的合计升序排列答 案：SELECT job,SUM(sal)sumFROM empWHERE joboSALESMANGROUP BY job HAVING sum5000ORDER BY sum;50

28、.客户表a(id name address)登陆流水表b(id time)购物流水表c(id time productid productnum)1.求每个客户的最新登陆时间time,姓名name,客户id?答 案：select a.id,a.name,d.time as timefrom a left join(select id,max(time)as time from b group by id)don a.id=d.id;2.查最新登陆并且已经购买商品的客户id,name,登陆的时间time（一条sql语句）答 案：select a.id,a.name,d.time as timefrom a,(select id,max(time)as time from b group by id)dwhere a.id=d.idand exists(select*from c where id=a.id);