SQL的执行顺序为:
先where 再group 再having 再select 后order.
sql语句解析的顺序的问题:
先where条件过滤出需要的纪录,再对筛选出来的记录分组group加having。接下来就是选取字段的过滤select最后order排序。所以别名只有在select和order by内才可以只用
表名和字段
–1.学生表
Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) --教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) --学生编号,课程编号,分数
建表语句
--学生表
spool student.log
prompt
prompt Creating table STUDENT
prompt ======================
prompt
create table JCXT.STUDENT
(
s_id VARCHAR2(20) not null,
s_name VARCHAR2(20),
s_birth VARCHAR2(20),
s_sex VARCHAR2(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
spool off
--课程表
spool course.log
prompt
prompt Creating table COURSE
prompt =====================
prompt
create table JCXT.COURSE
(
c_id VARCHAR2(20),
c_name VARCHAR2(20),
t_id VARCHAR2(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
spool off
--教师表
spool teacher.log
prompt
prompt Creating table TEACHER
prompt ======================
prompt
create table JCXT.TEACHER
(
t_id VARCHAR2(20),
t_name VARCHAR2(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
spool off
--成绩表
spool score.log
prompt
prompt Creating table SCORE
prompt ====================
prompt
create table JCXT.SCORE
(
s_id VARCHAR2(20),
c_id VARCHAR2(20),
s_score VARCHAR2(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
spool off
测试数据
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
SQL语句练习
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select a.*,b.s_score as score_b,c.s_score as score_c from student a,score b,score c
where a.s_id=b.s_id
and a.s_id=c.s_id
and b.c_id='01'
and c.c_id='02'
and b.s_score>c.s_score
SQL语句运行后结果
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.*, b.s_score as score_b, c.s_score as score_c
from student a
left join score b
on a.s_id = b.s_id
and b.c_id = '01'
or b.c_id = null
join score c
on a.s_id = c.s_id
and c.c_id = '02'
where b.s_score < c.s_score
SQL语句运行后结果
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) as score_a
from student b
join score a
on b.s_id = a.s_id
group by b.s_id, b.s_name
having ROUND(AVG(a.s_score), 2) >= 60
SQL语句运行后结果
4、--查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
(包括有成绩的和无成绩的)
select a.s_id, a.s_name, round(avg(b.s_score), 2) as score_b
from student a
left join score b
on a.s_id = b.s_id
group by a.s_id, a.s_name
having round(avg(b.s_score), 2) < 60
union
select a.s_id, a.s_name, 0 as score_b
from student a
where a.s_id not in (select distinct s_id from score)
SQL语句运行后结果
5、--查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,
a.s_name,
count(b.c_id) as couse_count,
sum(b.s_score) as score_count
from student a
left join score b
on a.s_id = b.s_id
group by a.s_id, a.s_name
SQL语句运行后结果
6、查询"李"姓老师的数量
select count(a.t_id) as count_teacher
from teacher a
where a.t_name like '李%'
SQL语句运行后结果
7、查询学过"张三"老师授课的同学的信息
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 course
where t_id = (select t_id from teacher where t_name = '张三'))
SQL语句运行后结果
8、查询没学过"张三"老师授课的同学的信息
select c.*
from student c
where c.s_id not in
(select a.s_id
from student a
join score b
on a.s_id = b.s_id
and b.c_id in (select a.c_id
from course a
join teacher b
on a.t_id = b.t_id
and b.t_name = '张三'))
SQL语句运行后结果
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.*
from student a, score b, score c
where a.s_id = b.s_id
and a.s_id = c.s_id
and b.c_id = '01'
and c.c_id = '02'
SQL语句运行后结果
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select a.*
from student a
where a.s_id in (select s_id from score where c_id = '01')
and a.s_id not in (select s_id from score where c_id = '02')
SQL语句运行后结果
11、查询没有学全所有课程的同学的信息
select s.*
from student s
where s.s_id in
(select a.s_id
from student a
left join score b
on a.s_id = b.s_id
group by a.s_id
having count(b.c_id) < (select count(*) from course))
SQL语句运行后结果
另一种SQL语句写法
select *
from student t
where t.s_id not in
(select s_id
from score
group by s_id
having count(*) = (select count(distinct c_id) from course))
SQL语句运行后结果
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select *
from student
where s_id in
(select distinct a.s_id
from score a
where a.c_id in (select c_id from score where s_id = '01'))
SQL语句运行后结果
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select a.*
from student a
where a.s_id in
(select distinct s_id
from score
group by s_id
having count(*) = (select count(c_id) from score where s_id = '01'))
--找到学过‘01’同学没学过的课程,有哪些同学,并排除他们
and a.s_id not in
(select s_id
from score
where c_id in
(select distinct c_id
from score
where c_id not in (select c_id from score where s_id = '01'))
group by s_id)
SQL语句运行结果