How can I resolve this query?

It is not necessary to use subqueries in this case, it would be advisable to use a INNER JOIN to join the records found in tables vehiculo and venta using the foreign key placa .

In the condition ( WHERE ) you must indicate both the brand and the year in which the sale was made (ex: 2017), if the date has the format YYYY-MM-DD use YEAR() to obtain only the year.

With the clause ORDER BY you can order the sales in a descending way taking into account the price, that is, from the most expensive to the cheapest and with ROW_NUM limit the query so that you only return the first record ( the most expensive sale of all).

SELECT placa
FROM vehiculo
INNER JOIN venta ON vehiculo.placa = venta.vehiculo
WHERE marca = "mazda" AND YEAR(fecha) = "2017"
ORDER BY precio DESC
WHERE ROWNUM = 1;

Normalization

Consists of a process in which rules are applied on each of the tables to avoid redundancy of data, to reduce problems when updating data and to protect the referential integrity of data.

Rules

• One or more vehicles will have the same model
• One or more models will belong to a brand
• Only by transitive dependence a vehicle will belong to a brand, that is, a vehicle to be a model x will have a brand y

Vehículo > Modelo > Marca

Problem

In your table vehiculo the columns modelo and marca are going to have data redundancy, which means that the same values will be repeated in multiple registers ( That's bad! ).

The problems that could cause you are:

• Take up more disk space
• Difficulty queries SQL especially if there are values that are not identical ( masda and mazda )

Solution

New tables are created to avoid redundancy of the data.

• Create a table marca with the columns id_marca and nombre
• Create a table modelo with the columns id_modelo , nombre and id_marca (FK)
• Modify table vehiculo , delete columns marca and modelo and add the foreign key id_modelo

How would the SQL query look?

Assuming the id_marca 1 belongs to the "Mazda" brand.

SELECT placa
FROM vehiculo
INNER JOIN modelo ON vehiculo.id_modelo = modelo.id_modelo
INNER JOIN venta ON vehiculo.placa = venta.vehiculo
WHERE modelo.id_marca = 1 AND YEAR(venta.fecha) = "2017"
ORDER BY venta.precio DESC
WHERE ROWNUM = 1;

Select veh.placa from vehiculo veh, 
      venta ven where veh.placa=ven.vehiculo and ven.precio >= 
      select max(precio) from venta venx , vehiculo vehx 
            where venx.vehiculo = vehx.placa and vehx.marca = 'mazda'