A煤n no tienes acceso a esta clase

Crea una cuenta y contin煤a viendo este curso

Curso de SQL y MySQL

Curso de SQL y MySQL

Alberto Alcocer

Alberto Alcocer

5 casos de negocio

19/24
Recursos

Aportes 95

Preguntas 9

Ordenar por:

驴Quieres ver m谩s aportes, preguntas y respuestas de la comunidad? Crea una cuenta o inicia sesi贸n.

Resumen de la clase:

-- 1. 驴Qu茅 nacionalidades hay?
-- Mediante la clausula DISTINCT trae solo los elementos distintos
SELECT DISTINCT nationality 
FROM authors
ORDER BY 1;

-- 2. 驴Cu谩ntos escritores hay de cada nacionalidad?
-- IS NOT NULL para traer solo los valores diferentes de nulo
-- NOT IN para traer valores que no sean los declarados (RUS y AUT)
SELECT nationality, COUNT(author_id) AS c_authors
FROM authors
WHERE nationality IS NOT NULL
	AND nationality NOT IN ('RUS','AUT')
GROUP BY nationality
ORDER BY c_authors DESC, nationality ASC;

Hola: Aqui les comparto mis respuestas

---驴Qu茅 nacionalidades hay?

SELECT nationality FROM authors GROUP BY nationality;

---驴Cuantos escritores hay de cada nacionalidad?

SELECT nationality, COUNT(author_id) as qty_authors FROM authors GROUP BY nationality;

---驴Cuantos libros hay de cada nacionalidad?

SELECT a.nationality, COUNT(b.book_id) as qty_book
FROM books as b
JOIN authors as a
ON b.author_id = a.author_id
GROUP BY a.nationality;

---驴Cual es el promedio/desviacion standard del precio de los libros?

SELECT AVG(price) as avg_price, STDDEV(price) as stddev_price FROM books;

---驴Cual es el precio maximo/minimo de un libro?

SELECT MAX(price) as max_price, MIN(price) as min_price FROM books;

---驴Cual es el precio maximo/minimo de un libro?

SELECT c.name, t.type, b.title, a.name, a.nationality
FROM transactions as t
LEFT JOIN clients as c
ON c.client_id = t.client_id
LEFT JOIN books as b
ON b.book_id = t.book_id
LEFT JOIN authors as a
ON b.author_id = a.author_id;

Este curso complementa perfectamente el de Fundamentos de bases de datos.

Hola, tambien hice una prueba con la linea de comandos; en la cual puse las 3 consultas en una archivo llamado prueba.sql y luego se lo envie a la mysql y el resultado le indique que me devuelva en un archivo llamado resultado.txt, aqui esta la linea de comando que realice:
mysql -u root -p -D cursoplatzi <鈥済:\prueba.sql鈥 >g:\resultado.txt

Trabajo con una terminal en windows.

Me puse el reto de responder a las 7 preguntas antes de ver la clase, esto es lo que sali贸 馃挭馃徏

-- 驴Qu茅 nacionalidades hay? 

SELECT nationality
FROM authors
WHERE nationality IS NOT NULL
GROUP BY nationality
ORDER BY nationality;

-- 驴Cuantos escritores hay de cada nacionalidad? 

SELECT nationality, COUNT(*) AS quantity_of_authors
FROM authors
GROUP BY nationality
ORDER BY nationality;

-- 驴Cuantos libros hay de cada nacionalidad? 

SELECT nationality, COUNT(*) AS quantity_of_books
FROM books AS b 
LEFT JOIN authors AS a
    ON b.author_id = a.author_id
GROUP BY nationality; 

-- 驴Cu谩l es el promedio/desviaci贸n est谩ndar del precio de libros? 

SELECT AVG(price) as `standard_deviation` FROM books; 

-- 驴Cu谩l es el promedio/desviaci贸n est谩ndar de la cantidad de autores por nacionalidad? 

SELECT AVG(quantity_of_authors) as standard_deviation_by_nationality FROM
    (SELECT COUNT(*) AS quantity_of_authors
    FROM authors
    GROUP BY nationality) 
    AS quantity_of_authors_by_nationality; 

-- 驴Cu谩l es el precio m谩ximo y m铆nimo de un libro?

SELECT price as max_price FROM books
ORDER BY price DESC
LIMIT 1;

SELECT price as min_price FROM books
WHERE price IS NOT NULL
ORDER BY price ASC
LIMIT 1; 

