数据库笔试题.docx

建表：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 = CLERK 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 部门号,ename 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中工作为CLERK的员工的姓名，工作，部门号，部门名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的员工名与工作select 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.deptno12 对于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 部门号,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) from emp as b where b.sala.sal) as 人数 from emp as awhere (select count(b.ename) from emp as b where b.sal5一个表里面，查成绩及格的男生数和女生数selectcount（sex）asfemale，count（sex）asmalefrom表名groupbysex selectsexas性别,count(sex)as人数groupbysex表中有语文、数学、英语字段，没有总成绩字段。查总成绩前三名 1、查找整个职员表的所有内容。select * from employees2、查看雇员名字(last_name)。select last_namefrom employees3、查看雇员编号、名字和工种。select 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_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 salary 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 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_pct 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的员工的信息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,employee_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,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=locations.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_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_idfrom 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,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表中的最低工资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,departments 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 employee)3、查询工资高于105号雇员的last_name，并且工种与他相同的雇员情况。select last_name,job_id,salaryfrom em