²âÊÔÒ»ÏÂ×Ô¼ºµÄSQLˮƽ

 
create table Student(Sname varchar(10),Ssex varchar(5),Sage int,S# int)
insert into Student
select 'ÏÄÁÁ','ÄÐ','21','1004'
select '³Éƽ','ÄÐ','20','1001' union all
select 'Íõ²¨','ÄÐ','19','1002' union all
select 'ͻȻ','Ů','19','1003'
 
create table Course(C# varchar(10),Cname varchar(10),T# varchar(10))
insert into Course
select '006','»¯Ñ§','C06' union all
select '005','Êýѧ','C06' union all
select '004','µØÀí','C05' union all
select '001','×ÔÈ»','C02' union all
select '002','Éç»á','C01' union all
select '003','ÐÄÀí','C03'
 
 
IF EXISTS (SELECT 1 from SC)
    DROP TABLE SC
GO
create table SC(S# int,C# varchar(10),score int)
insert into SC
select '1004','002',13 union all
select '1004','004',13 union all
select '1004','005',13 union all
select '1004','006',13 union all
select '1004','001',13 union all
select '1004','003',35 union all
select '1003','002',95 union all
select '1003','001',55 union all
select '1001','006',55 union all
select '1001','005',100 union all
select '1001','001',100 union all
select '1002','001',null union all
select '1001','002',90 union all
select '1003','003',68
 
create table Teacher(T# varchar(10),Tname varchar(10))
insert into Teacher
select 'C04','ÌìÀïºÆ'union all
select 'C05','ÌìÈκÆ'union all
select 'C03','Á¢Ð¡Àö'union all
select 'C02','ÖìÁзò'union all
select 'C01','ÌìÈκÆ'union all
select 'C04','ÌìÀïºÆ'union all
select 'C06','Ҷƽ'
 
select * from Course
select * from SC
select * from student
 
--Student(S#,Sname,Sage,Ssex) ѧÉú±í
--Course(C#,Cname,T#) ¿Î³Ì±í
--SC(S#,C#,score) ³É¼¨±í
--Teacher(T#,Tname) ½Ìʦ±í
 
--ÎÊÌ⣺
--1¡¢²éѯ“”¿Î³Ì±È“”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ:
select distinct S# from SC as sc where
(select score from sc as sc1 where c#='001' and sc1.s#=sc.s#)>
(select score from sc as sc2 where c#='002' and sc2.s#=sc.s#)
 
select S# from SC s where C#='001' and  score