SELECT DEREF(ss.COLUMN_VALUE) FROM THE(SELECT produtos FROM fornecedor_table p
 WHERE p.codigopessoa = 1) ss;

SELECT DEREF(ss.COLUMN_VALUE) FROM THE(SELECT produtos FROM fornecedor_table p
 WHERE p.codigopessoa = 1) ss where VALUE(ss).codigo = 1;

 select deref(p.column_value).nome from fornecedor_table f , table(f.produtos) p
 where Value(p).nome = 'Chocolate';

/*deleta da tabela aninhada do fornecedor que possui codigo = 1.*/
delete FROM THE(SELECT produtos FROM fornecedor_table p
 WHERE p.codigopessoa = 1) ss where VALUE(ss).codigo = 0;

delete from the(select funcionarios from setor_table s where s.nome = 'Limpeza') F Where F.nome = 'Daileon';


/* --------------------------------- Consultas Sobre Funcionario ------------------------------------------ */

/*Operação de deletar Funcionario*/
delete from the(select funcionarios from setor_table S where S.nome = 'Administração') F where F.codigopessoa = 1;

/*Lista todos os funcionarios*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomefuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F

/*Seleciona Funcionario pelo seu codigo*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomefuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F
WHERE F.codigopessoa = 1;


/*Seleciona Funcionario pelo seu nome*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomefuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F
WHERE F.nome = 'Xerxes';--LIKE '%Xerxes%';

/*Numero De funcionarios inativos*/
select count(*) from Setor_table S, table(S.funcionarios) F where F.Ativo = 'N';

/*numero de funcionarios ativos*/
select count(*) from Setor_table S, table(S.funcionarios) F where F.Ativo = 'S';

/*total de funcionarios*/
select count(F.codigopessoa) from Setor_table S, table(S.funcionarios) F;

/*seleciona funcionarios em ordem decrescente pelo salario*/
SELECT F.nome, deref(f.funcao).nome as nomefuncao, deref(f.funcao).salariobase as salariobase
from Setor_Table S, table(S.Funcionarios) F order by deref(f.funcao).salariobase desc;

/*seleciona funcionarios em ordem decrescente pelo valor da hora extra*/
SELECT F.nome, F.valor_hora_extra, deref(f.funcao).nome as nomefuncao from Setor_Table S,
table(S.Funcionarios) F order by F.valor_hora_extra desc;

/*seleciona funcionarios em ordem decrescente por horas trabalhadas*/
SELECT F.nome, F.horas_trabalhadas from Setor_Table S, table(S.Funcionarios) F order by F.horas_trabalhadas desc;

/*---------------------------------------------Consultas relativas a setor Setor---------------------------------------*/

/*Seleciona os funcionarios de um setor*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomeFuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F
WHERE S.nome = 'Administração';

/*Seleciona nome e funcção dos funcionarios por setor*/
SELECT F.nome, deref(f.funcao).nome as nomeFuncao from Setor_Table S, table(S.Funcionarios) F
WHERE S.nome = 'Comida';

/*seleciona todos os funcionarios inativos por setor*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomefuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F where S.nome = 'Administração' and F.ativo = 'N';

/*seleciona todos os funcionarios ativos por setor*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomefuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F where S.nome = 'Limpeza' and F.ativo = 'S';

/*Seleciona o funcionario que possui o maior salario no setor*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomefuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F
WHERE S.nome = 'Administração' and deref(F.funcao).salariobase = (Select max(deref(F.funcao).salariobase) from
Setor_table S,  table(S.funcionarios) F where S.nome = 'Administração' );

/*Seleciona o numero de funcionarios do setor por turno*/
SELECT count(*) as numeroDeFuncionariosPorTurno FROM the(Select funcionarios from Setor_table S where S.nome = 'Limpeza') F
where F.turno_trabalho.manha = 'S';

/*Seleciona os funcionarios de um setor por turno*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomeFuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F
WHERE S.nome = 'Limpeza' and F.turno_trabalho.manha = 'S';

/*-----------------------------------------Consultas relativas a Função----------------------------*/

