DIAGRAMA DE LA BASE DE DATOS DISTRIBUIDORA
PROVEEDORES Cod_Prov Nom_Prov Cat_Prov Ciudad_prov
COMPONENTES Cod_Comp
ENVIOS Cod_Prov
Nom_Comp
Cod_Comp
Color_Comp
Cod_Art
Peso_Comp
Cantidad
Ciudad_Comp
ARTICULOS Cod_Art Nom_Art Ciudad_Art
CONSULTAS A LA BASE DE DATOS CREATE DATABASE Distribuidora GO USE Distribuidora GO SELECT * FROM dbo. dbo.PROVEEDORES
Cod_Prov P1 P2 P3 P4 P5
Nom_Prov Nom_P rov CARLOS JUAN JOSE INMA EVA
Cat_Prov 20 10 30 20 30
Ciudad_prov SEVILLA MADRID SEVILLA SEVILLA CACERES
SELECT * FROM dbo.COMPONENTES
Cod_Comp C1 C2 C3 C4 C5 C6
Nom_Comp X3A B85 C4B C4B VT8 C30
Color_Comp ROJO VERDE AZUL ROJO AZUL ROJO
Peso_Comp 12 17 17 14 12 19
Ciudad_Comp SEVILLA MADRID MALAGA SEVILLA MADRID SEVILLA
SELECT * FROM ARTICULOS
Cod_Art T1 T2 T3 T4 T5 T6 T7
Nom_Art CLASIFICADORA PERFORADORA LECTORA CONSOLA MEZCLADORA TERMINAL CINTA
Ciudad_Art MADRID MALAGA CACERES CACERES SEVILLA BARCELONA SEVILLA
SELECT * FROM ENVIOS
Cod_Prov P1 P1 P2 P2 P2 P2 P2 P2 P2 P2 P3 P3 P4 P4 P5 P5 P5 P5 P5
Cod_Comp C1 C1 C3 C3 C3 C3 C3 C3 C3 C5 C3 C4 C6 C2 C2 C2 C5 C5 C6
Cod_Art T1 T4 T1 T2 T3 T4 T5 T6 T7 T2 T1 T2 T3 T7 T2 T4 T4 T7 T2
Cantidad 200 700 400 200 200 500 600 400 800 100 200 500 300 300 200 100 500 100 200
P5 P5 P5 P5 P5
C1 C3 C4 C5 C6
T4 T4 T4 T5 T4
100 200 800 400 500
Obtener todos los detalles de todos los articulos de la ciudad de CACERES SELECT * FROM ARTICULOS WHERE Ciudad_Art = 'CACERES' GO 2)-- Obtener todos los valores de Cod_Prov para los proveedores que abastecen el Articulo T 1 SELECT DISTINCT Cod_Prov FROM ENVIOS WHERE Cod_Art = 'T1' GO 3)-- Obtener la lista de pares de atributos (color,ciudad ) de la tabla componentes eliminando los pares duplicados SELECT DISTINCT Color_Comp,Ciudad_Comp FROM COMPONENTES GO 4)-- Obtener de la tabla Articulos los valores de Cod_Art y Ciudad_Art donde el nombre de la ciudad acaba en D o contiene al menos una E SELECT Cod_Art,Ciudad_Art FROM ARTICULOS WHERE Ciudad_Art LIKE '%D' OR Ciudad_Art LIKE '%E%' GO 5)-- Obtener los valores de Cod_Prov para los proveedores que suministran para el articulo T1 el componente C 1 SELECT Cod_Prov FROM ENVIOS WHERE Cod_Art = 'T1' AND Cod_Comp = 'C1' GO 6)-- Obtener los valores de Nom_Art en orden alfabetico para los articulos abastecidos por el proveedor P 1 SELECT Nom_Art FROM ARTICULOS a,ENVIOS e WHERE e.Cod_Prov = 'P1' AND e.Cod_Art = a.Cod_Art ORDER BY Nom_Art GO -- otra sentencia SELECT DISTINCT Nom_Art FROM ARTICULOS NATURAL JOIN ENVIOS ON Cod_Prov = 'P1' ORDER BY Nom_Art GO 7)-- Obtener los valores de Cod_Comp para los componentes suministrados para cualquier articulo de Madrid SELECT DISTINCT Cod_Comp FROM ENVIOS WHERE Cod_Art IN ( SELECT Cod_Art FROM ARTICULOS WHERE Ciudad_Art = 'MADRID' ) GO 8)-- Obtener todos los valores de Cod_Comp de los componentes tales que ningun otro componente tenga un valor de peso inferior SELECT Cod_Comp FROM COMPONENTES WHERE Peso_Comp = ( SELECT MIN (Peso_Comp )FROM COMPONENTES ) GO 9)-- Obtener los valores de Cod_Prov para los proveedores que suministren los articulos T 1 y T2 SELECT Cod_Prov FROM ENVIOS WHERE Cod_Art = 'T1' INTERSECT SELECT Cod_Prov FROM ENVIOS WHERE Cod_Art = 'T2' GO 1)--
Obtener los valores de Cod_Prov para los proveedores que suministran para un articulos de Sevilla o Madrid un componente ROJO SELECT Cod_Prov FROM ENVIOS e,COMPONENTES c,ARTICULOS a WHERE e.Cod_Comp = c.Cod_Comp AND e.Cod_Art = a.Cod_Art AND c.Color_Comp = 'ROJO' AND a.Ciudad_Art IN ('SEVILLA','MADRID' ) GO 11)-- Obtener,mediante subconsultas, los valores de Cod_Comp para los componentes suministrados para algun articulo de Sevilla por un proveedor de Sevilla SELECT Cod_Comp FROM ENVIOS WHERE Cod_Art IN ( SELECT Cod_Art FROM ARTICULOS WHERE Ciudad_Art ='SEVILLA' ) AND Cod_Prov IN (SELECT Cod_Prov FROM PROVEEDORES WHERE Ciudad_prov ='SEVILLA' ) GO 12)-- Obtener los valores de Cod_Art para los articulos que usan al menos un componente que se pueden obtener con el proveedor P 1 SELECT DISTINCT Cod_Art FROM ENVIOS WHERE Cod_Comp IN ( SELECT DISTINCT Cod_Comp FROM ENVIOS WHERE Cod_Prov ='P1') GO 13)-- Obtener todas las ternas (ciudad,Cod_Comp,ciudad ) tales que un proveedor de la primera ciudad suministre el componente especificado para un articulo montado en la segunda ciudad SELECT p.Ciudad_prov ,e.Cod_Comp,a.Ciudad_Art FROM ENVIOS e,PROVEEDORES p,ARTICULOS a WHERE e.Cod_Prov = p.Cod_Prov AND e.Cod_Art = a.Cod_Art GO 14)-- sin recuperar las ternas en los que los dos valores de ciudad sean los mismos SELECT p.Ciudad_prov ,e.Cod_Comp,a.Ciudad_Art FROM ENVIOS e,PROVEEDORES p,ARTICULOS a WHERE e.Cod_Prov = p.Cod_Prov AND e.Cod_Art = a.Cod_Art AND p.Ciudad_prov <> a.Ciudad_Art GO 15)-- Obtener el numero de suministros, el de articulos distintos suministrados y la cantidad total de articulos suministrados por el proveedor P2 SELECT COUNT(*)AS 'NUMERO DE SUMINISTROS', COUNT (DISTINCT Cod_Art )AS 'ARTICULOS SUMINISTRADOS', SUM(Cantidad ) AS 'TOTAL ARTICULOS SUMINISTRADOS' FROM ENVIOS WHERE Cod_Prov ='P2' GO 16)-- para cada articulo y componente suministrado obtener los valores de Cod_Comp Cod_Art y la cantidad total correspondiente SELECT Cod_Comp,Cod_Art,SUM(Cantidad) AS 'TOTAL' FROM ENVIOS GROUP BY Cod_Art,Cod_Comp GO 17)-- Obtener los valores de Cod_Art de los articulos abastecidos al menos por un proveedor que no viva en Madrid y que no este an la misma ciudad en la que se monta el articulo SELECT DISTINCT e.Cod_Art FROM ENVIOS e,ARTICULOS a WHERE e.Cod_Art = a.Cod_Art AND EXISTS ( SELECT * FROM PROVEEDORES p WHERE p.Ciudad_prov != a.Ciudad_Art AND p.Cod_Prov = e.Cod_Prov AND p.Ciudad_prov !='MADRID' ) GO 18)-- Obtener los valores de Cod_Prov para los proveedores que suministran al menos un componente ROJO 10)--
SELECT DISTINCT Cod_Prov FROM ENVIOS WHERE Cod_Comp IN ( SELECT Cod_Comp FROM ENVIOS WHERE Cod_Prov IN ( SELECT Cod_Prov FROM ENVIOS NATURAL JOIN COMPONENTES ON Color_Comp = 'ROJO')) 19)-- Obtener los identificadores de articulos Cod_Art, para los que se ha suministrado algun componente del que se haya suministrado una media superior a 320 articulos SELECT DISTINCT Cod_Art FROM ENVIOS WHERE Cod_Comp IN ( SELECT Cod_Comp FROM ENVIOS GROUP BY Cod_Comp HAVING AVG(Cantidad )>320) GO 20)-- Seleccionar los identificador de proveedores que hayan realizado algun envio con cantidad mayor que la media de los envios realizados para el componente a que corresponda dicho envio. SELECT DISTINCT Cod_Prov FROM ENVIOS e WHERE Cantidad > ( SELECT AVG(Cantidad ) FROM ENVIOS b WHERE b.Cod_Comp = e.Cod_Comp ) GO 21)-- Seleccionar los identificadores de componentes suministrados para el articulo T2 por el proveedor P2 SELECT Cod_Comp FROM ENVIOS WHERE Cod_Art = 'T2' AND Cod_Prov = 'P2' GO 22)-- Seleccionar todos los datos de los envios realizados de componentes cuyo color no sea ROJO SELECT e.* FROM ENVIOS e,COMPONENTES c WHERE e.Cod_Comp = c.Cod_Comp AND c.Color_Comp <>'ROJO' GO -- otra forma SELECT DISTINCT * FROM ENVIOS NATURAL JOIN COMPONENTES c ON c.Color_Comp <> 'ROJO' GO 23)-- Seleccionar los identificadores de los componentes que se suministren´para los articulos T 1 y T2 SELECT Cod_Comp FROM ENVIOS WHERE Cod_Art = 'T1' INTERSECT SELECT Cod_Comp FROM ENVIOS WHERE Cod_Art = 'T2' 24)-- Seleccionar el identificador de proveedores y el numero de envios de componentes de color ROJO llevados a cabo por cada proveedores SELECT Cod_Prov, COUNT(*) AS 'NUMERO DE ENVIOS' FROM ENVIOS WHERE Cod_Comp IN ( SELECT Cod_Comp FROM COMPONENTES WHERE Color_Comp = 'ROJO') GROUP BY Cod_Prov GO 25)-- Seleccionar los colores de componentes suministrados por el proveedor P1 SELECT DISTINCT Color_Comp AS 'COLOR' FROM COMPONENTES WHERE Cod_Comp IN ( SELECT DISTINCT Cod_Comp FROM ENVIOS WHERE Cod_Prov = 'P1') GO 26)-- Seleccionar los datos de envio y nombre de la ciudad de aquellos envios que cumplan que el articulo, proveedor y componente son de la misma ciudad. SELECT e.*,c.Ciudad_Comp FROM ENVIOS e,COMPONENTES c, ARTICULOS a, PROVEEDORES p WHERE e.Cod_Art = a.Cod_Art AND e.Cod_Comp = c.Cod_Comp AND e.Cod_Prov = p.Cod_Prov AND p.Ciudad_prov = a.Ciudad_Art GO
27)-- Seleccionar los nombres de los componentes que son suministrados en una cantidad total superior a 5 00 SELECT DISTINCT Nom_Comp FROM COMPONENTES WHERE Cod_Comp IN ( SELECT Cod_Comp FROM ENVIOS GROUP BY Cod_Comp HAVING SUM (Cantidad )>500) GO 28)-- Seleccionar los identificadores de proveedores que residan en Sevilla y no suministren mas de dos distintos (SELECT Cod_Prov FROM PROVEEDORES WHERE Ciudad_prov = 'SEVILLA' ) INTERSECT (SELECT Cod_Prov FROM ENVIOS GROUP BY Cod_Prov HAVING COUNT (DISTINCT Cod_Art)>2) GO 29)-- Seleccionar los identificadores de articulos para los cuales todos sus componentes se fabrican en una misma ciudad SELECT Cod_Art FROM ENVIOS e,COMPONENTES c WHERE e.Cod_Comp = c.Cod_Comp GROUP BY Cod_Art HAVING COUNT (DISTINCT Ciudad_Comp ) = 1 GO -- OTRA FORMA SELECT e.Cod_Art FROM ENVIOS e JOIN COMPONENTES c ON e.Cod_Comp = c.Cod_Comp GROUP BY Cod_Art HAVING COUNT (DISTINCT Ciudad_Comp ) = 1 GO 30)-- Seleccionar los identificadores de articulos para los que provean envios de todos los componentes existentes en la base de datos SELECT Cod_Art FROM ENVIOS GROUP BY Cod_Art HAVING COUNT (DISTINCT Cod_Comp ) = (SELECT COUNT (*) FROM COMPONENTES ) GO 31)-- Seleccionar los codigos de proveedores y articulos que suministran al menos dos componentes de color ROJO SELECT Cod_Prov,Cod_Art FROM ENVIOS e,COMPONENTES c WHERE e.Cod_Comp = c.Cod_Comp AND c.Color_Comp ='ROJO' GROUP BY Cod_Prov,Cod_Art HAVING COUNT (*)>1 GO -- OTRA FORMA SELECT DISTINCT Cod_Prov,Cod_Art FROM ENVIOS NATURAL JOIN COMPONENTES c ON c.Color_Comp ='ROJO' GROUP BY Cod_Prov,Cod_Art HAVING COUNT (*)>1 GO
