EJERCICIOS SQL PRACTICA: FUNCIONES AGREGACIÓN
Almacen(Nro, Nombre, Responsable)
Articulo(CodArt, Descripcion, precio)
Material(CodMat, Descripcion)
Proveedor(CodProv, Nombre, Domicilio, CodCiudad)
Ciudad(CodCiudad, Nombre)
Contiene(Cod_Contiene, Nro, CodArt)
Compuesto_por(Cod_Composicion, CodArt, CodMat)
Provisto_por(Cod_Provisto, CodMat, CodProv)
Referencias: Negrita y con color azul -> PK
Subrayado con guiones-> FK
Generar las sentencias necesarias para modelar la base de datos
con información.
# Creo las tablas
CREATE TABLE ALMACEN(
Nro INT(5) PRIMARY KEY,
Nombre VARCHAR(20) NOT NULL,
Responsable VARCHAR(50) NOT NULL);
CREATE TABLE ARTICULO(
CodArt INT(5) PRIMARY KEY,
Descripcion VARCHAR (50) NOT NULL,
Precio DOUBLE);
CREATE TABLE MATERIAL(
CodMat INT(6) PRIMARY KEY,
Descripcion VARCHAR(100) NOT NULL);
CREATE TABLE CIUDAD(
CodCiudad INT(2) PRIMARY KEY,
Nombre VARCHAR(100) NOT NULL);
CREATE TABLE PROVEEDOR(
CodProv INT(5) PRIMARY KEY,
Nombre VARCHAR(20) NOT NULL,
Domicilio VARCHAR(100),
CodCiudad INT(2) NOT NULL,
FOREIGN KEY (CodCiudad) REFERENCES Ciudad (CodCiudad));
CREATE TABLE CONTIENE(
Cod_Contiene INT(3) PRIMARY KEY AUTO_INCREMENT,
Nro INT(5) NOT NULL,
CodArt INT(5) NOT NULL,
FOREIGN KEY (Nro) REFERENCES ALMACEN (Nro),
FOREIGN KEY (CodArt) REFERENCES ARTICULO (CodArt));
CREATE TABLE COMPUESTO_POR(
Cod_Composicion INT(3) PRIMARY KEY AUTO_INCREMENT,
CodArt INT(5) NOT NULL,
CodMat INT(6) NOT NULL,
FOREIGN KEY (CodArt) REFERENCES ARTICULO (CodArt),
FOREIGN KEY (CodMat) REFERENCES MATERIAL (CodMat));
CREATE TABLE PROVISTO_POR(
Cod_Provisto INT(3) PRIMARY KEY AUTO_INCREMENT,
CodMat INT(6) NOT NULL,
CodProv INT(5) NOT NULL,
FOREIGN KEY (CodMat) REFERENCES MATERIAL (CodMat),
FOREIGN KEY (CodProv) REFERENCES PROVEEDOR (CodProv));
# Inserto Datos
INSERT INTO ALMACEN (Nro, Nombre, Responsable)
VALUES (001, 'La Original', 'Alfredo'),
(002, 'Galpon', 'Esteban'),
(003, 'Almacen de Don Juan', 'Juan'),
(004, 'La Tiendita', 'Roberto');
INSERT INTO ARTICULO (CodArt, Descripcion, Precio)
VALUES (001, 'Pan', 130.70),
(002, 'Facturas', 300.00),
(003, 'Cheese Cake', 450.87),
(004, 'Pasta Frola', 278.90);
INSERT INTO MATERIAL (CodMat, Descripcion)
VALUES (001, 'Aceite'),
(002, 'Harina'),
(003, 'Levadura'),
(004, 'Huevo'),
(005, 'Azucar'),
(006, 'Sal'),
(007, 'Agua');
INSERT INTO CIUDAD(CodCiudad, Nombre)
VALUES (1, 'La Plata'),
(2, 'Capital Federal'),
(3, 'Ramos Mejia'),
(4, 'La Matanza');
INSERT INTO PROVEEDOR (CodProv, Nombre, Domicilio,
CodCiudad)
VALUES(1, 'Arcor', 'Ayacucho 1234', 1),
(2, 'Molinos', 'Yatay 456', 4),
(3, 'Ledesma', 'Mario Bravo 987', 1),
(4, 'Marolio', 'Potosi 098', 2),
(5, 'Glaciar', 'Sarmiento 555', 3),
(6, ‘Johnson’, ‘Potosi 123’, 1);
INSERT INTO CONTIENE (Nro, CodArt)
VALUES (001, 001),
(001, 002),
(001, 003),
(001, 004),
(002, 003),
(002, 004),
(003, 001),
(004, 002);
INSERT INTO COMPUESTO_POR (CodArt, CodMat)
VALUES(001, 001),
(001, 002),
(001, 003),
(002, 002),
(002, 005),
(002, 007),
(003, 001),
(003, 002),
(003, 006),
(004, 007);
INSERT INTO PROVISTO_POR(CodMat, CodProv)
VALUES (001, 1),
(002, 3),
(003, 5),
(004, 4),
(005, 2),
(006, 2),
(007, 5);
CONSULTAS
#1) Listar los nombres de aquellos proveedores que no proveen
ningún material.
SELECT [Link]
FROM PROVEEDOR P
WHERE NOT EXISTS
(SELECT 1
FROM PROVISTO_POR PP
WHERE [Link] = [Link]);
#2) Listar los códigos y descripción de los materiales que provea
el proveedor 2 y no los provea el proveedor 5
Con Exists:
SELECT [Link] COD_MAT, [Link] DESC_MATERIAL
FROM MATERIAL M
WHERE EXISTS
(SELECT 1
FROM PROVISTO_POR PP
WHERE [Link]= 2 and [Link] = [Link])
and NOT EXISTS
(SELECT 1
FROM PROVISTO_POR PP
WHERE [Link]= 5 and [Link] = [Link]);
Con IN:
SELECT [Link] COD_MATERIAL, [Link]
DESCRIPCION_MATERIAL
FROM MATERIAL M
WHERE [Link] IN(
SELECT [Link]
FROM PROVISTO_POR PP
WHERE [Link] = 2)
AND [Link] NOT IN(
SELECT [Link]
FROM PROVISTO_POR PP
WHERE [Link] = 5);
OTRA FORMA DE HACERLO:
SELECT [Link], [Link]
FROM PROVISTO_POR PP1 JOIN MATERIAL M ON [Link] =
[Link]
WHERE [Link] = 2 AND [Link] NOT IN (SELECT
[Link]
FROM PROVISTO_POR PP2
WHERE [Link] = 5);
#3) Listar número y nombre de almacenes que contienen los
artículos de descripción ‘Pan’ y los de descripción ‘Facturas’
(ambos).
SELECT [Link] Nro_Almacen, [Link] Nombre_Almacen
FROM ALMACEN A
WHERE [Link] IN (SELECT [Link]
FROM ARTICULO AR JOIN CONTIENE C ON [Link] = [Link]
WHERE [Link] = 'Pan')
AND [Link] IN (SELECT [Link]
FROM ARTICULO AR JOIN CONTIENE C ON [Link] = [Link]
WHERE [Link] = 'Facturas');
OTRA FORMA DE HACERLO:
SELECT [Link] Nro_Almacen, [Link] Nombre_Almacen
FROM ALMACEN A
WHERE EXISTS (SELECT [Link]
FROM ARTICULO AR JOIN CONTIENE C ON [Link] = [Link]
WHERE [Link] = 'Pan' AND [Link] = [Link])
AND EXISTS(
SELECT [Link]
FROM ARTICULO AR JOIN CONTIENE C ON [Link] = [Link]
WHERE [Link] = 'Facturas' AND [Link] = [Link]);
#4) Listar la descripción de artículos compuestos por todos los
materiales
SELECT [Link]
from ARTICULO AR JOIN COMPUESTO_POR CP
ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(*) = (SELECT COUNT(*) FROM MATERIAL);
OTRA FORMA DE HACERLO:
SELECT [Link]
from ARTICULO AR
WHERE NOT EXISTS
(SELECT 1 FROM MATERIAL M
WHERE NOT EXISTS (SELECT 1 FROM COMPUESTO_POR CP
WHERE [Link] = [Link] AND [Link] = [Link]));
#5) Hallar los códigos y nombres de los proveedores que proveen
al menos un material que se usa en algún artículo cuyo precio es
mayor a $300
SELECT DISTINCT [Link], [Link] NOMBRE_PROV
FROM PROVISTO_POR PP JOIN PROVEEDOR P ON [Link] =
[Link]
WHERE [Link] IN(
SELECT [Link]
FROM COMPUESTO_POR CP
WHERE [Link] IN
(SELECT [Link]
FROM ARTICULO AR
WHERE [Link] > 300))
ORDER BY [Link];
OTRA FORMA DE HACERLO:
SELECT [Link], [Link]
FROM PROVEEDOR P
WHERE EXISTS
(SELECT 1
FROM PROVISTO_POR PP JOIN COMPUESTO_POR CP ON
[Link] = [Link]
JOIN ARTICULO A ON [Link] = [Link]
WHERE [Link] > 300
AND [Link] = [Link]);
#6) Listar la descripción de los artículos de mayor precio
SELECT [Link]
FROM ARTICULO AR
WHERE [Link] = (SELECT MAX([Link]) MAX_PRECIO FROM
ARTICULO AR);
