Based on the attached table, could you tell me if the resolution for the exercise below is correct?
Show orders that have items that were sold at a discount (discount occurs when the sales price - ITE_PrecoUnitario - is less than the price of "table" - PRO_Preco).
My resolution:
select p.ped_numero, ite_precounitario, pro_preco
from pedido p, item i, produto pr
where p.ped_numero = i.ped_numero
and i.pro_codigo = pr.pro_codigo
and ite_precounitario < pro_preco
It is, but I would put all the prefixes to avoid ambiguities:
select p.ped_numero, i.ite_precounitario, pr.pro_preco
from pedido p, item i, produto pr
where p.ped_numero = i.ped_numero
and i.pro_codigo = pr.pro_codigo
and i.ite_precounitario < pr.pro_preco
It would also pass to the ANSI syntax, which is more readable:
select p.ped_numero, i.ite_precounitario, pr.pro_preco
from pedido p
inner join item i on p.ped_numero = i.ped_numero
inner join produto pr on i.pro_codigo = pr.pro_codigo
where i.ite_precounitario < pr.pro_preco