计算机软件实验报告 - 图文(3)

2.创建对象：Stu，Course，Score。

CREATE TABLE Stu( );

CREATE TABLE Course( );

CREATE TABLE Score( );

Sno CHAR(4), Cno CHAR(2),

PRIMARY KEY(Sno,Cno), Grade NUMERIC,

Cno CHAR(2)PRIMARY KEY, Cname CHAR(10), Chour NUMERIC,

Sno CHAR(4)PRIMARY KEY, Sname CHAR(10), Sex CHAR(2), Age NUMERIC, BirthDay DATETIME, Class CHAR(10),

3.插入信息

Insert into Stu(Sno,Sname,Sex,Age,BirthDay,Class) values ('2561','王五','wm','10','1990-03-30','电子1002'); Insert into Stu(Sno,Sname,Sex,Age,BirthDay,Class) values ('2562', '李四','m','21','1992-03-23','电子1001'); Insert into Stu(Sno,Sname,Sex,Age,BirthDay,Class) values ('2563','陈七','wm','25','1987-05-14','电子1002'); Insert into Stu(Sno,Sname,Sex,Age,BirthDay,Class) values ('2564','赵八','m','20','1993-12-24','电子1002'); Insert into Stu(Sno,Sname,Sex,Age,BirthDay,Class)

values ('2565','陆游','m','20','1978-12-5','电子1002'); Select Sno,Sname,Sex,Age,BirthDay,Class from Stu;

Insert into Course (Cno,Cname,Chour) values ('01','语文','32');

Insert into Course (Cno,Cname,Chour) values ('02','数学','64');

Insert into Course (Cno,Cname,Chour) values ('03','英语','40');

Select Cno,Cname,Chour from Course;

Insert into Score (Sno,Cno,Grade) values ('2567','01','97');

Insert into Score (Sno,Cno,Grade) values ('2568','01','54');

Insert into Score (Sno,Cno,Grade) values ('2569','01','56');

Insert into Score (Sno,Cno,Grade) values ('2560','01','88');

Insert into Score (Sno,Cno,Grade) values ('2561','02','87');

Insert into Score (Sno,Cno,Grade) values ('2562','03','79');

Insert into Score (Sno,Cno,Grade) values ('2563','02','68');

Insert into Score (Sno,Cno,Grade) values ('2564','03','58');

Insert into Score (Sno,Cno,Grade) values ('2565','03','98');

Select Sno,Cno,Grade from Score;

1.查询学生出生日期(Sno, Sname, BirthDay)；

Select Sno,Sname,BirthDay from Stu;

2.按学号顺序查询一个班级的所有学生(Class, Sname)；

Select Class,Sname from Stu order by Sno;

3.列出学生选择各门课程的成绩(Sname, Cname, Grade) ；

Select Sname,Cname,Grade from Stu,Course,Score where Stu.Sno=Score.Sno and Course.Cno=Score.Cno;

4.列出有过不及格成绩的学生名单(Sno, Sname, Class);

Select distinct Stu.Sno,Sname,Class from Stu,Score where Stu.Sno=Score.Sno and Grade<60;

5.求学生的平均成绩和总成绩(Sname, 数学, 英语)；

Select Sname,avg(Grade) 数学,sum(Grade) 英语 from Stu,Score where Score.Sno=Stu.Sno group by Stu.Sname;