1. Mostrar los datos de los autos con más de 2 accidentes. 2. Mostrar los datos de personas con más de 2 accidentes entre las fechas 01/01/2000. 3. Mostrar los datos de los vehículos con un promedio de multas mayor que 200000.

database.sql

Create database DB_multas 
use DB_multas 


CREATE TABLE persona( 
    dni int, 
    Nombre varchar(50), 
    apellidos varchar(50), 
    direccion varchar(50), 
    tfno int, 
    poblacion varchar(50), 
    cantidad_multas int, 
    check(cantidad_multas>=0), 
    
    Primary key(dni)
);

CREATE TABLE vehiculo( 
    matricula varchar (50), 
    Marca varchar (50) not null, 
    modelo int not null, 
    Valor bigint, 
    check (Valor between 7000000 and 200000000), 
    acumulado float not null, 
    check (acumulado>=0), 
    
    primary key (matricula)
); 

CREATE TABLE accidente( 
    codigo int, 
    hora float, 
    fecha datetime, 
    lugar varchar(50), 
    primary key (codigo)
);

CREATE TABLE multa( 
    codigo_m int, 
    hora float, 
    fecha datetime, 
    lugar varchar(50), 
    importe float, 
    matricula varchar (50), 
    dni int,
    
    primary key (codigo_m), 
    foreign key (dni) references persona (dni) 
    on delete cascade on update cascade, 
    foreign key (matricula) references vehiculo (matricula) 
    on delete cascade on update cascade
);

CREATE TABLE personas_vehiculos( 
    matricula varchar (50), 
    dni int,
    
    primary key (matricula,dni), 
    foreign key (dni) references persona (dni) 
    on delete cascade on update cascade, 
    foreign key (matricula) references vehiculo (matricula) 
    on delete cascade on update cascade
); 

CREATE TABLE accidentes_persona( 
    codigo int, 
    dni int, 
    
    primary key (codigo,dni), 
    foreign key (dni) references persona (dni) 
    on delete cascade on update cascade, 
    foreign key (codigo) references accidente (codigo) 
    on delete cascade on update cascade
);

CREATE TABLE accidentes_vehiculos( 
    codigo int, 
    matricula varchar(50),
    
    primary key (codigo, matricula), 
    foreign key (matricula) references vehiculo (matricula) 
    on delete cascade on update cascade, 
    foreign key (codigo) references accidente (codigo) 
    on delete cascade on update cascade
);

queris.sql

SELECT * FROM total_accidentes_veiculos AS tav WHERE tav.total > 2;

SELECT * FROM total_accidentes_personas AS tap WHERE tap.total > 2 AND tap.fecha > 2000-01-01;

SELECT * FROM promedio_multas AS pm WHERE pm.promedio > 200000;

views.sql

CREATE VIEW total_accidentes_veiculos AS
    SELECT 
        v.*,
        COUNT(av.matricula) AS total
    FROM veiculo v
    LEFT JOIN accidentes_vehiculos AS av ON v.matricula = av.matricula
    LEFT JOIN accidente AS a ON av.codigo = a.codigo GROUP BY v.matricula;

CREATE VIEW total_accidentes_personas AS
    SELECT
        p.*,
        COUNT(ap.dni) AS total,
        a.*
    FROM persona p
    LEFT JOIN  accidentes_persona AS ap ON ap.dni =  p.dni
    LEFT JOIN accidentes AS a ON a.codigo = ap.codigo GROUP BY p.dni;

CREATE VIEW promedio_multas AS
    SELECT
        v.*,
        SUM(m.importe)/COUNT(m.importe) AS promedio
    FROM veiculo AS v
    LEFT JOIN multas AS m ON m.matricula = v.matricula GROUP BY v.matricula;