数据库SQL实战练习2

1、找出所有员工当前(to_date=‘9999-01-01’)具体的薪水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 distinct salary
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary desc;

2、获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=‘9999-01-01’

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

SELECT d.dept_no, d.emp_no , s.salary
FROM salaries s , dept_manager d
WHERE s.emp_no = d.emp_no
AND s.to_date = '9999-01-01'
AND d.to_date = '9999-01-01';

3、获取所有非manager的员工emp_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 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.emp_no
FROM employees e
WHERE e.emp_no not in (   SELECT distinct d.emp_no
                        FROM dept_manager d)

3、获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=‘9999-01-01’。

结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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 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));

SELECT e.emp_no,m.emp_no as manager_no
FROM dept_emp e,dept_manager m
WHERE e.dept_no = m.dept_no
AND e.emp_no <> m.emp_no
AND e.to_date = '9999-01-01'
AND m.to_date = '9999-01-01'

4、获取所有部门中当前员工薪水最高的相关信息，给出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,max(s.salary)
FROM dept_emp d , salaries s
WHERE  d.emp_no = s.emp_no
AND d.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
-- 用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；
Group BY d.dept_no;

5、从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 count(title) >= 2

6、从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 a.title,count(a.title) as t
FROM (  SELECT distinct emp_no,title
        FROM titles) as a 
GROUP BY a.title
HAVING t >= 2

7、查找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 last_name <> 'Mary'
and  emp_no % 2 == 1
order by hire_date desc

习惯书写SQL都是关键字小写的，我就不去转大写了，没什么影响的！
如果有什么问题写的不好写的不对的地方，欢迎指正！