怎样能熟练写出mysql语句_MYSQL语句强化练习

#1.查询“3001”课程比“3002”课程成绩高的所有学生的学号select a.sno from(select sno,score from sc where cno=3001)a,

(select sno,score from sc where cno=3002)bwhere a.score>b.score and a.sno=b.sno

#2.查询平均成绩大于60分的同学的学号和平均成绩selectsno,AVG(score) avg_scorefromscgroup bysnohaving avg_score>60#3.查询所有同学的学号、姓名、选课数、总成绩selectstudent.sno,

student.sname,count(sc.cno),sum(sc.score)fromstudentleft join sc on student.sno =sc.snogroup bystudent.sno

#4.查询姓“李”的教师数select count(distinct(teacher.tname)) numfrom teacher where teacher.tname like '李%'#5.查询没有学过“李老师”课程的同学的学号、姓名selectstudent.sno,

student.snamefromstudentwhere student.sno not in(select sc.sno fromscinner join course on sc.cno =course.cnoinner join teacher on teacher.tno =course.tnowhere teacher.tname = '李老师')

#6.查询学过“王老师”所有课程的学生的学号、姓名selectstudent.sno,student.snamefrom student where student.sno in(selectsc.snofromscinner join course on sc.cno =course.cnoinner join teacher on course.tno =teacher.tnowhere teacher.tname = '王老师'

group bysc.snohaving count(sc.sno)=(select COUNT(course.cno) fromteacherinner join course on teacher.tno =course.tnowhere teacher.tname = '王老师')

)

#7.查询学过“3001”也学过“3004”课程的学生的学号、姓名selectstudent.sno,student.snamefromstudentinner join sc on student.sno =sc.snowhere cno = 3001

and student.sno in(select sc.sno from sc where sc.cno=3004)selectstudent.sno,student.snamefromstudentinner join sc a on student.sno =a.snowhere cno = 3001

and EXISTS(select b.sno from sc b where a.sno = b.sno and b.cno=3004)

#8.查询所有课程成绩小于60的同学的学号、姓名selectstu.sno,stu.snamefromstudent stuwhere stu.sno not in(select sc.sno from sc where sc.score >= 60 group bysc.sno

)

#9.查询没有学全所有课的同学的学号、姓名selectstudent.sno,student.snamefromstudentinner join sc on student.sno =sc.snogroup by sc.sno having count(*)

)

#10.查询至少有一门课与学号为“1001”同学所学相同的学生的学号和姓名selectstudent.sno,student.snamefromstudentinner join sc on student.sno =sc.snowhere sc.sno != 1001

and sc.cno in(select sc.cno from sc where sc.sno = 1001)group bystudent.sno

#11.查询和“1001”号的同学学习的课程完全相同的其他同学学号和姓名selectsc.snofrom sc where sc.sno != 1002 and cno in(select cno from sc where sc.sno = 1002)group by sno having count(*) =(select count(*) from sc where sno = 1002)

#12.删除学习“李老师”课的sc表记录deletescfromscinner join course on course.cno =sc.cnoinner join teacher on teacher.tno =course.tnowhere teacher.tname = '李老师'#13.向sc表中插入一些记录，这些记录要求符合以下条件；没有上过编号“3004”课程的同学学号、3001号课程的平均成绩insert intosc(sno,cno,score)select sno,3001,(SELECT AVG(score) from sc where cno = 3001)from student where sno not in(select sno from sc where cno = 3003)

#14.按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生id，语文，数学，英语，有效课程数，有效平均分selects.snoas '学生id',

(select s1.score from sc s1 where s1.sno = s.sno and s1.cno = 3001) as '语文',

(select s2.score from sc s2 where s2.sno = s.sno and s2.cno = 3002) as '数学',

(select s3.score from sc s3 where s3.sno = s.sno and s3.cno = 3003) as '英语',count(cno) as '有效课程数',AVG(score) as '有效平均分'

from sc s group by s.sno order by '有效平均分'#15.查询各科成绩最高和最低的分：以如下的形式显示，课程id，最高分，最低分selects.cnoas '课程id',

(select max(score) from sc s1 where s1.cno = s.cno) as '最高分',

(select min(score) from sc s2 where s2.cno = s.cno) as '最低分'

from sc s group bys.cno

#16.按各科平均成绩从低到高和及格率的百分数从高到低顺序selects.cnoas '课程id',

course.cnameas '课程名',AVG(s.score) asavgScore,

(select count(*) from sc s1 where s.cno = s1.cno and s1.score>60)/count(sno)*100 asratefromsc sinner join course on course.cno =s.cnogroup bys.cnoorder by avgScore asc,rate desc#17.查询“3001”课程成绩在第2名到3名的学生的各科成绩selectstudent.sname,course.cname,sc.scorefromcourseinner join sc on course.cno =sc.cnoand course.cno = 3001

inner join student on student.sno =sc.snoorder by sc.score desclimit1,2#18.查询出只选修两门课程的全部学生的学号和姓名selectstudent.sno,student.snamefromstudentinner join sc on student.sno =sc.snogroup by student.sno having count(student.sno)=2