/*
1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
*/
SELECT
st.*,
sc.C_id AS '01',
sc2.C_id AS '02' /*选择学生所有列,成绩表列*/
FROM
student st
LEFT JOIN sorce sc ON sc.s_id = st.s_id
AND sc.c_id = '01' /*选择条件,将课程表和学生表建立链接*/
LEFT JOIN sorce sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
WHERE
sc.S_sorce > sc2.S_sorce
/*
2 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
*/
SELECT
st.*,
sc.C_id AS '01',
sc2.C_id AS '02' /*选择学生所有列,成绩表列*/
FROM
student st
LEFT JOIN sorce sc ON sc.s_id = st.s_id
AND sc.c_id = '01' /*选择条件,将课程表和学生表建立链接*/
LEFT JOIN sorce sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
WHERE
sc.S_sorce < sc2.S_sorce
/*
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
*/
SELECT
st.s_id,
st.s_name,
avgscore
FROM
student AS st
INNER JOIN (
SELECT s_id, AVG( s_sorce )
AS avgscore
FROM score
GROUP BY s_id HAVING avgscore >= 60
)
AS s ON st.s_id = s.s_id
/*
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(去尾保留一位小数)
(包括有成绩的和无成绩的)
*/
SELECT
st.s_id,
st.s_name,
CASE
WHEN AVG( sc.s_sorce ) IS NULL THEN
' ' ELSE ROUND ( AVG( sc.s_sorce ), 1 )
END AS avgscore
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
AVG( sc.s_sorce )< 60
OR AVG( sc.s_sorce ) IS NULL
/*
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
*/
SELECT
st.s_id,
st.s_name,
COUNT( s.s_id ) AS '选课总数',
SUM( s.s_sorce ) AS '总成绩'
FROM
student st
INNER JOIN score s ON st.s_id = s.s_id
GROUP BY
st.s_id,
st.s_name
/*
6、查询"李"姓老师的数量
*/
SELECT
count( t_name )
FROM
Teacher
WHERE
t_name LIKE '李%';
/*
7、查询学过"张三"老师授课的同学的信息
*/
SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
LEFT JOIN teacher t ON t.t_id = c.t_id
WHERE
t.t_name = "张三";
/*
8、查询没学过"张三"老师授课的同学的信息
*/
SELECT
st.*
FROM
student st
WHERE
st.s_id NOT IN (
SELECT
st.s_id
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
LEFT JOIN teacher t ON t.t_id = c.t_id
WHERE
t.t_name = "张三"
/*
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
*/
SELECT
st.*
FROM
student st
WHERE
s_id IN (
SELECT
a.s_id
FROM
( SELECT * FROM score WHERE c_id = '01' ) AS a
INNER JOIN ( SELECT * FROM score WHERE c_id = '02' ) AS b ON a.s_id = b.s_id
)
/*
查询学过编号为"01"但