Foro de elhacker.net

Programación => Bases de Datos => Mensaje iniciado por: KaRaLLo en 14 Mayo 2012, 21:27 pm


Título: Ejercicio bases de datos mysql, manipulación de datos.
Publicado por: KaRaLLo en 14 Mayo 2012, 21:27 pm
Bueno, ahí va otro boletín esta vez sobre manipulación de datos, es probable que encontréis errores pero ya digo que no está corregido :D Learn !!
 
Buenas referencias en..
 http://www.java2s.com/Code/SQL/CatalogSQL.htm (http://www.java2s.com/Code/SQL/CatalogSQL.htm)

1.- Un almacén de distribución de artículos desea mantener información sobre las ventas hechas por las tiendas que compran al almacén. Dispone de las siguientes tablas para mantener esta información:

ARTICULOS: almacena cada uno de los artículos que el almacén puede abastecer a las tiendas. Cada artículo viene determinado por las columnas: ARTICULO (PK entero corto), COD_FABRICANTE (PK FK entero corto), PESO (entero corto) y CATEGORIA (ENUM). La categoría puede ser 'PRIMERA', 'SEGUNDA' o 'TERCERA'.

Código
  1. CREATE TABLE `ARTICULOS` (
  2. `ARTICULO` TINYINT(2) NOT NULL,
  3. `COD_FAB` TINYINT(2) NOT NULL,
  4. `PESO` TINYINT(2) NULL DEFAULT NULL,
  5. `CATEGORIA` ENUM('PRIMERA','SEGUNDA','TERCERA') NULL DEFAULT NULL,
  6. PRIMARY KEY (`ARTICULO`, `COD_FAB`),
  7. INDEX `FK_ARTICULOS_FABRICANTE` (`COD_FAB`),
  8. CONSTRAINT `FK_ARTICULOS_FABRICANTE` FOREIGN KEY (`COD_FAB`) REFERENCES `FABRICANTE` (`COD_FAB`)
  9. )
  10. COLLATE='utf8_general_ci'
  11. ENGINE=InnoDB;
  12.  

FABRICANTES: contiene los países de origen de los fabricantes de artículos. Cada COD_FABRICANTE (PK entero corto) tiene su MARCA (UNICO de 20 caracteres) y su NOMBRE_PAIS (UNICO de 20 caracteres).

Código
  1. CREATE TABLE `FABRICANTE` (
  2. `COD_FAB` TINYINT(2) NOT NULL,
  3. `MARCA` VARCHAR(20) NULL DEFAULT NULL,
  4. `NOMBRE_PAIS` VARCHAR(20) NULL DEFAULT NULL,
  5. PRIMARY KEY (`COD_FAB`),
  6. UNIQUE INDEX `MARCA` (`MARCA`),
  7. UNIQUE INDEX `NOMBRE_PAIS` (`NOMBRE_PAIS`)
  8. )
  9. COLLATE='utf8_general_ci'
  10. ENGINE=InnoDB;
  11.  


TIENDAS: almacena los datos de las tiendas que venden artículos. Cada tienda se identifica por su NIF (PK entero largo) y almacena además su PROVINCIA (20 caracteres).


Código
  1. CREATE TABLE `TIENDAS` (
  2. `NIF` VARCHAR(10) NOT NULL,
  3. `PROVINCIA` VARCHAR(20) NULL DEFAULT NULL,
  4. PRIMARY KEY (`NIF`)
  5. )
  6. COLLATE='utf8_general_ci'
  7. ENGINE=InnoDB;
  8.  

PEDIDOS: son los pedidos que realizan las tiendas al almacén. Cada pedido almacenará: NIF (PK FK entero largo), ARTICULO (PK FK entero corto), COD_FABRICANTE (PK FK entero corto), PESO (entero corto), CATEGORIA (ENUM 'PRIMERA','SEGUNDA','TERCERA') y FECHA_PEDIDO (TIMESTAMP con valor por defecto FECHA ACTUAL). Cada fila de la tabla representa un pedido.


