一套SQL笔试题

1、一套SQL笔试题1、查找整个职员表的所有内容。select *from employees2、查看雇员名字(last_name)oselect last_namefrom employees3、查看雇员编号、名字和工种。select last_name, job_id , employee_idfrom employeesDEPARTMENT"显示4、显示所有雇员的姓名、工资并将为(Department_Id )。select last_name,salary,DEPARTMENT_ID as Department_Id from employees5、查找在60号部门工作的雇员。s

2、elect last_name+first_name name,department_idfrom employeeswhere departmet_id=606、要求查找职位为SH_CLERK和SA_MAN 的雇员姓名(last_name)。select last_name job_idwhere job_id in ( ' sh_clerk' ,sa_man')7、查找职位不是 SH_CLERK和SA_MAN的雇员工种及姓 名。将姓名显示为(first_name+last_name命名为" Name。)select first_name+last_name

3、 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 employees where salary not between 3000 and 500010、查找first_name以D开头，后面仅有三个字母的雇员信息。select * from emp

4、loyees where firstname like 'D ' and firstname not like 'd11、查找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_idwhere first_name like &

5、#39; m_c%l'13、查找哪些雇员的工种名不以SA开头。select job_idfrom employeeswhere job_id not like' sa%'14、查找没有奖金的雇员信息。select * where commission_pct is null15、查找有奖金的雇员信息 select * from employees where commission_pct is not null16、查找30号部门里不是 CLERK的雇员信息 select * from employees where department_id=30 and job_i

6、d 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=60and salary>5000 select last_name from employees order by last_name18、按字母顺序显示雇员的名字 (la

7、stname）。19、按部门号降序显示select * from employees order by department_id desc20、查找工资高于$2000的雇员信息，按部门号和雇员名字 排序。select * from employees where salary>2000 order bydepartment_id,employee_id21、选择奖金高于5%的雇员信息SELECT FIRST_NAME, LAST_NAME, COMMISSION_PCTFROM dbo.EMPLOYEESWHERE (COMMISSION_PCT > .05)22查询年工资高于

8、50000的员工信息 select * from employees where 12*salary>5000023查询奖金高于5000的员工姓名 day1、查由部门地区编号为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=17002、查询工作地区为北京的员工名及工资信息select

9、 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_pc

10、t,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_id4、查询由主管Raphaely管理的员工和薪水信息 a.salary,mission_pct,b.last_name from employees a,employees b where a.department_id=b.department_idselecta.lastname+a.fi

11、rstnameasname,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 fr

12、om 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 employees group by department_id8、查询每个部门的每个工种的雇员数 select count(*)num,department_id,job_id from employees group by d

13、epartment_id,job_id9、请算由employee表中总雇员数量 select count(*) from employee10 .请算由employee表中所有雇员的平均工资select avg(salary) from employee11 .请查询由employee表中的最低工资select min(salary) from employee12 .请查询由employee表中最高工资select max(salary)13、请计算由每个部门的平均工资、最高工资和最低工资 select max(salary) max,min(salary) min,avg(salary)a

14、vg,department_idfrom employeegroup by department_id14、查询按部门名称分组工资总和大于4200的部门名称、工资和select department_name,sum(salary)from employees e,departments d where e.department_id=d.department_idgroup by department_name having sum(salary)>4200 test0011.请查询由employee表中最低工资的雇员 select last_name from employee wh

15、ere salary=(select min(salary) from employee)select last_name from employeewhere salary=(select max(salary) from employee)3、查询工资高于 105号雇员的last_name,并且工种与他相 同的雇员情况。select last_name,job_id,salaryfrom employeeswhere salary>(select salary from employees where employee_id=' 105')and job_id=(se

16、lect job_id from employees where employee_id=' 105')4、查询工资高于或等于 30号部门工资最高额的雇员。select last_name,salaryfrom employeeswhere salary>=(select max(salary) from employees wheredepartment_id=30)5 查询工资在1000到5000之间的雇员所在部门的所有人 员的信息。select *from employees where department_id in(select department_id from employees wher