1. select c#,cname from c join (select count(s#),c# from sc group by c# having count(s#)=(select count(*) from s)) a on c.c#=a.c#
2. select distinct s# from (select s# from sc a join c on a.c#=c.c# where c.teacher='liu') b
2. select distinct s# from (select s# from sc a join c on a.c#=c.c# where c.teacher='liu') b
from c
where c# in (select c# from sc)
select *
from s
where s# in (select s#
from sc
where c# in (select C#
from c
where teacher = 'liu'))
select a.课程号,a.课程名 from sc a,s b where a.学号=b.学号
2.
先找出liu老师所授的所有课程号
select 课程号 from c where 任課老师='liu老师'
再根据以上结果从sc表中找出对应课程的选修者的学号,所以综合起来就是:
select a.学号 from sc a,(select 课程号 from c where 任課老师='liu老师') b
where a.课程号=b.课程号注:a,b分别为别名
select *
from c
where c# in (select c#
from sc
where exists (select * from s where s# = sc.s#)
and
exists (select * from c where c# = sc.c#)
)2.
select *
from s
where s# in (select s#
from sc
where exists (select 1
from c
where c# = sc.c# and teacher = 'liu'))
select *
from c
where c# in (select c#
from sc
where exists (select * from s where s# = sc.s#)
and
exists (select * from c where c# = sc.c#)
)--2.
select *
from s
where s# in (select s#
from sc
where exists (select *
from c
where c# = sc.c# and teacher = 'liu'))
c#,
cname
FROM c
WHERE c# IN
(SELECT c# --获取所有学生都参加的课程
FROM sc
GROUP BY c#
WHERE COUNT(s#)
IN
( SELECT COUNT(*) --获取共有多少学生
FROM s ))
2。检索选修课程包含liu老师所授课程 的学生的学号 SELECT DISTINCT s#
FROM sc
WHERE c# IN
(SELECT c# --获取liu老师所授课程号
FROM C
WHERE teacher IS LIKE '%liu%')
score.score,
student.student_name,
subject.object_name,
subject.teacher
FROM score,
student,
subject
WHERE ( score.student_id = student.student_id ) and
( subject.object_id = score.object_id ) and
( ( subject.teacher = '张老师' ) )