mysql数据库面试题(3)

huolan 2020-04-22

题目描述

11.获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date=‘9999-01-01‘。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。


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 `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`));

输入

select d.emp_no, m.emp_no as manager_no

from dept_emp  d inner join dept_manager  m

on d.dept_no = m.dept_no

where m.to_date = ‘9999-01-01‘ and d.to_date = ‘9999-01-01‘ and d.emp_no <> m.emp_no

输出

emp_no

manager_no

10001

10002

10003

10004

10009

10010

 12.获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary

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 `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 d.dept_no,d.emp_no,MAX(s.salary) AS salary

FROM salaries s inner join dept_emp d

ON d.emp_no=s.emp_no

WHERE d.to_date=‘9999-01-01‘ AND s.to_date=‘9999-01-01‘

GROUP BY d.dept_no

输出

dept_no

emp_no

salary

d001

10001

88958

d002

10006

43311

d003

10005

94692

d004

10004

74057

d005

10007

88070

d006

10009

95409

13.titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

输入

select title,count(title) as t from titles

group by title having t>=2;

输出

itle

t

Assistant Engineer

2

Engineer

4

省略

省略

Staff

3

 14.titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。注意对于重复的emp_no进行忽略。


CREATE TABLE IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

输入

SELECT title,COUNT(DISTINCT emp_no) AS t

FROM titles

GROUP BY title

HAVING t >= 2;

输出

title

t

Assistant Engineer

2

Engineer

3

省略

省略

Staff

3

 15.查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列

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

where emp_no%2!=0 and last_name!=‘Mary‘

order by hire_date desc;

输出

emp_no

birth_date

first_name

last_name

gender

hire_date

10011

1953-11-07

Mary

Sluis

F

1990-01-22

10005

1955-01-21

Kyoichi

Maliniak

M

1989-09-12

10007

1957-05-23

Tzvetan

Zielinski

F

1989-02-10

10003

1959-12-03

Parto

Bamford

M

1986-08-28

10001

1953-09-02

Georgi

Facello

M

1986-06-26

10009

1952-04-19

Sumant

Peac

F

1985-02-18

相关推荐