-- 驴C贸mo quedar铆a el reporte de pr茅stamos? 

SELECT c.name, b.title, 
(CASE 
    WHEN finished = 1 THEN 'returned'
    WHEN finished = 0 THEN 'owed'
END) AS `state`
FROM transactions AS t
INNER JOIN books AS b
    ON t.book_id = b.book_id
INNER JOIN clients AS c 
    ON t.client_id = c.client_id
WHERE (`type` = 'lend' and finished = 0) or `type` = 'return' 
ORDER BY `name`; 

Para el primer caso tambi茅n me funciono:
mysql> select nationality
-> from authors
-> group by nationality;

Resumen de la Clase:
Palabra Reservada:

  • Distinct: usada antes de una columna trae las diferentes opciones.

Complementos al filtrar y queremos excluir:

  • <> 鈥業nfoa excluir鈥
  • NOT IN 鈥業nfo a excluir鈥

Tip:

  • Importante orden de condiciones.

**C贸digo: **

-- Clase 17 5 Casos de Negocio

-- Preguntas a Queries
-- Que nacionalidades hay?
SELECT DISTINCT nationality FROM authors ORDER BY nationality;
-- Cuantos escritores hay de cada nacionalidad?
SELECT nationality, COUNT(author_id) AS c_authors
FROM authors
WHERE nationality IS NOT NULL
    AND nationality <> 'RUS'
GROUP BY nationality
ORDER BY c_authors DESC, nationality ASC;

SELECT nationality, COUNT(author_id) AS c_authors
FROM authors
WHERE nationality IS NOT NULL
    AND nationality NOT IN('RUS', 'AUT')
GROUP BY nationality
ORDER BY c_authors DESC, nationality ASC;
-- Cuantos libros hay de cada nacionalidad? Como reto
SELECT a.nationality, COUNT(b.book_id) AS c_books
FROM authors AS a 
LEFT JOIN books AS b 
    on a.author_id = b.author_id
