SELECT pe.nome, COUNT(DISTINCT pe2.cpf)
FROM Pessoa pe, Pessoa pe2, Dentista d, Cliente c, Cliente_Dentista_Secretario cds
WHERE pe.cpf = d.cpf
AND pe2.cpf = c.cpf
AND cds.cpf_dentista = d.cpf
AND cds.cpf_cliente = c.cpf
AND EXTRACT(year FROM cds.data_agendada) = 2012
AND pe2.cidade = 'Recife'
GROUP BY pe.cpf, pe.nome;

-----------------------------------------------------------------------------------------------------------

SELECT pe.nome, SUM(descc.porcentagem_desconto*ps.preco/100)
FROM Pessoa pe, Desconto descc, Presta_Servico ps, Dentista d, Servico s, Cliente_Dentista_Secretario cds
WHERE pe.cpf = d.cpf
AND d.cpf = cds.cpf_dentista
AND s.tipo = cds.tipo_servico
AND s.tipo = ps.tipo_servico
AND d.cpf = ps.cpf_dentista
AND cds.motivo_desconto = descc.motivo
GROUP BY pe.cpf, pe.nome;

-----------------------------------------------------------------------------------------------------------

SELECT s.tipo
FROM Pessoa pe, Cliente c, Cliente_Dentista_Secretario cds, Servico s
WHERE pe.cpf = c.cpf
AND pe.sexo = 'M'
AND cds.cpf_cliente = c.cpf
AND cds.tipo_servico = s.tipo
GROUP BY s.tipo HAVING COUNT(c.cpf) >= ALL(
	SELECT COUNT(c2.cpf)
	FROM Pessoa pe2, Cliente c2, Cliente_Dentista_Secretario cds2, Servico s2
	WHERE pe2.cpf = c2.cpf
	AND pe2.sexo = 'M'
	AND cds2.cpf_cliente = c2.cpf
	AND cds2.tipo_servico = s2.tipo
	GROUP BY s2.tipo
);

------------------------------------------------------------------------------------------------------------

(SELECT pe.sexo, COUNT(DISTINCT pe.cpf)
FROM Pessoa pe, Telefone t
WHERE pe.sexo = 'M'
AND pe.cpf = t.cpf
AND t.numero LIKE '5%'
GROUP BY pe.sexo)
UNION
(SELECT pe.sexo, COUNT(DISTINCT pe.cpf)
FROM Pessoa pe, Telefone t
WHERE pe.sexo = 'F'
AND pe.cpf = t.cpf
AND t.numero LIKE '5%'
GROUP BY pe.sexo);

------------------------------------------------------------------------------------------------------------

SELECT pe.nome
FROM Pessoa pe, Dentista d, Pessoa pe2, Dentista d2, Cliente_Dentista_Secretario cds
WHERE pe.cpf = d.cpf
AND pe2.cpf = d2.cpf
AND d.cpf_do_chefe = d2.cpf
AND d2.cpf = cds.cpf_dentista
GROUP BY pe.cpf, pe.nome HAVING COUNT(*) >= 5;

------------------------------------------------------------------------------------------------------------
SELECT MAX(MEDIA)
FROM (
	(SELECT AVG(a.salario) AS MEDIA
	FROM Assistente a)
	UNION
	(SELECT AVG(d.salario) AS MEDIA
	FROM Dentista d)
	UNION
	(SELECT AVG(s.salario) AS MEDIA
	FROM Secretario s)
);

------------------------------------------------------------------------------------------------------------

SELECT pe.nome, pe.rua, pe.cidade
FROM Pessoa pe, Telefone t, Cliente c
WHERE pe.cpf = t.cpf
AND c.cpf = pe.cpf
AND t.numero LIKE '%0019%';

------------------------------------------------------------------------------------------------------------

SELECT pe.nome, t.numero, pe2.nome
FROM Pessoa pe
INNER JOIN Telefone t
ON pe.cpf = t.cpf
INNER JOIN Cliente c
ON pe.cpf = c.cpf
INNER JOIN Cliente_Dentista_Secretario cds
ON cds.cpf_cliente = c.cpf
INNER JOIN Secretario s
ON cds.cpf_secretario = s.cpf
INNER JOIN Pessoa pe2
ON s.cpf = pe2.cpf
WHERE cds.data_agendada >= ALL(
	SELECT cds2.data_agendada
	FROM Cliente_Dentista_Secretario cds2
	WHERE cds2.cpf_cliente = c.cpf
)
AND cds.data_agendada < add_months(sysdate, -6);

------------------------------------------------------------------------------------------------------------

 
SELECT pe.nome, SUM(ps.preco)
FROM Presta_Servico ps, Pessoa pe, Cliente c, Cliente_Dentista_Secretario cds, Pessoa pe2, Dentista d, Servico s
WHERE pe.cpf = c.cpf
AND pe2.cpf = d.cpf
AND cds.cpf_dentista = d.cpf
AND cds.cpf_cliente = c.cpf
AND ps.cpf_dentista = d.cpf
AND ps.tipo_servico = cds.tipo_servico
AND cds.tipo_servico = s.tipo
AND pe.email LIKE '%@cin.ufpe.br'
GROUP BY pe.cpf, pe.nome;

---------------------------------------------------------------------------------------------------------------------

SELECT s.tipo
FROM Servico s, Cliente_Dentista_Secretario cds
WHERE s.tipo = cds.tipo_servico
GROUP BY s.tipo HAVING COUNT(*) = (
	SELECT MAX(COUNT(s2.tipo))
	FROM Servico s2, Cliente_Dentista_Secretario cds2
	WHERE s2.tipo = cds2.tipo_servico
	GROUP BY s2.tipo
);