Good morning, I am working on making a sales database BASICO (so as not to complicate me because I just want to understand the basic logic of how to handle tickets in a BD)
I have the following tables
create table cliente(
pk_Cliente int,
cli_Nombre varchar(50),
constraint apk_cliente primary key (pk_Cliente)
)
create table producto(
pk_Producto int,
pro_CodigoBarras bigint,
pro_Nombre varchar(100),
pro_Precio_sinIVA float,
pro_Precio_IncluidoIVA float,
constraint apk_producto primary key (pk_Producto)
)
create table ticket(
pk_Ticket int,
tick_Folio bigint,
tick_Cantidad int,
tick_SubTotal float,
fk_Cliente int,
fk_Producto int,
constraint apk_Ticket primary key (pk_Ticket),
constraint afk_Cliente foreign key (fk_Cliente) references cliente(pk_Cliente),
constraint afk_Producto foreign key (fk_Producto) references producto(pk_Producto)
)
create table total(
pk_total int,
total_Cantidad_Productos int,
total_SinIVA float,
total_ConIVA float,
total_subtotal float,
fk_Ticket int,
constraint pk_total primary key (pk_total),
constraint afk_Ticket foreign key (fk_Ticket) references ticket(pk_Ticket)
)
The detail is in this last table (Total) because when inserting into the total column_Quantity_Products depends on the sum of the number of products, and this number of products can be seen in the ticket table of the column tick_quantity, besides that I have to validate that it is within the same ticket bone the tick_Folio to the iguak that the pk_Ticket
Also not only is the total column_Quantity_Products of the total table, but also total_SinIVA, total_ConIVA, total_subtotal (The total of subtotals is equal to the final total, so I did not put a total of totals or something similar)
All this leads me to perform arithmetic operations and I know what could be done with the select to obtain certain results, but I would like to be able to do it from the insert into, if it is possible, in the same way many will think all that is done from the programming obtaining the data and the operations in the same programming, but I would like to do so to optimize processes. Thanks
I leave the insert of the other tables so that they do not waste so much time filling them and if they want to do tests in the SQL.
insert into cliente values
(1,'Acevedo Manríquez María Mireya'),
(2,'Acevedo Mejía Enrique'),
(3,'Acevedo Ruiz Carolina'),
(4,'Acosta Canto Tomás José'),
(5,'Acosta Gámez Celina'),
(6,'Aguilar Dorantes Irma'),
(7,'Aguilar Lemus María Ofelia'),
(8,'Aguilar Loranca Marcela'),
(9,'Aguilar Pérez Fredy Francisco'),
(10,'Alarcón Licona Salomón');
--TABLA PRODUCTO
insert into producto values
(1,750108545678,'Leche',null,31.60),
(2,750165465487,'Arroz',null,6.69),
(3,750546543218,'Maizena',null,13.93),
(4,750789456432,'Cafe',null,21.99),
(5,750644984652,'Frijol',null,28.00),
(6,750464631344,'Sopa',null,1.69),
(7,750885465426,'Huevos',null,20.80),
(8,750654657132,'Consomate',null,4.99),
(9,750855416774,'Harina de trigo',null,24.76),
(10,75087461321,'Azúcar',null,18.40);
--TABLA TICKET
--Codigo, Folio, Cantidad, Subtotal, Cliente, Producto
insert into ticket values
(1,091020181,4,null,1,1),
(2,091020182,2,null,1,1),
(3,091020183,8,null,1,1),
(4,091020184,4,null,1,1),
(5,091020185,2,null,1,1),
(6,091020186,8,null,1,1),
(7,091020187,60,null,1,1),
(8,091020188,21,null,1,1),
(9,091020189,2,null,1,1),
(10,0910201810,1,null,1,1);