Examen

CREATE DATABASE TRANSACCIONAL;
USE TRANSACCIONAL;

CREATE TABLE TIPOUSUARIO (
    TIPOUSUARIO INT(2) NOT NULL,
    DESCRIPCION VARCHAR(50),
    ESTADO INT(2),
    PRIMARY KEY (TIPOUSUARIO)
);

CREATE TABLE USUARIOS (
    IDUSUARIO INT(18) NOT NULL,
    NOMBRE VARCHAR(50),
    APELLIDOS VARCHAR(50),
    FECHA DATE,
    ESTADO INT(1),
    TIPOUSUARIO INT(2),
    PRIMARY KEY (IDUSUARIO)
);

INSERT INTO TIPOUSUARIO (TIPOUSUARIO, DESCRIPCION, ESTADO) VALUES (1, 'VENDEDOR', 1);
INSERT INTO TIPOUSUARIO (TIPOUSUARIO, DESCRIPCION, ESTADO) VALUES (2, 'CAJERO', 1);
INSERT INTO TIPOUSUARIO (TIPOUSUARIO, DESCRIPCION, ESTADO) VALUES (3, 'ADMINISTRADOR', 1);
INSERT INTO TIPOUSUARIO (TIPOUSUARIO, DESCRIPCION, ESTADO) VALUES (4, 'CONSULTA', 1);
INSERT INTO TIPOUSUARIO (TIPOUSUARIO, DESCRIPCION, ESTADO) VALUES (5, 'DIGITADOR', 1);

INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1001, 'JUAN', 'PEREZ', '2021-08-11', 1, 1);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1002, 'CARLOS', 'HERNANDEZ', '2021-08-01', 1, 1);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1003, 'ELMER', 'HOMERO', '2021-05-10', 1, 1);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1004, 'ESTEBAN', 'QUITO', '2021-04-01', 1, 2);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1005, 'ZOILA', 'QUIJADA', '2021-06-04', 1, 2);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1006, 'MARCELLO', 'VEGA', '2021-04-04', 1, 2);
/*Continuacion*/
-- Inserción de datos en la tabla USUARIOS
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1007, 'JESSICA', 'JIMENEZ', '2021-02-14', 1, 3);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1008, 'MARGARITA', 'CUERVO', '2021-01-15', 1, 3);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1009, 'RENE', 'ZELAYA', '2021-01-14', 1, 3);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1010, 'CARLOS', 'CHAVEZ', '2021-01-10', 2, 4);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1011, 'NILS', 'PENA', '2021-03-30', 1, 4);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1012, 'LUIS', 'AGUILAR', '2021-07-18', 2, 4);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1013, 'CARLOS', 'ARGUETA', '2021-05-01', 2, 5);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1014, 'JOSE', 'CAMPOS', '2021-04-04', 2, 5);
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO) VALUES (1015, 'KARLA', 'VALLE', '2021-02-25', 2, 5);


-- Establecimiento de la restricción de clave foránea
ALTER TABLE USUARIOS ADD CONSTRAINT FK_USUARIOS_TIPOUSUARIO FOREIGN KEY (TIPOUSUARIO) REFERENCES TIPOUSUARIO(TIPOUSUARIO);

-- Primera Consulta
SELECT 
    u.IDUSUARIO, 
    CONCAT(u.NOMBRE, ' ', u.APELLIDOS) AS NOMBREUSUARIO, u.FECHA, 
    CONCAT(u.TIPOUSUARIO,' ',t.DESCRIPCION) AS TIPOUSUARIO,
    u.ESTADO
FROM 
    USUARIOS u
JOIN 
    TIPOUSUARIO t 
ON 
    u.TIPOUSUARIO = t.TIPOUSUARIO;

-- Segunda consulta
SELECT u.IDUSUARIO,CONCAT(u.NOMBRE, ' ', u.APELLIDOS) AS NOMBREUSUARIO, u.FECHA,
CONCAT(u.TIPOUSUARIO,' ' , t.DESCRIPCION) AS TIPOUSUARIO, u.ESTADO
FROM USUARIOS u
JOIN TIPOUSUARIO t
ON u.TIPOUSUARIO = t.TIPOUSUARIO
WHERE u.ESTADO = 2;

-- Tercera consulta
SELECT u.IDUSUARIO,CONCAT(u.NOMBRE, ' ', u.APELLIDOS) AS NOMBREUSUARIO, u.FECHA,
CONCAT(u.TIPOUSUARIO,' ' , t.DESCRIPCION) AS TIPOUSUARIO,
CASE
	WHEN u.ESTADO  = 1 THEN 'ACTIVO'
    WHEN u.ESTADO =  2 THEN 'INACTIVO'
END AS ESTADO
FROM USUARIOS u
JOIN TIPOUSUARIO t
ON u.TIPOUSUARIO = t.TIPOUSUARIO;

-- Cuarta consulta
SELECT 
    u.IDUSUARIO,
    CONCAT(u.NOMBRE, ' ', u.APELLIDOS) AS NOMBREUSUARIO,
    u.FECHA,
    CONCAT(u.TIPOUSUARIO, ' ', t.DESCRIPCION) AS TIPOUSUARIO,
    CASE
        WHEN u.ESTADO = 1 THEN 'ACTIVO'
        WHEN u.ESTADO = 2 THEN 'INACTIVO'
    END AS ESTADO