WHERE nationality IS NOT NULL
GROUP BY nationality
ORDER BY c_books DESC;```

Biblioteca:

  1. f. Instituci贸n cuya finalidad consiste en la adquisici贸n, conservaci贸n, estudio y exposici贸n de libros y documentos.

Biblioteca: en ingl茅s Library

Librer铆a

  1. f. Tienda donde se venden libros.

Librer铆a en ingl茅s Bookstore

Fuente: RAE

Para no mostrar resultado NULL en la primera pregunta pueden agregar:

WHERE nationality IS NOT NULL;

Resultado:

SELECT DISTINCT nationality FROM authors WHERE nationality IS NOT NULL;
馃槂

el NOT y el IS NOT los he visto en filtros excluyentes, es decir podr铆amos querer ver el trafico de todo el mundo, pero excluyendo china y Rusia que podr铆a ser intentos de ataque, solo queremos ver la mayor铆a del trafico REAL, as铆 que podr铆amos excluir esos 2 paises. o por ejemplo, en una empresa podr铆amos querer ver a todos los empleados menos los operativos.

SELECT nationality , COUNT(author_id) AS c_authors
FROM authors
GROUP BY nationality
ORDER BY c_authors DESC, nationality ASC;
  1. SELECT nationality FROM authors GROUP by nationality

script pregunta 2.1

SELECT nationality, COUNT(author_id) FROM authors
GROUP BY nationality; 

Si fuera sin considerar el NULL como nacionalidad:
select nationality FROM authors WHERE nationality IS NOT NULL GROUP BY nationality;

QUE BUENOS CASOS

Querys de las Preguntas antes de la respuesta del profesor

  1. SELECT nationality FROM authors GROUP BY nationality;
  2. SELECT COUNT(author_id), nationality FROM authors GROUP BY nationality;
  3. SELECT COUNT(book_id), nationality FROM authors a INNER JOIN books b ON a.author_id=b.author_id GROUP BY nationality;
  4. SELECT AVG(price) as promedio from books;
  5. SELECT MAX(price), MIN(price) FROM books;
probando

1RA

SELECT  Count(DISTINCT nationality)
from authors;

+-----------------------------+
| Count(DISTINCT nationality) |
+-----------------------------+
|                          14 |
+-----------------------------+
1 row in set (1.49 sec)

2DA

mysql> SELECT nationality, COUNT(*) as Numero
    -> FROM authors
    -> GROUP BY nationality
    -> ORDER BY Numero DESC;
+-------------+--------+
| nationality | Numero |
+-------------+--------+
| NULL        |     71 |
| USA         |     27 |
| ENG         |     10 |
| IND         |      6 |
| RUS         |      4 |
| FRA         |      3 |
| AUT         |      2 |
| SWE         |      2 |
| GBR         |      1 |
| AUS         |      1 |
| MEX         |      1 |
| JAP         |      1 |
| ESP         |      1 |
| DEU         |      1 |
| COL         |      1 |
+-------------+--------+
15 rows in set (0.00 sec)

Segmentar busqueda con WHERE

El orden para filtrar en el where debe ser: primero filtrar las ocurrencias m谩s grandes segundo una vez reducido el grupo filtrar sobre las ocurrencias m谩s peque帽as

"<>" diferente

DISTINCT; esta palabra reservada antes de una columna garantiza que el valor no se repita a mostrar en un SELECT no se repita.

SELECT nationality, COUNT(author_id) AS C_AUTHOR FROM authors
WHERE nationality IS NOT NULL
GROUP BY nationality
ORDER BY C_AUTHOR DESC; 

Muy buenos casos practicos, los hice solo.

script pregunta 2.2

Funci贸n ORDER BY

Funci贸n ORDER BY

SELECT nationality AS nacionalidades, count(*) AS cantidad  from authors
WHERE nationality IS NOT NULL
GROUP BY nacionalidades
ORDER BY cantidad DESC;
SELECT nationality AS nacionalidades, count(*) AS cantidad  from authors
WHERE nationality IS NOT NULL
GROUP BY nacionalidades
ORDER BY cantidad DESC;

Entendido

SELECT nationality FROM authors;

SELECT DISTINCT nationality FROM authors;

SELECT DISTINCT nationality FROM authors ORDER BY nationality;

SELECT nationality FROM authors GROUP BY  nationality ;

SELECT nationality , COUNT(author_id) TOTAL FROM authors GROUP BY  nationality  ORDER BY TOTAL DESC;

SELECT nationality , COUNT(author_id) TOTAL FROM authors GROUP BY  nationality  ORDER BY TOTAL DESC, nationality;

SELECT nationality , COUNT(author_id) TOTAL FROM authors WHERE nationality IS NOT NULL
GROUP BY  nationality  ORDER BY TOTAL DESC, nationality;

SELECT nationality , COUNT(author_id) TOTAL FROM authors WHERE nationality <> 'RUS'
GROUP BY  nationality  ORDER BY TOTAL DESC, nationality;

SELECT nationality , COUNT(author_id) TOTAL FROM authors WHERE nationality NOT IN ('RUS')
GROUP BY  nationality  ORDER BY TOTAL DESC, nationality;

SELECT nationality , COUNT(author_id) TOTAL FROM authors WHERE nationality NOT IN ('RUS', 'AUT')
GROUP BY  nationality  ORDER BY TOTAL DESC, nationality;

SELECT nationality , COUNT(author_id) TOTAL FROM authors WHERE nationality IN ('RUS', 'AUT') GROUP BY  nationality  ORDER BY TOTAL DESC, nationality;

Cuando se usa IN importa que sean may煤sculas y min煤sculas?? Es posible usar expresiones regulares?

-- Traer libros y su author

SELECT b.title AS libro, a.name AS author
FROM books AS b
INNER JOIN authors AS a ON b.author_id = a.author_id
LIMIT 10;

-- Traer los autores que tienen mas de 1 libro.

SELECT a.author_id, a.name, a.nationality, COUNT(b.book_id) AS cantida_libros
FROM authors AS a
LEFT JOIN books AS b ON a.author_id = b.author_id
GROUP BY a.author_id
HAVING cantida_libros > 1
ORDER BY a.name
LIMIT 5;

-- Que nacionalidades hay

SELECT DISTINCT nationality
FROM authors;

-- Cuantos escritores hay de cada nacionalidad

SELECT nationality, COUNT(*) c_authors
FROM authors
WHERE nationality IS NOT NULL
GROUP BY nationality
ORDER BY c_authors DESC, nationality ASC;```

<>: Diferente

lo que buscamos con los querys es convertir los datos en informaci贸n

RESUMEN:


Este resumen muestra apuntes y c贸digo:

  1. 驴Que nacionalidades hay?
