-- 2022-09-06
-- No dia 06-09 eu compareci na sala de aula no turno da tarde e nenhum aluno compareceu.
-- Decidi resolver e documentar a solução das 4 tarefas que havia proposto para os alunos das duas turmas.
-- Aqui apresento, portanto.

-- Resolvendo perguntas propostas nas tarefas 1, 2, 3 e 4
-- ========================================================================================================================================
-- Tarefa 1
-- Escreva os comandos em SQL que traduzem esta sequencia de AR e grave em um arquivo com nome ARSQL1-SeuNome.txt
-- r <- clientes [[nome="josé"]]
CREATE TEMPORARY TABLE r AS (SELECT * FROM clientes WHERE clientes.nome='JOSÉ');
-- s <- r [[ r.cdcli=carros.cdcli ]] carros
CREATE TEMPORARY TABLE s AS (SELECT * FROM r INNER JOIN carros ON r.cdcli=carros.cdcli);
-- t <- s [[ s.cdcarro=e.cdcarro ]] estacionadas -> e
CREATE TEMPORARY TABLE t AS (SELECT * FROM s INNER JOIN estacionadas AS e ON s.cdcarro=e.cdcarro);
-- f <- t [[ dtestac ]] (distintos)
CREATE TEMPORARY TABLE f AS (SELECT DISTINCT dtestac FROM t);
-- Nota: no modelo de dados os campos com mesmo nome podem ser adequados para facilitar o entendimento de chaves primárias e as suas
-- correspondentes chaves estrangeiras. Entretanto, no âmbito do SGBD o comando SQL da linha:
-- CREATE TEMPORARY TABLE s AS (SELECT * FROM r INNER JOIN carros ON r.cdcli=carros.cdcli);
-- Cria uma tabela com dois campos com o mesmo nome. A operação de junção está correta, no entanto a criação da tabela aponta erro.
-- É possível se resolver esta situação escrevendo projeções nos comandos antes de fazer junções, na forma:
CREATE TEMPORARY TABLE r AS (SELECT cdcli as cdcliente FROM clientes WHERE clientes.nome='JOSÉ');
CREATE TEMPORARY TABLE s AS (SELECT cdcarro as cdcar FROM r INNER JOIN carros ON r.cdcliente=carros.cdcli);
CREATE TEMPORARY TABLE t AS (SELECT * FROM s INNER JOIN estacionadas AS e ON s.cdcar=e.cdcarro);
-- NOTE: as operações combinadas escritas no comando: SELECT cdcli as cdcliente FROM clientes WHERE clientes.nome='JOSÉ';
-- NÃO PODE SER INTERPRETADA na forma de UMA SÓ OPERAÇÃO de AR.
-- Esta situação pode ser também evitada SE A BASE tiver como regra a não repetição de nomes de campos das tabelas
-- (independentemente das tabelas).
-- ========================================================================================================================================
-- Tarefa 2
Dadas as tabelas abaixo responda à pergunta: 
Quais as marcas dos carros cujos os donos tenham nome "Jose" e que não fizeram serviço de "pintura" no mês de "Maio de 2010"?

Clientes      Carros                 Aplicação                  Serviços
+-----+----+  +-----+-------+-----+  +-------+-----+---------+  +-----+----+
|CdCli|Nome|  |CdCli|CdCarro|Marca|  |CdCarro|CdSer|DtServiço|  |CdSer|Nome|

-- Seleção.: a  <- clientes[[nome='José']]
             a1 <- a[[cdcli -> cdcliente]]
-- Junção..: b  <- a1[[a1.cdcli=carros.cdcli]]carros
             b1 <- b[[cdcarro -> cdcar, marca]]
-- Junção..: c  <- b1[[b1.cdcarro=a.cdcarro]]aplicacao -> a
-- Seleção.: d  <- c[[dtservico entre '2010-05-01' e '2010-05-31']]
             d1 <- d[[cdser->cdservico,marca]]
-- Junção..: e  <- d1[[d1.servico=s.cdser]]servicos as s
-- seleção.: f  <- e[[s.Nome<>'pintura']]
-- Projeção: f[[marca]]
CREATE TEMPORARY TABLE a AS (SELECT cdcli as cdcliente FROM clientes WHERE nome='José');
CREATE TEMPORARY TABLE b AS (SELECT cdcarro AS cdcar, marca FROM a INNER JOIN carros ON a.cdcliente=carros.cdcli);
CREATE TEMPORARY TABLE c AS (SELECT * FROM b INNER JOIN aplicacao ON b.cdcar=aplicacao.cdcarro);
CREATE TEMPORARY TABLE d AS (SELECT cdser as cdservico,marca FROM c WHERE dtservico BETWEEN '2010-05-01' and '2010-05-31');
CREATE TEMPORARY TABLE e AS (SELECT * FROM d INNER JOIN servicos AS s ON d.cdservico=s.cdser);
CREATE TEMPORARY TABLE f AS (SELECT * FROM e WHERE nome<>'pintura');
SELECT marca FROM f;
-- ========================================================================================================================================
-- Tarefa 3
Clientes      Carros                 Paradas
+-----+----+  +-----+-------+-----+  +-------+-------+
|CdCli|Nome|  |CdCli|CdCarro|Marca|  |CdCarro|DtEstac|
1. Quais os nomes dos donos de carros e as marcas dos carros que estacionaram entre 9/10 e 10/10?
2. Quais foram os dias de estacionadas de todos os carros exceto os da marca 'Gol'?
3. Quais são os registros em Estacionadas que indicam carros que foram excluídos da tabela Carros?

