关于sql:Employee测试数据库语句

1、查找整个职员表的所有内容。
select *
from employees
2、查看雇员名字 (last_name)。
select last_name
from employees
3、查看雇员编号、名字和工种。
select last_name，job_id，employee_id
from employees
4、显示所有雇员的姓名、工资并将 DEPARTMENT_ID 显示为（Department_Id）。
select last_name,salary,DEPARTMENT_ID as Department_Id
from employees
5、查找在 60 号部门工作的雇员。
select last_name+first_name name,department_id
from employees
where departmet_id=60
6、要求查找职位为 SH_CLERK 和 SA_MAN 的雇员姓名(last_name)。
select last_name job_id
from employees
where job_id in (‘sh_clerk’,’sa_man’)
7、查找职位不是 SH_CLERK 和 SA_MAN 的雇员工种及姓名。将姓名显示为(first_name+last_name 命名为”Name”)。
select first_name+last_name Name, job_id
from employees
where job_id not in (‘sh_clerk’,’sa_man’)
8、查找哪些雇员的工资在 2000 到 3000 之间
select *
from employees
where salary between 2000 and 3000
9、查找哪些雇员的工资不在 3000 到 5000 之间
select *
from employees
where salary not between 3000 and 5000
10、查找 first_name 以 D 结尾，前面仅有三个字母的雇员信息。
select *
from employees
where first_name like‘D___’ and first_name not like‘d__‘
11、查找 last_name 以 K 结尾的雇员信息。
select last_name,first_name,department_id
from employees
where last_name like‘k%’
12、查找名字以字母 M 结尾，以 l 结尾，并且第三个字母为 c 的雇员名字(First_name)、工种和所在部门号
select first_name,job_id,department_id
from employees
where first_name like‘m_c%l’
13、查找哪些雇员的工种名不以 SA 结尾。
select job_id
from employees
where job_id not like ‘sa%’
14、查找没有奖金的雇员信息。
select *
from employees
where commission_pct is null
15、查找有奖金的雇员信息。
select *
from employees
where commission_pct is not null
16、查找 30 号部门里不是 CLERK 的雇员信息。
select *
from employees
where department_id=30 and job_id not like‘%clerk%’
17、查找在 30 号部门工作或不是 CLERK 的雇员信息。
select *
from employees
where department_id=30
or job_id not like‘%clerk%’
查找 60 号部门且工资大于 5000 的员工的信息
select *
from employees
where department_id=60
and salary>5000
18、按字母程序显示雇员的名字(last_name)。
select last_name
from employees
order by last_name
19、按部门号降序显示。
select * from employees order by department_id desc
20、查找工资高于 $2000 的雇员信息，按部门号和雇员名字排序。
select * from employees where salary>2000 order by department_id,employee_id
21、抉择奖金高于 5% 的雇员信息
SELECT FIRST_NAME, LAST_NAME, COMMISSION_PCT
FROM dbo.EMPLOYEES
WHERE (COMMISSION_PCT > .05)
22 查问年工资高于 50000 的员工信息
select from employees where 12salary>50000
23 查问奖金高于 5000 的员工姓名

1、查出部门地区编号为 1700 的员工姓名
select first_name,last_name,city,department.location_id
from locations,employees,department
where locations.location_id=department.location_id
and locations.location_id=1700
2、查问工作地区为北京的员工名及工资信息
select first_name,last_name,salary,commission_pct,city
from locations,employees,departments
where departments.location_id=locations.location_id
and departments.department_id = employees.department_id
and departments.location_id=1700
3、查问薪水规范为 B 类的员工名称和员工薪水以及工资类别名称
select last_name,first_name,salary,commission_pct,gra
from departments d,employees e,job_grades j
where e.salary between j.lowest and j.highest
and j.gra=’b’
and d.department_id=e.department_id
4、查问出主管 Raphaely 治理的员工和薪水信息
select a.last_name+a.first_name as name, a.salary,a.commission_pct,b.last_name
from employees a,employees b
where a.department_id=b.department_id
and a.last_name like‘%raphaely%’
5、查出雇员所在的部门，并将没有雇员的部门的记录也显示进去。
select e.last_name+e.first_name as name,d.department_id
from departments d
left outer join employees e
on (e.department_id=d.department_id)
6、查问出没有调配部门的员工信息
select e.last_name+e.first_name as name,e.department_id
from departments d
left outer join employees e
on (e.department_id=d.department_id)
where d.department_id is null
7、计算每个部门的平均工资和工资总和
select department_id,sum (salary) sum,avg (salary) avg
from employees
group by department_id
8、查问每个部门的每个工种的雇员数
select count(*)num,department_id,job_id
from employees
group by department_id,job_id
9、请算出 employee 表中总雇员数量
select count(*)
from employee
10. 请算出 employee 表中所有雇员的平均工资
select avg(salary)
from employee
11. 请查问出 employee 表中的最低工资
select min(salary)
from employee
12. 请查问出 employee 表中最高工资
select max(salary)
from employee
13、请计算出每个部门的平均工资、最高工资和最低工资
select max(salary) max,min(salary) min,avg(salary) avg,department_id
from employee
group by department_id
14、查问按部门名称分组工资总和大于 4200 的部门名称、工资和
select department_name,sum(salary)
from employees e,departments d
where e.department_id=d.department_id
group by department_name
having sum(salary)>4200
test001

1. 请查问出 employee 表中最低工资的雇员
select last_name
from employee
where salary=(select min(salary) from employee)
2. 请查问出 employee 表中最高工资的雇员
select last_name
from employee
where salary=(select max(salary) from employee)
3、查问工资高于 105 号雇员的 last_name，并且工种与他雷同的雇员状况。
select last_name,job_id,salary
from employees
where salary>(select salary from employees where employee_id=’105′)
and job_id=(select job_id from employees where employee_id=’105′)
4、查问工资高于或等于 30 号部门工资最高额的雇员。
select last_name,salary
from employees
where salary>=(select max(salary) from employees where department_id=30)
5、查问工资在 1000 到 5000 之间的雇员所在部门的所有人员的信息。
select *
from employees
where department_id in
(select department_id from employees where salary between 1000 and 5000)
6、查找工资高于 60 号部门所有员工的人员信息。显示其员工编号，last_name 和工资。
select last_name,employee_id,salary
from employees
where salary>
(select max(salary) from employees where department_id=60)
7、将 114 号雇员的工种和部门号改为 102 号雇员的工种和部门号。
8、将所有与 106 号雇员雷同工种的职工的部门号改成 106 号雇员所在的部门。
9、查问工种不为 SH_CLERK，并且工资小于其中任何一个 SH_CLERK 的雇员信息。