Bienvenida conceptos b谩sicos y contexto hist贸rico de las Bases de Datos

1

Bienvenida conceptos b谩sicos y contexto hist贸rico de las Bases de Datos

2

Playground: tu primera consulta en bases de datos

Introducci贸n a las bases de datos relacionales

3

Historia de las bases de datos relacionales

4

Qu茅 son entidades y atributos

5

Entidades de Platzi Blog

6

Relaciones

7

M煤ltiples muchos

8

Diagrama ER

9

Diagrama F铆sico: tipos de datos y constraints

10

Diagrama F铆sico: normalizaci贸n

11

Formas normales en Bases de Datos relacionales

12

Diagrama F铆sico: normalizando Platziblog

RDBMS (MySQL) o c贸mo hacer lo anterior de manera pr谩ctica

13

Instalaci贸n local de un RDBMS (Windows)

14

驴Qu茅 es RDB y RDBMS?

15

Instalaci贸n local de un RDBMS (Mac)

16

Instalaci贸n local de un RDBMS (Ubuntu)

17

Clientes gr谩ficos

18

Servicios administrados

SQL hasta en la sopa

19

Historia de SQL

20

DDL create

21

Playground: CREATE TABLE

22

CREATE VIEW y DDL ALTER

23

DDL drop

24

Playground: VIEW, ALTER y DROP en SQL

25

DML

26

Playground: CRUD con SQL

27

驴Qu茅 tan standard es SQL?

28

Creando Platziblog: tablas independientes

29

Creando Platziblog: tablas dependientes

30

Creando Platziblog: tablas transitivas

Consultas a una base de datos

31

驴Por qu茅 las consultas son tan importantes?

32

Estructura b谩sica de un Query

33

SELECT

34

Playground: SELECT en SQL

35

FROM y SQL JOINs

36

Utilizando la sentencia FROM

37

Playground: FROM y LEFT JOIN en SQL

38

WHERE

39

Utilizando la sentencia WHERE nulo y no nulo

40

Playground: Filtrando Datos con WHERE

41

GROUP BY

42

ORDER BY y HAVING

43

Playground: Agrupamiento y Ordenamiento de Datos

44

El interminable agujero de conejo (Nested queries)

45

驴C贸mo convertir una pregunta en un query SQL?

46

Pregunt谩ndole a la base de datos

47

Consultando PlatziBlog

48

Playground: Prueba Final con PlatziBlog

Introducci贸n a la bases de datos NO relacionales

49

驴Qu茅 son y cu谩les son los tipos de bases de datos no relacionales?

50

Servicios administrados y jerarqu铆a de datos

Manejo de modelos de datos en bases de datos no relacionales

51

Top level collection con Firebase

52

Creando y borrando documentos en Firestore

53

Colecciones vs subcolecciones

54

Recreando Platziblog

55

Construyendo Platziblog en Firestore

56

Proyecto final: transformando tu proyecto en una db no relacional

Bases de datos en la vida real

57

Bases de datos en la vida real

58

Big Data

59

Data warehouse

60

Data mining

61

ETL

62

Business intelligence

63

Machine Learning

64

Data Science

65

驴Por qu茅 aprender bases de datos hoy?

Bonus

66

Bases de datos relacionales vs no relacionales

67

Elegir una base de datos

No tienes acceso a esta clase

隆Contin煤a aprendiendo! 脷nete y comienza a potenciar tu carrera

Playground: Agrupamiento y Ordenamiento de Datos

43/67

Aportes 76

Preguntas 2

Ordenar por:

驴Quieres ver m谩s aportes, preguntas y respuestas de la comunidad?

o inicia sesi贸n.

Una peque帽a correccion: en la descripcion del problema, en el primer item, en vez de decir que la tabla se llama teachers (en plural) dice que se llama teacher (en singular)

