数据库练习题二
1、表名和字段
–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) –学生编号,课程编号,分数
2、5、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select Student.* ,b.s_score'课程01的分数',c.s_score'课程02的分数'
from Student ,Score b ,Score c
where b.s_score>c.s_score and c.c_id='02' and b.c_id='01'
and Student.s_id=b.s_id and Student.s_id=c.s_id
3、6、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select Student.* ,b.s_score'课程01的分数',c.s_score'课程02的分数'
from Student ,Score b ,Score c
where b.s_score<c.s_score and c.c_id='02' and b.c_id='01'
and Student.s_id=b.s_id and Student.s_id=c.s_id
4、7、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select Student.s_id,Student.s_name,avg(s_score) as '平均分'
from Student left join score on Student.s_id=Score.s_id
group by Student.s_id,Student.s_name having avg(s_score)>=60
5、注意事项 ：
1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
6、8、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select Student.s_id,Student.s_name,avg(s_score) as '平均分'
from Student left join score on Student.s_id=Score.s_id
group by Student.s_id,Student.s_name having avg(s_score)<=60
union
select Student.s_id,Student.s_name,avg(s_score) as '平均分'
from Student left join score on Student.s_id=Score.s_id
group by Student.s_id,Student.s_name having avg(s_score) is null
7、9、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select Student.s_id,Student.s_name,count(Course.c_id) as '选课总数',sum(Score.s_score) as '所有课程总成绩'
from Student left join Score on Student.s_id=Score.s_id left join Course on course.c_id=Score.c_id
group by Student.s_id,Student.s_name
8、10、查询没学过"张三"老师授课的同学的信息
select *
from students
where Student.s_id not in(
select distinct Student.s_id
from student left join Score on Student.s_id=Score.s_id left join Course on Score.c_id=Course.c_id left join Teacher on Course.t_id=Teacher.t_id
where Teacher.t_name='张三'
)
9、11、查询学过编号为"01"并且也学过编号为"02 "的课程的同学的信息
select student.*
from student,score sc,score sc1
where sc.s_id=sc1.s_id and student.s_id=sc.s_id and sc.c_id='01' and sc1.c_id='02'
因为我操作的时候是基于sql2012和plsql 所以截图就有两种
