Oracle-SQL 小题

it2022-05-06  5

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

1 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+:高富帅)

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

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

 

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

1 select e.ename,e.job,e.sal, 2 decode(e.job, 3 'SALESMAN',1.1500, 4 'CLERK',1.25, 5 'ANALYST',1.50, 6 1.00 7 )*e.sal as new_sal 8 from scott.emp e 9 order by 2 ;

①decode 函数的用法

 

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

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

注意:通用函数

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

 

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

1 select d.deptno as 部门编号 ,d.dname as 部门名称 ,min(e.sal) as 最低工资 2 from scott.dept d ,scott.emp e 3 where e.deptno = d.deptno 4 group by d.deptno ,d.dname 5 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语句如下:

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

 

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

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

 

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

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

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

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

 

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

 

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

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

注意:

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

1 select avg(e.comm) 2 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.查询雇员编号,雇员姓名,部门名称,部门所在城市、部门所在国家,部门所在大陆

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

 

转载于:https://www.cnblogs.com/jiaxinwei/p/10251882.html

相关资源:数据结构—成绩单生成器

最新回复(0)