表结构：

SQL练习：

/*--1、查询课程1的成绩 比 课程2的成绩 高 的所有学生的学号；*/

SELECT A.sno FROM(SELECT sno,score FROM sc WHERE cno=1 )as A,(SELECT sno,score FROM sc WHERE cno=2 )as bWHERE A.score>b.score or A.score = b.score

/*--2、查询平均成绩大于60分的同学的学号和平均成绩；*/

SELECT sno,AVG(score) FROM sc GROUP BY sno HAVING AVG(score) >60

/*--3、查询所有同学的学号、姓名、选课数、总成绩；*/

/*我的回答1*/

SELECT A.sno,A.score,cno,B.sname FROM(SELECT sc.sno,score,COUNT(cno) cno FROM sc GROUP BY sc.sno )as A,(SELECT student.sno,sname FROM student) as BWHERE A.sno = B.sno

/*我的回答2*/

SELECT A.sno,sum(A.score),count(A.cno),B.sname	FROM sc as A,student as B	WHERE A.sno = B.sno	GROUP BY A.sno　　

/*其他回答*/

SELECT A.sno,A.score,COUNT(A.cno),B.sname FROMsc as A, student as B WHERE A.sno = B.sno GROUP BY A.cno

/*--4、查询姓“李”的老师的个数；*/

SELECT COUNT(*) FROM teacher where tname like '%李%'

/*--5、查询没学过“叶平”老师课的同学的学号、姓名；*/

/*我的回答1*/

SELECT distinct A.sno,D.sname FROM(SELECT sno,cno FROM sc ) as A,(SELECT tno FROM teacher WHERE tname='叶平') as B,(SELECT cno,tno FROM course) as C,(SELECT sname,sno FROM student) as DWHERE B.tno != C.tno AND A.cno = C.cno AND A.sno = D.sno

/*我的回答2*/

SELECT distinct D.sno,D.sname FROM sc as A, teacher as B,course as C,student as DWHERE B.tno != C.tno AND A.cno = C.cno AND A.sno = D.snoAND B.tname = '叶平'

/*我的回答3*/

SELECT sno,sname FROM student WHERE sno NOT in (    SELECT sno FROM sc WHERE cno in(        SELECT cno FROM course WHERE tno in(            SELECT tno FROM teacher WHERE tname='叶平'            )        )    )

/*--6、查询同时学过课程1和课程2的同学的学号、姓名；*/

/*我的回答1*/

SELECT A.sno , C.sname FROM (SELECT sno FROM sc WHERE cno = 1) as A ,(SELECT sno FROM sc WHERE cno = 2) as B,(SELECT sno,sname FROM student ) as CWHERE A.sno = B.sno AND A.sno = C.sno

/*我的回答2*/

SELECT sno,sname FROM student WHERE sno in (    SELECT sno FROM sc WHERE cno = 2 and cno in (        SELECT sno FROM sc WHERE cno = 1    ))

/*--7、查询学过“叶平”老师所教所有课程的所有同学的学号、姓名；*/

SELECT sname,sno FROM student WHERE sno in(    SELECT A.sno     FROM sc A,teacher B,course C    WHERE B.tno = C.tno     AND A.cno = C.cno    AND B.tname = '叶平')

/*--8、查询 课程编号1的成绩 比 课程编号2的成绩 高的所有同学的学号、姓名；*/

SELECT sname,C.sno FROM    (SELECT sno,score FROM sc WHERE cno = 1) as A,    (SELECT sno,score FROM sc WHERE cno = 2) as B,    (SELECT sno,sname FROM student ) as CWHERE A.score>B.score AND A.sno = B.sno AND C.sno = A.sno

 

SELECT sname,sno FROM student WHERE sno in (    SELECT A.sno FROM        (SELECT sno,score FROM sc WHERE cno = 1) as A,        (SELECT sno,score FROM sc WHERE cno = 2) as BWHERE A.score>B.score AND A.sno = B.sno)

/*--9、查询所有课程成绩小于60分的同学的学号、姓名；*/

SELECT sno FROM sc WHERE MAX(score)>60 GROUP BY sno ASC /*这样是错误的写法，注意！！！！！！！*/SELECT sno FROM sc GROUP BY sno ASC HAVING(MAX(score)<60)

　　

/*--10、查询所有课程成绩大于60分的同学的学号、姓名；*/

SELECT sno,MIN(score) FROM sc GROUP BY sno ASC HAVING(MIN(score)>60)