SELECT DISTINC nationality FROM authors; 
// Me trae solo las nacionalidades sin repetir, incluyendo el null.
SELECT DISTINC nationality FROM authors ORDER BY nationality; 
//me lo ordena en forma alfab茅tica de manera ascendente.

2.驴Cuantos escritores hay de cada nacionalidad?

SELECT nationality, COUNT(author_id) as c_authors
FROM authors
WHERE nationality IS NOT NULL
	AND nationality NOT IN ('RUS','AUT')
GROUP BY nationality
ORDER BY c_authors DESC, nationality   
// Puedo darle tantos par谩metros de ordenamiento como quiero. Ordena en orden de los par谩metros que le digo separados por coma.
;

// Le estoy diciendo, seleccione nacionalidad, cu茅ntemelos por el ID del autor y pongame una columna de esa cuenta que haga con el nombre/alias de c_authors
// Traiga todo esto de la tabla de autores, agrupelos por la nacionalidad y ordenelos de mayor a menor en t茅rminos de cantidad.
// Devu茅lvame todo lo que no sea nulo y discrimine a Rusia y Austria de la b煤squeda.

Me pareci贸 m谩s 煤til as铆 porque se identifica donde se concentra la mayor cantidad de autores:
SELECT a.nationality AS nacionalidad, COUNT(a.author_id) AS cant_autores
FROM authors AS a
WHERE a.nationality IS NOT NULL
GROUP BY a.nationality
ORDER BY cant_autores;

Me siento orgulloso de haber hecho los tres primeros querys sin necesidad de ver el video y con la misma precisi贸n del profe! Esto habla muy bien tambi茅n del profesor, y del profe Israel que dejo unas bases muy buenas en su curso de Fundamentos de Bases de datos, gracias!

Aqu铆 es donde sirve saber storytelling, para plantear bien los panoramas/necesidades de nuestras consultas

Para solucionar la primera pregunta 驴Qu茅 nacionalidades hay? use este Query y funciono :v

SELECT nationality, count(distinct nationality) FROM authors
group by nationality
ORDER BY nationality

Anexo mis consultas:

1-Cuantas nacionalidades hay

SELECT COUNT(distinct(a.nationality)) AS cant_nationalities
FROM authors AS a;

2-Cuantos escritores hay por cada nacionalidad

SELECT a.nationality AS nacionalidad, COUNT(a.author_id) AS cant_autores
FROM authors AS a
WHERE a.nationality IS NOT NULL
GROUP BY a.nationality
ORDER BY a.nationality;

3-Cuantos libros hay de cada nacionalidad

  SELECT a.nationality AS nacionalidad, COUNT(b.book_id) AS cant_libros
    FROM authors AS a
    JOIN books AS b
      ON a.author_id = b.author_id
   WHERE a.nationality IS NOT NULL
GROUP BY a.nationality
ORDER BY a.nationality;

4-Cual es el promedio/desviacion standard del precio de los libros. Adicionalmente segmentar por nacionalidad.

SELECT ROUND(AVG(b.price),2) AS 'Precio Promedio'
  FROM books AS b
 WHERE b.sellable = 1;

5-Cual es el precio maximo y minimo de un libro.

SELECT "Precio Maximo" AS 'Valor', MAX(b.price) AS 'Precio'
FROM books AS b
UNION
SELECT "Precio Minimo" AS 'Valor', MIN(b.price) AS 'Precio'
FROM books AS b;

6-Reporte de prestammos

