Oracle面试题

连接用户 超级用户 conn sys 用户名 密码 as sysdba 普通用户 conn system 用户名 密码 创建表空间 CREATE TABLESPACE epet tablespace DATAFILE E app Administrator oradata orcl test DBF SIZE 100M autoextend on next 32m maxsize 2048m 删除表空间 对象及数据文件 drop tablespace study including contents and datafiles 为表空间创建用户 CREATE USER user IDENTIFIED BY password DEFAULT TABLESPACE tablespace CONNECT 临时用户 RESOURCE 更为可靠和正式的用户 DBA 数据库管理员角色 拥有管理数据库的最高权限 分配权限或角色 GRANT privileges or role TO user 撤销权限或角色 REVOKE privileges or role FROM user CONNECT 角色 是授予最终用户的典型权利 最基本的 CREATE SESSION 建立会话 RESOURCE 角色 是授予开发人员的 CREATE CLUSTER 建立聚簇 CREATE PROCEDURE 建立过程 CREATE SEQUENCE 建立序列 CREATE TABLE 建表 CREATE TRIGGER 建立触发器 CREATE TYPE 建立类型 数据查询语言 DQL Data Query Language 用于检索数据库表中存储的行 可以使用 SQL 的 SELECT 语句编写查询语句 数据操作语言 DML Data Manipulation Language 用于修改表的内容 DML 语句有 三种 分别为 Insert Update Delete 事务控制语言 TCL Transaction Control Language 用于将对行所作的修改永久性的 存储到表中 或者取消这些修改操作 TC 语句共有 3 种 Commit 永久性的 保存对行所作的修改 Rollback 取消对行所作的修改 SavePoint 设置一个 保存点 可以将对行所作的修改回滚到此处 数据定义语言 DDL Data Definition Language 用于定义构成数据库的数据结构 例 如表 DDL 语句有 5 种基本类型 分别为 Create 创建数据库结构 Alter 修改数据库结构 Drop 删除数据库结构 数据控制语言 DCL Data Control Language 用于修改数据库结构的操作权限 DCL 语 句有两种 Grant 授予其他用户对数据库结构 例如表 的访问权限 REVOKE 防治其他用户访问数据库结构 dual 是一个虚拟表 用来构成 select 的语法规则 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 dual 获得一个随机数 3 得到序列的下一个值或当前值 用下面语句 select your sequence nextval from dual 获得序列 your sequence 的下一 个值 select your sequence currval from dual 获得序列 your sequence 的当前 值 4 可以用做计算器 select 7 9 from dual 多表联查 SELECT S SName AS 姓名 CS CourseName AS 课程 C Score AS 成绩 FROM Students AS S INNER JOIN Score AS C ON S SCode C StudentID INNER JOIN Course AS CS ON CS CourseID C CourseID 优先级 1算术运算符 2连接符 3比较符 4IS NOT NULL LIKE NOT IN 5 NOT BETWEEN 6NOT 7AND 8 OR 伪列分页 SELECT FROM SELECT A ROWNUM RN FROM student A WHERE ROWNUM 0 创建索引 create unique bitmap index 索引名字 on 表名 字段 创建同义词 CREATE PUBLIC SYNONYM synonym FOR object WITH 子句 使用 WITH 子句 可以避免在 SELECT 语句中重复书写相同的语句块 WITH 子句将该子句中的语句块执行一次 并存储到用户的临时表空间中 使用 WITH 子句可以提高查询效率 1 not null 非空 如果在列上定义了 not null 那么插入数据时必须为该列提 供数据 否则插不进去 2 unique 唯一键 当定义了唯一约束以后 该列值是不能重复的 但是可以 为 null 3 primary key 主键 用于唯一的标识表行的数据 当定义主键约束后 该列 不但不能重复而且不能为 null 说明 一张表最多只能有一个主键 但是可以有多个 unique 约束 联合主键 多列联合起来作为主键 4 foreign key 外键 用于定义主表和从表之间的关系 外键约束要定义在从 表上 主表则必须有主键约束或 unique 约束 当定义外键约束后 要求外键 列数据必须在主表的主键列存在或是为 null 5 check 检查 用于强制行数据必须满足的条件 假定在 score 列上定义了 check 约束 并要求 score 列值在 0 100 之间 如果不在此区间内就提示错误 DROP CONSTRAINT 删除约束 DISABLE CONSTRAINT 无效化约束 ENABLE CONSTRAINT 激活约束 查询约束 SELECTconstraint name constraint type search condition FROM user constraints WHERE table name EMPLOYEES UNION 操作符 合并数据 INTERSECT 操作符 交集 MINUS 操作符 补集 回滚 使用 SAVEPOINT 语句在当前事务中创建保存点 使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点 视图 CREATE VIEW stud view AS SELECT studno studname subno FROM Stud details 1 查询员工表所有数据 并说明使用 的缺点 答案 select from emp 使用 的缺点有 查询出了不必要的列 效率上不如直接指定列名 2 查询职位 JOB 为 PRESIDENT 的员工的工资 答案 select from emp where 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 hiredate between to date 1981 5 1 yyyy mm dd and to date 1981 12 31 y yyy mm dd 5 查询所有名字长度为 4 的员工的员工编号 姓名 答案 select from emp where length ename 4 6 显示 10 号部门的所有经理 MANAGER 和 20 号部门的所有职员 CLE RK 的详细信息 答案 select from emp where deptno 10 and job MANAGER or de ptno 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 高的员工的详细信息 答案 select from emp where comm sal 10 把 hiredate 列看做是员工的生日 求本月过生日的员工 答案 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 mon ths sysdate 1 mm 12 求 1982 年入职的员工 答案 select from emp where to char hiredate yyyy 1982 13 求 1981 年下半年入职的员工 答案 select from emp where hiredate between to date 1981 7 1 yyyy mm dd and to date 1982 1 1 yyy y mm dd 1 14 求 1981 年各个月入职的的员工个数 答案 select count to char trunc hiredate month yyyy mm from emp where to char hiredate yyyy 1981 group by trunc hiredate month order by trunc hiredate month 15 查询各个部门的平均工资 答案 select deptno avg sal from emp group by deptno 16 显示各种职位的最低工资 答案 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 empno 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 S COTT 21 显示销售部 SALES 员工的姓名 答案 select ename from emp e dept d where e deptno d deptno and d dname SALES 22 显示与 30 号部门 MARTIN 员工工资相同的员工的姓名和工资 答案 select ename sal from emp where sal select sal from emp where deptno 30 and ename M ARTIN 23 查询所有工资高于平均工资 平均工资包括所有员工 的销售人员 SALES MAN 答案 select from emp where job SALESMAN and sal select avg sal from emp 24 显示所有职员的姓名及其所在部门的名称和工资 答案 select ename job dname from emp e dept d where e deptno d deptno 25 查询在研发部 RESEARCH 工作员工的编号 姓名 工作部门 工作所 在地 答案 select empno ename dname loc from emp e dept d where e deptno d deptno and danme RESEARCH 26 查询各个部门的名称和员工人数 答案 select from select count c deptno from emp group by deptno e inner join dept d on e deptno d deptno 27 查询各个职位员工工资大于平均工资 平均工资包括所有员工 的人数和员 工职位 答案 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 s al group by sal 1 29 查询工资最高的 3 名员工信息 答案 select from select from emp order by sal desc where rownum e sal 1 rank from emp e order byrank 31 求入职日期相同的 年月日相同 的员工 答案 select from emp e where select count from emp where e hired ate hiredate 1 32 查询每个部门的最高工资 答案 select deptno max sal maxsal from emp group by deptno order by deptno 33 查询每个部门 每种职位的最高工资 答案 select deptno job max sal from emp group by deptno job order by deptno job 34 查询每个员工的信息及工资级别 答案 select e sg grade from emp e salgrade sg where sal between losa l and hisal 35 查询工资最高的第 6 10 名员工 答案 select from select e rownum rn from select from emp order by sal desc e where rownum 5 36 查询各部门工资最高的员工信息 答案 select from emp e where e sal select max sal from emp where deptno e 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 查询平均工资最高的部门信息 答案 select d avgsal from dept d select avg sal avgsal deptno from e mp group bydeptno se where avgsal select max avg sal from emp group by deptno an d d deptno se deptno 41 查询大于各部门总工资的平均值的部门信息 答案 select d sumsal from dept d select sum sal sumsal deptno from emp group bydeptno se where sumsal select avg sum sal from emp group by deptno an d se deptno d deptno 42 查询大于各部门总工资的平均值的部门下的员工信息 答案 select e sumsal from emp e select sum sal sumsal deptno from emp group bydeptno se where sumsal select avg sum sal from emp group by deptno an d se deptno e deptno 43 查询没有员工的部门信息 答案 select d from dept d left join emp e on e deptno d deptno wh ere empno is null 44 查询当前月有多少天 答案 select trunc add months sysdate 1 month trunc sysdate month f rom dual 45 列出最低薪金大于 1500 的各种工作及此从事此工作的全部雇员人数 答案 SELECT job COUNT empno FROM emp GROUP BY job HAVING MIN sal 1500 46 列出薪金高于公司平均薪金的所有员工 所在部门 上级领导 公司的 工资等级 答案 SELECT e empno e ename d dname m ename s grade FROM emp e dept d emp m salgrade s WHERE sal SELECT AVG sal FROM emp AND e mgr m empno AN D d deptno e deptno AND e sal BETWEEN s losal AND s hisal 47 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金 部门 名称