– 26、查询每门课程被选修的学生数:
SELECT c.c_id,c.c_name,COUNT(s_id)
FROM score sc JOIN course c ON sc.c_id=c.c_id
GROUP BY c.c_id,c.c_name
– 27、查询出只有两门课程的全部学生的学号和姓名:
SELECT s.s_id,s.s_name
FROM student s JOIN score sc ON s.s_id=sc.s_id
GROUP BY s.s_id,s.s_name HAVING COUNT(c_id)=2
– 28、查询男生、女生人数:
SELECT SUM(CASE WHEN s_sex='男' THEN 1 ELSE 0 END) man,SUM(CASE WHEN s_sex='女' THEN 1 ELSE 0 END) women
FROM student
– 29、查询名字中含有"风"字的学生信息:
SELECT *
FROM student
WHERE s_name LIKE '%风%'
– 30、查询同名同性学生名单,并统计同名人数:
SELECT s1.s_id,s1.s_name,s1.s_sex,count(*) as sameName
FROM student s1,student s2
WHERE s1.s_name=s2.s_name AND s1.s_id!=s2.s_id AND s1.s_sex=s2.s_sex
GROUP BY s1.s_id,s1.s_name,s1.s_sex
为什么GROUP BY?
根据sid统计每个名字的同名人数。
后续部分参见: