sql 根据s、c和sc表完成如下查询: s(sno,sname,age,sex,sdept,major) c(cno,cnamecpo,teacher,credit) sc(sno,cno,grade) 1要求用子查询完成 (1)查询选修了课程“C01”的学生的姓名和所在系。 (2)查询数学系成绩在80分以上的学生的学号，姓名。 2查询每个学生的情况以及他(她)所选修的课程。 3求学生的学号、姓名、选修的课程名及成绩。 4求选修数学课且成绩为90分以上的学生学号、姓名、及成 绩。 5查询每

1.
(1) SELECT s.sname, s.sdept FROM s WHERE s.sno IN (SELECT sc.sno FROM sc WHERE sc.cno = 'C01');
(2) SELECT s.sno, s.sname FROM s WHERE s.sdept = '数学系' AND s.sno IN (SELECT sc.sno FROM sc WHERE sc.grade >= 80);

2. SELECT s.sno, s.sname, s.age, s.sex, s.sdept, s.major, c.cname FROM s LEFT JOIN sc ON s.sno = sc.sno LEFT JOIN c ON sc.cno = c.cno;

3. SELECT s.sno, s.sname, c.cname, sc.grade FROM s LEFT JOIN sc ON s.sno = sc.sno LEFT JOIN c ON sc.cno = c.cno;

4. SELECT s.sno, s.sname, sc.grade FROM s LEFT JOIN sc ON s.sno = sc.sno WHERE sc.grade >= 90 AND sc.cno IN (SELECT cno FROM c WHERE cname = '数学');

5. SELECT s.sno, s.sname, COUNT(sc.cno) AS course_num FROM s LEFT JOIN sc ON s.sno = sc.sno WHERE sc.cno IN (SELECT cno FROM c WHERE cname = '数学') GROUP BY s.sno, s.sname;