** oracle 10.2.1,PL/SQL**
具体要求: _ 查询学分修满25分的同学姓名和学分详情_
表结构,忽略约束条件
--学生信息表:student_info
create table student_info
(
stuid int, --学号
stuname varchar2(20), --姓名
stusex char(4), --性别
birthday date default (sysdate), --出生年月
cellphone int, --手机号
address varchar2(40), --家庭地址
Email varchar2(20), --邮箱
classno int --所属班级
);
commit;
--课程信息表:course_info
create table course_info
(
cno varchar2(10), --课程编号
cname varchar2(20), --课程名称
cstart int, --课程开始学期
cperiod int, --课时
ccredits number(2,1) --学分
);
commit;
--成绩信息表: score_info
create table score_info
(
stuid int, --学号
cno varchar2(10), --课程编号
score int --成绩
);
commit;
- select *
select *
from (student_info natural join score_info) natural join course_info
where stuid in (select stuid
from score_info natural join course_info
group by stuid
having sum(ccredits)>=25
);
- select stuname,ccredits
select stuname,ccredits
from (student_info natural join score_info) natural join course_info
where stuid in (select stuid
from score_info natural join course_info
group by stuid
having sum(ccredits)>=25
);
- 当改为下面语句时正常
select stuname,ccredits
from ((select *
from (student_info natural join score_info) natural join course_info
where stuid in (select stuid
from score_info natural join course_info
group by stuid
having sum(ccredits)>=25
)
提问:如何解决 2例 中的重复问题?