Dificuldade com subselect

[LucianoR]

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í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á.

Editado Dezembro 13, 2011 por Denis Courcy
colocar codigo entre CODEs

[Denis Courcy]

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ê.

[LucianoR]

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;

Editado Dezembro 13, 2011 por LucianoR

[Denis Courcy]

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