Trabalho com querys

[Brunno]

Por Favor estou em rolado nas consultas ao banco pra verificar se esta certo. alguém podera me ajudem por favor.....

e o projeo de conclusao de curso... :wacko:

CREATE TABLE cliente (
   cpf_cnpj varchar(14) NOT NULL,
   nome varchar(20) NOT NULL,
   bairro varchar(20),
   rua varchar(20),
   PRIMARY KEY  (cpf_cnpj)
 ) 

 CREATE TABLE contrato(
   nºcontrato int NOT NULL identity(1,1),
   chassi varchar(30) NOT NULL,
   datainicio datetime,
   datafim datetime,
   matricula int not null,
   PRIMARY KEY (nºcontrato),
   FOREIGN KEY (chassi) REFERENCES veiculo,
   FOREIGN KEY (matricula) REFERENCES funcionario,
 ) 

 CREATE TABLE cor(
   idcor int NOT NULL identity (1,1),
   descricao varchar(20),
   PRIMARY KEY (idcor)
 ) 

 CREATE TABLE fabricante (
   cnpj varchar (14) NOT NULL,
   razaosocial varchar(20) NOT NULL,
   PRIMARY KEY (cnpj)
 ) 

 CREATE TABLE funcionario (
   matricula int NOT NULL identity (1,1),
   nome Varchar (20) not null,
   salario money,
   dataAdmi datetime,
   bairro varchar(20),
   nivelInstrucao varchar(20) NOT NULL,
   rua varchar(20) default NULL,
   PRIMARY KEY  (matricula)
 ) 

 CREATE TABLE funcionarioOrcamento (
  matricula int NOT NULL,
   nºservico int NOT NULL,
   data_autorizacao datetime NOT NULL,
   data_entrada datetime NOT NULL,
   valor money,
   observacoes varchar(20),
   FOREIGN KEY (matricula) REFERENCES funcionario, 
   FOREIGN KEY (nºservico) REFERENCES orcamento 
 ) 

 CREATE TABLE modelo (
   codmodelo int NOT NULL identity (1,1),
   descricao varchar(20) NOT NULL,
   cnpj varchar (14) NOT NULL,
   PRIMARY KEY  (codmodelo),
   FOREIGN KEY (cnpj) REFERENCES fabricante 
 ) 

 CREATE TABLE oficina (
   cnpj varchar (14) NOT NULL ,
   nome varchar(20) not null,
   gerente varchar(20) not null,
   rua varchar(20),
   bairro varchar(20),
   PRIMARY KEY (cnpj)
 ) 

 CREATE TABLE orcamento (
   nºservico int NOT NULL identity (1,1),
   chassi varchar (30) NOT NULL,
   descricao varchar(20),
   cnpj varchar (14) NOT NULL,
   PRIMARY KEY  (nºservico),
   FOREIGN KEY (cnpj) REFERENCES oficina,
   FOREIGN KEY (chassi) REFERENCES veiculo 
 ) 

 CREATE TABLE orcamento_produto (
   codigo int NOT NULL,
   nºservico int NOT NULL,
   FOREIGN KEY (codigo) REFERENCES produtos, 
   FOREIGN KEY (nºservico) REFERENCES orcamento 
 ) 

 CREATE TABLE pagamento (
   nºnota int NOT NULL identity (1,1),
   valorparcela money,
   nºparcela int not null,
   vencimento datetime NOT NULL,
   datapagamento datetime NOT NULL,
   nºcontrato int not null,
   PRIMARY KEY  (nºnota),
   FOREIGN KEY (nºcontrato) REFERENCES contrato 
 )

 CREATE TABLE produtos (
   codigo int NOT NULL identity (1,1),
   itens_pecas varchar(20) NOT NULL,
   PRIMARY KEY  (codigo)
 ) 

 CREATE TABLE renovacao (
   bonus varchar(20) NOT NULL,
   nºcontrato int NOT NULL ,
   nºcontratonovo int NOT NULL,
   primary key (nºcontrato,nºcontratonovo), 
   FOREIGN KEY (nºcontrato) REFERENCES contrato
 ) 

 CREATE TABLE seguradora (
   cnpj varchar(14) NOT NULL,
   rua varchar(20),
   bairro varchar(20),
   nome varchar(20) NOT NULL,
   PRIMARY KEY  (cnpj)
 ) 

 CREATE TABLE telefone_cliente (
   telefone varchar(8) NOT NULL,
   cpf_cnpj varchar (14) NOT NULL,
   FOREIGN KEY (cpf_cnpj) REFERENCES cliente 
 ) 

 CREATE TABLE telefone_funcionario (
   matricula int  NOT NULL,
   telefone varchar(8),
   FOREIGN KEY (matricula) REFERENCES funcionario 
 ) 

 CREATE TABLE telefone_oficina (
   telefone varchar(8) NOT NULL,
   cnpj varchar (14) NOT NULL,
   FOREIGN KEY (cnpj) REFERENCES oficina 
 ) 
 CREATE TABLE telefone_seguradora (
   telefone varchar(8),
   cnpj varchar (14) NOT NULL,
   FOREIGN KEY (cnpj) REFERENCES seguradora 
 )

  CREATE TABLE veiculo (
   chassi varchar (30) NOT NULL,
   ano datetime NOT NULL,
   responsavel varchar(20) NOT NULL,
   idcor int NOT NULL ,
   codmodelo int NOT NULL,
   cpf_cnpj varchar(14) NOT NULL,
   placa varchar(20),
   PRIMARY KEY  (chassi),
   FOREIGN KEY (idcor) REFERENCES cor,
   FOREIGN KEY (codmodelo) REFERENCES modelo,
   FOREIGN KEY (cpf_cnpj) REFERENCES cliente
 )

