(1)求学生的学号、姓名、选修的课程名及成绩;
(2)求选修1号课程且成绩在90 分以上的学生学号、姓名及成绩;
SELECT stuxym.Sno,Sname,grade
FROM stuxym,scxym
WHERE cno='1' and grade>90
(3)查询每一门课的先修课,包括课程号、课程名、先修课程号和先修课程名
SELECT coursexym.cno,coursexym.Cname,coursexym2.cno,coursexym2.Cname
FROM coursexym, coursexym2
WHERE coursexym.cno=coursexym2.cno
(4)查询每个学生的基本信息以及他(她)所选修的课程的课程号
SELECT stuxym.Sno,Sname,Ssex,Sage,sdept,Cno,grade
FROM stuxym,scxym
WHERE stuxym.Sno=scxym.sno
(5)求课程的课程号、课程名和选修该课程的人数;
SELECT scxym.cno,cname, count(*) 选修人数
FROM coursexym INNER JOIN scxym ON coursexym.cno=scxym.cno
GROUP BY scxym.cno, cname
(6)求选修课超过2门课(包括2门)的学生学号、姓名。
SELECT scxym.sno,sname
FROM stuxym INNER JOIN scxym ON stuxym.Sno=scxym.sno
GROUP BY scxym.sno,sname
HAVING count(*)>=2
(7)查询"李勇"的各门课程成绩,要求显示姓名、课程名称和成绩。
select Sname as 姓名,cpno as 课程号,grade as 成绩
from stuxym,scxym,coursexym
where Sname=‘李勇’ and stuxym.Sno=scxym.sno and scxym.Cno=coursexym.cno
(8)查询数学系"刘晨"的"数据库"这门课的成绩,要求显示姓名、成绩。
select sname as 姓名,grade as 成绩
from scxym,coursexym,stuxym
where scxym.Cno=coursexym.cno
and scxym.sno=stuxym.Sno
and cname='数据库'
and Sname='刘晨'
(9)查询各系男生的考试平均成绩
select sdept as 所在系,avg(grade) as 男生平均成绩
from scxym,stuxym
where scxym.sno=stuxym.Sno
and Ssex='男'
group by sdept
(10)统计有两门及以上课程不及格的学生的姓名。
SELECT sname
FROM stuxym INNER JOIN scxym ON stuxym.Sno=scxym.sno
where grade<60
GROUP BY scxym.sno,sname
HAVING count(*)>=2