SQL Server数据库连接查询和子查询实战案例

USE XSCJ
Go
SELECT student.sno,sname,cno,grade from student,sc
where student.sno=sc.sno

USE XSCJ
GO
SELECT student.sno,sname,cno,grade 
from student join sc on student.sno=sc.sno

USE XSCJ
select student.sno,sname from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and cname='数据库原理与应用'

select student.sno,sname from student join sc 
on student.sno=sc.sno join course on sc.cno=course.cno
where cname='数据库原理与应用'

select x.sno,sname,cno,grade
from student x,sc y
where x.sno=y.sno

select A.sname,A.ssex,A.sage
from student A,student B
where B.sname='张文宝' and A.sage>B.sage

SELECT student.sno,sname,cno,grade
from student join sc
on student.sno=sc.sno

SELECT student.sno,sname,cno,grade
from student left outer join sc
on student.sno=sc.sno

select sc.sno,sname,cno,grade
from sc right outer join student
on student.sno=sc.sno

select sc.sno,course.cno,cname,credit
from sc right outer join course
on course.cno=sc.cno

select *
from sc full outer join student 
on student.sno=sc.sno

select sdept from student where sage='19'
union
select sdept from student where sage='20'

select sdept from student where sage='19'
union all
select sdept from student where sage='20'

select sno,sname
from student
where sno in
(select sno from sc)

select distinct student.sno,sname
from student join sc
on student.sno=sc.sno

select sno,sname,sage
from student 
where sage>
(select AVG(sage) from student)

select sname,sage
from student
where sage>any
	(select sage from student where sdept='CS')
	AND sdept!='CS'
select * from student

select *
from course
where exists
(select * from sc where course.cno=sc.cno)

select *
from course
where not exists
(select * from sc where course.cno=sc.cno)

select *
into temp
from student 
where sdept='CS'
select * from temp

INSERT INTO SCL(sno,cno)
select sno,cno
from student,course

UPDATE sc
set grade=grade+5
where cno=
(select cno from course
 where sc.cno=course.cno and cname='前台页面设计')

delete from sc
 where cno=
 (select cno from course
 where sc.cno=course.cno and cname='前台页面设计')