problema de busca

[gremio10 14]

Boa noite, gostaria da ajuda de vocês para conseguir fazer esse trabalho que tenho.

 

Não estou conseguindo fazer uma consulta ao qual deve listar todos os usuários que voaram em todos os voos pilotados por um especifico piloto.

 

Só que eu não estou conseguindo, pois quando eu tento utilizar as funções que estamos aprendendo, não consigo retornar apenas o registro que realmente deve retornar.

 

Segue o SQL:

CREATE TABLE voo (num_voo char(10) NOT NULL, hora_part char(4) NOT NULL, hora_cheg char(4) NOT NULL, cidade_part char(20) NOT NULL, cidade_cheg char(20) NOT NULL, PRIMARY KEY (num_voo));

CREATE TABLE piloto (cod_piloto char(10) NOT NULL, nome char(30) NOT NULL, endereco char(50) NOT NULL, data_admissao date NOT NULL, PRIMARY KEY (cod_piloto));

CREATE TABLE execucao_voo (num_voo char(10) NOT NULL, DATA date NOT NULL, cod_piloto char(15) NOT NULL, n_lugares integer NOT NULL, PRIMARY KEY (num_voo,DATA), FOREIGN KEY (num_voo) REFERENCES voo);

CREATE TABLE cliente_p (cod_cli char(10) NOT NULL, nome char(30) NOT NULL, endereco char(50) NOT NULL, telefone char(20), PRIMARY KEY (cod_cli));

CREATE TABLE passagem (num_voo char(10) NOT NULL, DATA date NOT NULL, poltrona char(3) NOT NULL, cod_cli char(10), data_reserva date, PRIMARY KEY (num_voo, DATA, poltrona), FOREIGN KEY (num_voo, DATA) REFERENCES execucao_voo, FOREIGN KEY (cod_cli) REFERENCES cliente_p);

INSERT INTO voo VALUES ('v1', '800', '845', 'Sao Paulo', 'Rio de Janeiro');

INSERT INTO voo VALUES ('v2', '1000', '1330', 'Sao Paulo', 'Salvador');

INSERT INTO voo VALUES ('v3', '2200', '2330', 'Porto Alegre', 'Sao Paulo');

INSERT INTO voo VALUES ('v5', '1200', '1345', 'Porto Alegre', 'Rio de Janeiro');

INSERT INTO voo VALUES ('v4', '1100', '1150', 'Porto Alegre', 'Florianopolis');

 /* insercao de dados na tabela piloto */
INSERT INTO piloto VALUES ('p1', 'Pedro', 'Rua Carlos Gomes, 607', str_to_date('03/03/1989','%d/%m/yyyy'));

INSERT INTO piloto VALUES ('p4', 'Ronaldo', 'Rua 24 de outubro, 312/201', str_to_date('20/04/1980','%d/%m/yyyy'));

INSERT INTO piloto VALUES ('p2', 'Paulo', 'Rua Nilo Peçanha, 804/203', str_to_date('13/11/1990','%d/%m/yyyy'));

INSERT INTO piloto VALUES ('p3', 'Marcos', 'Rua Mariland, 645/302', str_to_date('12/07/1988','%d/%m/yyyy'));

 /* insercao de dados na tabela execucao_voo */
INSERT INTO execucao_voo VALUES ('v1', str_to_date('18/06/2002','%d/%m/yyyy'), 'p2', 70);

INSERT INTO execucao_voo VALUES ('v1', str_to_date('20/09/2002','%d/%m/yyyy'), 'p2', 200);

INSERT INTO execucao_voo VALUES ('v3', str_to_date('10/08/2002','%d/%m/yyyy'), 'p2', 140);

INSERT INTO execucao_voo VALUES ('v4', str_to_date('20/09/2002','%d/%m/yyyy'), 'p4', 100);

INSERT INTO execucao_voo VALUES ('v3', str_to_date('11/11/2002','%d/%m/yyyy'), 'p2', 300);

INSERT INTO execucao_voo VALUES ('v1', str_to_date('22/09/2002','%d/%m/yyyy'), 'p1', 110);

INSERT INTO execucao_voo VALUES ('v5', str_to_date('20/09/2002','%d/%m/yyyy'), 'p3', 145);

INSERT INTO execucao_voo VALUES ('v2', str_to_date('01/09/2002','%d/%m/yyyy'), 'p4', 350);

