数据库练习题二

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、查询"01"课程比"02"课程成绩高的学生的信息及课程分数selectStudent.*,b.s_score'课程01的分数',c.s_score'课程02的分数'fromStudent,Scoreb,Scorecwhereb.s_score>c.s_scoreandc.c_id='02'andb.c_id='01'andStudent.s_id=b.s_idandStudent.s_id=c.s_id

4、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩selectStudent.s_id,Student.s_name,avg(s_score)as'平均分'fromStudentleftjoinscoreonStudent.s_id=Score.s_idgroupbyStudent.s_id,Student.s_namehavingavg(s_score)>=60

7、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩selectStudent.s_id,Student.s_name,count(Course.c_id)as'选课总数',sum(Score.s_score)as'所有课程总成绩'fromStudentleftjoinScoreonStudent.s_id=Score.s_idleftjoinCourseoncourse.c_id=Score.c_idgroupbyStudent.s_id,Student.s_name

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息selectstudent.*fromstudent,scoresc,scoresc1wheresc.s_id=sc1.s_idandstudent.s_id=sc.s_idandsc.c_id='01'andsc1.c_id='02'因为我操作的时候是基于sql2012和plsql 所以截图就有两种