· 6 years ago · Oct 17, 2019, 04:06 AM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @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';
6
7-- -----------------------------------------------------
8-- Schema callcenter
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema callcenter
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `callcenter` ;
15USE `callcenter` ;
16
17-- -----------------------------------------------------
18-- Table `callcenter`.`cliente`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `callcenter`.`cliente` (
21 `id_cliente` INT NOT NULL,
22 `nombre` VARCHAR(45) NOT NULL,
23 `apellido` VARCHAR(45) NOT NULL,
24 PRIMARY KEY (`id_cliente`))
25ENGINE = InnoDB;
26
27
28
29-- -----------------------------------------------------
30-- Table `callcenter`.`supervisor`
31-- -----------------------------------------------------
32CREATE TABLE IF NOT EXISTS `callcenter`.`supervisor` (
33 `id_supervisor` INT NOT NULL,
34 `nombre` VARCHAR(45) NULL,
35 `apellido` VARCHAR(45) NULL,
36 PRIMARY KEY (`id_supervisor`))
37ENGINE = InnoDB;
38
39
40-- -----------------------------------------------------
41-- Table `callcenter`.`vendedor`
42-- -----------------------------------------------------
43CREATE TABLE IF NOT EXISTS `callcenter`.`vendedor` (
44 `id_vendedor` INT NOT NULL,
45 `nombre` VARCHAR(45) NOT NULL,
46 `apellido` VARCHAR(45) NOT NULL,
47 `id_supervisor` INT NOT NULL,
48 PRIMARY KEY (`id_vendedor`),
49 CONSTRAINT `fk_vendedor_supervisor1`
50 FOREIGN KEY (`id_supervisor`)
51 REFERENCES `callcenter`.`supervisor` (`id_supervisor`)
52 ON DELETE NO ACTION
53 ON UPDATE NO ACTION)
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `callcenter`.`producto`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `callcenter`.`producto` (
61 `id_producto` INT NOT NULL,
62 `nombre_modelo` VARCHAR(45) NOT NULL,
63 PRIMARY KEY (`id_producto`))
64ENGINE = InnoDB;
65
66
67-- -----------------------------------------------------
68-- Table `callcenter`.`color`
69-- -----------------------------------------------------
70CREATE TABLE IF NOT EXISTS `callcenter`.`color` (
71 `id_color` INT NOT NULL,
72 `nombre_color` VARCHAR(45) NOT NULL,
73 PRIMARY KEY (`id_color`))
74ENGINE = InnoDB;
75
76
77-- -----------------------------------------------------
78-- Table `callcenter`.`venta`
79-- -----------------------------------------------------
80CREATE TABLE IF NOT EXISTS `callcenter`.`venta` (
81 `id_venta` INT NOT NULL,
82 `id_cliente` INT NOT NULL,
83 `id_vendedor` INT NOT NULL,
84 `id_supervisor` INT NOT NULL,
85 `id_producto` INT NOT NULL,
86 `id_color` INT NOT NULL,
87 `fecha_venta` DATE NOT NULL,
88 `domicilio_entrega` VARCHAR(45) NOT NULL,
89 PRIMARY KEY (`id_venta`),
90 -- INDEX `fk_venta_cliente_idx` (`id_cliente` ASC) VISIBLE,
91 -- INDEX `fk_venta_vendedor1_idx` (`id_vendedor` ASC) VISIBLE,
92 -- INDEX `fk_venta_supervisor1_idx` (`id_supervisor` ASC) VISIBLE,
93 -- INDEX `fk_venta_producto1_idx` (`id_producto` ASC) VISIBLE,
94 -- INDEX `fk_venta_color1_idx` (`id_color` ASC) VISIBLE,
95 CONSTRAINT `fk_venta_cliente`
96 FOREIGN KEY (`id_cliente`)
97 REFERENCES `callcenter`.`cliente` (`id_cliente`)
98 ON DELETE NO ACTION
99 ON UPDATE NO ACTION,
100 CONSTRAINT `fk_venta_vendedor1`
101 FOREIGN KEY (`id_vendedor`)
102 REFERENCES `callcenter`.`vendedor` (`id_vendedor`)
103 ON DELETE NO ACTION
104 ON UPDATE NO ACTION,
105 CONSTRAINT `fk_venta_supervisor1`
106 FOREIGN KEY (`id_supervisor`)
107 REFERENCES `callcenter`.`supervisor` (`id_supervisor`)
108 ON DELETE NO ACTION
109 ON UPDATE NO ACTION,
110 CONSTRAINT `fk_venta_producto1`
111 FOREIGN KEY (`id_producto`)
112 REFERENCES `callcenter`.`producto` (`id_producto`)
113 ON DELETE NO ACTION
114 ON UPDATE NO ACTION,
115 CONSTRAINT `fk_venta_color1`
116 FOREIGN KEY (`id_color`)
117 REFERENCES `callcenter`.`color` (`id_color`)
118 ON DELETE NO ACTION
119 ON UPDATE NO ACTION)
120ENGINE = InnoDB;
121
122
123
124SET SQL_MODE=@OLD_SQL_MODE;
125SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
126SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
127
128-- ---------------------------------------------------------------------
129
130-- 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.
131
132-- Insert sobre cliente
133INSERT INTO cliente VALUES (1, 'Matias', 'Perez');
134INSERT INTO cliente VALUES (2, 'Martin', 'Rocca');
135INSERT INTO cliente VALUES (3, 'Julian', 'Sassone');
136INSERT INTO cliente VALUES (4, 'Fabian', 'Barreiro');
137INSERT INTO cliente VALUES (5, 'Marcelo', 'Tinelli');
138INSERT INTO cliente VALUES (6, 'Joan', 'Peralta');
139INSERT INTO cliente VALUES (7, 'Alejo', 'Mezzarapa');
140INSERT INTO cliente VALUES (8, 'Federico', 'Rodriguez');
141INSERT INTO cliente VALUES (9, 'Joaquin', 'Brizuela');
142INSERT INTO cliente VALUES (10, 'Pablo', 'Casiva');
143
144-- Insert sobre producto
145INSERT INTO producto VALUES (1, 'NexusOne');
146INSERT INTO producto VALUES (2, 'NexusS');
147INSERT INTO producto VALUES (3, 'GalaxyNexus');
148INSERT INTO producto VALUES (4, 'Nexus4');
149INSERT INTO producto VALUES (5, 'Nexus5');
150INSERT INTO producto VALUES (6, 'Nexus6');
151INSERT INTO producto VALUES (7, 'Nexus6P');
152INSERT INTO producto VALUES (8, 'Pixel');
153INSERT INTO producto VALUES (9, 'Pixel2');
154INSERT INTO producto VALUES (10, 'Pixel3');
155
156-- Insert sobre supervisor
157INSERT INTO supervisor VALUES (1, 'Nicolas', 'Melichopulos');
158INSERT INTO supervisor VALUES (2, 'Debora', 'Barrios');
159INSERT INTO supervisor VALUES (3, 'Ivan', 'Augusto');
160INSERT INTO supervisor VALUES (4, 'Catalina', 'Gonzalez');
161INSERT INTO supervisor VALUES (5, 'Ernesto', 'Gomez');
162INSERT INTO supervisor VALUES (6, 'Daniela', 'DiLeo');
163INSERT INTO supervisor VALUES (7, 'Andrea', 'Perez');
164INSERT INTO supervisor VALUES (8, 'Luciano', 'Marroni');
165INSERT INTO supervisor VALUES (9, 'Lucas', 'Molina');
166INSERT INTO supervisor VALUES (10, 'Carolina', 'Griffone');
167
168-- Insert sobre vendedor
169-- INSERT INTO vendedor VALUES (id, 'nombre', 'apellido', 'supervisor');
170INSERT INTO vendedor VALUES (1, 'Fernanda', 'Salas', 1);
171INSERT INTO vendedor VALUES (2, 'Emily', 'RuizLedezma', 5);
172INSERT INTO vendedor VALUES (3, 'Lautaro', 'Peralta', 1);
173INSERT INTO vendedor VALUES (4, 'Pablo', 'Iza', 2);
174INSERT INTO vendedor VALUES (5, 'Lucia', 'Macaya', 3);
175INSERT INTO vendedor VALUES (6, 'Luna', 'Rios', 1);
176INSERT INTO vendedor VALUES (7, 'Mateo', 'Farina', 6);
177INSERT INTO vendedor VALUES (8, 'Milena', 'PerezAmezquita', 6);
178INSERT INTO vendedor VALUES (9, 'Marianella', 'Luna', 8);
179INSERT INTO vendedor VALUES (10, 'Romina', 'Gomez', 5);
180
181-- Insert sobre color
182INSERT INTO color VALUES (1, 'rojo');
183INSERT INTO color VALUES (2, 'azul');
184INSERT INTO color VALUES (3, 'amarillo');
185INSERT INTO color VALUES (4, 'verde');
186INSERT INTO color VALUES (5, 'violeta');
187INSERT INTO color VALUES (6, 'naranja');
188INSERT INTO color VALUES (7, 'blanco');
189INSERT INTO color VALUES (8, 'negro');
190INSERT INTO color VALUES (9, 'dorado');
191INSERT INTO color VALUES (10, 'plateado');
192
193-- Insert sobre venta
194-- INSERT INTO venta VALUES (id, cliente, vendedor, supervisor, producto, color, fecha de venta, domicilio de entrega);
195INSERT INTO venta VALUES (1, 2, 1, 1, 9, 1, '2019-08-08', 'Callefalsa 1231');
196INSERT INTO venta VALUES (2, 5, 1, 1, 9, 1, '2019-08-07', 'Gandolfo 3231');
197INSERT INTO venta VALUES (3, 1, 1, 1, 9, 9, '2019-08-01', 'Costa Rica 2200');
198INSERT INTO venta VALUES (4, 2, 4, 2, 5, 7, '2019-08-01', 'General San Martin 2345');
199INSERT INTO venta VALUES (5, 2, 4, 2, 4, 7, '2019-08-08', 'Avenida Intendente Arnoldi 2456');
200INSERT INTO venta VALUES (6, 3, 7, 3, 3, 6, '2019-08-08', 'Cerrito 856');
201INSERT INTO venta VALUES (7, 7, 6, 3, 6, 4, '2019-08-20', 'Italia 415');
202INSERT INTO venta VALUES (8, 6, 6, 5, 7, 9, '2019-08-21', 'Silvano 233');
203INSERT INTO venta VALUES (9, 10, 10, 6, 10, 10, '2019-08-10', 'Avenida Libertador General San Martin 2020');
204INSERT INTO venta VALUES (10, 9, 8, 3, 10, 7, '2019-08-12', 'Avenida Libertador General San Martin 100');
205
206
207-- 16. Hacer la consulta SELECT de todas las filas de todas las tablas.
208
209SELECT * FROM cliente;
210SELECT * FROM producto;
211SELECT * FROM supervisor;
212SELECT * FROM vendedor;
213SELECT * FROM color;
214SELECT * FROM venta;
215
216
217-- 17. Hacer la consulta SELECT con condiciones y orden (WHERE y ORDER BY).
218
219-- Selecciona todas las columnas de la tabla venta donde el supervisor sea el ID 1 y los ordena por ID de venta.
220SELECT * FROM venta
221WHERE id_supervisor = 1
222ORDER BY id_venta;
223
224
225-- 18. Hacer la consulta SELECT que relacione 2 tablas (INNER JOIN).
226
227-- Selecciona columnas id_venta, id_vendedor, nombre y apellido del vendedor e id_supervisor, relacionando via INNER JOIN con la tabla vendedor.
228SELECT id_venta as venta, venta.id_vendedor, vendedor.nombre, vendedor.apellido, venta.id_supervisor FROM venta
229INNER JOIN vendedor ON venta.id_vendedor = vendedor.id_vendedor
230ORDER BY id_vendedor;
231
232
233-- 19. Hacer la consulta SELECT que relacione 3 tablas (INNER JOIN).
234
235-- Selecciona columnas id_venta, id_vendedor, nombre y apellido del vendedor e id_supervisor con nombre y apellido del supervisor,
236-- relacionando via INNER JOIN con las tablas vendedor y supervisor.
237SELECT id_venta as venta, venta.id_vendedor, vendedor.nombre, vendedor.apellido, venta.id_supervisor, supervisor.nombre, supervisor.apellido FROM venta
238INNER JOIN vendedor ON venta.id_vendedor = vendedor.id_vendedor
239INNER JOIN supervisor ON venta.id_supervisor = supervisor.id_supervisor
240ORDER BY id_vendedor;
241
242
243-- 20. Hacer la consulta SELECT con patrones de búsqueda (LIKE y '%').
244
245-- Consulta del item 18 con condición de filtrar resultados que en la columna vendedor.nombre comiencen con la letra F.
246SELECT id_venta as venta, venta.id_vendedor, vendedor.nombre, vendedor.apellido, venta.id_supervisor FROM venta
247INNER JOIN vendedor ON venta.id_vendedor = vendedor.id_vendedor
248WHERE vendedor.nombre LIKE 'F%'
249ORDER BY id_vendedor;
250
251
252-- 21. Hacer la consulta SELECT con la unión de dos consultas (UNION).
253
254-- Consulta datos de empleados (vendedores y supervisores) ordenados alfabeticamente por apellido y luego por nombre.
255SELECT id_vendedor as id, nombre, apellido FROM vendedor
256UNION
257SELECT id_supervisor as id, nombre, apellido FROM supervisor
258ORDER BY apellido, nombre;
259
260
261-- 22. Hacer la consulta SELECT con agrupadores, columnas calculadas y filtros (COUNT -u otros-, GROUP BY y HAVING).
262
263-- 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
264-- de ventas y luego orden alfabético por supervisor y luego por apellido del vendedor
265SELECT venta.id_vendedor as id, vendedor.nombre, vendedor.apellido, supervisor.apellido, COUNT(id_venta) as "ventas totales" FROM venta
266INNER JOIN vendedor ON venta.id_vendedor = vendedor.id_vendedor
267INNER JOIN supervisor ON venta.id_supervisor = supervisor.id_supervisor
268GROUP BY id
269HAVING COUNT(id_venta) > 1
270ORDER BY COUNT(id_venta) DESC, supervisor.apellido, vendedor.apellido;