SQL语句复习

表结构：

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 b
WHERE 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 B
WHERE 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 FROM
sc 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 D
WHERE 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 D
WHERE B.tno != C.tno 
AND A.cno = C.cno 
AND A.sno = D.sno
AND 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 C
WHERE 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 C
WHERE 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 B
WHERE 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)