SQL笔试题2
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
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 *
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 12*salary>50000
23 查询奖金高于5000的员工姓名
day
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’