Q473938 - CESPE - 2016 - TRT - 8ª Região (PA e AP) - Analista Judiciário - Tecnologia da Informação

CREATE TABLE predio

(

id numeric(7,0),

nome varchar(50),

local varchar(150),

mnemonico varchar(10),

CONSTRAINT pk_sede PRIMARY KEY (id),

CONSTRAINT uq_sede UNIQUE (mnemonico)

);

CREATE TABLE salas

(

codigo numeric(7,0) NOT NULL,

local varchar(10),

descricao varchar(50),

area numeric(10,2),

CONSTRAINT pk_salas PRIMARY KEY (codigo),

CONSTRAINT fk_sede_sala FOREIGN KEY (local)

REFERENCES predio (mnemonico)

);

Considerando os algoritmos acima, em que são criadas as tabelas

predio e salas, assinale a opção cuja expressão SQL apresenta

informações do registro da maior sala existente.

a)
select c1.local, c1.nome, c2.descricao, c2.area from predio as c1, salas as c2 where c2.local=c1.mnemonico having max(c2.area)
b)
select c1.local, c1.nome, c2.descricao, max(c2.area) from predio as c1, salas as c2 where c2.local=c1.id group by c1.local, c1.nome, c2.descricao
c)
select c1.local, c1.nome, c2.descricao from predio as c1, ( select local, descricao, area from salas as c1 where area = (select max(area) from salas as c2 where area>0) ) as c2 where c2.local=c1.mnemonico;
d)
select c1.local, c1.nome, c2.descricao, c2.area from predio as c1, ( select local, descricao, area from salas as c1 where area = (select max(area) from salas as c2 where area>0) ) as c2 where c2.id=c1.codigo;
e)
select c1.local, c1.nome, c2.descricao, max(c2.area) from predio as c1 join salas as c2 on c2.codigo=c1.id group by c1.local, c1.nome, c2.descricao

Banco de Dados (TI)