MySQL笔试题型

student学生表（sid,sname,sage,ssex）

course课程表（cid，cname，tid,stuid）

sc成绩表(sid,cid,score)

teacher教师表(tid，tname)

//1.查询数学课比英语课成绩高的所有学生的学号
select sid from student where (select * from sc join course on sc.cid=course.cid where cname='数学')>(select * from sc join course on sc.cid=course.cid where cname='英语')

//2.查询姓李的老师的个数
select count(*) from teacher where tname like '李%'

//3.查询没学过叶平老师课的同学的学号，姓名
select sid,sname from student where sid not in (select * from teacher inner join course on teacher.tid = course.tid where tname = '叶平')

//4.查询英语这门课学生成绩小于平均分的学生的学号以及姓名
select sid,sname from student where (select * from sc inner join course on sc.cid=course.cid where cname="英语" and score<avg(score))

//5.把sc表中叶平老师教的语文课成绩全部改成此课程的平均分
update sc set score=(select avg(score) from sc,course,teacher where course.cid=sc.cid and course.tid = teacher.tid and teacher.tname="叶平")

//6.删除学习叶平老师课的sc表记录
delete from sc where sc.cid in(select * from teacher,course where teacher.tid=course.tid and tname="叶平")

//7.把数学课的成绩从高到低进行排列并选出10个学生成绩最高的
select * from sc inner join course on sc.cid=course.cid where cname="数学" order by score desc limit 10；

//8.查询英语课成绩不及格的学生的学号姓名，并按学号从小到大排列
select * from student，sc，course where course.cid=sc.cid and course.stuid=student.sid and cname="英语" and score<60 order by student.sid

//9.查询课程编号为xxx123xxx并且成绩在90分以上的学生的学号和姓名
select sid,sname from student where (select * from sc inner join course on sc.cid = course.cid where cid like '%123%' and score>=90)

//10.查询出至少选修两门课程的学生的学号以及姓名
select sid，sname from student inner join course on student.sid=course.stuid where count(course.stuid)>=2

//11.删除英语和语文两科成绩倒数后两名的学生的所有信息
delete from student where（select * from sc join course on sc.cid=course.cid where cname='英语' order by score desc limit 2 and cname=‘语文’ order by score desc limit 2）

//12.修改所有女学生的数学成绩在58分到60分之间的学生成绩为70分
update sc set score = 70 where（select * from couse inner join student on course.stuid=student.sid inner join sc on couse.cid=sc.cid where ssex='女' and cname="数学" and 58<score<60）