INSERT INTO execucao_voo VALUES ('v1', str_to_date('23/09/2002','%d/%m/yyyy'), 'p4', 290);

INSERT INTO execucao_voo VALUES ('v1', str_to_date('11/11/2002','%d/%m/yyyy'), 'p4', 125);

INSERT INTO execucao_voo VALUES ('v5', str_to_date('10/11/2002','%d/%m/yyyy'), 'p4', 185);

 /* insercao de dados na tabela cliente */
INSERT INTO cliente_p VALUES ('c1', 'Joao', 'Rua Freire Alemao, 83/501', '(051) 330-9009');

INSERT INTO cliente_p VALUES ('c2', 'Luis', 'Rua Anita Garibaldi, 1001/703', '(051) 330-1009');

INSERT INTO cliente_p VALUES ('c3', 'Carlos', 'Av. Carazinho', 120);

INSERT INTO cliente_p VALUES ('c4', 'Maria', 'Av. Protasio Alves, 3244/303', '(051) 333-7445');

 /* insercao de dados na tabela passagem */
INSERT INTO passagem VALUES ('v5', str_to_date('20/09/2002','%d/%m/yyyy'), '16a', 'c3', str_to_date('12/03/2002','%d/%m/yyyy'));

INSERT INTO passagem VALUES ('v1', str_to_date('20/09/2002','%d/%m/yyyy'), '16b', 'c4', str_to_date('15/05/2002','%d/%m/yyyy'));

INSERT INTO passagem VALUES ('v1', str_to_date('18/06/2002','%d/%m/yyyy'), '24b', 'c3', str_to_date('12/03/2002','%d/%m/yyyy'));

INSERT INTO passagem VALUES ('v3', str_to_date('10/08/2002','%d/%m/yyyy'), '13a', 'c4', str_to_date('10/05/2002','%d/%m/yyyy'));

INSERT INTO passagem VALUES ('v4', str_to_date('20/09/2002','%d/%m/yyyy'), '19c', 'c3', str_to_date('13/06/2002','%d/%m/yyyy'));

INSERT INTO passagem VALUES ('v3', str_to_date('10/08/2002','%d/%m/yyyy'), '1a', 'c1', str_to_date('20/03/2002','%d/%m/yyyy'));

INSERT INTO passagem VALUES ('v3', str_to_date('11/11/2002','%d/%m/yyyy'), '2a', 'c2', str_to_date('12/09/2002','%d/%m/yyyy'));

INSERT INTO passagem VALUES ('v5', str_to_date('20/09/2002','%d/%m/yyyy'), '21a', 'c1', str_to_date('05/04/2002','%d/%m/yyyy'));

INSERT INTO passagem VALUES ('v5', str_to_date('10/11/2002','%d/%m/yyyy'), '11a', 'c3', str_to_date('15/04/2002','%d/%m/yyyy'));

INSERT INTO passagem VALUES ('v4', str_to_date('20/09/2002','%d/%m/yyyy'), '19a', 'c2', str_to_date('02/06/2002','%d/%m/yyyy'));

INSERT INTO passagem VALUES ('v1', str_to_date('11/11/2002','%d/%m/yyyy'), '5a', 'c3', str_to_date('23/06/2002','%d/%m/yyyy'));

A questão que tenho que fazer é essa:

 

Recuperar o código e nome de clientes que marcaram passagem em pelo menos todos os vôos comandados pelo piloto Ronaldo, que saíram de Porto Alegre.

 

O retorno é:

COD_CLI    NOME
---------- ------------------------------
c3         Carlos

Eu não tenho ideia de como fazer isso, eu tentei iniciar, mas sempre me retorna todos os clientes, pois eles já viajaram com o piloto Ronaldo. Não consegui desenvolver a lógica para retornar apenas os que viajaram em todos os voos de Ronaldo.

 

Obrigado pela ajuda.

[Fernando C 128]

Não consegui desenvolver a lógica para retornar apenas os que viajaram em todos os voos de Ronaldo.

 

amigo... se entendi bem, observe que não interessam "todos os voos", mas:

 

que saíram de Porto Alegre.

 

a logica, portanto:

select * from tabelas where piloto='Ronaldo' AND local_saida='Porto Alegre';

agora é contigo; tenta "fechar" ai pq tem - claro - que obter dados de várias tabelas..

