练习数据库

数据库：
create database 练习
go
use 练习
go
create table Student
(
s# int not null constraint PK_S# primary key,
Sname varchar(50) null,
Sage int not null,
Ssex char(2) not null

)
insert into student (S#,Sname,Sage,Ssex)
select 1,'陆运',20,'男' union
select 2,'静儿',19,'女' union
select 3,'洋洋',19,'女' union
select 4,'成成',14,'男' union
select 5,'小花',15,'女' union
select 6,'李四',23,'女' union
select 7,'里斯',20,'男' union
select 8,'张三',12,'男'

create table Teathers
(
T# int not null constraint PK_T# primary key,
Tname varchar(50) not null
)
insert into Teathers(T#,Tname)
select 1,'何葛' union
select 2,'李景' union
select 3,'扬名' union
select 4,'吴敏' union
select 5,'谈仅凭' union
select 6,'恬静平' union
select 7,'吴桐顺'


create table Course
(
c# int not null constraint PK_C# primary key,
Cname varchar(50) not null,
T# int not null constraint FK_T# references Teathers(T#)
)
insert into Course (C#,Cname,T#)
select 1,'语文',1 union
select 2,'数学',3 union
select 3,'英语',2 union
select 4,'矛盾',4 union
select 5,'教育',5 union
select 6,'体育',6 union
select 7,'生物',7


create table Scores
(
S# int not null constraint FK_S# references Student(S#),
C# int not null constraint FK_C# references Course(C#),
score int null
)

insert into Scores (S#,C#,score)
select 1,1 , 90 union
select 1,3 , 80 union
select 1, 2,99 union
select 1,4 , 26 union
select 1,5 ,30 union
select 1, 6,48 union
select 1 , 7,75 union
select 2, 2,45 union
select 2 ,3 , 98 union
select 2, 4,44 union
select 2 , 5,75 union
select 2 ,6 ,76 union
select 2,7 ,74 union
select 4 ,6 , 45 union
select 4, 1,74 union
select 4,7 ,45 union
select 5, 4,78 union
select 5 ,5 ,75

select * from Course
select * from Scores
Select * from Student
Select * from Teathers
--insert into ()

--alter table name add constraint constraintname
--default (values) for column
--unique(column)
--check(biaodashi)
--primary key ()
--foreign key () references tablename(column)

--insert into *tablename* (colunm) from tablename
--select ziduan union
--select ziduan


查询语句：
select student.sname,scores.score from student
inner join scores on student.s#=scores.s#
where c#=1 or c#=2

select sname,c.score from student d inner join
(
select a.s#,a.score,a.c# from
(
select s#,score,c# from scores where c#=1
) a,
(
select s#,score,c# from scores where c#=2
) b
where a.score >b.score and a.s#=b.s#
)
c
on c.s#=d.s#