本题目要求编写SQL语句， 在cou和sc中查询每个同学超过他选修的平均成绩的课程。 请使用： 1）用相关子查询实现 2）使用派生表实现。

要完成这个任务，你可以使用两种方法：

1. **使用相关子查询实现**[^1]:

   SELECT a.sno AS 学号, c.cname AS 课程名, a.grade AS 成绩
   FROM sc a
   JOIN cou ON a.cno = cou.cno
   WHERE a.grade > (SELECT AVG(b.grade) 
                    FROM sc b 
                    WHERE a.sno = b.sno)
   ORDER BY a.grade;

2. **使用派生表实现**:
首先创建一个派生表存储每个学生的平均成绩，然后再与原始表联接：

   CREATE TABLE avg_grades AS
   SELECT sno, AVG(grade) as avg_grade
   FROM sc
   GROUP BY sno;

   SELECT a.sno AS 学号, c.cname AS 课程名, a.grade AS 成绩
   FROM sc a
   JOIN cou ON a.cno = cou.cno
   JOIN avg_grades bg ON a.sno = bg.sno AND a.grade > bg.avg_grade
   ORDER BY a.grade;

相关问题

本题目要求编写SQL语句， 查询每个同学超过他选修的平均成绩的课程。 请使用： 1）用相关子查询实现 2）使用派生表实现。 CREATE TABLE `cou` ( `cno` char(4) NOT NULL, `cname` varchar(30) NOT NULL, `credit` smallint(6) DEFAULT NULL, `ptime` char(5) DEFAULT NULL, `teacher` char(10) DEFAULT NULL, PRIMARY KEY (`cno`) ) CREATE TABLE `sc` ( `sno` char(4) NOT NULL, `cno` char(4) NOT NULL, `grade` decimal(6,1) DEFAULT NULL, PRIMARY KEY (`sno`,`cno`), CONSTRAINT `fk_sc_cno` FOREIGN KEY (`cno`) REFERENCES `cou` (`CNO`), );

使用相关子查询实现：

SELECT s.sname, c.cname, sc.grade
FROM sc
JOIN cou c ON sc.cno = c.cno
JOIN (SELECT sno, AVG(grade) AS avg_grade FROM sc GROUP BY sno) AS avg_sc ON sc.sno = avg_sc.sno
JOIN student s ON sc.sno = s.sno
WHERE sc.grade > avg_sc.avg_grade
ORDER BY s.sno, c.cno;

使用派生表实现：

SELECT s.sname, c.cname, sc.grade
FROM sc
JOIN cou c ON sc.cno = c.cno
JOIN (SELECT sno, AVG(grade) AS avg_grade FROM sc GROUP BY sno) AS avg_sc ON sc.sno = avg_sc.sno
JOIN student s ON sc.sno = s.sno
JOIN (SELECT sno, cno, grade FROM sc) AS all_grade ON sc.sno = all_grade.sno AND sc.cno = all_grade.cno
WHERE all_grade.grade > avg_sc.avg_grade
ORDER BY s.sno, c.cno;

本题目要求编写SQL语句， 检索出没有选修'C语言'课程的学生记录，输出结果集按照学号升序排序。 要求使用not exist语句实现。 表结构: 请在这里写定义表结构的SQL语句。例如： CREATE TABLE `stu` ( `sno` char(4) NOT NULL, `sname` char(8) NOT NULL, `sex` tinyint(1) DEFAULT NULL, `mno` char(2) DEFAULT NULL, `birdate` datetime DEFAULT NULL, `memo` text, PRIMARY KEY (`sno`) ); CREATE TABLE `cou` ( `cno` char(4) NOT NULL, `cname` varchar(30) NOT NULL, `credit` smallint(6) DEFAULT NULL, `ptime` char(5) DEFAULT NULL, `teacher` char(10) DEFAULT NULL, PRIMARY KEY (`cno`) ) ; CREATE TABLE `sc` ( `sno` char(4) NOT NULL, `cno` char(4) NOT NULL, `grade` decimal(6,1) DEFAULT NULL, PRIMARY KEY (`sno`,`cno`), CONSTRAINT `fk_sc_cno` FOREIGN KEY (`cno`) REFERENCES `cou` (`CNO`), CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`) );

CREATE TABLE `stu` (
`sno` char(4) NOT NULL,
`sname` char(8) NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
`mno` char(2) DEFAULT NULL,
`birdate` datetime DEFAULT NULL,
`memo` text,
PRIMARY KEY (`sno`)
);

CREATE TABLE `cou` (
`cno` char(4) NOT NULL,
`cname` varchar(30) NOT NULL,
`credit` smallint(6) DEFAULT NULL,
`ptime` char(5) DEFAULT NULL,
`teacher` char(10) DEFAULT NULL,
PRIMARY KEY (`cno`)
);

CREATE TABLE `sc` (
`sno` char(4) NOT NULL,
`cno` char(4) NOT NULL,
`grade` decimal(6,1) DEFAULT NULL,
PRIMARY KEY (`sno`,`cno`),
CONSTRAINT `fk_sc_cno` FOREIGN KEY (`cno`) REFERENCES `cou` (`CNO`),
CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
);

SELECT sno, sname
FROM stu
WHERE NOT EXISTS (
SELECT *
FROM sc
WHERE stu.sno = sc.sno AND cno = 'C语言'
)
ORDER BY sno ASC;