[gremio10 14]

Eu consegui chegar nessa Query:

SELECT
	cliente_p.cod_cli,
	cliente_p.nome
FROM
	piloto
INNER JOIN execucao_voo ON execucao_voo.cod_piloto = piloto.cod_piloto
INNER JOIN passagem ON passagem.num_voo = execucao_voo.num_voo
INNER JOIN cliente_p ON cliente_p.cod_cli = passagem.cod_cli
INNER JOIN voo ON voo.num_voo = passagem.num_voo
WHERE
	piloto.cod_piloto = 'p4' AND voo.cidade_part = 'Porto Alegre'
GROUP BY
	cliente_p.cod_cli

Só que eu não consigo distinguir qual foi em todos os voos do Ronaldo, meu resultado foi:

cod_cli   nome
c2        Luis                          
c3        Carlos                        
c1        Joao                        

Como identificar que apenas o Carlos que sempre viajou com o piloto Ronaldo?

 

Obrigado.

[Motta 645]

http://forum.imasters.com.br/topic/526307-relacionar-duas-tabelas-e-quantidades-de-dados-diferentes/

 

 

Veja se ajuda

[gremio10 14]

@Motta, esse seu post me ajudou muito, consegui resolver a questão que estava com dúvida e agora entrei em um outra um pouco mais complicada, que gostaria de saber se poderia me dar uma luz.

 

e) Recuperar o código e nome de clientes que marcaram passagem em pelo menos todos os vôos comandados pelo piloto Ronaldo, que saíram de Porto Alegre. Selecionar somente aqueles clientes que tenham mais de uma passagem marcada até o final do ano em vôos ainda não executados.

COD_CLI    NOME
---------- --------------------------
c3         Carlos

Eu cheguei até aqui:

SELECT DISTINCT cliente_p.cod_cli, cliente_p.nome FROM cliente_p
INNER JOIN passagem ON passagem.cod_cli = cliente_p.cod_cli

EXCEPT

SELECT DISTINCT cliente_p.cod_cli, cliente_p.nome FROM cliente_p
RIGHT JOIN passagem ON passagem.cod_cli = cliente_p.cod_cli
RIGHT JOIN execucao_voo ON execucao_voo.num_voo = passagem.num_voo
RIGHT JOIN piloto ON piloto.cod_piloto = execucao_voo.cod_piloto
RIGHT JOIN voo ON voo.num_voo = execucao_voo.num_voo
WHERE piloto.cod_piloto = 'p4' AND voo.cidade_part = 'Porto Alegre'

Mas o retorno que tenho é

COD_CLI    NOME
---------- --------------------------
c4         Maria

Poderia me ajudar, por favor.

 

Obrigado.

[Motta 645]

Mas esta sua query está errada !?

 

O que faz este "EXCEPT" !?

 

Da primeira questão só muda a restrição da cidade (um where resolve) e da quantidade de passagens (um having deve resolver).

[gremio10 14]

A primeira questão eu resolvi assim:

SELECT
		cliente_p.cod_cli,
		cliente_p.nome
	FROM
		piloto
	INNER JOIN execucao_voo ON execucao_voo.cod_piloto = piloto.cod_piloto
	INNER JOIN passagem ON passagem.num_voo = execucao_voo.num_voo
	INNER JOIN cliente_p ON cliente_p.cod_cli = passagem.cod_cli
	INNER JOIN voo ON voo.num_voo = passagem.num_voo
	WHERE
		piloto.cod_piloto = 'p4' AND voo.cidade_part = 'Porto Alegre'
	GROUP BY
		cliente_p.cod_cli
	HAVING COUNT( DISTINCT passagem.num_voo ) = ( SELECT COUNT( DISTINCT execucao_voo.num_voo ) FROM execucao_voo INNER JOIN piloto ON piloto.cod_piloto = execucao_voo.cod_piloto INNER JOIN voo ON voo.num_voo = execucao_voo.num_voo WHERE piloto.cod_piloto = 'p4' AND voo.cidade_part = 'Porto Alegre' );

Bem como tu falou, usando o WHERE pra cidade e um HAVING, foi sem problema.

 

O que não estou conseguindo é usar como base a primeira questão para chegar ao resultado da segunda, mesmo sendo o mesmo resultado, a QUERY deve ser modificada para o que a questão solicita.

 

Obrigado.