x1--diz qual detento tem determinada tatuagem
select d.nome from detento d,caracfisicas c
	where
	d.num_ref_carac = numref
	and c.tatuagem = 'dragão'

x2--diz quais os presos tem Silva no nome
select d.nome  from detento d
	where
	d.nome like '%Silva%'

x3--diz os presos que tem pai ou mãe desconhecidos
select d.nome  from detento d
	where
	d.nome_pai like 'desconhecid_'
	or d.nome_mae like 'desconhecid_'

4--porcentagem de negros (só no sqlserver)
select count(d.nome)*100/
       (
                 select count(*)from detento
        ) as negros from detento d,caracfisicas c
        where 
        c.numref = d.num_ref_carac
        and c.cor like 'negr_'

5--listar os presos de uma cela
select d.nome,c.numcela from detento d,cela c
	where
	c.numcela = 5
	and d.numcela = 5

x6--funcionarios que controlam setores
select f.nome from funcionario f,setor s where
f.setor = s.numSetor and f.controlaSetor = 's'


x7--funcionarios q cuidam de celas especiais
select f.nome from funcionario f,cela c
	where
	f.rg = c.rgresponsavel and c.eh_especial = 's'

8--ordenar os funcionarios por sexo
select f.nome,f.sexo from funcionario f order by sexo

9--listar os presos associados às ocorrências que suas visitas
tentaram levar
select distinct d.nome as detento,r.ocorrencia,guest.nome as portador
from detento d,revista r,visitantes guest,visitas v
	where
	r.ocorrencia != 'sem ocorrencias'
	and r.rgvisitante = v.rgvisita
	and v.rgdetento = d.rg
	and guest.rgvisitante  = v.rgvisita

10--listar os telefones dos advogados de todos os presos de uma
tal cela.
select tel.telefone,adv.nome from telefonesadvogado tel,
detento d,cela c,ehdefendidopor defenc, advogado adv
	where
	c.numcela = 5
	and c.numcela = d.numcela
	and d.rg = defenc.rgdetento
	and defenc.oabadv = tel.regoabadv
	and defenc.oabadv = adv.numoab

11--Listar a quantidade de vagas de uma dada Cela

select capacidade - (
			select count(*) from detento d,cela c
				where
				c.numcela = 5
				and d.numcela = 5
		     ) from cela
	where
	numcela = 5


12--Listar as datas de entradas dos presos (formato brasileiro)
select d.nome,convert(char,d.dat_entrada,103) as Entrada from detento d


13--Listar os detentos que saírão da prisão em 2003
select d.nome, convert(char,dat_saida,103)as Saída from detento d
       where
       year(d.dat_saida)='2003'


14--Listar os responsáveis pelas celas
select f.nome,c.numcela,c.capacidade from funcionario f,cela c
	where
	c.rgresponsavel = f.rg


15--Listar os advogados que têm algum telefone cadastrado
select a.nome as Advogados_Com_Telefone_Cadastrado from advogado a
	where
	exists (
			select * from telefonesadvogado tad
			where
			a.numoab = tad.regoabadv
		)



16--Listar os advogados que não têm algum telefone cadastrado
select a.nome as Advogados_Com_Telefone_Cadastrado from advogado a
	where
	not exists (
			select * from telefonesadvogado tad
			where
			a.numoab = tad.regoabadv
		   )

17--Listar as celas que estão sem presos dentro
select c.localiz as Celas_Vazias,c.numcela,c.capacidade from cela c
	where
	not exists (
			select * from detento d
			where
			c.numcela = d.numcela
		   )


18--Fazer um balanço(%) dos presos negros, brancos e outras raças.

create view pnegros as
select count(d.nome)*100/
       (
                 select count(*)from detento
        ) as negros from detento d,caracfisicas c
        where
        c.numref = d.num_ref_carac
        and c.cor like 'negr_'


create view pbrancos as
select count(d.nome)*100/
       (
                 select count(*)from detento
        ) as brancos from detento d,caracfisicas c
        where
        c.numref = d.num_ref_carac
        and c.cor like 'branc_'

create view outros as
select count(d.nome)*100/
       (
                 select count(*)from detento
        ) as outros from detento d,caracfisicas c
        where
        c.numref = d.num_ref_carac
        and c.cor not like 'branc_' and
		c.cor not like 'negr_'


select * from pnegros,pbrancos,outros

19--Mostra os presos que têm altura maior que a altura média da casa
select d.nome,c.altura from detento d,caracfisicas c
	where 
	d.num_ref_carac = c.numref 
	and c.altura > (
				select avg(c.altura) from caracfisicas c
			)



MARILIA
1--Listar os presos sejam pretos ou mulatos que tenham grau superior
select d.rg, d.nome from detento d, caracfisicas e, fichapsico f
	where
	d.num_ref_carac = e.numRef and d.num_ref_psico = f.numRef
	and (e.cor like 'negr%' or e.cor like 'mulat%') and
	f.escolaridade like 'superior'


