MySQL--查询案例（二）

1、查询至少有一门课与学号为"1001"的同学所学相同的同学的学号和姓名;

SELECT student.sid , student.sname from student , sc
WHERE student.sid = sc.sid AND sc.cid IN (SELECT cid FROM sc WHERE sid = 1001)
GROUP BY sid;
2、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT course.cid , course.cname , COUNT(sc.cid),
SUM(CASE WHEN sc.score >= 85 AND sc.score <= 100 THEN 1 ELSE 0 END) '[100-85]',
SUM(CASE WHEN sc.score >= 70 AND sc.score <= 85 THEN 1 ELSE 0 END) '[85-70]',
SUM(CASE WHEN sc.score >= 60 AND sc.score <= 70 THEN 1 ELSE 0 END) '[70-60]',
SUM(CASE WHEN sc.score <= 60 THEN 1 ELSE 0 END) '[ <60]'
FROM course , sc
WHERE sc.cid = course.cid
GROUP BY cid

3、查询出只选修了一门课程的全部学生的学号和姓名

SELECT student.sid , student.sname FROM student , sc
WHERE sc.sid = student.sid
GROUP BY sc.sid
HAVING COUNT(sc.cid) = 1

4、查询男生、女生学生人数

SELECT ssex , COUNT(sid)  FROM student
GROUP BY ssex

5、查询姓"张"的学生名单

SELECT sname FROM student
WHERE sname LIKE'张%'

6、查询同名同性学生名单，并统计同名人数

SELECT sname , COUNT(sid) FROM student
GROUP BY sname

7、查询1981年出生的学生名单(注：student表中sage列的类型是datetime)

SELECT * FROM student
WHERE sage BETWEEN "1981-01-01 00:00:00" AND "1982-01-01 00:00:00"

8、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT cid , AVG(score) FROM sc
GROUP BY cid
ORDER BY AVG(score) , cid DESC

9、查询课程名称为"数据库"，且分数低于60的学生姓名和分数

SELECT student.sname , sc.score FROM student , sc
WHERE student.sid IN
(SELECT sid FROM sc
WHERE (cid IN (SELECT cid FROM course
WHERE cname = "数据库") AND sc.score < 60 ))
GROUP BY student.sid

10、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

SELECT student.sid , student.sname , course.cname , sc.score FROM sc , student , course
WHERE sc.sid = student.sid AND sc.cid = course.cid AND sc.score > 70
GROUP BY sc.sid