Tema: consulta mysql
Ver Mensaje Individual
  #9 (permalink)  
Antiguo 25/03/2015, 09:13
adrimessi96
 
Fecha de Ingreso: marzo-2015
Mensajes: 7
Antigüedad: 9 años, 1 mes
Puntos: 0
Respuesta: consulta mysql
QUIERO SABER QUE RECURSOS NO ESTAN EN USO AHORA
Código SQL: 
Ver original
  1. CREATE TABLE Departamentos
  2. (
  3.     CodDep      INT   PRIMARY KEY,
  4.     NomDep      VARCHAR (20)
  5. );
  6.  
  7. INSERT INTO Departamentos VALUES(10, 'Ventas');
  8. INSERT INTO Departamentos VALUES(20, 'Informática');
  9. INSERT INTO Departamentos VALUES(30, 'RRHH');
  10. INSERT INTO Departamentos VALUES(40, 'Diseño');
  11. INSERT INTO Departamentos VALUES(50, 'Dirección');
  12. INSERT INTO Departamentos VALUES(60, 'Asesoria');
  13.  
  14. CREATE TABLE Empleados
  15. (
  16.     CodEmp      INT PRIMARY KEY,
  17.     NomEmp      VARCHAR(20),
  18.     ApeEmp      VARCHAR(20),
  19.     DirEmp      VARCHAR(50),
  20.     PobEmp      VARCHAR(30),
  21.     TelEmp      VARCHAR(9),
  22.     DNIEmp      VARCHAR(9),
  23.     EmailEmp    VARCHAR(50),
  24.     SalEmp      INT,
  25.     FnacEmp     DATE,
  26.     CodDepEmp   INT,
  27.     CONSTRAINT Emp_Dep_fk FOREIGN KEY (CodDepEmp) REFERENCES Departamentos (CodDep)
  28. );
  29.  
  30. INSERT INTO Empleados VALUES(1, 'Markel','Gonzalez','Altamira 32','Arrasate','654354345','72839402A','[email protected]',1250,'1992/10/30',10);
  31. INSERT INTO Empleados VALUES(2, 'Irene','Inda','San Pedro 12','Arrasate','943797989','76543214S','[email protected]',3456,'1995/12/03',20);
  32. INSERT INTO Empleados VALUES(3, 'Julen','Aja','Supereibar 20','Eibar','694356394','32454345E','[email protected]',1865,'1992/08/30',30);
  33. INSERT INTO Empleados VALUES(4, 'Iñaki','Garcia','Gipuzkoa kalea 20','Arrasate','943485769','72638597n','[email protected]',1145,'1994/07/18',10);
  34. INSERT INTO Empleados VALUES(5, 'Oihane','Lameirinhas','Braulio Iriarte kalea 7','Elizondo','948576809','67484957k','[email protected]',1250,'1990/02/28',30);
  35. INSERT INTO Empleados VALUES(6, 'Ane','Sanchez','Saskaitz Plaza 3','Iruña','948364758','72638385n','[email protected]',1250,'1986/03/10',30);
  36. INSERT INTO Empleados VALUES(7, 'Hamilton','Barandiaran','Paularena kalea 23','Doneztebe','943567890','38438597n','[email protected]',1675,'1988/07/22',20);
  37. INSERT INTO Empleados VALUES(8, 'Juan', 'Lopez', 'Portu kalea , 3-2º', 'Arrasate','612345678','12345678A','[email protected]',NULL,'1994/11/30',30);
  38. INSERT INTO Empleados VALUES(9, 'Andoni', 'Martinez', 'Madina , 45', 'Aretxabaleta','615436738','22345678B','[email protected]',NULL,'1992/06/30',50);
  39. INSERT INTO Empleados VALUES(10, 'Pepe', 'Iriarte', 'Antton kalea, 34', 'Aretxabaleta','645574738','32345478C','[email protected]',2125,'1988/08/30',20);
  40. INSERT INTO Empleados VALUES(11, 'Asier', 'Sanz', 'Gipuzkoa bidea, 23', 'Bergara','676523448','42345478D','[email protected]',1450,'1989/09/15',40);
  41. INSERT INTO Empleados VALUES(12, 'Benito', 'Lertxundi', 'San Andres , 10 - 5', 'Arrasate','692374738','52345478E','[email protected]',1855,'1990/10/12',10);
  42. INSERT INTO Empleados VALUES(13, 'Jon', 'Lasa', 'Olakua 10-D 5', 'Oñati','673234765','62345478F','[email protected]',1250,'1992/02/23',40);
  43. INSERT INTO Empleados VALUES(14, 'Mikel', 'Arregi', 'San Pedro , 10', 'Antzuola','943234765','72745477G','[email protected]',850,'1996/10/23',50);
  44. INSERT INTO Empleados VALUES(15, 'Miren','Txintxurreta','Kale Barria 13 3A','Oñati','943784567','79601245A','[email protected]',880,'1993/12/12',10);
  45. INSERT INTO Empleados VALUES(16, 'Aitor','Biain','San Juan kale 26 2C','Oñati','943781234','72840545Q','[email protected]',750,'1993/09/17',10);
  46. INSERT INTO Empleados VALUES(17, 'Juanjo','Zumalde','San Andres kalea 2 1ezk','Arrasate','943788910','76125001E','[email protected]',1350,'1992/10/12',20);
  47. INSERT INTO Empleados VALUES(18, 'Ainhoa','Agirre','Atzeko Kale 17 2B','Oñati','943710129','43857116L','[email protected]',1900,'1993/12/31',20);
  48. INSERT INTO Empleados VALUES(19, 'Oihane','Olalde','San Lorentzo 65 3esk','Oñati','943718008','72123456I','[email protected]',1475,'1994/12/01',30);
  49. INSERT INTO Empleados VALUES(20, 'Iñigo','Ugarte','Olakua 9 7D','Oñati','943782468','79876543E','[email protected]',2050,'1990/11/10',30);
  50. INSERT INTO Empleados VALUES(21, 'Mikel','Irizar','Kale Zaharra 32 2esk','Oñati','943781357','44017449P','[email protected]',780,'1992/12/31',40);
  51. INSERT INTO Empleados VALUES(22, 'Izaskun','Ibabe','Kale Barria 49 3ezk','Oñati','943717557','76290183A','[email protected]',1450,'1990/07/09',40);
  52. INSERT INTO Empleados VALUES(200, 'Izaskun','Ibabe','Kale Barria 49 3ezk','Oñati','943717557','76290183A','[email protected]',122450,'1990/07/09',40);
  53.  
  54. CREATE TABLE Proveedores
  55. (
  56.     CodPro      INT PRIMARY KEY,
  57.     NomPro      VARCHAR(20),
  58.     DirPro      VARCHAR(50),
  59.     PobPro      VARCHAR(30),
  60.     TelPro      VARCHAR(9)
  61. );
  62.  
  63. INSERT INTO Proveedores VALUES(1, 'Acer','Avda. Euskadi , 12','San Sebastian','943667789');
  64. INSERT INTO Proveedores VALUES(2, 'Media Mark','San Lorenzo, 27','Bilbao','943783376');
  65. INSERT INTO Proveedores VALUES(3, 'Bikode','Kale Zaharra, 12','San Sbastian','943782134');
  66. INSERT INTO Proveedores VALUES(4, 'Lizker','Ibarra, 22','Bilbao','943783374');
  67. INSERT INTO Proveedores VALUES(5, 'HP','Olakua, 23','San Sebastian','943783165');
  68. INSERT INTO Proveedores VALUES(6, 'Bull','Gros , 45','San Sebastian','943883134');
  69.  
  70. CREATE TABLE Recursos
  71. (
  72.     CodRec      INT PRIMARY KEY,
  73.     DesRec      VARCHAR(50),
  74.     TipRec      CHAR,
  75.     PreRec      FLOAT,
  76.     FecRec      DATE,
  77.     CodProRec   INT,
  78.     CONSTRAINT Rec_Pro_fk FOREIGN KEY (CodProRec) REFERENCES Proveedores (CodPro)
  79. );
  80.  
  81. INSERT INTO Recursos VALUES(1000, 'Portatil HP 6550b', 'H' , 656 , '2011-10-01' , 5);
  82. INSERT INTO Recursos VALUES(1001, 'Portatil HP 6550b', 'H' , 656 , '2011-10-01' , 5);
  83. INSERT INTO Recursos VALUES(1002, 'Portatil HP 6550b', 'H' , 656 , '2011-10-01' , 5);
  84. INSERT INTO Recursos VALUES(1003, 'Portatil HP 6550b', 'H' , 656 , '2011-10-01' , 5);
  85. INSERT INTO Recursos VALUES(1004, 'Portatil HP 6550b', 'H' , 656 , '2011-10-01' , 5);
  86. INSERT INTO Recursos VALUES(1005, 'Portatil HP 6550b', 'H' , 656 , '2011-10-01' , 5);
  87. INSERT INTO Recursos VALUES(1006, 'Portatil HP 6550b', 'H' , 656 , '2011-10-01' , 5);
  88. INSERT INTO Recursos VALUES(2000, 'PC sobremesa HP 4210' , 'H' , 458 , '2011-10-01' , 5);
  89. INSERT INTO Recursos VALUES(2001, 'PC sobremesa HP 4210' , 'H' , 458 , '2011-10-01' , 5);
  90. INSERT INTO Recursos VALUES(3000, 'Impresora Fujitsu 2100', 'H' , 88 , '2009-05-01' , 4);
  91. INSERT INTO Recursos VALUES(3001, 'Plotter Acer H56', 'H' , 2359 , '2011-11-01' , 6);
  92.  
  93. CREATE TABLE Usan
  94. (
  95.     CodEmpUsa       INT,
  96.     CodRecUsa       INT,
  97.     FEntUsa         DATE,
  98.     FDevUsa         DATE,
  99.     CONSTRAINT Usa_pk   PRIMARY KEY (CodEmpUsa, CodRecUsa, FEntUsa),
  100.     CONSTRAINT Usa_Emp_fk FOREIGN KEY (CodEmpUsa) REFERENCES Empleados (CodEmp),
  101.     CONSTRAINT Usa_Rec_fk FOREIGN KEY (CodRecUsa) REFERENCES Recursos (CodRec)
  102. );
  103.  
  104. INSERT INTO Usan VALUES(1, 1001, '2012-12-31', '2013-02-15');
  105. INSERT INTO Usan VALUES(1, 1001, '2012-02-30', '2013-01-02');
  106. INSERT INTO Usan VALUES(7, 1001, '2014-08-28', NULL);
  107. INSERT INTO Usan VALUES(15, 1002, '2012-05-13', NULL);
  108. INSERT INTO Usan VALUES(20, 1003, '2012-09-21', NULL);
  109. INSERT INTO Usan VALUES(22, 1004, '2012-10-30', NULL);
  110.  
  111. CREATE TABLE Incidencias
  112. (
  113.     NumInc      INT PRIMARY KEY,
  114.     DesInc      VARCHAR(50),
  115.     FIniInc     DATE,
  116.     FFinInc     DATE,
  117.     CodRecInc   INT,
  118.     CodEmpInc   INT,
  119.     EstInc      VARCHAR (50),
  120.     CONSTRAINT Inc_Rec_fk FOREIGN KEY (CodRecInc) REFERENCES Recursos (CodRec),
  121.     CONSTRAINT Inc_Emp_fk FOREIGN KEY (CodEmpInc) REFERENCES Empleados (CodEmp)
  122. );
  123.  
  124. INSERT INTO Incidencias VALUES(1, 'Fallo arranque', '2012-09-02', '2012-11-06', 1000, 2, 'Arreglado');
  125. INSERT INTO Incidencias VALUES(2, 'Fallo gráfica', '2012-09-28', NULL , 1001, 20, 'Pendiente');
  126. INSERT INTO Incidencias VALUES(3, 'Tinta gastada', '2012-10-20', '2012-10-21' , 3000, 15, 'Arreglado');
  127. INSERT INTO Incidencias VALUES(4, 'Fallo de impresión', '2012-10-15', NULL, 1003, 2, 'Pendiente');
  128. INSERT INTO Incidencias VALUES(5, 'Fallo lectura HD', '2012-11-04', NULL, 1004, 1, 'Pendiente');
  129. INSERT INTO Incidencias VALUES(6, 'Fallo arranque', '2012-11-29', NULL, 1000, 2, 'Pendiente');
  130.  
  131. CREATE TABLE Tareas
  132. (
  133.     NumTar      INT PRIMARY KEY,
  134.     DesTar      VARCHAR(50),
  135.     FecTar      DATE,
  136.     TieTar      INT,
  137.     MatTar      FLOAT,
  138.     NumIncTar   INT,
  139.     CodEmpTar   INT,
  140.     CONSTRAINT Tar_Inc_fk FOREIGN KEY (NumIncTar) REFERENCES Incidencias (NumInc),
  141.     CONSTRAINT Tar_Emp_fk FOREIGN KEY (CodEmpTar) REFERENCES Empleados (CodEmp)
  142. );
  143.  
  144. INSERT INTO Tareas VALUES(1, 'Cambiar alimentador', '2012-11-03', 1, 0, 1, 2);
  145. INSERT INTO Tareas VALUES(2, 'Cambiar la memoria RAM', '2012-11-03', 2, 20, 1, 18);
  146. INSERT INTO Tareas VALUES(3, 'Reintalar Sistema operativo', '2012-11-04', 2, 0, 1, 18);
  147. INSERT INTO Tareas VALUES(4, 'Configurar el equipo', '2012-11-06', 2, 0, 1, 18);
  148. INSERT INTO Tareas VALUES(5, 'Revisar tarjeta gráfica', '2012-09-30', 1 , 0 , 2, 2);
  149. INSERT INTO Tareas VALUES(6, 'Cambiar la tarjeta', '2012-09-30', 1 , 125.45 , 2, 18);
  150. INSERT INTO Tareas VALUES(7, 'Cambiar cartucho de tinta', '2012-10-21', 1, 176, 3, 18);
Última edición por gnzsoloyo; 25/03/2015 a las 10:48 Razón: Popr favor, USAR HIGHLIGHT