--#############################################################
--##################  Aula 13/14 (30-03-2006)  ################
--#############################################################

/*
#######################################################
GROUP BY
Permite organizar os dados resultado de uma query de acordo com um determinado critério
Obter para cada aluno a média das notas dos exames realizados
#######################################################
*/
--todas as colunas da clasula select deverão estar contidas em funções de agregação
--ou referenciadas na clasula GROUP BY

--Selecciona alunos e média das notas agrupando a informação por aluno
select
	alunos.idAluno, avg(cast(nota as float)) as media
from
	alunos,exames
where
	alunos.idAluno = exames.idAluno
group by
	alunos.idAluno


/*
#######################################################
HAVING
Critérios q são aplicados depois de os dados terem sido agrupados.
Permite aplicar critérios de selecção sobre o resultado calculado para cada grupo
#######################################################
*/

--Selecciona alunos, nº de exames e média das notas de alunos cujas notas 
--sejam superiores a 5 e inferiores a 15, agrupando a informação por aluno e cuja
--média das notas seja positiva
select
	alunos.idAluno, count(*) as nºexames, avg(cast(nota as float)) as media
from
	alunos,exames	
where
	alunos.idAluno = exames.idAluno AND nota > 5 and nota <15 
group by
	alunos.idAluno
having
	avg(cast(nota as float))>=10

select * from exames

/*
#######################################################
TOP
limita o nº de registos retornados por uma query
#######################################################
*/

--Selecciona os 3 funcinários mais bem pagos
select top 3 *
from funcionarios
order by salario desc

--Selecciona 5% dos funcionários mais bem pagos
select top 25 PERCENT *
from funcionarios
order by salario desc

/*
#############################################
SUBQUERYS
#############################################
SubQueries Escalares
é um statement SELECT colocado dentro de uma instrução SQL
#############################################
*/


--Seleccionar informação sobre a nota mais baixa
select * from exames 
where nota = (select max(nota) from exames)

select * from exames where nota = max(nota)

--Seleccionar nome e nota do exame que teve a nota mais baixa
select nome,nota
from exames,alunos 
where exames.idAluno = alunos.idAluno and nota = (select min(nota) from exames)