首页 > 安全资讯 >

数据库面试题

17-08-07

数据库面试题。1、查找最晚入职员工的所有信息;2、查找入职员工时间排名倒数第三的员工所有信息;3、查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号dept _no;4、查找所有已经分配部门的员工的last _name和first _name

1、查找最晚入职员工的所有信息

CREATE TABLE employees (

emp_no int(11) NOT NULL,

birth_date date NOT NULL,

first_name varchar(14) NOT NULL,

last_name varchar(16) NOT NULL,

gender char(1) NOT NULL,

hire_date date NOT NULL,

PRIMARY KEY (emp_no));

select * from employees  Order by hire_date desc limit 0,1

2、查找入职员工时间排名倒数第三的员工所有信息

select * from employees order by hire_date desc limit 2,1

3、查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号dept _no

CREATE TABLE dept_manager (

dept_no char(4) NOT NULL,

emp_no int(11) NOT NULL,

from_date date NOT NULL,

to_date date NOT NULL,

PRIMARY KEY (emp_no,dept_no));

CREATE TABLE salaries (

emp_no int(11) NOT NULL,

salary int(11) NOT NULL,

from_date date NOT NULL,

to_date date NOT NULL,

PRIMARY KEY (emp_no,from_date));

select s.* , d.dept_no from salaries s , dept_manager d 
where s.to_date='9999-01-01'
and  d.to_date='9999-01-01' 
and s.emp_no=d.emp_no

4、查找所有已经分配部门的员工的last _name和first _name

CREATE TABLE dept_emp (

emp_no int(11) NOT NULL,

dept_no char(4) NOT NULL,

from_date date NOT NULL,

to_date date NOT NULL,

PRIMARY KEY (emp_no,dept_no));

CREATE TABLE employees (

emp_no int(11) NOT NULL,

birth_date date NOT NULL,

first_name varchar(14) NOT NULL,

last_name varchar(16) NOT NULL,

gender char(1) NOT NULL,

hire_date date NOT NULL,

PRIMARY KEY (emp_no));

select employees.last_name, employees.first_name,dept_emp.dept_no from employees inner join dept_emp on
employees.emp_no=dept_emp.emp_no;

5、查找所有员工的last _name和first _name以及对应部门编号dept _no,也包括展示没有分配具体部门的员工

select employees.last_name, employees.first_name, dept_emp.dept_no from employees left join dept_emp 
on employees.emp_no=dept_emp.emp_no;

6、查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序

select employees.emp_no,salaries.salary from employees left join salaries 
on employees.emp_no=salaries.emp_no and employees.hire_date=salaries.from_date 
order by  employees.emp_no desc;

7、查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

CREATE TABLE salaries (

emp_no int(11) NOT NULL,

salary int(11) NOT NULL,

from_date date NOT NULL,

to_date date NOT NULL,

PRIMARY KEY (emp_no,from_date));

SELECT emp_no,count(emp_no) as t from salaries 
GROUP BY emp_no HAVING t >15;

8、找出所有员工当前(to_date=’9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示

注意:

1、显示一次:DISTINCT

2、当前员工:to_date=’9999-01-01’

SELECT DISTINCT salary from salaries where to_date='9999-01-01' order by salary desc ;

9、获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date=’9999-01-01’

注意:

1、on:连接条件 、where:过滤

2、salaries和dept_manager顺序

SELECT d.dept_no,d.emp_no,s.salary 
from  salaries  s JOIN dept_manager  d
on d.emp_no=s.emp_no
where d.to_date='9999-01-01' 
and s.to_date='9999-01-01';

10、获取所有非manager的员工emp_no

CREATE TABLE dept_manager (

dept_no char(4) NOT NULL,

emp_no int(11) NOT NULL,

from_date date NOT NULL,

to_date date NOT NULL,

PRIMARY KEY (emp_no,dept_no));

CREATE TABLE employees (

emp_no int(11) NOT NULL,

birth_date date NOT NULL,

first_name varchar(14) NOT NULL,

last_name varchar(16) NOT NULL,

gender char(1) NOT NULL,

hire_date date NOT NULL,

PRIMARY KEY (emp_no));

SELECT emp_no 
FROM employees 
where emp_no NOT IN
(select emp_no from dept_manager);
相关文章
最新文章
热点推荐