文章目录
-
- 1. 查找最晚入职员工的所有信息
- 2. 查找入职员工时间排名倒数第三的员工所有信息
- 3. 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
- 4. 查找所有已经分配部门的员工的last_name和first_name
- 5. 查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工
- 6. 查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
- 7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
- 8. 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
- 9. 获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date='9999-01-01'
- 10. 获取所有非manager的员工emp_no
- 11. 获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
- 12. 获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
- 13. 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t
- 14. 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
- 15. 查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列
- 16. 统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg
- 17. 获取当前(to_date='9999-01-01')薪水第二多的员工的emp_no以及其对应的薪水salary
- 18. 查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by
- 19. 查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
- 20. 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
- 21. 查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
- 22. 统计各个部门对应员工涨幅的次数总和,给出部门编码dept_no、部门名称dept_name以及次数sum
- 23. 对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列
- 24. 获取所有非manager员工当前的薪水情况,给出dept_no、emp_no以及salary ,当前表示to_date='9999-01-01'
- 25. 获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date='9999-01-01',结果第一列给出员工的emp_no,第二列给出其manager的manager_no,第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary
- 26. 汇总各个部门当前员工的title类型的分配数目,结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
- 27. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。 提示:在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
- 28. 查找描述信息中包括robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部
- 29. 使用join查询方式找出没有分类的电影id以及名称
- 30. 使用子查询的方式找出属于Action分类的所有电影对应的title,description
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`));
- sql语句
select *
from employees
where hire_date = (select max(hire_date) from employees);
2. 查找入职员工时间排名倒数第三的员工所有信息
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`));
- sql语句
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`));
- sql语句
select s.*, d.dept_no
from salaries s
join dept_manager d
on d.emp_no = s.emp_no
andd.to_date='9999-01-01'
and s.to_date='9999-01-01';
JOIN 按照功能大致分为如下三类:
INNER JOIN/JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录。
LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。
RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。注意:mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.
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`));
- sql语句
select e.last_name, e.first_name,d.dept_no
from dept_emp d
left join employees e
on e.emp_no = d.emp_no;
5. 查找所有员工的last_name和first_name以及对应部门编号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 `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`));
- sql语句
select e.last_name, e.first_name, d.dept_no
from employees e
left join dept_emp d
on e.emp_no = d.emp_no;
6. 查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_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`));
- sql语句
select e.emp_no,s.salary
from employees e
join salaries s
on e.emp_no = s.emp_no
and e.hire_date = s.from_date
order by e.emp_no desc;
此题应注意以下四个知识点:
1、由于测试数据中,salaries.emp_no 不唯一(因为号码为 emp_no 的员工会有多次涨薪的可能,所以在 salaries 中对应的记录不止一条),employees.emp_no 唯一,即 salaries 的数据会多于 employees,因此需先找到 employees.emp_no 在 salaries 表中对应的记录salaries.emp_no,则有限制条件 e.emp_no = s.emp_no;
2、根据题意注意到 salaries.from_date 和 employees.hire_date 的值应该要相等,因此有限制条件 e.hire_date = s.from_date;
3、根据题意要按照 emp_no 值逆序排列,因