/*Selecina os fucionarios pela sua função.*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomefuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F
WHERE deref(f.funcao).nome = 'Faxineiro';

/*Quantidade de funcionarios com determinada função*/
SELECT count(*) as NumeroDeFuncionariosPorFuncao from Setor_Table S, table(S.Funcionarios) F
WHERE deref(f.funcao).nome = 'Gerente';

/*Seleciona os funcionarios inativos por função*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomefuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F
WHERE deref(f.funcao).nome = 'Faxineiro' and F.Ativo = 'N';

/*Seleciona os funcionarios ativos por função*/
SELECT F.nome, F.codigopessoa, deref(f.funcao).nome as nomefuncao, deref(f.funcao).salariobase as salariobase,
S.nome from Setor_Table S, table(S.Funcionarios) F
WHERE deref(f.funcao).nome = 'Faxineiro' and F.Ativo = 'S';

/*Operação de deletar uma função*/
DELETE FROM Funcao_table WHERE nome = 'Presidente';

/*--------------------------------------------Consultas relativas a quarto-------------------------------------*/

/*Deleta um quarto pelo seu codigo*/
DELETE FROM Quarto_table WHERE codigo = 101;

/*Seleciona todos os quartos*/
SELECT q.codigo, q.disponivel, DEREF(q.tipodequarto).nomedotipo, DEREF(q.tipodequarto).precobase FROM quarto_table q;

/*Numero de quartos disponíveis*/
SELECT count(*) from quarto_table where disponivel = 's';

/*Numero de quartos disponíveis por tipo de quarto*/
SELECT count(*) from quarto_table Q WHERE disponivel = 's' and DEREF(Q.tipoDeQuarto).nomeDoTipo = 'luxo-Casal';

/*Seleciona o codigo dos quartos pelo tipo*/
SELECT Q.codigo from quarto_table Q WHERE DEREF(Q.tipoDeQuarto).nomeDoTipo = 'luxo-Casal';

/*Quarto e a quantidade de cada produto consumido por esse quarto*/
select deref(c.produto).nome as Produto,c.datadavenda, c.quantidadevendida, deref(c.quarto).codigo from cliente_table a,
table(a.reservas) b, table(b.itensdavenda) c where b.codigo = 1 /*and deref(c.quarto).codigo = 1*/;

/*Quantidade de produtos vendida para um quarto*/
select b.codigo, deref(c.quarto).codigo as QuantidadeDeProdutos from cliente_table a,
table(a.reservas) b, table(b.itensdavenda) c where b.codigo = 1 /*and deref(c.quarto).codigo = 1*/;

/*----------------------------------------Consultas relativas a cliente--------------------------------------*/

/*Seleciona todas as reservas mostrando o nome do cliente e o codigo da reserva.*/
SELECT C.nome, R.codigo as CodigoDaReserva, R.valorfinal FROM  Cliente_table C, table(C.reservas) R;

/*Seleciona todas as reservas feitas por um cliente.*/
SELECT C.nome, C.codigoPessoa, R.codigo as CodigoDaReserva, R.valorfinal FROM Cliente_table C,
the(SELECT Reservas FROM Cliente_table C WHERE C.codigopessoa = 1) R
WHERE c.codigopessoa = 1;

/*Seleciona a quantidade de reservas feitas por um cliente.*/
SELECT C.nome, C.codigopessoa, COUNT(R.codigo) AS QuantidadeDeReservasPorCliente FROM Cliente_table C,
the(SELECT Reservas FROM Cliente_table C WHERE C.codigopessoa = 1) R
WHERE c.codigopessoa = 1;

/*----------------------------------------Consultas relativas a reservas--------------------------------------*/
/*Seleciona os quartos de uma reserva pelo codigo da reserva*/
SELECT DEREF(VALUE(Q)) FROM the(SELECT Quartos FROM Cliente_table C, table(C.reservas) R WHERE R.codigo = 1) Q;

