/1:查询没有上级领导的员工的编号,姓名,工资/
select e.employee_id,e.first_name,e.salary from employees e where e.manager_id is null
/2:查询emp表中没有奖金的员工的姓名,职位,工资,以及奖金/
select e.first_name,e.job_id,e.salary ,e.commission_pct from employees e
where e.commission_pct is null or e.commission_pct = 0
/3:查询emp表中含有奖金的员工的编号,姓名,职位,以及奖金/
select e.first_name,e.job_id,e.salary ,e.commission_pct from employees e
where e.commission_pct is not null or e.commission_pct <> 0
/4:查询含有上级领导的员工的姓名,工资以及上级领导的编号/
select e.first_name,e.salary,e.manager_id from employees e
where e.manager_id is not null
/5:查询emp表中名字以‘S’开头的所有员工的姓名/
select CONCAT(first_name,last_name) from employees
where CONCAT(first_name,last_name) like ‘s%’
/6:查询emp表中名字的最后一个字符是’S’的员工的姓名/
select CONCAT(first_name,last_name) from employees
where CONCAT(first_name,last_name) like ‘%s’
/7:查询倒数的第2个字符是‘E’的员工的姓名/
select CONCAT(first_name,last_name) from employees
where CONCAT(first_name,last_name) like ‘%e_’
/8:查询emp表中员工的倒数第3个字符是‘N’的员工姓名/
select CONCAT(first_name,last_name) from employees
where CONCAT(first_name,last_name) like ‘%n__’
/9:查询emp表中员工的名字中包含‘A’的员工的姓名/
select CONCAT(first_name,last_name) from employees
where CONCAT(first_name,last_name) like ‘%A%’
/10:查询emp表中名字不是以’K’开头的员工的所有信息/
select CONCAT(first_name,last_name) from employees
where CONCAT(first_name,last_name) not like ‘K%’
/11:查询emp表中名字中不包含‘A’的所有员工的信息/
select CONCAT(first_name,last_name) from employees
where CONCAT(first_name,last_name) not like ‘%A%’
/12:查询文员的员工人数量是多少(job= CLERK 的)/
select count(1) from employees where job_id=‘AD_VP’
/13:销售人员 job: AD_VP 的最高薪水/
select max(salary) from employees where job_id=‘AD_VP’
/14.最早和最晚入职时间/
select MIN(hiredate) ,max(hiredate)from employees
/15:查询emp表中员工的编号,姓名,职位, 工资,并且工资在10000~20000之间。/
select employee_id,first_name,job_id,salary from employees
where salary >= 10000 and salary <= 20000
/16:查询emp表中员工在20号部门,并且含有上级领导的员工的姓名,职位,
上级领导编号以及所属部门的编号/
select e.first_name,e.job_id,e.manager_id,e.department_id from employees e
where e.department_id=20 and e.manager_id is not null
/17:查询emp表中名字中包含’E’,并且职位不是AD_VP的员工的编号,姓名,职位,以及工资/
select CONCAT(e.first_name,e.last_name),e.department_id,e.job_id,e.salary from employees e
where CONCAT(e.first_name,e.last_name) like ‘%e%’ and e.job_id <> ‘AD_VP’
/18.查询emp表中10号部门或者20号部门中员工的编号,姓名,所属部门的编号/
select e.department_id,CONCAT(e.first_name,e.last_name),e.department_id from employees e
where e.department_id in (10,20)
/19.查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号,姓名,职位以及奖金/
select e.department_id,CONCAT(e.first_name,e.last_name),e.job_id,e.commission_pct from employees e
where CONCAT(e.first_name,e.last_name) not like ‘%t_’ or e.commission_pct is null
or e.commission_pct = 0
/20:查询工资高于3000或者部门编号是30的员工的姓名,职位,工资,入职时间以及所属部门的编号/
select CONCAT(e.first_name,e.last_name),job_id,salary,e.hiredate,e.department_id from employees e
where e.salary>3000 or e.department_id=30
/21:查询不是30号部门的员工的所有信息/
select * from employees where department_id <> 30 or department_id is null
/22:查询奖金不为空的员工的所有