Trabalho da fac , HELP

[robalo 0]

Boa noite, 

Preciso de ajuda urgente ! Tenho um trabalho da fac para entregar e não consigo resolver 3 interrogações ! Alguém que perceba me pode ajudar ?

-Determine a pegada ecológica associada a cada um dos produtos do tipo lar.

-Email do(s) consumidor(es) que registou compras implicando menor pegada ecológica – ter em conta o número de dependentes, dividindo a mesma pelo número de pessoas no agregado (consumidor + número de dependentes). 

-Email dos consumidores que realizaram compras que incluem todos os elementos mencionados na tabela “Elemento”

 

E este é o insert :

Spoiler

Insert into Consumidor ( numero, email, sexo, nascimento) values
(123456789,'[email protected]','M','1968-07-22'),
(121212121,'[email protected]','F','1954-11-14'),
(987654321,'[email protected]','F','1972-05-24');

Insert into Dependente ( consumidor, numero, sexo, nascimento) values
(987654321,1,'F','2015-10-21'),
(987654321,2,'F','2007-08-15'),
(987654321,3,'F','1999-09-19');

Insert into Marca ( numero, nome) values
( 1, 'Rovex'),
( 2, 'Contilua'),
( 3, 'Matacao'),
( 4, 'Electrina'),
( 5, 'Medicala'),
( 6, 'Aprima'),
( 7, 'MacKing'),
( 8, 'Avax'),
( 9, 'Peskanov'),
( 10, 'Oleitao'),
( 11, 'Oscosse'),
( 12, 'Tidao'),
( 13, 'QtaMateus'),
( 14, 'Fonavoz'),
( 15, 'Carrilona'),
( 16, 'Galpina');

 

Insert into Produto ( codigo, marca, nome, tipo, comercioJusto) values
( 1, 1, 'lixivia', 'lar', 'B'),
( 1, 2, 'makeup', 'lar', 'C'),
( 1, 3, 'insecticida', 'lar', 'C'),
( 1, 4, 'pilhas-NICd-AA', 'lar', 'C'),
( 2, 4, 'pilhas-alcalinas-AAA', 'lar', 'C'),
( 1, 5, 'shampoo', 'lar', 'B'),
( 2, 5, 'termometro', 'lar', 'D'),
( 1, 6, 'fiambre', 'alimentac', 'C'),
( 1, 7, 'hamburger', 'alimentac', 'C'),
( 1, 8, 'fosforos', 'lar', 'D'),
( 1, 9, 'carapaus', 'alimentac', 'A'),
( 1, 10, 'azeite', 'alimentac', 'B'),
( 1, 11, 'lampada-led-10W', 'lar', 'B'),
( 1, 12, 'sabao', 'lar', 'C'),
( 1, 13, 'pato', 'alimentac', 'B'),
( 1, 14, '4ZR56', 'electrodom', 'C'),
( 1, 15, 'autocarro', 'viagem', 'B'),
( 1, 16, 'gasolina', 'viagem', 'B');

Insert into Elemento ( codigo, nome, pegadaEcologica, saude) values
( 'MER', 'mercúrio', 12, 15),
( 'CHU', 'chumbo', 7, 12),
( 'COB', 'cobre', 3, 7),
( 'CDM', 'cádmio', 8, 8),
( 'PVC', 'PVC', 4, 7),
( 'BHA', 'BHA', 10, 7),
( 'BHT', 'BHT', 11, 6),
( 'DBP', 'DBP', 10, 8),
( 'SIL', 'Silicones', 9, 3),
( 'BRM', 'brómio', 7, 9),
( 'PDF', 'PDF', 6, 7),
( 'CO2', 'CO2', 15, 7),
( 'FTA', 'Ftalatos', 8, 6),
( 'BIF', 'Bifenilos-policlorados', 5, 12),
( 'FRA', 'fragâncias-sintécticas', 11, 4);

 

