sql练习题归纳总结中（4）

16.统计出当前各个title类型对应的员工当前薪水对应的平均工资

题目描述

统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

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

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 t.title,avg(s.salary)

from salaries as s

inner join titles as t

on s.emp_no=t.emp_no

where s.to_date='9999-01-01'

and t.to_date='9999-01-01'

group by title

 

17.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

题目描述：

获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

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

from salaries

where to_date='9999-01-01'

order by salary desc

limit 1,1

//此处注意最高的薪水不一定是一个人

 

select emp_no, salary

from salaries

where to_date = '9999-01-01'

and salary = (select distinct salary from salaries order by salary desc limit 1,1)

//牛客网上排行第一的答案是这样的。

 

18.查找当前薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

题目描述：

查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

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 e.emp_no,max(s.salary) as salary,e.first_name,e.last_name

from employees as e

inner join salaries as s

on e.emp_no=s.emp_no

where s.to_date='9999-01-01'

where s.salary!=(select max(salary) from salaries where to_date = '9999-01-01'))

//注意嵌套的select中，只有一个表，故salary必须直接写为salary而不是s.salary。而且不要忘记to_date

 

19.查找所有员工的last_name和first_name以及对应的dept_name

题目描述:

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

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 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 last_name,first_name,dept_name

from (employees as a left join dept_emp as d on a.emp_no=d.emp_no)

left join departments as dm

on d.dept_no=dm.dept_no

//两次左连接，第一次在括号里面，e左连接d表，由emp_no获得dept_no,

第二次左连接dm，由dept_no获得dept_name.

 

20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

题目描述:

查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

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 max(salary)-min(salary) as growth

from salaries

where emp_no=10001

//这种是不考虑减薪情况的。

select 

(select salary from salaries where emp_no =10001 order by desc limit 0,1)-

(select salary from salaries where emp_no =10001 order by asc limit 0,1) as growth

//最后一条消息减去第一条消息，是比较全面的

select的用法正在学习。