1、建表：dept:deptno(primary key),dname,locemp:empno(primary key),ename,job,mgr,sal,deptno1 列出 emp 表中各部门的部门号，最高工资，最低工资select max(sal) as 最高工资,min(sal) as 最低工资,deptno from emp group by deptno;2 列出 emp 表中各部门 job 为CLERK的员工的最低工资，最高工资select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp where job = C
2、LERK group by deptno;3 对于 emp 中最低工资小于 1000 的部门，列出 job 为CLERK的员工的部门号，最低工资，最高工资select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp as bwhere job=CLERK and 1000(select min(sal) from emp as a where a.deptno=b.deptno) group by b.deptno4 根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资select deptno as 部门号,ena
3、me as 姓名,sal as 工资 from emp order by deptno desc,sal asc5 写出对上题的另一解决方法（请补充）6 列出张三所在部门中每个员工的姓名与部门号select ename,deptno from emp where deptno = (select deptno from emp where ename = 张三)7 列出每个员工的姓名，工作，部门号，部门名select ename,job,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno8 列出 emp 中工作为CL
4、ERK的员工的姓名，工作，部门号，部门名select ename,job,dept.deptno,dname from emp,dept where dept.deptno=emp.deptno and job=CLERK9 对于 emp 中有管理者的员工，列出姓名，管理者姓名（管理者外键为 mgr）select a.ename as 姓名,b.ename as 管理者 from emp as a,emp as b where a.mgr is not null and a.mgr=b.empno10 对于 dept 表中，列出所有部门名，部门号，同时列出各部门工作为CLERK的员工名与工作s
5、elect dname as 部门名,dept.deptno as 部门号,ename as 员工名,job as 工作 from dept,empwhere dept.deptno *= emp.deptno and job = CLERK11 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资 from emp as awhere a.sal(select avg(sal) from emp as b where a.deptno=b.deptno) order by a.d
6、eptno12 对于 emp，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序select count(a.sal) as 员工数,a.deptno as 部门号 from emp as awhere a.sal(select avg(sal) from emp as b where a.deptno=b.deptno) group by a.deptno order by a.deptno13 对于 emp 中工资高于本部门平均水平，人数多与 1 人的，列出部门号，人数，按部门号排序select count(a.empno) as 员工数,a.deptno as 部门号
7、,avg(sal) as 平均工资 from emp as awhere (select count(c.empno) from emp as c where c.deptno=a.deptno and c.sal(select avg(sal) from emp as b where c.deptno=b.deptno)1group by a.deptno order by a.deptno14 对于 emp 中低于自己工资至少 5 人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数select a.deptno,a.ename,a.sal,(select count(b.ename
8、) from emp as b where b.sal5一个表里面，查成绩及格的男生数和女生数select count（sex） as female，count （sex） as male from 表名 group by sex select sex as 性别, count(sex) as 人数 group by sex表中有语文、数学、英语字段，没有总成绩字段。查总成绩前三名 1、查找整个职员表的所有内容。select * from employees2、查看雇员名字(last_name)。select last_namefrom employees3、查看雇员编号、名字和工种。sele
9、ct last_name，job_id，employee_idfrom employees4、显示所有雇员的姓名、工资并将 DEPARTMENT_ID 显示为（Department_Id）。select last_name,salary,DEPARTMENT_ID as Department_Idfrom employees5、查找在 60 号部门工作的雇员。select last_name+first_name name,department_idfrom employeeswhere departmet_id=606、要求查找职位为 SH_CLERK 和 SA_MAN 的雇员姓名(last
10、_name)。select last_name job_idfrom employeeswhere job_id in (sh_clerk,sa_man)7、查找职位不是 SH_CLERK 和 SA_MAN 的雇员工种及姓名。将姓名显示为(first_name+last_name 命名为”Name”)。select first_name+last_name Name, job_idfrom employeeswhere job_id not in (sh_clerk,sa_man)8、查找哪些雇员的工资在 2000 到 3000 之间select *from employeeswhere sa
11、lary between 2000 and 30009、查找哪些雇员的工资不在 3000 到 5000 之间select *from employeeswhere salary not between 3000 and 500010、查找 first_name 以 D 开头，后面仅有三个字母的雇员信息。select *from employeeswhere first_name like D_ and first_name not like d_ 11、查找 last_name 以 K 开头的雇员信息。select last_name,first_name,department_idfrom
12、employeeswhere last_name like k%12、查找名字以字母 M 开头，以 l 结尾，并且第三个字母为 c 的雇员名字(First_name)、工种和所在部门号select first_name,job_id,department_idfrom employeeswhere first_name like m_c%l13、查找哪些雇员的工种名不以 SA 开头。select job_idfrom employeeswhere job_id not like sa%14、查找没有奖金的雇员信息。select *from employeeswhere commission_p
13、ct is null15、查找有奖金的雇员信息。select *from employeeswhere commission_pct is not null16、查找 30 号部门里不是 CLERK 的雇员信息。select *from employeeswhere department_id=30 and job_id not like %clerk%17、查找在 30 号部门工作或不是 CLERK 的雇员信息。select *from employeeswhere department_id=30or job_id not like %clerk%查找 60 号部门且工资大于 5000 的
14、员工的信息select *from employeeswhere department_id=60and salary500018、按字母顺序显示雇员的名字(last_name)。select last_namefrom employeesorder by last_name19、按部门号降序显示。select * from employees order by department_id desc20、查找工资高于$2000 的雇员信息，按部门号和雇员名字排序。select * from employees where salary2000 order by department_id,em
15、ployee_id21、选择奖金高于 5%的雇员信息SELECT FIRST_NAME, LAST_NAME, COMMISSION_PCTFROM dbo.EMPLOYEESWHERE (COMMISSION_PCT .05)22 查询年工资高于 50000 的员工信息select * from employees where 12*salary5000023 查询奖金高于 5000 的员工姓名select last_name from employees where commission_pct=5000;day1、查出部门地区编号为 1700 的员工姓名select first_name
16、,last_name,city,department.location_idfrom locations,employees,departmentwhere locations.location_id=department.location_idand locations.location_id=17002、查询工作地区为北京的员工名及工资信息select first_name,last_name,salary,commission_pct,cityfrom locations,employees,departmentswhere departments.location_id=locatio
17、ns.location_idand departments.department_id = employees.department_idand departments.location_id=17003、查询薪水标准为 B 类的员工名称和员工薪水以及工资类别名称select last_name,first_name,salary,commission_pct,grafrom departments d,employees e,job_grades jwhere e.salary between j.lowest and j.highestand j.gra=band d.department
18、_id=e.department_id4、查询出主管 Raphaely 管理的员工和薪水信息select a.last_name+a.first_name as name, a.salary,mission_pct,b.last_namefrom employees a,employees bwhere a.department_id=b.department_idand a.last_name like %raphaely%5、查出雇员所在的部门，并将没有雇员的部门的记录也显示出来。select e.last_name+e.first_name as name,d.department_id
19、from departments dleft outer join employees eon (e.department_id=d.department_id)6、查询出没有分配部门的员工信息select e.last_name+e.first_name as name,e.department_idfrom departments dleft outer join employees eon (e.department_id=d.department_id)where d.department_id is null7、计算每个部门的平均工资和工资总和select department_id
20、,sum (salary) sum,avg (salary) avgfrom employeesgroup by department_id8、查询每个部门的每个工种的雇员数select count(*)num,department_id,job_idfrom employeesgroup by department_id,job_id9、请算出 employee 表中总雇员数量select count(*)from employee10.请算出 employee 表中所有雇员的平均工资select avg(salary)from employee11.请查询出 employee 表中的最低工
21、资select min(salary)from employee12.请查询出 employee 表中最高工资select max(salary)from employee13、请计算出每个部门的平均工资、最高工资和最低工资select max(salary) max,min(salary) min,avg(salary) avg,department_idfrom employeegroup by department_id14、查询按部门名称分组工资总和大于 4200 的部门名称、工资和select department_name,sum(salary)from employees e,d
22、epartments dwhere e.department_id=d.department_idgroup by department_namehaving sum(salary)4200test0011.请查询出 employee 表中最低工资的雇员select last_namefrom employeewhere salary=(select min(salary) from employee)2.请查询出 employee 表中最高工资的雇员select last_namefrom employeewhere salary=(select max(salary) from emplo
23、yee)3、查询工资高于 105 号雇员的 last_name，并且工种与他相同的雇员情况。select last_name,job_id,salaryfrom employeeswhere 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,salaryfrom employeeswhere salary=(selec
24、t max(salary) from employees where department_id=30)5 查询工资在 1000 到 5000 之间的雇员所在部门的所有人员的信息。select *from employeeswhere department_id in(select department_id from employees where salary between 1000 and 5000)6 查找工资高于 60 号部门所有员工的人员信息。显示其员工编号，last_name 和工资。select last_name,employee_id,salaryfrom employeeswhere salary(select max(salary) from employees where department_id=60)9、查询工种不为 SH_CLERK，并且工资小于其中任何一个 SH_CLERK 的雇员信息。select * from employmeeswhere job_id SH_CLERK and salary(select min(salary) from employees where job_id=SH_CLERK);
