oracle查询超过5000行报错,oracle系列教材（十七）阶段性练习2

8 个答案

inon

答案时间&＃xff1a;2020-03-01

--查询部门编号大于等于50小于等于90的部门中工资小于5000的员工的编号、部门编号和工资

select employee_id,salary,department_id from hr.employees where department_id between 50 and 90 and salary <5000;

--显示员工姓名加起来一共15个字符的员工

select last_name || &＃39; &＃39; || first_name from hr.employees where last_name || &＃39; &＃39; || first_name like&＃39;_______________&＃39;;

--显示不带有“ R ”的员工的姓名

select last_name || &＃39; &＃39; || first_name from hr.employees where last_name || &＃39; &＃39; || first_name not like &＃39;%R%&＃39;;

--查询所有员工的部门的平均工资&＃xff0c;要求显示部门编号&＃xff0c;部门名(需要多表关联查询&＃xff1a; employees, department, location)

select * from hr.departments

select avg(e.salary),d.department_id,d.department_name from hr.employees e left join hr.departments d on e.department_id &＃61; d.department_id group by d.department_id,d.department_name;

Lmy199612

答案时间&＃xff1a;2019-07-18

不是也可以用length&＃xff0c;楼主答案用的也是length函数&＃xff0c;他的

select first_name||last_name len from hr.employees

相当于

select first_name||last_name as len from hr.employees

然后length(len)&＃xff0c;一样的

ge shu

答案时间&＃xff1a;2018-09-01

--查询部门编号大于等于50小于等于90的部门中工资小于5000的员工的编号、部门编号和工资

select e.employee_id,e.department_id,e.salary from hr.employees e where department_Id between 50 and 90 and (salary <5000)

--显示员工姓名加起来一共15个字符的员工

select * from (select first_name||last_name len from hr.employees ) t where length(len) &＃61; 15

select * from hr.employees where length(first_name||last_name)&＃61; 15

--显示不带有“ R ”的员工的姓名

select first_name||last_name from hr.employees where first_name||last_name not like &＃39;%R%&＃39;

--查询所有员工的部门的平均工资&＃xff0c;要求显示部门编号&＃xff0c;部门名&＃xff0c;部门所在地(需要多表关联查询&＃xff1a; employees, departments, locations)

select avg(e.salary),e.department_id, dept.department_name,locs.street_address from hr.employees e

left join hr.departments dept

on e.department_id &＃61; dept.department_id

left join hr.locations locs

on dept.location_id &＃61; locs.location_id

group by e.department_id, dept.department_name,locs.street_address

IceBearScript

答案时间&＃xff1a;2018-04-22

因为sum,count,max,min,avg这类的函数不是单组函数&＃xff0c;他们的值是动态的&＃xff0c;必须有个范围才能约束值&＃xff0c;默认就是全局范围&＃xff0c;但是如果同时select了其他字段的话&＃xff0c;就要用group by 手动分组了&＃xff0c;不分组的假设可以运行的话&＃xff0c;函数以外的字段都会列出来&＃xff0c;然后里面一堆单个显示的重复记录&＃xff0c;然后像sum这样的函数在后面显示又有什么意义呢&＃xff0c;你一条记录就一个数据。

lixinjia_65

答案时间&＃xff1a;2018-01-20

因为爱所以爱

DaiJue

答案时间&＃xff1a;2017-11-29

出错是因为&＃xff0c;分组的时候&＃xff0c;查询字段&＃xff0c;只能是统计函数&＃xff0c;或者被分组的字段

你可以在后面加group by e.department_id,d.department_name,l.street_address

Jinwen

答案时间&＃xff1a;2017-11-25

第二个也可以使用length函数

select * from hr.employees e

where length(e.first_name||e.last_name)&＃61;15

970121ding

答案时间&＃xff1a;2017-11-13

查询部门编号大于等于50小于等于90的部门中工资小于5000的员工的编号、部门编号和工资

select e.employee_id, e.department_id,e.salary from hr.employees e where e.department_id between 50 and 90 and e.salary <5000

显示员工姓名加起来一共15个字符的员工

select * from hr.employees e where e.first_name||e.last_name like &＃39;_______________&＃39;;

显示示不带有“ R ”的员工的姓名

select * from hr.employees e where e.first_name||e.last_name not like &＃39;%R%&＃39;;

查询有员工的部门的平均工资&＃xff0c;要求显示部门编号&＃xff0c;部门名&＃xff0c;部门所在地(需要多表关联查询&＃xff1a; employees, department, location)

select avg(e.salary), e.department_id,d.department_name,l.street_address from hr.employees e

left join hr.departments d

on e.department_id &＃61; d.department_id

left join hr.locations l

on d.location_id &＃61; l.location_id

group by e.department_id ,d.department_name,l.street_address