SELECT c.name AS 'Nombre', t.modified_at AS 'Fecha', t.type AS 'Tipo', t.finished AS 'Completada', b.title as 'Titulo', a.name AS 'Autor'
FROM transactions AS t
JOIN clients AS c
on t.client_id = c.client_id
join books AS b
on t.book_id = b.book_id
join authors AS a
on b.author_id = a.author_id
ORDER BY t.modified_at;```

Tengo una pregunta.
驴Por qu茅 cuando se ejecuta el query 鈥淪ELECT DISTINCT nationality FROM authors ORDER BY nationality;鈥 en el output sale NULL antes de AUS?
卤------------+
| nationality |
卤------------+
| NULL |
| AUS |
| AUT |
| COL |
| DEU |
| ENG |
| ESP |
| FRA |
| GBR |
| IND |
| JAP |
| MEX |
| RUS |
| SWE |
| USA |
卤------------+

una consulta a que le llama tupla estimado profesor?

驴Alguien sabe si al hacer un filtrado por un tipo de descripci贸n tipo String de un campo, consume m谩s recursos que filtrando por tipo n煤mero? O.o

No entend铆 a que se refer铆a con ORDER BY RAND(). Alguien sabe?

Muy interesante COUNT() y GROUP BY

Me adelante un poco e hice los ejercicios a ver que tan parecidos salen :

--que nacionalidades hay?

SELECT nationality
FROM authors
WHERE nationality IS NOT NULL
GROUP BY nationality;

--cuantos escritores hay de cada nacionalidad?

SELECT nationality,COUNT(author_id) AS "# de authores"
FROM authors
WHERE nationality IS NOT NULL
GROUP BY nationality
ORDER BY nationality ASC;

--cuantos libros hay de cada nacionalidad?

SELECT nationality,COUNT(b.book_id) AS "# de libros"
FROM authors AS a
INNER JOIN books AS b 
ON b.author_id=a.author_id
WHERE a.nationality IS NOT NULL
GROUP BY nationality
ORDER BY nationality ASC;

--cual es el promedio/desviacion estandar del precio de libros?

SELECT STDDEV(price),AVG(price)
FROM books;

--idem, pero por nacionalidad

SELECT a.nationality,STDDEV(b.price) AS "desviacion estandar",AVG(b.price) AS "promedio"
FROM books AS b
INNER JOIN authors AS a  
ON b.author_id=a.author_id
WHERE a.nationality IS NOT NULL
AND b.price IS NOT NULL
GROUP BY nationality
ORDER BY nationality ASC;

--cual es el precio maximo/minimo de un libro?

SELECT MAX(price),MIN(price)
FROM books;

--reporte de prestamos

SELECT type,COUNT(transaction_id) AS "cantidad de operaciones"
FROM transactions
GROUP BY type
HAVING type IS NOT NULL;```

Utilizar NULL en lenguajes orientados a objetos es considerado un anti-patr贸n de dise帽o.

Un antipatr贸n de dise帽o es un patr贸n de dise帽o que invariablemente conduce a una mala soluci贸n para un problema.

Un patr贸n de dise帽o cubre las t茅cnicas para resolver problemas comunes en el desarrollo de software y otros 谩mbitos referentes al dise帽o de interacci贸n o interfaces.

Que buena clase

/*1. 驴Qu茅 nacionalidades hay?*/
SELECT DISTINCT nationality
FROM authors ORDER BY nationality ASC;

/*2. 驴Cuantos escritores hay de cada nacionalida*/
SELECT nationality, COUNT(name) AS c_authors
FROM authors
WHERE nationality IS NOT NULL AND nationality NOT IN ('RUS')
GROUP BY nationality
ORDER BY c_authors DESC, nationality ASC;

El operador IN le permite especificar m煤ltiples valores en una cl谩usula WHERE.

El operador IN es una abreviatura de m煤ltiples condiciones OR.

excelente curso

INTERESANTE CURSO

Awesome class!

Prueben con estas lineas de consultas y a ver que les muestra:
select distinct nationality from authors;
select nationality from authors group by nationality;
select distinct nationality, COUNT(nationality) AS cant from authors;

Hola, tambien hice una prueba con la linea de comandos; en la cual puse las 3 consultas en una archivo llamado prueba.sql y luego se lo envie a la mysql y el resultado le indique que me devuelva en un archivo llamado resultado.txt, aqui esta la linea de comando que realice:
mysql -u root -p -D cursoplatzi <鈥済:\prueba.sql鈥 >g:\resultado.txt

Trabajo con una terminal en windows.

Otra forma en vez de utilizar IS NOT NULL

SELECT nationality, COUNT(*) AS Num_Authors
FROM authors
WHERE nationality NOT IN(鈥淣ULL鈥, 鈥淩US鈥)
GROUP BY nationality
ORDER BY Num_Authors DESC, nationality;

excelentes clases

SELECT nationality, COUNT(author_id) AS c_authors
FROM authors
WHERE nationality IS NOT NULL
AND nationality NOT IN (鈥楻US鈥, 鈥楢UT鈥)
GROUP BY nationality
ORDER BY c_authors DESC, nationality ASC;

鈥 QUE NACIONALIDADES HAY

SELECT DISTINCT nationality FROM authors;

鈥 CUANTOS ESCRITORES HAY DE CADA NACIONALIDAD
SELECT nationality, COUNT(author_id) AS c_authors FROM authors WHERE nationality IS NOT NULL AND nationality NOT IS ('RUS', 'AUT') GROUP BY nationality ORDER BY c_authors DESC, nationality ASC ;

