尚硅谷——企业SQL面试复习与测试.pdf-资料库

————————————————————————————— “玩转”Java 系列题目：企业 SQL 面试复习与测试一、 SQL 复习 1.常见的数据库对象有哪些？表(table) 视图(view) 序列(sequence) 索引(index) 同义词(synonym) 存储过程(procedure) 存储函数(function) 触发器(trigger) 2.表：数据的主要存储方式，由行和列组成。后面重点说视图：存储起来的 select 语句。对视图中数据的 DML 操作，会导致创建视图使用的表中的数据的修改。 create view emp_vu as select department_id,avg(salary) dept_avg_sal from employees group by department_id; --with read only select * from emp_vu; 序列：提供了一系列有规律的数值，常用来作为表的主键的值 create sequence emp_id_seq start with 1001 increment by 1 maxvalue 10000 --minvalue --cycle/nocycle --cache/nocache 1)nextval / currval select emp_id_seq.currval from dual; select emp_id_seq.nextval from dual; create table emp( id number(10), name varchar2(15) ) insert into emp values(emp_id_seq.nextval,'BB'); 1

“玩转”Java 系列 ————————————————————————————— select * from emp; 裂缝：①多个表共用一个序列②出现回滚③出现异常索引(index):当使用索引作用的列作为查询条件进行查询时，可以提高查询的效率。 --如何创建索引：①自动创建（声明为主键或唯一性约束的列） ②手动创建 create index emp_sal on employees(salary); 3.重点：表 DDL:CREATE TABLE;ALTER TABLE;TRUNCATE TABLE;DROP TABLE;RENAME .. TO .. 不可回滚，即意味着：自动提交 --1.创建表 --1.1“白手起家” create table dept( dept_id number(10), dept_name varchar2(15), location_id varchar2(10), birth Date ) select * from dept; --1.2 基于现有的表，创建 create table emp1 as select employee_id id,last_name name,hire_date,salary from employees --where department_id = 80; where 1=2; select * from emp1; --1）对现有的表的复制/空表 create table emp_copy as select * from employees --where 1=2; select * from emp_copy; 2

“玩转”Java 系列 ————————————————————————————— --2.修改表 --2.1 增加一个列 ALTER table emp add(salary number(10,2) default 2000); select * from emp; --2.2 修改现有的列 alter table emp modify(salary number(15,2)); insert into emp(id,name) values(1004,'CC'); --2.3 重命名现有的列 alter table emp rename column salary to sal; --2.4 删除现有的列 alter table emp drop column sal; --3.重命名现有的表 rename emp to employee; select * from employee; --4.清空表 truncate table employee; rollback; --5.删除表 drop table employee; DML:增、删、改、查 --增 insert into ... --1.一条一条的添加 select * from dept; insert into dept 3

“玩转”Java 系列 ————————————————————————————— values(,,,); insert into dept(dept_id,location_id,dept_name) values(,,); --2.导入数据 insert into dept(dept_id,location_id,dept_name) select department_id,location_id,department_name from departments; alter table dept modify(dept_name varchar2(20)); --删 delete from dept where dept_id < 40; --改 update dept set location_id = 1700 where dept_id = 20; commit; --查询（重中之重） select .... --分组函数（count / max / min / avg / sum） from ...,....--多表连接 where ...--过滤条件和多表的连接条件（若不写，会出现笛卡尔积的错误） group by ...--凡是在 select 中出新了分组函数，则没有使用分组函数的列要作为 group by 的条件 having avg(..) ...--分组函数作为过滤条件，要使用 having order by ... asc/desc; 二、企业 SQL 考核真题数据表： dept: deptno(primary key), dname, loc emp: empno(primary key), ename, job, mgr(references emp(empno)), sal, deptno(references dept(deptno)) 4

