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;