MySQL笔试题练习及答案(三)

第21题

题目描述

查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面）

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

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
a.emp_no,
(b.salary - c.salary) as growth
from employees as a
join salaries as b
on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
join salaries as c
on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc;
-- 本题主要用两个内join 第一个join表是当前薪水,第二个join表是入职薪水.

第22题

题目描述

统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序

CREATE TABLE
departments
(

dept_no
char(4) NOT NULL,

dept_name
varchar(40) NOT NULL,

PRIMARY KEY (
dept_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
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.dept_name,
temp.sum
from departments d
join (
select 
dep.dept_no dept_no,
count(s.salary) as sum
from dept_emp dep 
join salaries s 
on dep.emp_no = s.emp_no 
group by dep.dept_no
) temp
on d.dept_no = temp.dept_no
order by d.dept_no asc;

第23题

题目描述

对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_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
emp_no,
salary,
dense_rank() over(order by salary desc) t_rank
from salaries
where to_date='9999-01-01';

第24题

题目描述

获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

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

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

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
de.dept_no, 
s.emp_no, 
s.salary 
from 
(employees  e join salaries s on s.emp_no = e.emp_no AND s.to_date = '9999-01-01')
join dept_emp de on e.emp_no = de.emp_no
where de.emp_no not in (select emp_no from dept_manager where to_date = '9999-01-01');

第25题

题目描述

获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,

结果第一列给出员工的emp_no，

第二列给出其manager的manager_no，

第三列给出该员工当前的薪水emp_salary,

第四列给该员工对应的manager当前的薪水manager_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
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 
sem.emp_no AS emp_no, 
sdm.emp_no AS manager_no, 
sem.salary AS emp_salary, 
sdm.salary AS manager_salary
FROM (
    SELECT 
    s.salary, 
    s.emp_no, 
    de.dept_no 
    FROM salaries s 
    INNER JOIN dept_emp de 
    ON s.emp_no = de.emp_no 
    AND s.to_date = '9999-01-01' 
    ) AS sem, 
    (SELECT 
     s.salary, 
     s.emp_no, 
     dm.dept_no 
     FROM salaries s 
     INNER JOIN dept_manager dm 
     ON s.emp_no = dm.emp_no 
     AND s.to_date = '9999-01-01' 
    ) AS sdm
WHERE sem.dept_no = sdm.dept_no 
AND sem.salary > sdm.salary;

第26题

题目描述

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

(注：sqllite,字符串拼接为 || 符号，不支持concat函数，mysql支持concat函数)

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 concat(last_name," ",first_name) as name from employees;

第27题

题目描述

创建一个actor表，包含如下列信息

在这里插入图片描述

-- sqlite版本
create table actor (
    actor_id smallint(5) primary key not null,
    first_name varchar(45) not null,
    last_name varchar(45) not null,
    last_update timestamp not null default (datetime('now','localtime')) 
) 
-- MySQL版本
create table actor (
    actor_id smallint(5) primary key not null,
    first_name varchar(45) not null,
    last_name varchar(45) not null,
    last_update date not null
)

第28题

题目描述

题目已经先执行了如下语句:

drop table if exists actor;

CREATE TABLE actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update DATETIME NOT NULL)

请你对于表actor批量插入如下数据(不能有2条insert语句哦!)

在这里插入图片描述

insert into actor values 
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');

第29题

题目描述

题目已经先执行了如下语句:

drop table if exists actor;

CREATE TABLE actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update DATETIME NOT NULL);

insert into actor values (‘3’, ‘WD’, ‘GUINESS’, ‘2006-02-15 12:34:33’);

对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

在这里插入图片描述

-- 原来接一个ignore就好了,但是在sqlite中用insert or ignore into
insert ignore into actor 
values(3,'ED','CHASE','2006-02-15 12:34:33');

第30题

在这里插入图片描述

create table actor_name 
select first_name,last_name from actor;

原文链接：https://blog.csdn.net/feichong621/article/details/110734444

你可能也喜欢