牛客网sql题目（二）

11.

• 题目描述
  从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
  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 title,count(title) as t
from titles
group by title
having t>=2

2.

• 题目描述
  从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
  注意对于重复的emp_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);
• 题目解答：

select title,count(title)as t
from (select distinct emp_no,title,from_date,to_date
     from titles)as temple
group by title
having t>=2

13.

• 题目描述
  查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
  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 *
from employees
where emp_no&1 and last_name not in(select last_name
                                           from employees
                                           where last_name='Marry')
order by hire_date desc

select * from employees
where emp_no % 2 = 1
and last_name != 'Mary'
order by hire_date desc

14.

• 题目描述
  统计出当前各个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 titles.title,avg(salaries.salary)
from titles join salaries on salaries.emp_no=titles.emp_no
where salaries.to_date='9999-01-01'
and titles.to_date='9999-01-01'
group by titles.title

15.

• 题目描述
  获取当前（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'
and salary in (select distinct salary
               from salaries
               order by salary desc
               limit 1,1)

16.

• 题目描述
  查找当前薪水(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.last_name, e.first_name 
FROM employees AS e JOIN salaries AS s 
ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

17.

• 题目描述
  查找所有员工的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 e.last_name,e.first_name,temple.dept_name
from employees e left join(select * from departments d,dept_emp where d.dept_no=dept_emp.dept_no)as temple
on e.emp_no=temple.emp_no

18.

• 题目描述
  查找员工编号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((select salary from salaries
where emp_no=10001
order by to_date desc limit 0,1)-
(select salary from salaries
where emp_no=10001
order by from_date limit 0,1))
as growth

select (max(salary)-min(salary)) as growth
from salaries
where emp_no=10001

19.

• 题目描述
  查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
  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 current_salary.emp_no,(current_salary.salary -hire_salary.salary) as growth
from ( employees join salaries on employees.emp_no = salaries.emp_no and salaries.to_date='9999-01-01') as current_salary
join ( employees join salaries on employees.emp_no = salaries.emp_no and salaries.from_date = employees.hire_date)as hire_salary
on current_salary.emp_no = hire_salary.emp_no
order by growth

20.

• 题目描述
  统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum
  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 de.dept_no, dp.dept_name, COUNT(s.salary) AS sum 
FROM (dept_emp AS de  JOIN salaries AS s ON de.emp_no = s.emp_no) 
 JOIN departments AS dp ON de.dept_no = dp.dept_no 
GROUP BY de.dept_no