trabalho de banco de dados da Cristiane

cristiane

CREATE TABLE Autores (
  codigo int not null,
  nome varchar(255),
  nacionalidade varchar(255),
  PRIMARY KEY (codigo)
);

CREATE TABLE Saloes (
  numero int not null,
  andar int,
  area int,
  PRIMARY KEY (numero)
);

CREATE TABLE Obras (
  codigo int not null,
  titulo varchar(255),
  ano int,
  autor int not null,
  salao int not null,
  PRIMARY KEY (codigo),
  FOREIGN KEY (autor) REFERENCES Autores(codigo),
  FOREIGN KEY (salao) REFERENCES Saloes(numero)
);

CREATE INDEX obras_autor
ON Obras(autor);

CREATE INDEX obras_salao
ON Obras(salao);

CREATE TABLE Pinturas (
  codigo int not null,
  estilo varchar(255),
  area int,
  PRIMARY KEY (codigo),
  FOREIGN KEY (codigo) REFERENCES Obras(codigo)
);

CREATE INDEX pinturas_codigo
ON Pinturas(codigo);

CREATE TABLE Esculturas (
  codigo int not null,
  altura int,
  peso int,
  material varchar(255),
  PRIMARY KEY (codigo),
  FOREIGN KEY (codigo) REFERENCES Obras(codigo)
);

CREATE INDEX esculturas_codigo
ON Esculturas(codigo);

CREATE TABLE Funcionarios (
  RG varchar(20) not null,
  nome varchar(255),
  salario number(19,4),
  turno char check (turno in ('M', 'T', 'N')),
  funcao varchar(20) check (funcao in ('segurança', 'faxineiro')),
  PRIMARY KEY (RG)
);

CREATE TABLE Lotacoes (
  RG varchar(20) not null,
  numero int not null,
  horaEntrada timestamp,
  horaSaida timestamp,
  PRIMARY KEY (RG, numero, horaEntrada),
  FOREIGN KEY (RG) REFERENCES Funcionarios(RG),
  FOREIGN KEY (numero) REFERENCES Saloes(numero)
);

CREATE INDEX lotacoes_rg
ON Lotacoes(RG);

CREATE INDEX lotacoes_numero
ON Lotacoes(Numero);

-- Buscar o código e o título das obras de 1965 a 1975 que estão no salão 36.
select ob.CODIGO, ob.TITULO from OBRAS ob
inner join SALOES s on ob.SALAO = s.NUMERO
where ob.ANO between 1965 and 1975
and s.NUMERO = 36;

-- Buscar o código e o título das obras do autor Pablo Picasso que se encontram no terceiro andar do museu.
select ob.CODIGO, ob.TITULO from OBRAS ob
inner join SALOES s on ob.SALAO = s.NUMERO
inner join AUTORES a on ob.AUTOR = a.CODIGO
where a.NOME = 'Pablo Picasso' and
s.ANDAR = 3;

-- Buscar o código e o título das obras impressionistas ou cujo material de fabricação é argila.
select ob.CODIGO, ob.TITULO from OBRAS ob
inner join ESCULTURAS e on ob.CODIGO = e.CODIGO
inner join PINTURAS p on ob.CODIGO = p.CODIGO
where p.ESTILO = 'impressionista' or
e.MATERIAL = 'argila';

-- Buscar o nome e a nacionalidade dos autores que possuem obras expostas no museu.
-- Subconsulta
select a.NOME, a.NACIONALIDADE from AUTORES a
where a.CODIGO in (select ob.AUTOR from OBRAS ob);
-- Junção
select a.NOME, a.NACIONALIDADE from AUTORES a
inner join OBRAS ob on a.CODIGO = ob.AUTOR;