1.求学生的学号、姓名、选修的课程名及成绩。
SELECT DISTINCT studentyyx.Sno,Sname,Cname,Grade
FROM studentyyx INNER JOIN scyyx ON studentyyx.Sno=scyyx.Sno
INNER JOIN courseyyx ON scyyx.Cno=courseyyx.Cno
2.求选修1号课程且成绩在90分以上的学生学号、姓名及成绩。
SELECT studentyyx.Sno,Sname,Grade
FROM studentyyx INNER JOIN scyyx ON studentyyx.Sno=scyyx.Sno
WHERE Cno='2' AND Grade>85
3.查询每一门课的先修课，包括课程号、课程名、先修课程号和先修课程名。
SELECT course1.Cno,course1.Cname,course2.Cno,course2.Cname
FROM course1,course2
WHERE course1.Cpno=course2.Cno
SELECT studentyyx.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM studentyyx LEFT OUTER JOIN scyyx ON studentyyx.Sno=scyyx.Sno
5.求课程的课程号、课程名和选修该课程的人数。
SELECT DISTINCT scyyx.Cno,Cname,COUNT(*)yyx
FROM courseyyx INNER JOIN scyyx ON courseyyx.Cno=scyyx.Cno
GROUP BY scyyx.Cno,Cname
6.求选修课超过2门课（包括2门）的学生学号、姓名。
SELECT scyyx.Sno,Sname
FROM studentyyx INNER JOIN scyyx ON studentyyx.Sno=scyyx.Sno
GROUP BY scyyx.Sno,Sname
HAVING COUNT(*)>=2
7.查询“刘1”的各门课程成绩，要求显示姓名、课程号和成绩
SELECT DISTINCT Sname AS xm,scyyx.Cno AS kch,Grade AS cj
FROM studentyyx,scyyx,courseyyx
WHERE studentyyx.Sno=scyyx.Sno AND scyyx.Cno=courseyyx.Cno AND Sname='刘1'
8.查询数学系“钱4”的“英语”这门课的成绩，要求显示姓名、成绩。
SELECT Sname as xm,Grade as cj
FROM scyyx,courseyyx,studentyyx
WHERE scyyx.Cno=courseyyx.Cno
AND scyyx.Sno=studentyyx.Sno
AND Cname='英语'
and Sdept='ma'
AND Sname='钱4'
9.查询各系男生的考试平均成绩。
SELECT Sdept as szx,AVG(Grade) as nspj
FROM scyyx,studentyyx
WHERE scyyx.Sno=studentyyx.Sno
AND Ssex='男'
GROUP BY Sdept
10.统计有两门及以上课程不及格的学生的姓名。
SELECT Sname
FROM studentyyx INNER JOIN scyyx ON studentyyx.Sno=scyyx.Sno
WHERE Grade<60
GROUP BY scyyx.Sno,Sname
HAVING COUNT(*)>=2
