数据库学习四

1. 创建学生成绩管理数据库（XSCJDB），并在该数据库中创建学生表Student(学号、姓名、性别、年龄、系别)、课程表Course(课程号、课程名、学分数、学时数)和成绩表SC(学号、课程号、成绩)，然后在每个表里添加若干条记录；

select*

from Student

select*

from SC;

select*

from Course;

（2）查询选修了2号课程且成绩在90分以上的学生学号和姓名；

select Sname,Student.Sno,Grade

from Student,SC

where Grade>90 and Student.Sno=SC.Sno and Cno='2';

（3）查询与“陈华”在同一个系的学生信息（嵌套查询）；

select *

from Student

where Sdept IN(

                 select Sdept

                   from Student

                   where Sname='陈华'

              )

and Sname<>'陈华';

（4）查询与“陈华”在同一个系的学生信息（自连接）；

select S1.Sname,S1.Sage,S1.Sdept,S1.Sno,S1.Ssex

from Student S1,Student S2

where S1.Sdept=S2.Sdept and S2.Sname='陈华'

and S1.Sname<>'陈华';

（5）查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄；

select Sname,Sage

from Student

where Sdept<>'CS' and Sage> all(

                                   select Sage

                                   from Student

                                   where Sdept='CS'

                               );

（6）查询选修了课程名为“数学”的学生学号和姓名；

select Student.Sno,Student.Sname,Cname,Grade

from SC,Student,Course

where  Student.Sno=SC.Sno and SC.Cno=Course.Cno and Cname='数学';

（7）查询既选修了课程1又选修了课程2的学生；

select Sname,Student.Sno,Ssex,Sage,Sdept

from SC,Student,Course

where SC.Cno='1' and Student.Sno=SC.Sno and SC.Cno=Course.Cno

intersect

select Sname,Student.Sno,Ssex,Sage,Sdept

from SC,Student,Course

where SC.Cno='2' and Student.Sno=SC.Sno and SC.Cno=Course.Cno;

（8）查询没有选修2号课程的学生姓名；

select Sname,Student.Sno,Ssex,Sage,Sdept

from SC,Student,Course

where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Student.Sname!=all(

                                                                                               select Sname

                                                                                               from SC,Student,Course

                                                                                               where SC.Cno='2' and Student.Sno=SC.Sno and SC.Cno=Course.Cno

                                                                                             );

（9）查询每个学生及其选修课情况（等值连接）；

select Student.*,SC.*

from Student,SC

where Student.Sno=SC.Sno;

（10）查询每个学生及其选修课情况（外连接）；

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

from Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);

（11）查询每个学生的学号、姓名、选修的课程及成绩（多表连接）。

select Sname,Student.Sno,SC.Cno,Grade

from SC,Student,Course

where Student.Sno=SC.Sno and SC.Cno=Course.Cno;