将员工的姓名按首字母排序,并写出姓名的长度(length) select length(last_name) as name'len, substr(last_name,1,1) as shouzifu, last_name from employee order by shouzifu; 做一个查询有以下结果 dream salary <last_name> earns <salary> monthly but wants earns <salary*3> select concat(last_nam...
查询工作岗位是 SA_REP 或者工作岗位是 AD_PRES 并且薪水大于 15000 的员工姓名、工作 ID 以及薪水。 select last_name,job_id,salary from employees where job_id = 'SA_REP' or job_id='AD_PRES' and salary > 15000 1. 2. 2.6.2 示例二 查询工作岗位是 SA_REP 或者是 AD_PRES 并且他们的薪水大...
1. 查询工资最低的员工信息: last_name, salary 首先对子查询类型进行判断,此题需要查询工资最低的员工,因此需要增加筛选条件,故使用where子查询。 SELECT last_name,salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees ); 1. 2. 3. 4. 5. 6. 2. 查询平均工资最低的部门信息 这个...
1.查询每个工种有奖金的员工的最高工资 select max(salary),job_id from employees where commission_pct is not null group by job_id 1. 2. 3. 4. 删选分组结果中>12000 select max(salary),job_id from employees where commission_pct is not null group by job_id having max(salary)>12000; 1. ...
MAX(salary), job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000 ; 1. 2. 3. 4. 5. 6. 7. 8. 查询领导编号大于102的每个领导下的最低工资>5000的领导编号,以及其最低工资。 ①查询每个领导下面的员工的最低工资 ...
②查询员工的信息,满足 salary>①结果 SELECT * FROM employees WHERE salary>( SELECT salary FROM employees WHERE last_name = 'Abel' ); 1. 2. 3. 4. 5. 6. 7. 8. 9. 案例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id 和工资 ①查询141号员工的job_id SELECT job_id...
案例六 查询每个工种有奖金的员工的最高工资 而且最高工资大于12000的工种编号和最高工资 SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000; 1. 2. 3. 4. 5. 小结 分组查询中的筛选条件分为2类 ...