#查询学过“张三”老师授课的同学的信息
select *
from student where s_id in (
select s_id from score where c_id =
(select c_id from course where t_id =
(select t_id from teacher where t_name = '张三')))
select
c.*
from
course a,score b,student c,teacher d
where d.t_id = a.t_id
and a.c_id = b.c_id
and b.s_id = c.s_id
and d.t_name = '张三';
#查询没学过“张三”老师授课 的同学的信息
select * from student where s_name not in
(select
c.s_name
from
course a,score b,student c,teacher d
where d.t_id = a.t_id
and a.c_id = b.c_id
and b.s_id = c.s_id
and d.t_name = '张三')
#查询学过编号为"01"并且学过编号为“02”的课程的同学的信息
select
stu.*
from
score a, score b,student stu
where a.c_id = '01'
and b.c_id = '02'
and a.s_id = stu.s_id
and b.s_id = stu.s_id
#查询学过编号为"01"并且学过编号为“02”的课程的同学的信息
select
stu.*
from
score a, score b,student stu
where a.c_id = '01'
and b.c_id = '02'
and a.s_id = stu.s_id
and b.s_id = stu.s_id
## 查询没有学完全部课程的学生信息
select stu.s_id,count(sco.c_id) as cot
from student stu
left JOIN score sco
on stu.s_id = sco.s_id
group by (stu.s_id) having cot < (select count(*) from course)
##查询至少有一门跟‘01’同学课程一致的同学的信息
select stu.*
from
student stu,score sco
where stu.s_id = sco.s_id
and sco.c_id in (select sco.c_id as tCourse
from student stu,score sco
where stu.s_id = sco.s_id
and stu.s_id = '01') GROUP BY stu.s_id
select
a.*
from student a
left join score b
on a.s_id =b.s_id
where b.c_id in
(select c_id from score where s_id ='01' )
group by 1
查找最晚入职员工的所有信息
select * from employees where hire_date = (select max(hire_date) from employees)
#查找入职员工时间排名倒数第三的员工所有信息 注意去重
select * from employees where hire_date = (
select distinct hire_date from employees order by hire_date desc limit 2,1
)
# 三个表inner join
查找所有员工的last_name和first_name以及对应的dept_name
select e.last_name,e.first_name,d.dept_name
from employees e left join dept_emp de on e.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no
#查找在职员工自入职以来的薪水涨幅情况 两张表结合
select e.emp_no, j.salary - s.salary as growth
from employees e
join salaries s on e.emp_no = s.emp_no and e.hire_date = s.from_date
join salaries j on e.emp_no = j.emp_no and j.to_date = '9999-01-01'
order by growth;
查找在职员工自入职以来的薪水涨幅情况
select e.emp_no, (s1.salary - s2.salary) as growth
from employees e
inner join salaries s1
on e.emp_no = s1.emp_no
and s1.to_date = '9999-01-1'
inner join salaries s2
on e.emp_no = s2.emp_no
and e.hire_date = s2.from_date
order by growth
获取所有非manager员工当前的薪水情况
select de.dept_no,e.emp_no,s.salary
from employees e
inner join salaries s
on e.emp_no = s.emp_no and s.to_date = '9999-01-01'
inner join dept_emp de
on e.emp_no = de.emp_no
where de.emp_no not in(
select emp_no
from dept_manager dm
where dm.to_date = '9999-01-01'
)
