表名和字段–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) –学生编号,课程编号,分数
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
6、查询'01'课程比'02'课程成绩低的学生的信息及课程分数select Student.* ,b.s_score'课程01的分数',c.s_score'课程02的分数'from Student ,Score b ,Score c where b.s_score
7、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩select Student.s_id,Student.s_name,avg(s_score) as '平均分'from Student left join score on Student.s_id=Score.s_idgroup by Student.s_id,Student.s_name having avg(s_score)>=60
注意事项 :1、where 后不能跟聚合函数,因为where执行顺序大于聚合函数。2、where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行。3、having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组。
8、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 -- (包括有成绩的和无成绩的)select Student.s_id,Student.s_name,avg(s_score) as '平均分'from Student left join score on Student.s_id=Score.s_idgroup 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_idgroup by Student.s_id,Student.s_name having avg(s_score) is null
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_idgroup by Student.s_id,Student.s_name
10、查询没学过'张三'老师授课的同学的信息select *from studentswhere Student.s_id not in(select distinct Student.s_idfrom 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_idwhere Teacher.t_name='张三')
11、查询学过编号为'01'并且也学过编号为'02 '的课程的同学的信息select student.*from student,score sc,score sc1where 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 所以截图就有两种
给小蓝孩点点赞