Oracle-SQL 小题

1.查询姓名中不包含C和c的员工信息

select * from employees where instr(first_name||last_name,'C')=0 or instr(first_name||last_name,'c')=0;

①字符函数 instr(input,char,m,n) 的用法：
返回在字符值中查找字符串char的数字位置。参数m作为查找的开始，参数n代表第n次发现。m和n的默认值是1，即默认从开始位置查找，并且报告第一个发现的位置。如果在字符值中未查找到char，则返回0；
②逻辑条件 or 使用注意：

or 两侧应该是两个条件而不是两个值。

 

2.查询员工姓名和工资等级 （0-1999：屌丝，2000-3999：白领，4000+：高富帅）

select first_name||' '||last_name as "Name" , 
       case when salary between 0 and 1999 then '屌丝'
            when salary between 2000 and 3999 then '白领'
            when salary >= 4000 then '高富帅'
       end as "工资阶级"
       from employees;

①case...when...then 函数的使用

 

3.按工种调节工资(SALESMAN 0.15 CLERK 0.25 ANALYST 0.5)打印员工信息和调节前后的工资

select e.ename,e.job,e.sal,
       decode(e.job,
       'SALESMAN',1.1500,
       'CLERK',1.25,
       'ANALYST',1.50,
       1.00
       )*e.sal as new_sal
from scott.emp e
order by 2 ;

①decode 函数的用法

 

4.查询所有员工信息(提成无的显示0)

select e.first_name||' '||e.last_name as "姓名" , e.salary as "工资" ,
       e.commission_pct as "佣金比例" , nvl(e.commission_pct,0)*salary as "提成"
from hr.employees e
order by 2 desc ;

注意：通用函数

①nvl(expr,value1) 如果第一个参数不为空，则函数返回第一个参数值，否则返回第二个；
②nvl2(expr,value1,value2) 如果第一个参数不为空，则函数返回第二个参数值，否则返回第三个 ；
③coalesce(expr1,expr2,...,exprn) 如果expr1为非空，则返回expr1 的值；如果expr1 为空，则返回expr2的值，依次类推，如果前面的表达式都为空，则返回exprn 的值 。

 

5.查询部门最低工资高于20号部门最低工资的部门的编号、名称及部门最低工资

select d.deptno as 部门编号 ,d.dname as 部门名称 ,min(e.sal) as 最低工资
from scott.dept d ,scott.emp e
where e.deptno = d.deptno
group by d.deptno ,d.dname
having min(e.sal) > (select min(e.sal) from scott.emp e where e.deptno=20) ;

语句也要按照d.dname 分组 ,这样select 子句才能有 d.dname；

注意：

①在使用 GROUP BY 进行分组的 SQL 语句中，WHERE 子句先写出约束条件过滤行，而 HAVING 子句是分组后，再写出约束条件过滤组。本题用的是 HAVING 子句。
②GROUP BY 子句置于 WHERE 子句后、ORDER BY 子句前，后面的表达式必须包含字段，且要指明是那一个表的字段，但不能使用列的别名（as 后的定义名）。

在SELECT 子句中，只可以有组函数和分组字段(包括对分组字段的操作)，如果包括其他字段会报错。
另外，如果GROUP BY 子句后要根由ORDER BY 子句，则ORDER BY 子句用于排序的字段必须是分组字段或组函数。

例子：假设想获得每个部门最高的薪水值，并按照各部门最高薪水值显示出部门编号和薪水，其SQL语句如下：

select e.department_id,max(e.salary) 
from hr.employees e
group by e.department_id 
order by max(salary) desc;

 

6.查询员工工资为其部门最低工资的员工的编号和姓名及工资

select e.empno as 员工编号 ,e.ename as 员工姓名 ,e.sal 员工工资,e.deptno as 员工部门
from scott.emp e
where e.sal in (select min(e.sal) from scott.emp e group by e.deptno)

 

7.查询所有工作种类数量

select count(*) as 种类数
from 
( select distinct e.job from scott.emp e )

而这个代码是干什么的，思考一下

select count(e.job) as 种类数
from scott.emp e
group by e.job

　

此代码：把整张表按照工作不同分组，然后查询每一组的工作的数量。

 例题：有订单表orders，包含字段用户信息userid，字段产品信息productid，返回至少被订购过两次的productid？

select productid from orders group by productid having count（productid）>1

 

8.查询所有员工平均奖金

select sum(e.comm)/count(nvl(e.mgr,0))
from scott.emp e

注意：

所有组函数对空值都是省略的，若按照所有的来算，可以用空值处理函数。
组函数有 MIN、MAX、AVG、SUM、COUNT、STDDEV、VARIANCE
如果，查询有奖金的员工的平均奖金

select avg(e.comm)
from scott.emp e

 

9.每个部门的总工资

select e.deptno as 部门编号,d.dname as 部门名称 , sum(e.sal)as 部门总工资 
from scott.emp e,scott.dept d
where e.deptno = d.deptno
group by e.deptno,d.dname

注意：

在使用 GROUP BY 进行分组时，在 SELECT 子句中，只可以有组函数和分组字段，如果包括其他字段会报错。在写代码过程中，没有对 d.dname 分组时，程序报错。所以需得对d.dname 编号 才能查询这个字段。

 

 10.查询各工种平均工资的最大值和最小值

select max(sal) as 最大值, min(sal) as 最小值
from (select avg(e.sal) as sal from scott.emp e group by e.job)

 

11.查询雇员编号，雇员姓名，部门名称，部门所在城市、部门所在国家，部门所在大陆

select e.employee_id ,e.first_name||' '||e.last_name as 名字 ,d.department_name as 部门名称 ,l.city as 部门所在城市,
       c.country_name as 部门所在国家, r.region_name as 部门所在大陆 
from hr.employees e , hr.departments d , hr.locations l , hr.countries c ,hr.regions r
where e.department_id = d.department_id(+) and d.location_id = l.location_id(+) 
      and l.country_id = c.country_id(+) and c.region_id = r.region_id(+) 
order by e.employee_id ;