/

*########################################################################
#########################################################################
#############################*/
/*DISTINCT*/
SELECT DISTINCT (CUSTOMER_ID) FROM [Link];

SELECT DISTINCT (STORE_ID) FROM [Link];

/
*########################################################################
#########################################################################
#############################*/

/*ORDER BY*/
SELECT* FROM [Link] ORDER BY COUNTRY DESC;

SELECT* FROM [Link] ORDER BY COUNTRY ASC;

SELECT* FROM [Link] ORDER BY FIRST_NAME ASC;

SELECT STORE_ID AS TIENDA, FIRST_NAME AS NOMBRE, LAST_NAME AS APELLIDO

FROM [Link] ORDER BY APELLIDO DESC; /*Cambia el nombre de las
columnas store_id, first_name y last_name a Tienda, Nombre y Apellido
respectivamente. Ordena de manera descendente la columna Apellido*/

SELECT DISTINCT (AMOUNT) FROM [Link] ORDER BY AMOUNT ASC; /*¿Cuál

es la cantidad mas baja y mas alta de la columna amount?*/
/
*########################################################################
#########################################################################
#############################*/

/*WHERE*/
SELECT* FROM [Link] WHERE first_name = 'DAN';

SELECT* FROM [Link] WHERE COUNTRY_ID = 102;

SELECT* FROM [Link] WHERE STORE_ID = 1;

SELECT* FROM [Link] WHERE FILM_ID > 50;

SELECT DISTINCT AMOUNT FROM [Link] WHERE AMOUNT < 3; /*SELECCIONA

TODOS LOS VALORES MENORES A 3 DE AMOUNT Y ADEMAS TODOS LOS QUE SON
DISTINTOS*/

SELECT* FROM [Link] WHERE STAFF_ID != 2;

SELECT* FROM [Link] WHERE NAME != 'FRENCH';

/*EXERCISES*/
SELECT DESCRIPTION, RELEASE_YEAR FROM [Link]; /*Consulta
description, release_year de la tabla film de la base de datos sakila.*/

SELECT* FROM [Link] WHERE TITLE = 'IMPACT ALADDIN'; /*Filtra la

información donde title sea IMPACT ALADDIN*/
SELECT* FROM [Link]; /*Consulta la tabla payment de la base de
datos sakila.*/

SELECT* FROM [Link] WHERE AMOUNT > 0.99; /*Filtra la información

donde amount sea mayor a 0.99.*/
/
*########################################################################
#########################################################################
#############################*/

/*AND OR NOT*/

/*AND*/
SELECT *FROM [Link] WHERE COUNTRY = 'ALGERIA' AND COUNTRY_ID = 2;

/*OR*/
SELECT *FROM [Link] WHERE COUNTRY = 'ALGERIA' OR COUNTRY_ID = 12;

SELECT *FROM [Link] WHERE LANGUAGE_ID = 1 OR NAME='GERMAN';

/*NOT*/
SELECT* FROM [Link] WHERE NOT NAME='ACTION';

SELECT* FROM [Link] WHERE NOT RATING = 'PG';

SELECT DISTINCT (RATING) FROM [Link] WHERE NOT RATING = 'PG';

/*EXERCISES*/
SELECT* FROM [Link]; /*Consulta la tabla payment de la base de
datos sakila.*/

SELECT* FROM [Link] WHERE CUSTOMER_ID = 36 AND AMOUNT > 0.99 AND
STAFF_ID = 1; /*Filtra la información donde customer_id sea igual a 36,
amount sea mayor a 0.99 y staff_id sea igual a 1.*/

SELECT* FROM [Link];/*Consulta la tabla rental de la base de datos

sakila.*/

SELECT* FROM [Link] WHERE NOT STAFF_ID = 1 AND CUSTOMER_ID > 250
AND INVENTORY_ID < 100;/*Filtra la información donde staff_id no sea 1,
customer_id sea mayor a 250 y inventory_id sea menor de 100*/
/
*########################################################################
#########################################################################
#############################*/

/*IN*/
SELECT* FROM [Link] WHERE FIRST_NAME IN ('MARY', 'PATRICIA');

