5.11
数据库实验报告一
CREATE TABLE student (
sno Char(8) PRIMARY KEY,
sname Varchar(20) NULL,
sex Char(2) NULL,
sdept Varchar(20) NULL
);
CREATE TABLE course (
cno Char(6) PRIMARY KEY,
cname Varchar(20) NULL,
);
CREATE TABLE sc (
sno Char(8),
cno Char(6),
grade int,
PRIMARY KEY (sno, cno),
CONSTRAINT FK_students FOREIGN KEY (sno) REFERENCES student(sno),
CONSTRAINT FK_course FOREIGN KEY (cno) REFERENCES course(cno)
);
go
INSERT INTO student (sno, sname, sex, sdept)
VALUES ('20050101', '王飞', '男', '计算机系');
INSERT INTO student (sno, sname, sex, sdept)
VALUES ('20050102', '李丽', '女', '信息系');
go
INSERT INTO course (cno,cname)
VALUES ('01','数据结构');
INSERT INTO course (cno,cname)
VALUES ('02','数据库原理');
INSERT INTO sc (sno,cno,grade)
VALUES ('20050101','02','70');
INSERT INTO sc (sno,cno,grade)
VALUES ('20050102','01','90');
CREATE Table c_avg_grade(
cno Char(6),
avg_grade Decimal(5, 2)
)
Insert INTO c_avg_grade(cno,avg_grade)
SELECT
cno ,
AVG(grade) AS avg_grade
FROM sc
GROUP BY cno;
UPDATE sc
SET grade = 85
WHERE sno='20050101' and cno='02';
DELETE FROM sc WHERE sno = '20050102' AND cno = '02';
UPDATE sc
SET grade = 0
WHERE cno = '数据库原理';
DELETE FROM sc
WHERE sno = '李丽';
DELETE FROM student;
DELETE FROM course;
DELETE FROM sc;
DELETE FROM c_avg_grade;
DROP TABLE student;
DROP TABLE course;
DROP TABLE sc;
DROP TABLE c_avg_grade;
DROP DATABASE stumanage1;