Lista 04 - Gabarito

Lista

_{​ ​04​ ​-​ ​Gabarito}

--1._{​ ​Obtenha​ ​o​ ​nome​ ​e​ ​salário​ ​dos​ ​empregados​ ​e​ ​caso​ ​o​ ​empregado​ ​seja​ ​coordenador} de_{​ ​algum​ ​projeto,​ ​o​ ​código​ ​e​ ​orçamento​ ​do​ ​projeto​ ​que​ ​o​ ​empregado​ ​coordena.}

SELECT_{​ ​E.nome,​ ​E.salario,​ ​P.pcodigo,​ ​P.orçamento} FROM_{​ ​EMPREGADOS​ ​E}

LEFT_{​ ​JOIN​ ​PROJETOS​ ​P​ ​ON​ ​E.ecodigo​ ​=​ ​P.coordenador;} (60_{​ ​rows)}

--2._{​ ​Obtenha​ ​o​ ​nome​ ​e​ ​local​ ​de​ ​trabalho​ ​dos​ ​empregados​ ​e​ ​caso​ ​o​ ​empregado​ ​seja} gerente_{​ ​de​ ​algum​ ​departamento,​ ​o​ ​código​ ​do​ ​departamento​ ​que​ ​o​ ​empregado} gerencia.

SELECT_{​ ​E.nome,​ ​D1.local,​ ​D2.dcodigo} FROM_{​ ​EMPREGADOS​ ​E}

INNER_{​ ​JOIN​ ​DEPARTAMENTOS​ ​D1​ ​ON​ ​E.dcodigo​ ​=​ ​D1.dcodigo} LEFT_{​ ​JOIN​ ​DEPARTAMENTOS​ ​D2​ ​ON​ ​E.ecodigo​ ​=​ ​D2.gerente;} (53_{​ ​rows)}

--3._{​ ​Obtenha​ ​a​ ​média​ ​de​ ​salarios​ ​do​ ​departamento​ ​“D30”​ ​(dcodigo​ ​igual​ ​a​ ​30).}

SELECT_{​ ​AVG(E.salario)​ ​AS​ ​MEDIA_SALARIO} FROM_{​ ​EMPREGADOS​ ​E}

WHERE_{​ ​E.dcodigo​ ​=​ ​30;}

--4._{​ ​Obtenha​ ​o​ ​maior​ ​salário​ ​da​ ​empresa.}

SELECT_{​ ​MAX(E.salario)​ ​AS​ ​MAIOR_SALARIO} FROM_{​ ​EMPREGADOS​ ​E;}

--5._{​ ​Obtenha​ ​o​ ​nome​ ​do​ ​coordenador​ ​do​ ​projeto​ ​de​ ​maior​ ​orçamento.}

SELECT_{​ ​E.nome,​ ​P1.orçamento} FROM_{​ ​PROJETOS​ ​P1}

INNER_{​ ​JOIN​ ​EMPREGADOS​ ​E​ ​ON​ ​P1.coordenador​ ​=​ ​E.ecodigo} WHERE_{​ ​P1.orçamento​ ​IN​ ​(}

SELECT_{​ ​MAX(P2.orçamento)} FROM_{​ ​PROJETOS​ ​P2}

);

--6._{​ ​Obtenha​ ​o​ ​departamento​ ​que​ ​tem​ ​o​ ​empregado​ ​de​ ​maior​ ​salário.}

SELECT_{​ ​E1.dcodigo} FROM_{​ ​EMPREGADOS​ ​E1} WHERE_{​ ​E1.salario​ ​IN​ ​(}

SELECT_{​ ​MAX(E2.salario)} FROM_{​ ​EMPREGADOS​ ​E2} );

--7._{​ ​Obtenha​ ​a​ ​soma​ ​dos​ ​salários​ ​de​ ​todos​ ​os​ ​empregados​ ​que​ ​trabalham​ ​no} departamento_{​ ​“D30”​ ​​ ​(dcodigo​ ​igual​ ​a​ ​30).}

SELECT_{​ ​SUM(E.salario)} FROM_{​ ​EMPREGADOS​ ​E} WHERE_{​ ​E.dcodigo​ ​=​ ​30;}

