22,210
社区成员
发帖
与我相关
我的任务
分享
--答:
--1.查询有参加课程名称为'JAVA'考试的学员学号和姓名
select c.course_name,b.std_no,b.std_name
from score a
left join student b on a.std_no=b.std_no
left join course c on a.course_no=c.course_no
where c.course_name='JAVA'
--2.查询没有参加课程编号为1003考试的学员姓名
select std_name
from student
where std_name not in
(
select b.std_name
from score a
left join student b on a.std_no=b.std_no
left join course c on a.course_no=c.course_no
where c.course_no='1003'
)
--3.查询参加了全部课程考试的学员姓名
select std_name
from
(select std_no,count(std_no) as scnt from score group by std_no) a join
(select count(1) as ccnt from course) b on scnt=ccnt join
student c on a.std_no=c.std_no
/*函数实现
alter function dbo.fun_q(@std_no int)
returns varchar(10)
as
begin
declare @c1 int,@c2 int,@str varchar(10)
select @c1=count(1)
from course
select @c2=count(std_no)
from score
where std_no=@std_no
if @c1=@c2
select @str=std_name from student where std_no=@std_no
return (@str)
end
go
select a as [name]
from
(
select dbo.fun_q(std_no)as a from student
) as a
where a is not null
*/
--4. 查询没有考试的学员人数
select count(1)
from student
where std_no not in
(
select std_no from score
)
--5. 找出没有参加"李明"老师讲授课程考试的所有学生姓名
select std_name from student where std_name not in
(
select b.std_name
from score a
left join student b on a.std_no=b.std_no
left join course c on a.course_no=c.course_no
where c.teacher='李明'
)
--6. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
select std_name,avg(mark) as avgmark
from score a left join student b on a.std_no=b.std_no
where b.std_name in
(
select case when count(a.std_no)>=2 then std_name end as std_name
from score a left join student b on a.std_no=b.std_no
where a.mark<60
group by a.std_no,std_name
)
group by std_name
--7. 列出既学过1001号课程,又学过1002号课程的所有学生姓名
select std_name
from score a left join student b on a.std_no=b.std_no
where a.std_no in
(
select std_no
from score
where course_no='1001'
)
and course_no='1002'
--8. 列出课程1001成绩比1002成绩高的所有学生的学号
--9. 列出课程1001成绩比1002成绩高的所有学生的学号及其1001和1002的成绩
select d.* from
(
select case when a.mark>b.mark then a.std_no end as sno
from
(select * from score where course_no='1001') a join
(select * from score where course_no='1002') b on a.std_no=b.std_no
) as c join score d on c.sno=d.std_no
where sno is not null and course_no in('1001','1002')