计算机求职面试题

计算机方向有点用哦

SQL

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 *

from employees

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’

and d.department_id=e.department_id

4、查询出主管Raphaely管理的员工和薪水信息

select http://doc.guandang.netst_name+a.first_name as name,

a.salary,http://doc.guandang.netmission_pct,http://doc.guandang.netst_name

from employees a,employees b

where a.department_id=b.department_id

and http://doc.guandang.netst_name like ‘%raphaely%’

5、查出雇员所在的部门，并将没有雇员的部门的记录也显示出来。

select http://doc.guandang.netst_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 http://doc.guandang.netst_name+e.first_name as name,e.department_id

from departments d

left outer join employees e

on (e.department_id=d.department_id)

where d.department_id is null

7、计算每个部门的平均工资和工资总和

select department_id,sum (salary) sum,avg (salary) avg

from employees

group by department_id

8、查询每个部门的每个工种的雇员数

select count(*)num,department_id,job_id

from employees

group by department_id,job_id

9、请算出employee表中总雇员数量

select count(*)

from employee

10.请算出employee表中所有雇员的平均工资

计算机方向有点用哦

select avg(salary)

from employee

11.请查询出employee表中的最低工资

select min(salary)

from employee

12.请查询出employee表中最高工资

select max(salary)

from employee

13、请计算出每个部门的平均工资、最高工资和最低工资

select max(salary) max,min(salary) min,avg(salary) avg,department_id from employee

group by department_id

14、查询按部门名称分组工资总和大于4200的部门名称、工资和

select department_name,sum(salary)

from employees e,departments d

where e.department_id=d.department_id

group by department_name< …… 此处隐藏：13900字，全部文档内容请下载后查看。喜欢就下载吧 ……