--8._{​ ​Para​ ​cada​ ​departamento,​ ​obtenha​ ​o​ ​número​ ​de​ ​empregados​ ​que​ ​trabalham} naquele_{​ ​departamento​ ​e​ ​a​ ​soma​ ​dos​ ​seus​ ​salários.}

SELECT_{​ ​D.dcodigo,​ ​COUNT(E.ecodigo)​ ​AS​ ​Numero_Funcionarios,​ ​SUM(E.salario)} FROM_{​ ​EMPREGADOS​ ​E}

RIGHT_{​ ​JOIN​ ​DEPARTAMENTOS​ ​D​ ​ON​ ​E.dcodigo​ ​=​ ​D.dcodigo} GROUP_{​ ​BY​ ​(D.dcodigo)}

ORDER_{​ ​BY​ ​D.dcodigo;}

--_{​ ​ORDER​ ​BY​ ​Não​ ​necessário,​ ​só​ ​para​ ​organizar​ ​o​ ​resultado}

--9._{​ ​Para​ ​cada​ ​departamento,​ ​obtenha​ ​o​ ​número​ ​de​ ​empregados​ ​que​ ​trabalham} naquele_{​ ​departamento​ ​e​ ​a​ ​soma​ ​dos​ ​seus​ ​salários.​ ​Exclua​ ​os​ ​departamentos​ ​com} número_{​ ​de​ ​empregados​ ​menor​ ​do​ ​que​ ​30.}

SELECT_{​ ​D.dcodigo,​ ​COUNT(E.ecodigo)​ ​AS​ ​Numero_Funcionarios,​ ​SUM(E.salario)} FROM_{​ ​EMPREGADOS​ ​E}

RIGHT_{​ ​JOIN​ ​DEPARTAMENTOS​ ​D​ ​ON​ ​E.dcodigo​ ​=​ ​D.dcodigo} GROUP_{​ ​BY​ ​(D.dcodigo)}

--10._{​ ​Obtenha​ ​o​ ​departamento,​ ​que​ ​tem​ ​a​ ​maior​ ​média​ ​de​ ​salários.}

--_{​ ​Solução​ ​01}

CREATE_{​ ​VIEW​ ​V1(dnumero,​ ​media)​ ​AS​ ​SELECT​ ​D.dcodigo,​ ​AVG(E.salario)} FROM_{​ ​EMPREGADOS​ ​E}

RIGHT_{​ ​JOIN​ ​DEPARTAMENTOS​ ​D​ ​ON​ ​E.dcodigo​ ​=​ ​D.dcodigo} GROUP_{​ ​BY​ ​D.dcodigo;}

SELECT_{​ ​V1.dnumero,​ ​V1.media}

FROM_{​ ​V1​ ​WHERE​ ​V1.media​ ​IN​ ​(​ ​SELECT​ ​MAX(V1.media)​ ​FROM​ ​V1​ ​);}

--_{​ ​Solução​ ​02}

SELECT_{​ ​V1.dnumero,​ ​V1.media} FROM_{​ ​V1​ ​WHERE​ ​V1.media​ ​>=​ ​ALL​ ​(}

SELECT_{​ ​V1.media} FROM_{​ ​V1}

where_{​ ​V1.media​ ​IS​ ​NOT​ ​NULL} );

--_{​ ​Solução​ ​03​ ​-​ ​sem​ ​Visão}

SELECT_{​ ​V.dnumero,​ ​V.media} FROM_{​ ​(}

SELECT_{​ ​D.dcodigo​ ​as​ ​dnumero,​ ​AVG(E.salario)​ ​as​ ​media} FROM_{​ ​EMPREGADOS​ ​E}

RIGHT_{​ ​JOIN​ ​DEPARTAMENTOS​ ​D​ ​ON​ ​E.dcodigo​ ​=​ ​D.dcodigo} GROUP_{​ ​BY​ ​D.dcodigo}

)_{​ ​V}

WHERE_{​ ​V.media​ ​IN​ ​(}

SELECT_{​ ​MAX(V2.media)} FROM_{​ ​(}

SELECT_{​ ​D.dcodigo,​ ​AVG(E.salario)​ ​as​ ​media} FROM_{​ ​empregados​ ​E}

RIGHT_{​ ​JOIN​ ​DEPARTAMENTOS​ ​D​ ​ON​ ​E.dcodigo​ ​=​ ​D.dcodigo} GROUP_{​ ​BY​ ​D.dcodigo}

)_{​ ​V2} )

