MYSQL_demo

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

SELECT cid FROM sc WHERE sid=1001;
SELECT sid FROM sc WHERE cid in (SELECT cid FROM sc WHERE sid=1001);
SELECT sid,sname FROM student WHERE sid in (SELECT sid FROM sc WHERE cid in (SELECT cid FROM sc WHERE sid=1001));
-- 2、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

 

SELECT cid,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END ) as `[<60]`,

SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END ) as `[70-60]` ,

SUM(CASE WHEN score >= 70 THEN 1 ELSE 0 END ) as `[85-70]` ,

SUM(CASE WHEN score >= 85 THEN 1 ELSE 0 END ) as `[100-85]`
FROM sc GROUP BY cid;

 

-- 3、查询出只选修了一门课程的全部学生的学号和姓名
select sid FROM sc GROUP BY sid HAVING COUNT(sid)=1;
SELECT sid,sname FROM student WHERE sid in (select sid FROM sc GROUP BY sid HAVING COUNT(sid)=1);

-- 4、查询男生、女生学生人数
SELECT COUNT(sid) as '男生人数' FROM student WHERE ssex ='男';
SELECT COUNT(sid) as '女生人数' FROM student WHERE ssex ='女';

-- 5、查询姓"张"的学生名单
SELECT * FROM student WHERE sname like '%张%';

-- 6、查询同名同性学生名单，并统计同名人数 、
SELECT * FROM student
SELECT a.sname,COUNT(1) FROM (SELECT * FROM student) a,(SELECT * FROM student) b WHERE a.sname=b.sname and a.sid != b.sid GROUP BY a.sname

-- 7、查询1981年出生的学生名单(注：student表中sage列的类型是datetime)
SELECT sname FROM student WHERE sage like '%1981%';

-- 8、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT cid,AVG(score) as avg FROM sc GROUP BY sid ORDER BY avg,cid DESC

-- 9、查询课程名称为"数据库"，且分数低于60的学生姓名和分数
SELECT cid FROM course WHERE cname = '数据库';
SELECT sid,score FROM sc WHERE cid in (SELECT cid FROM course WHERE cname = '数据库') HAVING score <60;
SELECT a.sname, b.score FROM (SELECT * FROM student) a, (SELECT sid,score FROM sc WHERE cid in (SELECT cid FROM course WHERE cname = '数据库') HAVING score <60) b
WHERE a.sid = b.sid

-- 10、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
SELECT sid,cid FROM sc WHERE score >70;
SELECT sid,sname FROM student ;
SELECT * FROM course ;
SELECT b.sname,c.cname,a.score FROM (SELECT * FROM sc WHERE score >70) a,(SELECT * FROM student ) b,(SELECT * FROM course )c WHERE b.sid =a.sid and c.cid=a.cid;