CREATE TABLE PERSONA (
	id_persona NUMBER(25),
	DNI VARCHAR(10),
	nombre VARCHAR2(20),
	apellidos VARCHAR2(40),
	direccion VARCHAR2(50),
	telefono NUMBER(10),
	poblacion VARCHAR2(20),
	CONSTRAINT PK_ID_PERSONA PRIMARY KEY (id_persona),
	CONSTRAINT NN_DNI CHECK(dni IS NOT NULL)
);

CREATE TABLE VEHICULO (
	matricula VARCHAR2(9),
	marca VARCHAR2(20),
	modelo VARCHAR2(15),
	KMS NUMBER(10),
	id_persona NUMBER(25),
	CONSTRAINT PK_MATRICULA PRIMARY KEY(matricula),
	CONSTRAINT FK_ID_PERSONA FOREIGN KEY (id_persona) REFERENCES PERSONA(id_persona),
	CONSTRAINT NN_MARCA CHECK(marca IS NOT NULL)
);


CREATE TABLE INVOLUCRAR (
	matricula VARCHAR2(9),
	fecha DATE,
	Num_Referencia Number(20),
	CONSTRAINT FK_MATRICULA FOREIGN  KEY (matricula) REFERENCES VEHICULO(matricula), 
	CONSTRAINT FK_Num_Referencia FOREIGN KEY (Num_Referencia) REFERENCES ACCIDENTE(Num_Referencia)
);


CREATE TABLE ACCIDENTE (
	Num_Referencia Number(20),
	Gravedad VARCHAR2(20),
	Lugar VARCHAR2(30),
	CONSTRAINT PK_NUM_REFERENCIA PRIMARY KEY(Num_Referencia),
	CONSTRAINT NN_GRAVEDAD CHECK(Gravedad IS NOT NULL)
);


-- PERSONA ----
INSERT INTO PERSONA VALUES(1, '123456789A', 'Marcos', 'Lopez', 'Ibon de plan 23', 623512934, 'Zaragoza');
INSERT INTO PERSONA VALUES(2, '893453789N', 'Felipe', 'Felips', 'Ibon de Astun 21', 603502934, 'Madrid');
INSERT INTO PERSONA VALUES(3, '623656889I', 'Alberto', 'Crespo', 'Ibon de Norte 28', 653512934, 'Barcelona');
INSERT INTO PERSONA VALUES(4, '529956789P', 'Sergio', 'Tenor', 'Ibon de Sur 27', 653572934, 'Zaragoza');
INSERT INTO PERSONA VALUES(5, '689459789Y', 'Javier', 'Lolez', 'Ibon de Verde 25', 683812934, 'Madrid');
INSERT INTO PERSONA VALUES(6, '926496789U', 'Andrew', 'Lolaz', 'Ibon de Mal 22', 643517934, 'Zaragoza');
INSERT INTO PERSONA VALUES(7, '223486789M', 'Roberto', 'Carlo', 'Ibon de Paz 29', 673512734, 'Barcelona');



--VEHICULO----
INSERT INTO VEHICULO VALUES('A34589A34', 'Opel', 'gt', 152000, 1);
INSERT INTO VEHICULO VALUES('1713DVY', 'Fiat', 'Punto', 182000, 1);
INSERT INTO VEHICULO VALUES('AD3J5324J', 'Opel', 'xr', 154703, 2);
INSERT INTO VEHICULO VALUES('DW3452N53', 'Mercedes', 'gt', 20000, 3);
INSERT INTO VEHICULO VALUES('D23JCU4NM', 'Seat', 'Ibiza', 50000, 4);
INSERT INTO VEHICULO VALUES('PEBCAJ3JE', 'Opel', 'gt', 80200, 5);
INSERT INTO VEHICULO VALUES('DJ2JD83JS', 'Audi', 'A4', 14000, 6);
INSERT INTO VEHICULO VALUES('L1234DEW3', 'BMW', 'SERIE 1', 135000, 7);