--11._{​ ​Obtenha​ ​o​ ​número​ ​de​ ​horas​ ​que​ ​o​ ​empregado​ ​“E21”​ ​(ecodigo​ ​igual​ ​a​ ​21)} trabalha_{​ ​no​ ​projeto​ ​“P1”​ ​(pcodigo​ ​igual​ ​a​ ​1).}

SELECT_{​ ​T.numHoras} FROM_{​ ​TRABALHA​ ​T}

WHERE_{​ ​T.ecodigo​ ​=​ ​21​ ​AND​ ​T.pcodigo​ ​=​ ​1;}

--12._{​ ​Obtenha​ ​o​ ​número​ ​total​ ​de​ ​horas​ ​que​ ​o​ ​empregado​ ​“E21”​ ​(ecodigo​ ​igual​ ​a​ ​21)} trabalha_{​ ​(considerando​ ​todos​ ​os​ ​projetos​ ​que​ ​ele​ ​trabalha).}

SELECT_{​ ​SUM(T.numHoras)} FROM_{​ ​TRABALHA​ ​T}

--13._{​ ​Para​ ​cada​ ​projeto,​ ​obtenha​ ​o​ ​número​ ​de​ ​horas​ ​que​ ​o​ ​empregado​ ​“E21”​ ​(ecodigo} igual_{​ ​a​ ​21)​ ​trabalha.}

--_{​ ​No​ ​caso​ ​em​ ​que​ ​o​ ​empregado​ ​“E21”​ ​não​ ​trabalhe​ ​no​ ​projeto​ ​o​ ​valor​ ​deve​ ​ser​ ​Null.} SELECT_{​ ​P.pcodigo,​ ​T.numHoras}

FROM_{​ ​(}

SELECT_{​ ​*}

FROM_{​ ​TRABALHA} WHERE_{​ ​ecodigo​ ​=​ ​21​ ​)​ ​T}

RIGHT_{​ ​JOIN​ ​PROJETOS​ ​P​ ​ON​ ​T.pcodigo​ ​=​ ​P.pcodigo;} (16_{​ ​rows)}

--_{​ ​14.​ ​Obtenha​ ​o​ ​número​ ​total​ ​de​ ​horas​ ​que​ ​os​ ​empregados​ ​trabalham​ ​no​ ​projeto} “P21”_{​ ​(pcodigo​ ​igual​ ​a​ ​21).}

SELECT_{​ ​SUM(T.numHoras)} FROM_{​ ​TRABALHA​ ​T}

--_{​ ​15.​ ​Para​ ​cada​ ​empregado,​ ​obtenha​ ​o​ ​nome​ ​do​ ​empregado​ ​e​ ​o​ ​total​ ​de​ ​horas​ ​que​ ​ele} trabalha_{​ ​em​ ​cada​ ​projeto.​ ​No​ ​caso​ ​em​ ​que​ ​o​ ​empregado​ ​não​ ​trabalhe​ ​em​ ​algum} projeto_{​ ​o​ ​valor​ ​deve​ ​ser​ ​Null.}

SELECT_{​ ​E.nome,​ ​T.pcodigo,​ ​T.numHoras} FROM_{​ ​EMPREGADOS​ ​E}

LEFT_{​ ​JOIN​ ​TRABALHA​ ​T​ ​ON​ ​E.ecodigo​ ​=​ ​T.ecodigo} LEFT_{​ ​JOIN​ ​PROJETOS​ ​P​ ​ON​ ​P.pcodigo​ ​=​ ​T.pcodigo} ORDER_{​ ​BY​ ​e.nome;}

--OBS:_{​ ​ORDER​ ​BY​ ​não​ ​é​ ​necessario,​ ​mas​ ​melhora​ ​a​ ​visualização​ ​do​ ​resultado} (60_{​ ​rows)}

--16._{​ ​Para​ ​cada​ ​departamento,​ ​obtenha​ ​o​ ​número​ ​de​ ​empregados​ ​desse​ ​departamento} que_{​ ​trabalham​ ​em​ ​cada​ ​projetos.}

SELECT_{​ ​D.dcodigo,​ ​T.pcodigo,​ ​COUNT(​ ​E.ecodigo)} FROM_{​ ​DEPARTAMENTOS​ ​D}

