student(sno,sname,sage,ssex,sdept) course(cno,cname,credit,ctime) sc(sno,cno,grade) 1．建立学生表的insert触发器，若向学生表中插入一条记录，则自动向选课(sc)表中插入一行，令该生选一门选修人数最多的课。写sql语句

以下是向学生表中插入记录时自动向选课表中插入一行的 SQL 语句：

CREATE TRIGGER insert_sc AFTER INSERT ON student
FOR EACH ROW
BEGIN
  INSERT INTO sc (sno, cno, grade)
  SELECT NEW.sno, cno, NULL
  FROM (
    SELECT cno, COUNT(*) AS num
    FROM sc
    GROUP BY cno
    ORDER BY num DESC
    LIMIT 1
  ) AS t
  WHERE cno NOT IN (
    SELECT cno
    FROM sc
    WHERE sno = NEW.sno
  );
  UPDATE course
  SET num = num + 1
  WHERE cno = (
    SELECT cno
    FROM (
      SELECT cno, COUNT(*) AS num
      FROM sc
      GROUP BY cno
      ORDER BY num DESC
      LIMIT 1
    ) AS t
  );
END;

该触发器会在向学生表中插入记录时自动执行，向选课表中插入一行数据，使该学生选修人数最多的尚未选修的课程。同时，更新选修课程的选修人数。

相关问题

利用sql server语言在下列表中创建学生选课成绩信息视图(sno,sname,sdept,cno,cname,grade)和创建教学基本信息视图（cno,cname,ctime,clocation,tno,tname,ttitle）; 学生表stu (sno,sname ,ssex,sage ,sdept) 课程表course (cno，cname,clocation ) 选课表score （sno，cno,grade) 教师表teacher( tno，tname，ttitle) 教学表teaching(cno，tno，ctime，clocation）

创建学生选课成绩信息视图：

CREATE VIEW student_score AS
SELECT stu.sno, stu.sname, stu.sdept, score.cno, course.cname, score.grade
FROM stu
INNER JOIN score ON stu.sno = score.sno
INNER JOIN course ON score.cno = course.cno;

创建教学基本信息视图：

CREATE VIEW teaching_info AS
SELECT teaching.cno, course.cname, teaching.ctime, teaching.clocation, teacher.tno, teacher.tname, teacher.ttitle
FROM teaching
INNER JOIN course ON teaching.cno = course.cno
INNER JOIN teacher ON teaching.tno = teacher.tno;

以上代码是针对SQL Server语言的，如果需要在其他数据库中创建视图，语法可能会有所不同。

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;

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