SELECT* FROM [Link] WHERE SPECIAL_FEATURES IN ('Trailers',

'Trailers,Deleted Scenes') AND RATING IN('G','NC-17') AND LENGTH > 50;

SELECT* FROM [Link] WHERE NAME NOT IN

('ACTION','ANIMATION','CHILDREN');
/*EXERCISES*/
SELECT* FROM SAKILA.FILM_TEXT; /*Consulta la tabla film_text de la base
de datos sakil*/

SELECT* FROM SAKILA.FILM_TEXT WHERE TITLE IN ('ZORRO ARK', 'VIRGIN

DAISY', 'UNITED PILOT'); /*Filtra la información donde title sea ZORRO
ARK, VIRGIN DAISY, UNITED PILOT*/

SELECT* FROM [Link]; /*Consulta la tabla city de la base de datos

sakila.*/

SELECT* FROM [Link] WHERE CITY IN ('Chiayi', 'Dongying', 'Fukuyama',

'Kilis'); /*Filtra la información donde city sea Chiayi, Dongying,
Fukuyama y Kilis.*/
/
*########################################################################
#########################################################################
#############################*/

/*BETWEEN*/
SELECT* FROM [Link] WHERE (CUSTOMER_ID BETWEEN 300 AND 350) AND
STAFF_ID=1;

SELECT* FROM [Link] WHERE AMOUNT NOT BETWEEN 3 AND 5;

/*EXCERCISES*/
SELECT* FROM [Link];/*Consulta la tabla payment de la base de
datos sakila.*/

SELECT* FROM [Link] WHERE (AMOUNT BETWEEN 2.99 AND 4.99) AND
(STAFF_ID = 2) AND (CUSTOMER_ID IN(1, 2));/*Filtra la información donde
amount esté entre 2.99 y 4.99, staff_id sea igual a 2 y customer_id sea
1 y 2.*/

SELECT* FROM [Link];/*Consulta la tabla address de la base de

datos sakila.*/

SELECT* FROM [Link] WHERE (CITY_ID BETWEEN 300 AND 350);/*Filtra

la información donde city_id esté entre 300 y 350.*/

SELECT* FROM [Link];/*Consulta la tabla film de la base de datos

sakila.*/

SELECT* FROM [Link] WHERE (RENTAL_RATE BETWEEN 0.99 AND 2.99) AND
(LENGTH <= 50) AND (REPLACEMENT_COST < 20);/*Filtra la información donde
rental_rate esté entre 0.99 y 2.99, length sea menor igual de 50 y
replacement_cost sea menor de 20.*/
/
*########################################################################
#########################################################################
#############################*/

/*LIKE*/
SELECT* FROM [Link] WHERE FIRST_NAME LIKE 'A%' AND LAST_NAME LIKE
'B%';
SELECT* FROM [Link] WHERE FIRST_NAME LIKE '%A' AND LAST_NAME LIKE
'%N';

SELECT* FROM [Link] WHERE FIRST_NAME LIKE '%NE%' AND LAST_NAME LIKE
'%RO%'; /*BUSQUEDA DE COINCIDENCIAS ENTREMEDIO*/

SELECT* FROM [Link] WHERE FIRST_NAME LIKE 'A%E'; /*BUSQUEDA DE

COINCIDENCIAS EN PRINCIPIO Y FINAL*/

SELECT* FROM [Link] WHERE FIRST_NAME LIKE 'C%N' AND LAST_NAME LIKE
'G%';

/*EXCERCISES*/
SELECT* FROM [Link]; /*Consulta la tabla film de la base de datos
sakila.*/

SELECT* FROM [Link] WHERE RELEASE_YEAR = 2006 AND TITLE LIKE 'ALI%';
/*Filtra la información donde release_year sea igual a 2006 y title
empiece con ALI.*/

/
*########################################################################
#########################################################################
#############################*/

/*JOINS*/

/*INER JOIN*/
SELECT* FROM [Link] F INNER JOIN [Link] L ON (F.LANGUAGE_ID
= L.LANGUAGE_ID);

SELECT [Link], [Link], F.RELEASE_YEAR, [Link]

FROM [Link] F INNER JOIN [Link] L ON (F.LANGUAGE_ID =
L.LANGUAGE_ID); /*MISMA COSULTA QUE ANTERIOR PERO MAS LIMPIA*/

SELECT [Link] AS DIRECCION, [Link] AS CIUDAD

FROM [Link] A INNER JOIN [Link] C ON (A.CITY_ID =
C.CITY_ID);

SELECT C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, A.ACTOR_ID,

A.FIRST_NAME, A.LAST_NAME
FROM [Link] C RIGHT JOIN [Link] A ON (C.LAST_NAME =
A.LAST_NAME);

SELECT C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, A.ACTOR_ID,

A.FIRST_NAME, A.LAST_NAME
FROM [Link] C LEFT JOIN [Link] A ON (C.LAST_NAME =
A.LAST_NAME);

/*EXCERCISES*/
SELECT* FROM [Link];/*Consulta la tabla address de la base de
datos sakila.*/

SELECT*
FROM [Link] C INNER JOIN [Link] CT ON (C.CITY_ID =
CT.COUNTRY_ID);/*Realiza un INNER JOIN con las tablas city y country*/

SELECT [Link], [Link], [Link]

FROM [Link] C
INNER JOIN [Link] A ON (C.CITY_ID = A.CITY_ID)
INNER JOIN [Link] CO ON (C.CITY_ID = CO.COUNTRY_ID); /*Mostrar
las columnas address, city, country*/

SELECT* FROM [Link]; /*Consulta la tabla customer de la base de

datos sakila.*/

SELECT*
FROM [Link] S LEFT JOIN [Link] A ON (S.STORE_ID =
A.ADDRESS_ID);/*Realiza un LEFT JOIN con las tablas store y address*/

SELECT C.FIRST_NAME, [Link], S.STORE_ID

FROM [Link] S
LEFT JOIN [Link] A ON (S.STORE_ID = A.ADDRESS_ID)
LEFT JOIN [Link] C ON (C.CUSTOMER_ID =
S.MANAGER_STAFF_ID);/*Mostrar las columnas first_name, address,
store_id*/

SELECT C.FIRST_NAME, [Link], S.STORE_ID

FROM [Link] C
LEFT JOIN [Link] S ON (C.STORE_ID = S.STORE_ID)
LEFT JOIN [Link] A ON (C.ADDRESS_ID = A.ADDRESS_ID); /*Mostrar
las columnas first_name, address, store_id*/

SELECT* FROM [Link];/*Consulta la tabla rental de la base de datos

sakila.*/

SELECT*
FROM [Link] R
INNER JOIN [Link] S ON (R.STAFF_ID = S.STAFF_ID);/*Realiza un INNER
JOIN con la tabla staff*/

/
*########################################################################
#########################################################################
#############################*/

/*COUNT - AVG - SUM - MAX - MIN*/

/*SUM*/
SELECT* FROM [Link];
SELECT SUM(AMOUNT) FROM [Link]; /*Suma todos los valores de una
columna*/

SELECT INVENTORY_ID + FILM_ID + STORE_ID

FROM [Link]; /*Suma los valores entre columnas*/

/*COUNT*/
SELECT* FROM [Link];
SELECT COUNT(*) FROM [Link]; /*CUENTA LA CANTIDAD DE REGISTROS EN
LA TABLA*/
SELECT COUNT(FIRST_NAME) FROM [Link];

/*AVG*/
SELECT AVG(AMOUNT) FROM [Link]; /*PROMEDIO DE UNA COLUMNA*/
SELECT AVG(RENTAL_DURATION) FROM [Link];

/*MAX - MIN*/
SELECT MAX(LENGTH) FROM [Link];
SELECT MIN(LENGTH) FROM [Link];
SELECT MIN(REPLACEMENT_COST) FROM [Link];
SELECT MAX(REPLACEMENT_COST) FROM [Link];

/*EXERCISES*/
SELECT* FROM [Link];/*Consulta la tabla rental de la base de datos
sakila.*/

SELECT COUNT(RENTAL_ID) FROM [Link];/*Realiza un COUNT de la

columna rental_id*/

SELECT* FROM [Link];/*Consulta la tabla payment de la base de

datos sakila.*/

SELECT MAX(AMOUNT) FROM [Link];/*Realiza un MAX de la columna

amount*/

/
*########################################################################
#########################################################################
#############################*/
/*GROUP BY*/
SELECT LAST_NAME, COUNT(*) FROM [Link]
GROUP BY LAST_NAME;

SELECT C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, SUM([Link])

FROM [Link] P INNER JOIN [Link] C ON (C.CUSTOMER_ID =
P.CUSTOMER_ID)
GROUP BY 1,2,3;

/*EXCERCISES*/
SELECT* FROM [Link] R;/*Consulta la tabla rental de la base de
datos sakila.*/

SELECT MAX(RENTAL_DATE), CUSTOMER_ID FROM [Link] GROUP BY

CUSTOMER_ID; /*Realiza un MAX de la columna rental_date*/

/
*########################################################################
#########################################################################
#############################*/
/*HAVING*/
SELECT AMOUNT, COUNT(*) FROM [Link]
GROUP BY AMOUNT
HAVING COUNT(*) <= 10; /*HAVING SE UTILIZA EN CONJUNTO CON COUNT PARA
MEDIR EL VALOR DE COUNT CON <, >, =, SE UTILIZA POR QUE WHERE NO PUEDE
HACERLO*/

SELECT LAST_NAME, COUNT(*)

FROM [Link]
GROUP BY LAST_NAME
HAVING COUNT(*) > 3;

SELECT C.CUSTOMER_ID, C.LAST_NAME, C.FIRST_NAME, SUM([Link])

FROM [Link] P
INNER JOIN [Link] C ON (P.CUSTOMER_ID = C.CUSTOMER_ID)
GROUP BY 1,2,3
HAVING SUM([Link]) < 60
ORDER BY SUM([Link]) DESC;

/*EXERCISES*/
SELECT LAST_NAME, COUNT(*) FROM [Link]
GROUP BY LAST_NAME; /*Realiza un COUNT de last_name*/

SELECT LAST_NAME, COUNT(*) FROM [Link]

GROUP BY LAST_NAME
HAVING COUNT(*) > 2;/*Mostrar cuando el COUNT sea mayor de 2*/

/
*########################################################################
#########################################################################
#############################*/
/*FUNCIONES UTILES*/

/*CHAR_LENGTH*/
SELECT NAME, CHAR_LENGTH(NAME) AS LONGITUDCADENA
FROM [Link];

SELECT CITY, CHAR_LENGTH(CITY) AS LONGITUDCIUDAD

FROM [Link];

/*CONCAT*/
SELECT*, CONCAT(FIRST_NAME, ' ',LAST_NAME) AS NOMBRE_COMPLETO FROM
[Link];

SELECT CONCAT_WS(" - ", TITLE, DESCRIPTION, RELEASE_YEAR) FROM

[Link]; /*CONCATENACION CON MUCHAS COLUMNAS*/

/*ROUND*/
SELECT*, ROUND(AMOUNT, 0) FROM [Link]; /*REDONDEAR VALORES
NUMERICOS*/

/*LOWER CASE, UPPER CASE - MAYUSCULAS Y MINUSCULAS*/

SELECT*, LCASE(CONCAT(FIRST_NAME, "", LAST_NAME)) AS "NOMBRE COMPLETO"
FROM [Link]; /*MINUSCULAS*/

SELECT*, UCASE(CONCAT(FIRST_NAME, "", LAST_NAME)) AS "NOMBRE COMPLETO"

FROM [Link]; /*MAYUSCULAS*/

/*EXERCISES*/
SELECT EMAIL, CHAR_LENGTH(EMAIL) FROM [Link];/*Usa la función
CHAR_LENGTH() en la tabla customer y calcula la longitud de la columna
email.*/

SELECT CONCAT_WS(" ", FIRST_NAME, LAST_NAME, EMAIL) FROM [Link];

/*Usa la función CONCAT_WS() en la tabla film y has un concatenado de
todas las columnas.*/

SELECT CUSTOMER_ID, ROUND(AVG(AMOUNT),0) FROM [Link]

GROUP BY CUSTOMER_ID;/*Consulta la tabla payment y has un group by por
customer_id para el promedio de amount, después usa la función ROUND()
para redondear a cero decimales el promedio.*/

SELECT*, UCASE(CITY) FROM [Link] AS CIUDAD;/*Usa la función UCASE()

en la tabla city y convierte la columna city en mayúsculas.*/

/
*########################################################################
#########################################################################
#############################*/
/*CASE*/
SELECT* FROM [Link];

SELECT ADDRESS, ADDRESS2,

CASE
WHEN ADDRESS2 IS NULL THEN "SIN DIRECCION"
ELSE "CON DIRECCION"
END AS COMENTARIO
FROM [Link];

SELECT* FROM [Link];

SELECT PAYMENT_ID, AMOUNT,

CASE
WHEN AMOUNT < 1 THEN "PRECIO MINIMO"
WHEN AMOUNT BETWEEN 1 AND 3 THEN "PRECIO INTERMEDIO"
ELSE "PRECIO MAXIMO"
END AS COMENTARIO
FROM [Link];

/*EXERCISES*/

/*Usa la función CASE en la tabla film y calcula 3 casos, si rental_rate

es menor que 1 ingresa "Pelicula Mala",
si la calificacion esta dentro de 1 y 3 que muestre "Pelicula Buena", si
es mayor que muestre "Pelicula Excelente"*/
SELECT TITLE,
CASE
WHEN RENTAL_RATE < 1 THEN "PELICULA MALA"
WHEN RENTAL_RATE BETWEEN 1 AND 3 THEN "PELICULA BUENA"
ELSE "PELICULA EXCELENTE"
END AS COMENTARIO
FROM [Link];

/
*########################################################################
#########################################################################
#############################*/
/*SUBCONSULTAS*/
SELECT* FROM [Link];

SELECT TITLE FROM [Link]

WHERE TITLE LIKE 'K%' OR TITLE LIKE 'Q%'
AND TITLE IN (SELECT TITLE FROM [Link] WHERE LANGUAGE_ID IN
(SELECT
LANGUAGE_ID FROM [Link] WHERE NAME = 'English'));

SELECT* FROM [Link];

SELECT FIRST_NAME, LAST_NAME

FROM [Link]
WHERE ACTOR_ID IN (SELECT ACTOR_ID FROM SAKILA.FILM_ACTOR WHERE FILM_ID
IN
(
SELECT FILM_ID FROM [Link] WHERE TITLE = 'Alone Trip'));

SELECT TITLE FROM [Link]

WHERE FILM_ID IN (SELECT FILM_ID FROM SAKILA.FILM_CATEGORY WHERE
CATEGORY_ID IN

(SELECT CATEGORY_ID FROM [Link] WHERE NAME = 'Family'));

/*EXERCISES*/
/*FILM,FILM_CATEGORY, CUSTOMER, RENTAL*/
/*SELECT FIRST_NAME, LAST_NAME, EMAIL
FROM [Link]
WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM [Link] WHERE
INVENTORY_ID IN

() )*/
/**Encuentre el nombre completo y la dirección de correo electrónico de
todos los clientes que hayan alquilado una película de acción.*/

/
*########################################################################
#########################################################################
#############################*/
/*VIEWS - VISTAS*/

CREATE VIEW INGRESOS_POR_GENERO AS

SELECT [Link],SUM([Link])
FROM [Link] C
INNER JOIN SAKILA.FILM_CATEGORY FC
ON (C.CATEGORY_ID = FC.CATEGORY_ID)
INNER JOIN [Link] I
ON (FC.FILM_ID = I.INVENTORY_ID)
INNER JOIN [Link] R
ON (I.INVENTORY_ID = R.INVENTORY_ID)
INNER JOIN [Link] P
ON (R.RENTAL_ID = P.RENTAL_ID)
GROUP BY [Link]
ORDER BY SUM([Link]) DESC LIMIT 5;

SELECT* FROM INGRESOS_POR_GENERO;

/*arreglar vista que no funciona*/