student(sno,sname,sage,ssex,sdept) course(cno,cname,credit,ctime) sc(sno,cno,grade) 1.建立学生表的insert触发器,若向学生表中插入一条记录,则自动向选课(sc)表中插入一行,令该生选一门选修人数最多的课。写sql语句
时间: 2024-03-02 17:52:06 浏览: 22
以下是向学生表中插入记录时自动向选课表中插入一行的 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;
```
该触发器会在向学生表中插入记录时自动执行,向选课表中插入一行数据,使该学生选修人数最多的尚未选修的课程。同时,更新选修课程的选修人数。
相关问题
STUDENT表有 sno sname ssex sage sdept,couse 有cno cname credit semester,sc有sno cno gradr,请使用SQL语句完成查询选修了生物信息学课程且成绩高于该门课程平均分数的学生的姓名。
SELECT s.sname
FROM student s, sc, couse c
WHERE s.sno = sc.sno
AND sc.cno = c.cno
AND c.cname = '生物信息学'
AND sc.grade > (
SELECT AVG(sc.grade)
FROM sc, couse
WHERE sc.cno = couse.cno
AND couse.cname = '生物信息学'
)
```
使用SQL命令创建创建以下三个关系: Student(Sno,Sname,Ssex,Sage,Sdept) Course (Cno, Cname, Cpno, Ccredit) SC(Sno, Cno, Grade)
好的,我可以为您提供SQL命令来创建这三个关系。请使用以下命令:
创建学生表:
```
CREATE TABLE Student (
Sno CHAR(10) PRIMARY KEY,
Sname VARCHAR(20) NOT NULL,
Ssex CHAR(2) NOT NULL,
Sage INT NOT NULL,
Sdept VARCHAR(20) NOT NULL
);
```
创建课程表:
```
CREATE TABLE Course (
Cno CHAR(10) PRIMARY KEY,
Cname VARCHAR(20) NOT NULL,
Cpno CHAR(10),
Ccredit INT NOT NULL
);
```
创建选课表:
```
CREATE TABLE SC (
Sno CHAR(10) REFERENCES Student(Sno),
Cno CHAR(10) REFERENCES Course(Cno),
Grade INT NOT NULL,
PRIMARY KEY (Sno, Cno)
);
```
这样就成功创建了三个关系表:Student、Course、SC。