“玩转”Java 系列 ————————————————————————————— EMP empno(primary key) ename job mgr(references emp(empno)) sal deptno(references dept(deptno)) DEPT deptno(primary key) dname loc 1 列出 emp 表中各部门的部门号，最高工资，最低工资 select max(sal) as 最高工资,min(sal) as 最低工资,deptno from emp group by deptno; 2 列出 emp 表中各部门 job 含’REP’的员工的部门号，最低工资，最高工资 select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp where job like '%REP%' group by deptno; 3 对于 emp 中最低工资小于 7000 的部门中 job 为'SA_REP'的员工的部门号，最低工资，最高工资 select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp b where job='SA_REP' and 7000>(select min(sal) from emp a where a.deptno=b.deptno) group by b.deptno 4 写出对上题的另一解决方法（请补充） select deptno,min(sal),max(sal) from emp where job = 'SA_REP' and deptno in ( select deptno from emp --group by deptno having min(sal) < 7000 ) group by deptno 5 根据部门号由高而低，工资由低而高列出每个员工的姓名，部门号，工资 select deptno as 部门号,ename as 姓名,sal as 工资 from emp order by deptno desc,sal asc 6 列出'Abel'所在部门中每个员工的姓名与部门号 select ename,deptno from emp where deptno = (select deptno from emp where ename = 'Abel') 5

————————————————————————————— “玩转”Java 系列 (法二) select ename,deptno from emp e1 where exists ( select 'x' from emp e2 where e1.deptno = e2.deptno and e2.ename = 'Abel' ) 7 列出每个员工的姓名，工作，部门号，部门名 select ename,job,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno 8 列出 emp 中工作为'SH_CLERK'的员工的姓名，工作，部门号，部门名 select ename,job,dept.deptno,dname from emp,dept where dept.deptno=emp.deptno and job='SH_CLERK' 9 对于 emp 中有管理者的员工，列出姓名，管理者姓名（管理者外键为 mgr） select a.ename as 姓名,b.ename as 管理者 from emp a,emp b where a.mgr is not null and a.mgr=b.empno 10 对于 dept 表中，列出所有部门名，部门号，同时列出各部门工作为'SH_CLERK' 的员工名与工作 select dname as 部门名,dept.deptno as 部门号,ename as 员工名,job as 工作 from dept,emp where dept.deptno = emp.deptno(+) and job = 'SH_CLERK' 11 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序 select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资 from emp a where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno) order by a.deptno (法二)select e.deptno,ename,sal from emp e,(select deptno,avg(sal) avg_sal from emp group by deptno) b where e.sal > b.avg_sal and e.deptno = b.deptno 6

“玩转”Java 系列 ————————————————————————————— 12 对于 emp，列出各个部门中工资高于本部门平均水平的员工数和部门号，按部门号排序 select count(a.sal) as 员工数,a.deptno 部门号 from emp a where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno) group by a.deptno order by a.deptno 13. 对于 emp 中工资高于本部门平均水平，人数多于 1 人的，列出部门号，高于部门平均工资的人数，按部门号排序 select * from( select deptno,count(*) count_num from emp e where sal > ( select avg(sal) from emp e1 where e.deptno = e1.deptno ) group by deptno ) e1 where e1.count_num > 1 order by e1.deptno 14 对于 emp 中工资高于本部门平均水平，且其人数多于 3 人的，列出部门号，部门人数，按部门号排序 select count(a.empno) as 员工数,a.deptno as 部门号,avg(sal) as 平均工资 from emp a where (select count(c.empno) from emp c where c.deptno=a.deptno and c.sal>(select avg(sal) from emp b where c.deptno=b.deptno))>3 group by a.deptno order by a.deptno (法二) select m.deptno,count(ee1.empno) from( select e1.deptno,count(empno) count_num from emp e1 where e1.sal > (select avg(sal) from emp e2 where e1.deptno = e2.deptno) group by e1.deptno ) m,emp ee1 7

————————————————————————————— where m.count_num > 3 and m.deptno = ee1.deptno “玩转”Java 系列 group by m.deptno 15 对于 emp 中低于自己工资至少 5 人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数 select a.deptno,a.ename,a.sal,(select count(b.ename) from emp as b where b.sal5 8

资料库

尚硅谷——企业SQL面试复习与测试.pdf

相关推荐

数据库

热门标签

最新资料