SQL啊

Question

student表
Sno     Sname                Ssex Sage   Clno
------- -------------------- ---- ------ -----
2000101 李勇                   男    20     00311
2000102 刘诗晨                  女    19     00311
2000103 王一鸣                  男    20     00312
2000104 张婷婷                  女    21     00312
2001101 李勇敏                  女    19     01311
2001102 贾向东                  男    22     01311
2001103 陈宝玉                  男    20     01311
2001104 张逸凡                  男    21     01311
2001110 队长                   男    20     00311Grade表
Sno     Cno  G
------- ---- ---------------------------------------
2000101 1    92
2000101 3    88
2000101 5    86
2000102 1    NULL
2000102 6    55
2000103 3    65
2000103 6    78
2000103 5    66
2000104 1    54
2000104 6    NULL
2001101 2    70
2001101 4    65
2001102 2    70
2000103 1    90
2000103 2    83
2000103 4    76
2000103 7    56
2001103 2    83course表
Cno  Cname                Credit
---- -------------------- ------
1    数据库                  4
2    离散数学                 3
3    管理信息系统               2
4    操作系统                 4
5    数据结构                 4
6    数据处理                 2
7    C语言                  4class表
Clno  Speciality           Inyear Number      Monitor
----- -------------------- ------ ----------- -------
00311 计算机软件                2000   120         2000101
00312 计算机应用                2000   140         2000103
01311 计算机软件                2001   220         2001103帮忙写下这个问题的查询语句吧，这句搞的我累死了，我刚学这玩意，大家帮帮忙呗
题：找出2000级计算机软件班的成绩为空的学生姓名。

Answer 1

use tempdb;
/*
create table student
(
Sno nvarchar(10) not null,
Sname nvarchar(10) not null,
Ssex nvarchar(10) not null,
Sage int,
Clno nvarchar(10) not null
);
insert into student(Sno,Sname,Ssex,Sage,Clno)
values
('2000101','李勇','男',20,'00311'),
('2000102','刘诗晨','女',19,'00311'),
('2000103','王一鸣','男',20,'00312'),
('2000104','张婷婷','女',21,'00312'),
('2001101','李勇敏','女',19,'01311'),
('2001102','贾向东','男',22,'01311'),
('2001103','陈宝玉','男',20,'01311'),
('2001104','张逸凡','男',21,'01311'),
('2001110','队长','男',20,'00311');create table Grade
(
Sno nvarchar(10) not null,
Cno int not null,
G int
);
insert into Grade(Sno,Cno,G)
values
('2000101',1,92),
('2000101',3,88),
('2000101',5,86),
('2000102',1,NULL),
('2000102',6,55),
('2000103',3,65),
('2000103',6,78),
('2000103',5,66),
('2000104',1,54),
('2000104',6,NULL),
('2001101',2,70),
('2001101',4,65),
('2001102',2,70),
('2000103',1,90),
('2000103',2,83),
('2000103',4,76),
('2000103',7,56),
('2001103',2,83);create table course
(
Cno int not null,
Cname nvarchar(10) not null,
Credit int not null
);
insert into course(Cno,Cname,Credit)
values
(1,'数据库',4),
(2,'离散数学',3),
(3,'管理信息系统',2),
(4,'操作系统',4),
(5,'数据结构',4),
(6,'数据处理',2),
(7,'C语言',4);create table class
(
Clno nvarchar(10) not null,
Speciality nvarchar(10) not null,
Inyear int not null,
Number int not null,
Monitor nvarchar(10) not null
);
insert into class(Clno,Speciality,Inyear,Number,Monitor)
values
('00311','计算机软件',2000,120,'2000101'),
('00312','计算机应用',2000,140,'2000103'),
('01311','计算机软件',2001,220,'2001103');
*/
--找出2000级计算机软件班的成绩为空的学生姓名
select t2.Sname
from class as t1
join student as t2 on t1.Clno = t2.Clno
join Grade as t3 on t2.Sno = t3.Sno
where t1.Inyear = 2000 and t1.Speciality = '计算机软件'
and t3.G is null