convert.sql

来自「oracle数据库的排序查询算法」· SQL 代码 · 共 61 行

delete from works;
drop table works;

delete from manages;
drop table manages;

delete from company;
drop table company;

delete from employee;
drop table employee;


create table company
   (company_name 	varchar(15)	not null,
    company_city 	varchar(15)	not null,
    primary key(company_name));

create table employee
   (employee_name 	varchar(15)	not null,
    employee_street 	varchar(12)	not null,
    employee_city 	varchar(15)	not null,
    primary key(employee_name));

create table works
   (employee_name 	varchar(15)	not null,
    company_name 	varchar(15)	not null,
    salary              integer,	
    primary key(employee_name),
    check(salary>=0),
    foreign key(employee_name) references employee(employee_name),
    foreign key(company_name) references company(company_name));

create table manages
   (employee_name 	varchar(15)	not null,
    manager_name 	varchar(15)	not null,
    primary key(employee_name),
    foreign key(employee_name) references employee(employee_name),
    foreign key(manager_name) references employee(employee_name));

insert into employee select * from customer;

insert into employee values ('许迪飞','华山16栋103','广州市');

insert into company select branch_name,branch_city from branch;

insert into company values ('中国建设银行','广州市');

insert into works
select customer_name,branch_name,balance*2.5
from depositor d,account a
where d.account_number=a.account_number and(customer_name,balance)=any(select customer_name,max(balance)from depositor,account where depositor.account_number=account.account_number group by customer_name);

insert into works values ('许迪飞','中国建设银行','900');

insert into manages
select e.employee_name,m.employee_name
from works e,works m
where e.company_name=m.company_name and (m.company_name,m.salary)=any(select company_name,max(salary)from works group by company_name);
 
commit;