student(sno,sname,sage,ssex,sdept) course(cno,cname,credit,ctime) sc(sno,cno,,grade) 1 根据上面基本表的信息定义视图显示每门课的课名、选课人数、平均成绩 3 利用视图，查询平均成绩最高的学生。

1. 创建视图：

CREATE VIEW course_stats AS
SELECT c.cname, COUNT(s.sno) AS num_students, AVG(sc.grade) AS avg_grade
FROM course c
LEFT JOIN sc ON c.cno = sc.cno
LEFT JOIN student s ON s.sno = sc.sno
GROUP BY c.cno, c.cname;

2. 查询平均成绩最高的学生：

SELECT s.sno, s.sname, AVG(sc.grade) AS avg_grade
FROM student s
LEFT JOIN sc ON s.sno = sc.sno
WHERE s.sno IN (
  SELECT sno
  FROM sc
  WHERE cno IN (
    SELECT cno
    FROM course_stats
    WHERE avg_grade = (
      SELECT MAX(avg_grade)
      FROM course_stats
    )
  )
)
GROUP BY s.sno, s.sname
ORDER BY AVG(sc.grade) DESC
LIMIT 1;

注意：这里使用了子查询和嵌套查询，可以根据实际情况对查询进行优化。