每天五道MySQL---3

1. 获取所有非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 emp_no 
   from employees 
   where employees.emp_no not in(
       select emp_no from dept_manager 
   )
   

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(emp_no) 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(emp_no) as t
   from (select distinct emp_no, title 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 <> '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 title, avg(salary) as avg
   from salaries s inner join titles 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