LEFT_{​ ​JOIN​ ​EMPREGADOS​ ​E​ ​ON​ ​D.dcodigo​ ​=​ ​E.dcodigo} LEFT_{​ ​JOIN​ ​TRABALHA​ ​T​ ​ON​ ​E.ecodigo​ ​=​ ​T.ecodigo} GROUP_{​ ​BY​ ​D.dcodigo,​ ​T.pcodigo}

(22_{​ ​rows)}

--_{​ ​​Bonus​:​ ​Para​ ​mostrar​ ​realmente​ ​todo​ ​par​ ​(departamento,​ ​projeto)​ ​teriamos​ ​que​ ​fazer} assim:

SELECT_{​ ​V1.dcodigo,​ ​V1.pcodigo,​ ​COUNT(V2.ecodigo)} FROM_{​ ​(}

SELECT_{​ ​D.DCODIGO,​ ​P.PCODIGO}

FROM_{​ ​DEPARTAMENTOS​ ​D,​ ​PROJETOS​ ​P} ORDER_{​ ​BY​ ​D.DCODIGO,​ ​P.PCODIGO​ ​)​ ​V1} LEFT_{​ ​JOIN​ ​(}

SELECT_{​ ​E.ecodigo,​ ​E.dcodigo,​ ​T.pcodigo} FROM_{​ ​EMPREGADOS​ ​E}

INNER_{​ ​JOIN​ ​TRABALHA​ ​T​ ​ON​ ​E.ecodigo​ ​=​ ​T.ecodigo}

)_{​ ​V2​ ​ON​ ​V1.dcodigo​ ​=​ ​V2.dcodigo​ ​and​ ​V1.pcodigo​ ​=​ ​V2.pcodigo} group_{​ ​by​ ​V1.dcodigo,​ ​V1.pcodigo}

ORDER_{​ ​BY​ ​v1.dcodigo,​ ​v1.pcodigo} (96_{​ ​rows)}

--17._{​ ​Nome​ ​dos​ ​empregados​ ​que​ ​coordenam​ ​mais​ ​de​ ​3​ ​projetos.}

--_{​ ​Solução​ ​01}

CREATE_{​ ​VIEW​ ​V17(ecodigo,​ ​nome,​ ​NumProj)​ ​AS} SELECT_{​ ​E.ecodigo,​ ​E.nome,​ ​COUNT(P.pcodigo)} FROM_{​ ​EMPREGADOS​ ​E,​ ​PROJETOS​ ​P}

WHERE_{​ ​E.ecodigo​ ​=​ ​P.coordenador} GROUP_{​ ​BY​ ​E.ecodigo;}

SELECT_{​ ​V17.nome} FROM_{​ ​V17}

WHERE_{​ ​NumProj​ ​>​ ​3;} --_{​ ​Solução​ ​02​ ​-​ ​Sem​ ​View}

SELECT_{​ ​E.nome}

FROM_{​ ​EMPREGADOS​ ​E,​ ​PROJETOS​ ​P} WHERE_{​ ​E.ecodigo​ ​=​ ​P.coordenador} GROUP_{​ ​BY​ ​E.ecodigo}

HAVING_{​ ​COUNT(*)​ ​>​ ​3} --_{​ ​Solução​ ​03​ ​-​ ​Sem​ ​View}

SELECT_{​ ​E.nome}

FROM_{​ ​EMPREGADOS​ ​E} WHERE_{​ ​E.ecodigo​ ​IN​ ​(}

SELECT_{​ ​P.coordenador} FROM_{​ ​PROJETOS​ ​P} GROUP_{​ ​BY​ ​P.coordenador} HAVING_{​ ​COUNT(P.pcodigo)​ ​>​ ​3} );

