MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | basedatosii | | basedatosiii | | cadena | | createinsert | | cumple | | fecha | | fechas2 | | green | | greens | | hospital | | information_schema | | mysql | | papeleria | | parcial1 | | performance_schema | | phpmyadmin | | proyecto | | prueba | | pruebadetrigger | | sebas | | subconsultas | | test | | tipodatos | | veterinaria | +--------------------+ 24 rows in set (0.003 sec) MariaDB [(none)]> use papeleria; Database changed MariaDB [papeleria]> MariaDB [papeleria]> show tables; +------------------------+ | Tables_in_papeleria | +------------------------+ | categorias | | consumidor | | detalles_pedido | | detalles_venta | | distribuidor | | historial_inventario | | metodos_pago | | pedidos_distribuidores | | producto | | producto_promocion | | promociones | | ventas | +------------------------+ 12 rows in set (0.001 sec) MariaDB [papeleria]> CREATE TABLE cantidad_ventas_por_fecha AS -> SELECT Fecha, COUNT(*) AS total_ventas -> FROM ventas -> GROUP BY Fecha; Query OK, 10 rows affected (0.018 sec) Records: 10 Duplicates: 0 Warnings: 0 MariaDB [papeleria]> select * from cantidad_ventas_por_fecha; +------------+--------------+ | Fecha | total_ventas | +------------+--------------+ | 2023-01-05 | 1 | | 2023-01-12 | 1 | | 2023-01-18 | 1 | | 2023-02-02 | 1 | | 2023-02-10 | 1 | | 2023-02-15 | 1 | | 2023-03-01 | 1 | | 2023-03-08 | 1 | | 2023-03-15 | 1 | | 2023-03-22 | 1 | +------------+--------------+ 10 rows in set (0.000 sec) MariaDB [papeleria]> CREATE TABLE resumen_ventas_enero_febrero AS -> SELECT c.Ciudad, COUNT(*) AS total_ventas -> FROM ventas v -> INNER JOIN consumidor c ON v.FK_consumidor = c.ID_consumidor -> WHERE v.Fecha BETWEEN '2023-01-05' AND '2023-02-15' -> GROUP BY c.Ciudad; Query OK, 6 rows affected (0.012 sec) Records: 6 Duplicates: 0 Warnings: 0 MariaDB [papeleria]> select * from resumen_ventas_enero_febrero; +--------------+--------------+ | Ciudad | total_ventas | +--------------+--------------+ | Barranquilla | 1 | | Bogot  | 1 | | Bucaramanga | 1 | | Cali | 1 | | Cartagena | 1 | | Medell¡n | 1 | +--------------+--------------+ 6 rows in set (0.000 sec) MariaDB [papeleria]> select * from ventas; +----------+------------+---------------+ | ID_venta | Fecha | FK_consumidor | +----------+------------+---------------+ | VENT001 | 2023-01-05 | CONS001 | | VENT002 | 2023-01-12 | CONS002 | | VENT003 | 2023-01-18 | CONS003 | | VENT004 | 2023-02-02 | CONS004 | | VENT005 | 2023-02-10 | CONS005 | | VENT006 | 2023-02-15 | CONS006 | | VENT007 | 2023-03-01 | CONS007 | | VENT008 | 2023-03-08 | CONS008 | | VENT009 | 2023-03-15 | CONS009 | | VENT010 | 2023-03-22 | CONS010 | +----------+------------+---------------+ 10 rows in set (0.000 sec) MariaDB [papeleria]> DELIMITER // MariaDB [papeleria]> CREATE TRIGGER trg_actualizar_resumen_ventas -> AFTER INSERT ON ventas -> FOR EACH ROW -> BEGIN -> DECLARE existe INT; -> DECLARE ciudad_consumidor VARCHAR(45); -> -> -- Obtener la ciudad del consumidor -> SELECT Ciudad INTO ciudad_consumidor -> FROM consumidor -> WHERE ID_consumidor = NEW.FK_consumidor; -> -> -- Verificar si ya existe un registro para esa fecha y ciudad -> SELECT COUNT(*) INTO existe -> FROM resumen_ventas_diarias -> WHERE Fecha = NEW.Fecha AND Ciudad = ciudad_consumidor; -> -> IF existe > 0 THEN -> -- Si existe, incrementar el contador -> UPDATE resumen_ventas_diarias -> SET total_ventas = total_ventas + 1 -> WHERE Fecha = NEW.Fecha AND Ciudad = ciudad_consumidor; -> ELSE -> -- Si no existe, insertar nuevo registro -> INSERT INTO resumen_ventas_diarias (Fecha, Ciudad, total_ventas) -> VALUES (NEW.Fecha, ciudad_consumidor, 1); -> END IF; -> END // Query OK, 0 rows affected (0.009 sec) MariaDB [papeleria]> DELIMITER ; MariaDB [papeleria]> INSERT INTO ventas VALUES ('VENT011', '2023-01-05', 'CONS001'); ERROR 1146 (42S02): Table 'papeleria.resumen_ventas_diarias' doesn't exist MariaDB [papeleria]> SELECT * FROM ventas; +----------+------------+---------------+ | ID_venta | Fecha | FK_consumidor | +----------+------------+---------------+ | VENT001 | 2023-01-05 | CONS001 | | VENT002 | 2023-01-12 | CONS002 | | VENT003 | 2023-01-18 | CONS003 | | VENT004 | 2023-02-02 | CONS004 | | VENT005 | 2023-02-10 | CONS005 | | VENT006 | 2023-02-15 | CONS006 | | VENT007 | 2023-03-01 | CONS007 | | VENT008 | 2023-03-08 | CONS008 | | VENT009 | 2023-03-15 | CONS009 | | VENT010 | 2023-03-22 | CONS010 | +----------+------------+---------------+ 10 rows in set (0.000 sec) MariaDB [papeleria]> SELECT * FROM resumen_ventas_diarias; ERROR 1146 (42S02): Table 'papeleria.resumen_ventas_diarias' doesn't exist MariaDB [papeleria]> INSERT INTO ventas VALUES ('VENT011', '2023-01-05', 'CONS001'); ERROR 1146 (42S02): Table 'papeleria.resumen_ventas_diarias' doesn't exist MariaDB [papeleria]> CREATE TABLE resumen_ventas_diarias ( -> Fecha VARCHAR(45) NOT NULL, -> Ciudad VARCHAR(45) NOT NULL, -> total_ventas INT NOT NULL DEFAULT 1, -> PRIMARY KEY (Fecha, Ciudad) -> ); Query OK, 0 rows affected (0.009 sec) MariaDB [papeleria]> DELIMITER // MariaDB [papeleria]> CREATE TRIGGER trg_actualizar_resumen_ventas -> AFTER INSERT ON ventas -> FOR EACH ROW -> BEGIN -> DECLARE existe INT; -> DECLARE ciudad_consumidor VARCHAR(45); -> -> -- Obtener la ciudad del consumidor -> SELECT Ciudad INTO ciudad_consumidor -> FROM consumidor -> WHERE ID_consumidor = NEW.FK_consumidor; -> -> -- Verificar si ya existe un registro para esa fecha y ciudad -> SELECT COUNT(*) INTO existe -> FROM resumen_ventas_diarias -> WHERE Fecha = NEW.Fecha AND Ciudad = ciudad_consumidor; -> -> IF existe > 0 THEN -> -- Si existe, incrementar el contador -> UPDATE resumen_ventas_diarias -> SET total_ventas = total_ventas + 1 -> WHERE Fecha = NEW.Fecha AND Ciudad = ciudad_consumidor; -> ELSE -> -- Si no existe, insertar nuevo registro -> INSERT INTO resumen_ventas_diarias (Fecha, Ciudad, total_ventas) -> VALUES (NEW.Fecha, ciudad_consumidor, 1); -> END IF; -> END // ERROR 1359 (HY000): Trigger 'papeleria.trg_actualizar_resumen_ventas' already exists MariaDB [papeleria]> DELIMITER ; MariaDB [papeleria]> SELECT * FROM resumen_ventas_diarias; Empty set (0.001 sec) MariaDB [papeleria]> INSERT INTO ventas VALUES ('VENT011', '2023-01-05', 'CONS001'); Query OK, 1 row affected (0.004 sec) MariaDB [papeleria]> SELECT * FROM resumen_ventas_diarias; +------------+--------+--------------+ | Fecha | Ciudad | total_ventas | +------------+--------+--------------+ | 2023-01-05 | Bogot  | 1 | +------------+--------+--------------+ 1 row in set (0.000 sec) MariaDB [papeleria]> INSERT INTO ventas VALUES ('VENT012', '2023-01-05', 'CONS001'); Query OK, 1 row affected (0.003 sec) MariaDB [papeleria]> SELECT * FROM resumen_ventas_diarias; +------------+--------+--------------+ | Fecha | Ciudad | total_ventas | +------------+--------+--------------+ | 2023-01-05 | Bogot  | 2 | +------------+--------+--------------+ 1 row in set (0.000 sec) MariaDB [papeleria]> SELECT -> v.ID_venta, -> v.Fecha, -> c.Nombre AS Cliente, -> p.Nombre AS Producto, -> dv.Cantidad_vendida, -> p.Precio_venta, -> (dv.Cantidad_vendida * p.Precio_venta) AS Total_Linea -> FROM ventas v -> JOIN consumidor c ON v.FK_consumidor = c.ID_consumidor -> 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 | Precio_venta | Total_Linea | +----------+------------+------------------+-----------------------+------------------+--------------+-------------+ | VENT001 | 2023-01-05 | Mar¡a Gonz lez | Cuaderno cuadriculado | 2 | 3000 | 6000 | | VENT001 | 2023-01-05 | Mar¡a Gonz lez | L piz grafito | 5 | 500 | 2500 | | VENT002 | 2023-01-12 | Carlos Rodr¡guez | Borrador blanco | 1 | 800 | 800 | | VENT003 | 2023-01-18 | Ana Mart¡nez | Resma papel carta | 3 | 15000 | 45000 | | VENT004 | 2023-02-02 | Luis P‚rez | Tajal piz met lico | 2 | 2500 | 5000 | | VENT005 | 2023-02-10 | Sof¡a L¢pez | Marcador permanente | 4 | 3000 | 12000 | | VENT006 | 2023-02-15 | Jorge Ram¡rez | Carpeta oficio | 1 | 5000 | 5000 | | VENT007 | 2023-03-01 | Diana Garc¡a | Pegante en barra | 3 | 3500 | 10500 | | VENT008 | 2023-03-08 | Pedro S nchez | Regla 30 cm | 2 | 1500 | 3000 | | VENT009 | 2023-03-15 | Laura Fern ndez | Corrector l¡quido | 1 | 4500 | 4500 | +----------+------------+------------------+-----------------------+------------------+--------------+-------------+ 10 rows in set (0.005 sec) MariaDB [papeleria]> CREATE USER 'empleado_basico'@'localhost' IDENTIFIED BY 'papeleria123'; Query OK, 0 rows affected (0.004 sec) MariaDB [papeleria]> SHOW GRANTS FOR 'empleado_basico'@'localhost'; +------------------------------------------------------------------------------------------------------------------------+ | Grants for empleado_basico@localhost | +------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `empleado_basico`@`localhost` IDENTIFIED BY PASSWORD '*CC44E6E6C8D72A59DCDFF734DACDC6EA39A198DF' | +------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [papeleria]> GRANT [tipo_permiso] ON [base_datos].[tabla] TO 'usuario'@'host'; 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 '[tipo_permiso] ON [base_datos].[tabla] TO 'usuario'@'host'' at line 1 MariaDB [papeleria]> GRANT SELECT, INSERT ON papeleria.consumidor TO 'usuario_limitado'@'localhost'; Query OK, 0 rows affected (0.003 sec) MariaDB [papeleria]> GRANT SELECT ON papeleria.producto TO 'usuario'@'localhost'; Query OK, 0 rows affected (0.003 sec) MariaDB [papeleria]> GRANT SELECT ON papeleria.ventas TO 'usuario'@'localhost'; Query OK, 0 rows affected (0.003 sec) MariaDB [papeleria]> SHOW GRANTS FOR 'empleado_basico'@'localhost'; +------------------------------------------------------------------------------------------------------------------------+ | Grants for empleado_basico@localhost | +------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `empleado_basico`@`localhost` IDENTIFIED BY PASSWORD '*CC44E6E6C8D72A59DCDFF734DACDC6EA39A198DF' | +------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [papeleria]> exit