SQL练习与Learning 数据库脚本
mis · ) · 2007-12-07
<< SQL上机实验步骤及举例
|
首 页
|
如何通过用户定义函数显示逻辑类型的属性 >>
2007-11-27
SQL练习与Learning 数据库脚本
- [
数据库
]
create
database
learningSys
go
use
learningSys
go
create
table
student
(
sid
char
(
8
)
primary
key
,
sname
varchar
(
20
)
not
null,
department
varchar
(
20
)
not
null,
gender
bit
,
age
smallint
)
go
create
table
course
(
cid
char
(
3
)
primary
key
,
cname
varchar
(
20
)
not
null,
department
varchar
(
20
)
not
null
)
go
create
table
learning
(
sid
char
(
8
)
not
null,
cid
char
(
3
)
not
null,
score
decimal
(
4
,
1
)
)
go
-- alter table
learning drop constraint PK_learning
-- go
ALTER
TABLE
learning
ADD
CONSTRAINT
PK_learning
PRIMARY
KEY
(
sid
,
cid
)
go
ALTER
TABLE
learning
ADD
CONSTRAINT
FK_learning_course
FOREIGN
KEY
(
cid
)
REFERENCES
course
(
cid
),
CONSTRAINT
FK_learning_student
FOREIGN
KEY
(
sid
)
REFERENCES
student
(
sid
)
go
insert
into
student
values
(
'001'
,
'
张三
'
,
'
计算机系
'
,
1
,
18
)
insert
into
student
values
(
'002'
,
'
李四
'
,
'
计算机系
'
,
1
,
18
)
insert
into
student
values
(
'003'
,
'
王五
'
,
'
计算机系
'
,
1
,
18
)
insert
into
student
values
(
'004'
,
'
陈涛
'
,
'
计算机系
'
,
1
,
18
)
insert
into
student
values
(
'005'
,
'
李平
'
,
'
计算机系
'
,
1
,
18
)
insert
into
student
values
(
'006'
,
'
陈海峰
'
,
'
计算机系
'
,
1
,
18
)
insert
into
student
values
(
'007'
,
'
刘小翔
'
,
'
计算机系
'
,
1
,
18
)
insert
into
student
values
(
'008'
,
'
易建联
'
,
'
计算机系
'
,
1
,
18
)
insert
into
student
values
(
'009'
,
'
王玮
'
,
'
会计系
'
,
0
,
21
)
insert
into
student
values
(
'010'
,
'
张华凤
'
,
'
会计系
'
,
0
,
22
)
insert
into
course
values
(
'c01'
,
'MIS'
,
'
计算机系
'
)
insert
into
course
values
(
'c02'
,
'Word'
,
'
计算机系
'
)
insert
into
course
values
(
'c03'
,
'English'
,
'
外语系
'
)
insert
into
course
values
(
'c04'
,
'Mathmatics'
,
'
数学系
'
)
insert
into
course
values
(
'c05'
,
'MBA'
,
'
工商管理系
'
)
insert
into
course
values
(
'c06'
,
'
哲学
'
,
'
社科系
'
)
insert
into
course
values
(
'c07'
,
'
会计
'
,
'
会计学
'
)
insert
into
learning
values
(
'001'
,
'c02'
,
88
)
insert
into
learning
values
(
'001'
,
'c03'
,
70.5
)
insert
into
learning
values
(
'001'
,
'c04'
,
77
)
insert
into
learning
values
(
'001'
,
'c05'
,
64
)
insert
into
learning
values
(
'001'
,
'c06'
,
50
)
insert
into
learning
values
(
'001'
,
'c07'
,
83
)
insert
into
learning
values
(
'002'
,
'c01'
,
100
)
insert
into
learning
values
(
'002'
,
'c02'
,
99.5
)
insert
into
learning
values
(
'002'
,
'c03'
,
80
)
insert
into
learning
values
(
'002'
,
'c04'
,
88
)
insert
into
learning
values
(
'003'
,
'c01'
,
84
)
insert
into
learning
values
(
'003'
,
'c02'
,
48
)
insert
into
learning
values
(
'003'
,
'c03'
,
55
)
insert
into
learning
values
(
'003'
,
'c04'
,
77
)
insert
into
learning
values
(
'004'
,
'c03'
,
80
)
insert
into
learning
values
(
'005'
,
'c04'
,
21
)
insert
into
learning
values
(
'005'
,
'c05'
,
84
)
insert
into
learning
values
(
'005'
,
'c02'
,
48
)
insert
into
learning
values
(
'002'
,
'c07'
,
95
)
update
course
set
department
=
'
会计系
'
where
cid
=
'c07'
update
student
set
department
=
'
会计系
'
where
sid
=
'006'
or
sid
=
'005'
update
student
set
gender
=
0
where
sid
=
'002'
or
sid
=
'001'
--
求计算机系的所有学生数据
select
*
from
student
where
department
=
'
计算机系
'
--
求所开课程的课程号,课程名
select
cid
课程号
,
cname
课程名
from
course
--
求计算机系年龄不到20岁的所有男生
select
*
from
student
where
age
<
20 and gender=1
--
求已被学生所选课的课号
select
DISTINCT
cid
from
learning
--
求计算机系和会计系的所有女生
select
*
from
student
where
department
in
(
'
计算机系
'
,
'
会计系
'
)
and
gender
=
0
--
求选了课程的所有学生
select
*
from
student
where
sid
in
(
select
sid
from
learning
)
select
distinct
s
.
sid
,
s
.
sname
,
s
.
gender
,
s
.
age
from
student
as
s
,
learning
as
l
where
s
.
sid
=
l
.
sid
--
求选了课号为
“c01”
,并且成绩在分以上的所有学生学号
select
sid
from
learning
where
cid
=
'c01'
and
score
>
80
--
求同时选了课号为
“c02”
或
“c03”
的学生学号和两门课的成绩
select
la
.
sid
学号
,
la
.
score c02_
成绩
,
lb
.
score c03_
成绩
from
learning
as
la
,
learning
as
lb
where
la
.
sid
=
lb
.
sid
and
la
.
cid
=
'c02'
and
lb
.
cid
=
'c03'
--
求选了会计系所开课程的学生姓名,所在系
select
s
.
sname
,
s
.
department
from
student
as
s
inner
join
learning
as
l
on
(
s
.
sid
=
l
.
sid
)
inner
join
course
as
c
on
(
l
.
cid
=
c
.
cid
)
where
c
.
department
=
'
会计系
'
Tag:
SQLServer
DB
SQL
Sam
@ 19:57:33 |
引用 0
|
编辑
评论
老师,数据库原理与设计对我们这些一点基础都没有的同学,真的好难。。能不能再说慢点。。或者是课后题都给我们标准答案,让我们去背诵,我觉得这样的效率可能高点。
工商管理
(
http://
发表评论
姓名:
E-mail:
地址:
原始快照:web.archive.org