Solução_{​ ​04​ ​-​ ​Sem​ ​view} SELECT_{​ ​E.nome} FROM_{​ ​empregados} E_{​ ​WHERE​ ​(}

select_{​ ​count(*)} from_{​ ​projetos}

--18._{​ ​Nome​ ​dos​ ​departamentos​ ​que​ ​têm​ ​mais​ ​empregados​ ​que​ ​o​ ​departamento​ ​D​​ ​20​.}

--_{​ ​(Foi​ ​alterado​ ​o​ ​valor​ ​do​ ​codigo​ ​de​ ​departamento​ ​para​ ​o​ ​20​ ​(que​ ​não​ ​tem​ ​empregados)} para_{​ ​ter​ ​resultado)}

SELECT_{​ ​D.dcodigo,​ ​D.nome} FROM_{​ ​EMPREGADOS​ ​E}

INNER_{​ ​JOIN​ ​DEPARTAMENTOS​ ​D​ ​ON​ ​E.dcodigo​ ​=​ ​D.dcodigo} GROUP_{​ ​BY​ ​D.dcodigo}

HAVING_{​ ​COUNT(​ ​E.ecodigo)​ ​>​ ​(}

SELECT_{​ ​COUNT(DISTINCT​ ​E1.ecodigo)} FROM_{​ ​EMPREGADOS​ ​E1}

WHERE_{​ ​E1.dcodigo​ ​=​ ​20} );

--19._{​ ​Obtenha​ ​os​ ​nomes​ ​dos​ ​empregados​ ​que​ ​ganham​ ​mais​ ​do​ ​que​ ​o​ ​gerente​ ​do​ ​seu} departamento.

SELECT_{​ ​E1.nome}

FROM_{​ ​EMPREGADOS​ ​E1}

INNER_{​ ​JOIN​ ​DEPARTAMENTOS​ ​D​ ​ON​ ​E1.dcodigo​ ​=​ ​D.dcodigo} INNER_{​ ​JOIN​ ​EMPREGADOS​ ​E2​ ​ON​ ​D.gerente​ ​=​ ​E2.ecodigo} WHERE_{​ ​E1.salario​ ​>​ ​E2.salario}

--20._{​ ​Liste​ ​os​ ​códigos​ ​dos​ ​projetos​ ​que​ ​têm​ ​um​ ​empregado​ ​chamado​ ​“JOAO​ ​SILVA”} que_{​ ​trabalha​ ​no​ ​projeto​ ​ou​ ​gerencia​ ​o​ ​departamento​ ​que​ ​controla​ ​o​ ​projeto.​ ​(Funciona} mudando_{​ ​o​ ​nome​ ​do​ ​funcionário​ ​para​ ​“Narciso”,​ ​por​ ​exemplo)}

--_{​ ​​ ​Solução​ ​01}

SELECT_{​ ​DISTINCT​ ​(T.pcodigo)} FROM_{​ ​TRABALHA​ ​T}

INNER_{​ ​JOIN​ ​PROJETOS​ ​P​ ​ON​ ​T.pcodigo​ ​=​ ​P.pcodigo} WHERE_{​ ​T.ecodigo​ ​=​ ​(}

SELECT_{​ ​E.ecodigo} FROM_{​ ​EMPREGADOS​ ​E} WHERE_{​ ​E.nome​ ​=​ ​'Narciso'} )

OR_{​ ​P.coordenador​ ​=​ ​(}

SELECT_{​ ​E1.ecodigo​ ​FROM} EMPREGADOS_{​ ​E1}

WHERE_{​ ​E1.nome​ ​=​ ​'Narciso'​ ​)​ ​;} --_{​ ​Solução​ ​02}

SELECT_{​ ​DISTINCT(T.pcodigo)} FROM_{​ ​TRABALHA​ ​T}

INNER_{​ ​JOIN​ ​PROJETOS​ ​P​ ​ON​ ​T.pcodigo​ ​=​ ​P.pcodigo} WHERE_{​ ​T.ecodigo​ ​IN​ ​(}

SELECT_{​ ​E.ecodigo} FROM_{​ ​EMPREGADOS​ ​E} WHERE_{​ ​E.nome​ ​=​ ​'Narciso'} )_{​ ​OR​ ​P.coordenador​ ​IN​ ​(}

SELECT_{​ ​E1.ecodigo} FROM_{​ ​EMPREGADOS​ ​E1} WHERE_{​ ​E1.nome​ ​=​ ​'Narciso'} )

--21._{​ ​Obtenha​ ​os​ ​nomes​ ​dos​ ​empregados​ ​que​ ​não​ ​trabalham​ ​em​ ​nenhum​ ​projeto.}

--_{​ ​Solução​ ​01}

SELECT_{​ ​e.nome} FROM_{​ ​empregados​ ​e} WHERE_{​ ​e.ecodigo​ ​NOT​ ​IN​ ​(}

SELECT_{​ ​distinct​ ​ecodigo} FROM_{​ ​trabalha}

) --_{​ ​Solução​ ​02}

SELECT_{​ ​e.nome} FROM_{​ ​empregados​ ​E} WHERE_{​ ​NOT​ ​EXISTS​ ​(}

SELECT_{​ ​*}

FROM_{​ ​trabalha​ ​T}

WHERE_{​ ​T.ecodigo​ ​=​ ​E.ecodigo} )

--22._{​ ​Obtenha​ ​os​ ​nomes​ ​dos​ ​empregados​ ​que​ ​trabalham​ ​em​ ​pelo​ ​menos​ ​um​ ​projeto.}

--_{​ ​Solução​ ​01}

SELECT_{​ ​e.nome} FROM_{​ ​empregados​ ​e} WHERE_{​ ​e.ecodigo​ ​IN​ ​(}

SELECT_{​ ​distinct​ ​ecodigo} FROM_{​ ​trabalha}

) --_{​ ​Solução​ ​02}

SELECT_{​ ​e.nome} FROM_{​ ​empregados​ ​E} WHERE_{​ ​EXISTS​ ​(}

SELECT_{​ ​*​ ​FROM} trabalha_{​ ​T}

WHERE_{​ ​T.ecodigo​ ​=​ ​E.ecodigo} )

--23._{​ ​Liste​ ​os​ ​nomes​ ​dos​ ​empregados​ ​no​ ​departamento​ ​'D30'​ ​que​ ​tem​ ​o​ ​maior​ ​salário}

--OBS:_{​ ​Mudando​ ​para​ ​o​ ​departamento​ ​D30​ ​para​ ​ter​ ​resultado} --_{​ ​Solução​ ​01}

SELECT_{​ ​E.nome}

FROM_{​ ​EMPREGADOS​ ​E} WHERE_{​ ​E.dcodigo​ ​=​ ​30}

AND_{​ ​E.salario​ ​IN​ ​(}

SELECT_{​ ​MAX(E.salario)} FROM_{​ ​EMPREGADOS​ ​E} WHERE_{​ ​E.dcodigo​ ​=​ ​30} );

--_{​ ​Solução​ ​02}

SELECT_{​ ​e.nome} FROM_{​ ​empregados​ ​E} WHERE_{​ ​E.dcodigo​ ​=​ ​30}

AND_{​ ​e.salario​ ​>=ALL​ ​(}

select_{​ ​max(empregados.salario)}

From_{​ ​empregados​ ​where​ ​empregados.dcodigo​ ​=​ ​30} )

--24._{​ ​Para​ ​cada​ ​empregado​ ​obtenha​ ​o​ ​número​ ​de​ ​projetos​ ​que​ ​ele​ ​trabalha​ ​e​ ​o​ ​total​ ​de} horas_{​ ​que​ ​trabalha​ ​nestes​ ​projetos.}

SELECT_{​ ​T.ecodigo,​ ​COUNT(*),​ ​SUM(T.numHoras)} FROM_{​ ​TRABALHA​ ​T}

GROUP_{​ ​BY​ ​T.ecodigo} (17_{​ ​rows)}

--25._{​ ​Para​ ​cada​ ​empregado​ ​que​ ​trabalha​ ​em​ ​mais​ ​de​ ​um​ ​projeto​ ​obtenha​ ​nome​ ​dos} empregados_{​ ​e​ ​número​ ​de​ ​projetos​ ​que​ ​trabalha.​ ​(MODIFICADA​ ​DA​ ​LISTA)}

SELECT_{​ ​e.nome,​ ​count(*)} FROM_{​ ​trabalha​ ​t}

INNER_{​ ​JOIN​ ​empregados​ ​e​ ​ON​ ​e.ecodigo​ ​=​ ​t.ecodigo} GROUP_{​ ​BY​ ​t.ecodigo​ ​,​ ​e.nome}

--26._{​ ​Para​ ​cada​ ​projeto​ ​que​ ​tem​ ​mais​ ​de​ ​5​ ​empregados​ ​alocados,​ ​obtenha​ ​o​ ​código​ ​do} projeto,_{​ ​a​ ​média​ ​de​ ​horas​ ​que​ ​os​ ​empregados​ ​trabalham​ ​no​ ​projeto.}

--OBS:_{​ ​resultado​ ​modificando​ ​para​ ​mais​ ​de​ ​3​ ​empregados} SELECT_{​ ​T.pcodigo​ ​,​ ​COUNT(*),​ ​AVG(T.numHoras)} FROM_{​ ​TRABALHA​ ​T}

GROUP_{​ ​BY​ ​T.pcodigo​ ​HAVING​ ​count(*)​ ​>​ ​3}

--27._{​ ​Obtenha​ ​os​ ​projetos​ ​que​ ​tem​ ​mais​ ​empregados​ ​do​ ​que​ ​a​ ​média​ ​dos​ ​projetos​ ​e} total_{​ ​de​ ​horas​ ​alocadas​ ​maior​ ​do​ ​que​ ​a​ ​média​ ​dos​ ​projetos}

CREATE_{​ ​VIEW​ ​V27​ ​(pcodigo,​ ​quantidade_emp,​ ​horas_alocadas)​ ​AS} SELECT_{​ ​T.pcodigo,​ ​COUNT(*)​ ​AS​ ​quantidade_emp,​ ​SUM(T.numHoras)} FROM_{​ ​TRABALHA​ ​T​ ​GROUP​ ​BY​ ​T.pcodigo​ ​;}

SELECT_{​ ​*} FROM_{​ ​V27} WHERE_{​ ​V27.quantidade_emp​ ​>​ ​(} SELECT_{​ ​AVG(V27.quantidade_emp)} FROM_{​ ​V27} )_{​ ​AND​ ​V27.horas_alocadas​ ​>​ ​(} SELECT_{​ ​AVG(V27.horas_alocadas)} FROM_{​ ​V27} );

--28._{​ ​Obtenha​ ​o​ ​nome​ ​e​ ​salário​ ​dos​ ​empregados​ ​que​ ​ganham​ ​um​ ​salário​ ​superior​ ​ao} salário_{​ ​médio​ ​do​ ​respectivo​ ​departamento}

SELECT_{​ ​E.nome,​ ​E.salario} FROM_{​ ​EMPREGADOS​ ​E} WHERE_{​ ​E.salario​ ​>​ ​(}

SELECT_{​ ​AVG(E1.salario)} FROM_{​ ​EMPREGADOS​ ​E1} WHERE_{​ ​E1.dcodigo​ ​=​ ​E.dcodigo} );

--29._{​ ​Obtenha​ ​o​ ​nome​ ​e​ ​salário​ ​dos​ ​empregados​ ​cujo​ ​salario​ ​médio​ ​do​ ​seu} departamento_{​ ​é​ ​superior​ ​a​ ​media​ ​de​ ​salários​ ​do​ ​departamento​ ​D30.}

--_{​ ​OBS:​ ​Mudando​ ​para​ ​departamento​ ​1​ ​para​ ​ter​ ​resultado} --_{​ ​Solução​ ​01}

SELECT_{​ ​E.nome,​ ​E.salario} FROM_{​ ​EMPREGADOS​ ​E} WHERE_{​ ​E.dcodigo​ ​IN​ ​(}

SELECT_{​ ​E1.dcodigo} FROM_{​ ​EMPREGADOS​ ​E1} GROUP_{​ ​BY​ ​E1.dcodigo} HAVING_{​ ​AVG(E1.salario)​ ​>​ ​(}

SELECT_{​ ​AVG(E2.salario)} FROM_{​ ​EMPREGADOS​ ​E2} WHERE_{​ ​E2.dcodigo​ ​=​ ​1​ ​)} )

--_{​ ​Solução​ ​02:​ ​Solução​ ​com​ ​visão}

CREATE_{​ ​VIEW​ ​V29​ ​(dnumero,​ ​med_sal_dep)​ ​AS} SELECT_{​ ​E.dcodigo,​ ​AVG(E.salario)}

FROM_{​ ​EMPREGADOS​ ​E} GROUP_{​ ​BY​ ​E.dcodigo;} SELECT_{​ ​E.nome,​ ​E.salario}

FROM_{​ ​EMPREGADOS​ ​E,​ ​V29​ ​V1} WHERE_{​ ​E.dcodigo​ ​=​ ​V1.dnumero}

AND_{​ ​V1.med_sal_dep​ ​>​ ​(} SELECT_{​ ​med_sal_dep} FROM_{​ ​V29​ ​V2} WHERE_{​ ​V2.dnumero​ ​=1} ); (41_{​ ​rows)}