Código
  1. CREATE TABLE `PEDIDOS` (
  2. `NIF` VARCHAR(10) NOT NULL,
  3. `ARTICULO` TINYINT(2) NOT NULL,
  4. `COD_FAB` TYNYINT(2) NOT NULL,
  5. `PESO` SMALLINT(10) NULL DEFAULT NULL,
  6. `CATEGORIA` ENUM('PRIMERA','SEGUNDA','TERCERA') NULL DEFAULT NULL,
  7. `FECHA_PEDIDO` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  8. PRIMARY KEY (`NIF`, `ARTICULO`, `COD_FAB`),
  9. INDEX `FK_PEDIDOS_ARTICULOS` (`ARTICULO`),
  10. INDEX `FK_PEDIDOS_FABRICANTE` (`COD_FAB`),
  11. CONSTRAINT `FK_PEDIDOS_ARTICULOS` FOREIGN KEY (`ARTICULO`) REFERENCES `ARTICULOS` (`ARTICULO`),
  12. CONSTRAINT `FK_PEDIDOS_FABRICANTE` FOREIGN KEY (`COD_FAB`) REFERENCES `FABRICANTE` (`COD_FAB`)
  13. )
  14. COLLATE='utf8_general_ci'
  15. ENGINE=InnoDB;
  16.  


2.- Crea una tabla VENTAS, a partir de PEDIDOS, que almacena las ventas de artículos que hace cada una de las tiendas. Cada venta se identifica por: NIF (PK FK entero largo), ARTICULO (PK FK entero corto), COD_FABRICANTE (PK FK entero corto), PESO (entero corto), CATEGORIA (ENUM 'PRIMERA','SEGUNDA','TERCERA') y FECHA_PEDIDO (TIMESTAMP con valor por defecto FECHA ACTUAL). Cada fila de la tabla representa una venta.

Código
  1. CREATE TABLE VENTAS (SELECT * FROM PEDIDOS);
  2.  


*************************************

3.- Modifica las tablas PEDIDOS y VENTAS para que incluyan un campo donde almacenar UNIDADES_PEDIDAS (entero corto) y UNIDADES_VENDIDAS (entero corto) respectivamente.


Código
  1. ALTER TABLE VENTAS ADD COLUMN UNIDADES_VENDIDAS TINYINT(10) NOT NULL AFTER FECHA_PEDIDO;
  2. ALTER TABLE PEDIDOS ADD COLUMN UNIDADES_PEDIDAS TINYINT(10) NOT NULL AFTER FECHA_PEDIDO;
  3.  

4.- Introduce en las tablas los siguientes datos:

Código
  1. INSERT INTO `ARTICULOS` (`ARTICULO`, `COD_FAB`, `PESO`, `CATEGORIA`) VALUES
  2. (1, 1, 20, 'PRIMERA'),
  3. (2, 1, 45, 'TERCERA'),
  4. (3, 1, 10, 'TERCERA'),
  5. (4, 1, 15, 'PRIMERA'),
  6. (5, 2, 100, 'SEGUNDA'),
  7. (6, 2, 15, 'PRIMERA'),
  8. (7, 2, 30, 'PRIMERA'),
  9. (8, 2, 80, 'TERCERA'),
  10. (9, 3, 25, 'TERCERA'),
  11. (10, 3, 25, 'PRIMERA'),
  12. (11, 3, 70, 'SEGUNDA'),
  13. (12, 3, 90, 'SEGUNDA');
  14.  
  15. INSERT INTO `FABRICANTE` (`COD_FAB`, `MARCA`, `NOMBRE_PAIS`) VALUES
  16. (1, 'FELVI', 'ITALIA'),
  17. (2, 'CANTIER', 'FRANCIA'),
  18. (3, 'PEDROSA', 'ESPAÑA');
  19.  
  20. INSERT INTO `PEDIDOS` (`NIF`, `ARTICULO`, `COD_FAB`, `PESO`, `CATEGORIA`, `FECHA_PEDIDO`, `UNIDADES_PEDIDAS`) VALUES
  21. ('2222-A', 12, 3, 90, 'SEGUNDA', '0000-00-00 00:00:00', 20),
  22. ('4545-C', 1, 1, 20, 'PRIMERA', '2009-03-11 00:00:00', 10),
  23. ('5555-B', 1, 1, 20, 'PRIMERA', '2009-03-09 00:00:00', 40),
  24. ('5555-B', 2, 1, 45, 'TERCERA', '2009-03-11 00:00:00', 20),
  25. ('7788-D', 3, 1, 10, 'TERCERA', '2010-03-12 00:00:00', 40);
  26.  
  27. INSERT INTO `TIENDAS` (`NIF`, `PROVINCIA`) VALUES
  28. ('1111-A', 'SEVILLA'),
  29. ('2222-A', 'TOLEDO'),
  30. ('4545-C', 'MADRID'),
  31. ('5555-B', 'PONTEVEDRA'),
  32. ('7788-D', 'TOLEDO'),
  33. ('9911-H', 'TOLEDO');
  34.  
  35.  
  36. INSERT INTO `VENTAS` (`NIF`, `ARTICULO`, `COD_FAB`, `PESO`, `CATEGORIA`, `FECHA_PEDIDO`, `UNIDADES_VENDIDAS`) VALUES
  37. ('2222-A', 12, 3, 90, 'SEGUNDA', '2012-03-16 00:00:00', 10),
  38. ('4545-C', 1, 1, 20, 'PRIMERA', '2009-11-04 00:00:00', 5),
  39. ('5555-B', 1, 1, 20, 'PRIMERA', '2009-12-03 00:00:00', 20),
  40. ('5555-B', 2, 1, 45, 'TERCERA', '2009-03-13 00:00:00', 20),
  41. ('7788-D', 3, 1, 10, 'TERCERA', '2011-03-11 00:00:00', 30);
  42.  




5.- Realiza las siguientes operaciones:

a) Da de alta un nuevo artículo de 'PRIMERA' categoría para los fabricantes de 'FRANCIA' y abastece con 5 unidades de ese artículo a todas las tiendas y en la fecha de hoy.

Código
  1. #primero doy de alta un articulo nuevo
  2. INSERT INTO ARTICULOS
  3. SELECT (13,
  4. (SELECT A.COD_FAB FROM ARTICULOS A, FABRICANTE F WHERE F.NOMBRE_PAIS LIKE'FRANCIA' AND A.COD_FAB=F.COD_FAB LIMIT 1),
  5. NULL,'PRIMERA';
  6.  
  7. INSERT INTO PEDIDOS
  8. SELECT TIENDAS.NIF, 13, FABRICANTES.COD_FAB, 40, 'PRIMERA', NOW(), 5 FROM TIENDAS, FABRICANTES 
  9. WHERE FABRICANTES.NOMBRE_PAIS='FRANCIA';
  10. [/code=SQL]
  11.  
  12. [SIZE=10pt][i][b]b) Inserta un pedido de 20 unidades en la tienda '1111-A' con el artículo que mayor número de ventas haya realizado.[/b][/i][/SIZE]
  13.  
  14. [code=SQL]
  15. INSERT INTO PEDIDOS (`NIF`,`ARTICULO`,`COD_FAB`,`UNIDADES_PEDIDAS`)
  16. VALUES
  17. ('1111-A',
  18. (SELECT ARTICULO FROM VENTAS WHERE UNIDADES_VENDIDAS = (SELECT MAX(UNIDADES_VENDIDAS) FROM VENTAS)),
  19. (SELECT COD_FAB FROM ARTICULOS WHERE ARTICULO = (SELECT ARTICULO FROM VENTAS WHERE UNIDADES_VENDIDAS = (SELECT MAX(UNIDADES_VENDIDAS) FROM VENTAS))),
  20. 20)
  21.  

Código
  1. #A
  2. INSERT INTO PEDIDOS (`NIF`,`ARTICULO`,`COD_FAB`,`UNIDADES_PEDIDAS`)
  3. VALUES
  4. ('1111-A',
  5. ARTICULO,
  6. COD_FAB,
  7. CATEGORIA,
  8. NOW(),
  9. 20
  10. FROM VENTAS GROUP BY ARTICULO ORDER BY SUM(UNIDADES_VENDIDAS) DESC LIMIT 1;)
  11.  


