#####
## si no tienen mysql en sus máquinas deben conectarse al servido anakena del DCC 
## haciendo lo siguiente desde una terminal (linux/mac)
##
## ejecutar: ssh pec-dato@anakena.dcc.uchile.cl
## password: mysql-dcc-2016
##
## para windows se debe ejecutar lo anterior desde una aplicacion para ssh como putty
##
## en lo que sigue xx representa el numero de grupo asignado
## una vez conectado a anakena hacer lo que sigue
##
## ejecutar: cd xx
## ejecutar: mysql -u pec16_27_u -p -h localhost pec16_27_db --local-infile
## password: sql-2016:xx
##
######

#####
## Comienze mirando los datos (donde pueda mirarlos)  
#####

#######
## Ejercicio 0: cree una tabla llamada 'ActaELA' usando la instruccion create table
## su tabla debe tener atributos id, fecha, pais, region, provincia, 
## comuna, tema, concepto, acuerdo, fundamento
#######



#######
# los siguientes son algunos comandos útiles
# muestra las tablas en mi base de datos
show tables; 

# describe una tabla en particular (es util para recordar los nombres de los atributos y sus tipos)
describe ActaELA;

#######
## el siguiente comando sirve para cargar datos desde un archivo
## donde dice '../elaEjercicio.csv' debe ir la ruta al archivo de datos .csv a cargar
## el comando está escrito pensando en quienes se conectaron al servidor 
#######

load data 
local infile '../elaEjercicio.csv' 
into table ActaELA 
fields terminated by ';' lines terminated by '\n';

## si todo funcionó bien esto debiera mostrar 100 tuplas de la tabla

select * 
from ActaELA 
limit 1000;

## OJO! estamos trabajando con datos de verdad, por lo que lo mejor será 
## limitar las consultas que creamos que entregarán muchos datos

## puede usar este comando para ver paginación mas "linda" (solo en linux/unix)
## se puede salir de la paginación con 'q'
## la paginación se puede eliminar usando nopager

pager less -SFX;
select * 
from ActaELA 
limit 1000;

########
## la estructura general de una consulta básica en SQL es 
## select <lista de atributos a mostrar>
## from <tabla desde donde sacar la informacion>
## limit <cantidad de tuplas a seleccionar> (esto es opcional)
## los comandos se terminan con un punto y coma para ser ejecutados en la línea de comandos
########

select fecha, comuna, concepto, acuerdo 
from ActaELA 
limit 1000; 

########
## se puede usar where <condicion que deben cumplir las tuplas> para seleccionar solo algunas
########

select fecha, comuna, concepto, acuerdo 
from ActaELA
where comuna = 'La Pintana' 
limit 1000; 

######## 
## Ejercicio 1: seleccione todos los conceptos y fundamentos para los cuales 
## no hay acuerdo (o sea que dicen 'Desacuerdo'). Recuerde limitar!
########



######## 
## Ejercicio 2: seleccione todas las comunas donde se mencionó el concepto 'Asamblea Constituyente'
## muestre también el concepto (no solo la comuna). No es necesario limitar en este caso
## (que pasa si escribe 'Asambleas Constituyentes'?)
########



########
## no solo el = se puede usar en la condicion where, tambien se puede usar el <> que quiere decir "distinto"
## y también >, <, >=, <=
## por ejemplo, la siguiente consulta entrega todos los conceptos mencionados en ELAs antes del 2016-24-04
########

select fecha, concepto 
from ActaELA 
where fecha < '2016-24-04';

######## 
## Ejercicio 3: invente dos consultas similares a las anteriores, e indique qué es lo que está consultando
########


######## 
## Ejercicio 4: la instruccion like se puede usar para preguntar por texto que sea similar a algo
## o que contenga algún otro texto.
## La siguiente consulta obtiene todos los conceptos y fundamentos donde se menciona la palabra "Presidenta".
## El símbolo '%' se usa como "comodín" para un texto de cualquier largo
########


######## 
## Ejercicio 5: cómo podría de forma simple hacer una consulta que obtenga los datos
## cuando se menciona la palabra "Presidente" o "Presidenta"?
########


######## 
## También se puede usar not like para decir que no quiero que el texto cumpla con la condición
## por ejemplo en la siguiente consulta obtiene todos los fundamentos que no tengan una "s"
########

select fundamento 
from ActaELA
where fundamento not like '%s%'
limit 1000;

######## 
## En el comando like se pueden usar también _ para denotar un único caracter que puede
## ser cualquiera
########

########
## El siguiente ejemplo muestra los conceptos y fundamentos 
## en Provincias cuyo nombre comienza con R y tiene largo 5
########

select provincia, comuna, concepto, fundamento 
from ActaELA
where comuna like 'R____'
limit 1000;

######## 
## Ejercicio 6: invente dos consultas similares a las anteriores 
## idealmente interesantes usando el comando like
########


########
## Hasta ahora nuestras consultas entregan valores repetidos, por ejemplo, esta entrega las 
## regiones donde se menciona "pena de muerte" en el fundamento.
## Analíce la respuesta. Por qué se repiten los valores?
########

select comuna 
from ActaELA
where fundamento like '%pena de muerte%';

########
## la instruccion distinct se puede usar para eliminar los registros duplicados
## se escribe antes de los atributos consultados de la forma
## select distinct <atributos>, como se compara el resultado de la consulta de abajo con la anterior?
########