2--Presos com algum tipo de alergia
select e.nome, f.Obs_medicas from detento e,fichapsico f
	where
	(e.num_ref_psico = f.numRef) and  ((f.Obs_medicas like '%alergia%') or 		(f.Obs_medicas like 'alérgico%'))				
	
3--Presos que tem alguma deficiência e exercem alguma atividade interna
select e.rg, e.nome, f.atividade from detento e, ativparalelas f,caracfisicas g
	where 
	e.num_ref_psico = f.numRefFicha and e.num_ref_carac = g.numRef
 	and g.deficiencia not like 'nenhuma'    	
	
4--quantos funcionarios do sexo feminino atuam no setor de carceragem
select count(*) from funcionario f, setor g
	where 
	f.sexo = 'f' and f.setor = g.numSetor and g.nome like 'carceragem'

5--Listar os presos que possuem só um dos pais ou nenhum
select rg, nome from detento 
	where 
	nome_pai = 'desconhecido'
	or nome_mae = 'desconhecida'	

6--Listar os presos que estavam desempregados antes da prisão	
select f.rg, f.nome from detento f, caracfisicas m
 	where 
 	f.num_ref_carac = m.numRef 
    	and m.ultOcupacao like 'desempregad_' order by f.nome
  
7--listar os funcionarios q cuidam de celas especiais
select m.rg,m.nome from funcionario m, cela f
 	where m.rg = f.rgresponsavel
    	and f.eh_especial like 's' order by m.nome
    	
8--Listar os funcionarios que trabalham na carceragem mais d 4 horas atendem a presos que foram condenados por trafico de drogas
select f.rg, f.nome, g.rg, g.nome from funcionario f, detento g, cela h 
	where 
	f.rg = h.rgresponsavel 
	and g.numcela = h.numcela
	and g.condenacao like 'Narcotráfico' 
	and f.cargahoraria not like '4 horas'

	
10--liste todos os telefones do advogado q repres. o preso tal
select d.nome as cliente,t.telefone,e.oabadv, ad.nome from telefonesadvogado t, 
ehdefendidopor e,detento d,advogado ad
	where 
	t.regoabadv = e.oabadv
	and d.rg = e.rgdetento
	and e.rgdetento like '8952307' 
	and ad.numoab = e.oabadv    	

11--retorne o número máximo de presos que a capacidade de uma cela comporta
select max(capacidade) from cela

12--retorne a média de presos por cela que o presídio comporta
select avg(capacidade) from cela

13--listar os menbros de ONG q foram à prosão
select rgVisitante, nome from visitantes, visitas
	where
	rgvisitante = rgvisita 
	and outros like '%ONG%'

14--listar presos nascidos em Recife ou paulista usando IN:
select rg, nome from detento
	where
	naturalidade in ('Recife', 'Paulista') order by nome

15--Listar as visitas que levaram presentes
select * from visitas
       where
       presentes is not null

16--Listar presos deficientes
select d.rg,d.nome,d.sexo, e.deficiencia from detento d, caracfisicas e
       where
       d.num_ref_carac = e.numRef
       and e.deficiencia not like 'nenhuma'

17--Listar presos com grau de periculosodade entre 2 e 4, ordenados por ordem de maior  para menor periculosidade
select f.rg,f.nome,f.vulgo, m.graupericulosidade from detento f, caracfisicas m
       where
       f.num_ref_carac = m.numref
       and m.grauPericulosidade between 2 and 4 order by m.graupericulosidade desc

18---Listar todas as pessoas diretamente ligadas ao presidio(funcs, detentos e visitantes)
 ((SELECT  rg,nome   FROM  detento)
UNION
  (SELECT rg,nome  FROM  funcionario )
UNION
  (SELECT rg,nome  FROM  funcionario )
UNION
  (SELECT rgVisitante,nome  FROM  visitantes )
  ) order by nome

19--Mostrar a porcentagem de Advogados formados no Rio de Janeiro
 select count(*)*100/
       (
                    select count(*) from advogado
       ) as Porcentagem_de_Advogados_Cariocas
       from advogado
       where numOAB like 'RJ%'
       
       
21--Listar os presos e suas datas de nascimento
select rg,nome,CONVERT(CHAR,dat_nasc,103 ) from detento

22--Criar uma visão dos funcionários do setor de carceragem que trabalham em celas não-especiais
por até quatro horas
create view carcereiros1 as
  select distinct f.rg, f.nome from funcionario f, cela m
  where 
  f.rg = m.rgresponsavel 
  and m.eh_especial like 'n' 
  and f.cargahoraria like '4 horas'

23--Listar todos os presos com mais de 65 anos
select f.rg,f.nome from detento f
       where 
       65 < (
                   SELECT DATEDIFF(year,f.dat_nasc,GETDATE())
            )




24--Listar o percentual de presos com mais de 65 anos
select count(*)*100 /
       (
                    select count(*) from detento
       )
       from detento f
       where 65 < (
                           SELECT DATEDIFF(year,f.dat_nasc,GETDATE())
                   )