使用数据库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 '计算机'