Jump to content
Sign in to follow this  
orianis

PostgreSQL

Recommended Posts

orianis

Com estas relaçoes:

telefone(Nbi,Telefone)

modelo(Marca, Modelo, Nlugares, Consumo)

taxi(Modelo, Ano, Kms, Matricula)

servico(DataInicio, DataFim, Kms, Valor, Matricula)

turno(DataInicio, DataFim, KmInicio, KmFim, Matricula, Nbi)

cliente(Nome, Morada, CodigoPostal, Nif)

pedido(Nif,MoradaInicio, CodigoPostalInicio, DataPedido, matricula, DataInicio)

como respondo a:

Quais os nomes dos motoristas que nunca fizeram um servico para satisfazer um pedido

do Sr José Silva?

Eu ja tenho o codigo em SQL mas deve estar mal... nao me dá o resultado esperado...

select distinct nome as nome_m

from Motorista

except all

select nome as nome_c

from Cliente natural inner join Servico natural inner join Pedido

where nome like '%José Silva%';

se alguem puder ajudar :D

Share this post


Link to post
Share on other sites
Baderous

Será melhor se apresentares as tabelas todas e com as chaves primárias e estrangeiras identificadas. Na tua query estás a referir-te à tabela Motorista que nem sequer está aí listada.

Share this post


Link to post
Share on other sites
orianis

Podes crer esqueci-me... Mas tambem nao sei se devia meter aqui isto tudo lol  :fartnew2:

create table Motorista (

nome varchar(50),

NCartaCond varchar(10),

DataNasc varchar(20),

Nbi integer,

primary key (NBi));

create table Telefone(

Nbi integer,

Telefone integer,

primary key (Nbi,Telefone),

foreign key (Nbi) references Motorista on delete restrict);

create table Modelo(

marca varchar(50),

modelo varchar(50),

nlugares integer,

consumo float,

primary key (modelo));

create table Taxi(

modelo varchar(50),

ano integer,

Kms integer,

matricula varchar(10),

primary key (matricula),

foreign key (modelo) references Modelo on delete restrict);

create table Servico(

DataInicio varchar(20),

DataFim varchar(20),

Kms integer,

Valor float,

matricula varchar(10),

primary key (DataInicio,matricula),

foreign key (matricula) references Taxi on delete restrict);

create table Turno(

DataInicio varchar(20),

DataFim varchar(20),

KmInicio integer,

KmFim integer,

matricula varchar(10),

NBi integer,

primary key (DataInicio,Nbi),

foreign key (matricula) references Taxi on delete restrict,

foreign key (NBi) references Motorista on delete restrict);

create table Cliente(

nome varchar(50),

morada varchar(50),

CodigoPostal varchar(30),

NIF varchar(30),

primary key (NIF));

create table Pedido(

NIF varchar (30),

MoradaInicio varchar(50),

CodigoPostalInicio varchar(30),

DataPedido varchar(20),

matricula varchar(10),

DataInicio varchar(20),

primary key (MoradaInicio,NIF),

foreign key (matricula) references Taxi on delete restrict,

foreign key (NIF) references Cliente on delete restrict);

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.