数据库练习二：查询语句练习

先进行sql语句的练习，然后再根据语句进行优化以及建立索引。

# 1查看雇员编号、名字和部门
select e.emp_no,e.last_name,de.dept_name from 
    employees as e,dept_emp as emp,departments as de where
    e.emp_no = emp.emp_no and de.dept_no=emp.dept_no ;


# 2显示所有雇员的编号、姓名、工资
select e.emp_no,concat(e.first_name,' ', e.last_name) `name`,sa.salary from
    employees as e,salaries as sa where
    e.emp_no=sa.emp_no limit 100;


# 3查找在d005号部门工作的雇员
select e.emp_no,concat(e.first_name,' ', e.last_name) `name`,de.dept_name from
    employees as e,dept_emp as emp,departments as de where
    de.dept_no='d005' and de.dept_no=emp.dept_no and e.emp_no=emp.emp_no;

# 4要求查找职位为Engineer和Senior Engineer的雇员姓名(last_name)
select e.last_name from 
    employees as e ,titles as ti where
    e.emp_no=ti.emp_no and
    (ti.title='Engineer' or ti.title='Senior Engineer') ;

select e.last_name from 
    employees as e ,titles as ti where
    e.emp_no=ti.emp_no and
    ti.title in ('Engineer','Senior Engineer') ;

# 5查找职位不是Engineer和Senior Engineer的部门编号，雇员部门及姓名。将姓名显示为(first_name+last_name命名为”Name”)

select concat(e.first_name,' ',last_name) `name`,de.dept_no,de.dept_name from
    employees as e ,dept_emp as emp , departments as de , titles as ti where
    e.emp_no = ti.emp_no and e.emp_no = emp.emp_no and emp.dept_no=de.dept_no and
    ti.title != 'Engineer' and ti.title != 'Senior Engineer' ;

## 会发现数据有重复，那是因为在员工职称表(title)里，有些员工是兼顾多个职称的。

# 6查找哪些雇员的工资在60000到90000之间
select concat(e.first_name,' ',last_name) `name`,salary from
    employees as e,salaries where
    e.emp_no = salaries.emp_no and
    salaries.salary between 60000 and 90000;

# 7查找哪些雇员的工资不在60000到90000之间
select concat(e.first_name,' ',last_name) `name`,salary from
    employees as e,salaries where
    e.emp_no = salaries.emp_no and
    salaries.salary not between 60000 and 90000;

# 8查找first_name以P开头，后面仅有四个字母的雇员信息
select distinct concat(e.first_name,' ',last_name) `name` from
    employees as e where
    employees.first_name like "P____";

# 9查找last_name以K开头的雇员信息
select concat(e.first_name,' ',last_name) `name` from
    employees as e where
    e.last_name like "K%";

# 10查找名字以字母K开头，以i结尾，并且第三个字母为o的雇员名字(First_name)、职位和所在部门号
select concat(e.first_name,' ',last_name) `name` , emp.dept_no,title from
    employees as e,dept_emp as emp,titles where
    e.emp_no = emp.emp_no and
    e.emp_no = titles.emp_no and
    e.first_name like 'k_o%i';


# 11查找哪些雇员的职位名不以Se开头
select concat(e.first_name,' ',last_name) `name` , title from
    employees as e , titles where
    e.emp_no = titles.emp_no and
    titles.title not like 'Se%';

# 12查找d005号部门里不是Staff的雇员信息
select concat(e.first_name,' ',last_name) `name` , e.emp_no,title from
    employees as e ,titles as ti , dept_emp as emp where
    emp.dept_no = 'd005' and
    e.emp_no=emp.emp_no and
    e.emp_no = ti.emp_no and
    ti.title !='Staff';

# 13查找d005号部门工资大于100000的员工的信息
select concat(e.first_name,' ',last_name) `name` , e.emp_no,salary from
    employees as e ,salaries , dept_emp as emp where
    e.emp_no = emp.emp_no and
    emp.dept_no='d005'and
    e.emp_no = salaries.emp_no and
    salary >100000;


# 14按字母顺序显示雇员的名字(last_name)
select last_name from 
    employees 
    order by last_name;


# 15按部门编号降序显示雇员信息
select concat(e.first_name,' ',last_name) `name` , dept_no from
    employees as e ,dept_emp as emp where
    e.emp_no = emp.emp_no order by
    emp.dept_no desc ;


# 16计算每个部门的平均工资和工资总和.(显示平均值，总值，部门编号，部门名称 )
select  avg(salary) , count(salary) ,emp.dept_no,de.dept_name from
    employees as e ,salaries as sa ,dept_emp as emp,departments as de where
    e.emp_no=sa.emp_no and
    emp.emp_no=e.emp_no and
    emp.dept_no =de.dept_no group by
    dept_name ,dept_no order by dept_no;


# 17请算出employees表中每位雇员的平均工资
select first_name,last_name,avg(salary) salary from
    employees as e , salaries as sa where
    e.emp_no=sa.emp_no group by
    first_name,last_name order by salary ;


# 18请算出employees表中所有雇员的平均工资
select avg(salary) from
    employees as e , salaries as sa where
    e.emp_no=sa.emp_no ;


# 19请查询出employees表中的最低工资的员工信息

## 典型的慢查询
select concat(e.first_name,' ',last_name) `name` ,salary  from
    employees as e , salaries as sa where
    e.emp_no=sa.emp_no  order by
    salary  limit 1;

## 正确姿势
select concat(e.first_name,' ',last_name) `name` ,salary  from
    employees as e , salaries as sa where
    e.emp_no=sa.emp_no  and
    sa.salary= 
    (select min(salary)  from salaries);


# 20请计算出每个部门的平均工资、最高工资和最低工资
select  avg(salary) , count(salary) ,max(salary),min(salary),emp.dept_no,de.dept_name from
    employees as e ,salaries as sa ,dept_emp as emp,departments as de where
    e.emp_no=sa.emp_no and
    emp.emp_no=e.emp_no and
    emp.dept_no =de.dept_no group by
    dept_name ,dept_no order by dept_no; 

# 21查询薪水发放时间在1986-06-26 ~ 1987-06-25薪水高于46135号雇员并且工种与他相同的雇员情况。
select e.* ,sa.from_date from
    employees as e ,salaries as sa ,titles as ti where
    ti.title in 
    (select title from titles where emp_no=46135) and
    sa.from_date>'1986-06-26' and
    sa.to_date < '1987-06-25' and
    e.emp_no=sa.emp_no and
    e.emp_no = ti.emp_no and
    sa.salary >
     ( select salary from salaries as sa where 
     sa.from_date>'1986-06-26' and sa.to_date< '1987-06-25' and emp_no=46135);

# 22查询工资在10000到50000之间的雇员所在部门的所有人员的信息。
select e.*,de.dept_no,de.dept_no,salary from
    employees as e ,dept_emp as emp,departments as de ,salaries as sa where
    sa.salary in (select salary from salaries where salary between 10000 and 50000 ) and
    e.emp_no=emp.emp_no and
    emp.dept_no = de.dept_no and
    sa.emp_no =e.emp_no order by salary;

# 23查询出在薪水发放时间在1986-06-26 ~ 1987-06-25的员工信息
###（工号，姓名，性别，薪水，职位)
select e.emp_no , concat(e.first_name,' ',last_name) `name`, e.gender,salary,title from
    employees as e , salaries as sa , titles as ti where
    e.emp_no = sa.emp_no and
    e.emp_no = ti.emp_no and
    sa.from_date > '1986-06-26' and
    sa.to_date < '1987-06-25';