SELECT c.*,a.`s_score` c01,b.`s_score` c02 FROM Score a,Score b,Student c WHERE
c.`s_id`=a.`s_id` AND
a.`s_id` = b.`s_id` AND a.`c_id`!=b.`c_id` AND a.`c_id` = '01' AND b.`c_id` = '02' AND a.`s_score` > b.`s_score`
查询"01"课程⽐"02"课程成绩低的学⽣的信息及课程分数
SELECT s.*,a.`s_score` c01,b.`s_score` c02 FROM Score a INNER JOIN Student s ON a.`s_id` = s.`s_id`
INNER JOIN Score b ON a.`s_score` < b.`s_score` AND a.`s_id` = b.`s_id` AND a.`c_id`!=b.`c_id`
WHERE a.`c_id` ='01' AND b.`c_id` ='02'
查询平均成绩⼤于等于60分的同学的学⽣编号和学⽣姓名和平均成绩
SELECT s_id,AVG(s_score) `avg` FROM Score GROUP BY s_id HAVING `avg` > 60
查询平均成绩⼩于60分的同学的学⽣编号和学⽣姓名和平均成绩 (包括有成绩的和⽆成绩的)
SELECT sco.s_id,s.`s_name`,AVG(s_score) `avg`
FROM Score sco
JOIN Student AS s
ON s.`s_id` = Sco.`s_id`
GROUP BY s_id HAVING `avg` > 60
查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩
SELECT stu.s_name,COUNT(sco.s_score) `count`,SUM(sco.s_score) `sum`
FROM Score AS sco
JOIN Student AS stu
ON stu.s_id = sco.`s_id`
GROUP BY sco.s_id
查询"李"姓⽼师的数量
SELECT COUNT(1)
FROM Teacher
WHERE t_name
LIKE '李%'
查询学过"张三"⽼师授课的同学的信息
SELECT stu.*
FROM Teacher t
JOIN Course c
ON c.t_id = t.t_id
JOIN score s
ON s.c_id = c.c_id
JOIN Student stu
ON stu.s_id = s.`s_id`
WHERE t.t_name = '张三'
查询没学过"张三"⽼师授课的同学的信息
SELECT *
FROM Student result
WHERE result.s_id NOT IN
(SELECT stu.s_id
FROM Student stu
JOIN score s
ON s.s_id = stu.s_id
JOIN Course c
ON s.c_id = c.c_id
JOIN Teacher t
ON t.t_id = c.`t_id`
WHERE t.t_name='张三')
查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT *
FROM Student
WHERE Student.`s_id` IN(
SELECT sco1.`s_id`
FROM score sco1
JOIN score sco2
ON sco1.s_id = sco2.s_id
WHERE sco1.c_id = '01' AND sco2.c_id = '02');
查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT *
FROM score s1
WHERE s1.`c_id` = '01'
AND s1.`s_id` NOT IN (SELECT s2.`s_id` FROM score s2 WHERE s2.`c_id` = '02')
查询"01"课程⽐"02"课程成绩⾼的学⽣的信息及课程分数SELECT c.*,a.`s_score` c01,b.`s_score` c02 FROM Score a,Score b,Student c WHERE c.`s_id`=a.`s_id` AND a.`s_id` = b.`s_id` AND a.`c_id`!=b.`c_id` AND a.`c_id` = '01' AND b.`c_id` = '02' AND a.`s_score` > b.`s_score`..