-- ========================================================================================================================================
Tarefa 4
-- Tarefa 4
Professores    Atribuídas        Disciplinas                      TipoDisciplinas
+------+----+  +------+------+   +------+--------------+------+   +------+------------------+
|IdProf|Nome|  |ProfId|DiscId|   |IdDisc|NomeDisciplina|TipoId|   |IdTipo|NomeTipoDisciplina|

1. Quais são os nomes dos professores e os nomes das disciplinas atribuídas aos professores que ministram disciplinas do tipo "Tecnologia da Informação"?
-- Seleção.: a <- TipoDisciplinas [[NomeTipoDisciplina='Tecnologia da Informação']]
-- Junção..: b <- a [[ a.IdTipo=ds.TipoId ]] Disciplinas -> ds
-- Junção..: c <- b [[ b.IdDisc=at.DiscId ]] atribuidas -> at
-- Junção..: d <- c [[ c.ProfId=pr.IdProf ]] Professores -> pr
-- Projeção: d [[d.Nome, d.NomeDisciplina]]
-- 'Traduzindo' para SQL.
-- Seleção.: a <- TipoDisciplinas [[NomeTipoDisciplina='Tecnologia da Informação']]
create temporary table a
       AS (select * from TipoDisciplinas where NomeTipoDisciplina='Tecnologia da Informação');
	   
-- Junção..: b <- a [[ a.IdTipo=ds.TipoId ]] Disciplinas -> ds
create temporary table b
       AS (select * from a inner join disciplinas as ds on a.IdTipo=ds.TipoId);

-- Junção..: c <- b [[ b.IdDisc=at.DiscId ]] atribuidas -> at
create temporary table c
       AS (select * from b inner join atribuidas as at on b.IdDisc=at.DiscId);

-- Junção..: d <- c [[ c.ProfId=pr.IdProf ]] Professores -> pr
create temporary table d
       AS (select * from c inner join Professores as pr on c.ProfId=pr.IdProf);

-- Projeção: d [[d.Nome, d.NomeDisciplina]]
select Nome, NomeDisciplina from d;

-- Combinando comandos SQL (escrevendo o comando sem tabelas temporárias).
select Nome, NomeDisciplina
       from TipoDisciplinas as td inner join disciplinas as ds on td.IdTipo=ds.TipoId
                                  inner join atribuidas as at  on ds.IdDisc=at.DiscId 
                                  inner join Professores as pr on at.ProfId=pr.IdProf;
       where NomeTipoDisciplina='Tecnologia da Informação'
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------
2. Qual é a quantidade de disciplina atribuídas a cada professor?
-- projeção: atribuidas [[ profid, CONTA(discid) as qtddisc]] {agrupado por profid} {ordenado por profid}
select profid, COUNT(discid) as qtddisc from atribuidas group by profid order by profid;
-- SE a pergunta fosse:
-- Exiba o nome de cada professor e quantidade de disciplinas minitradas pelo professor.
-- Junção..: a <- atribuidas -> at [[ at.profid=pr.idprof]] professores -> pr
-- projeção: a [[ idprof, Nome, CONTA(discid) as qtddisc]] {agrupado por idprof} {ordenado por idprof}
-- Em SQL
-- Junção..: a <- atribuidas -> at [[ at.profid=pr.idprof]] professores -> pr
create temporary table a AS
       (select * from atribuidas as at inner join professores as pr ON at.profid=pr.idprof);