Insert  into compra ( produto, prodMarca, consumidor, quantidade) values
( 1, 1, 123456789, 1.5),
( 1, 2, 123456789, 0.5),
( 1, 3, 123456789, 0.5),
( 1, 4, 123456789, 4),
( 2, 4, 123456789, 8),
( 1, 5, 123456789, 1),
( 2, 5, 123456789, 2),
( 1, 6, 123456789, 4),
( 1, 7, 123456789, 1),
( 1, 8, 123456789, 3),
( 1, 9, 123456789, 2),
( 1, 10, 123456789, 2),
( 1, 11, 123456789, 2),
( 1, 12, 987654321, 2),
( 1, 13, 987654321, 3),
( 1, 14, 987654321, 2),
( 1, 15, 987654321, 30),
( 1, 16, 987654321, 40),
( 1, 1, 987654321, 1.5),
( 1, 2, 987654321, 5),
( 1, 3, 987654321, 9),
( 1, 4, 987654321, 2),
( 2, 4, 987654321, 3),
( 1, 5, 987654321, 5),
( 2, 5, 987654321, 2),
( 1, 6, 121212121, 5),
( 1, 7, 121212121, 2),
( 1, 8, 121212121, 4),
( 1, 9, 121212121, 3),
( 1, 10, 121212121, 3),
( 1, 11, 121212121, 4),
( 1, 12, 121212121, 3),
( 1, 13, 121212121, 31),
( 1, 14, 121212121, 41),
( 1, 15, 121212121, 20),
( 1, 16, 121212121, 153);

 

Insert into composto ( produto, prodMarca, elemento, percentagem) values
( 1, 1, 'PDF', 3),
( 1, 1, 'FTA', 5),
( 1, 1, 'BIF', 4),
( 1, 1, 'FRA', 2),
( 1, 2, 'PVC', 3),
( 1, 2, 'BHA', 4),
( 1, 2, 'BHT', 5),
( 1, 2, 'DBP', 4),
( 1, 2, 'SIL', 2),
( 1, 2, 'FRA', 2),
( 1, 3, 'MER', 3),
( 1, 3, 'CHU', 4),
( 1, 3, 'PVC', 2),
( 1, 3, 'BRM', 5),
( 1, 3, 'FTA', 5),
( 1, 3, 'FRA', 5),
( 1, 4, 'CDM', 20),
( 1, 4, 'PVC', 2),
( 1, 4, 'SIL', 1),
( 1, 4, 'BRM', 2),
( 2, 4, 'PVC', 2),
( 2, 4, 'SIL', 1),
( 2, 4, 'BRM', 2),
( 1, 5, 'PVC', 8),
( 1, 5, 'BHT', 3),
( 1, 5, 'SIL', 3),
( 1, 5, 'BRM', 2),
( 1, 5, 'FRA', 5),
( 2, 5, 'MER', 10),
( 2, 5, 'COB', 3),
( 2, 5, 'PVC', 4),
( 1, 6, 'PVC', 2),
( 1, 6, 'SIL', 1),
( 1, 6, 'PDF', 2),
( 1, 6, 'FRA', 2),
( 1, 7, 'PVC', 10),
( 1, 7, 'SIL', 1),
( 1, 7, 'FTA', 5),
( 1, 7, 'BIF', 6),
( 1, 7, 'FRA', 1),
( 1, 8, 'CHU', 2),
( 1, 8, 'PVC', 1),
( 1, 8, 'BRM', 2),
( 1, 9, 'PVC', 1),
( 1, 10, 'PVC', 2),
( 1, 11, 'CHU', 2),
( 1, 11, 'COB', 4),
( 1, 11, 'PVC', 3),
( 1, 12, 'PVC', 2),
( 1, 12, 'SIL', 4),
( 1, 13, 'PVC', 1),
( 1, 14, 'MER', 3),
( 1, 14, 'COB', 4),
( 1, 14, 'CDM', 2),
( 1, 14, 'PVC', 4),
( 1, 14, 'SIL', 2),
( 1, 14, 'BRM', 2),
( 1, 15, 'MER', 1),
( 1, 15, 'CHU', 2),
( 1, 15, 'PVC', 5),
( 1, 15, 'BHA', 3),
( 1, 15, 'BRM', 2),
( 1, 15, 'CO2', 5),
( 1, 16, 'MER', 1),
( 1, 16, 'CHU', 6),
( 1, 16, 'PVC', 20),
( 1, 16, 'BHA', 3),
( 1, 16, 'BRM', 2),
( 1, 16, 'CO2', 20),
( 1, 16, 'PDF', 1);

 