/*Numero de quartos de uma reserva pelo codigo da reserva*/
SELECT count(DEREF(VALUE(Q)).codigo) as NumeroDeQuartos FROM the(SELECT Quartos FROM Cliente_table C,
table(C.reservas) R WHERE R.codigo = 1) Q;

/*Seleciona as reservas que possuem data de chegada entre os intervalos especidifacos*/
SELECT C.nome, R.codigo as CodigoDaReserva, R.valorfinal FROM  Cliente_table C, table(C.reservas) R
WHERE R.dataDeChegada BETWEEN to_date('26/3/1973','DD/MM/YYYY') AND to_date('26/3/1973','DD/MM/YYYY');

/*Seleciona as reservas que possuem data de saida entre os intervalos especidifacos*/
SELECT C.nome, R.codigo as CodigoDaReserva, R.valorfinal FROM  Cliente_table C, table(C.reservas) R
WHERE R.dataDeSaida BETWEEN to_date('26/3/1973','DD/MM/YYYY') AND to_date('26/3/1973','DD/MM/YYYY');

/*------------------------------------------Consultas Lupin--------------------------------------------------*/

/*Consultas*/

/******************************Simples**************************************/
/*Produto*/
select codigo,nome,quantidade,precodecusto,precodevenda,fornecedores from produto_table;

/*Fornedor*/
select nome, codigopessoa, produtos,remessas from fornecedor_table;
select nome, codigopessoa from fornecedor_table;

/*Reservas*/
select b.codigo,b.datadechegada,b.datadesaida,b.itensdavenda as Itens_Da_Venda from cliente_table a, table(a.reservas) b;

/*Remessas*/
select nome,codigopessoa,remessas from fornecedor_table;


/*Aniversariantes do mes*/

/*Cliente*/
select  nome from cliente_table where to_char(DATA_nascimento,'MM') = 05;

/*Fornecedor*/
select  nome,data_nascimento from fornecedor_table where to_char(DATA_nascimento,'MM') = 10;

select  nome, codigopessoa from fornecedor_table;

/*Funcionarios*/
select f.nome, f.data_nascimento from Setor_table S, Table(S.funcionarios) F where to_char(Data_nascimento, 'MM') = 5;

/*---------------------------------------------------------------------------------------------------------------*/

select b.nome,b.data_nascimento from setor_table a,table(a.funcionarios) b where to_char(DATA_nascimento,'MM') = 10;

/*Produtos por setor*/
select a.nome from produto_table a where (deref(a.setor)).nome = 'Limpeza';

/*Todos os produtos e o nome do setor.*/
select a.nome,(deref(a.setor)).nome as Setor from produto_table a;
select a.nome, a.codigo, (deref(a.setor)).nome as Setor from produto_table a;

/*Produtos com quantidade abaixo de x */
select a.nome, a.quantidade from produto_table a where a.quantidade < 201;

/*Produtos por fornecedor*/
select a.nome, a.codigo as NomeProduto,deref(b.column_value).nome from produto_table a,table(a.fornecedores) b;

/*Produtos vendidos por data */
select deref(c.produto).nome as Produto,c.datadavenda, c.quantidadevendida from cliente_table a,table(a.reservas) b,table(b.itensdavenda) c where
c.datadavenda > to_date('15/6/2006','DD/MM/YYYY');

CREATE OR REPLACE PROCEDURE InserirRefCruzada
(p_fornecedor  fornecedor_table.nome%TYPE,
 p_produto produto_table.nome%TYPE
 )
  AS
p_cf integer;
p_cp integer;
 BEGIN

Insert into  the(select fornecedores from produto_table b where b.nome = p_produto) select ref(f) from fornecedor_table f where f.nome = p_fornecedor;
Insert into  the(select produtos from fornecedor_table b where b.nome = p_fornecedor) select ref(f) from produto_table f where f.nome = p_produto;

 COMMIT;
 END;
/

/* Colocar referencia em produto */
update produto_table set setor = (select ref(a) from setor_table a where a.nome = 'Comida') where nome = 'Chocolate';

select nome from produto_table;

select nome from fornecedor_table where data_nascimento > to_date('26/3/1973','DD/MM/YYYY');