--ACCIDENTE---- 
INSERT INTO ACCIDENTE VALUES(1, 'muy grave', 'Rotonda Hospitalet');
INSERT INTO ACCIDENTE VALUES(2, 'muy grave', 'AVENIDA Madrid');
INSERT INTO ACCIDENTE VALUES(3, 'poco grave', 'A2 direccion huesca');
INSERT INTO ACCIDENTE VALUES(4, 'media', 'Paseo los canarios');
INSERT INTO ACCIDENTE VALUES(5, 'poco grave', 'Lolito Parra');
INSERT INTO ACCIDENTE VALUES(6, 'muy grave', 'Los olvidados');



--INVOLUCRAR--
INSERT INTO INVOLUCRAR VALUES('A34589A34', SYSDATE, 1);
INSERT INTO INVOLUCRAR VALUES('AD3J5324J', TO_DATE('11-11-2024','DD/MM/YYYY'), 2);
INSERT INTO INVOLUCRAR VALUES('DW3452N53', TO_DATE('13-1-2025','DD/MM/YYYY'), 3);
INSERT INTO INVOLUCRAR VALUES('D23JCU4NM', SYSDATE, 4);
INSERT INTO INVOLUCRAR VALUES('PEBCAJ3JE', TO_DATE('08-03-2024','DD/MM/YYYY'), 5);
INSERT INTO INVOLUCRAR VALUES('DJ2JD83JS', TO_DATE('09-03-2025','DD/MM/YYYY'), 6);



--PARTE 2:

--1.Localización de TODOS los clientes que hayan comprado algún coche de la marca “Opel” con más de 150.000 kms 

select * from vehiculo;
select * from persona;
select * from involucrar;
select * from accidente;

SELECT nombre, apellidos, direccion, KMS, marca FROM PERSONA, VEHICULO
    WHERE PERSONA.id_persona = VEHICULO.id_persona
    AND UPPER(marca) = 'OPEL' --1ER FALLO  (Para no encontrar problemas con coincidencias realizar un UPPER en marca y poner marca en mayúscula)
    AND KMS > 150000;

--2. Coches que tenemos de la marca “Opel” con más de 150.000 kms

SELECT matricula ,KMS, marca FROM VEHICULO --FALLO. No se necesita la tabla PERSONA.
    WHERE UPPER(marca) = 'OPEL'
    AND KMS > 150000;


--3. Matrícula de coches que todavía NO hayan tenido un accidente.

SELECT VEHICULO.MATRICULA FROM VEHICULO WHERE MATRICULA NOT IN
(SELECT VEHICULO.MATRICULA  FROM VEHICULO,INVOLUCRAR, ACCIDENTE
WHERE VEHICULO.MATRICULA = INVOLUCRAR.MATRICULA AND INVOLUCRAR.NUM_REFERENCIA = ACCIDENTE.NUM_REFERENCIA);
--¿Cómo hemos llegado a la solución?
--1º. ¿Qué vehiculos tienen accidente? (subquery)
--2º. Excluyo todos los vehículos (matrículas) que no he obtenido en el punto anterior.
--3º. Cuando utilizo una lista [devuelve más de un elemento] (procedente de una subquery en filtros [where]) es imprescindíble usar IN o NOT IN.




--4. Nombre, apellido y teléfono de los clientes que tengan algún coche que haya tenido un accidente en los últimos 30 días.

SELECT NOMBRE, APELLIDOS TELEFONO FROM PERSONA, VEHICULO, INVOLUCRAR
   WHERE PERSONA.ID_PERSONA = VEHICULO.ID_PERSONA
   AND VEHICULO.MATRICULA = INVOLUCRAR.MATRICULA
   AND FECHA >= SYSDATE - 30; --CURRENT_DATE SIMILAR A SYSDTE 

--5. Número total de clientes que tengan algún coche que hayan tenido un accidente “muy grave” el último año.

SELECT COUNT(NOMBRE) FROM PERSONA, VEHICULO, INVOLUCRAR, ACCIDENTE
   WHERE PERSONA.ID_PERSONA = VEHICULO.ID_PERSONA
   AND VEHICULO.MATRICULA = INVOLUCRAR.MATRICULA
   AND ACCIDENTE.Num_Referencia = INVOLUCRAR.Num_Referencia
   --AND FECHA >= SYSDATE - 365 (+-)
   AND FECHA BETWEEN (SYSDATE-365) AND SYSDATE 
   AND UPPER(GRAVEDAD) = 'MUY GRAVE';