Return vehicles that have all parts in a given table

The below query would be an example of how to do this.

--|      veiculo      |
--| idVeiculo | placa |

--|        peca       |
--| idPeca | nomePeca |

--|      relVeiPec     |
--| idVeiculo | idPeca |

--| pecaObrig |
--|   idPeca  |

Declare @Veiculos table
(     
    idVeiculo int,  
    placa varchar(7)
);

insert into @Veiculos values 
(1,'AAA1111'), 
(2,'BBB2222'), 
(3,'CCC3333'), 
(4,'DDD4444');

Declare @Peca table
(     
    idPeca int,     
    nomePeca varchar(20)
);

insert into @Peca values 
(1,'porta'),  
(2,'vidro'), 
(3,'pneu'), 
(4,'Motor'), 
(5,'chassis'),  
(6,'parabrisa');

Declare @pecaObrig table 
(   
    idPeca int
);

insert into @pecaObrig values 
(1), 
(4), 
(5);

Declare @relVeiPec table
(    
    idVeiculo int, 
    idPeca int
)

insert into @relVeiPec values (1,1) ,(1,2) ,(1,3) ,(1,4) ,(1,5),(1,6),
(2,1) ,(2,2) ,(2,6),(3,1) ,(3,2),(3,4) ,(3,6),(4,1) ,(4,4) ,(4,5);

SELECT vp.idVeiculo, v.placa, vp.idPeca, p.nomePeca 
    FROM @relVeiPec AS vp
    INNER JOIN @Veiculos AS v 
        ON v.idVeiculo = vp.idVeiculo
    INNER JOIN @Peca AS p 
        ON p.idPeca = vp.idPeca
        WHERE v.idVeiculo in (
                    select idVeiculo 
                        from @pecaObrig po
                        join @relVeiPec rcp
                        on rcp.idPeca = po.idPeca
                        group by idVeiculo
                        having count(rcp.idPeca)  = (select count(*) from @pecaObrig)
                    )

Or you can use EXISTS which is more efficient than IN

SELECT vp.idVeiculo, v.placa, vp.idPeca, p.nomePeca 
FROM @relVeiPec AS vp
INNER JOIN @Veiculos AS v ON v.idVeiculo = vp.idVeiculo
INNER JOIN @Peca AS p ON p.idPeca = vp.idPeca
WHERE EXISTS  (
    select idVeiculo from @pecaObrig po
    join @relVeiPec rcp
    on rcp.idPeca = po.idPeca
    where idVeiculo = vp.idVeiculo
    group by idVeiculo
    having count(rcp.idPeca)  = (select count(*) from @pecaObrig)

SQL

SELECT v.* FROM pecaobrig c
RIGHT JOIN relveipec a on a.idPeca = c.idPeca 
INNER JOIN veiculo v on v.idVeiculo=a.idVeiculo
WHERE not(c.idPeca is null)
GROUP BY a.idVeiculo 
HAVING count(a.idVeiculo) = (SELECT count(idPeca) FROM pecaobrig)

To optimize create a StoredProcedure

DELIMITER $$

CREATE PROCEDURE 'test1'.'VeiculosComPecasObrigatorias' ()
BEGIN

    DECLARE qtde int;
    SET qtde = (SELECT count(idPeca) FROM pecaobrig);

    SELECT v.* FROM pecaobrig c
    RIGHT JOIN relveipec a on a.idPeca = c.idPeca 
    INNER JOIN veiculo v on v.idVeiculo=a.idVeiculo
    WHERE not(c.idPeca is null)
    GROUP BY a.idVeiculo 
    HAVING count(a.idVeiculo) = qtde;          
END