sql查询语句练习

select * from emp where a=b group by deptno having c>0 order by sal

#where：子句查询的条件
#group by：按列分组
#having：分组结果的筛选条件

执行顺序：  from --> where --> group by --> having --> select --> order by --> limit

where后面不能跟聚合函数

1、先看一下emp表中存放的数据：

empno：员工编号

ename：员工姓名

job：员工职位

mgr：部门经理的编号

hiredate：入职日期

sal：薪资

comm：奖金

deptno：部门编号

(1)查询EMP表中各部门工资大于2000的员工人数，并且按人数从高到低排列

思路：

step1：先找出工资大于2000的人   

select * from emp where sal>2000;

执行结果如图：

step2：分组找出各个部门工资大于2000的人(对step1中的查询结果按部门进行分组)，并进行人数统计

​
select deptno,count(*) from emp where sal>2000 group by deptno;

​

执行结果：

step3：对step2中统计出的人数进行排序

select deptno,count(*) sort_count
from emp
where sal>2000
group by deptno
order by sort_count desc;

执行顺序： from --> where --> group by --> having --> select --> order by --> limit

因为执行顺序select在order by之前，所以select 后面对count(*)重命名之后，order by后面在执行的时候也能识别到

执行结果如图：

(2) 查询EMP表中员工人数大于3的所在部门的部门编号，及对应的人数

方法一：使用查询语句嵌套的方法

step1：先查询出各部门的部门编号以及对应部门的员工数量，对查询的员工数量起了个别名叫dept_num_count

select deptno,count(*) dept_num_count
from emp
group by deptno;

运行结果如下图：

step2：将step1的查询结果进行过滤，只查询dept_num_count>3的部门信息，并返回部门编号和对应部门的员工数量

select deptno,dept_num_count
from (select deptno,count(*) dept_num_count
from emp
group by deptno)
where dept_num_count>3;

执行结果如图：

 方法二：直接使用having过滤分组查询结果

select deptno,count(*)
from emp
group by deptno
having count(*)>3;

执行结果如图所示：

2、查询所有科目都大于80的学生信息，表名stu_score

思路一分析：先查询出分数小于等于80的学生信息，然后条件筛选学生姓名不在分数小于等于80分的查询结果集中(只要有一科成绩小于80，姓名就会查询出来)，再进行去重操作

select distinct 姓名
from stu_score
where 姓名 not in(select 姓名 from stu_score where 分数<=80);

执行结果如下：

思路二分析：只要根据学生姓名进行分组，学生的最小成绩都比80分大那就说明该学生的所有成绩都是大于80分的。

select 姓名,min(分数)
from stu_score 
group by 姓名
having min(分数)>80;

运行结果：

3、习题：

建表及插入数据：

create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
commit;

student表：                                                               

 teacher表：

course表：

sc表：

 

(1)查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

方法一：

select a.sno,a.sname
from student a join sc b
on a.sno=b.sno
where b.cno in ('c001','c002')
group by a.sno,a.sname
having count(*)=2;

根据题目可知，需要用到的表是sc表和student表，先把这两个表连接，因为学生的课程不需要无效匹配，所以直接使用join，先看下前半部分两表关联的结果：

select a.*,b.*
from student a join sc b
on a.sno=b.sno;

运行结果如下：可以看到每个学生选的课程都不一样，需要对该结果集进行筛选

把没有选c001、c002课程的学生过滤掉，只保留选了这两门课程的人

所以接下来是：

select a.*,b.*
from student a join sc b
on a.sno=b.sno
where b.cno in ('c001','c002');

运行结果如下图：从下图可以看到该结果集中所有的数据都有c001和c002这两门课程了，可是题目要求是查询出同时选择c001和c002两门课程的学生，所以需要我们进一步处理

 从上图能看出同时选择了c001和c002的学生的课程数刚好是2，只选择其中一门课程的学生课程数是1，说明了我们可以对上个结果集按学生的姓名、学号进行分组，再对分组出来的课程数量进行计数，并把课程数不是2的学生信息过滤掉，就说明留下来的学生就都是同时选择了c001和c002的学生，将学生的学号和姓名返回

select a.sname,a.sno
from student a join c b
on a.sno=b.sno
where b.cno in (c001'','c002')
group by a.sname,a.sno
having count(*)=2;

查询结果如下图所示：

方法二：

select a.sno,a.sname
from student a join sc b
on a.sno=b.sno
and b.cno='c001'
join sc c
on a.sno=c.sno
where c.cno='c002';

查询结果如下图：

分步解析：

step1：先把student表和sc使用join连接起来，再加限定条件把选择了c001课程的人筛选出来

select a.*,b.*
from student a join sc b
on a.sno=b.sno
and b.cno='c001';

运行结果如下图：可以看到确实查询出了只选择c001课程的学生

 step2：我们可以用同样的方法可以查询出只选择了c002课程的学生：

select a.*,c.*
from student a join sc c
on a.sno=c.sno
and c.cno='c002'

运行结果如下图所示，查询出了只选择c002的学生

step3：把step1筛选出的结果集作为一个新表和sc表进行连接：

select a.*,b.*
from student a join sc b
on a.sno=b.sno
and b.cno='c001'
join sc c
on b.sno=c.sno
and c.cno='c002'

运行结果如下图所示：

 能看到step2和step3的表中都有s001、s002、s003，说明两门课程都选了的学生就是他们三个人，所以不用select a.*,b.*，只把他们三个的学号和姓名查询出来即可：

则代码就变成了：

select a.sname,a.sno
from student a join sc b
on a.sno=b.sno
and b.cno='c001'
join sc c 
on b.sno=c.sno
and c.cno='c002';

(2)查询学过“谌燕”老师所教的所有课的同学的学号、姓名；

select cno,tname
from course
where cno in (select tno from teacher where tname='谌燕')

(3)查询“c001”课程比“c002”课程成绩高的所有学生的学号；

select a.sno,a.score,b.score
from sc a join sc b
on a.cno='c001'
and b.cno='c002'
where a.score>b.score;

使用自连接，a表取学号是c001的数据，b表取学号是c002的数据，过滤条件是c001对应的成绩大于c002的成绩，执行结果如图：

 

3、(1)求出每个部门超过其所在部门平均工资人员占整个部门人员数目的百分比

先查看一下emp表的内容：

分步解析：

百分比=超过部门平均工资的人数/整个部门的人数

step1：先求出部门的平均工资

select deptno,avg(sal)
from emp
group by deptno

运行结果：

 step2：

将emp表和step1的结果集进行left join连接，连接条件是emp表的deptno(部门编号)和step1的部门编号相等

select a.deptno,a.sal,b.asal
from emp a
left join (select deptno,avg(sal) asal from emp group by deptno) b
on a.deptno=b.deptno

运行结果如下图，图中可以看到部门编号、各部门员工的薪资sal和各部门的平均薪资asal

 step3：要得到各部门超过平均薪资的员工数量，需要对step2的结果集进行条件筛选，添加判断条件，只将sal>asal的结果筛选并统计个数，就得到了各部门的员工薪资大于部门平均工资的员工数量

select a.deptno,round(count(b.deptno)/count(a.deptno),4)*100||'%' 百分比
from emp a
left join (select deptno,avg(sal) asal from emp group by deptno) b
on a.deptno=b.deptno
and a.sal>b.asal 
group by a.deptno;

运行结果如下：这里用了简单的字符串拼接符号||以及round函数，round(a,b)表示对数字a保留b位数，举个例子，round(12677.098776,2)=12677.10  对该数字保留两位小数，小数点第三位是8，所以对小数点第二位的9四舍五入就变成了10