Question

用SQL语句实现：学生表、课程表、选课表三张表中的问题：

学生表：student（学号：sno、姓名：sname、性别：sgender、年龄：sage）
课程表：couse （课程编号：cno、课程名称：cname）
选课表：sc（学号：sno、课程编号：cno、成绩：marks）
问题一：查询选了”计算机原理“的学生学号和姓名？
问题二：查询”周星驰“同学选修的课程名字？
问题三：选秀了5门课程的学生学号和姓名？

Answer 1

第一个
select s.sno, s.sname
from student s, sc t
where s.sno = t.sno
and t.cno = (
select c.cno
from couse c
where c.cname = '计算机原理'
);
第二个：
select c.cname
from student s, couse c, sc t
where c.cno = t.cno and t.sno = s.sno and s.sname = '周星驰';
第三个：
select s.sno, s.sname
from student s
where s.sno in (
select t.sno
from sc t
group by t.sno
having count(t.cno) = 5
);
我已经在本地数据库建表测试过了，如果有什么问题，可以再联系我。

Answer 2

你那个couse表名字错误了吧，应该是course才对吧？答案如下

问题一：查询选了”计算机原理“的学生学号和姓名？
SELECT student.sno, student.sname
FROM student, sc, couse
WHERE student.sno = sc.sno
AND couse.cname = '计算机原理'
AND couse.cno = sc.cno

问题二：查询”周星驰“同学选修的课程名字？
SELECT cname
FROM couse
WHERE cno IN (SELECT sc.cno FROM sc, student
WHERE sc.sno = student.sno
AND student.sname = '周星驰')

问题三：选秀了5门课程的学生学号和姓名？
SELECT sno, sname
FROM student
WHERE sno IN (SELECT sno FROM sc
GROUP BY sno
HAVING COUNT(cno) = 5)

加油！取得答案后好好研究下自己掌握才是最重要的。

Answer 3

这些问题都不难的，在企业管理器里面稍微动手写一下，相信花点时间不难出来结果

问题一：

select sno,sname from student where sno in
(
select sno from sc where cno =
(
select cno from course where cname='计算机原理'
)
)

问题二：

select cname from course where cno in
(
select cno from sc where sno =
(
select sno from student where sname = '周星弛'
)
)

问题三：

select sno,sname from student where sno in
(
select sno from sc group by sno having count(*) = 5
)

Answer 4

问题一：select sno, sname from student
where sno in(select sno from sc
where cno =(select cno from couse
where cname="计算机原理"))
问题二：select cname from couse
where cno in(select cno from sc
where sno=(select sno from student
where sname ="周星驰"))
问题三：select sno, sname from student
where sno in(select sno from sc
where count(cno)=5

Answer 5

用子查询
select 姓名
from 学生,选课,课程
where 学生.学号=选课.学号 and 选课.课程号=课程.课程号 and 课程.课程名='数据库'
and 学生.学号 in
(select 学生.学号
from 学生,选课,课程
where 学生.学号=选课.学号 and 选课.课程号=课程.课程号 and 课程.课程名='操作系统';
}