29 lines
947 B
SQL
29 lines
947 B
SQL
# 学生人数不少于10人。（10分）
|
||
SELECT COUNT(s_id) FROM day02_t_student
|
||
|
||
# 课程数量不少于3。（5分）
|
||
SELECT COUNT(c_id) FROM day02_t_course
|
||
|
||
# 每个学生可以选择多个课程，每门课程至少有3个学生。（15分）
|
||
SELECT c_name,COUNT(stu.s_id) FROM
|
||
day02_t_student stu
|
||
LEFT JOIN day02_stu_cou sc ON sc.s_id = stu.s_id
|
||
LEFT JOIN day02_t_course cou ON cou.c_id = sc.c_id
|
||
GROUP BY cou.c_id
|
||
|
||
# 3.查询男生和女生各多少人。（15分）
|
||
SELECT sex,COUNT(s_id) FROM
|
||
day02_t_student
|
||
GROUP BY sex
|
||
|
||
# 4. 查询男生和女生分别占总人数的比例。（20分）
|
||
SELECT sex,COUNT(s_id),COUNT(s_id)/(SELECT COUNT(s_id) FROM day02_t_student) FROM
|
||
day02_t_student
|
||
GROUP BY sex
|
||
|
||
# 5.查询每门课程下的学生总人数。（15分）
|
||
SELECT c_name,COUNT(stu.s_id) FROM
|
||
day02_t_student stu
|
||
LEFT JOIN day02_stu_cou sc ON sc.s_id = stu.s_id
|
||
LEFT JOIN day02_t_course cou ON cou.c_id = sc.c_id
|
||
GROUP BY cou.c_id |