--姓名,性别,年龄看是否有需要,有需要就加入,沒需要就刪掉 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;
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;
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;