manual mysql 2

7/17/2019 Manual Mysql 2

http://slidepdf.com/reader/full/manual-mysql-2 1/6

01. 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'.CREATE TABLE ÀRTICULOS` (ÀRTICULO` 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 (ÀRTICULO`, `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. CadaCOD_FABRICANTE (PK entero corto) tiene su MARCA (UNICO de 20 caracteres) y su

NOMBRE_PAIS (UNICO de 20 caracteres).

CódigoCREATE 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`)


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).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.CREATE TABLE `PEDIDOS` (`NIF` VARCHAR (10) NOT NULL,ÀRTICULO` 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`, ÀRTICULO`, `COD_FAB`),INDEX `FK_PEDIDOS_ARTICULOS` (ÀRTICULO`),



INDEX `FK_PEDIDOS_FABRICANTE` (`COD_FAB`),CONSTRAINT `FK_PEDIDOS_ARTICULOS` FOREIGN KEY (ÀRTICULO`)

REFERENCES ÀRTICULOS` (ÀRTICULO`),CONSTRAINT `FK_PEDIDOS_FABRICANTE` FOREIGN KEY (`COD_FAB`)

REFERENCES `FABRICANTE` (`COD_FAB`)


ENGINE=InnoDB;

02. Crea una tablaVENTAS, a partir de PEDIDOS, que almacena las ventas de artículos que hace cada unade 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.CREATE TABLE VENTAS (SELECT * FROM PEDIDOS);

03. Modifica las tablas PEDIDOS y VENTAS para que incluyan un campo donde almacenar

UNIDADES_PEDIDAS (entero corto) y UNIDADES_VENDIDAS (entero corto) respectivamente. ALTER TABLE VENTAS ADD COLUMN UNIDADES_VENDIDAS TINYINT(10) NOT NULL

AFTER FECHA_PEDIDO;

ALTER TABLE PEDIDOS ADD COLUMN UNIDADES_PEDIDAS TINYINT(10) NOT NULLAFTER FECHA_PEDIDO;

04. Introduce en las tablas los siguientes datos:INSERT INTO ÀRTICULOS` (ÀRTICULO`, `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`, ÀRTICULO`, `COD_FAB`, `PESO`, `CATEGORIA`,`FECHA_PEDIDO`, ÙNIDADES_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`, ÀRTICULO`, `COD_FAB`, `PESO`, `CATEGORIA`,`FECHA_PEDIDO`, ÙNIDADES_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);

05. Realiza las siguientes operaciones:

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



INSERT INTO ARTICULOSSELECT (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 PEDIDOSSELECT 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 ventashaya realizado.[/b][/i][/SIZE]

[code=SQL]INSERT INTO PEDIDOS (`NIF`,ÀRTICULO`,`COD_FAB`,ÙNIDADES_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)

#AINSERT INTO PEDIDOS (`NIF`,ÀRTICULO`,`COD_FAB`,ÙNIDADES_PEDIDAS`) VALUES

('1111-A',ARTICULO,COD_FAB,CATEGORIA,NOW(),20FROM 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:INSERT INTO TIENDASVALUES ('1111-E','MADRID');

INSERT INTO PEDIDOSSELECT '1111-E',ARTICULO,COD_FAB,PESO,CATEGORIA,NOW(),20FROM 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ódigo1. INSERT INTO TIENDAS2. VALUES

3. ('2222-F','SEVILLA'),4. ('3333-G','SEVILLA');5.6. INSERT INTO PEDIDOS7. SELECT '2222-

F',ARTICULOS.ARTICULO,ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(),308. FROM ARTICULOS,FABRICANTES9. WHERE (FABRICANTES.MARCA='CANTIER')10. AND (FABRICANTES.COD_FAB=ARTICULOS.COD_FAB);



11.12. INSERT INTO PEDIDOS13. SELECT '3333-G', ARTICULOS.ARTICULO,

ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(),3014. 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ódigo1. INSERT INTO VENTAS2. SELECT TIENDAS.NIF,

ARTICULOS.ARTICULO,ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGORIA,SYSDATE(), 103. 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ódigo1. ALTER TABLE PEDIDOS DROP PRIMARY KEY, ADD PRIMARY

KEY(NIF,ARTICULO,COD_FAB,FECHA_PEDIDO);

2.3. INSERT INTO PEDIDOS4. SELECT DISTINCT '5555-B',

5. ARTICULOS.ARTICULO,ARTICULOS.COD_FAB,ARTICULOS.PESO,ARTICULOS.CATEGOR

IA,SYSDATE(),106. 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.ARTICULO10. AND A>3011. 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 B2. SET A.PROVINCIA = B.PROVINCIA3. WHERE NIF = 1111

4.

h) Cambia todos los artículos de 'PRIMERA' categoría a 'SEGUNDA' categoría del país 'ITALIA'.Código1. UPDATE ARTICULOS A2. SET A.CATEGORIA = 'SEGUNDA'3. WHERE A.CATEGORIA = 'PRIMERA'

4. AND A.COD_FAB = (SELECT COD_FAB FROM FABRICANTE WHERE NOMBRE_PAISLIKE '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ódigo1. 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 PEDIDOSGROUP BY P.ARTICULO) AS D WHERE AR.EXISTENCIAS<B7. AND

8. AR.ARTICULO=P.ARTICULO AND D.C=AR.ARTICULO GROUP BY P.ARTICULO) AS E9. 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ódigo1. DELETE FROM TIENDAS2. WHERE NIF3. NOT IN(SELECT DISTINCT NIF FROM VENTAS);

4.

k) Elimina los artículos que no hayan tenido ni compras ni ventas.Código1. DELETE FROM ARTICULOS2. 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 PEDIDOS11. 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ódigo1. OPCION B2. UPDATE PEDIDOS AS A,(SELECT NIF FROM PEDIDOS WHERE NIF =5555 ORDER BY

FECHA_PEDIDO DESC LIMIT 1) AS B3. SET UNIDADES_PEDIDAS = UNIDADES_PEDIDAS-14. WHERE

5. A.NIF = B.NIF6.



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ódigo1. CREATE TABLE INFORME2. SELECT FABRICANTES.MARCA,ARTICULOS.ARTICULO, NULL,(S,0)3. FROM FABRICANTES, ARTICULOS

4. LEFT OUTER JOIN5. (SELECT VENTAS.ARTICULO AS B,6. SUM (VENTAS.UNIDADES_VENDIDAS) AS S FROM VENTAS7. GROUP BY VENTAS.ARTICULO) AS A8. ON ARTICULOS.ARTICULO = A.B9. WHERE ARTICULOS.COD_FAB=FABRICANTES.COD_FAB

10.

o) Borra el contenido de la tabla anterior.[

Código

1. TRUNCATE TABLE INFORME2. 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.

manual mysql 2

Documents