【数据库系统概论】实训（三）

使用数据库school，用SQL语句完成以下查询

1.实现两表的笛卡尔积运算

use school1
go

select  Student.Sno,Cno,Grade  from  student,sc  

S01  	C01  	92.0
S01  	C03  	84.0
S01  	C01  	90.0
S01  	C02  	94.0
S01  	C03  	82.0
S01  	C01  	72.0
S01  	C02  	90.0
S01  	C03  	75.0
S02  	C01  	92.0
S02  	C03  	84.0
S02  	C01  	90.0
S02  	C02  	94.0
S02  	C03  	82.0
S02  	C01  	72.0
S02  	C02  	90.0
S02  	C03  	75.0
S03  	C01  	92.0
S03  	C03  	84.0
S03  	C01  	90.0
S03  	C02  	94.0
S03  	C03  	82.0
S03  	C01  	72.0
S03  	C02  	90.0
S03  	C03  	75.0
S04  	C01  	92.0
S04  	C03  	84.0
S04  	C01  	90.0
S04  	C02  	94.0
S04  	C03  	82.0
S04  	C01  	72.0
S04  	C02  	90.0
S04  	C03  	75.0
S05  	C01  	92.0
S05  	C03  	84.0
S05  	C01  	90.0
S05  	C02  	94.0
S05  	C03  	82.0
S05  	C01  	72.0
S05  	C02  	90.0
S05  	C03  	75.0
S06  	C01  	92.0
S06  	C03  	84.0
S06  	C01  	90.0
S06  	C02  	94.0
S06  	C03  	82.0
S06  	C01  	72.0
S06  	C02  	90.0
S06  	C03  	75.0

2.实现两表的等值连接

use school1
go

select   Student.*, SC.*   from  student,sc  where   Sdept  =   '自动化'

3.实现两表的自然连接

use school1
go

select   Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade  from  student,sc  
      where   student.sno=Sc.Sno

4.实现多表的自然连接

use school1
go

select  Sname,Cname,Grade  from  student,sc ,course
       where   student.sno=Sc.Sno  and  Sc.Cno=Course.Cno  

5.查询王建平同学生选修的课程号和相应的考试成绩

use school1
go
SELECT SC.Cno,SC.Grade
FROM Student,SC
WHERE	  Student.Sname='王建平' and Student.Sno = SC.Sno

6.查询每一门课的间接先修课

use school1
go

select  A.Cno,A.Cname,B.Pre_Cno 
                 from  Course  A,Course  B
                 where  A.Pre_Cno =  B.Cno  and  B.Pre_Cno is not null 

7.查询同时选修了C01和C02课程的学生学号

use school1
go
select  Sno,Grade  from  SC  where  Cno=(select Cno from Course  where  Cname  like  '数据结构')

select  Sno,Grade  from  SC ,Course where  SC.Cno=Course.Cno and Cname  like  '数据结构'

8.查询成绩表中有记录同学的详细情况（用两种方式实现）

use school1
go
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE	Student.Sno=SC.Sno AND Grade IS NOT NULL

9.查询女学生的学号、姓名和各科考试成绩

use school1
go
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE	Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Ssex='女'

如果改成男学生呢？

use school1
go
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE	Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Ssex='男'

10.查询考试成绩大于总平均分的学生学号

use school1
go
Select  distinct Sno from  SC  where  Grade>(select avg(grade) from SC)

11.查询有一门成绩比所有选修了C02号课程的学生成绩高的学生学号

use school1
go
Select  Sno  from  SC where grade > all (select  Grade from SC where Cno='C02')  and  Cno<>'C02'

12.查询成绩至少比选修了C02号课程的一个学生成绩高的学生学号

use school1
go
Select  Sno  from  SC where grade > (select  Min(Grade) from SC where Cno='C02')  and  Cno<>'C02'

13.查询王建平同学选修课程的课程详细信息（用两种方式实现）

use school1
go
SELECT Course.Cno,Cname,Pre_Cno,Credit 
FROM Course,SC
WHERE	  SC.Sno='S01' and Course.Cno = SC.Cno

14.查询与“刘华”在同一个系学习的学生（用多种方式实现）

use school1
go
 --Select  Sno,Sname,Sdept  from  Student  where  Sdept IN  (select  Sdept from  student where  Sname='刘华')

--Select  S1.Sno,S1.Sname,S1.Sdept from  Student  S1,Student  S2 where  S1.Sdept=S2.Sdept  and S2.Sname='刘华'

Select  Sno,Sname,Sdept  from  Student S1 where Exists  (select  *  from  student S2 where  S2.Sdept=S1.Sdept and S2.Sname='刘华')

15.查询所有学生都选修了的课程号和课程名

use school1
go
 Select  Sname from Student where not Exists (select * from course where not Exists (select * from SC where  Sno=Student.Sno and Cno=Course.Cno))

16.查询至少选修了学生S03选修的全部课程的学生学号

use school1
go
 Select  distinct Sno from  SC X  where  Not Exists (select  *  from  SC  Y   where  Y.sno='S03'and  not  Exists   (select  *  from  SC  Z   where  Z.Sno=X.Sno  and  Z.Cno=Y.Cno))

17.查询同时选修了C01，C02和C03课程的学生学号

18.查询18岁学生和计算机学生的基本信息（用两种方式实现）

use school1
go
Select  *  from  student where  Sage=18   and Sdept  like  '计算机'

use school1
go
--Select  *  from  student where  Sage=18   and Sdept  like  '计算机'

Select  *  from  student where  Sdept  like  '计算机'   and Sage  like '18'

Select  *  from  student where  Sdept  like  '计算机'   or Sage  like '18'

19.查询计算机系中年龄大于18岁学生的基本信息（用两种方式实现）

use school1
go
Select  *  from  student where  Sage>18  intersect  select *  from  student     

use school1
go
Select  *  from  student where  Sage>18  

20.查询年龄大于18岁学生基本信息与计算机系学生基本信息的差集（用两种方式实现）

use school1
go
Select  *  from  student where  Sage>18 except  select *  from  student  where  Sdept like  '计算机' 

use school1
go
Select  *  from  student where  Sage>18   and Sdept not like  '计算机'