Ejercicio bases de datos mysql, manipulación de datos.

[KaRaLLo]

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 Learn !!

Buenas referencias en..
 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 (sql) [Seleccionar] Expandir


CREATE TABLE `ARTICULOS` (
`ARTICULO` TINYINT(2) NOT NULL,
`COD_FAB` TINYINT(2) NOT NULL,
`PESO` TINYINT(2) NULL DEFAULT NULL,
`CATEGORIA` ENUM('PRIMERA','SEGUNDA','TERCERA') NULL DEFAULT NULL,
PRIMARY KEY (`ARTICULO`, `COD_FAB`),
INDEX `FK_ARTICULOS_FABRICANTE` (`COD_FAB`),
CONSTRAINT `FK_ARTICULOS_FABRICANTE` FOREIGN KEY (`COD_FAB`) REFERENCES `FABRICANTE` (`COD_FAB`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;


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 (sql) [Seleccionar] Expandir


CREATE TABLE `FABRICANTE` (
`COD_FAB` TINYINT(2) NOT NULL,
`MARCA` VARCHAR(20) NULL DEFAULT NULL,
`NOMBRE_PAIS` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`COD_FAB`),
UNIQUE INDEX `MARCA` (`MARCA`),
UNIQUE INDEX `NOMBRE_PAIS` (`NOMBRE_PAIS`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;



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 (sql) [Seleccionar] Expandir


CREATE TABLE `TIENDAS` (
`NIF` VARCHAR(10) NOT NULL,
`PROVINCIA` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`NIF`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;


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 (sql) [Seleccionar] Expandir


CREATE TABLE `PEDIDOS` (
`NIF` VARCHAR(10) NOT NULL,
`ARTICULO` TINYINT(2) NOT NULL,
`COD_FAB` TYNYINT(2) NOT NULL,
`PESO` SMALLINT(10) NULL DEFAULT NULL,
`CATEGORIA` ENUM('PRIMERA','SEGUNDA','TERCERA') NULL DEFAULT NULL,
`FECHA_PEDIDO` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`NIF`, `ARTICULO`, `COD_FAB`),
INDEX `FK_PEDIDOS_ARTICULOS` (`ARTICULO`),
INDEX `FK_PEDIDOS_FABRICANTE` (`COD_FAB`),
CONSTRAINT `FK_PEDIDOS_ARTICULOS` FOREIGN KEY (`ARTICULO`) REFERENCES `ARTICULOS` (`ARTICULO`),
CONSTRAINT `FK_PEDIDOS_FABRICANTE` FOREIGN KEY (`COD_FAB`) REFERENCES `FABRICANTE` (`COD_FAB`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;



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 (sql) [Seleccionar] Expandir


CREATE TABLE VENTAS (SELECT * FROM PEDIDOS);



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

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 (sql) [Seleccionar] Expandir


ALTER TABLE VENTAS ADD COLUMN UNIDADES_VENDIDAS TINYINT(10) NOT NULL AFTER FECHA_PEDIDO;
ALTER TABLE PEDIDOS ADD COLUMN UNIDADES_PEDIDAS TINYINT(10) NOT NULL AFTER FECHA_PEDIDO;


4.- Introduce en las tablas los siguientes datos:

Código (sql) [Seleccionar] Expandir


INSERT INTO `ARTICULOS` (`ARTICULO`, `COD_FAB`, `PESO`, `CATEGORIA`) VALUES
(1, 1, 20, 'PRIMERA'),
(2, 1, 45, 'TERCERA'),
(3, 1, 10, 'TERCERA'),
(4, 1, 15, 'PRIMERA'),
(5, 2, 100, 'SEGUNDA'),
(6, 2, 15, 'PRIMERA'),
(7, 2, 30, 'PRIMERA'),
(8, 2, 80, 'TERCERA'),
(9, 3, 25, 'TERCERA'),
(10, 3, 25, 'PRIMERA'),
(11, 3, 70, 'SEGUNDA'),
(12, 3, 90, 'SEGUNDA');

INSERT INTO `FABRICANTE` (`COD_FAB`, `MARCA`, `NOMBRE_PAIS`) VALUES
(1, 'FELVI', 'ITALIA'),
(2, 'CANTIER', 'FRANCIA'),
(3, 'PEDROSA', 'ESPAÑA');

INSERT INTO `PEDIDOS` (`NIF`, `ARTICULO`, `COD_FAB`, `PESO`, `CATEGORIA`, `FECHA_PEDIDO`, `UNIDADES_PEDIDAS`) VALUES
('2222-A', 12, 3, 90, 'SEGUNDA', '0000-00-00 00:00:00', 20),
('4545-C', 1, 1, 20, 'PRIMERA', '2009-03-11 00:00:00', 10),
('5555-B', 1, 1, 20, 'PRIMERA', '2009-03-09 00:00:00', 40),
('5555-B', 2, 1, 45, 'TERCERA', '2009-03-11 00:00:00', 20),
('7788-D', 3, 1, 10, 'TERCERA', '2010-03-12 00:00:00', 40);

INSERT INTO `TIENDAS` (`NIF`, `PROVINCIA`) VALUES
('1111-A', 'SEVILLA'),
('2222-A', 'TOLEDO'),
('4545-C', 'MADRID'),
('5555-B', 'PONTEVEDRA'),
('7788-D', 'TOLEDO'),
('9911-H', 'TOLEDO');


INSERT INTO `VENTAS` (`NIF`, `ARTICULO`, `COD_FAB`, `PESO`, `CATEGORIA`, `FECHA_PEDIDO`, `UNIDADES_VENDIDAS`) VALUES
('2222-A', 12, 3, 90, 'SEGUNDA', '2012-03-16 00:00:00', 10),
('4545-C', 1, 1, 20, 'PRIMERA', '2009-11-04 00:00:00', 5),
('5555-B', 1, 1, 20, 'PRIMERA', '2009-12-03 00:00:00', 20),
('5555-B', 2, 1, 45, 'TERCERA', '2009-03-13 00:00:00', 20),
('7788-D', 3, 1, 10, 'TERCERA', '2011-03-11 00:00:00', 30);





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 (sql) [Seleccionar] Expandir


#primero doy de alta un articulo nuevo
INSERT INTO ARTICULOS
SELECT (13,
(SELECT A.COD_FAB FROM ARTICULOS A, FABRICANTE F WHERE F.NOMBRE_PAIS LIKE'FRANCIA' AND A.COD_FAB=F.COD_FAB LIMIT 1),
NULL,'PRIMERA';

INSERT INTO PEDIDOS
SELECT TIENDAS.NIF, 13, FABRICANTES.COD_FAB, 40, 'PRIMERA', NOW(), 5 FROM TIENDAS, FABRICANTES
WHERE FABRICANTES.NOMBRE_PAIS='FRANCIA';
[/code=sql]

[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]

[code=sql]
INSERT INTO PEDIDOS (`NIF`,`ARTICULO`,`COD_FAB`,`UNIDADES_PEDIDAS`)
VALUES
('1111-A',
(SELECT ARTICULO FROM VENTAS WHERE UNIDADES_VENDIDAS = (SELECT MAX(UNIDADES_VENDIDAS) FROM VENTAS)),
(SELECT COD_FAB FROM ARTICULOS WHERE ARTICULO = (SELECT ARTICULO FROM VENTAS WHERE UNIDADES_VENDIDAS = (SELECT MAX(UNIDADES_VENDIDAS) FROM VENTAS))),
20)


Código (sql) [Seleccionar] Expandir


#A
INSERT INTO PEDIDOS (`NIF`,`ARTICULO`,`COD_FAB`,`UNIDADES_PEDIDAS`)
VALUES
('1111-A',
ARTICULO,
COD_FAB,
CATEGORIA,
NOW(),
20
FROM VENTAS GROUP BY ARTICULO ORDER BY SUM(UNIDADES_VENDIDAS) DESC LIMIT 1;)



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 [Seleccionar] Expandir


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 (sql) [Seleccionar] Expandir


INSERT INTO TIENDAS
VALUES
('2222-F','SEVILLA'),
('3333-G','SEVILLA');

INSERT INTO PEDIDOS
SELECT '2222-F',ARTICULOS.ARTICULO,ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(),30
FROM ARTICULOS,FABRICANTES
WHERE (FABRICANTES.MARCA='CANTIER')
AND (FABRICANTES.COD_FAB=ARTICULOS.COD_FAB);

INSERT INTO PEDIDOS
SELECT '3333-G', ARTICULOS.ARTICULO, ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(),30
FROM ARTICULOS,FABRICANTES WHERE
(FABRICANTES.MARCA='CANTIER')
AND
(FABRICANTES.COD_FAB = ARTICULOS.COD_FAB);


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

Código (sql) [Seleccionar] Expandir


INSERT INTO VENTAS
SELECT TIENDAS.NIF, ARTICULOS.ARTICULO,ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(), 10
FROM (ARTICULOS.CATEGORIA='PRIMERA');


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 (sql) [Seleccionar] Expandir


ALTER TABLE  PEDIDOS DROP PRIMARY KEY,ADD PRIMARY KEY(NIF,ARTICULO,COD_FAB,FECHA_PEDIDO);

INSERT INTO PEDIDOS
SELECT DISTINCT '5555-B',
ARTICULOS.ARTICULO,ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(),10
FROM ARTICULOS,VENTAS,
(SELECT SUM(VENTAS.UNIDADES_VENDIDAS) AS A,
VENTAS.ARTICULO AS B FROM VENTAS GROUP BY VENTAS.ARTICULO) AS C,
WHERE ARTICULO.ARTICULO=VENTAS.ARTICULO
AND A>30
AND C.B = ARTICULOS.ARTICULO;


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

Código (sql) [Seleccionar] Expandir


UPDATE TIENDAS AS A, (SELECT PROVINCIA FROM TIENDAS WHERE NIF='2222-a') AS B
SET A.PROVINCIA = B.PROVINCIA
WHERE NIF = 1111


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

Código (sql) [Seleccionar] Expandir


UPDATE ARTICULOS A
SET A.CATEGORIA = 'SEGUNDA'
WHERE A.CATEGORIA = 'PRIMERA'
AND A.COD_FAB = (SELECT COD_FAB FROM FABRICANTE WHERE NOMBRE_PAIS LIKE 'ITALIA')


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 (sql) [Seleccionar] Expandir


ALTER TABLE ARTICULOS ADD COLUMN EXISTENCIAS INT(10) NULL DEFAULT 10000 AFTER CATEGORIA;

UPDATE PEDIDOS AS P, ARTICULOS AS AR,
(SELECT P.UNIDADES_PEDIDAS FROM PEDIDOS GROUP BY P.ARTICULO),
(SELECT AR.ARTICULO AS A FROM ARTICULOS, PEDIDOS,
(SELECT SUM(P.UNIDADES_PEDIDAS) AS B, P.ARTICULO AS C FROM PEDIDOS GROUP BY P.ARTICULO) AS D WHERE AR.EXISTENCIAS<B
AND
AR.ARTICULO=P.ARTICULO AND D.C=AR.ARTICULO GROUP BY P.ARTICULO) AS E
SET P.UNIDADES_PEDIDAS=(P.UNIDADES_PEDIDAS*(20/100)) WHERE P.ARTICULO=E.A;


j) Elimina aquellas tiendas que no hayan realizado ventas.

Código (sql) [Seleccionar] Expandir


DELETE FROM TIENDAS
WHERE NIF
NOT IN(SELECT DISTINCT NIF FROM VENTAS);


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

Código (sql) [Seleccionar] Expandir


DELETE FROM ARTICULOS
WHERE ARTICULO NOT IN (SELECT DISTINCT ARTICULO FROM VENTAS)
AND
ARTICULO NOT IN (SELECT DISTINCT ARTICULO FROM PEDIDOS);
[/code=sql]

[size=10pt][i][b]l) Borra los pedidos de 'PRIMERA' categoría cuyo país de procedencia sea 'FRANCIA'.
[/b][/i][/size]
[code=sql]
DELETE FROM PEDIDOS
WHERE ARTICULO IN(SELECT ARTICULO FROM ARTICULOS WHERE CATEGORIA = 'PRIMERA')
AND
COD_FAB = ANY(SELECT COD_FAB FROM FABRICANTE WHERE NOMBRE_PAIS = 'FRANCIA')


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

Código (sql) [Seleccionar] Expandir


OPCION B
UPDATE PEDIDOS AS A,(SELECT NIF FROM PEDIDOS WHERE NIF =5555 ORDER BY FECHA_PEDIDO DESC LIMIT 1) AS B
SET UNIDADES_PEDIDAS = UNIDADES_PEDIDAS-1
WHERE
A.NIF = B.NIF






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 (sql) [Seleccionar] Expandir


CREATE TABLE INFORME
SELECT FABRICANTES.MARCA,ARTICULOS.ARTICULO,NULL,(S,0)
FROM FABRICANTES, ARTICULOS
LEFT OUTER JOIN
(SELECT VENTAS.ARTICULO AS B,
SUM(VENTAS.UNIDADES_VENDIDAS) AS S FROM VENTAS
GROUP BY VENTAS.ARTICULO) AS A
ON ARTICULOS.ARTICULO = A.B
WHERE ARTICULOS.COD_FAB=FABRICANTES.COD_FAB


o) Borra el contenido de la tabla anterior.
[

Código (sql) [Seleccionar] Expandir


TRUNCATE TABLE INFORME
code]


[size=10pt][i][b]p) Elimina la tabla anterior.[/b][/i][/size]  

[code=sql]
DROP TABLE INFORME
[/code][/code][/code]