子查询等

① 子查询 (内查询) 在主查询之前一次执行完成。
② 子查询的结果被主查询(外查询)使用 。

运算符 说明
比较运算符
< 小于
<= 小于或等于
= 等于
!= 或 <> 不等于

= 大于等于
大于
in 在某集合内
between 在某范围内
逻辑运算符
not 或 ! 逻辑非
or 或 || 逻辑或
and 或 && 逻辑与

语法：

例 1：
谁的工资比 Abel 高?

select last_name,salary from employees
where salary>( select salary from employees
where last_name='Abel')

例 2：
查询和 Zlotkey 相同部门的员工姓名和工资

select last_name,salary from employees
where department_id = ( select department_id from employees
where last_name='Zlotkey')

满足多个条件： 例 3：
返回 job_id 与 141 号员工相同，salary 比 143 号员工多的员工姓名，
job_id 和工资

select last_name,job_id,salary from employees
where job_id = ( select job_id from employees
where employee_id=141)

and salary > ( select salary from employees
where employee_id=143)

加入函数： 例 4：
返回公司工资最少的员工的 last_name,job_id 和 salary

select last_name,job_id,salary from employees
where salary = (

select min(salary) from employees)

子查询中的 HAVING 字句： 例 5：
查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资

select department_id,min(salary) from employees
group by

department_id having min(salary) > ( select min(salary)
from employees

where department_id=50)

多行子查询

① 返回多行。
② 使用多行比较操作符。

IN 的使用： 例 5：
返回location_id 是 1400 或 1700 的部门中的所有员工姓名

select last_name from employees
where department_id in ( select department_id from departments
where location_id in (1400,1700))

ANY 的使用： 例 6：
返回其它部门中比 job_id 为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及 salary

select employee_id,last_name,job_id,salary from employees
where salary < any( select salary
from employees

where job_id = 'IT_PROG') and job_id <> 'IT_PROG' 

1.查询每个专业的学生人数

select majorid,count(*) from student group by majorid

2.查询参加考试的学生中，每个学生的平均分、最高分

select studentno,avg(score),max(score) from result group by studentno

3.查询姓张的每个学生的最低分大于60的学号、姓名

select s.studentno,s.studentname,min(score) from student s join result r  on s.studentno = r.studentno where studentname like '张%' group by s.studentno  having min(score) >60

4.查询每个专业生日在“1988-1-1”后的学生姓名、专业名称

select studentname ,majorname from student s join major m on m.majorid = s.majorid where 'borndate'>'1998-1-1'

5.查询每个专业的男生人数和女生人数分别是多少

select count(*),sex,majorid from student group by sex ,majorid 

6.查询专业和张翠山一样的学生的最低分

select min(score) from result where studentno in (select studentno from student where majorid = (select majorid from student where studentname = '张翠山'))

7.查询大于60分的学生的姓名、密码、专业名

select studentname,loginpwd,majorname from student s join major m on s.majorid=m.majorid join result r on s.studentno=r.studentno where r.score>60

8.按邮箱位数分组，查询每组的学生个数

select length(email),count(*) from student group by length(email)

9.查询学生名、专业名、分数

select studentname,majorname,score from student s join major m on s.majorid=m.majorid join result r on s.studentno=r.studentno 

10.查询哪个专业没有学生，分别用左连接和右连接实现

select m.majorid,m.majorname from major m left join student s on m.majorid=s.majorid where s.studentno is null
select m.majorid ,m.majorname from  student s right join major m on m.majorid = s.majorid  where s.studentno is null

11.查询没有成绩的学生人数

select count(*) from student s left join result r on s.studentno=r.studentno where r.score is null

1. 查询工资最低的员工信息: last_name, salary

select last_name,salary from employees where salary = (select min(salary) from employees) 

2. 查询平均工资最低的部门信息

select * from departments where department_id = (select department_id from employees group by department_id having avg(salary)=(select min(avg(salary)) from employees group by department_id))

3. 查询平均工资最低的部门信息和该部门的平均工资

select d.*,(select avg(salary) from employees where department_id =d.department_id) from departments d where d.department_id = (select department_id from employees group by department _id having avg(salary) =(select min(avg(salary)) from employees group by department_id))

4. 查询平均工资高于公司平均工资的部门有哪些

select department_id from employees group by department_id from employees group by department_id having avg(salary)>(select avg(salary) from employees)

5. 查询出公司中所有 manager 的详细信息.

select employee_id, last_name from employees where employee_id in (select distinct manager_id from employees)

6. 查询平均工资最高的部门的 manager 的详细信息:

last_name, department_id, email, salary
select last_name,department_id,email,salary from employees where employee_id =(select manager_id from departments where department_id =(select department_id from employees group by department_id having avg(salary) =(select max(avg(salary)) from employees group by department_id)))