Question

设教学数据库中有3个关系：学生关系 S（Sno, Sname, Sage, Ssex)、学习关系 SC (Sno, Cno, Grade)、课程

设教学数据库中有3个关系：学生关系 S（Sno, Sname, Sage, Ssex)、学习关系 SC (Sno, Cno, Grade)、课程关系 C(Cno, Cname, Teacher)。利用SQL完成以下查询： （1）查询学习课程号为C2的学生学号和成绩 （2）查询学习了课程C2的学生的学号和姓名 （3）查询学习了课程名为Maths的学生学号与姓名 （4）查询选修了课程号为C2或C4的学生的学号 （5）查询没有选修C2课程的学生姓名与年龄

Answer 1

1、select s.sno,s.sname,sc.grade

from c join sc on c.cno=sc.cno
join s on s.sno=sc.sno
where c.cno='c2'
2、
select distinct s.sno,s.sname

from c join sc on c.cno=sc.cno and c.cno=‘c2’
join s on s.sno=sc.sno
3、
select s.sno,s.sname

from c join sc on c.cno=sc.cno
join s on s.sno=sc.sno
where c.cname='Maths'
4、
select s.sno,s.sname

from c join sc on c.cno=sc.cno
join s on s.sno=sc.sno
where c.cno in('c2','c4')
5、
select s.sname,s.sage

from c join sc on c.cno=sc.cno
join s on s.sno=sc.sno
where c.cno<>'c2'

Answer 2

1）SELECT s.sno, sc.grade
FROM (s INNER JOIN sc ON s.sno = sc.sno) INNER JOIN c ON sc.cno = c.cno
WHERE (((c.cno)="c2"));
2）SELECT s.sno, s.sname
FROM (s INNER JOIN sc ON s.sno = sc.sno) INNER JOIN c ON sc.cno = c.cno
WHERE (((c.cno)="c2"));
3）SELECT s.sno, s.sname
FROM (s INNER JOIN sc ON s.sno = sc.sno) INNER JOIN c ON sc.cno = c.cno
WHERE (((c.cname)="maths"));
4）SELECT s.sno
FROM (s INNER JOIN sc ON s.sno = sc.sno) INNER JOIN c ON sc.cno = c.cno
WHERE (((c.cno)="c2")) OR (((c.cno)="c4"));
5）SELECT s.sname, s.sag
FROM (s INNER JOIN sc ON s.sno = sc.sno) INNER JOIN c ON sc.cno = c.cno
WHERE (((c.cno)<>"c2"));

Answer 3