每日sql 思维练习（思想为主 编码为辅）

濾请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

，以上例子查询结果如下:

樂在题干中找到几个关键词emp_no为奇数 last_name不为Mary的员工 按照hire_date 逆序排序

抓住关键词就很好写了 第一个过滤条件是 N%2 =1 的数据 第二个过滤条件是不为Mary的可以使用！= 或者 <> 第三个关键字是逆序 可以使用desc 进行操作

select * from employees emp where emp.emp_no % 2 = 1
 and emp.last_name <> 'Mary' ORDER BY hire_date DESC

drop table if exists  `employees` ; 
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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Bezalel','Mary','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1953-11-07','Mary','Sluis','F','1990-01-22');

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

请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序，以上例子输出如下:

一般出现各个 每个 这些词的时候第一个想到的就是 Group By 进行分组 之后可以提取一个 平均 对应的是sql 中的 AVG 最后一个有效信息是 升序
主要的思路是先使用Inner join 进行联表查询 然后根据 titie 进行分组 根据 salary 求平均数 根据平均数进行升序排序

select title.title,AVG(sa.salary) from titles title 
inner join salaries sa on sa.emp_no = title.emp_no
 GROUP BY title ORDER BY AVG(sa.salary) desc

drop table if exists  `salaries` ; 
drop table if exists  titles;
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 titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');