select distinct comuna 
from ActaELA
where fundamento like '%pena de muerte%';


########
## Ejercicio 7: seleccione todas las provincias distintas 
## en donde se realizaron Encuentros Locales
########


########
## Ejercicio 8: seleccione todas las comunas distintas cuyo nombre empieza con R
## en donde se realizaron Encuentros Locales
########


#######
## Ejercicio 9: invente una consulta interesante en donde se utilice distinct y lo que ha aprendido hasta ahora
#######


########
## Si lo notó, hay valores nulos en algunos campos denotado por NULL
## el valor NULL no puede ser comparado con = o con like se necesita usar "is" o "is not"
## pruebe los siguientes ejemplos (mas sobre NULL en otras clases)
########

select distinct provincia
from ActaELA
where provincia is not NULL;

select distinct provincia
from ActaELA
where provincia is NULL;

#######
## Hasta ahora el where ha tenido una condición única, pero se pueden hacer consultas donde hayan
## varias condiciones vinculadas por operadores lógicos como "and" "or" "not"
## la siguiente consulta muestra información de actas que dicen "Asamblea Constituyente" como concepto 
## y que tienen "Acuerdo"
#######

select fecha, concepto, fundamento
from ActaELA
where acuerdo = 'Acuerdo' and concepto = 'Asamblea Constituyente';

#######
## se puede combinar con distinct, por ejemplo 
## la siguiente consulta
## entrega todas las comunas distintas de la provincia de Santiago 
## que mencionan "pena de muerte" en los fundamentos
#######

select distinct comuna
from ActaELA 
where fundamento like '%pena de muerte%' and provincia = 'Santiago';

#######
## Ejercicio 10: seleccione información de conceptos, fundamentos, etc
## para respuestas que no estan en el tema de INSTITUCIONES
## y cuyo concepto es 'Asamblea Constituyente'
#######


#######
## Ejercicio 11: seleccione información de conceptos, fundamentos, etc
## para respuestas de la comuna de Las Condes que mencionan "diversidad sexual" en los fundamentos
#######


#######
## Ejercicio 12: seleccione información de conceptos, fundamentos, etc
## para respuestas que mencionan simultaneamente las palabras Educación, Calidad, y Gratuita
#######


#######
## Ejercicio 13: seleccione información de conceptos, fundamentos, etc
## para respuestas tales que el concepto es 'Asamblea Constituyente' o en el fundamento se menciona 
## 'Asamblea Constituyente
#######


#######
## Ejercicio 14: invente 3 consultas interesantes que usen combinaciones complejas en el where
## y diga cual es la idea de lo que está obteniendo desde los datos
#######


#######
## Funciones de Agregacion: se pueden calcular valores agregados sobre los resultados de consultas
## las funciones más típicas son count, max, min, sum, avg
## también hay otras menos 
## La siguiente consulta cuenta las tuplas en la tabla ActaELA
#######

select count(id)
from ActaELA;

select count(region)
from ActaELA;

#####
## count se puede combinar con distinct
## la siguiente consulta cuenta la cantidad de regiones distintas en las que se realizaron ELAs
#####

select count(distinct region)
from ActaELA;

#####
## en este caso resulta interesante cambiar el nombre de la columna en el resultado usando as
#####

select count(distinct region) as regiones_total
from ActaELA;


#######
## Ejercicio 15: cuente la cantidad de comunas distintas en las que se realizaron ELAs
#######


#######
## en la misma consulta se pueden poner varias funciones de agregacion
## Ejercicio 16: cuente la cantidad de regiones, provincias y comunas en las que se realizaron ELAs
#######


#######
## Ejercicio 17: cuente en cuantos ELAs distintos se menciona el concepto 'Propiedad Privada'
#######


#######
## Ejercicio 18: use la función max y min para encontrar la fecha del primer y último ELA que mencionó
## la palabra aborto de entre todos los ELAs de Arica
#######


#######
## Ejercicio 19: invente 3 consultas interesantes que usen todo lo aprendido hasta ahora, incluyendo
## combinaciones complejas en el where, distinct, funciones de agregacion, like, etc.
## y diga cual es la idea de lo que está obteniendo desde los datos
#######


#######
## Instruccion group by
## hasta ahora la agergación se ha hecho sobre todo el resultado de una consulta.
## la instrucción 'group by' sirve para hacer agregaciones por grupo dentro de una consulta
#######

#######
## Compare el resultado de las dos consultas siguientes
#######

select count(distinct provincia) cantidad_provincias
from ActaELA; 

select count(distinct provincia) cantidad_provincias_por_region
from ActaELA 
group by region;

#######
## La ultima consulta cuenta las provincias distintas pero considerando tuplas donde la region es la misma
## si ademas seleccionamos la region obtenemos las regiones y las cantidades de provincias por region en donde
## se realizaron ELAs
#######

select region, count(distinct provincia) cantidad_provincias_por_region
from ActaELA 
group by region;

#######
## similarmente podemos hacerlo para las regiones y la cuenta de comunas por region
#######

select region, count(distinct comuna) cantidad_comunas_por_region
from ActaELA 
group by region;

#######
## Ejercicio 20: seleccione la cantidad de Enucentros locales distintos que se realizaron en cada una de las
## regiones del pais
#######