北大“数据库原理”上机实践题目总结(8)
试用SQL语言完成下列功能:
1 创建以上3个表,在定义中要求声明:
(1)每个表的主外码。
(2)每个存款单上的存款余额不能少于10.
(3)客户姓名不能为空。
create database exam
create table customer
(cno char(4),
cname char(8)not null,
addr char(20),
primary key(cno))
create table account
(ano char(4),
money int,
primary key(ano),
check(money>10))
create table custacc
(cno char(4),
ano char(4),
primary key(cno,ano),
foreign key(cno) references customer(cno),
foreign key(ano) references account(ano))
2 向表中插入如下数据:
insert into customer
values('101','张三','海淀区')
insert into customer
values('102','李四','朝阳区');
insert into customer
values('103','王玲','宣武区')
insert into customer
values('105','李飞','朝阳区')
insert into customer
values('109','赵四','崇文区')
insert into customer
values('110','李平','海淀区')
insert into Account
values('203','500')
insert into Account
values('279','700')
insert into Account
values('210','1200')
insert into Account
values('243','6400')
insert into Account
values('204','9500')
insert into Account
values('208','500')
insert into Account
values('209','700')
insert into Account
values('211','1200')
insert into Account
values('244','6400')
insert into Account
values('207','950')
insert into Account
values('225','4200')
insert into Account
values('240','650')
insert into custacc
values('101','203')
insert into custacc
values('101','210')
insert into custacc
values('102','209')
insert into custacc
values('105','279')
insert into custacc
values('105','243')
insert into custacc
values('101','204')
insert into custacc
values('109','207')
insert into custacc
values('109','225')
insert into custacc
values('110','244')
insert into custacc
values('110','240')
insert into custacc
values('103','208')
insert into custacc
values('109','211')