--Consultas. --1. Consulta que liste todos os veículos cadastrados na base de dados. A --consulta deve apresentar: Chassi, Placa, Ano, cor (descrição), modelo --(descrição) e marca (descrição). Ordene o resultado pelo Chassi.

select v.chassi, v.placa,v.ano, c.descricao, m.descricao
 from cor c, veiculo v, modelo m
 where c.idcor = v.idcor and v.codmodelo = m.codmodelo
 order by v.chassi

--------------------------------------------------------------------------- --2.Consulta que liste o último contrato realizado. A consulta deve exibir o --número do contrato, a data de início e fim, o corretor responsável pelo --contrato e o veículo segurado (Chassi e Placa).

select c.nºcontrato,c.datainicio,c.datafim, 
 f.matricula,f.nome,v.chassi,v.placa
 from veiculo v, contrato c, funcionario f
 where v.chassi = c.chassi and c.matricula = f.matricula 
 order by  v.placa, v.chassi

-------------------------------------------------------------------------- --3.Consulta que totaliza a quantidade de contratos por corretor. A consulta --deve apresentar as seguintes informações: matrícula do corretor, nome do --corretor e quantidade de contratos realizados. Ordene as informações pela --matrícula do corretor.

select f.nome,c.nºcontrato 
 from contrato c ,funcionario f
 where c.matricula = f.matricula
 order by f.matricula

---------------------------------------------------------------------------

Editado Maio 31, 2010 por quintelab
Adicionar tag CODE

[quintelab]

Título alterado de Ajudar para Trabalho com querys

[fulvio]

Bom dia Bruno,

É.... agora vou dar uma de PAI: não gosto de ajudar quando é trabalho, pois a pessoa está aprendendo e o mercado é cruel!!! A hora é esta meu amigo.

As consultas relativamente são simples. Precisa de conhecer poucos comandos e o fundamental: lógica.

Como não tenho dados para inserir nas tabelas, fiz os selects e não os testei. Dê uma olhada se eles retornarão as informações corretas.

Fiz as consultas e expliquei. Entenda e estude bem!! Tem ai as provas finais e o MERCADO (este ai é uma prova todos os dias... rs).

Vamos lá:

1º --> O seu join deve estar retornando um exponencial, pois possuia uma tabela referenciada e não havia o seu relacionamento na cláusula WHERE. Refeito:

select v.chassi, v.placa,v.ano, c.descricao, m.descricao, f.razaosocial
from veiculo v, cor c, modelo m, fabricante f
where v.idcor = c.idcor and v.codmodelo = m.codmodelo and m.cnpj=f.cnpj
order by v.chassi

2º --> O seu join ordenava por placa e chassi. O enunciado estava querendo o ultimo contrato. Ordenar pela data de inicio decrescente (do maior pro menor) e pegar o 1º registro lhe restornará o valor desejado. Se não for pela datainicio, só ordernar pela datafim. Refeito:

select top 1 c.nºcontrato, c.datainicio, c.datafim, f.matricula, f.nome, v.chassi, v.placa
from contrato c, funcionario f, veiculo v
where c.matricula = f.matricula and c.chassi = v.chassi
order by c.datainicio desc

3º --> Esse aí você nem mexeu... rs. Para somar a quantidade de contratos, você deve utilizar a função SUM. Esta função obriga a utilização do comando GROUP BY (caso tenha outras colunas sendo referenciadas além da coluna que será somada). Refeito:

select f.matricula, f.nome, sum(c.nºcontrato)
from funcionario f, contrato c
where f.matricula = c.matricula
group by f.matricula, f.nome
order by f.matricula

Dá uma olhada nestes scripts e vê se funciona. Qualquer dúvida, pode postar. :.)