MariaDB [(none)]> source C:\Users\user\Downloads\subconsultas.sql Query OK, 0 rows affected (0.047 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.005 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.020 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.005 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 1 row affected (0.075 sec) Database changed Query OK, 0 rows affected (0.438 sec) Query OK, 0 rows affected (0.075 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 5 rows affected (0.010 sec) Records: 5 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.076 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.071 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 7 rows affected (0.003 sec) Records: 7 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.051 sec) Query OK, 0 rows affected (0.013 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) MariaDB [subconsultas]> show tables; +------------------------+ | Tables_in_subconsultas | +------------------------+ | articulo | | detalle | +------------------------+ 2 rows in set (0.013 sec) MariaDB [subconsultas]> describe articulo; +---------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+---------+-------+ | codigo | char(10) | NO | PRI | NULL | | | articulo | char(40) | NO | | NULL | | | cantidad | int(11) | NO | | NULL | | | valorunitario | int(11) | NO | | NULL | | | existencia | int(11) | NO | | NULL | | +---------------+----------+------+-----+---------+-------+ 5 rows in set (0.172 sec) MariaDB [subconsultas]> describe detalle; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | nrofactura | char(10) | NO | | NULL | | | fecha | date | NO | | NULL | | | cantidad | int(11) | NO | | NULL | | | valorventa | int(11) | NO | | NULL | | | total | int(11) | NO | | NULL | | | codigo | char(10) | NO | | NULL | | +------------+----------+------+-----+---------+----------------+ 7 rows in set (0.110 sec) MariaDB [subconsultas]> select *from articulo; +--------+------------+----------+---------------+------------+ | codigo | articulo | cantidad | valorunitario | existencia | +--------+------------+----------+---------------+------------+ | 150 | nevera | 25 | 950000 | 0 | | 200 | televisor | 11 | 1200000 | 0 | | 250 | estufa | 30 | 750000 | 0 | | 300 | ventilador | 17 | 110000 | 0 | | 350 | lavadora | 13 | 980000 | 0 | +--------+------------+----------+---------------+------------+ 5 rows in set (0.003 sec) MariaDB [subconsultas]> select *from detalle; +----+------------+------------+----------+------------+-------+--------+ | id | nrofactura | fecha | cantidad | valorventa | total | codigo | +----+------------+------------+----------+------------+-------+--------+ | 1 | 1200 | 2010-01-30 | 3 | 0 | 0 | 150 | | 2 | 1250 | 2010-02-13 | 5 | 0 | 0 | 150 | | 3 | 1250 | 2010-02-13 | 7 | 0 | 0 | 250 | | 4 | 1300 | 2010-03-02 | 1 | 0 | 0 | 350 | | 5 | 1300 | 2010-03-02 | 2 | 0 | 0 | 300 | | 6 | 1400 | 2010-03-11 | 3 | 0 | 0 | 200 | | 7 | 1500 | 2010-03-21 | 5 | 0 | 0 | 250 | +----+------------+------------+----------+------------+-------+--------+ 7 rows in set (0.001 sec) MariaDB [subconsultas]> show create table detalle; +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | detalle | CREATE TABLE `detalle` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nrofactura` char(10) NOT NULL, `fecha` date NOT NULL, `cantidad` int(11) NOT NULL, `valorventa` int(11) NOT NULL, `total` int(11) NOT NULL, `codigo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec) MariaDB [subconsultas]> show create table articulo; +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | articulo | CREATE TABLE `articulo` ( `codigo` char(10) NOT NULL, `articulo` char(40) NOT NULL, `cantidad` int(11) NOT NULL, `valorunitario` int(11) NOT NULL, `existencia` int(11) NOT NULL, PRIMARY KEY (`codigo`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec) MariaDB [subconsultas]> update detalle set valorventa = (select valorunitario + (valorunitario * 0.23) from articulo where articulo.codigo = detalle.codigo); Query OK, 7 rows affected (0.078 sec) Rows matched: 7 Changed: 7 Warnings: 0 MariaDB [subconsultas]> select *from detalle; +----+------------+------------+----------+------------+-------+--------+ | id | nrofactura | fecha | cantidad | valorventa | total | codigo | +----+------------+------------+----------+------------+-------+--------+ | 1 | 1200 | 2010-01-30 | 3 | 1168500 | 0 | 150 | | 2 | 1250 | 2010-02-13 | 5 | 1168500 | 0 | 150 | | 3 | 1250 | 2010-02-13 | 7 | 922500 | 0 | 250 | | 4 | 1300 | 2010-03-02 | 1 | 1205400 | 0 | 350 | | 5 | 1300 | 2010-03-02 | 2 | 135300 | 0 | 300 | | 6 | 1400 | 2010-03-11 | 3 | 1476000 | 0 | 200 | | 7 | 1500 | 2010-03-21 | 5 | 922500 | 0 | 250 | +----+------------+------------+----------+------------+-------+--------+ 7 rows in set (0.001 sec) MariaDB [subconsultas]> update detalle set total = cantidad*valorventa; Query OK, 7 rows affected (0.024 sec) Rows matched: 7 Changed: 7 Warnings: 0 MariaDB [subconsultas]> select *from detalle; +----+------------+------------+----------+------------+---------+--------+ | id | nrofactura | fecha | cantidad | valorventa | total | codigo | +----+------------+------------+----------+------------+---------+--------+ | 1 | 1200 | 2010-01-30 | 3 | 1168500 | 3505500 | 150 | | 2 | 1250 | 2010-02-13 | 5 | 1168500 | 5842500 | 150 | | 3 | 1250 | 2010-02-13 | 7 | 922500 | 6457500 | 250 | | 4 | 1300 | 2010-03-02 | 1 | 1205400 | 1205400 | 350 | | 5 | 1300 | 2010-03-02 | 2 | 135300 | 270600 | 300 | | 6 | 1400 | 2010-03-11 | 3 | 1476000 | 4428000 | 200 | | 7 | 1500 | 2010-03-21 | 5 | 922500 | 4612500 | 250 | +----+------------+------------+----------+------------+---------+--------+ 7 rows in set (0.005 sec) MariaDB [subconsultas]> select *from articulo; +--------+------------+----------+---------------+------------+ | codigo | articulo | cantidad | valorunitario | existencia | +--------+------------+----------+---------------+------------+ | 150 | nevera | 25 | 950000 | 0 | | 200 | televisor | 11 | 1200000 | 0 | | 250 | estufa | 30 | 750000 | 0 | | 300 | ventilador | 17 | 110000 | 0 | | 350 | lavadora | 13 | 980000 | 0 | +--------+------------+----------+---------------+------------+ 5 rows in set (0.001 sec) MariaDB [subconsultas]> update articulo set existencia = cantidad - (select sum(cantidad) from detalle where detalle.codigo = articulo.codigo); Query OK, 5 rows affected (0.058 sec) Rows matched: 5 Changed: 5 Warnings: 0 MariaDB [subconsultas]> select *from articulo; +--------+------------+----------+---------------+------------+ | codigo | articulo | cantidad | valorunitario | existencia | +--------+------------+----------+---------------+------------+ | 150 | nevera | 25 | 950000 | 17 | | 200 | televisor | 11 | 1200000 | 8 | | 250 | estufa | 30 | 750000 | 18 | | 300 | ventilador | 17 | 110000 | 15 | | 350 | lavadora | 13 | 980000 | 12 | +--------+------------+----------+---------------+------------+ 5 rows in set (0.001 sec) MariaDB [subconsultas]> Delete from articulo where codigo = (select codigo from detalle where cantidad between 2 and 5 and detalle.codigo = articulo.codigo group by articulo.codigo); Query OK, 4 rows affected (0.074 sec) MariaDB [subconsultas]> select *from articulo; +--------+----------+----------+---------------+------------+ | codigo | articulo | cantidad | valorunitario | existencia | +--------+----------+----------+---------------+------------+ | 350 | lavadora | 13 | 980000 | 12 | +--------+----------+----------+---------------+------------+ 1 row in set (0.001 sec) MariaDB [subconsultas]> create table copia like detalle; Query OK, 0 rows affected (0.264 sec) MariaDB [subconsultas]> describe copia; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | nrofactura | char(10) | NO | | NULL | | | fecha | date | NO | | NULL | | | cantidad | int(11) | NO | | NULL | | | valorventa | int(11) | NO | | NULL | | | total | int(11) | NO | | NULL | | | codigo | char(10) | NO | | NULL | | +------------+----------+------+-----+---------+----------------+ 7 rows in set (0.148 sec) MariaDB [subconsultas]> describe detalle; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | nrofactura | char(10) | NO | | NULL | | | fecha | date | NO | | NULL | | | cantidad | int(11) | NO | | NULL | | | valorventa | int(11) | NO | | NULL | | | total | int(11) | NO | | NULL | | | codigo | char(10) | NO | | NULL | | +------------+----------+------+-----+---------+----------------+ 7 rows in set (0.056 sec) MariaDB [subconsultas]> select *from copia; Empty set (0.002 sec) MariaDB [subconsultas]> insert into copia select*from detalle where month(fecha)=2; Query OK, 2 rows affected (0.021 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [subconsultas]> select *from copia; +----+------------+------------+----------+------------+---------+--------+ | id | nrofactura | fecha | cantidad | valorventa | total | codigo | +----+------------+------------+----------+------------+---------+--------+ | 2 | 1250 | 2010-02-13 | 5 | 1168500 | 5842500 | 150 | | 3 | 1250 | 2010-02-13 | 7 | 922500 | 6457500 | 250 | +----+------------+------------+----------+------------+---------+--------+ 2 rows in set (0.001 sec) MariaDB [subconsultas]> exit