SET feedback off

drop table empresa cascade constraints;
drop table producto cascade constraints;
drop table subvencion cascade constraints;
drop table pais cascade constraints;
drop table comisario cascade constraints;
drop table nacionalidad cascade constraints;
drop table documento cascade constraints;



/***********************
*                      *
*        FASE 1        *
*                      *
************************/



CREATE TABLE EMPRESA(
CIF NUMBER (9),
NOMBRE VARCHAR2(20),
PAIS VARCHAR2(30),
FACTURACION NUMBER(2),
NUMERO_TRAB NUMBER(3),
DIRECCION VARCHAR2(30),
EMAIL VARCHAR2(30)
);



INSERT INTO EMPRESA VALUES ( 1111111,'ALPHA','Spain',21,30,'Aptdo 32, 28080 Madrid', 'info@alpha.es');
INSERT INTO EMPRESA VALUES ( 2222222,'BETA','United Kingdom',50,100,'Strigh st., 98066 London','info@beta.uk');
INSERT INTO EMPRESA VALUES ( 3333333,'ALPHABETA','Chequia',32,300,'Sbetrisstas 23, 099 Prague','alphabeta@bussines.com');
INSERT INTO EMPRESA VALUES ( 4444444,'PHI','France',44,60,'Rue Rodin, 7. Paris','info@phi.fr');




CREATE TABLE PRODUCTO(
CODIGO_PRODUCTO NUMBER (3),
NOMBRE VARCHAR2(15),
PRECIO NUMBER(5,2),
SUBVENCION NUMBER(3,1) 
);


INSERT INTO PRODUCTO VALUES (101,'arroz', 2.5, 25.7);
INSERT INTO PRODUCTO VALUES (202,'lino', 12.25, 35.7);
INSERT INTO PRODUCTO VALUES (303,'olivar', 20.5, 15.8);
INSERT INTO PRODUCTO VALUES (404,'vid', 6, 40.4);
INSERT INTO PRODUCTO VALUES (505,'tabaco', 8.35, 5.7);





CREATE TABLE SUBVENCION(
CIF NUMBER (9),
CODIGO_PRODUCTO NUMBER(3),
FECHA  DATE,
PEDIDOS NUMBER(2),
CONCEDIDOS NUMBER(2)
);

INSERT INTO SUBVENCION VALUES (1111111,202,TO_DATE('16-06-2003','dd-mm-yyyy'), 22, 20);
INSERT INTO SUBVENCION VALUES (1111111,101,TO_DATE('16-06-2003','dd-mm-yyyy'), 32, null);
INSERT INTO SUBVENCION VALUES (1111111,404,TO_DATE('26-03-2004','dd-mm-yyyy'), 26, null);
INSERT INTO SUBVENCION VALUES (1111111,303,TO_DATE('07-04-2004','dd-mm-yyyy'), 27, 0);
INSERT INTO SUBVENCION VALUES (2222222,202,TO_DATE('16-06-2003','dd-mm-yyyy'), 82, 80);
INSERT INTO SUBVENCION VALUES (2222222,202,TO_DATE('26-06-2003','dd-mm-yyyy'), 27, 0);
INSERT INTO SUBVENCION VALUES (3333333,202,TO_DATE('16-06-2003','dd-mm-yyyy'), 92, 25);
INSERT INTO SUBVENCION VALUES (3333333,101,TO_DATE('16-07-2003','dd-mm-yyyy'), 23, null);
INSERT INTO SUBVENCION VALUES (3333333,202,TO_DATE('16-01-2004','dd-mm-yyyy'), 32, null);
INSERT INTO SUBVENCION VALUES (4444444,202,TO_DATE('26-08-2003','dd-mm-yyyy'), 27, 0);

COMMIT;




/***********************
*                      *
*        FASE 2        *
*                      *
************************/


CREATE table pais(
codigo_pais number(9) primary key,
nombre_completo varchar2(30),
capital varchar2(30) unique,
fecha_entrada date,
PIB number(9),
Num_habitantes number(9),
Km2  number(8),
tipo varchar2(7) check(tipo IN ('NUEVO','ANTIGUO')),
deficit number(3,2),
caracter varchar2(30),
Fecha_peticion date,
Idioma varchar2(20),
Fundacion date,
Moneda varchar2(30)
);



