-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------





--
-- Create schema usuario
--

CREATE DATABASE IF NOT EXISTS usuario;
USE usuario;

--
-- Definition of table `tdepta`
--

CREATE TABLE `tdepta` (
  `numdep` varchar(50) DEFAULT NULL,
  `nomdep` varchar(50) DEFAULT NULL,
  `numdirec` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tdepta`
--

INSERT INTO `tdepta` (`numdep`,`nomdep`,`numdirec`) VALUES 
 ('A00','Centro Procesos','000010'),
 ('B01','Planificación','000020'),
 ('C01','Información','000030'),
 ('D01','Desarrollo',NULL),
 ('D11','Fabricación','000060'),
 ('D21','Administración','000070'),
 ('E01','Servicios','000050'),
 ('E11','Operaciones','000090'),
 ('E21','Software','000100');


--
-- Definition of table `templa`
--

CREATE TABLE `templa` (
  `nuempl` varchar(6) DEFAULT NULL,
  `nombre` varchar(12) DEFAULT NULL,
  `inicial` varchar(50) DEFAULT NULL,
  `apellido` varchar(50) DEFAULT NULL,
  `dept` varchar(50) DEFAULT NULL,
  `tlfn` varchar(50) DEFAULT NULL,
  `feching` datetime DEFAULT NULL,
  `codtra` int(10) DEFAULT NULL,
  `niveduc` int(10) DEFAULT NULL,
  `sexo` varchar(50) DEFAULT NULL,
  `fechnac` datetime DEFAULT NULL,
  `salario` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `templa`
--

INSERT INTO `templa` (`nuempl`,`nombre`,`inicial`,`apellido`,`dept`,`tlfn`,`feching`,`codtra`,`niveduc`,`sexo`,`fechnac`,`salario`) VALUES 
 ('000010','Cristina','I','Hernández','A00','3978','1985-01-01 00:00:00',66,18,'F','1973-08-14 00:00:00',52750),
 ('000020','Miguel','L','Tapia','B01','3476','1993-10-10 00:00:00',61,18,'M','1978-02-02 00:00:00',41250),
 ('000030','Salomé','A','Kempes','C01','4738','1995-04-05 00:00:00',60,20,'F','1981-05-11 00:00:00',38250),
 ('000050','Juan','B','Garrido','E01','6789','1989-08-17 00:00:00',58,16,'M','1965-09-15 00:00:00',40175),
 ('000060','Isidro','F','Suarez','D11','6423','1983-09-14 00:00:00',55,16,'M','1975-07-07 00:00:00',32250),
 ('000070','Eva','D','Puente','D21','7831','2000-09-30 00:00:00',56,16,'F','1963-05-26 00:00:00',36170),
 ('000090','Elena','W','Herranz','E11','5498','1990-08-15 00:00:00',55,16,'F','1981-05-15 00:00:00',29750),
 ('000100','Tomás','Q','Soler','E21','0972','2000-06-19 00:00:00',54,14,'M','1966-12-18 00:00:00',26150),
 ('000110','Vicente','G','Luengo','A00','3490','2005-05-16 00:00:00',58,19,'M','1969-11-05 00:00:00',46500),
 ('000120','Simón',NULL,'Otero','A00','2167','2003-12-05 00:00:00',58,14,'M','1982-10-18 00:00:00',29250),
 ('000130','Dolores','M','Quintana','C01','4578','1991-07-28 00:00:00',55,16,'F','1965-09-15 00:00:00',23800),
 ('000140','Heliodora','A','Nieto','C01','1793','1996-12-15 00:00:00',56,18,'F','1976-01-19 00:00:00',28420),
 ('000150','Bruno',NULL,'Alvarez','D11','4510','1992-02-12 00:00:00',55,16,'M','1977-05-17 00:00:00',25280),
 ('000160','Elisa','R','Pinto','D11','3782','1997-10-11 00:00:00',54,17,'F','1965-04-12 00:00:00',22250),
 ('000170','Mateo','J','Yarza','D11','2890','1998-09-15 00:00:00',54,16,'M','1961-01-05 00:00:00',24680),
 ('000180','Marina','S','Sandoval','D11','1682','1993-07-07 00:00:00',53,17,'F','1979-02-21 00:00:00',21340),
 ('000190','Jaime','H','Walker','D11','2986','1994-07-26 00:00:00',53,16,'M','1962-06-25 00:00:00',20450),
 ('000200','David',NULL,'Bondia','D11','4501','1986-03-03 00:00:00',55,16,'M','1981-05-29 00:00:00',27740),
 ('000210','Wenceslao','T','Jurado','D11','0942','1999-04-11 00:00:00',52,17,'M','1963-02-23 00:00:00',18270),
 ('000220','Jimena','K','Luque','D11','0672','1988-08-29 00:00:00',55,18,'F','1978-03-19 00:00:00',29840),
 ('000230','Jaime','J','Jimenez','D21','2094','1986-11-21 00:00:00',53,14,'M','1975-05-30 00:00:00',22180),
 ('000240','Salvador','M','Martínez','D21',NULL,'1999-12-05 00:00:00',55,17,'M','1964-03-31 00:00:00',28760),
 ('000250','Daniel','S','Sierra','D21',NULL,'1989-10-30 00:00:00',52,15,'M','1979-11-12 00:00:00',19180),
 ('000260','Susana','V','Junquera','D21','8953','1995-09-11 00:00:00',52,16,'F','1976-10-05 00:00:00',17250),
 ('000270','Maria','L','Perez','D21',NULL,'2000-09-30 00:00:00',55,15,'F','1963-05-25 00:00:00',27380),
 ('000280','Engracia','R','Sanchez','E11','8997','1987-03-24 00:00:00',54,17,'F','1976-03-28 00:00:00',26250),
 ('000290','Juan','R','Palacios','E11','4502','2000-05-30 00:00:00',42,12,'M','1976-07-09 00:00:00',15340),
 ('000300','Pedro','X','Sierra','E11','2095','1992-06-19 00:00:00',48,14,'M','1976-10-27 00:00:00',17750),
 ('000310','Matilde','F','Serna','E11','3332','1984-09-12 00:00:00',43,12,'F','1971-04-25 00:00:00',15900),
 ('000320','Ramón','V','Morán','E21','9990','1985-07-07 00:00:00',52,16,'M','1972-08-11 00:00:00',19950),
 ('000330','Willy',NULL,'Lerma','E21','2103','1996-02-23 00:00:00',55,14,'M','1981-07-18 00:00:00',25370),
 ('000340','Javier','R','Gil','E21','5698','2005-05-05 00:00:00',54,16,'M','1966-05-17 00:00:00',23840);


--
-- Definition of function `bruto`
--

DROP FUNCTION IF EXISTS `bruto`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` FUNCTION `bruto`(codigo varchar(50)) RETURNS int(11)
BEGIN
        declare pasta int;
        select sum(salario) into pasta from templa where dept=codigo;
        if(pasta is null) then
                return 0;
        else
                return pasta;
        end if;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of function `edad`
--

DROP FUNCTION IF EXISTS `edad`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` FUNCTION `edad`(fecha1 DATE, fecha2 DATE) RETURNS int(11)
BEGIN
        DECLARE respuesta INT;
        SET respuesta=year(fecha2)-year(fecha1);
        RETURN respuesta;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;



