LucianoR Posted December 13, 2011 Report Share Posted December 13, 2011 (edited) Tenho as tabelas e os seguintes povoamentos:CREATE TABLE produto ( idproduto INTEGER, descricao VARCHAR(200), composicao VARCHAR(150), CONSTRAINT pk_idproduto PRIMARY KEY(idproduto) ); CREATE TABLE materia_prima ( idinsumo INTEGER, descricao VARCHAR(150), unidade VARCHAR(2), precounitario DECIMAL(10,2), CONSTRAINT pk_idinsumo PRIMARY KEY(idinsumo) ); CREATE TABLE insumos ( idproduto INTEGER, idinsumo INTEGER, qtd INTEGER, CONSTRAINT pk_insumos_idproduto PRIMARY KEY(idproduto,idinsumo), CONSTRAINT fk_idproduto FOREIGN KEY(idproduto) REFERENCES produto(idproduto), CONSTRAINT fk_idinsumo FOREIGN KEY(idinsumo) REFERENCES materia_prima(idinsumo) ); CREATE TABLE cliente ( nome VARCHAR(200), rua VARCHAR(300), numero VARCHAR(10), complemento VARCHAR(100), bairro VARCHAR(100), cidade VARCHAR(100), CONSTRAINT fk_nome PRIMARY KEY(nome) ); CREATE TABLE pedido ( numero_pedido INTEGER, data_pedido DATE, cliente VARCHAR(200), CONSTRAINT pk_numero_pedido PRIMARY KEY(numero_pedido), CONSTRAINT fk_cliente FOREIGN KEY(cliente) REFERENCES cliente(nome) ); CREATE TABLE itens_do_pedido ( numero_pedido INTEGER, seq_pedido INTEGER UNIQUE, idproduto INTEGER, qtd_produto INTEGER, CONSTRAINT pk_numero_pedido_idproduto PRIMARY KEY(numero_pedido,idproduto), CONSTRAINT fk_numero_pedido FOREIGN KEY (numero_pedido) REFERENCES pedido(numero_pedido), CONSTRAINT fk_idp_idproduto FOREIGN KEY(idproduto) REFERENCES produto(idproduto) ); INSERT INTO produto VALUES(1,'Poste',NULL); INSERT INTO produto VALUES(2,'Bolo',NULL); INSERT INTO produto VALUES(3,'Pastel de Queijo',NULL); INSERT INTO materia_prima VALUES(10,'Cimento','SC',30.00); INSERT INTO materia_prima VALUES(11,'Areia','Mt',90.00); INSERT INTO materia_prima VALUES(12,'Pedreiro','Hh',10.00); INSERT INTO materia_prima VALUES(13,'Trigo','Kg',3.00); INSERT INTO materia_prima VALUES(14,'Açucar','Kg',2.00); INSERT INTO materia_prima VALUES(15,'Manteiga','Kg',3.50); INSERT INTO materia_prima VALUES(16,'Queijo','Kg',15.00); INSERT INTO materia_prima VALUES(17,'Sebo','Kg',15.00); INSERT INTO cliente VALUES ('Arnaldo José Almeida','Angustura','1961',NULL,'Pedreira','Belém'); INSERT INTO cliente VALUES ('Marlene Cardoso','Augusta','1965',NULL,'Marco','Belém'); INSERT INTO cliente VALUES ('José Roberto','Leblon','205',NULL,'Jurunas','Belém'); INSERT INTO pedido VALUES(1,'10-10-11','Arnaldo José Almeida'); INSERT INTO pedido VALUES(2,'05-08-11','Marlene Cardoso'); INSERT INTO pedido VALUES(3,'06-08-11','José Roberto'); INSERT INTO pedido VALUES(4,'07-08-11','José Roberto'); INSERT INTO itens_do_pedido VALUES(1,1,1,10); INSERT INTO itens_do_pedido VALUES(2,2,2,2); INSERT INTO itens_do_pedido VALUES(2,3,3,5); INSERT INTO itens_do_pedido VALUES(3,4,1,5); INSERT INTO itens_do_pedido VALUES(4,5,1,1); INSERT INTO itens_do_pedido VALUES(4,6,3,2); INSERT INTO itens_do_pedido VALUES(4,7,2,3); /* Poste */ INSERT INTO insumos VALUES (1,10,4); INSERT INTO insumos VALUES (1,11,3); INSERT INTO insumos VALUES (1,12,2); /* Bolo */ INSERT INTO insumos VALUES (2,13,3); INSERT INTO insumos VALUES (2,14,1); INSERT INTO insumos VALUES (2,15,1); /* Pastel de Queijo */ INSERT INTO insumos VALUES (3,13,1); INSERT INTO insumos VALUES (3,15,1); INSERT INTO insumos VALUES (3,16,1);Não consigo de jeito nenhum resolver essa questão:4. Utilizando sub-query na lista de select, faça:a. Crie uma query para construir a seguinte saídaMatéria Prima ;Descrição da Matéria Prima;Cliente que mais adquiriu produtos que contém esse insumo;Quant. Produtos que usam esse insuumoAlguém poderia me ajudar a resolver isso com subquery? Agradeço desde já. Edited December 13, 2011 by Denis Courcy colocar codigo entre CODEs Quote Link to comment Share on other sites More sharing options...
0 Denis Courcy Posted December 13, 2011 Report Share Posted December 13, 2011 Oi 'LucianoR' Isto está com cara de trabalho escolar. Mostre como você tentou fazer o exercício que te orientarei no prosseguimento dele.Não faremos o exercício pra você. Quote Link to comment Share on other sites More sharing options...
0 LucianoR Posted December 13, 2011 Author Report Share Posted December 13, 2011 (edited) Eu estou estudando pra uma prova.Esse é um dos exercícios da lista que meu professor passou sim, foi o único que não consegui resolver, por isso vim pedir ajuda.Não estou pedindo pra fazer por mim, só queria entender como posso fazer por subselect, pois todos os exemplos que eu vejo na internet são bem simples e não me ajudam muito.Desculpa pelo mal entendido.Mas de qualquer forma, aqui estão os outros exercícios da mesma lista resolvidos:1. Faça a inclusão das seguintes tabelas e suas constraints:a. Cliente (nome, rua, numero, complemento, bairro, cidade) b. Pedido (numero pedido, data pedido, cliente)c. Itens do pedido (numero pedido, seq. pedido, idproduto, qtd. produto )A Resposta já está no tópico.2. Faça a inserção das linhas nas tabelas, de tal forma que: “O cliente Arnaldo José Almeida fez um pedido de 10 postes, a cliente Marlene Cardoso fez um pedido de 2 bolos e de 5 pasteis de queijo, e o cliente José Roberto fez 2 pedidos, um com 5 postes e outro com 1 poste, 2 pasteis e 3 bolos”A resposta também já está no tópico.3. Desenvolva as seguintes requisições de informação:a. Qual será o custo total para fazer o atendimento a todos os pedidos?b. Quanto o cliente “José Roberto” terá que pagar pelo seu pedido?c. Quais as matérias-primas e suas quantidades que teremos que adquirir para atender a todos os pedidos./* a. Qual será o custo total para fazer o atendimento a todos os pedidos? */select sum(it.qtd_produto*insu.qtd*mat.precounitario)from itens_do_pedido it join produto produ on it.idproduto = produ.idproduto join insumos insu on produ.idproduto = insu.idproduto join materia_prima mat on insu.idinsumo = mat.idinsumo;/* b. Quanto o cliente “José Roberto” terá que pagar pelo seu pedido? */select cli.nome,sum(it.qtd_produto*insu.qtd*mat.precounitario)from cliente cli join pedido ped on cli.nome = ped.cliente join itens_do_pedido it on ped.numero_pedido = it.numero_pedido join produto produ on it.idproduto = produ.idproduto join insumos insu on produ.idproduto = insu.idproduto join materia_prima mat on insu.idinsumo = mat.idinsumo and cli.nome = 'José Roberto' group by cli.nome;/* c. Quais as matérias-primas e suas quantidades que teremos que adquirir para atender a todos os pedidos */ select mat.descricao,sum(it.qtd_produto*insu.qtd)from cliente cli join pedido ped on cli.nome = ped.cliente join itens_do_pedido it on ped.numero_pedido = it.numero_pedido join produto produ on it.idproduto = produ.idproduto join insumos insu on produ.idproduto = insu.idproduto join materia_prima mat on insu.idinsumo = mat.idinsumo group by mat.descricao order by sum desc; Edited December 13, 2011 by LucianoR Quote Link to comment Share on other sites More sharing options...
0 Denis Courcy Posted December 14, 2011 Report Share Posted December 14, 2011 Oi, 'LucianoR'creio que você pode continuar daqui:SELECT mp.idinsumo AS mat_prima, mp.descricao AS desc_mat_prima, xyz.cliente, xyz.qtprodutos FROM materia_prima mp INNER JOIN insumos ins ON ins.idinsumo = mp.idinsumo INNER JOIN ( SELECT abc.cliente, abc.idproduto, MAX(abc.qtdprodutos) AS qtprodutos FROM ( SELECT ped.cliente, prod.idproduto, COUNT(itped.idproduto) AS qtdprodutos FROM pedido ped INNER JOIN itens_do_pedido itped ON itped.numero_pedido = ped.numero_pedido INNER JOIN produto prod ON prod.idproduto = itped.idproduto GROUP BY ped.cliente, prod.idproduto ) abc GROUP BY abc.cliente, abc.idproduto ) xyz ON xyz.idproduto = ins.idproduto WHERE mp.idinsumo = 10 Quote Link to comment Share on other sites More sharing options...
Question
LucianoR
Tenho as tabelas e os seguintes povoamentos:
Não consigo de jeito nenhum resolver essa questão:
4. Utilizando sub-query na lista de select, faça:
a. Crie uma query para construir a seguinte saída
Matéria Prima ;
Descrição da Matéria Prima;
Cliente que mais adquiriu produtos que contém esse insumo;
Quant. Produtos que usam esse insuumo
Alguém poderia me ajudar a resolver isso com subquery? Agradeço desde já.
Edited by Denis Courcycolocar codigo entre CODEs
Link to comment
Share on other sites
3 answers to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.