[robalo 0]

SELECT P.nome, SUM(CO.percentagem * E.pegadaEcologica) AS PegadaEcologica 
FROM Produto P, Elemento E, composto CO 
WHERE CO.elemento=E.codigo AND CO.produto = P.codigo AND CO.prodMarca = P.marca AND P.tipo = "lar" GROUP BY (P.nome)

 

já consegui resolver o 1º mas os outros 2 estou mesmo á nora ... HELP

[Cσяvσ⋆ 1,804]

2 minutos atrás, robalo disse:

SELECT P.nome, SUM(CO.percentagem * E.pegadaEcologica) AS PegadaEcologica 
FROM Produto P, Elemento E, composto CO 
WHERE CO.elemento=E.codigo AND CO.produto = P.codigo AND CO.prodMarca = P.marca AND P.tipo = "lar" GROUP BY (P.nome)

 

já consegui resolver o 1º mas os outros 2 estou mesmo á nora ... HELP

 

Arranja-me o código para criar as tabelas, o insert não serve para nada sem isto.

[robalo 0]

Spoiler

Create table  Consumidor  (
    numero    int(9),
    email    varchar(30)    not null,
    sexo    char(1)    not null,
    nascimento    date    not null,
    constraint Consumidor_sexo_RI001     check (sexo in ('F','M')),
    constraint Consumidor_unique_RI002    unique(email),
    constraint pk_Consumidor     primary key (numero)
);

Create table  Dependente  (
    consumidor    int(9),
    numero    int(2),
    sexo    char(1)    not null,
    nascimento    date    not null,
    constraint Dependente_sexo_RI003 check (sexo in ('F','M')),
    constraint fk_Dependente_consumidor     foreign key (consumidor) references Consumidor(numero) on delete cascade,
    constraint pk_Dependente     primary key (consumidor,numero)
);

Create table  Elemento  (
    codigo    char(3),
    nome    varchar(25) not null,
    pegadaEcologica    int(2) not null,
    saude    int(2) not null,
    constraint pk_Elemento     primary key (codigo)
);

Create table  Marca  (
    numero    int(7),
    nome    varchar(30) not null,
    constraint pk_Marca     primary key (numero)
);

Create table  Produto  (
    codigo    int(6),
    marca    int(7),
    nome    varchar(50) not null,
    tipo    char(10),
    comercioJusto    char(1),
    constraint Produto_tipo_RI004    check (tipo in ('alimentac','lar','jardim','automov','viagem','electrodom')),
    constraint Produto_comercioJusto_RI005     check (comercioJusto in ('A','B','C','D')),
    constraint fk_Produto_marca     foreign key (marca) references Marca(numero) on delete cascade,
    constraint pk_Produto     primary key (codigo,marca)
);

Create table  compra  (
    produto    int(6),
    prodMarca    int(7),
    consumidor    int(9),
    quantidade    decimal(10,3)    not null,
    constraint compra_quantidade_RI006    check (quantidade>0),
    constraint fk_compra_produto     foreign key (produto,prodMarca) references Produto(codigo,marca) on delete cascade,
    constraint fk_compra_consumidor     foreign key (consumidor) references Consumidor(numero) on delete cascade,
    constraint pk_compra     primary key (produto,prodMarca,consumidor)
);

