内容发布更新时间 : 2024/9/28 21:12:54星期一 下面是文章的全部内容请认真阅读。

一套SQL笔试题

from employees4、显示所有雇员的姓名、工资并将DEPARTMENT_ID

显示为（Department_Id）。select

as

Department_Idfrom

last_name,salary,DEPARTMENT_ID

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

1

employeeswhere last_name like ‘k%’12、查找名字以字母M开头，以l结尾，并且第三个字母为c的雇员名字(First_name)、工种和所在部门号select first_name,job_id,department_idfrom employeeswhere first_name like ‘m_c%l’13、查找哪些雇员的工种名不以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

salary>500018、按字母顺序显示雇员的名字(last_name)。select last_namefrom employeesorder by last_name19、按部门号降序显示。select * from employees order by department_id desc20、查找工资高于$2000的雇员信息，按部门号和雇员名字排序。select *

from

employees

where

salary>2000

order

by

department_id,employee_id21、选择奖金高于5%的雇员信息SELECT FIRST_NAME, LAST_NAME, COMMISSION_PCTFROM dbo.EMPLOYEESWHERE (COMMISSION_PCT > .05)22 查询年

2

工资高于50000的员工信息select * from employees where 12*salary>5000023 查询奖金高于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类的员工名称和员工薪水以及工资类别名称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=’b’and d.department_id=e.department_id4、查询出主管Raphaely管理的员工和薪水信息select a.last_name+a.first_name as name, a.salary,a.commission_pct,b.last_namefrom

employees

select

a,employees bwhere a.department_id=b.department_idand a.last_name like ‘%raphaely%’5、查出雇员所在的部门，并将

3