【Oracle】练习题及题解

--创建表
create table tbEmp(
	eid int primary key,
	ename varchar2(8) not null unique
		constraint CK_ename check(length(ename)>1 and length(ename)<6),
	esex varchar2(2) default '男' not null 
		constraint CK_esex check(esex in('男','女')),
	sAge int not null
		constraint CK_sAge check(sAge between 10 and 50),
	eaddr varchar2(200) default '地址不详'
);

create table tbProd(
	pid int primary key,
	ptype varchar2(20) not null,
	pmark varchar2(200),
	pspec varchar2(200)
);

create table tbSales(
	srno int primary key,
	eid int not null
		constraint FK_eid references tbEmp(eid),
	pid int not null
		constraint Fk_pid references tbProd(pid),
	pqty int not null
		constraint CK_pqty check(pqty>=0),
	pamount numeric(10,2) not null
		constraint CK_pamount check(pamount>=0),
	sdate date not null
);

create sequence seq_eid;
create sequence seq_pid;
create sequence seq_srno;


insert into tbEmp values(seq_eid.nextval,'张三',default,24,'湖南长沙');
insert into tbEmp values(seq_eid.nextval,'李云','男',26,'湖南长沙');
insert into tbEmp values(seq_eid.nextval,'王武','女',22,'湖南衡阳');
insert into tbEmp values(seq_eid.nextval,'陈彬',default,30,'湖北武汉');
insert into tbEmp values(seq_eid.nextval,'赵柳','女',24,'湖南益阳');
insert into tbEmp values(seq_eid.nextval,'钱七',default,32,'湖南长沙');
 
 
insert into tbProd values(seq_pid.nextval,'笔记本','紫光','');
insert into tbProd values(seq_pid.nextval,'笔记本','联想','U350');
insert into tbProd values(seq_pid.nextval,'电视机','长虹','29英寸');
insert into tbProd values(seq_pid.nextval,'电视机','长虹','52英寸');
insert into tbProd values(seq_pid.nextval,'电视机','熊猫','32英寸');
insert into tbProd values(seq_pid.nextval,'电视机','TCL','42英寸');
insert into tbProd values(seq_pid.nextval,'电视机','TCL','52英寸');

insert into tbSales values(seq_srno.nextval,1,5,1,4000,to_date('2012-5-14','YYYY-MM_DD'));
insert into tbSales values(seq_srno.nextval,2,1,2,8000,to_date('2012-3-14','YYYY-MM_DD')