c) Da de alta una tienda en la provincia de 'MADRID' y abastécela con 20 unidades de cada uno de los artículos existentes.

Código:
INSERT INTO TIENDAS
VALUES ('1111-E','MADRID');

INSERT INTO PEDIDOS
SELECT '1111-E',ARTICULO,COD_FAB,PESO,CATEGORIA,NOW(),20
FROM ARTICULOS;

d) Da de alta dos tiendas en la provincia de 'SEVILLA' y abastécelas con 30 unidades de artículos de la marca de fabricante 'CANTIER'.

Código
  1. INSERT INTO TIENDAS 
  2. VALUES 
  3. ('2222-F','SEVILLA'),
  4. ('3333-G','SEVILLA');
  5.  
  6. INSERT INTO PEDIDOS
  7. SELECT '2222-F',ARTICULOS.ARTICULO,ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(),30
  8. FROM ARTICULOS,FABRICANTES
  9. WHERE (FABRICANTES.MARCA='CANTIER')
  10. AND (FABRICANTES.COD_FAB=ARTICULOS.COD_FAB);
  11.  
  12. INSERT INTO PEDIDOS 
  13. SELECT '3333-G', ARTICULOS.ARTICULO, ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(),30
  14. FROM ARTICULOS,FABRICANTES WHERE
  15. (FABRICANTES.MARCA='CANTIER')
  16. AND 
  17. (FABRICANTES.COD_FAB = ARTICULOS.COD_FAB);
  18.  

e) Realiza una venta para todas las tiendas de 'TOLEDO' de 10 unidades en los artículos de 'PRIMERA' categoría.

Código
  1. INSERT INTO VENTAS
  2. SELECT TIENDAS.NIF, ARTICULOS.ARTICULO,ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(), 10
  3. FROM (ARTICULOS.CATEGORIA='PRIMERA');
  4.  

f) Para aquellos artículos de los que se hayan vendido más de 30 unidades, realiza un pedido de 10 unidades para la tienda con NIF '5555-B' con la fecha actual.


Código
  1. ALTER TABLE  PEDIDOS DROP PRIMARY KEY,ADD PRIMARY KEY(NIF,ARTICULO,COD_FAB,FECHA_PEDIDO);
  2.  
  3. INSERT INTO PEDIDOS
  4. SELECT DISTINCT '5555-B',
  5. ARTICULOS.ARTICULO,ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(),10 
  6. FROM ARTICULOS,VENTAS,
  7. (SELECT SUM(VENTAS.UNIDADES_VENDIDAS) AS A,
  8.  VENTAS.ARTICULO AS B FROM VENTAS GROUP BY VENTAS.ARTICULO) AS C,
  9. WHERE ARTICULO.ARTICULO=VENTAS.ARTICULO
  10. AND A>30
  11. AND C.B = ARTICULOS.ARTICULO;
  12.  

g) Cambia los datos de la tienda con NIF '1111-A' igualándolos a los de la tienda con NIF '2222-A'.

Código
  1. UPDATE TIENDAS AS A, (SELECT PROVINCIA FROM TIENDAS WHERE NIF='2222-a') AS B
  2. SET A.PROVINCIA = B.PROVINCIA
  3. WHERE NIF = 1111
  4.  

h) Cambia todos los artículos de 'PRIMERA' categoría a 'SEGUNDA' categoría del país 'ITALIA'.

Código
  1. UPDATE ARTICULOS A
  2. SET A.CATEGORIA = 'SEGUNDA'
  3. WHERE A.CATEGORIA = 'PRIMERA'
  4. AND A.COD_FAB = (SELECT COD_FAB FROM FABRICANTE WHERE NOMBRE_PAIS LIKE 'ITALIA')
  5.  

i) Modifica aquellos pedidos en los que la cantidad pedida sea superior a las existencias del artículo, asignando el 20 por 100 de las existencias a la cantidad que se ha pedido.

