17,090
社区成员
发帖
与我相关
我的任务
分享
create table student(
id char(5) primary key, ------- 学号 主键
name varchar(255) not null, -------姓名
sex char(5) default 'MAN' constraint ck_sex check (sex in ('MAN','WOMEN')), --------性别
age number(3) not null ---------年龄
)
create table Course(
Student_id char(5), ---------学号
id number(1), ---------科目编号
name varchar(15) not null, --------- 科目名称
score number(3) not null, -----------成绩
constraint pk_stuid primary key (Student_id,id) -----设定主键
)
select * from student;
select * from course;
insert into student values(1,'zhangsan','MAN',22);
insert into student values(2,'lisi','WOMEN',22);
insert into student values(3,'wangwu','MAN',22);
INSERT INTO course values(1,001,'语文',60);
INSERT INTO course values(1,002,'数学',70);
INSERT INTO course values(1,003,'英语',80);
INSERT INTO course values(2,001,'语文',60);
INSERT INTO course values(2,002,'数学',80);
INSERT INTO course values(3,001,'语文',60);
INSERT INTO course values(3,003,'英语',90);
INSERT INTO course values(3,004,'物理',90);
--查找所有学生的科目总分大于400的学生名单 及总分
select a1.name,sum(score) "总分"
from student a1,course a2
where a1.id=a2.Student_id group by a1.name having sum(a2.score)>=240;
--姓名,性别,年龄看是否有需要,有需要就加入,沒需要就刪掉
select S.id 学号,min(S.name) 姓名,min(S.sex) 性别,min(S.age) 年龄,
sum(C.score) 科目总分
from Student S,Course C
where S.id=C.Student_id
group by S.id
having sum(C.score)>400;