数据库

实验一

 

1.建表

CREATE TABLE student
(
    sno   Char(8)     NOT NULL PRIMARY KEY,
    sname Varchar(20) NOT NULL,
    sex   Char(2)     NOT NULL,
    sdept Varchar(20) NOT NULL
);

create table course(
    cno char(6) not null primary key ,
    cname varchar(20)

);


CREATE TABLE sc (
                    sno CHAR(8) NOT NULL,
                    cno CHAR(6) NOT NULL,
                    grade INT NOT NULL,
                    PRIMARY KEY (sno, cno),
                    FOREIGN KEY (sno) REFERENCES student(sno),
                    FOREIGN KEY (cno) REFERENCES course(cno)
);

2.插入数据

INSERT INTO student (sno, sname, sex, sdept) VALUES
('20050101', '王飞', '男', '计算机系'),
('20050102', '李丽', '女', '信息系');

insert into course(cno,cname)values ('01','数据库结构'),
                                    ('02','数据库原理');
insert into sc(sno, cno, grade) value ('20050101','01','70'),
    ('20050102','02','90');

3.对每一门课，求学生的平均成绩，并把结果以基本表的形式存入数据库

CREATE TABLE course_avg_grade AS
SELECT c.cno, c.cname, AVG(s.grade) AS avg_grade
FROM course c
         JOIN sc s ON c.cno = s.cno
GROUP BY c.cno, c.cname;

4.

修改sc表中sno为20050102、cno为02的记录的grade属性值为85，然后将该条记录删除。将此两条SQL语句写入实验报告。

update sc set grade=85 where sno='20050102'and cno='02';
delete from sc where sno='20050102' and cno ='02';

5.

CREATE TABLE course_avg_grade AS
SELECT c.cno, c.cname, AVG(s.grade) AS avg_grade
FROM course c
         JOIN sc s ON c.cno = s.cno
GROUP BY c.cno, c.cname;

update sc set grade=85 where sno='20050102'and cno='02';
delete from sc where sno='20050102' and cno ='02';

UPDATE sc SET grade = 0 WHERE cno = '02';
DELETE FROM sc WHERE sno = '20050102';

DELETE FROM sc;
DELETE FROM course;
DELETE FROM student;

DROP TABLE sc;
DROP TABLE course;
DROP TABLE student;