SELECT
  teachers.name AS teacher,
  SUM(courses.n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses
ON teachers.id = courses.teacher_id
GROUP BY teacher
ORDER BY total_reviews DESC;

Estan a otro nivel los retos, no pude 馃槮
Al menos los intent茅.

Les dejo mi analisis y solucion:

  • Tengo dos tablas:

    • courses: id, name, n_reviews y teacher_id
    • teachers: id y name.

    (De lo anterior sabemos que courses.teacher_id y teachers .id son lo mismo, por lo que haremos un INNER JOIN hacioendo coincidir estos dos valores)
    FROM teachers
    INNER JOIN courses ON teachers.id = courses.teacher_id

  • Nos piden SELECCIONAR la SUMA total de reviews en todos los cursos por cada profesor. Es decir:

    • SELECT teachers. name, SUM(courses.n_reviews).
      En este punto, vale aclarar que nos piden nombrar estos puntos, por lo que:

    SELECT teachers.name AS teacher, SUM (courses.n_reviews) AS total_reviews

  • Piden AGRUPAR por profesores para luego ORDENAR:
    GROUP BY courses.teacher_id

  • ORDENAR de forma DESCENDENTE el total de reviews (total_reviews) que encontramos anteriormente:
    ORDER BY total_reviews DESC

  • Por ultimo, las reviews deben ser mayores a 0. Lo hacemos con HAVING ya que es una coluimna que se genera despues:
    HAVING total_reviews > 1

Basandonos en lo anterior, organizando la informacion en el orden correcto (ver la clase anterior) la solucion a la que llegue seria:

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses ON teachers.id = courses.teacher_id
GROUP BY courses.teacher_id
HAVING total_reviews > 1
ORDER BY total_reviews DESC;

Mi Soluci贸n
.
.
.
.

<SELECT teachers.name AS teacher,SUM(courses.n_reviews) AS total_reviews
FROM teachers 
inner join courses ON teachers.id = courses.teacher_id
GROUP BY courses.teacher_id
ORDER BY total_reviews DESC;
> 

Soluci贸n鈥 馃槃
.

.
.
.
.
.
.

SELECT t.name AS teacher, SUM(c.n_reviews) AS total_reviews
FROM teachers AS t
INNER JOIN courses AS c ON t.id = c.teacher_id
GROUP BY c.teacher_id
ORDER BY total_reviews DESC;

Mi solucion
.
.
.
.
.
.
.
.
.
.

SELECT 
  teachers.name AS teacher,
  SUM(n_reviews) AS total_reviews
FROM teachers  
INNER JOIN courses
ON teachers.id = courses.teacher_id
GROUP BY teachers.name
ORDER BY total_reviews DESC;

Fue un reto realmente鈥
Espero lo intentes pero si te frusta por ac谩 te dejo mi soluci贸n, la idea es que puedas analizarlo y no solo transcribir la respuesta.

Les comparto el c贸digo de la prueba con comentarios de los pasos para que lo puedan entender mejor 馃槂


Mi t茅cnica es ir de menos a m谩s鈥 es decir tomo la parte m谩s sencilla del requerimiento y voy probando que funcione, as铆 le voy agregando las funcionalidades m谩s complejas del requerimiento hasta completarlo, de esta manera en todo momento estoy seguro hasta que punto el desarrollo est谩 funcionando y voy solucionando cada problema o error que se va presentando!

Con las tablas/entidades abreviadas鈥 馃槉

Perd铆 una vida pero lo logr茅, jaja

select c.teacher_id,c.name
from courses c
order by c.teacher_id

Los ejercicios en el playground deber铆an revisarse en la prueba por el resultado que arrojas, ya que si lo haces algo diferente me da error y luego al correr la soluci贸n que presentan en el ejercicio obtengo la misma respuesta que con el c贸digo que escrib铆, pero igual al correr la prueba me sale que hay errores.

Asi se fue XD

SELECT teachers.name AS teacher, sum(courses.n_reviews) AS total_reviews
FROM teachers,courses
WHERE teachers.id = courses.teacher_id 
GROUP BY teacher
ORDER BY total_reviews DESC
;
SELECT t.name AS teacher, sum(c.n_reviews) AS total_reviews
FROM teachers AS t, courses AS c 
WHERE t.id = c.teacher_id
GROUP BY teacher
ORDER BY total_reviews desc;

buena practica

SELECT
  teachers.name AS teacher, 
  sum(n_reviews) AS total_reviews
FROM
  courses
INNER JOIN 
  teachers ON courses.teacher_id=teachers.id  
GROUP BY
  teacher_id
ORDER BY
  total_reviews DESC
;

Comparto mi soluci贸n, el query funciona si omito el WHERE Y HAVING, pero decido dejarlo as铆 por las condiciones que pide el ejercicio 馃槂

SELECT teachers.name AS teacher, SUM(n_reviews) AS total_reviews FROM courses
INNER JOIN teachers ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL
GROUP BY courses.teacher_id
HAVING total_reviews > 1
ORDER BY total_reviews DESC;

Despues de media hora y dos dias de repaso me salio, lagrimas de felicidad :鈥)


SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM courses
RIGHT JOIN teachers ON teachers.id = courses.teacher_id
WHERE teacher_id IS NOT NULL
GROUP BY teacher
ORDER BY total_reviews DESC
; 

Aqu铆 mi aporte con LEFT JOIN
.
.
.
.
.
.
.

SELECT t.name AS teacher,
SUM(c.n_reviews) AS total_reviews
FROM courses AS c
LEFT JOIN teachers AS t
ON c.teacher_id = t.id
WHERE t.id IS NOT NULL
GROUP BY teacher
ORDER BY total_reviews DESC
;

mi aporte

SELECT 

teachers.name AS teacher,

SUM(n_reviews) AS total_reviews

FROM courses
LEFT JOIN teachers ON courses.teacher_id = teachers.id

WHERE teacher_id IS NOT NULL

GROUP BY teacher
ORDER BY total_reviews DESC;

SELECT
teachers. name AS teacher ,
SUM(courses. n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses
ON teachers. id = courses.teacher_id
GROUP BY teacher
ORDER BY total_reviews DESC

Buenas tardes muchachos ac谩 est谩n mis aportes

<SELECT 
teachers.name AS teacher,
SUM (courses.n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses ON teachers.id = courses.teacher_id
GROUP BY teacher_id
ORDER BY Total_reviews DESC;> 
SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews FROM teachers
INNER JOIN courses ON teachers.id = courses.teacher_id
GROUP BY teachers.id
ORDER BY total_reviews DESC;

Con lucha pero se pudo

SELECT teachers.name AS teacher,SUM(n_reviews) AS total_reviews
FROM courses 
LEFT JOIN teachers ON teacher_id = teachers.id
GROUP BY teacher_id HAVING teacher_id is not null
ORDER BY total_reviews DESC;

Aqui mi propuesta de solucion, me llama la atencion como en las demas propuestas de solucion los compa帽eros hacen el join desde teachers como tabla de la cual jalan la informacion
Segun a mi me ense帽aron en un join debes poner tu from desde la tabla donde est茅 la segunda tabla como foranea, o bien la tabla en donde este contenida la informacion de varias tablas, para despues poder explotar correctamente las relaciones, digo en esta ocasi贸n no importa mucho pero me pareci贸 muy curioso que colocaran el from desde una tabla independiente y no de la tabla donde estan enlazadas ambas

.
.
.
.
.
.
.
.
.
.

SELECT t.name AS teacher, SUM(c.n_reviews) AS total_reviews
FROM teachers t
INNER JOIN courses c ON t.id = c.teacher_id
WHERE c.n_reviews > 0
GROUP BY t.id, t.name
ORDER BY total_reviews DESC;

SELECT teachers.id, teachers.name AS teacher, SUM(courses.n_reviews) AS    total_reviews
  FROM teachers
  LEFT JOIN courses ON teachers.id = courses.teacher_id
  GROUP BY courses.teacher_id
  HAVING total_reviews > 0
  ORDER BY total_reviews DESC;

Hola les comparto mi query, ojo que no est谩 bien, en mi caso no le铆 la parte de no contemplar los cursos sin review y tampoco renombre la columna t. name

<


SELECT t.name, sum(c.n_reviews) as total_reviews
FROM teachers t 
left JOIN courses c
ON t.id = c.teacher_id
Group by t.name
Order by total_reviews desc
;  
>

Aqu铆 el correcto:

<

SELECT t.name as tracher, sum(c.n_reviews) as total_reviews
FROM teachers t 
left JOIN courses c
ON t.id = c.teacher_id
WHERE c.teacher_id IS NOT NULL
Group by t.name
Order by total_reviews desc
; 


> 

Aqu铆 mi aporte a la soluci贸n:

SELECT
  T.name as teacher,
  SUM(C.n_reviews) AS total_reviews
FROM teachers AS T
INNER JOIN courses AS C
ON T.id = C.teacher_id
GROUP BY teacher
ORDER BY total_reviews DESC
;

Mi solucion









SELECT teachers.name AS teacher, 
SUM(n_reviews) AS total_reviews
FROM courses
INNER JOIN teachers ON courses.teacher_id = teachers.id
WHERE teacher_id IS NOT NULL
GROUP BY teacher_id
ORDER BY total_reviews DESC
;
-- Escribe tu c贸digo aqu铆 馃憞
SELECT teachers.name AS teacher, SUM(n_reviews) AS total_reviews
FROM courses
RIGHT JOIN teachers on teachers."id" = courses.teacher_id
GROUP BY teachers.name
HAVING total_reviews > 0
ORDER BY total_reviews DESC

mi solucion:

<SELECT teachers.name as teacher,sum(courses.n_reviews) as total_reviews FROM courses INNER JOIN teachers
on teachers.id=courses.teacher_id
where courses.teacher_id  is not null
and courses.n_reviews>=1
group by teacher
order by total_reviews DESC> 
-- -- Escribe tu c贸digo aqu铆 馃憞
SELECT teachers.name as teacher, SUM(n_reviews) as total_reviews
FROM courses
RIGHT JOIN teachers on teachers."id" = courses.teacher_id
GROUP BY teachers.name
HAVING total_reviews > 0
ORDER BY total_reviews DESC
;

Si se pudo!!! 馃コ馃コ馃コ

-- Escribe tu c贸digo aqu铆 馃憞
-- SELECT * FROM courses;

-- SELECT * FROM teachers;

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM courses JOIN teachers
WHERE teacher_id = teachers.id
GROUP BY teacher
ORDER BY total_reviews DESC;

Mi respuesta:

SELECT teachers.name AS teacher, SUM(n_reviews) AS total_reviews FROM teachers LEFT JOIN courses ON teachers.id = courses.teacher_id  GROUP BY teacher HAVING total_reviews>1 ORDER BY total_reviews DESC ;

mi soluci贸n .
.
.
.
.
.
.
.

SELECT name AS teacher , SUM(n_reviews) AS total_reviews FROM(
SELECT teachers.name , n_reviews FROM courses left JOIN teachers
ON courses.teacher_id = teachers.id where teachers.id is not null)  GROUP BY teacher ORDER BY total_reviews DESC;

LEE ESTO ANTES DE EMPEZAR

En la descripcion dice que la tabla se llama teacher pero hay un error y la tabla se llama teachers.

Para que no te quedes pensando como loco cual es el error jeje

y tambien no se si soy el unico pero la consola me muestra que mi codigo esta bien y cumple las codiciones pero las pruebas nop y por lo que veo hay un error de entendimiento entre la consola y las pruebas, como sea aqui les dejo mi codigo espero les sirva.

SELECT teachers.name as teachers, sum(courses.n_reviews) as total_reviews
from teachers inner join courses on teachers.id = courses.teacher_id
group by teachers
having total_reviews >1
order by total_reviews desc;

43. Playground: Agrupamiento y Ordenamiento de Datos

-- Escribe tu c贸digo aqu铆 馃憞
SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM courses
INNER JOIN teachers ON courses.teacher_id = teachers.id
GROUP BY teachers.name
ORDER BY total_reviews DESC;

Mi soluci贸n:

SELECT SUM(courses.n_reviews) AS total_reviews, teachers.name AS teacher
FROM courses
INNER JOIN teachers
ON courses.teacher_id = teachers.id
GROUP BY teacher
HAVING total_reviews > 0
ORDER BY total_reviews DESC;

Mi soluci贸n!

SELECT teachers.name as teacher, SUM(n_reviews) as total_reviews
FROM teachers
  INNER JOIN courses ON  teachers.id = courses.teacher_id 
  GROUP BY teacher
  ORDER BY total_reviews desc;

Si estuvo bien dif铆cil pero se logr贸!
*
Aqu铆 est谩 mi soluci贸n por si alguien se quiere guiar:

select 
teachers.name AS teacher, 
sum(courses.n_reviews) AS total_reviews
from courses 
left join teachers on courses.teacher_id = teachers.id
group by teachers.id, teachers.name
having count (courses.id) > 0 and courses.teacher_id is not null
order by total_reviews desc;

Como qued贸 muy largo el query decid铆 cambiar el nombre de las tablas y ponerles un alias 馃挌

Ac谩 pueden probar el c贸digo:

SELECT t.name AS teacher, SUM(c.n_reviews) AS total_reviews 
FROM teachers AS t
INNER JOIN courses AS c
ON c.teacher_id = t.id
GROUP BY c.teacher_id
ORDER BY total_reviews DESC;

Mi soluci贸n:

SELECT t.name AS teacher, SUM(c.n_reviews) AS total_reviews
FROM courses c
INNER JOIN teachers t
ON c.teacher_id = t.id
GROUP BY teacher
ORDER BY total_reviews DESC; 

Teniendo clara la teor铆a de conjuntos se deber铆a aplicar un INNER JOIN pero realice el ejercicio con un LEFT JOIN para tener que utilizar el HAVING y aplicar lo visto en la clase.
Igualmente no realice los filtros con los alias ya que en queries mas complejos se puede prestar para confusiones.

SELECT t.name       as teacher
  ,sum(c.n_reviews)  as total_reviews
FROM teachers     t
left join courses c on c.teacher_id = t.id
group by t.name
having sum(c.n_reviews) is not null
order by sum(c.n_reviews) desc;
<SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews 
FROM teachers INNER JOIN courses on teachers.id = courses.teacher_id
GROUP BY teacher
HAVING total_reviews >=1 AND teacher IS NOT NULL
ORDER BY total_reviews DESC;> 

De mi lado use el Having

SELECT 
      T.name AS teacher,
      SUM(C.n_reviews) AS total_reviews
FROM courses AS C
INNER JOIN teachers AS T ON C.teacher_id=T.id
GROUP BY 1
HAVING C.n_reviews>1
ORDER BY 2 DESC
;
SELECT teachers.name as teacher, SUM(courses.n_reviews) as total_reviews
FROM courses
INNER JOIN teachers ON courses.teacher_id=teachers.id
GROUP BY teacher
ORDER BY total_reviews DESC

Comparto mi soluci贸n 馃憞

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM courses
INNER JOIN teachers ON courses.teacher_id = teachers.id
GROUP BY teacher
ORDER BY total_reviews DESC;

Aqu铆 Mi soluci贸n y la explico:

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses ON teachers.id = courses.teacher_id
GROUP BY courses.teacher_id
ORDER BY total_reviews DESC;

EXPLICACION:

1- SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews:

* La cl谩usula SELECT especifica las columnas que se incluir谩n en el resultado de la consulta.
* "teachers.name AS teacher" selecciona la columna "name" de la tabla "teachers" y la renombra como "teacher".
* "SUM(courses.n_reviews) AS total_reviews" calcula la suma de la columna "n_reviews" de la tabla "courses" y la renombra como "total_reviews".
* FROM teachers INNER JOIN courses ON teachers.id = courses.teacher_id:

2- La cl谩usula FROM especifica las tablas de las cuales se obtendr谩n los datos.
* "teachers" y "courses" son las tablas que se utilizan en la consulta.
* "INNER JOIN" combina las filas de las tablas "teachers" y "courses" bas谩ndose en la condici贸n siguiente.
* "teachers.id = courses.teacher_id" es la condici贸n de uni贸n que establece que el ID del profesor en la tabla "teachers" debe ser igual al ID del profesor en la tabla "courses".

3- GROUP BY courses.teacher_id:

* La cl谩usula GROUP BY agrupa los resultados por el ID del profesor en la tabla "courses".
Esto significa que las filas con el mismo ID de profesor se agrupar谩n juntas.

4- ORDER BY total_reviews DESC:

* La cl谩usula ORDER BY ordena los resultados en funci贸n de la columna "total_reviews" de forma descendente (de mayor a menor).
* Esto significa que los profesores con el mayor n煤mero de revisiones totales aparecer谩n primero en el resultado.

-- En resumen, esta consulta recuperar谩 los nombres de los profesores y la suma total de revisiones de los cursos que han impartido, ordenados por el n煤mero total de revisiones en orden descendente. --

Soluci贸n al problema:

SELECT teachers.name AS teacher, SUM(n_reviews) AS total_reviews
FROM courses
INNER JOIN teachers ON courses.teacher_id = teachers.id
WHERE teacher_id IS NOT NULL 
GROUP BY teachers.name
ORDER BY total_reviews DESC;

Ejercicio agrupamiento y ordenamiento de datos:

SELECT t.name AS teacher, SUM(c.n_reviews) AS total_reviews
FROM courses c
INNER JOIN teachers t
ON c.teacher_id = t.id
GROUP BY teacher
ORDER BY total_reviews DESC

Se suele utilizar las iniciales del nombre de cada tabla para traer el valor de la columna. Por ejemplo, en este caso: courses (c) y necesito de courses la columna 鈥渘_reviews鈥 por lo que quedar铆a ==> << c.n_reviews >>

Esta es mi soluci贸n, puede diferenciarse un poco con algunas respuestas, ya que no realizo INNER JOIN directamente con la sentencia 鈥淚NNER鈥, la validacion la realizo con (teachers. id = courses.teacher_id) la cual esta en el "WHERE"
Espero sirva de ayuda y o si desean aportar alguna correcci贸n bienvenida sea.

SELECT  teachers.name AS teacher, 
        SUM(courses.n_reviews) AS total_reviews
FROM courses, teachers
WHERE teachers.id = courses.teacher_id
    AND courses.n_reviews>0
GROUP BY courses.teacher_id
ORDER BY total_reviews DESC;

Comparto mi aporte
*
*

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM teachers
  INNER JOIN courses ON teachers.id = courses.teacher_id
  GROUP BY courses.teacher_id
  ORDER BY total_reviews DESC
;


馃榿Logrado! Excelente que los retos vayan aumentando en complejidad, eso nos desaf铆a a aplicar todo lo aprendido hasta ahora.
.
.
.
.
.
.
.
.

SELECT teachers.name AS teacher, SUM(n_reviews) AS total_reviews
FROM courses
JOIN teachers ON teachers.id = courses.teacher_id
GROUP BY teacher
ORDER BY total_reviews DESC;

Soluci贸n
.
.
.
.
.

SELECT teachers.name AS teacher, sum(n_reviews) AS total_reviews 
FROM courses 
INNER JOIN teachers ON teacher_id = teachers.id
GROUP BY teacher
ORDER BY total_reviews DESC
;

Mi c贸digo est谩 aqu铆:

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses ON teachers.id = courses.teacher_id
GROUP BY teacher
ORDER BY total_reviews DESC;

Wuuu Resuelto !!
.
.
.
.
.
.
.
.
.
.
.
.

Hola a todos, les comparto mi c贸digo para este laboratorio, realmente interesante estos retos que nos plantea Platzi.
隆A seguir creciendo!

<SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews 
FROM teachers INNER JOIN courses ON teachers.id = courses.teacher_id
GROUP BY teacher
HAVING total_reviews >= 1
ORDER BY total_reviews DESC;> 
SELECT teachers.name AS teacher, SUM(n_reviews) AS total_reviews
FROM courses
INNER JOIN teachers ON teacher_id = teachers.id
GROUP BY teacher
ORDER BY total_reviews DESC;
select t.name as teacher, sum(c.n_reviews) as total_reviews
from courses c
join teachers t on t.id = c.teacher_id
group by t.name
order by total_reviews desc

Comparto mi Query

SELECT 
  teachers.name AS teacher,
  SUM(Courses.n_reviews) AS total_reviews
FROM Courses
  INNER JOIN teachers ON Courses.teacher_id = teachers.id
GROUP BY teacher
HAVING total_reviews >= 1
ORDER BY total_reviews DESC;

Me result贸 un poco complejo completar porque no sab铆a de la funci贸n SUM(), la confund铆 con COUN, pero al final logr茅 obtener el resultado, aqui les dejo mi respuesta

SELECT 
  teachers.name AS teacher,
  SUM(courses.n_reviews) AS total_reviews
FROM courses
  INNER JOIN teachers ON courses.teacher_id = teachers.id
GROUP BY teachers.name
ORDER BY total_reviews DESC;

<code> 
SELECT * FROM courses;
SELECT
teachers.name AS teacher,
SUM(courses.n_reviews) AS total_reviews
FROM courses
LEFT JOIN teachers ON teachers.id = courses.teacher_id
WHERE teacher_id IS NOT NULL
GROUP BY courses.teacher_id
ORDER BY total_reviews DESC
;

MI soluci贸n:
.
.
.
.
.
.

.

.
.
.

.
.
.
.
.
.
.
.
.
.

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM courses
INNER JOIN teachers ON courses.teacher_id = teachers.id
GROUP BY teacher
HAVING n_reviews>1
ORDER BY total_reviews DESC;

Estos playground son de lo mejor que pudieron hacer en platzi!! te imaginas que tuvieran mas de un ejercicio? o un mega ejercicio complementario tipo entrevista ?
esta de locos !
Mi soluci贸n:

SELECT teachers.name AS teacher,SUM(courses.n_reviews) total_reviews
FROM courses
INNER JOIN teachers ON  courses.teacher_id = teachers.id
GROUP BY teacher
ORDER BY total_reviews DESC

Piyenlon:







SELECT teachers.name as teacher, SUM(courses.n_reviews) as total_reviews
FROM courses
INNER JOIN teachers
ON courses.teacher_id = teachers.id
GROUP BY teachers.name
ORDER BY total_reviews DESC
;

Mi respuesta 馃槂

SELECT t.name as teacher, sum(c.n_reviews) as total_reviews
from courses c
INNER JOIN teachers t on c.teacher_id = t.id
GROUP BY teacher
ORDER BY total_reviews DESC ;

En mi caso us茅 la sentencia where/having para considerar la restricci贸n de no contar cursos sin al menos un review. S茅 que en este caso es redundante porque los cursos sin reviews impl铆citamente se sumar谩n como 0 y dar谩 igual el resultado, pero en caso de no contar reviews sino otra columna (como cantidad de cursos por profesor) s铆 ser铆a necesario agregar la restricci贸n.

select 
  teachers.name as teacher, 
  sum(courses.n_reviews) as total_reviews
from teachers
inner join courses on teachers.id = courses.teacher_id
where courses.n_reviews >= 1 -- omitir si se usa having y viceversa
group by teacher
--having total_reviews >= 1
order by total_reviews desc;

MI respuesta:

SELECT teachers.name AS teacher, SUM(n_reviews) AS total_reviews
FROM courses 
INNER JOIN teachers
ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL
GROUP BY teacher
ORDER BY total_reviews DESC;


Muy dura Silvia.

SELECT * FROM courses;
SELECT * FROM teachers;


SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses ON teachers.id = courses.teacher_id
GROUP BY teachers.id,courses.n_reviews
HAVING courses.n_reviews > 0
ORDER BY total_reviews DESC;

#Creo que era algo asi, no estoy seguro
undefined