Código
  1. ALTER TABLE ARTICULOS ADD COLUMN EXISTENCIAS INT(10) NULL DEFAULT 10000 AFTER CATEGORIA;
  2.  
  3. UPDATE PEDIDOS AS P, ARTICULOS AS AR, 
  4. (SELECT P.UNIDADES_PEDIDAS FROM PEDIDOS GROUP BY P.ARTICULO), 
  5. (SELECT AR.ARTICULO AS A FROM ARTICULOS, PEDIDOS, 
  6. (SELECT SUM(P.UNIDADES_PEDIDAS) AS B, P.ARTICULO AS C FROM PEDIDOS GROUP BY P.ARTICULO) AS D WHERE AR.EXISTENCIAS<B 
  7. AND
  8. AR.ARTICULO=P.ARTICULO AND D.C=AR.ARTICULO GROUP BY P.ARTICULO) AS E
  9. SET P.UNIDADES_PEDIDAS=(P.UNIDADES_PEDIDAS*(20/100)) WHERE P.ARTICULO=E.A;
  10.  

j) Elimina aquellas tiendas que no hayan realizado ventas.

Código
  1. DELETE FROM TIENDAS
  2. WHERE NIF 
  3. NOT IN(SELECT DISTINCT NIF FROM VENTAS);
  4.  

k) Elimina los artículos que no hayan tenido ni compras ni ventas.

Código
  1. DELETE FROM ARTICULOS
  2. WHERE ARTICULO NOT IN (SELECT DISTINCT ARTICULO FROM VENTAS)
  3. AND
  4. ARTICULO NOT IN (SELECT DISTINCT ARTICULO FROM PEDIDOS);
  5. [/code=SQL]
  6.  
  7. [SIZE=10pt][i][b]l) Borra los pedidos de 'PRIMERA' categoría cuyo país de procedencia sea 'FRANCIA'.
  8. [/b][/i][/SIZE]
  9. [code=SQL]
  10. DELETE FROM PEDIDOS
  11. WHERE ARTICULO IN(SELECT ARTICULO FROM ARTICULOS WHERE CATEGORIA = 'PRIMERA')
  12. AND
  13. COD_FAB = ANY(SELECT COD_FAB FROM FABRICANTE WHERE NOMBRE_PAIS = 'FRANCIA')
  14.  

m) Resta uno a las unidades de los últimos pedidos de la tienda con NIF '5555-B'.

Código
  1. OPCION B
  2. UPDATE PEDIDOS AS A,(SELECT NIF FROM PEDIDOS WHERE NIF =5555 ORDER BY FECHA_PEDIDO DESC LIMIT 1) AS B
  3. SET UNIDADES_PEDIDAS = UNIDADES_PEDIDAS-1
  4. WHERE
  5. A.NIF = B.NIF
  6.  





n) Crea una tabla INFORME, que contenga en tres columnas las marcas, artículo y la suma total de unidades vendidas de cada artículo.

Código
  1. CREATE TABLE INFORME
  2. SELECT FABRICANTES.MARCA,ARTICULOS.ARTICULO,NULL,(S,0)
  3. FROM FABRICANTES, ARTICULOS
  4. LEFT OUTER JOIN
  5. (SELECT VENTAS.ARTICULO AS B,
  6. SUM(VENTAS.UNIDADES_VENDIDAS) AS S FROM VENTAS
  7. GROUP BY VENTAS.ARTICULO) AS A
  8. ON ARTICULOS.ARTICULO = A.B
  9. WHERE ARTICULOS.COD_FAB=FABRICANTES.COD_FAB
  10.  

o) Borra el contenido de la tabla anterior.
[
Código
  1. TRUNCATE TABLE INFORME
  2. code]
  3.  
  4.  
  5. [SIZE=10pt][i][b]p) Elimina la tabla anterior.[/b][/i][/SIZE]  
  6.  
  7. [code=SQL]
  8. DROP TABLE INFORME 
  9.  
[/code][/code][/code]


Powered by SMF 1.1.21 | SMF © 2006-2008, Simple Machines