INSERT INTO pais values(1,'Espana','Madrid','10-03-1986',null,44000000,300000,'ANTIGUO',0,'Receptor',null,null,null,null);
INSERT INTO pais values(2,'Alemania','Berlin','10-03-1960',null,70000000,400000,'ANTIGUO',0.9,'Fundador',null,null,null,null);
INSERT INTO pais values(3,'Italia','Roma','10-03-1972',null,50000000,150000,'ANTIGUO',1.2,'Innovador',null,null,null,null);
INSERT INTO pais values(4,'Portugal','Lisboa','10-03-1986',null,20000000,80000,'ANTIGUO',1.5,'Europeista',null,null,null,null);
INSERT INTO pais values(5,'Francia','Paris','10-03-1960',null,6500000,400000,'ANTIGUO',0.9,'Fundador',null,null,null,null);
INSERT INTO pais values(6,'Holanda','Amsterdam','10-03-1966',null,20000000,100000,'ANTIGUO',0.3,'Menor',null,null,null,null);
INSERT INTO pais values(7,'Austria','Viena','10-03-1996',null,20000000,200000,'ANTIGUO',0.9,'Menor',null,null,null,null);
INSERT INTO pais values(8,'Suecia','Estocolmo','10-03-1996',null,25000000,200000,'ANTIGUO',1.1,'Reciente',null,null,null,null);
INSERT INTO pais values(9,'Polonia','Varsovia','1-05-2004',null,40000000,350000,'NUEVO',null,null,'24-07-2001','Polaco',null,null);
INSERT INTO pais values(10,'Malta','Valletta','1-05-2004',null,600000,8000,'NUEVO',null,null,'30-12-2002','Maltes',null,null);
INSERT INTO pais values(11,'Letonia','Riga','1-05-2004',null,2000000,70000,'NUEVO',null,null,'30-12-2002','Leton',null,null);


create table COMISARIO(
nombre VARCHAR2(20) NOT NULL,
numero_pasaporte NUMBER(10) NOT NULL,
dia_nacimiento NUMBER(2) NOT NULL,
mes_nacimiento Number(2) NOT NULL,
ano_nacimiento Number(4) NOT NULL,
Nombramiento date,
Area VARCHAR2(25),
nacional_de references pais,
PRIMARY KEY(Nombramiento,area)
);


INSERT INTO comisario values('P. Peter', 837426623,2,3,1956,'14-07-1999','Agricultura',1);
INSERT INTO comisario values('P. Schwtizr', 02398476,5,7,1958,'14-06-2000','Agricultura',2);
INSERT INTO comisario values('L. Romani', 142335411,4,3,1960,'14-03-1996','Agricultura',3);
INSERT INTO comisario values('L. Manfredi', 5675342,4,3,1963,'14-03-1996','Industria',4);
INSERT INTO comisario values('L. Persciu', 34545672,4,3,1950,'14-03-1995','Industria',5);
INSERT INTO comisario values('P. Johndo', 12342341,5,7,1957,'14-06-2000','Industria',6);
INSERT INTO comisario values('L. Lopez', 789333,4,3,1968,'14-03-1998','Industria',7);
INSERT INTO comisario values('L. Persic', 9234572,4,3,1963,'14-03-1996','Pesca',8);
INSERT INTO comisario values('L. Marines', 98798,4,3,1950,'14-03-1995','Comercio',1);
INSERT INTO comisario values('P. Johanson', 4534534,5,7,1957,'14-06-2000','Pesca',2);
INSERT INTO comisario values('L. Stwysh', 456763,4,3,1968,'14-03-1998','Comercio',3);
INSERT INTO comisario values('L. Barban', 4353452,4,3,1963,'14-03-1996','Moneda',4);
INSERT INTO comisario values('L. Kjahus', 234458,4,3,1950,'14-03-1995','Tursimo',5);
INSERT INTO comisario values('P. Triperti', 98734566,5,7,1957,'14-06-2000','Moneda',6);
INSERT INTO comisario values('L. Wawarst', 23478628,4,3,1968,'14-03-1998','Moneda',7);


CREATE table documento(
Comisario number(10),
Pais references pais,
num_orden number(2),
fecha   date
);



INSERT INTO documento VALUES (837426623,1,1,'14-09-2002');
INSERT INTO documento VALUES (837426623,1,2,'18-09-2002');
INSERT INTO documento VALUES (837426623,1,3,'14-10-2002');
INSERT INTO documento VALUES (02398476,1,1,null);
INSERT INTO documento VALUES (02398476,1,2,'14-07-2001');
INSERT INTO documento VALUES (142335411,9,1,'24-11-2000');
INSERT INTO documento VALUES (5675342,8,1,null);
INSERT INTO documento VALUES (5675342,8,2,null);
INSERT INTO documento VALUES (5675342,8,3,null);
INSERT INTO documento VALUES (5675342,8,4,null);
INSERT INTO documento VALUES (34545672,4,1,null);
INSERT INTO documento VALUES (12342341,5,1,null);
INSERT INTO documento VALUES (789333,9,1,null);



COMMIT;

