· 6 years ago · Nov 18, 2019, 02:12 PM
1DROP DATABASE IF EXISTS prueba02_db;
2CREATE DATABASE prueba02_db;
3USE prueba02_db;
4
5
6-- start tables
7
8CREATE TABLE cargo (
9 id INT AUTO_INCREMENT NOT NULL,
10 nombre VARCHAR(50) NOT NULL,
11 sueldo INT NOT NULL,
12
13 PRIMARY KEY (id),
14 UNIQUE (nombre)
15);
16
17CREATE TABLE persona (
18 uuid VARCHAR(36) NOT NULL,
19 rut VARCHAR(13) NOT NULL,
20 nombre VARCHAR(50) NOT NULL,
21 cargo_id_fk INT NOT NULL,
22
23 PRIMARY KEY (uuid),
24 UNIQUE (rut),
25 FOREIGN KEY (cargo_id_fk) REFERENCES cargo(id)
26);
27
28CREATE TABLE telefono(
29 id INT AUTO_INCREMENT NOT NULL,
30 numero_telefono VARCHAR(20) NOT NULL,
31
32 PRIMARY KEY (id),
33 UNIQUE (numero_telefono)
34);
35
36CREATE TABLE persona_telefono (
37 id INT AUTO_INCREMENT NOT NULL,
38 persona_uuid_fk VARCHAR(36) NOT NULL,
39 telefono_id_fk INT NOT NULL,
40 is_activo BIT(1) NOT NULL DEFAULT 1,
41
42 PRIMARY KEY (id),
43 UNIQUE (telefono_id_fk),
44 FOREIGN KEY (persona_uuid_fk) REFERENCES persona(uuid),
45 FOREIGN KEY (telefono_id_fk) REFERENCES telefono(id)
46);
47-- end tables / start inserts
48
49INSERT INTO cargo(nombre,sueldo) VALUES ("Director Ejecutivo - Tier 0",3000000),
50 ("Empleado - TIER A",1200000),
51 ("Empleado - TIER B",1000000),
52 ("Empleado - TIER C",700000);
53
54INSERT INTO persona(uuid,rut,nombre,cargo_id_fk)
55 VALUES (UUID(),'111-1','Juan Fuentes', 1),
56 (UUID(),'222-2','Ricardo Martinez' , 2),
57 (UUID(),'333-3','Sebastian Valenzuela' , 3),
58 (UUID(),'444-4','Estefania Silva' , 4),
59 (UUID(),'555-5','Cristian Estay' , 4);
60
61INSERT INTO telefono(numero_telefono)
62 VALUES ('+56-955-524-034'), -- 1
63 ('+56-955-515-698'), -- 2
64 ('+56-955-576-051'), -- 3
65 ('+56-955-566-729'), -- 4
66 ('+56-955-593-816'), -- 5
67 ('+56-955-595-688'), -- 6
68 ('+56-955-563-173'), -- 7
69 ('+56-955-516-245'), -- 8
70
71 ('+56-955-573-000'), -- 9
72 ('+56-955-537-000'); -- 10
73
74
75SET @Juan_Fuentes = (SELECT uuid FROM persona WHERE rut = '111-1');
76SET @Ricardo_Mart = (SELECT uuid FROM persona WHERE rut = '222-2');
77SET @Sebastian_Va = (SELECT uuid FROM persona WHERE rut = '333-3');
78SET @Estefania_Si = (SELECT uuid FROM persona WHERE rut = '444-4');
79
80INSERT INTO persona_telefono(persona_uuid_fk, telefono_id_fk)
81 VALUES (@Juan_Fuentes, 1), (@Juan_Fuentes, 2), (@Juan_Fuentes, 3),
82 (@Ricardo_Mart, 4), (@Ricardo_Mart, 5),
83 (@Sebastian_Va, 6),
84 (@Estefania_Si, 7), (@Estefania_Si, 8);
85
86UPDATE persona_telefono SET is_activo = 0 WHERE id = 2 OR id = 4;
87
88-- end inserts / start sql query
89
90-- Tabla de puntajes
91/*
92//==========[]=========\\
93|| pregunta || puntaje ||
94|]==========[]=========[|
95|| 1 || 5 ||
96|| 2 || 5 ||
97|| 3 || 5 ||
98|| 4 || 5 ||
99|| 5 || 5 ||
100|| 6 || 20 ||
101\\==========[]=========//
102*/
103
104
105-- 1) MOSTRAR EL COSTO TOTAL MENSUAL DE LA MANO DE OBRA TOTAL DE LA EMPRESA
106-- (CONSIDERE EL CAMPO CARGO.SUELDO COMO COSTO MENSUAL)
107
108
109
110-- 2) MOSTRAR EL NOMBRE DE LA PERSONA Y NUMERO DE CELULAR,
111-- DE LAS PERSONAS QUE TIENEN ALGÚN CELULAR INACTIVO
112
113
114
115-- 3) MUESTRE EL NOMBRE DE LA PERSONA Y LA CANTIDAD DE CELULARES QUE ESTA TIENE
116
117
118
119-- 4) MOSTRAR LOS NUMERO DE CELULARES QUE AUN NO SE ASIGNAN A PERSONA ALGUNA
120
121
122
123
124-- 5) MOSTRAR EL NOMBRE DE LAS PERSONAS QUE NO TIENEN CELULAR ASOCIADO
125
126
127
128/*
1296) GENERE PROCEDIMIENTO ALMACENADO PARA AGREGAR UNA PERSONA Y 1 CELULAR ASOCIADO
130 ============================================================================
131 Considere (Sobre persona y celular):
132 - Si la persona existe
133 -> Si el celular no existe, crea un nuevo celular y lo asocia a persona
134 -> Si el celular YA existe , hace nada...
135 - Si la persona NO existe
136 -> Crea la persona
137 -> Si el celular no existe, crea un nuevo celular y lo asocia a persona
138 -> Si el celular YA existe , hace nada...
139
140
141 ============================================================================
142 Considere (Sobre cargo):
143 -Los cargos se dijitaran en el procedimiento como un solo VARCHAR(1)
144 -donde los valores son:
145 + "0" -> Director Ejecutivo - Tier 0
146 + "A" -> Empleado - TIER A
147 + "B" -> Empleado - TIER B
148 + "C" -> Empleado - TIER C
149
150 ============================================================================
151*/
152
153-- El procedimiento debe llamarse de la siguiente forma ☜(ﾟヮﾟ☜)
154
155CALL agregar_persona ('13444312-5', 'Juan Acevedo Acevedo y Acevedo', 'A' , '+56-955-563-173');
156-- RUT NOMBRE CARGO NUMERO