Yo hice lo mismo que Facundo y resolv铆 el ejercicio con anterioridad.
He aqu铆 mi resultado:

-- 1. 驴Qu茅 ncaionalidades hay de los autores?
SELECT DISTINCT nationality FROM authors ORDER BY nationality;

-- 2. 驴Cu谩ntos escritores hay de cada nacionalidad?
SELECT DISTINCT nationality, COUNT(author_id) AS cantAuthors 
FROM authors GROUP BY nationality ORDER BY cantAuthors, nationality;

-- 3. 驴Cu谩ntos libros hay de cada nacionalidad?
SELECT DISTINCT nationality, COUNT(book_id) AS numBooks FROM books AS b 
INNER JOIN authors AS a ON b.author_id = a.author_id GROUP BY nationality ORDER BY numBooks;

-- 4. 驴Cu谩l es el promedio/desviaci贸n est谩ndar del precio de los libros?
SELECT AVG(price) AS avgPrice FROM books;

-- 5. 驴Cu谩l es el promedio/desviaci贸n est谩ndar de la cantidad de autores por nacionalidad?
SELECT AVG(cantAuthors) AS avgCantAuthPerNat  
FROM (SELECT DISTINCT COUNT(author_id) AS cantAuthors FROM authors GROUP BY nationality) AS CantAuthPerNat ;

-- 6. 驴Cu谩l es el precio m谩ximo y m铆nimo de los libros?
SELECT MAX(price) AS maxPrice, MIN(price) AS minPrice FROM books;

-- 7. 驴C贸mo quedar铆a el reporte de pr茅stamos?
SELECT t.transaction_id, c.name,  b.title, t.modified_at AS `date`, t.finished AS status FROM transactions AS t 
INNER JOIN clients AS c on t.client_id = c.client_id  INNER JOIN books AS b ON t.book_id = b.book_id
WHERE type = 'lend' ORDER BY t.finished, t.transaction_id;```

La siguiente consulta arroja el lenguaje de los libros por autor (author_id)

select author_id, language, count(author_id) as c_languaje_books
from books 
group by author_id, languag;
  • En este formato
author_id language c_languaje_books
3 en 2
3 es 1
  • Como deber铆a elaborar la consulta (solo con la tabla books) si deseo que me muestre el resultado de la siguiente manera
author_id c_languaje_books_es c_languaje_books_en
1 1 1
3 1 2

Gracias por la ayuda

"convention over configuration"
beco

--Que nacionalidades hay?
SELECT DISTINCT(nationality)
FROM authors
ORDER BY nationality;

SELECT DISTINCT nationality
FROM authors
ORDER BY nationality;

SELECT nationality
    ,COUNT(*) AS Cantidad
FROM authors
GROUP BY nationality
ORDER BY COUNT(*) DESC
    ,nationality;

SELECT nationality
    ,COUNT(*) AS Cantidad
FROM authors
GROUP BY nationality
ORDER BY Cantidad DESC
    ,nationality;

SELECT nationality
    ,COUNT(*) AS Cantidad
FROM authors
GROUP BY nationality
ORDER BY 2 DESC
    ,nationality;

script pregunta 1

script pregunta 2

script pregunta 2

script pregunta 2

PRIMERO

SELECT  nationality 
FROM    authors 
WHERE   nationality IS NOT NULL 
GROUP BY nationality
ORDER BY nationality;

SEGUNDO

SELECT  nationality, COUNT(author_id) AS cant_authors 
FROM    authors 
WHERE   nationality IS NOT NULL 
GROUP BY nationality
ORDER BY cant_authors DESC;

TERCERO

SELECT  B.nationality, COUNT(*) AS cant_books
FROM    books A
        INNER JOIN authors B ON A.author = B.author_id
WHERE   B.nationality IS NOT NULL 
GROUP BY B.nationality
ORDER BY cant_books DESC;

Excelente clase.

馃彜 Soluci贸n a los dos primeros casos de uso

-- 1. 驴Qu茅 nacionalidades hay?
SELECT nationality FROM authors GROUP BY nationality;
SELECT DISTINCT nationality FROM authors;

-- 2. 驴Cu谩ntos escritores hay de cada nacionalidad?
SELECT nationality, COUNT(author_id) AS authors FROM authors
WHERE nationality IS NOT NULL
GROUP BY nationality ORDER BY authors DESC, nationality ASC;

Buenis铆mo!!

Tener en cuenta que el orden de las condiciones es importante

Me parece que como lo han mencionado las condiciones que vamos estructurando en cada una de las querys es importante ya que como lo menciona el profesor nos ayuda a hacer consultas mas detalladas

El de cantidad de libros por nacionalidad me parece que se puede resolver as铆:

SELECT a.nationality AS Nacionalidad, COUNT(b.book_id) AS Cantidad
 FROM authors AS a
 INNER JOIN books as b ON a.author_id = b.author_id
 GROUP BY a.nationality
 HAVING Nacionalidad IS NOT NULL
 ORDER BY Cantidad DESC, Nacionalidad ASC; ```

