以下是今天oracle sql 课堂中做的一些小练习,希望会对同学们有点帮助!
(注:sql语句查询结果均以上课时例题表为基础,如表中数据变化,查询结果会有所不同)
两表没有任何关联时会产生迪卡尔机:
select first_name , name from s_emp , s_dept;
等值连接:
练习一:查看员工的姓名和员工部门号:(要考虑到表中实际数据中空值的影响)
select first_name , name from s_emp, s_dept where s_emp.dept_id=s_dept.id;
练习二:每个员工所在的部门和部门所在的地区
select first_name , name from s_emp, s_dept, s_region where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id;
非等值连接:
练习三:查出每个员工和每个员工的工资级别
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal between b.losal and b.hisal;
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal>=b.losal and a.sal<=b.hisal;
自连接:
select first_name , manager_id from s_emp;
练习四:查出所有员工的部门领导的名称:( 这种sql会少一条记录,总经理没有被配置上)
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id;
外连接:(防止空值时,用(+)的一方会模拟一条记录配置另一方)这就称为外连接,一个记录都不能少;
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id(+);
练习五:查看员工分部的部门:
select distinct(deptno) from emp ;
找出没有员工的部门:(很经典的一个例子,用外连接来解决的标准做法,这是一种方式,用子查询也可以实现)
第一步:
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno;
第二步:
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno and e.deptno is null;
练习六:查询员工有多少人有提成:
select count( commission_pct ) from s_emp ;
select sum(commission_pct)/ count(*) from s_emp;
练习七:员工分部在多少个不同的部门:
select count(dept_id) from s_emp;
select count(distinct dept_id) from s_emp;
练习八:求各个部门的平均工资:
select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ;
select dept_id , avg(salary) aa from s_emp group by dept_id ;
//体会下句sql
select region_id , count(*) from s_dept ****此句会有错,请多体会********
select max(region_id) , count(*) from s_dept; (强制语法上可以正确,但是不能保证结果也会正确)
练习九:求各个部门不同工种的平均工资:
select dept_id , title, avg(salary) from s_emp group by dept_id , title ;
练习十:查询哪些部门的平均工资比2000高:
select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)>2000;
练习十一:除了42部门以外的部门的平均工资:
select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id!=42;
select dept_id , avg(salary) from s_emp where dept_id!=42 group by (dept_id ) ;(此种sql效率要高,先过滤再计算)
练习十二:求各个部门的平均工资:
//****这问题很经典,为了过 oracle sql 语法关而写max(d.name) ***
select max(d.name) , avg(e.salary) , max(r.name) from s_emp e, s_dept d , s_region r where e.dept_id = d.id and d.region_id=r.id group by d.id ;
关于子查询: Subqueries
练习十三:找出所有员工中,工资最低的那个员工:( 利用子查询 )
select first_name, salary from s_emp where salary = ( select min(salary) from s_emp) ;
//这样写会出错姓名和工资不一致
select max(first_name), min(salary) from s_emp;
练习十四:查询谁跟Smith的工种一样:
select last_name from s_emp where last_name='Smith';
//下种写法可能还存在bug,没有考虑到数据的全面性,有潜在性问题
select last_name , title from s_emp where title =( select title from s_emp where last_name='Smith' ) and last_name <> 'Smith' ;
//这种写法才考虑的比较全面
select last_name , title from s_emp where title in ( select title from s_emp where last_name='Smith' ) and last_name <> 'Smith' ;
使用子查询时应注意: 单行子查询返回多个结果时会有错误 single-row subquery returns more than one value
练习十五:查出哪些员工的工资比平均工资低:
select * from s_emp where salary < ( select avg(salary) from s_emp) ;
哪些部门的平均工资比32部门的平均工资要低:
第一步先查出各个部门的平均工资:
select min(avg(salary ) ) from s_emp group by dept_id;
第二步再查出哪个部门的工资是最低的:
select dept_id, avg(salary) from s_emp group by dept_id having avg(salary) = (select min(avg(salary) ) from s_emp group by dept_id ) ;
练习十六:哪个部门里没有员工(用子查询的方式来实现):
select deptno from dept where deptno not in ( select deptno from emp );