数据库建立

建表
create table employee
(eno varchar(50) not null,
ename varchar(50) not null,
esex varchar(2) not null,
eage smallint not null,
primary key(eno),
check (esex in ('男','女')),
check (eage>0)
);

create table company
(cno varchar(50) not null,
cname varchar(50) not null,
primary key(cno)
);

create table works
(eno varchar(50) not null,
cno varchar(50) not null,
wage numeric(10,2) not null default 0,
primary key(eno,cno),
foreign key (eno) references employee on update cascade on delete cascade,
foreign key (cno) references company on update cascade on delete cascade,
check (wage>=0)
);

向表中插入数据

insert into employee values ('E01','张三','女',32);
insert into employee values ('E02','李四','男',28);
insert into employee values ('E03','王五','女',42);
insert into employee values ('E04','赵六','男',37);
insert into employee values ('E05','陈七','男',51);


insert into company values ('C01','阳光科技');
insert into company values ('C02','晨光科技');
insert into company values ('C03','未来科技');


insert into works values ('E01','C01',3000);
insert into works values ('E01','C02',4000);
insert into works values ('E02','C02',5000);
insert into works values ('E02','C03',2500);
insert into works values ('E03','C01',3500);
insert into works values ('E04','C02',3000);
insert into works values ('E05','C03',2000);

查询和统计数据


select e.eno,ename,cname,wage 
from employee as e,company as c,works as w 
where w.eno=e.eno and w.cno=c.cno and e.eno='E01';

select e.eno,ename,eage,sum(wage) 
from employee as e,works as w 
where w.eno=e.eno and eage>=40 
group by e.eno,ename,eage  
order by sum(wage) desc;

select e.eno,ename,cname,wage 
from employee as e,company as c,works as w 
where w.eno=e.eno and w.cno=c.cno and e.eno in (select eno from works group by eno having count(*)>1);
展开阅读全文

没有更多推荐了,返回首页