Create table  composto  (
    produto    int(6),
    prodMarca    int(7),
    elemento    char(3),
    percentagem    decimal(4,1)    not null,
    constraint composto_percentagem_RI007    check (percentagem>0 and percentagem<=100),
    constraint fk_composto_produto     foreign key (produto,prodMarca) references Produto(codigo,marca) on delete cascade,
    constraint fk_composto_elemento     foreign key (elemento) references Elemento(codigo) on delete cascade,
    constraint pk_composto     primary key (produto,prodMarca,elemento)
);

 

 

Tens ai corvo

[Cσяvσ⋆ 1,804]

Foste tu que fizeste e preencheste a DB? Nossa senhora dos milagres...

[robalo 0]

Foi o stor que nos deu essa DB e os insert's , nós simplesmente temos de fazer as interrogações

[Cσяvσ⋆ 1,804]

Agora, robalo disse:

Foi o stor que nos deu essa DB e os insert's , nós simplesmente temos de fazer as interrogações

 

Tou perdido no meio disto tudo, nem sei por onde começar...

[robalo 0]

Posso te mandar o que já fiz do trabalho ...

[robalo 0]

Spoiler

/*1. Indique por ordem alfabética descendente o nome de todos os produtos com classificação de comércio justo igual ou superior a B – em que A é a melhor classificação */

SELECT nome 
FROM Produto 
WHERE comercioJusto <= "B" ORDER BY nome ASC 

/*2. Indique o sexo e a idade de cada um dos dependentes do consumidor com email '[email protected]'. */

SELECT TIMESTAMPDIFF(YEAR, D.nascimento, CURDATE()), D.sexo
FROM Dependente D
WHERE D.consumidor IN ( SELECT numero    
                        FROM Consumidor C
                        WHERE C.email = '[email protected]')

/*3. Email dos consumidores que compraram gasolina. */

SELECT C.email
FROM Consumidor C
WHERE C.numero IN ( SELECT O.consumidor
                  FROM compra O
                  WHERE O.produto = 1 AND O.prodMarca = 16)

/*4. Email do(s) consumidor(es) que comprou mais gasolina. */ 

SELECT C.email
FROM Consumidor C
WHERE C.numero IN ( SELECT O.consumidor
                    FROM compra O
                    WHERE O.produto = 1 AND O.prodMarca = 16 AND O.quantidade >= ALL(
                                SELECT O2.quantidade 
                                FROM compra O2 ))

/*5. Determine a pegada ecológica associada a cada um dos produtos do tipo lar. */

SELECT P.nome, SUM(CO.percentagem * E.pegadaEcologica) AS PegadaEcologica 
FROM Produto P, Elemento E, composto CO 
WHERE CO.elemento=E.codigo AND CO.produto = P.codigo AND CO.prodMarca = P.marca AND P.tipo = "lar" GROUP BY (P.nome)

/*6. Nome do(s) produto(s) mais prejudicial para a saúde – quanto maiores os valores no atributo “saúde”, mais prejudiciais são para a mesma. */

SELECT nome
FROM Elemento 
WHERE saude = ( SELECT MAX(saude)
                FROM Elemento)

 

[Cσяvσ⋆ 1,804]

2 minutos atrás, robalo disse:

  Mostrar conteúdo oculto

/*1. Indique por ordem alfabética descendente o nome de todos os produtos com classificação de comércio justo igual ou superior a B – em que A é a melhor classificação */

SELECT nome 
FROM Produto 
WHERE comercioJusto <= "B" ORDER BY nome ASC 

/*2. Indique o sexo e a idade de cada um dos dependentes do consumidor com email '[email protected]'. */

