Microsoft Windows [Versión 10.0.26100.4061] (c) Microsoft Corporation. Todos los derechos reservados. C:\Users\Yeison>cd/xampp/mysql/bin C:\xampp\mysql\bin>mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.4.32-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | basedatosii | | basedatosiii | | collar | | createinsert | | cursos | | green | | iis | | information_schema | | inventario | | mysql | | parcial1 | | parcial2 | | performance_schema | | phpmyadmin | | proyecto | | prueba | | test | | turismo | | veterinaria | | yeison_roa | +--------------------+ 20 rows in set (0.043 sec) MariaDB [(none)]> use inventario; Database changed MariaDB [inventario]> show tables; +----------------------+ | Tables_in_inventario | +----------------------+ | alertas_inventario | | cliente | | detalles_devolucion | | detalles_pedido | | detalles_venta | | devoluciones | | inventario_historico | | pedidos_proveedores | | producto | | proveedores | | venta | +----------------------+ 11 rows in set (0.003 sec) MariaDB [inventario]> describe devoluciones; +---------------+------------------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------------------------------------------------+------+-----+---------+-------+ | id_devolucion | varchar(20) | NO | PRI | NULL | | | Fecha | date | NO | | NULL | | | Motivo | enum('defectuoso','equivocacion','insatisfaccion','otros') | NO | | NULL | | | Estado | enum('pendiente','aprobada','rechazada','procesada') | NO | | NULL | | | FK_venta | varchar(20) | NO | MUL | NULL | | | Comentarios | text | YES | | NULL | | | Evidencia | blob | YES | | NULL | | +---------------+------------------------------------------------------------+------+-----+---------+-------+ 7 rows in set (0.059 sec) MariaDB [inventario]> describe devoluciones; +---------------+------------------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------------------------------------------------+------+-----+---------+-------+ | id_devolucion | varchar(20) | NO | PRI | NULL | | | Fecha | date | NO | | NULL | | | Motivo | enum('defectuoso','equivocacion','insatisfaccion','otros') | NO | | NULL | | | Estado | enum('pendiente','aprobada','rechazada','procesada') | NO | | NULL | | | FK_venta | varchar(20) | NO | MUL | NULL | | | Comentarios | text | YES | | NULL | | | Evidencia | blob | YES | | NULL | | +---------------+------------------------------------------------------------+------+-----+---------+-------+ 7 rows in set (0.016 sec) MariaDB [inventario]> describe alertas_inventario; +-------------+--------------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------------------------------------------------+------+-----+---------+----------------+ | id_alerta | int(11) | NO | PRI | NULL | auto_increment | | FK_producto | varchar(20) | NO | MUL | NULL | | | Tipo_alerta | enum('stock_minimo','stock_sobrante','sin_movimiento') | NO | | NULL | | | Fecha | date | NO | | NULL | | | Leida | tinyint(1) | YES | | 0 | | +-------------+--------------------------------------------------------+------+-----+---------+----------------+ 5 rows in set (0.022 sec) MariaDB [inventario]> show tables; +----------------------+ | Tables_in_inventario | +----------------------+ | alertas_inventario | | cliente | | detalles_devolucion | | detalles_pedido | | detalles_venta | | devoluciones | | inventario_historico | | pedidos_proveedores | | producto | | proveedores | | venta | +----------------------+ 11 rows in set (0.001 sec) MariaDB [inventario]> describe cliente; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id_cliente | varchar(20) | NO | PRI | NULL | | | Nombre | varchar(45) | NO | | NULL | | | Telefono | varchar(45) | NO | | NULL | | | Ciudad | varchar(45) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 4 rows in set (0.041 sec) MariaDB [inventario]> describe detalles_devolucion; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | id_detalle_dev | varchar(20) | NO | PRI | NULL | | | Cantidad | int(11) | NO | | NULL | | | FK_devolucion | varchar(20) | NO | MUL | NULL | | | FK_producto | varchar(20) | NO | MUL | NULL | | +----------------+-------------+------+-----+---------+-------+ 4 rows in set (0.028 sec) MariaDB [inventario]> describe detalles_pedido; +-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | id_detallePedi | varchar(20) | NO | PRI | NULL | | | Nombre | varchar(45) | NO | | NULL | | | camtidad_pedido | int(45) | NO | | NULL | | | Precio_compra | int(45) | NO | | NULL | | | FK_pedidoProve | varchar(20) | NO | MUL | NULL | | | FK_produto | varchar(20) | NO | MUL | NULL | | +-----------------+-------------+------+-----+---------+-------+ 6 rows in set (0.045 sec) MariaDB [inventario]> show tables; +----------------------+ | Tables_in_inventario | +----------------------+ | alertas_inventario | | cliente | | detalles_devolucion | | detalles_pedido | | detalles_venta | | devoluciones | | inventario_historico | | pedidos_proveedores | | producto | | proveedores | | venta | +----------------------+ 11 rows in set (0.001 sec) MariaDB [inventario]> describe detalles_venta; +------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+-------+ | id_detalleVent | varchar(20) | NO | PRI | NULL | | | Cantidad_vendida | int(45) | NO | | NULL | | | FK_venta | varchar(20) | NO | MUL | NULL | | | FK_producto | varchar(20) | NO | MUL | NULL | | +------------------+-------------+------+-----+---------+-------+ 4 rows in set (0.031 sec) MariaDB [inventario]> describe inventario_historico; +-------------------+----------------------------------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+----------------------------------------------+------+-----+---------------------+----------------+ | id_registro | int(11) | NO | PRI | NULL | auto_increment | | fecha | timestamp | NO | | current_timestamp() | | | FK_producto | varchar(20) | NO | MUL | NULL | | | cambio_cantidad | int(11) | NO | | NULL | | | tipo_movimiento | enum('compra','venta','devolucion','ajuste') | NO | | NULL | | | origen_movimiento | varchar(20) | YES | | NULL | | +-------------------+----------------------------------------------+------+-----+---------------------+----------------+ 6 rows in set (0.038 sec) MariaDB [inventario]> describe pedidos_proveedores; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | id_pedidoProve | varchar(20) | NO | PRI | NULL | | | Fecha | varchar(45) | NO | | NULL | | | FK_proveedor | varchar(20) | NO | MUL | NULL | | +----------------+-------------+------+-----+---------+-------+ 3 rows in set (0.042 sec) MariaDB [inventario]> describe producto; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id_producto | varchar(20) | NO | PRI | NULL | | | Nombre | varchar(45) | NO | | NULL | | | Precio_costo | int(45) | NO | | NULL | | | Precio_venta | int(45) | NO | | NULL | | | Disponible | int(45) | NO | | NULL | | | stock_minimo | int(11) | NO | | 10 | | | stock_maximo | int(11) | YES | | NULL | | | imagen | blob | YES | | NULL | | | activo | tinyint(1) | YES | | 1 | | +--------------+-------------+------+-----+---------+-------+ 9 rows in set (0.033 sec) MariaDB [inventario]> describe proveedores; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id_proveedor | varchar(20) | NO | PRI | NULL | | | Nombre | varchar(45) | NO | | NULL | | | Telefono | varchar(45) | NO | | NULL | | | Ciudad | varchar(45) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.028 sec) MariaDB [inventario]> describe venta; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id_venta | varchar(20) | NO | PRI | NULL | | | Fecha | varchar(45) | NO | | NULL | | | FK_cliente | varchar(20) | NO | MUL | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.028 sec) MariaDB [inventario]> show tables; +----------------------+ | Tables_in_inventario | +----------------------+ | alertas_inventario | | cliente | | detalles_devolucion | | detalles_pedido | | detalles_venta | | devoluciones | | inventario_historico | | pedidos_proveedores | | producto | | proveedores | | venta | +----------------------+ 11 rows in set (0.001 sec) MariaDB [inventario]> select * from alertas_inventario, -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 MariaDB [inventario]> select * from alertas_inventario; +-----------+-------------+----------------+------------+-------+ | id_alerta | FK_producto | Tipo_alerta | Fecha | Leida | +-----------+-------------+----------------+------------+-------+ | 1 | P001 | stock_minimo | 2024-10-25 | 0 | | 2 | P003 | stock_minimo | 2024-10-26 | 1 | | 3 | P007 | stock_minimo | 2024-10-28 | 0 | | 4 | P010 | stock_minimo | 2024-10-30 | 1 | | 5 | P002 | sin_movimiento | 2024-10-27 | 0 | | 6 | P005 | stock_sobrante | 2024-10-29 | 0 | | 7 | P008 | stock_minimo | 2024-11-01 | 0 | | 8 | P004 | sin_movimiento | 2024-10-31 | 1 | | 9 | P006 | stock_minimo | 2024-11-02 | 0 | | 10 | P009 | stock_sobrante | 2024-11-03 | 0 | +-----------+-------------+----------------+------------+-------+ 10 rows in set (0.010 sec) MariaDB [inventario]> select * from cliente; +------------+-----------------+------------+--------------+ | id_cliente | Nombre | Telefono | Ciudad | +------------+-----------------+------------+--------------+ | C001 | Juan Perez | 3111234567 | Medellin | | C002 | Ana Martinez | 3122345678 | Bogota | | C003 | Carlos Gomez | 3133456789 | Cali | | C004 | Laura Rodriguez | 3144567890 | Barranquilla | | C005 | Luis Fernandez | 3155678901 | Medellin | | C006 | Marta Suarez | 3166789012 | Medellin | | C007 | David Ramirez | 3177890123 | Bogota | | C008 | Gloria Ortiz | 3188901234 | Santa Marta | | C009 | Rosa Morales | 3199012345 | Cali | | C010 | Pedro Rios | 3100123456 | Medellin | +------------+-----------------+------------+--------------+ 10 rows in set (0.000 sec) MariaDB [inventario]> show tables; +----------------------+ | Tables_in_inventario | +----------------------+ | alertas_inventario | | cliente | | detalles_devolucion | | detalles_pedido | | detalles_venta | | devoluciones | | inventario_historico | | pedidos_proveedores | | producto | | proveedores | | venta | +----------------------+ 11 rows in set (0.001 sec) MariaDB [inventario]> select * from detalles_devolucion; +----------------+----------+---------------+-------------+ | id_detalle_dev | Cantidad | FK_devolucion | FK_producto | +----------------+----------+---------------+-------------+ | DD001 | 1 | DEV001 | P001 | | DD002 | 1 | DEV002 | P002 | | DD003 | 1 | DEV003 | P003 | | DD004 | 1 | DEV004 | P004 | | DD005 | 1 | DEV005 | P005 | | DD006 | 1 | DEV006 | P006 | | DD007 | 1 | DEV007 | P007 | | DD008 | 1 | DEV008 | P008 | | DD009 | 1 | DEV009 | P009 | | DD010 | 1 | DEV010 | P010 | +----------------+----------+---------------+-------------+ 10 rows in set (0.001 sec) MariaDB [inventario]> select * from detalles_pedido; +----------------+---------------------+-----------------+---------------+----------------+------------+ | id_detallePedi | Nombre | camtidad_pedido | Precio_compra | FK_pedidoProve | FK_produto | +----------------+---------------------+-----------------+---------------+----------------+------------+ | DP001 | Refrigerador Grande | 10 | 1400000 | PP001 | P001 | | DP002 | Lavadora Compacta | 15 | 1150000 | PP002 | P002 | | DP003 | Microondas Digital | 20 | 290000 | PP003 | P003 | | DP004 | Horno Electrico | 25 | 680000 | PP004 | P004 | | DP005 | Licuadora Potente | 30 | 145000 | PP005 | P005 | +----------------+---------------------+-----------------+---------------+----------------+------------+ 5 rows in set (0.000 sec) MariaDB [inventario]> select * from detalles_venta; +----------------+------------------+----------+-------------+ | id_detalleVent | Cantidad_vendida | FK_venta | FK_producto | +----------------+------------------+----------+-------------+ | DV001 | 2 | V001 | P001 | | DV002 | 1 | V002 | P002 | | DV003 | 3 | V003 | P003 | | DV004 | 2 | V004 | P004 | | DV005 | 1 | V005 | P005 | | DV006 | 2 | V006 | P006 | | DV007 | 1 | V007 | P007 | | DV008 | 4 | V008 | P008 | | DV009 | 2 | V009 | P009 | | DV010 | 3 | V010 | P010 | +----------------+------------------+----------+-------------+ 10 rows in set (0.000 sec) MariaDB [inventario]> show tables; +----------------------+ | Tables_in_inventario | +----------------------+ | alertas_inventario | | cliente | | detalles_devolucion | | detalles_pedido | | detalles_venta | | devoluciones | | inventario_historico | | pedidos_proveedores | | producto | | proveedores | | venta | +----------------------+ 11 rows in set (0.001 sec) MariaDB [inventario]> select * from devoluciones; +---------------+------------+----------------+-----------+----------+----------------------------------------+-----------+ | id_devolucion | Fecha | Motivo | Estado | FK_venta | Comentarios | Evidencia | +---------------+------------+----------------+-----------+----------+----------------------------------------+-----------+ | DEV001 | 2024-10-25 | defectuoso | procesada | V001 | Refrigerador con falla en el compresor | NULL | | DEV002 | 2024-10-26 | equivocacion | aprobada | V002 | Cliente pidió modelo equivocado | NULL | | DEV003 | 2024-10-27 | insatisfaccion | pendiente | V003 | Microondas no cumplió expectativas | NULL | | DEV004 | 2024-10-28 | otros | rechazada | V004 | Devolución sin justificación válida | NULL | | DEV005 | 2024-10-29 | defectuoso | procesada | V005 | Licuadora con fuga en la base | NULL | | DEV006 | 2024-10-30 | equivocacion | aprobada | V006 | Color no coincidió | NULL | | DEV007 | 2024-10-31 | defectuoso | pendiente | V007 | TV con pixeles muertos | NULL | | DEV008 | 2024-11-01 | insatisfaccion | aprobada | V008 | Ventilador muy ruidoso | NULL | | DEV009 | 2024-11-02 | otros | rechazada | V009 | Cafetera usada devuelta | NULL | | DEV010 | 2024-11-03 | defectuoso | procesada | V010 | Plancha no calienta | NULL | +---------------+------------+----------------+-----------+----------+----------------------------------------+-----------+ 10 rows in set (0.000 sec) MariaDB [inventario]> select * from inventario_historico; +-------------+---------------------+-------------+-----------------+-----------------+-------------------+ | id_registro | fecha | FK_producto | cambio_cantidad | tipo_movimiento | origen_movimiento | +-------------+---------------------+-------------+-----------------+-----------------+-------------------+ | 1 | 2024-10-25 10:00:00 | P001 | -2 | venta | V001 | | 2 | 2024-10-25 11:30:00 | P002 | -1 | venta | V002 | | 3 | 2024-10-26 09:15:00 | P003 | -3 | venta | V003 | | 4 | 2024-10-26 14:00:00 | P004 | 25 | compra | PP004 | | 5 | 2024-10-27 10:30:00 | P005 | 30 | compra | PP005 | | 6 | 2024-10-28 16:45:00 | P001 | 1 | devolucion | DEV001 | | 7 | 2024-10-29 12:00:00 | P002 | 1 | devolucion | DEV002 | | 8 | 2024-10-30 10:20:00 | P006 | -2 | venta | V006 | | 9 | 2024-10-31 11:10:00 | P007 | -1 | venta | V007 | | 10 | 2024-11-01 15:30:00 | P008 | 5 | ajuste | NULL | +-------------+---------------------+-------------+-----------------+-----------------+-------------------+ 10 rows in set (0.000 sec) MariaDB [inventario]> select * from producto; +-------------+----------------------+--------------+--------------+------------+--------------+--------------+--------+--------+ | id_producto | Nombre | Precio_costo | Precio_venta | Disponible | stock_minimo | stock_maximo | imagen | activo | +-------------+----------------------+--------------+--------------+------------+--------------+--------------+--------+--------+ | P001 | Refrigerador | 1500000 | 0 | 50 | 10 | NULL | NULL | 1 | | P0011 | Refrigerador Toyota | 2000000 | 0 | 0 | 10 | NULL | NULL | 1 | | P002 | Lavadora | 1200000 | 0 | 30 | 10 | NULL | NULL | 1 | | P003 | Microondas | 300000 | 0 | 100 | 10 | NULL | NULL | 1 | | P004 | Horno | 700000 | 0 | 60 | 10 | NULL | NULL | 1 | | P005 | Licuadora | 150000 | 0 | 200 | 10 | NULL | NULL | 1 | | P006 | Aspiradora | 500000 | 0 | 80 | 10 | NULL | NULL | 1 | | P007 | Televisor | 2000000 | 0 | 40 | 10 | NULL | NULL | 1 | | P008 | Ventilador | 80000 | 0 | 150 | 10 | NULL | NULL | 1 | | P009 | Cafetera | 120000 | 0 | 120 | 10 | NULL | NULL | 1 | | P010 | Plancha | 90000 | 0 | 70 | 10 | NULL | NULL | 1 | +-------------+----------------------+--------------+--------------+------------+--------------+--------------+--------+--------+ 11 rows in set (0.000 sec) MariaDB [inventario]> select * from proveedores; +--------------+----------------------+------------+--------------+ | id_proveedor | Nombre | Telefono | Ciudad | +--------------+----------------------+------------+--------------+ | PR001 | ElectroHouse | 3001234567 | Medellin | | PR002 | MegaElectro | 3012345678 | Bogota | | PR003 | HogarPlus | 3023456789 | Cali | | PR004 | TecnoElectro | 3034567890 | Barranquilla | | PR005 | ElectroCenter | 3045678901 | Cartagena | | PR006 | HomeElectronics | 3056789012 | Pereira | | PR007 | TotalElectro | 3067890123 | Bucaramanga | | PR008 | ElectroService | 3078901234 | Santa Marta | | PR009 | ElectroDistribuidora | 3089012345 | Manizales | | PR010 | ElectroTienda | 3090123456 | Ibagué | +--------------+----------------------+------------+--------------+ 10 rows in set (0.000 sec) MariaDB [inventario]> select * from venta; +----------+------------+------------+ | id_venta | Fecha | FK_cliente | +----------+------------+------------+ | V001 | 2024-10-15 | C001 | | V002 | 2024-10-16 | C002 | | V003 | 2024-10-17 | C003 | | V004 | 2024-10-18 | C004 | | V005 | 2024-10-19 | C005 | | V006 | 2024-10-20 | C006 | | V007 | 2024-10-21 | C007 | | V008 | 2024-10-22 | C008 | | V009 | 2024-10-23 | C009 | | V010 | 2024-10-24 | C010 | +----------+------------+------------+ 10 rows in set (0.000 sec) MariaDB [inventario]> select date_format(now(),'%h:%i:%s %p'); +----------------------------------+ | date_format(now(),'%h:%i:%s %p') | +----------------------------------+ | 01:36:55 PM | +----------------------------------+ 1 row in set (0.003 sec) MariaDB [inventario]> CREATE TABLE cantidad_devoluciones_por_fecha AS -> SELECT Fecha, COUNT(*) AS total_devoluciones -> FROM devoluciones -> GROUP BY Fecha; Query OK, 10 rows affected (0.047 sec) Records: 10 Duplicates: 0 Warnings: 0 MariaDB [inventario]> MariaDB [inventario]> select * from cantidad_devoluciones_por_fecha; +------------+--------------------+ | Fecha | total_devoluciones | +------------+--------------------+ | 2024-10-25 | 1 | | 2024-10-26 | 1 | | 2024-10-27 | 1 | | 2024-10-28 | 1 | | 2024-10-29 | 1 | | 2024-10-30 | 1 | | 2024-10-31 | 1 | | 2024-11-01 | 1 | | 2024-11-02 | 1 | | 2024-11-03 | 1 | +------------+--------------------+ 10 rows in set (0.000 sec) MariaDB [inventario]> select TABLE cantidad_devoluciones_por_fecha AS SELECT Fecha, COUNT(*) AS total_devoluciones FROM devoluciones GROUP BY Fecha; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TABLE cantidad_devoluciones_por_fecha AS SELECT Fecha, COUNT(*) AS total_devo...' at line 1 MariaDB [inventario]> select * from devoluciones; +---------------+------------+----------------+-----------+----------+----------------------------------------+-----------+ | id_devolucion | Fecha | Motivo | Estado | FK_venta | Comentarios | Evidencia | +---------------+------------+----------------+-----------+----------+----------------------------------------+-----------+ | DEV001 | 2024-10-25 | defectuoso | procesada | V001 | Refrigerador con falla en el compresor | NULL | | DEV002 | 2024-10-26 | equivocacion | aprobada | V002 | Cliente pidió modelo equivocado | NULL | | DEV003 | 2024-10-27 | insatisfaccion | pendiente | V003 | Microondas no cumplió expectativas | NULL | | DEV004 | 2024-10-28 | otros | rechazada | V004 | Devolución sin justificación válida | NULL | | DEV005 | 2024-10-29 | defectuoso | procesada | V005 | Licuadora con fuga en la base | NULL | | DEV006 | 2024-10-30 | equivocacion | aprobada | V006 | Color no coincidió | NULL | | DEV007 | 2024-10-31 | defectuoso | pendiente | V007 | TV con pixeles muertos | NULL | | DEV008 | 2024-11-01 | insatisfaccion | aprobada | V008 | Ventilador muy ruidoso | NULL | | DEV009 | 2024-11-02 | otros | rechazada | V009 | Cafetera usada devuelta | NULL | | DEV010 | 2024-11-03 | defectuoso | procesada | V010 | Plancha no calienta | NULL | +---------------+------------+----------------+-----------+----------+----------------------------------------+-----------+ 10 rows in set (0.000 sec) MariaDB [inventario]> show tables; +---------------------------------+ | Tables_in_inventario | +---------------------------------+ | alertas_inventario | | cantidad_devoluciones_por_fecha | | cliente | | detalles_devolucion | | detalles_pedido | | detalles_venta | | devoluciones | | inventario_historico | | pedidos_proveedores | | producto | | proveedores | | venta | +---------------------------------+ 12 rows in set (0.001 sec) MariaDB [inventario]> CREATE TABLE resumen_alertas_octubre AS -> SELECT Tipo_alerta, COUNT(*) AS total_alertas -> FROM alertas_inventario -> WHERE Fecha BETWEEN '2024-10-25' AND '2024-10-31' -> GROUP BY Tipo_alerta; Query OK, 3 rows affected (0.033 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [inventario]> select * from resumen_alertas_octubre; +----------------+---------------+ | Tipo_alerta | total_alertas | +----------------+---------------+ | stock_minimo | 4 | | stock_sobrante | 1 | | sin_movimiento | 2 | +----------------+---------------+ 3 rows in set (0.000 sec) MariaDB [inventario]> select * from alertas_inventario; +-----------+-------------+----------------+------------+-------+ | id_alerta | FK_producto | Tipo_alerta | Fecha | Leida | +-----------+-------------+----------------+------------+-------+ | 1 | P001 | stock_minimo | 2024-10-25 | 0 | | 2 | P003 | stock_minimo | 2024-10-26 | 1 | | 3 | P007 | stock_minimo | 2024-10-28 | 0 | | 4 | P010 | stock_minimo | 2024-10-30 | 1 | | 5 | P002 | sin_movimiento | 2024-10-27 | 0 | | 6 | P005 | stock_sobrante | 2024-10-29 | 0 | | 7 | P008 | stock_minimo | 2024-11-01 | 0 | | 8 | P004 | sin_movimiento | 2024-10-31 | 1 | | 9 | P006 | stock_minimo | 2024-11-02 | 0 | | 10 | P009 | stock_sobrante | 2024-11-03 | 0 | +-----------+-------------+----------------+------------+-------+ 10 rows in set (0.000 sec) MariaDB [inventario]> show tables; +---------------------------------+ | Tables_in_inventario | +---------------------------------+ | alertas_inventario | | cantidad_devoluciones_por_fecha | | cliente | | detalles_devolucion | | detalles_pedido | | detalles_venta | | devoluciones | | inventario_historico | | pedidos_proveedores | | producto | | proveedores | | resumen_alertas_octubre | | venta | +---------------------------------+ 13 rows in set (0.001 sec) MariaDB [inventario]> CREATE TABLE resumen_alertas ( -> Fecha DATE, -> Tipo_alerta ENUM('stock_minimo','stock_sobrante','sin_movimiento'), -> total_alertas INT DEFAULT 1, -> PRIMARY KEY (Fecha, Tipo_alerta) -> ); Query OK, 0 rows affected (0.032 sec) MariaDB [inventario]> DELIMITER // MariaDB [inventario]> MariaDB [inventario]> CREATE TRIGGER trg_actualizar_resumen_alertas -> AFTER INSERT ON alertas_inventario -> FOR EACH ROW -> BEGIN -> DECLARE existe INT; -> -> -- Verificar si ya existe un registro para esa fecha y tipo -> SELECT COUNT(*) INTO existe -> FROM resumen_alertas -> WHERE Fecha = NEW.Fecha AND Tipo_alerta = NEW.Tipo_alerta; -> -> IF existe > 0 THEN -> -- Si ya existe, incrementar el contador -> UPDATE resumen_alertas -> SET total_alertas = total_alertas + 1 -> WHERE Fecha = NEW.Fecha AND Tipo_alerta = NEW.Tipo_alerta; -> ELSE -> -- Si no existe, insertar nuevo registro -> INSERT INTO resumen_alertas (Fecha, Tipo_alerta, total_alertas) -> VALUES (NEW.Fecha, NEW.Tipo_alerta, 1); -> END IF; -> END // Query OK, 0 rows affected (0.011 sec) MariaDB [inventario]> MariaDB [inventario]> DELIMITER ; MariaDB [inventario]> SELECT * FROM resumen_alertas; Empty set (0.002 sec) MariaDB [inventario]> INSERT INTO alertas_inventario (FK_producto, Tipo_alerta, Fecha, Leida) -> VALUES ('P011', 'stock_minimo', '2024-10-27', 0); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`inventario`.`alertas_inventario`, CONSTRAINT `alertas_inventario_ibfk_1` FOREIGN KEY (`FK_producto`) REFERENCES `producto` (`id_producto`) ON DELETE CASCADE) MariaDB [inventario]> INSERT INTO alertas_inventario (FK_producto, Tipo_alerta, Fecha, Leida) -> VALUES ('P011', 'stock_minimo', '2024-10-27', 0); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`inventario`.`alertas_inventario`, CONSTRAINT `alertas_inventario_ibfk_1` FOREIGN KEY (`FK_producto`) REFERENCES `producto` (`id_producto`) ON DELETE CASCADE) MariaDB [inventario]> INSERT INTO alertas_inventario (FK_producto, Tipo_alerta, Fecha, Leida) -> VALUES ('P011', 'stock_minimo', '2024-10-27', 0); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`inventario`.`alertas_inventario`, CONSTRAINT `alertas_inventario_ibfk_1` FOREIGN KEY (`FK_producto`) REFERENCES `producto` (`id_producto`) ON DELETE CASCADE) MariaDB [inventario]> INSERT INTO alertas_inventario (FK_producto, Tipo_alerta, Fecha, Leida) -> VALUES ('P001', 'stock_minimo', '2024-10-27', 0); Query OK, 1 row affected (0.008 sec) MariaDB [inventario]> SELECT * FROM resumen_alertas; +------------+--------------+---------------+ | Fecha | Tipo_alerta | total_alertas | +------------+--------------+---------------+ | 2024-10-27 | stock_minimo | 1 | +------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [inventario]> INSERT INTO producto (id_producto, Nombre, Precio_costo, Precio_venta, Disponible, stock_minimo, stock_maximo, imagen, activo) -> VALUES ('P011', 'Nuevo Producto', 100000, 150000, 10, 5, NULL, NULL, 1); Query OK, 1 row affected (0.006 sec) MariaDB [inventario]> SELECT * FROM resumen_alertas; +------------+--------------+---------------+ | Fecha | Tipo_alerta | total_alertas | +------------+--------------+---------------+ | 2024-10-27 | stock_minimo | 1 | +------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [inventario]> INSERT INTO alertas_inventario (FK_producto, Tipo_alerta, Fecha, Leida) -> VALUES ('P011', 'stock_minimo', '2024-10-27', 0); Query OK, 1 row affected (0.021 sec) MariaDB [inventario]> SELECT * FROM resumen_alertas; +------------+--------------+---------------+ | Fecha | Tipo_alerta | total_alertas | +------------+--------------+---------------+ | 2024-10-27 | stock_minimo | 2 | +------------+--------------+---------------+ 1 row in set (0.000 sec) MariaDB [inventario]> SELECT -> v.id_venta, -> v.Fecha, -> c.Nombre AS Cliente, -> p.Nombre AS Producto, -> dv.Cantidad_vendida -> FROM venta v -> JOIN cliente c ON v.FK_cliente = c.id_cliente -> JOIN detalles_venta dv ON v.id_venta = dv.FK_venta -> JOIN producto p ON dv.FK_producto = p.id_producto -> ORDER BY v.Fecha; +----------+------------+-----------------+--------------+------------------+ | id_venta | Fecha | Cliente | Producto | Cantidad_vendida | +----------+------------+-----------------+--------------+------------------+ | V001 | 2024-10-15 | Juan Perez | Refrigerador | 2 | | V002 | 2024-10-16 | Ana Martinez | Lavadora | 1 | | V003 | 2024-10-17 | Carlos Gomez | Microondas | 3 | | V004 | 2024-10-18 | Laura Rodriguez | Horno | 2 | | V005 | 2024-10-19 | Luis Fernandez | Licuadora | 1 | | V006 | 2024-10-20 | Marta Suarez | Aspiradora | 2 | | V007 | 2024-10-21 | David Ramirez | Televisor | 1 | | V008 | 2024-10-22 | Gloria Ortiz | Ventilador | 4 | | V009 | 2024-10-23 | Rosa Morales | Cafetera | 2 | | V010 | 2024-10-24 | Pedro Rios | Plancha | 3 | +----------+------------+-----------------+--------------+------------------+ 10 rows in set (0.003 sec) MariaDB [inventario]> exit Bye C:\xampp\mysql\bin>mysqldump -B -uroot -p --routines inventario>c:/xampp/inventario21.sql Enter password: C:\xampp\mysql\bin>mysql -u root -p -h localhost Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 10.4.32-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.000 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | basedatosii | | basedatosiii | | collar | | createinsert | | cursos | | green | | iis | | information_schema | | inventario | | mysql | | parcial1 | | parcial2 | | performance_schema | | phpmyadmin | | proyecto | | prueba | | test | | turismo | | veterinaria | | yeison_roa | +--------------------+ 20 rows in set (0.001 sec) MariaDB [(none)]> use mysql; Database changed MariaDB [mysql]> select user from user; +------+ | User | +------+ | root | | root | | pma | | root | +------+ 4 rows in set (0.003 sec) MariaDB [mysql]> CREATE USER 'usuario_sin_permisos'@'localhost' IDENTIFIED BY '123'; Query OK, 0 rows affected (0.004 sec) MariaDB [mysql]> SHOW GRANTS FOR 'usuario_sin_permisos'@'localhost'; +-----------------------------------------------------------------------------------------------------------------------------+ | Grants for usuario_sin_permisos@localhost | +-----------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `usuario_sin_permisos`@`localhost` IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | +-----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [mysql]> CREATE USER 'usuario_limitado'@'localhost' IDENTIFIED BY '123'; Query OK, 0 rows affected (0.002 sec) MariaDB [mysql]> GRANT SELECT, INSERT ON mi_basedatos.mi_tabla TO 'usuario_limitado'@'localhost'; ERROR 1146 (42S02): Table 'mi_basedatos.mi_tabla' doesn't exist MariaDB [mysql]> GRANT SELECT, INSERT ON inventario.cliente TO 'usuario_limitado'@'localhost'; Query OK, 0 rows affected (0.002 sec) MariaDB [mysql]> SHOW GRANTS FOR 'usuario_limitado'@'localhost'; +-------------------------------------------------------------------------------------------------------------------------+ | Grants for usuario_limitado@localhost | +-------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `usuario_limitado`@`localhost` IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | | GRANT SELECT, INSERT ON `inventario`.`cliente` TO `usuario_limitado`@`localhost` | +-------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) MariaDB [mysql]> CREATE USER 'usuario_admin'@'localhost' IDENTIFIED BY 'tu_contraseña'; Query OK, 0 rows affected (0.002 sec) MariaDB [mysql]> MariaDB [mysql]> GRANT ALL PRIVILEGES ON mi_basedatos.* TO 'usuario_admin'@'localhost'; Query OK, 0 rows affected (0.001 sec) MariaDB [mysql]> MariaDB [mysql]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.001 sec) MariaDB [mysql]> SHOW GRANTS FOR 'usuario_admin'@'localhost'; +----------------------------------------------------------------------------------------------------------------------+ | Grants for usuario_admin@localhost | +----------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `usuario_admin`@`localhost` IDENTIFIED BY PASSWORD '*69C63797DDCA61B801AB9A50D4E34E3B318EF9A9' | | GRANT ALL PRIVILEGES ON `mi_basedatos`.* TO `usuario_admin`@`localhost` | +----------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) MariaDB [mysql]> Ctrl-C -- exit! Bye C:\xampp\mysql\bin>