[sql]代码库
drop table sc;
drop table student;
drop table course;
--学生表
create table student(
id number(4) primary key,
name varchar2(10)
);
--课程
create table course(
id number(4) primary key,
name1 varchar2(20)
);
--课程表
create table sc(
sid number(4) references student(id),
cid number(4) references course(id),
grade number(4,2)
);
insert into student
select 1,'小何' from dual union
select 2,'小凳' from dual union
select 3,'小宝' from dual union
select 4,'小飞' from dual;
insert into course values(1,'java');
insert into course values(2,'c#');
insert into course values(3,'sql');
insert into course values(4,'php');
insert into sc
select 1,1,98 from dual union
select 2,1,95 from dual union
select 4,1,88 from dual union
select 1,2,78 from dual union
select 3,2,99 from dual union
select 4,2,89 from dual union
select 1,3,76 from dual union
select 4,3,84 from dual union
select 1,4,91 from dual union
select 2,4,73 from dual;
--查询选修所有课程的学生
select name from student where id in(select sid from sc group by
sid having count(*)=(select count(*) from course));
--查询选修两门课程以上的学生
select name from student where id in(select sid from sc group by sid having count(*)>=2);
by: 发表于:2017-09-28 14:48:15 顶(0) | 踩(0) 回复
??
回复评论