-
1.0
createtrigger
sc_t
afterupdateof
grade
on
sc
referencign
oldrow
as
oldtuple
newrow
as
newtuple
for
eachrow
when
(
newtuple
.
grade
>=
1.0
*
oldtuple
.
grade
)
insertinto
sc_u
< br>(
sno
,
cno
,
oldgrade
,
ne
wgrade
)
values
(
oldtuple
.
sno
,
oldtuple
.
cno
,
oldtuple
.
grade
,
newtuple
.
grade
)
/*
触发器
*/
2.0
begintransaction
读取账户甲的金额
belance
;
balance
=
balance
-
amount
;
if
(
balance
<
0
)
then
{
打印
'
金额不足
,<
/p>
不能转账
'
;
rollback
;}
else
{
读账户乙的余额
BALANCE1
;
balance1
=
balance1
+
amount
;
写回
balance1
;
commit
;}
3.0
createdatabase
student
;
use
student
createtable
stu
<
/p>
(
Sno
char
(
11
)
Primarykey
,
Sname
char
< br>(
20
)
unique
,
Ssex
char
(
2
),
Sage
SMALLINT
,
Sdept
char
(
20
)
)
createtable
course
(
Cno
char
(
4
)
PRIMARYKEY
,
Cname
char
(
40
)NOTNULL,
Cpno
< br>CHAR
(
4
),
Ccredit
SMALLINT
,
FOREIGNKEY
(
Cpno
)
references
course
(
Cno
)
)
createtable
sc
(
Sno
char
(
11
),
Cno
< br>char
(
4
),
Grade
SMALLINT
,
PRIMARYKEY
(
Sno
,
Cno
),
FORE
IGNKEY
(
Sno
)
references
stu
(
Sno
),
FOREIGNKEY
(
Cno
)
references<
/p>
course
(
Cno
< br>)
)
select
*
from
course
;
4.0
/*
6.0
建立下面二个关系模式
*/
createdataba
se
work
;
use
work
createtable
worker
(
Wno
char
< br>(
10
),
Wname
char
(
3
),
age
smallint
,
job
char
(
3<
/p>
),
Wmoney
SMALLINT
, <
/p>
Wpub
char
(
5
),
primarykey
(
Wno
),
foreignkey<
/p>
(
Wpub
)
r
eferences
club
(
Wpu
b
),
check
(
age
<
60
)
);
createtable
club
(
Wpub
char
(
< br>5
)
primarykey
,
Cname
char
(
3
)notnull,
masseger
char
(
2
),
phone
char
(
10
)
unique
);
/*
8.0 */
createtable
female
(
fid
intprimarykey
,
fname
char
(
3
)notnull,
fage
int
,
< br>phone
char
(
10
)notnull
);
createtable
male
(
mid
int
,
mname
char
(
3
),
mage
smallint
,
phone
char
(
10
),
primarykey
(
mid
)
);
select
COUNT
(
fid
)
from
female
;
select
COUNT
(
mid
)
from
male
;
create
assertionfm
check
(
50
>=(
select
COUNT
(
fid
)
from
female<
/p>
)+(
select
COUNT
(
mid
)
from
male
))
);
5.0
createdataba
se
homewark
;
use
homewark
createtable
s
(
sno
char
(
5
)
primarykey
,
sname
char
(
30
),
statussmallint
,
c
ity
char
(
20
)
);
select<
/p>
*
from
s
;
createtable
p
(
pno
char
(
5
)
primarykey
,
pname
char
(
10
),
color
char
(
2
),
weightsmallint
);
select
*
from
p
;
createtable
j
(
jno
char
(
2
)
primarykey
,
jname
chAR
(
15
),
city
char
(
10
)
);
select
*
from
j
;
createtable
spj
( <
/p>
sno
char
(
2
),
pno
char
(
2
),
jno
char
(
2
),
qty
smallint
,
primarykey
(
sno
,
pno
,
jno
)
);
selec
t
*
from
spj
< br>;
selectdistinct
sno
from
spj
where
jno
=
'j1'
;
select
sno
from
spj
where
pno
=
'p1'
a
nd
jno
=
'j1'
;
select
sno
from
spj
,
p
where
spj
.
pno
=
p
.
pno
and
jno
=
'j1'
and
color
=
'
红
'
;
selectdistinct
sno
from
spj
where
pno
=
'p1'
and
pno
in
(
select
pno
from
p
where
color
=
'
红
'
);
select
jno
from
spj
,
s
,
p
where
s
pj
.
pno
=
p
.
pno
and
spj
.
sno
=
s
.
sno
and
color
=
'
红
'
and
city
< br>!=
'
天津
'
< br>;
select
jno
from
spj
where
pno
in
(
select
pno
from
p
where
c
olor
=
'
红
'
)
and
sno
in
(
select
sno
from
s
where
city
!=
'
天津
'
);
select
jno
from
spj
where
sno
=
's1'
;
select
pname
,
qty
from<
/p>
p
,
spj
w
here
spj
.
pno
=
p
.
pno
and
spj
.
jno<
/p>
=
'j2'
;
selectdistinct
p
.<
/p>
pno
from
s
,
spj
,
p
< br>where
s
.
sno
=
spj
.
sno <
/p>
and
spj
.
pno
=
p
.
pno
and
city
=
''
;
createview
pro1
as
select
sno
,
pno
,
qty
from<
/p>
spj
,
j
w
here
spj
.
jno
=
j
.
jno
and
jname
=
''
;
selectdistinct
pno
,
qty
from
pro1
;
select
*
from
pro1
where
pno
=
'p1'
;
6.0
select
sname
,
sno
from
stu
;
select
*
from
stu
orderby
Sdept
,
Sage
desc
;
select
COU
NT
(
distinct
sno
)
from
sc
;
select
COUNT
(*)
from
stu
;
select
cno
,
COUNT
(
sno
)
from
sc
groupby
Cno
;
select
stu
.*,
sc
.*
from<
/p>
stu
,
sc
where
stu
.
sno
=
sc
.
sno
;
select
sname
from
stu
where
Sno
in
(
select
Sno
from
sc
where
Cno
=
'2'
);
select
sname
,
sage
from
stu
where
Sage
select
Sage
from
stu
-
-
-
-
-
-
-
-
-
上一篇：李将军列传原文及翻译
下一篇：明史·张学颜传 阅读答案附翻译