查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT
s.*,
s1.s_score 01score,
s2.s_score 02score
FROM
Score s1,
Score s2,
student s
WHERE
s1.s_id = s2.s_id
AND s1.s_id = s.s_id
AND s1.c_id in ( '01','02')
AND s1.s_score > s2.s_score;
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
使用函数 HAVING
SELECT
st.*,
avgscore
FROM
student AS st
INNER JOIN ( SELECT s_id, AVG( s_score ) AS avgscore FROM score GROUP BY s_id HAVING avgscore > 60 ) AS s ON st.s_id = s.s_id
查询"李"姓老师的数量
select count(tname)
from Teacher
where tname like '李%';
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 10分
SELECT
st.s_id,
st.s_name,
COUNT(sc.c_id),
CASE WHEN SUM(sc.s_score) IS NULL THEN 0.00 ELSE ROUND(SUM(sc.s_score),2) END AS '总成绩'
FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
GROUP BY st.s_id
求每门课程的学生人数
SELECT c_id,COUNT(c_id) FROM score
GROUP BY c_id
查询1990年出生的学生名单
SELECT * FROM student
WHERE YEAR(s_birth)='1990'
查询下周过生日的学生(存在疑点)
select * from student
where week(concat(year(curdate()),"-",mid(s_birth,6,5)))