No respondimos la pregunta 3, yo la hice as铆:

SELECT a.nationality, COUNT(b.book_id) AS c_books
FROM authors AS a
JOIN books AS b
ON a.author_id = b.author_id
WHERE nationality IS NOT NULL
GROUP BY nationality
ORDER BY c_books DESC, nationality ASC;

No se que piensen, recibo recomendaciones.

DISTINCT se usa para devolver solo valores distintos. Dentro de una tabla, una columna a menudo contiene muchos valores duplicados; y, a veces, solo desea enumerar los diferentes valores (distintos).

驴QUE NACIONALIDADES HAY?
select nationality from authors;
select DISTINCT nationality from authors
select DISTINCT nationality from authors ORDER BY nationality;

驴CUANTOS ESCRITORES HAY DE CADA NACIONALIDAD?
SELECT NATIONALITY, COUNT(AUTHOR_ID) AS CNT_AUTHORS
FROM AUTHORS
WHERE NATIONALITY IS NOT NULL
鈥 AND NATIONALITY <> 'RUS鈥
AND NATIONALITY NOT IN (鈥楻US鈥, 鈥楢UT鈥)
GROUP BY NATIONALITY
ORDER BY CNT_AUTHORS DESC, NATIONALITY ASC;

Conversion over configuration

驴Que nacionalidades hay?

SELECT nationality 
FROM authors;

SELECT DISTINCT nationality 
FROM authors;



驴Cu谩ntos escritores hay por nacionalida?

SELECT DISTINCT nationality, COUNT(author_id) AS c_authors
FROM authors 
GROUP BY nationality
ORDER BY c_authors DESC
;

SELECT DISTINCT nationality, COUNT(author_id) AS c_authors
FROM authors 
GROUP BY nationality
ORDER BY c_authors DESC, nationality ASC
;


SELECT DISTINCT nationality, COUNT(author_id) AS c_authors
FROM authors 
WHERE nationality IS NOT NULL
    AND nationality NOT IN ('RUS', 'AUT')
GROUP BY nationality
ORDER BY c_authors DESC, nationality ASC
;

/*
Translating questions to queries!
Th questions are focused on the library example we were working on through out the course

  1. How many nationalities are there?
  2. How many writters are in each nationality?
  3. How many books are in each nationality?
  4. What is the averge/standard deviation of the book鈥檚 prices?
  5. Same as 4, but with nationalities
  6. What is the max/min of books
  7. How would the report end up looking like?

*/

鈥 1)

SELECT DISTINCT nationality as Nationality
FROM authors as a
ORDER BY a.nationality;

鈥 2)

SELECT a.nationality as Nationality, COUNT(a.id_author) as `Amount`
FROM authors as a
WHERE a.nationality IS NOT NULL
GROUP BY a.nationality
ORDER BY Amount;

NOT IN
NOT LIKE

Pausen el video en el min 1:00 y traten de hacerlo ustedes solos primero. Eso les ayudar谩 mucho en su aprendizaje. Yo creo en ustedes [email protected]

Resumen:

-- 1. 驴Que nacionalidades hay?
SELECT distinct nationality FROM authors
ORDER BY nationality;

---2. 驴Cuantos escritores hay de cada nacionalidad?

SELECT nationality, count(author_id) AS c_authors
FROM authors
WHERE  nationality IS NOT NULL
    AND nationality  IN('RUS','AUS')
GROUP BY nationality 
ORDER BY c_authors DESC, nationality ASC;
/* 驴Qu茅 nacionalidades hay? */

SELECT nationality FROM authors WHERE nationality IS NOT NULL GROUP BY nationality;

/* 驴Cu谩ntos escritores hay de cada nacionalidad? */

