☺☺☺
六道题
考查知识点
题号 | 知识点 |
---|---|
1 | 问题的分析、表的链接 |
2 | 考察GROUP BY 分组 |
3 | ORDER BY... 以…为准排序 |
4 | 考察表之间的连接 |
5 | ...LEFT JOIN...ON... 会读取左边数据表的全部数据,即便右边表无对应数据,若无对应数据则赋值NULL |
6 | 先链接出一个虚表,然后再将这个虚表进行链接得到想要的结果 |
一、获取员工其当前的薪水比其manager当前薪水还高的相关信息
- 题目描述:
获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
CREATE TABLEdept_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 TABLEdept_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 TABLEsalaries
(
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信息
注意条件是当前时间(to_date=‘9999-01-01’),且这里需要连接员工表dept_emp与工资表salaries得到员工工资 - 找到每个经理的emp_no与salary信息
同样,注意条件是当前时间(to_date=‘9999-01-01’),且这里需要连接经理表dept_manager与工资表salaries得到部门经理工资 - 比较员工的salary与该员工的部门经理的salary,大于则输出所需的信息,限制条件emp.salary > mang.salary
注意这里的条件仍然有当前时间(to_date=‘9999-01-01’)。并且有员工所属部门的经理,因此emp.dept_no = mang.dept_no
- 代码:
SELECT emp.emp_no, mang.emp_no, emp.salary, mang.salary
FROM
(SELECT a.dept_no, a.emp_no, b.salary
FROM dept_emp a, salaries b
WHERE a.emp_no = b.emp_no
AND a.to_date='9999-01-01'
AND b.to_date='9999-01-01'
) emp,
(SELECT c.dept_no, c.emp_no, d.salary
FROM dept_manager c, salaries d
WHERE c.emp_no = d.emp_no
AND c.to_date='9999-01-01'
AND d.to_date='9999-01-01'
) mang
WHERE emp.salary > mang.salary
AND emp.dept_no = mang.dept_no
二、汇总各个部门当前员工的title类型的分配数目
- 题目描述:
汇总各个部门当前员工的title类型的分配数目,即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count
(注:因为员工可能有离职,所有dept_emp里面to_date不为’9999-01-01’就已经离职了,不计入统计,而且员工可能有晋升,所以如果titles.to_date 不为 ‘9999-01-01’,那么这个可能是员工之前的职位信息,也不计入统计)
CREATE TABLEdepartments
(
dept_no
char(4) NOT NULL,
dept_name
varchar(40) NOT NULL,
PRIMARY KEY (dept_no
));
CREATE TABLEdept_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 EXISTStitles
(
emp_no
int(11) NOT NULL,
title
varchar(50) NOT NULL,
from_date
date NOT NULL,
to_date
date DEFAULT NULL);
- 分析:
乍一看挺复杂,其实就是考察分组,花里胡哨一堆条件没什么用,直接用WHERE限制即可。
这里用了两个分组,分别是对部门分组和对title进行分组,实现的功能是统计相同部门下相同头衔的员工个数:
对部门分组是因为每个部门有多个title都需要列出来,先根据部门进行分类;
对title分组是为了统计出COUNT(title)即每个部门里每个title的值; - 代码:
SELECT a.dept_no, a.dept_name, c.title, COUNT(c.title)
FROM departments a, dept_emp b, titles c
WHERE a.dept_no = b.dept_no
AND b.emp_no = c.emp_no
AND b.to_date = '9999-01-01'
AND c.to_date = '9999-01-01'
GROUP BY a.dept_no, c.title
三、给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列
- 题目描述:
给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。
提示:在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)
(数据保证每个员工的每条薪水记录to_date-from_date=1年,而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)
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
));
- 分析:
题目已经说了这个数据是保证每个员工每天薪水记录to_date-from_date=1年,所以降低了难度,只需要用日期里的年相减为1便是年薪水涨幅。
年薪水涨幅,所以是每个员工两年的同一日期的薪水记录相减,即上一年的from_date - 今年的from_date,或者上一年的to_date - 今年的to_date。
本题需要用两个时间段的薪水值相减,因此对表有复建,然后再用emp_no链接再相减,约束条件要使得两年相减的差值为1,且salary值之差>5000。 - 代码:
SELECT b.emp_no, b.from_date, (b.salary-a.salary) AS sub
FROM salaries a, salaries b
WHERE a.emp_no = b.emp_no
AND strftime('%Y', b.to_date)-strftime('%Y', a.to_date) = 1
AND sub > 5000
ORDER BY sub
DESC
四、查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id)),而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
- 题目描述:
查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id)),而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
- 分析:
- 要得到film.description里包含’robot’的电影分类名category.name,需要连接film表与file_category表、cetegory表与file_category表.
- 要得到该分类下的电影数目,需要在category表下进行统计得出该分类的category_id
- 要求该分类包含电影的总数量大于等于5部,用WHERE去限制
- 代码:
select name,count(name)
from film,film_category,category
where film.description like '%robot%'
and film.film_id= film_category.film_id
and film_category.category_id= category.category_id
and category.category_id in
(select category_id
from film_category
group by category_id
having count(film_id)>=5
)
五、使用join查询方式找出没有分类的电影id以及名称
- 题目描述:
使用join查询方式找出没有分类的电影id以及名称
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));
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL,last_update
timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL,last_update
timestamp);
- 分析:
让表film左链接表film_category,这个时候没有分类的表的category_id为NULL,用where找出这时的film_id和title即可。 - 代码:
SELECT f.film_id, f.title
FROM film f LEFT JOIN film_category fc ON fc.film_id = f.film_id
WHERE fc.category_id IS NULL
六、子查询的方式找出属于Action分类的所有电影对应的title,description
- 分析:
- 先连接category 与film_category 找出类别=‘Action’的电影的film_id;
- 然后再链接film与之前连接好的表,找出此时film_id下的title与description。
- 代码:
SELECT c.title, c.description
FROM film c,
(
SELECT *
FROM category c JOIN film_category FC ON c.category_id = fc.category_id
WHERE c.name = 'Action'
) s
WHERE c.film_id = s.film_id