FROM 
    USUARIOS u
JOIN 
    TIPOUSUARIO t
ON 
    u.TIPOUSUARIO = t.TIPOUSUARIO
WHERE 
    YEAR(u.FECHA) = 2021 AND MONTH(u.FECHA) BETWEEN 1 AND 5;

-- Quinto ejercicio
SELECT t.DESCRIPCION AS TIPOUSUARIO, COUNT(t.DESCRIPCION) AS  CANTIDAD
FROM USUARIOS u
JOIN TIPOUSUARIO t
ON u.TIPOUSUARIO = t.TIPOUSUARIO
GROUP BY t.DESCRIPCION
ORDER BY t.DESCRIPCION ASC;

-- Sexto ejercicio
-- Insertar usuario tipo VENDEDOR
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO)
VALUES (1016, 'CAROLINA', 'LOPEZ', '2021-10-30', 1, 1);

-- Insertar usuario tipo CAJERO
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO)
VALUES (1017, 'ARIEL', 'GALDAMEZ', '2021-10-30', 1, 2);

-- Insertar usuario tipo ADMINISTRADOR
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO)
VALUES (1018, 'XIOMARA', 'CARRANZA', '2021-10-30', 1, 3);

-- Insertar usuario tipo CONSULTA
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO)
VALUES (1019, 'ERICK', 'VARELA', '2021-10-30', 1, 4);

-- Insertar usuario tipo DIGITADOR
INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO)
VALUES (1020, 'MIGUEL', 'LINARES', '2021-10-30', 1, 5);

SELECT * FROM USUARIOS;

-- Septimo ejercicio
-- Insertar nuevos tipos de usuarios
INSERT INTO TIPOUSUARIO (TIPOUSUARIO, DESCRIPCION, ESTADO)
VALUES (6, 'JEFE', 1);

INSERT INTO TIPOUSUARIO (TIPOUSUARIO, DESCRIPCION, ESTADO)
VALUES (7, 'BODEGA', 1);

SELECT * FROM USUARIOS;

-- Octavo ejercicio
SELECT DISTINCT ESTADO AS ID,
CASE
	WHEN u.ESTADO  = 1 THEN 'ACTIVO'
    WHEN u.ESTADO =  2 THEN 'INACTIVO'
    WHEN u.ESTADO = 3  THEN 'VACACION'
    WHEN u.ESTADO = 4  THEN 'RETIRO'
    WHEN u.ESTADO = 5  THEN 'MATERNIDAD'
END AS ESTADO
FROM USUARIOS u;

INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO)
VALUES (1021, 'Usuario-21', 'Apellido-21', '2021-10-30', 3, 1);

INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO)
VALUES (1022, 'Usuario-22', 'Apellido-22', '2021-10-30', 4, 1);

INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO)
VALUES (1023, 'Usuario-23', 'Apellido-23', '2021-10-30', 5, 1);

INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO)
VALUES (1024, 'Usuario-24', 'Apellido-24', '2021-10-30', 5, 6);

INSERT INTO USUARIOS (IDUSUARIO, NOMBRE, APELLIDOS, FECHA, ESTADO, TIPOUSUARIO)
VALUES (1025, 'Usuario-24', 'Apellido-24', '2021-10-30', 5, 7);

----- Noveno ejercicio
ALTER TABLE USUARIOS
ADD CORREO VARCHAR(255),
ADD TELEFONO VARCHAR(20);

SELECT * FROM USUARIOS;

-- Actualizar el CORREO y TELEFONO para el usuario con IDUSUARIO 1023
UPDATE USUARIOS
SET CORREO = CONCAT(LOWER(REPLACE(NOMBRE, ' ', '_')), '_', LOWER(REPLACE(APELLIDOS, ' ', '_')), '@gmail.com'),
    TELEFONO = CONCAT('(+503) ', SUBSTR(FLOOR(RAND() * 100000000), 1, 4), '-', SUBSTR(FLOOR(RAND() * 10000), 1, 4))
-- En esta apartado solo el ID cambio y hace toda la concatenacion de nombre y apellido para generar correo
WHERE IDUSUARIO = 1025;

-- Decimo ejercicio
DROP VIEW nombre_vista;

CREATE VIEW nombre_vista AS
SELECT 
    u.IDUSUARIO, 
    CONCAT(u.NOMBRE, ' ', u.APELLIDOS) AS NOMBREUSUARIO, u.FECHA, 
    CONCAT(u.TIPOUSUARIO,' ',t.DESCRIPCION) AS TIPOUSUARIO, u.CORREO, u.TELEFONO,
CASE
        WHEN u.ESTADO = 1 THEN 'ACTIVO'
        WHEN u.ESTADO = 2 THEN 'INACTIVO'
        WHEN u.ESTADO = 3  THEN 'VACACION'
		WHEN u.ESTADO = 4  THEN 'RETIRO'
		WHEN u.ESTADO = 5  THEN 'MATERNIDAD'
    END AS ESTADO
FROM 
    USUARIOS u
JOIN 
    TIPOUSUARIO t 
ON 
    u.TIPOUSUARIO = t.TIPOUSUARIO;

select * from nombre_vista;