11111

查询"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)))