--创建学生表
create table student(sno varchar2(20),sname varchar2(20));
--创建课程表
create table course(cno varchar2(20),cname varchar2(20));
--创建学生课程关联表
create table sc(sno varchar2(20),cno varchar2(20));
--初始化学生表数据
insert into student values('200215121','李勇');
insert into student values('200215122','刘晨');
insert into student values('200215123','王敏');
insert into student values('200215124','张立');
--初始化课程表数据
insert into course values('1','数据库');
insert into course values('2','数学');
insert into course values('3','信息系统');
insert into course values('4','操作系统');
insert into course values('5','数据结构');
insert into course values('6','数据处理');
insert into course values('7','PASCAL语言');
--初始化学生,课程表数据
insert into sc values('200215121','1');
insert into sc values('200215121','2');
insert into sc values('200215121','3');
insert into sc values('200215121','4');
insert into sc values('200215121','5');
insert into sc values('200215121','6');
insert into sc values('200215121','7');
insert into sc values('200215121','2');
insert into sc values('200215121','3');
insert into sc values('200215122','4');
insert into sc values('200215122','5');
--查询选修了课程号为1的学生姓名
--方法一:
select sno from student
where
exists
(select * from sc where student.sno = sno and cno = '1')
--方法二:
select student.sno
from student,sc
where student.sno = sc.sno and sc.cno = '1';
--方法三
select student.sname from student
where student.sno in
(select sc.sno from sc where sc.cno = '1');
--查询没有选修课程1的学生姓名
select sname from student
where not exists(select * from sc where student.sno = sc.sno and sc.cno = '1')
--查询选修全部课程的学今生姓名
--方法一:
select Sname
from Student
where not exists
(
select *
from Course
where not exists
(
select *
from SC
where Sno=Student.Sno AND
Cno=Course.Cno
) );
--方法二:
select student.sno
from student where student.sno in
(select sc.sno from sc group by sc.sno having count(*) = (select count(*) from course));
sql exsits 用法
最新推荐文章于 2024-07-16 22:01:53 发布