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 ;
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 ;
最新文章
- mvc 对一个或者多个实体验证失败
- el表达式的function标签
- Ubuntu的which、whereis、locate和find命令
- 使用FileSystemWatcher监视文件变化
- JS 操作 HTML 自定义属性
- C# Lazy<;T>;(转)
- URL中#(井号)的作用(转)
- JBoss7官方最新版下载地址
- 注解式Schedule配置定时任务
- 剑指Offer--图的操作
- easyui时间框只选择年月
- CF987B - High School: Become Human
- js 设备判断(移动端pc端 安卓ios 微信)
- thymeleaf拆分头部(head)显示异常问题
- error2019-01-17 宏STDOUT_FILENO
- 5、python的变量和常量
- day02 Python列表的增删查改及常用操作
- options.html:1 Refused to load the script &#39;xxxx&#39; because it violates the following Content Security Policy directive: ";script-src &#39;self&#39; blob: filesystem: chrome-extension-resource:";.
- PAT甲题题解-1028. List Sorting (25)-水排序
- 新版POI如何获取日期类型的cell的值