sql 题目编写

SELECT s.Sname, g.Cno, g.Grade
from 学生表 s 
left join 成绩表 g
on s.Sno = g.Sno
WHERE g.Grade > 80
ORDER BY g.Grade desc


SELECT DISTINCT s.Sno, s.Sname
from 学生表 s 
left join 成绩表 g
on s.Sno = g.Sno
WHERE g.Grade > 80
and s.Sdept = "数学系"

-- 3. 只改对应的成绩
update 成绩表 
set Grade = IFNULL(Grade,0) + 10 
WHERE Cno = "c01"

-- 3. 更新选择c01课程学生的所有成绩
update 成绩表 
set Grade = IFNULL(Grade,0) + 10 
WHERE Sno in (
    SELECT DISTINCT Sno
    from Grade 
    WHERE Cno = "c01"
)

update 成绩表 as ug, (
	SELECT g.课程编号, AVG(g.成绩) 平均成绩
	from 成绩表 g
	left join 课程表 c
	on g.课程编号 = c.课程编号
	left join 老师表 t
	on c.教师编号 = t.教师编号
	WHERE t.教师 = "朱清时"
	GROUP BY g.课程编号
) as avg_gread
set ug.成绩 = avg_gread.平均成绩
WHERE ug.课程编号 = avg_gread.课程编号
and avg_gread.平均成绩 is not null


select DISTINCT b.学号,b.课程编号,b.成绩 
from 成绩表 a, 成绩表 b 
where a.课程编号 != b.课程编号 
and a.成绩 = b.成绩 
and a.学号 = b.学号

select DISTINCT b.学号,b.课程编号,b.成绩 
from 成绩表 a, 成绩表 b 
where a.课程编号 != b.课程编号 
and a.成绩 = b.成绩 
and a.学号 !=b.学号

SELECT c.课程名, c.课程编号, st.学生名, s.成绩
from 课程表 c
left join 成绩表 s
on c.课程编号 = s.课程编号
left join 学生表 st
on s.学号 = st.学号
WHERE c.课程编号 = (
	SELECT 课程编号
	from 成绩表
	GROUP BY 课程编号
	ORDER BY count(1)
	limit 1
)

select c.c_name from course c where c.c_id in(
	-- 被选最多的课程的cid
	SELECT sc.c_id from score sc GROUP BY c_id
	-- 继续筛选
	having count(s_id) >= all(
		select count(s_id) from score sc GROUP BY c_id
	)
);