(1) 查询系为计算机的学生学号与姓名;
(2) 查询计算机系所开课程之课程号与课程名;
(3) 查询知道修读一门OS的学生姓名;
(4) 查询每个学生已选修课程门数和总平均成绩;
(5) 查询所有课程的成绩都在80分以上的学生姓名、学号并按学号顺序排列;
(6) 删除在S,SC中所有S#以‘91’开头的元组。
答:
(1) SELECT S#, Sn FROM S WHERE Sd=计算机
(2) SELECT C.C#, C.Cn FROM S, C, SC WHERE C.C#=SC.C# AND SC.S#=S.S# AND S.Sd=’计算机’
(3) SELECT S.Sn FROM S, C, SC WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.Cn=’OS’
(4) SELECT S#,COUNT(*), AVG(G) FROM SC GROUP BY S#
(5) SELECT S#, Sn FROM S WHERE S# IN (SELECT S# FROM SC GROUP BY S# HAVING MIN(G) ≥80) ORDER BY S#
(6) DELETE FROM SC WHERE S# LIKE ‘91%’ DELETE FROM S WHERE S# LIKE ‘91%’