实验5-SQL高级查询及多表查询（一）

实验5-SQL高级查询及多表查询（一）

By Supyo （万里学院）

（1）查询选了数学的同学的学号，姓名，成绩，并要求成绩按照由高到低排列出来

SELECT sc.sno,student.sname ,sc.grade from  sc,student
WHERE sc.sno = student.sno and sc.cno =2
ORDER BY sc.grade DESC

（2）请将计算机系选了1号课的同学按成绩由高到低排序，如果成绩相同，再按学号由小到大排序，最后显示学号，姓名及成绩

SELECT student.sno ,student.sname,sc.grade FROM student,sc
where sc.cno =1 and student.sdept ='计算机系' and sc.sno = student.sno
ORDER BY sc.grade DESC,sno2

（3）查询信息系和管理系选了数学课的成绩达到80分（含80)的同学姓名及成绩

SELECT student.sname,sc.grade FROM student,sc,course
WHERE student.sno = sc.sno and course.cno = sc.cno and course.cname='数学' and sc.grade >=80 and student.sdept in('信息系','管理系')

（4）查询管理系选了数据库课的成绩大于70并且已在密码表中注册过的同学的姓名及成绩

SELECT student.sname,sc.grade FROM student,sc,mm
where  student.sdept ='管理系'  
and sc.cno = 1 and sc.grade >70
and sc.sno = student.sno and mm.sno=student.sno AND sc.sno = mm.sno

（5）请将名字中含有叠字的所有学生姓名查询出来（黄婷婷）

select  sname  from  student where right(sname,1)=left(right(sname,2),1)  or  (left(right(sname,2),1)  =left(right(sname,3),1)  ) and sname like '___%'  or left(sname,1)=right(left(sname,2),1)

（6）查询密码表中第一位密码和最后一位密码字符相同的1989年出生的同学姓名，密码，出生日期

SELECT DISTINCT student.sname,mm.password1,student.csrq FROM student,mm
where mm.sno = student.sno and YEAR(student.csrq) ='1989' and right(mm.password1,1) = LEFT(mm.password1,1)

（7）查询选了数据库课的管理系同学的最高成绩，最低成绩，及平均成绩（请用最高成绩，最低成绩，及平均成绩表示出来）

SELECT MAX(sc.grade) as '最高成绩' , MIN(sc.grade) as '最低成绩' , AVG(sc.grade) as '平均成绩' FROM sc,student
WHERE sc.cno =1 and sc.sno = student.sno and student.sdept = '管理系'

（8）查询2009级选了数据库的人数

SELECT  COUNT(sc.sno) as 人数 FROM sc,student
where student.sno = sc.sno and sc.cno = 1 and sc.sno LIKE '2009%'

（9）学校拿出5000元奖励数据库课程中80分以上的同学，请问每个同学平均能拿到多少奖金？（请用平均奖金表示并保留1位小数）

SELECT  ROUND(5000/
(SELECT COUNT(*) as 'pjrs' FROM sc
where sc.cno =1 and sc.grade >80
) ,1)as '平均奖金'

（10）如果将所有选了数据库课程的同学成绩，下调15分，请查询该课程将有多少个不及格同学

SELECT count(sc.sno) as '不及格人数' FROM sc 
where sc.cno =1 and sc.grade <75