create table based on another table

Question

I have a table called " Product " it has: ID_P, Name, expiration date Now I want to create another table called "Stock" this table has ID, Quantity_of_the_product, product_location_in_the_stock, ID_P (foreign key), and expiration date I want to bring the expiration date from the table product

I am new to SQL oracle so please be patient with me I am using: SQL ORACLE

UPDATE: and is it right if I do this

    CREATE TABLE stock (ID, Quantity_of_the_product,product_location_in_the_stock, ID_P foreign key)
 AS SELECT expiration_date 
FROM Product 
WHERE stock.id_p = product.id_P

Answer 1

First question - why do you want to duplicate the expiration date for any one Product across any number of Stock records? This is denormalisation and you should only do it if you really, really mean to. (If a Product's expiration date can change over time, you might need to model this differently).
Normally, you would only store the ID of the Product record and go and get the expiration date whenever you needed it. YMMV.

Assuming you do want to, something like this should do the trick:

insert into Stock 
( Quantity_of_the_product -- I'm assiming ID is an auto-incremented value 
, product_location_in_the_stock 
, ID_P 
, expiration_date
)
select 
  ? -- Stock Quantity being inserted 
, ? -- Stock Location being inserted
, p.ID_P 
, p.expiration_date 
from Product p 
where p.ID_P = ? -- product ID being inserted 
;

---

Edits from comments:

Products: ID_P, Name, expiration date
Stock: ID, quantity, location, ID_P (foreign key), and expiration date

Read up on Data Normalisation.
Essentially, you want a Single Source of Truth for any data item. If each product has one and only one expiration date, then that value belongs in the Product table. If you need to access that value when looking at Stock items, then you use the product foreign key in the Stock table go and look up that value:

create table products 
( ID_P ... 
, Name ... 
, expiration_date ... 
, primary key ( ID_P ) 
) 
create table stock
( ID ...
, quantity ...
, location ...
, ID_P ... 
, primary key ( ID ) 
, foreign key ID_P references products ( ID_P ) 
); 

select 
  s.ID
, s.quantity
, s.location
, p.expiration_date 
from stock s
inner join products p on s.ID_P = p.ID_P 
; 

... is it right ... CREATE TABLE stock (ID, Quantity_of_the_product, product_location_in_the_stock, ID_P foreign key) AS SELECT expiration_date FROM Product

You cannot create a record in the the stock table using [only] a select on the products table because the data values you need to insert into the stock table (like quantity & location) do not exist in the products table. You need to insert each stock record separately, with its quantity and location. As long as you leave the expiration date in the products table (where it [probably] belongs), you don't even need the sub-select:

insert into stock 
( Quantity_of_the_product
, product_location_in_the_stock
, ID_P 
) 
values 
( :quantity 
, :location 
, :product_id 
); 

Of course, you do need to know the product.id of the relevant product!

I'm assuming here that your product.id column is an auto-incrementing numeric field. If that's the case, then the database can generate a value for this field each time you run an insert on this table. (If not, you'll have to generate and insert that unique value yourself).