Add information from two tables to second table

I’m a newbie in query building.

I created two tables:

product:

productid | name | price

order:

id | productid | quantity | fullprice

Can I automatically calculate fullprice (product.price * order.quantity), when I add new record values (productid, quantity)?

Answer

You can join:

select p.*, o.quantity, o.quantity * p.price as fullprice
from products p
inner join orders o on o.productid = p.productid

I would not recommend storing fullprice. This is derived information, that can be computed on the fly when needed, using above query. If you are going to use the query often, you might want to create a view:

create view v_product_orders as
select p.*, o.quantity, o.quantity * p.price as fullprice
from products p
inner join orders o on o.productid = p.productid

If you want to store the fullprice, then you need to create an insert trigger, which makes your schema more complex.