SELECT author_id, nationality, COUNT(author_id) AS total FROM authors WHERE nationality
IS NOT NULL GROUP BY nationality;

/* 驴Cu谩ntos libros hay de cada nacionalidad? */

SELECT a.nationality, COUNT(book_id) AS total_libros
FROM books AS b INNER JOIN authors AS a ON 
a.author_id = b.author_id 
GROUP BY nationality;

/* 驴Cu谩l es el promedio/desviaci贸n standard del precio de libros */

SELECT AVG(price) AS promedio FROM books;

/* idem, pero por nacionalidad */

/* 驴Cu谩l es el precio m谩ximo/minimo de un libro */

SELECT MAX(price) AS valor_max, MIN(price) AS valor_min
FROM books;

/* 驴C贸mo quedar铆a el reporte de prestamos */

SELECT t.transaction_id, b.title, 
c.name, (CASE 
WHEN finished = 1 THEN 'Devuelto'
WHEN finished = 0 THEN 'Pendiente'
END) AS estado FROM transactions AS t INNER JOIN books AS b ON
t.book_id = b.book_id INNER JOIN clients AS c ON
t.client_id = c.client_id 
WHERE type = 'lend';```

RETO TERMINADOOOO 

Respond铆 las preguntas de la siguiente manera:

/*
1.- 驴Qu茅 nacionalidades hay?
2.- 驴Cu谩ntos escritores hay de cada nacionalidad?
*/
select nationality, count(author_id) as c_authors
from authors
where nationality is not null
	and nationality  not in('RUS', 'COL')
group by nationality
order by c_authors desc, nationality asc;

/*
3.- 驴Cu谩ntos libros hay de cada nacionalidad?
*/

SELECT COUNT(b.book_id) as cant_libros, a.nationality
FROM books as b
JOIN authors AS a
	on a.author_id = b.author_id
WHERE a.nationality is not NULL
GROUP BY  a.nationality
ORDER BY cant_libros DESC;

/*
4.- 驴Cu谩l es el promedio/desviaci贸n est谩ndar del precio de libros?
*/

SELECT sellable, AVG(price)  AS promedio, STD(price) AS Des_standard
FROM books
WHERE price IS NOT NULL
GROUP BY sellable;

/*
5.- 驴Cu谩l es el promedio/desviaci贸n est谩ndar del precio de libros por nacionalidad?
*/
SELECT b.sellable, AVG(b.price)  AS promedio, STD(b.price) AS Des_standard, a.nationality
FROM books as b
JOIN authors as a
	ON a.author_id = b.author_id
WHERE price IS NOT NULL
GROUP BY a.nationality;

Muy bien explicado

Un peque帽o resume sobre el uso de GROUP BY

Para quienes quieran poblar la tabla transactions aleatoreamente con datos de las otras tablas

INSERT INTO transactions(book_id,client_id,type)
SELECT books.book_id,clients.client_id,'sell'
FROM books,clients ORDER BY RAND() LIMIT 5;

Ahi se guardaran 5 registros, cambien el 5 por la cantidad de registros que uieran guardar.

Traduciendo preguntas a Queries de SQL en Casos de Negocio:

.
1.- 驴Qu茅 nacionalidades hay?

SELECT DISTINCT nationality FROM authors
ORDER BY nationality;

2.- 驴Cu谩ntos escritores hay de cada nacionalidad?

--- importante agrupar por columna correcta al utilizar COUNT
SELECT nationality, COUNT(author_id) AS c_authors
FROM authors
WHERE nationality IS NOT NULL
GROUP BY nacionality
ORDER BY authors DESC, nacionality ASC

3.- 驴Cu谩ntos libros hay de cada nacionalidad?
4.- 驴Cu谩l es el promedio/desviaci贸n est谩ndar del precio de libros?
5.- 驴Cu谩l es el promedio/desviaci贸n est谩ndar del precio de libros por nacionalidad?
6.- 驴Cu谩l es el precio m谩ximo/m铆nimo de un libro?
7.- 驴C贸mo quedar铆a el reporte de pr茅xtamos?

Palabra Reservada:
Distinct: usada antes de una columna trae las diferentes opciones.
Si queremos excluir:
<> 鈥業nformaci贸n a excluir鈥

no puedo superar los 190 y cachos de nacionalidades

驴C贸mo har铆a para mostrar 煤nicamente los pa铆ses d贸nde haya m谩s de 2 o 3 autores?