表 Course (课程信息表)
Cno(课程编号) Cname(课程名) Credit(课程学分)
1 数据库 3
2 操作系统 4
3 数据结构 4
4 离散数学 4
5 C++ 4表 Grade (成绩信息表)
Sno (学号) Cno(课程编号) G(成绩)
2000101 1 65
2000101 2 98
2000101 3 68
2000101 4 68
2000101 5 78
2000102 1 88
2000102 4 56
2000102 5 36
2000103 3 64
2000103 2 79问题:
(1)找出选修了全部课程的学生学号
(2)给定课程号集合,找出选修了这些课程号的学生学号。例如给定课程号集合为(1,4,5)那么满足条件的学号就为2000101,2000102。
谢谢各位!
Cno(课程编号) Cname(课程名) Credit(课程学分)
1 数据库 3
2 操作系统 4
3 数据结构 4
4 离散数学 4
5 C++ 4表 Grade (成绩信息表)
Sno (学号) Cno(课程编号) G(成绩)
2000101 1 65
2000101 2 98
2000101 3 68
2000101 4 68
2000101 5 78
2000102 1 88
2000102 4 56
2000102 5 36
2000103 3 64
2000103 2 79问题:
(1)找出选修了全部课程的学生学号
(2)给定课程号集合,找出选修了这些课程号的学生学号。例如给定课程号集合为(1,4,5)那么满足条件的学号就为2000101,2000102。
谢谢各位!
解决方案 »
- 一个getdate函数搞得我快要崩溃了,谁帮我看一下,100分!
- 问一下问题,关于row_number的效率问题
- 奇了,我同时对数据库中的一个表进行了导入数据和查询
- 不用重新查询数据库可以得到主键值?
- 一个datetime型的变量@zdate ,内容是2003-09-06,经过CAST(@zdate AS VARCHAR(10))变换以后怎么变成09 06 2003了呢?顺序变了,我想要原
- 关于replace!
- Can you help me?
- 还是关于存储过程与text类型之间关系的问题
- 请大家进来看看!
- 如何把mdf和日志文件导入SQL?
- SQL导出EXCEL乱序问题
- 怎么修改SQL Server的默认端口?
select distinct Sno
from Grade
where Cno in(1,4,5)
insert into Course select 1,'數據庫',3
insert into Course select 2,'操作系統',4
insert into Course select 3,'數據結構',4
insert into Course select 4,'離散數學',4
insert into Course select 5,'C++',4create table Grade (Sno varchar(10),Cno int,G int)
insert into Grade select '2000101',1,65
insert into Grade select '2000101',2,98
insert into Grade select '2000101',3,68
insert into Grade select '2000101',4,68
insert into Grade select '2000101',5,78
insert into Grade select '2000102',1,88
insert into Grade select '2000102',4,56
insert into Grade select '2000102',5,36
insert into Grade select '2000103',3,64
insert into Grade select '2000103',2,79--1.
select Sno from Grade group by Sno having count(Cno)=(select count(*) from Course)--2.
declare @s varchar(10),@sql varchar(1000),@n varchar(10)
set @sql=''
set @s='1,4,5'
set @n=@s
while patindex('%[0-9]%',@n)>0
set @n=stuff(@n,patindex('%[0-9]%',@n),1,'')
set @sql='select Sno from Grade where Cno in ('+@s+') group by Sno having count(Sno)=(len('''+@n+''')+1)'
exec(@sql)drop table Course
drop table Grade/*result-1:*/
Sno
----------
2000101/*result-2:*/
Sno
----------
2000101
2000102
select distinct c.sno from sgrade c,course a where a.cno not in
(select distinct b.cno from sgrade b where b.sno=c.sno))
select distinct Sno
from Grade
where Cno in(1,2,3,4,5)
insert into Course select 1,'數據庫',3
insert into Course select 2,'操作系統',4
insert into Course select 3,'數據結構',4
insert into Course select 4,'離散數學',4
insert into Course select 5,'C++',4create table Grade (Sno varchar(10),Cno int,G int)
insert into Grade select '2000101',1,65
insert into Grade select '2000101',2,98
insert into Grade select '2000101',3,68
insert into Grade select '2000101',4,68
insert into Grade select '2000101',5,78
insert into Grade select '2000102',1,88
insert into Grade select '2000102',4,56
insert into Grade select '2000102',5,36
insert into Grade select '2000103',3,64
insert into Grade select '2000103',2,791.select distinct sno from grade a where not exists (select * from course where not exists (select * from grade where cno=course.cno and sno=a.sno))
最里层的查询是找出该学生所选的全部课程
中间层的查询找出该学生没有学的课程
外层的查询的意思是:不存在该学生还有没有选的课程=找出选了所有课程的学生
2.select distinct sno from grade where cno in (1,4,5)