===============================================================================================
  SQLITE FROM SCRATCH
  Didactic Python code · Kardex Tauro · kardex-tauro.muisca.co
===============================================================================================
Products read from the CSV: 8

===============================================================================================
STEP 1: CREATE THE DATABASE AND THE TABLE
===============================================================================================
SQL: CREATE TABLE IF NOT EXISTS productos (codigo TEXT PRIMARY KEY, producto TEXT NOT NULL, categoria TEXT NOT NULL, precio INTEGER NOT NULL, existencias INTEGER NOT NULL)
Products table ready.

===============================================================================================
STEP 2: INSERT THE PRODUCTS
===============================================================================================
SQL: INSERT INTO productos (codigo, producto, categoria, precio, existencias) VALUES (?, ?, ?, ?, ?)
Products inserted: 8

===============================================================================================
STEP 3: QUERY (SELECT ... ORDER BY)
===============================================================================================
SQL: SELECT codigo, producto, categoria, precio, existencias FROM productos ORDER BY codigo
-----------------------------------------------------------------------------------------------
Code    Product                      Category               Price       Stock             Value
-----------------------------------------------------------------------------------------------
E01     THHN 12 AWG wire (meter)     Electrical          3,600.00         500      1,800,000.00
E02     Breaker 20 A                 Electrical         42,800.00          24      1,027,200.00
E03     Black insulating tape        Electrical          5,900.00         200      1,180,000.00
H01     650 W hammer drill           Tools             289,900.00          12      3,478,800.00
H02     16 oz ball hammer            Tools              45,900.00          40      1,836,000.00
H03     10 in adjustable wrench      Tools              62,900.00          18      1,132,200.00
P01     PVC pipe 1/2 in x 3 m        Plumbing           18,900.00         120      2,268,000.00
P02     1/2 in shut-off valve        Plumbing           34,500.00          35      1,207,500.00
-----------------------------------------------------------------------------------------------
Rows queried: 8

===============================================================================================
STEP 4: UPDATE ONE PRICE (UPDATE)
===============================================================================================
SQL: SELECT precio FROM productos WHERE codigo = ?
Code updated: E01
Price before: 3,600.00
SQL: UPDATE productos SET precio = ? WHERE codigo = ?
Price after: 3,900.00
Rows changed by the UPDATE: 1

===============================================================================================
STEP 5: DELETE ONE PRODUCT (DELETE)
===============================================================================================
SQL: DELETE FROM productos WHERE codigo = ?
Code deleted: E03
Rows deleted by the DELETE: 1
SQL: SELECT COUNT(*) FROM productos
Products left in the table: 7

===============================================================================================
STEP 6: BUSINESS QUERY (SUM ... GROUP BY)
===============================================================================================
SQL: SELECT COUNT(*), SUM(precio * existencias) FROM productos
Products in the table: 7
SQL: SELECT categoria, COUNT(*) AS articulos, SUM(precio * existencias) AS valor FROM productos GROUP BY categoria ORDER BY valor DESC
INVENTORY VALUE BY CATEGORY
--------------------------------------------------------
Category         Products             Value   % of value
--------------------------------------------------------
Tools                   3      6,447,000.00      49.98 %
Plumbing                2      3,475,500.00      26.94 %
Electrical              2      2,977,200.00      23.08 %
--------------------------------------------------------
Check (sum of categories = total): TIES

===============================================================================================
STEP 7: CLOSE AND COMMIT
===============================================================================================
Leaving the with block makes the connection commit the pending changes.
Had anything failed before, the with block would undo it and the database would stay as it was.
Connection closed. The database was saved.

===============================================================================================
CLOSING
===============================================================================================
  Products in the table: 7
  Total inventory value: 12,899,700.00
  File written: salida/tienda.db
