-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema callcenter
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema callcenter
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `callcenter` ;
USE `callcenter` ;

-- -----------------------------------------------------
-- Table `callcenter`.`cliente`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `callcenter`.`cliente` (
  `id_cliente` INT NOT NULL,
  `nombre` VARCHAR(45) NOT NULL,
  `apellido` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_cliente`))
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table `callcenter`.`supervisor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `callcenter`.`supervisor` (
  `id_supervisor` INT NOT NULL,
  `nombre` VARCHAR(45) NULL,
  `apellido` VARCHAR(45) NULL,
  PRIMARY KEY (`id_supervisor`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `callcenter`.`vendedor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `callcenter`.`vendedor` (
  `id_vendedor` INT NOT NULL,
  `nombre` VARCHAR(45) NOT NULL,
  `apellido` VARCHAR(45) NOT NULL,
  `id_supervisor` INT NOT NULL,
  PRIMARY KEY (`id_vendedor`),
  CONSTRAINT `fk_vendedor_supervisor1`
    FOREIGN KEY (`id_supervisor`)
    REFERENCES `callcenter`.`supervisor` (`id_supervisor`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `callcenter`.`producto`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `callcenter`.`producto` (
  `id_producto` INT NOT NULL,
  `nombre_modelo` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_producto`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `callcenter`.`color`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `callcenter`.`color` (
  `id_color` INT NOT NULL,
  `nombre_color` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_color`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `callcenter`.`venta`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `callcenter`.`venta` (
  `id_venta` INT NOT NULL,
  `id_cliente` INT NOT NULL,
  `id_vendedor` INT NOT NULL,
  `id_supervisor` INT NOT NULL,
  `id_producto` INT NOT NULL,
  `id_color` INT NOT NULL,
  `fecha_venta` DATE NOT NULL,
  `domicilio_entrega` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_venta`),
  -- INDEX `fk_venta_cliente_idx` (`id_cliente` ASC) VISIBLE,
  -- INDEX `fk_venta_vendedor1_idx` (`id_vendedor` ASC) VISIBLE,
  -- INDEX `fk_venta_supervisor1_idx` (`id_supervisor` ASC) VISIBLE,
  -- INDEX `fk_venta_producto1_idx` (`id_producto` ASC) VISIBLE,
  -- INDEX `fk_venta_color1_idx` (`id_color` ASC) VISIBLE,
  CONSTRAINT `fk_venta_cliente`
    FOREIGN KEY (`id_cliente`)
    REFERENCES `callcenter`.`cliente` (`id_cliente`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_venta_vendedor1`
    FOREIGN KEY (`id_vendedor`)
    REFERENCES `callcenter`.`vendedor` (`id_vendedor`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_venta_supervisor1`
    FOREIGN KEY (`id_supervisor`)
    REFERENCES `callcenter`.`supervisor` (`id_supervisor`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_venta_producto1`
    FOREIGN KEY (`id_producto`)
    REFERENCES `callcenter`.`producto` (`id_producto`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_venta_color1`
    FOREIGN KEY (`id_color`)
    REFERENCES `callcenter`.`color` (`id_color`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- ---------------------------------------------------------------------

-- 15.  Insertar 10 filas como mínimo a cada una de las tablas del modelo usando la herramienta MySql Workbench para este punto y los restantes.

-- Insert sobre cliente
INSERT INTO cliente VALUES (1, 'Matias', 'Perez');
INSERT INTO cliente VALUES (2, 'Martin', 'Rocca');
INSERT INTO cliente VALUES (3, 'Julian', 'Sassone');
INSERT INTO cliente VALUES (4, 'Fabian', 'Barreiro');
INSERT INTO cliente VALUES (5, 'Marcelo', 'Tinelli');
INSERT INTO cliente VALUES (6, 'Joan', 'Peralta');
INSERT INTO cliente VALUES (7, 'Alejo', 'Mezzarapa');
INSERT INTO cliente VALUES (8, 'Federico', 'Rodriguez');
INSERT INTO cliente VALUES (9, 'Joaquin', 'Brizuela');
INSERT INTO cliente VALUES (10, 'Pablo', 'Casiva');

-- Insert sobre producto
INSERT INTO producto VALUES (1, 'NexusOne');
INSERT INTO producto VALUES (2, 'NexusS');
INSERT INTO producto VALUES (3, 'GalaxyNexus');
INSERT INTO producto VALUES (4, 'Nexus4');
INSERT INTO producto VALUES (5, 'Nexus5');
INSERT INTO producto VALUES (6, 'Nexus6');
INSERT INTO producto VALUES (7, 'Nexus6P');
INSERT INTO producto VALUES (8, 'Pixel');
INSERT INTO producto VALUES (9, 'Pixel2');
INSERT INTO producto VALUES (10, 'Pixel3');

-- Insert sobre supervisor
INSERT INTO supervisor VALUES (1, 'Nicolas', 'Melichopulos');
INSERT INTO supervisor VALUES (2, 'Debora', 'Barrios');
INSERT INTO supervisor VALUES (3, 'Ivan', 'Augusto');
INSERT INTO supervisor VALUES (4, 'Catalina', 'Gonzalez');
INSERT INTO supervisor VALUES (5, 'Ernesto', 'Gomez');
INSERT INTO supervisor VALUES (6, 'Daniela', 'DiLeo');
INSERT INTO supervisor VALUES (7, 'Andrea', 'Perez');
INSERT INTO supervisor VALUES (8, 'Luciano', 'Marroni');
INSERT INTO supervisor VALUES (9, 'Lucas', 'Molina');
INSERT INTO supervisor VALUES (10, 'Carolina', 'Griffone');

-- Insert sobre vendedor
-- INSERT INTO vendedor VALUES (id, 'nombre', 'apellido', 'supervisor');
INSERT INTO vendedor VALUES (1, 'Fernanda', 'Salas', 1);
INSERT INTO vendedor VALUES (2, 'Emily', 'RuizLedezma', 5);
INSERT INTO vendedor VALUES (3, 'Lautaro', 'Peralta', 1);
INSERT INTO vendedor VALUES (4, 'Pablo', 'Iza', 2);
INSERT INTO vendedor VALUES (5, 'Lucia', 'Macaya', 3);
INSERT INTO vendedor VALUES (6, 'Luna', 'Rios', 1);
INSERT INTO vendedor VALUES (7, 'Mateo', 'Farina', 6);
INSERT INTO vendedor VALUES (8, 'Milena', 'PerezAmezquita', 6);
INSERT INTO vendedor VALUES (9, 'Marianella', 'Luna', 8);
INSERT INTO vendedor VALUES (10, 'Romina', 'Gomez', 5);

-- Insert sobre color
INSERT INTO color VALUES (1, 'rojo');
INSERT INTO color VALUES (2, 'azul');
INSERT INTO color VALUES (3, 'amarillo');
INSERT INTO color VALUES (4, 'verde');
INSERT INTO color VALUES (5, 'violeta');
INSERT INTO color VALUES (6, 'naranja');
INSERT INTO color VALUES (7, 'blanco');
INSERT INTO color VALUES (8, 'negro');
INSERT INTO color VALUES (9, 'dorado');
INSERT INTO color VALUES (10, 'plateado');

-- Insert sobre venta
-- INSERT INTO venta VALUES (id, cliente, vendedor, supervisor, producto, color, fecha de venta, domicilio de entrega);
INSERT INTO venta VALUES (1, 2, 1, 1, 9, 1, '2019-08-08', 'Callefalsa 1231');
INSERT INTO venta VALUES (2, 5, 1, 1, 9, 1, '2019-08-07', 'Gandolfo 3231');
INSERT INTO venta VALUES (3, 1, 1, 1, 9, 9, '2019-08-01', 'Costa Rica 2200');
INSERT INTO venta VALUES (4, 2, 4, 2, 5, 7, '2019-08-01', 'General San Martin 2345');
INSERT INTO venta VALUES (5, 2, 4, 2, 4, 7, '2019-08-08', 'Avenida Intendente Arnoldi 2456');
INSERT INTO venta VALUES (6, 3, 7, 3, 3, 6, '2019-08-08', 'Cerrito 856');
INSERT INTO venta VALUES (7, 7, 6, 3, 6, 4, '2019-08-20', 'Italia 415');
INSERT INTO venta VALUES (8, 6, 6, 5, 7, 9, '2019-08-21', 'Silvano 233');
INSERT INTO venta VALUES (9, 10, 10, 6, 10, 10, '2019-08-10', 'Avenida Libertador General San Martin 2020');
INSERT INTO venta VALUES (10, 9, 8, 3, 10, 7, '2019-08-12', 'Avenida Libertador General San Martin 100');


-- 16.  Hacer la consulta SELECT de todas las filas de todas las tablas.

SELECT * FROM cliente;
SELECT * FROM producto;
SELECT * FROM supervisor;
SELECT * FROM vendedor;
SELECT * FROM color;
SELECT * FROM venta;


-- 17.  Hacer la consulta SELECT con condiciones y orden (WHERE y ORDER BY).

-- Selecciona todas las columnas de la tabla venta donde el supervisor sea el ID 1 y los ordena por ID de venta.
SELECT * FROM venta
WHERE id_supervisor = 1
ORDER BY id_venta;


-- 18.  Hacer la consulta SELECT que relacione 2 tablas (INNER JOIN).

-- Selecciona columnas id_venta, id_vendedor, nombre y apellido del vendedor e id_supervisor, relacionando via INNER JOIN con la tabla vendedor.
SELECT id_venta as venta, venta.id_vendedor, vendedor.nombre, vendedor.apellido, venta.id_supervisor FROM venta
INNER JOIN vendedor ON venta.id_vendedor = vendedor.id_vendedor
ORDER BY id_vendedor;


-- 19.  Hacer la consulta SELECT que relacione 3 tablas (INNER JOIN).

-- Selecciona columnas id_venta, id_vendedor, nombre y apellido del vendedor e id_supervisor con nombre y apellido del supervisor,
-- relacionando via INNER JOIN con las tablas vendedor y supervisor.
SELECT id_venta as venta, venta.id_vendedor, vendedor.nombre, vendedor.apellido, venta.id_supervisor, supervisor.nombre, supervisor.apellido FROM venta
INNER JOIN vendedor ON venta.id_vendedor = vendedor.id_vendedor
INNER JOIN supervisor ON venta.id_supervisor = supervisor.id_supervisor
ORDER BY id_vendedor;


-- 20.  Hacer la consulta SELECT con patrones de búsqueda (LIKE y '%').

-- Consulta del item 18 con condición de filtrar resultados que en la columna vendedor.nombre comiencen con la letra F.
SELECT id_venta as venta, venta.id_vendedor, vendedor.nombre, vendedor.apellido, venta.id_supervisor FROM venta
INNER JOIN vendedor ON venta.id_vendedor = vendedor.id_vendedor
WHERE vendedor.nombre LIKE 'F%'
ORDER BY id_vendedor;


-- 21.  Hacer la consulta SELECT con la unión de dos consultas (UNION).

-- Consulta datos de empleados (vendedores y supervisores) ordenados alfabeticamente por apellido y luego por nombre.
SELECT id_vendedor as id, nombre, apellido FROM vendedor
UNION
SELECT id_supervisor as id, nombre, apellido FROM supervisor
ORDER BY apellido, nombre;


-- 22.  Hacer la consulta SELECT con agrupadores, columnas calculadas y filtros (COUNT -u otros-, GROUP BY y HAVING).

-- Consulta la cantidad total de ventas por operador, mostrando solo aquellos que tengan más de una venta, ordenado de mayor a menor por cantidad
-- de ventas y luego orden alfabético por supervisor y luego por apellido del vendedor
SELECT venta.id_vendedor as id, vendedor.nombre, vendedor.apellido, supervisor.apellido, COUNT(id_venta) as "ventas totales" FROM venta
INNER JOIN vendedor ON venta.id_vendedor = vendedor.id_vendedor
INNER JOIN supervisor ON venta.id_supervisor = supervisor.id_supervisor
GROUP BY id
HAVING COUNT(id_venta) > 1
ORDER BY COUNT(id_venta) DESC, supervisor.apellido, vendedor.apellido;