sql练习3283.doc

sqlsql 练习练习 328332831.从 emp 表中查询出员工的编号，名字，职位，薪水，奖金2.从 emp 表中查询出员工的编号，名字，职位，月薪，奖金，年薪3.从 emp 表中查询出员工的编号，名字，职位，月薪，奖金，年薪(包含奖金) 并且将这个年薪定义一个别名 annual_sal4.查询语句中去点重复记录的语法 注意 distinct 是去掉查询的所有字段内容都一致的记录5.查询出薪水大于 1500 的人的编号，名字，职位，月薪，奖金，年薪6.查询出薪水大于等于 2000 小于等于 3000 并且职位不等于ANALYST 的人的编号，名字，职位，月薪，奖金，年薪7.查询出入职日期 28-9 月 -81 之后入职并且奖金不为空的人的编号，名字，职位，月薪，奖金，年薪8.查询编号为 7369，7782，7902 这三个人的信息(两种写法)9.查询编号不为 7369，7782，7902 的人的信息(两种写法)10.查询出名字中包含 A 的人的信息11.查询出倒数第三个字符为 Y 的人的信息12.将 emp 表中所有人的信息按照薪水的降序进行排列13.查询出薪水在 1000 到 4000 之间的人的信息 并且按照入职时间的先后进行显示14.查询出薪水在 1000 到 4000 并且名字中包含 A 并且奖金为空的人的信息 并且按照薪水的降序进行排列15.查询出名字中包含 a 的人信息16.查询出名字前两个字符为 al 的人的信息(注意：两种写法 一种是 like 一种是 substr 建议 如果能使用 substr 的条件就不要使用 like)17.求出入职日期大于 1981-09-01 的人的信息 要求显示的入职日期要与中国人习惯接受的形式显示 并且按照入职日期的先后顺序排列 (两种方法 第一种 to_char 第二种 to_date)18.查询出工资在公司的平均工资以上的人的信息select * from emp where sal>(select avg(sal) from emp)19.求出部门编号为 10 的所有员工的平均工资20.查询出平均薪水大于 2000 的部门的部门编号，平均薪水，该部门最高薪水21.查询语句语法：select 要显示的查询的字段列表(字段与字段间用,隔开) from 表名 where 过滤的条件(条件与条件用 and 或者 or 连接) group by 分组的字段 having 分组限制的条件order by 字段 desc 或者是 asc；where 过滤条件中不可以使用组函数 而 having 字句可以使用组函数 用 group 语句时注意查询列表的内容 (显示的字段要么出现在组函数中 要么出现在 group by 的字句中)22.查询出工资大于 1200，并且入职日期在 1981-09-09 以后的平均薪水大于 2000 的部门的平均工资及部门编号 并且将其结果按照平均工资进行降序的排列select deptno,avg(sal) from emp where sal>1200 and hiredate>to_date('1981-09-09','YYYY-MM-DD') group by deptno having avg(sal)>2000 order by avg(sal) desc;23.查询出公司所有人里面工资在部门编号为 30 最高工资之上的人信息select * from emp where sal>(select max(sal) from emp where deptno=30);24.查询出工资大于公司的平均工资并且入职日期大于 1981-02-16，并且名字中包含 a 的人的编号，名称，月薪，年薪 并且按照年薪进行降序排列select empno,ename,sal,(sal+nvl(comm,0))*12 annual_sal from emp where sal>(select avg(sal) from emp) and hiredate> to_date('1981-02-16','YYYY-MM-DD') and lower(ename) like '%a%' order by annual_sal desc25.公司里面薪水最高的人的编号，名称，工作，入职日期，年薪select empno,ename,job,to_char(hiredate,'YYYY-MM-DD'),(sal+nvl(comm,0))*12 annual_sal from emp where sal=(select max(sal) from emp)26.求平均薪水大于 1600 的部门里面大于 1200 的部门平均薪水，并且按照平均薪水的降序进行排序select avg(sal) from emp where sal>1200 and deptno in(select deptno from emp group by deptno having avg(sal)>1600) group by deptno27.求出每个人的编号，名称，工资，入职日期，部门编号，部门名称及部门所在地28.查询工资大于平均工资，且名字中不包含 a 的，并且入职日期大于 1982-07-07 的员工的编号，名称，月薪，年薪，部门编号，部门名称，部门所在地 并且按照薪水降序进行排列select empno,ename,sal,e.deptno,dname,loc from emp e,dept d where e.deptno=d.deptno and sal>(select avg(sal) from emp) and lower(ename) not like '%a%' and hiredate>to_date('1982-07-07','YYYY-MM-DD')order by sal desc;29.求出每个人的名字，工资，职位，编号，以及对应的直接领导的名字，工资，职位，编号信息select e1.empno,e1.ename,e1.sal,e1.mgr,e2.empno,e2.ename,e2.sal from emp e1,emp e2where e1.mgr=e2.empno;30.求每个人的工资属于哪个级别select empno,ename,sal,grade from emp e,salgrade swhere e.sal between s.losal and s.hisal;31.查出名字里面第二个字母不是 A 的人的信息以及所在的部门情况以及工资的等级情况select ename,empno,sal,e.deptno,dname,loc,grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and lower(ename) not like '_a%';32.每个人和对应的经理人的信息（包含 king）33.求出每个部门平均薪水等级34.求平均薪水最高的部门编号(1)求出每个部门的平均薪水(2)平均薪水最高的值(3)根据最高值求出对应的部门编号35.求平均薪水最高的部门名称(1)求出每个部门的平均薪水(2)平均薪水最高的值(3)根据最高值求出对应的部门编号(4)根据部门编号到 dept 表中求出部门名称select dname from dept where deptno=(select deptno from (select deptno,avg(sal) avg_sal from emp group by deptno) a where avg_sal=(select max(avg_sal) from (select deptno,avg(sal) avg_sal from emp group by deptno)))36.求平均薪水等级最低的部门部门名称(1)部门的平均薪水等级(2)最低的部门的平均薪水等级值(3)根据薪水最低值求对应的部门编号(4)根据编号求该部门的名称select dname from deptwhere deptno=(select deptno from (select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from empgroup by deptno) a join salgrade son a.avg_sal between s.losal and s.hisal)where grade=(select min(grade) from (select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from empgroup by deptno) a join salgrade son a.avg_sal between s.losal and s.hisal)))37.薪水最高的前 5 个人38.*按薪水从高到低排列的第 6 个到第 10 个人的信息select * from (select rownum r,a.* from (select ename,empno,sal from emp order by sal desc) a) bwhere b.r between 6 and 10注意：rownum 伪字段的使用 当 rownum 和 order by 一起使用时，会首先选出符合 rownum 条件的记录，然后再排序 oracle 进行分页的原理39.查询出名字中包含 a 的，并且薪水大于 1200，并且入职日期大于 1979-01-03 的人里面薪水由高到低顺序排名的 3 到 7 人的信息select * from (select rownum r,a.* from (select * from empwhere lower(ename) like '%a%' and sal>1200and hiredate>to_date('1979-01-03','YYYY-MM-DD')order by sal desc ) a) where r between 3 and 5视图：创建视图 需要赋权给 scott 用户 grant create table,create view to scott; create view v$_dept_avg_sal_info as select deptno,grade,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);索引：索引是为了加快对数据的搜索速度而设立索引是要占空间的，而且索引太多会引起插入数据、修改数据、删除数据时速度的降低当我们在字段上添加主键或唯一约束时，oracle 会自动创建一个与之对应的约束条件创建索引 create index index-name on tablename(col,col)删除索引 drop index index－name序列是 oracle 专有的对象，它用来产生一个自动递增的数列创建语法： create sequence myse increment by 1 --增长度 MINVALUE 1 MAXVALUE 999999999999999999999999999start with 1 --从哪里增加，就是说下一个获取的值从这个值开始 nomaxvalue --不设置最大值 对应的：maxvalue 30、 order 。