-- projeção: a [[ idprof, Nome, CONTA(discid) -> qtddisc]] {agrupado por idprof} {ordenado por idprof}
select idprof, Nome, CONTA(discid) as qtddisc from a group by idprof order by idprof;
-- Otimizando o comando em um só SQL.
select idprof, Nome, COUNT(discid) as qtddisc
       from atribuidas as at inner join professores as pr ON at.profid=pr.idprof
	   group by idprof order by idprof;
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------
3. Quais são os nomes dos professores que ministram todas disciplinas?
-- Preparando as tabelas
-- Projeção..: p  <- professores[[idprof]]
-- Projeção..: pf <- atribuidas[[profid->idprof, discid->iddisc]]
-- Projeção..: f  <- disciplinas[[iddisc]]
-- Fazendo a divisão
-- Prod.Cart.: s <- p x f
-- Subtração.: t <- s - pf
-- Projeção..: w <- t[[idprof]](distintos)
-- Subtração.: r <- p - w
-- projeção..: k <- r[[idprof->profid]]
-- A projeção acima evita a repetição do nome do campo 'idprof' na execução da próxima junção.
-- Complementando a resposta
-- Junção....: m <- k[[k.profid=professores.idprof]]professores
-- Projeção..: m[[nome]]
-- Em SQL
-- Projeção..: p  <- professores[[idprof]]
create temporary table p as (select idprof from professores);
-- Projeção..: pf <- atribuidas[[profid->idprof, discid->iddisc]]
create temporary table pf as (select prodid as idprof, discid as iddisc from atribuidas);
-- Projeção..: f  <- disciplinas[[iddisc]]
create temporary table f as (select iddisc from disciplinas);
-- Prod.Cart.: s <- p x f
create temporary table s as (select * from p cross join f);
-- Subtração.: t <- s - pf
create temporary table t as (select * from s except (select * from pf));
-- Projeção..: w <- t[[idprof]](distintos)
create temporary table w as (select distinct idprof from t);
-- Subtração.: r <- p - w
create temporary table r as (select * from p except (select * from w));
-- projeção..: k <- r[[idprof->profid]]
create temporary table k as (select idprof as profid from r);
-- Junção....: m <- k[[k.profid=professores.idprof]]professores
create temporary table m as (select * from k inner professores on k.profid=professorres.idprof);
-- Projeção..: m[[nome]]
select nome from m;
-- NÃO vou escrever o ÚNICO comando que processa a resposta desta pergunta porque é alvo de uma das perguntas do TesteSQL.
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------
4. Qual é a média da quantidade de disciplinas atribuídas aos professores para cada tipo de disciplina? O que pode representar esta média (interprete o resultado)
-- Junção..: t1 <- atribuidas->a [[ a.discid=d.iddisc ]] disciplinas->d
-- Junção..: t2 <- t1 [[ t1.tipoid=t.idtipo ]] TipoDisciplinas -> t
-- Projeção: t2 [[ idtipo, media(iddisc) -> QtdDisc) ]] {agrupado por idtipo}{ordenado por idtipo}
-- Em SQL
-- Junção..: t1 <- atribuidas->a [[ a.discid=d.iddisc ]] disciplinas->d
create temporary table t1 as (select * from atribuidas as a inner join disciplinas as d on a.discid=d.iddisc);
-- Junção..: t2 <- t1 [[ t1.tipoid=t.idtipo ]] TipoDisciplinas -> t
create temporary table t1 as (select * from t1 inner join TipoDisciplinas as t on t1.tipoid=t.idtipo);
-- Projeção: t2 [[ idtipo, media(iddisc) -> QtdDisc) ]]
select idtipo, AVG(iddisc) as QtdDisc from t2 group by idtipo order by idtipo
-- Escrevendo em um comando
select idtipo, avg(iddisc) as QtdDisc 
       from atribuidas as a inner join disciplinas as d ON a.discid = d.iddisc
                            inner join TipoDisciplinas as t ON d.tipoid = t.idtipo
       group by idtipo order by idtipo;
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------
5. Quantas disciplinas são atribuídas para cada professor que ministra disciplinas do tipo "Matemáticas"? 
-- Seleção.: t1 <- TipoDisciplinas [[ NomeTipoDisciplina='Matemáticas' ]]
-- Junção..: t2 <- t1 [[ t1.idtipo=d.tipoid ]] disciplinas -> d
-- Junção..: t3 <- t2 [[ t2.iddisc=a.discid ]] atribuidas -> a
-- Projeção: t3 [[profid,conta(iddisc)->QtdDisc]] {agrupado por profid}{ordenado por profid}
-- Em SQL
-- Seleção.: t1 <- TipoDisciplinas [[ NomeTipoDisciplina='Matemáticas' ]]
create temporary table t1 as (select * from TipoDisciplinas where NomeTipoDisciplina='Matemáticas');
-- Junção..: t2 <- t1 [[ t1.idtipo=d.tipoid ]] disciplinas -> d
create temporary table t2 as (select * from t1 inner join disciplinas as d on t1.idtipo=d.tipoid);
-- Junção..: t3 <- t2 [[ t2.iddisc=a.discid ]] atribuidas -> a
create temporary table t3 as (select * from t2 inner join atribuidas as a on t2.iddisc=a.discid);
-- Projeção: t3 [[profid,conta(iddisc)->QtdDisc]] {agrupado por profid}{ordenado por profid}
select profid,count(iddisc from t3 group by profid order by profid;

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------
6. Quais são, em ordem crescente, as disciplinas com mais professores associados?
atribuidas [[ discid, conta(profid) ]] {agrupado por discid}{ordenado por conta(profid) desc}
select at.fkdisciplina, count(at.fkprofessor)as qtdprof from atribuicoes as at group by fkdisciplina order by count(at.fkprofessor) desc limit 10;
-- o complemento limit exibe somente as 10 primeiras linhas do resultado do processamento.
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------
7. Sendo que qualquer professor só pode ministrar disciplinas de um tipo, indique quais são as disciplinas (código e nome) que ainda podem ser atribuídas ao professor com nome "ANA"?

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