SELECT TIMESTAMPDIFF(YEAR, D.nascimento, CURDATE()), D.sexo
FROM Dependente D
WHERE D.consumidor IN ( SELECT numero    
                        FROM Consumidor C
                        WHERE C.email = '[email protected]')

/*3. Email dos consumidores que compraram gasolina. */

SELECT C.email
FROM Consumidor C
WHERE C.numero IN ( SELECT O.consumidor
                  FROM compra O
                  WHERE O.produto = 1 AND O.prodMarca = 16)

/*4. Email do(s) consumidor(es) que comprou mais gasolina. */ 

SELECT C.email
FROM Consumidor C
WHERE C.numero IN ( SELECT O.consumidor
                    FROM compra O
                    WHERE O.produto = 1 AND O.prodMarca = 16 AND O.quantidade >= ALL(
                                SELECT O2.quantidade 
                                FROM compra O2 ))

/*5. Determine a pegada ecológica associada a cada um dos produtos do tipo lar. */

SELECT P.nome, SUM(CO.percentagem * E.pegadaEcologica) AS PegadaEcologica 
FROM Produto P, Elemento E, composto CO 
WHERE CO.elemento=E.codigo AND CO.produto = P.codigo AND CO.prodMarca = P.marca AND P.tipo = "lar" GROUP BY (P.nome)

/*6. Nome do(s) produto(s) mais prejudicial para a saúde – quanto maiores os valores no atributo “saúde”, mais prejudiciais são para a mesma. */

SELECT nome
FROM Elemento 
WHERE saude = ( SELECT MAX(saude)
                FROM Elemento)

 

 

Bem, para começar o teu 5 deve estar mal, para juntares tabelas relacionáveis tens que utilizar o JOIN, não dar select em várias tabelas pelo FROM (não relacionada nada).

 

https://www.w3schools.com/sql/sql_join.asp

 

Vou ver o que raio está para aqui feito e já te digo alguma coisa, isto envolve contas que não faço a mínima do que sejam, se tiveres fórmulas por ai mete-as cá porque pelos vistos calcular a Pegada Ecológica não é só ir buscar o valor pegadaEcologica à tabela elemento.

[robalo 0]

Spoiler

Modelo Relacional (SQL/DDL) 
Considere como base de referência para esta segunda etapa do projeto o seguinte esquema relacional para um portal para consumidores com preocupações sociais e ambientais, correspondente a uma simplificação dos conceitos definidos para a primeira etapa. Pode consultar uma solução do modelo EA na página da disciplina na aula teórica do dia 10-11-2017. Considere ainda o ficheiro createE2.sql com o esquema relacional especificado em SQL/DDL e o ficheiro insertE2.sql com um conjunto de comandos SQL/DML de inserção de dados (poderá inserir mais linhas caso necessário):  
 
Consumidor (numero, email, sexo, nascimento) 
Dependente (Consumidor.numero, numero, sexo, nascimento)  
Elemento (codigo, nome, pegadaEcologica, saude)  Marca (numero, nome)  
Produto (codigo, Marca.numero, nome, tipo, comercioJusto)  compra (Produto.codigo, Produto.marca, Consumidor.numero, quantidade) composto (Produto.codigo, Produto.marca, Elemento.codigo, percentagem) 
 
