START TRANSACTION;
-- get next order number
SELECT @ordernum:=MAX(orderNumber)+1 
  FROM orders;

-- insert a new order
INSERT INTO orders (orderNumber, orderDate, customerNumber)
       VALUES(@ordernum, '2022-11-01', 191);

-- insert the line items for the order
INSERT INTO orderdetails(orderNumber, productCode, quantityOrdered, priceEach,orderLineNumber)
       VALUES(@ordernum,'S19-2021', 10, '13', 1),
             (@ordernum,'Q42-77Z', 8, '9',2);

COMMIT;

-- Display the latest order
SELECT * 
  FROM orders AS a 
 INNER JOIN orderdetails b 
 USING (orderNumber) 
 WHERE a.orderNumber=@ordernum;
