Disciplina : Gerˆencia de Banco de Dados 1 - 20 Semestre 2010 Professora : Sandra de Amo
Lista de Exerc´ıcios 4 - Consultas SQL Considere o seguinte esquema de banco de dados :
Produto(Modelo,Tipo,Fabr)
O atributo Tipo pode assumir valores : PC, impressora, monitor PC(Modelo,Velocidade,RAM,HD,CD,Pre¸co,TipoPC)
O atributo TipoPC pode assumir valores desktop, notebook ou netbook Laptop(Modelo,TamanhoTela)
Impressora(Modelo,Cor,Pre¸co)
O atrbituo Cor pode assumir valores ‘Sim’ ou ‘N~ao’ Monitor(Modelo,Tipo,Tamanho,Pre¸co)
O atributo Tipo pode assumir valores: Plasma, LCD, LED Dˆe os comandos SQL para realizar as seguintes consultas:
1. Para cada tamanho de telas de laptops, dˆe os modelos daqueles com maior capacidade de HD e menor pre¸co.
Solu¸c˜ao:
SELECT L.TamanhoTela, L.Modelo
FROM Laptop L, PC, (SELECT L1.TamanhoTela, Max(PC1.HD), Min(PC1.Pre¸co) FROM PC1 PC, Laptop L1
WHERE PC1.Modelo = L1.Modelo GROUP BY L.TamanhoTela) AS TEMP WHERE L.Modelo = PC.Modelo AND L.TamanhoTela = TEMP.TamanhoTela AND PC.HD = TEMP.HD AND
PC.Pre¸co = TEMP.Pre¸co GROUP BY L.TamanhoTela
2. Dˆe os fabricantes que produzem os modelos de PCs com maior velocidade e maior capacidade de RAM e com menores pre¸cos.
Solu¸c˜ao: SELECT P.Fabr FROM Produto P, PC
WHERE P.Modelo = PC.Modelo AND
PC.Velocidade >= ALL (SELECT PC1.Velocidade FROM PC PC1 ) AND PC.RAM >= ALL (SELECT PC2.RAM FROM PC PC2) AND
3. Para cada fabricante, dˆe o total dos pre¸cos de sua impressora colorida mais barata, seu monitor LED com maior tamanho de tela mais barato e seu desktop com maior velocidade mais barato. Solu¸c˜ao:
SELECT Temp.Fabr, SUM(Temp.MinPre¸co) FROM
(
(SELECT P.Fabr, MIN(I.Pre¸co) AS MinPre¸co FROM Produto P, Impressora I
WHERE P.Modelo = I.Modelo AND I.Cor = ’Sim’)
UNION
(SELECT P.Fabr, MIN(M.Pre¸co) AS MinPre¸co FROM Produto P, Monitor M
WHERE P.Modelo = M.Modelo
AND M.Tipo = ’LED’ AND M.TamanhoTela >= ALL (SELECT M1.TamanhoTela
FROM Monitor M1, Produto P1 WHERE M1.Modelo = P1.Modelo AND P1.Fabr = P.Fabr AND
M1.Tipo = ’LED’)) UNION
(SELECT P.Fabr, MIN(PC.Pre¸co) AS MinPre¸co FROM Produto P, PC
WHERE P.Modelo = PC.Modelo AND PC.Velocidade >= ALL (SELECT PC1.Velocidade FROM PC PC1, Produto P1
WHERE PC1.Modelo = P1.Modelo AND P1.Fabr = P.Fabr))
) AS Temp
GROUP BY Temp.Fabr HAVING COUNT = 3
4. Para cada fabricante, dˆe os modelos de laptops que fabricam com o menor tamanho de tela e o menor pre¸co.
Solu¸c˜ao: Vamos primeiramente descrever a constru¸c˜ao de cada subconsulta intermedi´aria que vai compor a consulta final
• PRIMEIRA SUBCONSULTA TEMP2: Para cada fabricante fixo P.Fabr, retorna uma tabela com duas colunas, contendo para cada tamanho de tela de laptop fornecido pelo fabricante P.Fabr, o menor pre¸co dos laptops que ele fornece com este tamanho de tela.
TEMP2
SELECT L2.TamanhoTela, MIN(PC2.Preco) AS MPreco FROM Laptop L2, PC PC2, Produto P2
WHERE L2.Modelo = PC2.Modelo AND L2.Modelo = P2.Modelo AND P2.Fabr = P.Fabr GROUP BY L2.TamanhoTela
• SEGUNDA SUBCONSULTATEMP1: Seleciona na tabelaTEMP2as tuplas correspondendo ao menor tamanho de tela
TEMP1:
SELECT TEMP2.TamanhoTela, TEMP2.MPreco
FROMTEMP2
WHERE TEMP2.TamanhoTela <= (SELECT T.TamanhoTela FROM TEMP2 AS T) Repare que a resposta a esta consulta ´e ´unica, isto ´e, cont´em apenas uma tupla.
• TERCEIRA SUBCONSULTA TEMP3: Seleciona todos os modelos de laptops possuindo o tamanho de tela e o pre¸co da tupla da tabelaTEMP1
TEMP3
SELECT PC1.Modelo
FROM Laptop L1, PC PC1,TEMP1
WHERE L1.Modelo = PC1.Modelo AND L1.TamanhoTela = TEMP1.TamanhoTela AND PC1.Preco = TEMP1.MPreco
• CONSULTA PEDIDA : Constr´oi a lista dos fabricantes e seus respectivos modelos perten-centes `a resposta da TERCEIRA CONSULTA.
SELECT P.Fabr, P.Modelo FROM Produto P, Laptop L
WHERE L.Modelo = P.Modelo AND AND P.Modelo IN (TEMP3)
RESPOSTA FINAL (juntando as subconsultas intermedi´arias numa ´unica consulta) SELECT P.Fabr, P.Modelo
FROM Produto P, Laptop L WHERE L.Modelo = P.Modelo AND AND P.Modelo IN (SELECT PC1.Modelo
FROM Laptop L1, PC PC1, (SELECT TEMP2.TamanhoTela, TEMP2.MPreco
FROM (SELECT L2.TamanhoTela, MIN(PC2.Preco) AS MPreco FROM Laptop L2, PC PC2, Produto P2
WHERE L2.Modelo = PC2.Modelo AND L2.Modelo = P2.Modelo AND P2.Fabr = P.Fabr GROUP BY L2.TamanhoTela) AS TEMP2
WHERE TEMP2.TamanhoTela <= (SELECT T.TamanhoTela FROM TEMP2 AS T)) AS TEMP1
WHERE L1.Modelo = PC1.Modelo AND L1.TamanhoTela = TEMP1.TamanhoTela AND PC1.Preco = TEMP1.MPreco)
5. Para cada fabricante que fabrica mais do que 10 modelos de PCs e mais do que 5 modelos de impressoras, dˆe a m´edia dos pre¸cos de todos os modelos de PCs que ele fabrica com capacidade de HD = 500Gb.
Solu¸c˜ao:
SELECT P.Fabr, AVG(PC.Preco) FROM Produto P, PC
WHERE P.Modelo = PC.Modelo AND PC.HD = 500
AND (SELECT COUNT(PC1.Modelo) FROM PC PC1, Produto P2 WHERE PC1.Modelo = P2.Modelo AND P2.Fabr = P.Fabr) >= 10) AND (SELECT COUNT(I.Model) FROM Impressora I, Produto P3 WHERE I.Model = P3.Model AND
P3.Fabr = P.Fabr) >= 5) GROUP BY P.Fabr
6. Para cada fabricante que fabrica tanto PCs quanto impressoras, dˆe o n´umero total produtos que fabrica.
Solu¸c˜ao:
SELECT COUNT(P.Modelo) FROM Produto P
WHERE EXISTS (SELECT PC.Modelo FROM PC, Produto P1
WHERE P1.Modelo = PC.Modelo AND P1.Fabr = P.Fabr)
AND EXISTS (SELECT I.Modelo FROM Impressora I, Produto P2 WHERE P2.Modelo = I.Modelo AND P2.Fabr = P.Fabr)
7. Para cada fabricante que fabrica todos os modelos de PCs com capacidade m´axima de HD, dˆe os menores pre¸cos destes PCs com capacidade m´axima que ele fabrica.
Solu¸c˜ao:
SELECT P.Fabr, MIN(PC.Preco) FROM Produto P, PC
WHERE NOT EXISTS ( (SELECT PC2.Modelo FROM PC PC2 WHERE PC2.HD >= ALL (SELECT PC3.HD FROM PC PC3)) EXCEPT
(SELECT PC4.HD FROM PC PC4, Produto P2 WHERE PC4.Modelo = P2.Modelo AND P2.Fabr = P.Fabr))
AND PC.HD >= ALL (SELECT PC1.HD FROM PC PC1) GROUP BY P.Fabr
8. Para cada fabricante que s´o fabrica impressoras branco-e-preto, dˆe a m´edia dos pre¸cos deste tipo de impressora que ele fabrica.
Solu¸c˜ao:
SELECT P.Fabr, AVG(I.Pre¸co) FROM Produto P, Impressora I
WHERE P.Modelo = I.Modelo AND I.Cor = ‘N~ao’ AND NOT EXISTS
(SELECT I1.Modelo
FROM Impressora I1, Produto P1 WHERE P1.Modelo = I1.Modelo AND P1.Fabr = P.Fabr AND
I1.Cor = ‘Sim’) GROUP BY P.Fabr
9. Para cada fabricante que s´o produz um modelo de impressora colorida, dˆe lista de modelos de PCs que eles fabrica.
Solu¸c˜ao:
SELECT P.Fabr, PC.Modelo FROM Produto P, PC
WHERE P.Modelo = PC.Modelo AND UNIQUE (SELECT I.Modelo FROM Impressora I, Produto P1 WHERE I.Modelo = P1.Modelo AND I.Cor = ‘Sim’
AND P1.Fabr = P.Fabr) GROUP BY P.Fabr