Não são considerados nem os especialistas, nem as empresas, nem as respetivas interações.  Associa-se o fator relacionado com o comércio justo (defesa de direitos humanos) diretamente ao produto e dispensa-se as justificações científicas para os valores utilizados no cálculo de cada fator.  Regras de Integridade (as essenciais): 
RI-1: Domínio do atributo sexo de Consumidor: sexo in ('F','M') RI-2: Chave candidata de Consumidor: email RI-3: Domínio do atributo sexo de Dependente: sexo in ('F','M') RI-4: Domínio do atributo tipo em Produto:  tipo in ('alimentac','lar','jardim','automov', 'viagem','electrodom') RI-5: Domínio do atributo comercioJusto em Produto: comercioJusto in ('A','B','C','D') RI-6: Domínio do atributo quantidade de compra: quantidade >0  RI-7: Domínio do atributo percentagem de composto:  percentagem >0 e percentagem <=100 RI Adicionais: A quantidade na compra refere-se a unidades de produtos. Para o caso dos produtos tipo viagem referem-se a quilómetros. Na maioria dos consumíveis do dia a dia (alimentação, lar, automóvel/combustíveis, etc.) referem-se a unidades do SI (metros, litros, quilogramas, etc.). 

 
 
 
Objetivos 
Com base na especificação acima descrita e NÃO no obtido pelo grupo na fase anterior do trabalho, traduza para interrogações SQL/DML os pedidos de dados indicados a seguir. 
 
1. Indique por ordem alfabética descendente o nome de todos os produtos com classificação de comércio justo igual ou superior a B – em que A é a melhor classificação. 
2. Indique o sexo e a idade de cada um dos dependentes do consumidor com email '[email protected]'. 
3. Email dos consumidores que compraram gasolina. 
4. Email do(s) consumidor(es) que comprou mais gasolina. 
5. Determine a pegada ecológica associada a cada um dos produtos do tipo lar. 
6. Nome do(s) produto(s) mais prejudicial para a saúde – quanto maiores os valores no atributo “saúde”, mais prejudiciais são para a mesma. 
7. Liste o sexo e a idade de todas as pessoas abrangidas por esta base de dados – consumidores e seus dependentes. 
8. Email do(s) consumidor(es) que registou compras implicando menor pegada ecológica – ter em conta o número de dependentes, dividindo a mesma pelo número de pessoas no agregado (consumidor + número de dependentes). 
9. Email dos consumidores que realizaram compras que incluem todos os elementos mencionados na tabela “Elemento”. 
 
Nota: 
O cálculo da pegada ecológica de um consumidor deverá ter em conta a quantidade dos produtos comprados e para cada produto, a pegada ecológica dos seus elementos constituintes e a respetiva percentagem.  
𝑝𝑒𝑔𝑎𝑑𝑎𝐸𝑐𝑜𝑙𝑜𝑔𝑖𝑐𝑎 = ∑ 𝑝𝑟𝑜𝑑𝑢𝑡𝑜𝑖 ∗ ∑ 𝑒𝑙𝑒𝑚𝑒𝑛𝑡𝑜𝑖,𝑗 𝑛𝑒𝑙𝑒𝑚𝑒𝑛𝑡𝑜𝑠 𝑗=1 𝑛𝑝𝑟𝑜𝑑𝑢𝑡𝑜𝑠 𝑖=1
∗ pegadaEcologicaj 
 
em que:  
 
produto i  = quantidade de produto i element o i j = % de composição pegadaEcológica j = pegadaEcológica por unidade do elemento j 

 

[Cσяvσ⋆ 1,804]

Citar

𝑝𝑒𝑔𝑎𝑑𝑎𝐸𝑐𝑜𝑙𝑜𝑔𝑖𝑐𝑎 = ∑ 𝑝𝑟𝑜𝑑𝑢𝑡𝑜𝑖 ∗ ∑ 𝑒𝑙𝑒𝑚𝑒𝑛𝑡𝑜𝑖,𝑗 𝑛𝑒𝑙𝑒𝑚𝑒𝑛𝑡𝑜𝑠 𝑗=1 𝑛𝑝𝑟𝑜𝑑𝑢𝑡𝑜𝑠 𝑖=1 ∗ pegadaEcologicaj

 

uh... no thanks.

 

Seja como for, para isto tudo só precisas de saber como fazer um JOIN, o resto são contas de colunas presentes no mesmo join, isso já sabes fazer pelos vistos.

 

Altera o teu 5 e faz o resto com os JOINs.

[robalo 0]

hum, okay vou tentar

[Xandy 46]

Problema resolvido?