数据库练习题（二）

原创

mb6478612aac887 2023-06-01 17:25:03 ©著作权

©著作权归作者所有：来自51CTO博客作者mb6478612aac887的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.题目描述
获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
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 d.dept_no,d.emp_no,s.salary from dept_emp d ,salaries s
on d.emp_no=s.emp_no 
where d.to_date='9999-01-01' and s.to_date='9999-01-01' 
GROUP BY d.dept_no
having max(s.salary);

此题思路如下：
1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no；
2、选取每个员工当前的工资水平，用d.to_date = ‘9999-01-01’ AND s.to_date = ‘9999-01-01’作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；
3、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；
4、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY d.dept_no

2.题目描述
从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;

此题应注意以下三点：
1、用COUNT()函数和GROUP BY语句可以统计同一title值的记录条数
2、根据题意，输出每个title的个数为t，故用AS语句将COUNT(title)的值转换为t
3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

SELECT title, COUNT(title) AS t FROM titles
GROUP BY title HAVING t >= 2

3.题目描述
从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 (distinct emp_no)  as t from titles
group by title having t >= 2;

此题应注意以下三点：
1、先用GROUP BY title将表格以title分组，再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数
2、根据题意，输出每个title的个数为t，故用AS语句将COUNT(DISTINCT emp_no)的值转换为t
3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

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

4.题目描述
查找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 emp_no,birth_date ,first_name,last_name,gender,hire_date from employees
WHERE emp_no%2 = 1 AND last_name IS NOT 'Mary'
ORDER BY hire_date DESC

5.题目描述
统计出当前各个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) AS avg FROM titles T,salaries S
WHERE T.emp_no = S.emp_no AND S.to_date = T.to_date AND S.to_date = '9999-01-01'
GROUP BY T.title

当前时间指的是S.to_date = ‘9999-01-01’

6.题目描述
获取当前（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 salary = (select salary from salaries group by salary order by salary desc limit 1,1)

7.题目描述
查找当前薪水(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 s.emp_no,s.salary,e.last_name,e.first_name from salaries s,employees e 
where s.emp_no=e.emp_no 
and s.to_date='9999-01-01' 
and s.salary=(
    select max(salary) from (
        select * from salaries where salary!=(
            select max(salary) from salaries where to_date='9999-01-01'
        )
    ) where to_date='9999-01-01'
);

最高薪水：
select max(salary) from salaries where to_date=’9999-01-01’
除去最高薪水：
select * from salaries where salary!=(
select max(salary) from salaries where to_date=’9999-01-01’
)
剩下的最高薪水：
select max(salary) from (
select * from salaries where salary!=(
select max(salary) from salaries where to_date=’9999-01-01’
)
) where to_date=’9999-01-01’

SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name 
FROM employees AS e INNER 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')


8.题目描述
查找所有员工的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,D.dept_name FROM 
(employees E LEFT JOIN dept_emp ON E.emp_no = dept_emp.emp_no)
LEFT JOIN departments D ON dept_emp.dept_no = D.dept_no

9.题目描述
查找员工编号emp_now为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'

10.题目描述
查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_noy以及其对应的薪水涨幅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));

1.找出每个员工当前工资
select e.emp_no,s.salary as sTo 
from employees as e
left join  salaries as s on e.emp_no=s.emp_no where s.to_date='9999-01-01'
2.找出每个员工入职时的工资
select e.emp_no,s.salary as sHire from employees as e
left join  salaries as s on e.emp_no=s.emp_no and s.from_date=e.hire_date
3.结合找出growth
select t1.emp_no,(t1.sTo-t2.sHire) as growth from 
(select e.emp_no,s.salary as sTo from employees as e left join  salaries as s 
on e.emp_no=s.emp_no where s.to_date='9999-01-01') as t1 join
(select e.emp_no,s.salary as sHire from employees as e left join  salaries as s 
on e.emp_no=s.emp_no and s.from_date=e.hire_date) as t2 on t1.emp_no=t2.emp_no
order by growth asc

;