bd Facturacion tablas tabla cliente cedula nombre direccion fechainicio tabla detallefactura id nrofactura codigo cantidad valor total tabla factura nrofactura cedula subtotal iva retencion total tabla producto codigo articulo valorunitario cantidad valorventa existencia informacion cliente +--------+-------------------+-----------+-------------+ | cedula | nombre | direccion | fechainicio | +--------+-------------------+-----------+-------------+ | 100 | alexandra lopez | aranjuez | 2003-12-03 | | 200 | sebastian perez | sabaneta | 2005-07-17 | | 300 | sara herrera | floresta | 2007-09-20 | | 400 | luisa roldan | floresta | 2009-10-15 | | 500 | mario henao | bello | 2004-02-28 | | 600 | luisa cifuentes | florencia | 2003-09-01 | | 700 | edith garcia | envigado | 2006-03-03 | | 800 | claudia lainez | itag | 2005-04-13 | | 900 | vivian piedrahita | centro | 2006-01-19 | +--------+-------------------+-----------+-------------+ detallefactura +----+------------+--------+----------+-------+-------+ | id | nrofactura | codigo | cantidad | valor | total | +----+------------+--------+----------+-------+-------+ | 1 | 1001 | 10 | 1 | 0 | 0 | | 2 | 1001 | 40 | 2 | 0 | 0 | | 3 | 1001 | 70 | 1 | 0 | 0 | | 4 | 2002 | 60 | 2 | 0 | 0 | | 5 | 3003 | 20 | 4 | 0 | 0 | | 6 | 3003 | 80 | 1 | 0 | 0 | | 7 | 4004 | 10 | 2 | 0 | 0 | | 8 | 4004 | 20 | 1 | 0 | 0 | | 9 | 4004 | 30 | 1 | 0 | 0 | | 10 | 4004 | 60 | 1 | 0 | 0 | | 11 | 4004 | 70 | 1 | 0 | 0 | | 12 | 4004 | 80 | 2 | 0 | 0 | | 13 | 5005 | 10 | 3 | 0 | 0 | | 14 | 6006 | 20 | 1 | 0 | 0 | | 15 | 6006 | 80 | 2 | 0 | 0 | | 16 | 6006 | 30 | 2 | 0 | 0 | | 17 | 6006 | 90 | 2 | 0 | 0 | | 18 | 6006 | 50 | 1 | 0 | 0 | | 19 | 6006 | 40 | 2 | 0 | 0 | | 20 | 7007 | 10 | 1 | 0 | 0 | | 21 | 7007 | 90 | 2 | 0 | 0 | | 22 | 8008 | 60 | 2 | 0 | 0 | | 23 | 8008 | 40 | 2 | 0 | 0 | | 24 | 8008 | 50 | 1 | 0 | 0 | | 25 | 9009 | 50 | 2 | 0 | 0 | | 26 | 9009 | 80 | 1 | 0 | 0 | | 27 | 1101 | 30 | 1 | 0 | 0 | | 28 | 2202 | 30 | 2 | 0 | 0 | | 29 | 1101 | 60 | 1 | 0 | 0 | | 30 | 3303 | 60 | 2 | 0 | 0 | | 31 | 4404 | 90 | 3 | 0 | 0 | | 32 | 5505 | 80 | 4 | 0 | 0 | | 33 | 6606 | 70 | 2 | 0 | 0 | | 34 | 7707 | 10 | 1 | 0 | 0 | | 35 | 8808 | 40 | 2 | 0 | 0 | | 36 | 9909 | 20 | 3 | 0 | 0 | | 37 | 9909 | 30 | 4 | 0 | 0 | | 38 | 6606 | 50 | 5 | 0 | 0 | | 39 | 7707 | 40 | 1 | 0 | 0 | | 40 | 1101 | 30 | 1 | 0 | 0 | +----+------------+--------+----------+-------+-------+ factura +------------+--------+----------+-----+-----------+-------+ | nrofactura | cedula | subtotal | iva | retencion | total | +------------+--------+----------+-----+-----------+-------+ | 1001 | 100 | 0 | 0 | 0 | 0 | | 2002 | 300 | 0 | 0 | 0 | 0 | | 3003 | 200 | 0 | 0 | 0 | 0 | | 4004 | 400 | 0 | 0 | 0 | 0 | | 5005 | 200 | 0 | 0 | 0 | 0 | | 6006 | 500 | 0 | 0 | 0 | 0 | | 7007 | 100 | 0 | 0 | 0 | 0 | | 8008 | 400 | 0 | 0 | 0 | 0 | | 9009 | 400 | 0 | 0 | 0 | 0 | | 1101 | 600 | 0 | 0 | 0 | 0 | | 2202 | 700 | 0 | 0 | 0 | 0 | | 3303 | 600 | 0 | 0 | 0 | 0 | | 4404 | 100 | 0 | 0 | 0 | 0 | | 5505 | 700 | 0 | 0 | 0 | 0 | | 6606 | 800 | 0 | 0 | 0 | 0 | | 7707 | 900 | 0 | 0 | 0 | 0 | | 8808 | 800 | 0 | 0 | 0 | 0 | | 9909 | 900 | 0 | 0 | 0 | 0 | +------------+--------+----------+-----+-----------+-------+ producto +--------+--------------+---------------+----------+------------+------------+ | codigo | articulo | valorunitario | cantidad | valorventa | existencia | +--------+--------------+---------------+----------+------------+------------+ | 10 | tv | 1250000 | 22 | 0 | 0 | | 20 | auricules | 75000 | 27 | 0 | 0 | | 30 | mp3 | 150000 | 24 | 0 | 0 | | 40 | mouse | 25000 | 33 | 0 | 0 | | 50 | taclado | 130000 | 45 | 0 | 0 | | 60 | disco duro | 203000 | 17 | 0 | 0 | | 70 | unidad dvd | 250000 | 19 | 0 | 0 | | 80 | usb | 80000 | 28 | 0 | 0 | | 90 | lapiz optico | 134000 | 17 | 0 | 0 | +--------+--------------+---------------+----------+------------+------------+ planteamiento - calcular valorventa aumentando un 17% del valorunitario Analisis campo a reemplazar...................... valor venta de la tabla producto con que reeemplazar.....................con valor unitario(de la tabla product0+ (valorunitario*0.17) condiciones..................no como se relacionan () comando que resuelve: update mysql> update producto set valorventa= valorunitario+(valorunitario*0.17); Query OK, 9 rows affected (0.11 sec) Rows matched: 9 Changed: 9 Warnings: 0 mysql> select * from producto; +--------+---------------+--------------+----------+------------+------------+ | codigo | valorunitario | articulo | cantidad | valorventa | existencia | +--------+---------------+--------------+----------+------------+------------+ | 10 | 1250000 | tv | 22 | 1462500 | 0 | | 20 | 75000 | auriculares | 27 | 87750 | 0 | | 30 | 150000 | mp3 | 24 | 175500 | 0 | | 40 | 25000 | mause | 33 | 29250 | 0 | | 50 | 130000 | teclado | 45 | 152100 | 0 | | 60 | 203000 | disco duro | 17 | 237510 | 0 | | 70 | 250000 | unidad dvd | 19 | 292500 | 0 | | 80 | 80000 | usb | 28 | 93600 | 0 | | 90 | 134000 | lapiz optico | 17 | 156780 | 0 | +--------+---------------+--------------+----------+------------+------------+ 9 rows in set (0.00 sec) - llevar el valorventa a valor de detallefactura segun articulo comprado Analisis campo a reemplazar...................... valor detalle factura con que reeemplazar.....................con valor venta producto condiciones..................segun articulo comprado comando que resuelve: update relacion (detallefactura.codigo=producto.codigo) mysql> update detallefactura set valor=(select valorventa from producto where detallefactura.codigo=producto.codigo); Query OK, 40 rows affected (0.08 sec) Rows matched: 40 Changed: 40 Warnings: 0 mysql> select * from detallefactura; +------------------+------------+--------+----------+---------+-------+ | idDETALLEFACTURA | NROFACTURA | codigo | cantidad | valor | total | +------------------+------------+--------+----------+---------+-------+ | 1 | 1001 | 10 | 1 | 1462500 | 0 | | 10 | 4004 | 60 | 1 | 237510 | 0 | | 11 | 4004 | 70 | 1 | 292500 | 0 | | 12 | 4004 | 80 | 2 | 93600 | 0 | | 13 | 5005 | 10 | 3 | 1462500 | 0 | | 14 | 6006 | 20 | 1 | 87750 | 0 | | 15 | 6006 | 80 | 2 | 93600 | 0 | | 16 | 6006 | 30 | 2 | 175500 | 0 | | 17 | 6006 | 90 | 2 | 156780 | 0 | | 18 | 6006 | 50 | 1 | 152100 | 0 | | 19 | 6006 | 40 | 2 | 29250 | 0 | | 2 | 1001 | 40 | 2 | 29250 | 0 | | 20 | 7007 | 10 | 1 | 1462500 | 0 | | 21 | 7007 | 90 | 2 | 156780 | 0 | | 22 | 8008 | 60 | 2 | 237510 | 0 | | 23 | 8008 | 40 | 2 | 29250 | 0 | | 24 | 8008 | 50 | 1 | 152100 | 0 | | 25 | 9009 | 50 | 2 | 152100 | 0 | | 26 | 9009 | 80 | 1 | 93600 | 0 | | 27 | 1101 | 30 | 1 | 175500 | 0 | | 28 | 2202 | 30 | 2 | 175500 | 0 | | 29 | 1101 | 60 | 1 | 237510 | 0 | | 3 | 1001 | 70 | 1 | 292500 | 0 | | 30 | 3303 | 60 | 2 | 237510 | 0 | | 31 | 4404 | 90 | 3 | 156780 | 0 | | 32 | 5505 | 80 | 4 | 93600 | 0 | | 33 | 6606 | 70 | 2 | 292500 | 0 | | 34 | 7707 | 10 | 1 | 1462500 | 0 | | 35 | 8808 | 40 | 2 | 29250 | 0 | | 36 | 9909 | 20 | 3 | 87750 | 0 | | 37 | 9909 | 30 | 4 | 175500 | 0 | | 38 | 6606 | 50 | 5 | 152100 | 0 | | 39 | 7707 | 40 | 1 | 29250 | 0 | | 4 | 2002 | 60 | 2 | 237510 | 0 | | 40 | 1101 | 30 | 1 | 175500 | 0 | | 5 | 3003 | 20 | 4 | 87750 | 0 | | 6 | 3003 | 80 | 1 | 93600 | 0 | | 7 | 4004 | 10 | 2 | 1462500 | 0 | | 8 | 4004 | 20 | 1 | 87750 | 0 | | 9 | 4004 | 30 | 1 | 175500 | 0 | +------------------+------------+--------+----------+---------+-------+ 40 rows in set (0.00 sec) mysql> - calcular el total en la tabla detallefactura (cantidad x valor) analisis campo a reemplazar..............total con que.....................cantidad*valor comando..................update mysql> update detallefactura set total=cantidad*valor; Query OK, 40 rows affected (0.05 sec) Rows matched: 40 Changed: 40 Warnings: 0 mysql> select* from detallefactura; +------------------+------------+--------+----------+---------+---------+ | idDETALLEFACTURA | NROFACTURA | codigo | cantidad | valor | total | +------------------+------------+--------+----------+---------+---------+ | 1 | 1001 | 10 | 1 | 1462500 | 1462500 | | 10 | 4004 | 60 | 1 | 237510 | 237510 | | 11 | 4004 | 70 | 1 | 292500 | 292500 | | 12 | 4004 | 80 | 2 | 93600 | 187200 | | 13 | 5005 | 10 | 3 | 1462500 | 4387500 | | 14 | 6006 | 20 | 1 | 87750 | 87750 | | 15 | 6006 | 80 | 2 | 93600 | 187200 | | 16 | 6006 | 30 | 2 | 175500 | 351000 | | 17 | 6006 | 90 | 2 | 156780 | 313560 | | 18 | 6006 | 50 | 1 | 152100 | 152100 | | 19 | 6006 | 40 | 2 | 29250 | 58500 | | 2 | 1001 | 40 | 2 | 29250 | 58500 | | 20 | 7007 | 10 | 1 | 1462500 | 1462500 | | 21 | 7007 | 90 | 2 | 156780 | 313560 | | 22 | 8008 | 60 | 2 | 237510 | 475020 | | 23 | 8008 | 40 | 2 | 29250 | 58500 | | 24 | 8008 | 50 | 1 | 152100 | 152100 | | 25 | 9009 | 50 | 2 | 152100 | 304200 | | 26 | 9009 | 80 | 1 | 93600 | 93600 | | 27 | 1101 | 30 | 1 | 175500 | 175500 | | 28 | 2202 | 30 | 2 | 175500 | 351000 | | 29 | 1101 | 60 | 1 | 237510 | 237510 | | 3 | 1001 | 70 | 1 | 292500 | 292500 | | 30 | 3303 | 60 | 2 | 237510 | 475020 | | 31 | 4404 | 90 | 3 | 156780 | 470340 | | 32 | 5505 | 80 | 4 | 93600 | 374400 | | 33 | 6606 | 70 | 2 | 292500 | 585000 | | 34 | 7707 | 10 | 1 | 1462500 | 1462500 | | 35 | 8808 | 40 | 2 | 29250 | 58500 | | 36 | 9909 | 20 | 3 | 87750 | 263250 | | 37 | 9909 | 30 | 4 | 175500 | 702000 | | 38 | 6606 | 50 | 5 | 152100 | 760500 | | 39 | 7707 | 40 | 1 | 29250 | 29250 | | 4 | 2002 | 60 | 2 | 237510 | 475020 | | 40 | 1101 | 30 | 1 | 175500 | 175500 | | 5 | 3003 | 20 | 4 | 87750 | 351000 | | 6 | 3003 | 80 | 1 | 93600 | 93600 | | 7 | 4004 | 10 | 2 | 1462500 | 2925000 | | 8 | 4004 | 20 | 1 | 87750 | 87750 | | 9 | 4004 | 30 | 1 | 175500 | 175500 | +------------------+------------+--------+----------+---------+---------+ 40 rows in set (0.02 sec) mysql> Analisis - calcular cuanto es el subtotal de cada factura y guardarlo en el campo respectivo campo a reemplazar..............subtotal tabla factura con que.....................sum(total) detallefactura comando..................update relacion...................detallefactura.nrofactura=factura.nrofactura mysql> update factura set subtotal=(select sum(total) from detallefactura where detallefactura.nrofactura=factura.nrofactura); Query OK, 18 rows affected (0.14 sec) Rows matched: 18 Changed: 18 Warnings: 0 mysql> select * from factura; +------------+--------+----------+------+-----------+-------+ | Nrofactura | cedula | subtotal | iva | retencion | total | +------------+--------+----------+------+-----------+-------+ | 1001 | 100 | 1813500 | NULL | 0 | 0 | | 1101 | 600 | 588510 | NULL | 0 | 0 | | 2002 | 300 | 475020 | NULL | 0 | 0 | | 2202 | 700 | 351000 | NULL | 0 | 0 | | 3003 | 200 | 444600 | NULL | 0 | 0 | | 3303 | 600 | 475020 | NULL | 0 | 0 | | 4004 | 400 | 3905460 | NULL | 0 | 0 | | 4404 | 100 | 470340 | NULL | 0 | 0 | | 5005 | 200 | 4387500 | NULL | 0 | 0 | | 5505 | 700 | 374400 | NULL | 0 | 0 | | 6006 | 500 | 1150110 | NULL | 0 | 0 | | 6606 | 800 | 1345500 | NULL | 0 | 0 | | 7007 | 100 | 1776060 | NULL | 0 | 0 | | 7707 | 900 | 1491750 | NULL | 0 | 0 | | 8008 | 400 | 685620 | NULL | 0 | 0 | | 8808 | 800 | 58500 | NULL | 0 | 0 | | 9009 | 400 | 397800 | NULL | 0 | 0 | | 9909 | 900 | 965250 | NULL | 0 | 0 | +------------+--------+----------+------+-----------+-------+ 18 rows in set (0.00 sec) - calcular el iva (16% del subtotal), la retencion(3.5% del subtotal) y el total(subtotal + iva - retencion) Analisis campo a reemplazar..............iva factura con que.....................subtoral*0.16 de factura comando..................update mysql> update factura set iva=subtotal*0.16; Query OK, 18 rows affected (0.36 sec) Rows matched: 18 Changed: 18 Warnings: 0 mysql> select * from factura; +------------+--------+----------+--------------------+-----------+-------+ | Nrofactura | cedula | subtotal | iva | retencion | total | +------------+--------+----------+--------------------+-----------+-------+ | 1001 | 100 | 1813500 | 290160 | 0 | 0 | | 1101 | 600 | 588510 | 94161.6 | 0 | 0 | | 2002 | 300 | 475020 | 76003.2 | 0 | 0 | | 2202 | 700 | 351000 | 56160 | 0 | 0 | | 3003 | 200 | 444600 | 71136 | 0 | 0 | | 3303 | 600 | 475020 | 76003.2 | 0 | 0 | | 4004 | 400 | 3905460 | 624873.6 | 0 | 0 | | 4404 | 100 | 470340 | 75254.40000000001 | 0 | 0 | | 5005 | 200 | 4387500 | 702000 | 0 | 0 | | 5505 | 700 | 374400 | 59904 | 0 | 0 | | 6006 | 500 | 1150110 | 184017.6 | 0 | 0 | | 6606 | 800 | 1345500 | 215280 | 0 | 0 | | 7007 | 100 | 1776060 | 284169.60000000003 | 0 | 0 | | 7707 | 900 | 1491750 | 238680 | 0 | 0 | | 8008 | 400 | 685620 | 109699.2 | 0 | 0 | | 8808 | 800 | 58500 | 9360 | 0 | 0 | | 9009 | 400 | 397800 | 63648 | 0 | 0 | | 9909 | 900 | 965250 | 154440 | 0 | 0 | +------------+--------+----------+--------------------+-----------+-------+ 18 rows in set (0.02 sec) Analisis campo a reemplazar..............retencion factura con que.....................subtoral*0.035 de factura comando..................update mysql> update factura set retencion=subtotal*0.035; Query OK, 18 rows affected (0.09 sec) Rows matched: 18 Changed: 18 Warnings: 0 mysql> select * from factura; +------------+--------+----------+--------------------+--------------------+-------+ | Nrofactura | cedula | subtotal | iva | retencion | total | +------------+--------+----------+--------------------+--------------------+-------+ | 1001 | 100 | 1813500 | 290160 | 63472.50000000001 | 0 | | 1101 | 600 | 588510 | 94161.6 | 20597.850000000002 | 0 | | 2002 | 300 | 475020 | 76003.2 | 16625.7 | 0 | | 2202 | 700 | 351000 | 56160 | 12285.000000000002 | 0 | | 3003 | 200 | 444600 | 71136 | 15561.000000000002 | 0 | | 3303 | 600 | 475020 | 76003.2 | 16625.7 | 0 | | 4004 | 400 | 3905460 | 624873.6 | 136691.1 | 0 | | 4404 | 100 | 470340 | 75254.40000000001 | 16461.9 | 0 | | 5005 | 200 | 4387500 | 702000 | 153562.50000000003 | 0 | | 5505 | 700 | 374400 | 59904 | 13104.000000000002 | 0 | | 6006 | 500 | 1150110 | 184017.6 | 40253.850000000006 | 0 | | 6606 | 800 | 1345500 | 215280 | 47092.50000000001 | 0 | | 7007 | 100 | 1776060 | 284169.60000000003 | 62162.100000000006 | 0 | | 7707 | 900 | 1491750 | 238680 | 52211.25000000001 | 0 | | 8008 | 400 | 685620 | 109699.2 | 23996.7 | 0 | | 8808 | 800 | 58500 | 9360 | 2047.5000000000002 | 0 | | 9009 | 400 | 397800 | 63648 | 13923.000000000002 | 0 | | 9909 | 900 | 965250 | 154440 | 33783.75 | 0 | +------------+--------+----------+--------------------+--------------------+-------+ 18 rows in set (0.00 sec) Analisis campo a reemplazar..............total factura con que.....................subtoral+iva - retencion de factura comando..................update mysql> update factura set total=subtotal+iva-retencion; Query OK, 18 rows affected (0.36 sec) Rows matched: 18 Changed: 18 Warnings: 0 mysql> select * from factura; +------------+--------+----------+--------------------+--------------------+------------+ | Nrofactura | cedula | subtotal | iva | retencion | total | +------------+--------+----------+--------------------+--------------------+------------+ | 1001 | 100 | 1813500 | 290160 | 63472.50000000001 | 2040187.5 | | 1101 | 600 | 588510 | 94161.6 | 20597.850000000002 | 662073.75 | | 2002 | 300 | 475020 | 76003.2 | 16625.7 | 534397.5 | | 2202 | 700 | 351000 | 56160 | 12285.000000000002 | 394875 | | 3003 | 200 | 444600 | 71136 | 15561.000000000002 | 500175 | | 3303 | 600 | 475020 | 76003.2 | 16625.7 | 534397.5 | | 4004 | 400 | 3905460 | 624873.6 | 136691.1 | 4393642.5 | | 4404 | 100 | 470340 | 75254.40000000001 | 16461.9 | 529132.5 | | 5005 | 200 | 4387500 | 702000 | 153562.50000000003 | 4935937.5 | | 5505 | 700 | 374400 | 59904 | 13104.000000000002 | 421200 | | 6006 | 500 | 1150110 | 184017.6 | 40253.850000000006 | 1293873.75 | | 6606 | 800 | 1345500 | 215280 | 47092.50000000001 | 1513687.5 | | 7007 | 100 | 1776060 | 284169.60000000003 | 62162.100000000006 | 1998067.5 | | 7707 | 900 | 1491750 | 238680 | 52211.25000000001 | 1678218.75 | | 8008 | 400 | 685620 | 109699.2 | 23996.7 | 771322.5 | | 8808 | 800 | 58500 | 9360 | 2047.5000000000002 | 65812.5 | | 9009 | 400 | 397800 | 63648 | 13923.000000000002 | 447525 | | 9909 | 900 | 965250 | 154440 | 33783.75 | 1085906.25 | +------------+--------+----------+--------------------+--------------------+------------+ 18 rows in set (0.00 sec) - calcular cuales son las existencias de cada articulo Analisis campo a reemplazar.................................existencias con que...............cantidad - suma(cantidad )de detallefactura condiciones ................segun la tabla detalle relaciones................. detallefactura.codigo=producto.codigo comando....update mysql> update producto set existencia=cantidad-(select sum(cantidad) from detallefactura where detallefactura.codigo=producto.codigo); Query OK, 9 rows affected (0.08 sec) Rows matched: 9 Changed: 9 Warnings: 0 mysql> select * from producto; +--------+---------------+--------------+----------+------------+------------+ | codigo | valorunitario | articulo | cantidad | valorventa | existencia | +--------+---------------+--------------+----------+------------+------------+ | 10 | 1250000 | tv | 22 | 1462500 | 14 | | 20 | 75000 | auriculares | 27 | 87750 | 18 | | 30 | 150000 | mp3 | 24 | 175500 | 13 | | 40 | 25000 | mause | 33 | 29250 | 24 | | 50 | 130000 | teclado | 45 | 152100 | 36 | | 60 | 203000 | disco duro | 17 | 237510 | 9 | | 70 | 250000 | unidad dvd | 19 | 292500 | 15 | | 80 | 80000 | usb | 28 | 93600 | 18 | | 90 | 134000 | lapiz optico | 17 | 156780 | 10 | +--------+---------------+--------------+----------+------------+------------+ 9 rows in set (0.00 sec) mysql> - total de facturas de cada cliente mysql> select factura.cedula, cliente.nombre, sum(factura.total)as 'total' from factura inner join cliente on cliente.cedula=factura.cedula group by factura.cedula; +--------+-------------------+------------+ | cedula | nombre | total | +--------+-------------------+------------+ | 100 | ALEXANDRA LOPEZ | 4567387.5 | | 200 | SEBASTIAN PEREZ | 5436112.5 | | 300 | SARA HERRERA | 534397.5 | | 400 | LUISA ROLDAN | 5612490 | | 500 | MARIO HENAO | 1293873.75 | | 600 | LUISA CIFUENTES | 1196471.25 | | 700 | EDITH GARCIA | 816075 | | 800 | CLAUDIA LAINEZ | 1579500 | | 900 | VIVIAN PIEDRAHITA | 2764125 | +--------+-------------------+------------+ 9 rows in set (0.00 sec) - listar el total de ventas de cada producto mysql> select detallefactura.codigo, producto.articulo, sum(detallefactura.cantidad) from detallefactura inner join producto on producto.codigo=detallefactura.codigo group by detallefactura.codigo; +--------+--------------+------------------------------+ | codigo | articulo | sum(detallefactura.cantidad) | +--------+--------------+------------------------------+ | 10 | TV | 8 | | 20 | AURICULARES | 9 | | 30 | MP3 | 11 | | 40 | MOUSE | 9 | | 50 | TECLADO | 9 | | 60 | DISCO DURO | 8 | | 70 | UNIDAD DVD | 4 | | 80 | USB | 10 | | 90 | LAPIZ OPTICO | 7 | +--------+--------------+------------------------------+ 9 rows in set (0.00 sec) - listar las facturas y el valor de cada una segun las compra de alexandra lopez mysql> select factura.cedula, cliente.nombre, factura.nrofactura,factura.total from factura inner join cliente on cliente.cedula=factura.cedula where cliente.cedula='100'; +--------+-----------------+------------+---------+ | cedula | nombre | nrofactura | total | +--------+-----------------+------------+---------+ | 100 | ALEXANDRA LOPEZ | 1001 | 2040190 | | 100 | ALEXANDRA LOPEZ | 4404 | 529132 | | 100 | ALEXANDRA LOPEZ | 7007 | 1998070 | +--------+-----------------+------------+---------+ 3 rows in set (0.00 sec) -crear una tabla detallado dentro de la bd subconsultas con los campos id auto incremento cedula nrofactura totalarticulos valortotal mysql> describe detallado; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+----------------+ | idDETALLADO | int(11) | NO | PRI | NULL | auto_increment | | Cedula | varchar(45) | NO | | NULL | | | nrofactura | varchar(45) | NO | | NULL | | | totalarticulos | int(11) | YES | | NULL | | | volartotal | double | YES | | NULL | | +----------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.02 sec) crear una subconsulta para ingresar estos datos a la tabla nueva cedula del cliente numero de facturas del cliente suma de los articulos de cada factura total pagado de cada factura mysql> insert into detallado (cedula, nrofactura, totalarticulo, valortotal) (select factura.cedula, factura.nrofactura, sum(detallefactura.cantidad), factura.total from factura inner join detallefactura on detallefactura.nrofactura=factura.nrofactura group by detallefactura.nrofactura); Query OK, 18 rows affected (0.11 sec) Records: 18 Duplicates: 0 Warnings: 0 mysql> select * from detallado; +----+--------+------------+---------------+------------+ | id | cedula | nrofactura | totalarticulo | valortotal | +----+--------+------------+---------------+------------+ | 1 | 100 | 1001 | 4 | 2040190 | | 2 | 600 | 1101 | 3 | 662074 | | 3 | 300 | 2002 | 2 | 534398 | | 4 | 700 | 2202 | 2 | 394875 | | 5 | 200 | 3003 | 5 | 500175 | | 6 | 600 | 3303 | 2 | 534398 | | 7 | 400 | 4004 | 8 | 4393640 | | 8 | 100 | 4404 | 3 | 529132 | | 9 | 200 | 5005 | 3 | 4935940 | | 10 | 700 | 5505 | 4 | 421200 | | 11 | 500 | 6006 | 10 | 1293870 | | 12 | 800 | 6606 | 7 | 1513690 | | 13 | 100 | 7007 | 3 | 1998070 | | 14 | 900 | 7707 | 2 | 1678220 | | 15 | 400 | 8008 | 5 | 771322 | | 16 | 800 | 8808 | 2 | 65812.5 | | 17 | 400 | 9009 | 3 | 447525 | | 18 | 900 | 9909 | 7 | 1085910 | +----+--------+------------+---------------+------------+ 18 rows in set (0.00 sec) - eliminar las facturas de los clientes que hayan iniciado vinculo con la empresa antes de 2004 mysql> delete detallado.* from detallado inner join cliente on detallado.cedula=cliente.cedula where fechainicio<'2004-01-01'; Query OK, 5 rows affected (0.22 sec) mysql> select * from detallado; +----+--------+------------+---------------+------------+ | id | cedula | nrofactura | totalarticulo | valortotal | +----+--------+------------+---------------+------------+ | 3 | 300 | 2002 | 2 | 534398 | | 4 | 700 | 2202 | 2 | 394875 | | 5 | 200 | 3003 | 5 | 500175 | | 7 | 400 | 4004 | 8 | 4393640 | | 9 | 200 | 5005 | 3 | 4935940 | | 10 | 700 | 5505 | 4 | 421200 | | 11 | 500 | 6006 | 10 | 1293870 | | 12 | 800 | 6606 | 7 | 1513690 | | 14 | 900 | 7707 | 2 | 1678220 | | 15 | 400 | 8008 | 5 | 771322 | | 16 | 800 | 8808 | 2 | 65812.5 | | 17 | 400 | 9009 | 3 | 447525 | | 18 | 900 | 9909 | 7 | 1085910 | +----+--------+------------+---------------+------------+ 13 rows in set (0.00 sec)