Fork me on GitHub

MySQL练习二

发表于 | | 阅读次数:

统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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`));

输入描述:

无

输出描述:

dept_no dept_name sum
d001 Marketing 24
省略 省略 省略
d006 Quality Management 25

解答:

1
select c.dept_no, c.dept_name, count(salary) as sum from (salaries a, dept_emp b on a.emp_no= b.emp_no), departments c on c.dept_no= b.dept_no group by c.dept_no

对所有员工的当前(to_date=’9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

1
2
3
4
5
6
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`));

输入描述:

无

输出描述:

emp_no salary rank
10005 94692 1
10009 94409 2
10010 94409 2
10001 88958 3

解答

1
2
3
4
select a.emp_no, a.salary, count(distinct b.salary) as rank 
from salaries a, salaries b 
where a.salary<= b.salary and a.to_date= "9999-01-01" and b.to_date= "9999-01-01" 
group by a.emp_no order by rank

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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`));

输入描述:

无

输出描述:

dept_no emp_no salary
d001 10001 88958
d004 10003 43311
d005 10007 88070
d006 10009 95409

解答

1
2
3
4
5
6
select d.dept_no, e.emp_no, s.salary 
from dept_emp d, (
    select emp_no from employees where emp_no not in (
        select emp_no from dept_manager
    )) e, salaries s 
where d.emp_no= e.emp_no and d.emp_no= s.emp_no and d.to_date= "9999-01-01" and s.to_date= "9999-01-01"

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

结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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`));

输入描述:

无

输出描述:

emp_no manager_no emp_salary manager_salary
10001 10002 88958 72527
10009 10010 95409 94409

解答

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select t1.emp_no, t2.emp_no,t1.salary as s1,t2.salary as s2
from
(
    select salary,s.emp_no,dept_no
    from salaries as s join dept_emp de on s.emp_no=de.emp_no
    where s.to_date='9999-01-01' and de.to_date='9999-01-01' and
    s.emp_no not in (select emp_no from dept_manager dm)
) as t1,
(
    select salary,s.emp_no,dept_no
    from salaries as s join dept_manager dm on s.emp_no=dm.emp_no
    where s.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
) as t2
where s1>s2
and t1.dept_no = t2.dept_no

汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 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);

输入描述:

无

输出描述:

dept_no dept_name title count
d001 Marketing Senior Engineer 1
d001 Marketing Staff 1
d002 Finance Senior Engineer 1
d003 Human Resources Senior Staff 1
d004 Production Senior Engineer 2
d005 Development Senior Staff 1

解答

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
法一：
select e.dept_no,d.dept_name,t.title,count(t.title)
from departments d,dept_emp e,titles t
where d.dept_no=e.dept_no
and e.emp_no=t.emp_no
and e.to_date='9999-01-01'
and t.to_date='9999-01-01'
group by e.dept_no,t.title
法二：
select dp.dept_no,dp.dept_name,t.title,count(*) as count
from (
        select ds.dept_no,ds.dept_name,de.emp_no
        from departments as ds 
                inner join
             dept_emp as de
                on ds.dept_no = de.dept_no
           where de.to_date = '9999-01-01'
        ) as dp
        inner join 
     titles as t
        on dp.emp_no = t.emp_no
    where t.to_date = '9999-01-01'
    group by dp.dept_no,t.title

解析: 法一、先按照emp_no分组，再按照每个emp_no里的title分组。
法二、先将部门表和部门员工表进行关联获取到部门编号和部门名称以及员工编号，然后再关联titles表，最后使用dept_no和title进行分组即可。

给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime(‘%Y’, to_date)

1
2
3
4
5
6
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`));

输入描述:

无

输出描述:

emp_no from_date salary_growth
10003 1995-12-03 24178
10008 1998-03-11 20843
10008 2000-03-10 5997

解答

1
2
3
4
5
6
select s1.emp_no, s1.from_date, s1.salary- s2.salary as salary_growth
from salaries s1, salaries s2 
where (strftime('%Y', s1.to_date)- strftime('%Y', s2.to_date))= 1
and s1.emp_no= s2.emp_no
and salary_growth> 5000
order by salary_growth desc

查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

film film
字段 说明
file_id 电影id
title 电影名称
description 电影描述信息 
1
2
3
4
5
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
category category
字段 说明
category_id 电影分类id
name 电影分类名称
last_update 电影分类最后更新时间 
1
2
3
4
CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category film_category
字段 说明
film_id 电影id
category_id 电影分类id
last_update 电影id和分类id对应关系的最后更新时间 
1
2
3
CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

输入描述:

无

输出描述:

无

解答

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select c.name,count(fc.film_id)
from (
        select category_id,count(film_id) as amount
        from film_category
        group by category_id
        having amount>=5
        ) as cc,
        category as c,
        film as f,
        film_category as fc
where f.film_id = fc.film_id
      and fc.category_id = c.category_id
      and cc.category_id = c.category_id
      and f.description like '%robot%'

解析: 本题考查的是多表关联，本身有三个表关联，但是由于有条件说分类下的电影总数>=5，所以可以先查询出film_category表内满足条件的category_id，然后与其他三表进行关联即可。

使用join查询方式找出没有分类的电影id以及名称

输入描述:

无

输出描述:

无

解答:

1
select cc.film_id, cc.title from (select * from film as f left join film_category as fc on f.film_id= fc.film_id) as cc where cc.category_id is null
Your support will encourage me to continue to create!