¡El poder de los datos!

1

¡El poder de los datos!

Introducción a las bases de datos relacionales

2

Buenas prácticas de bases de datos con SQL

3

Tipos de datos en SQL

4

¿Cómo crear una base de datos en SQL?

5

Práctica: Tu primera consulta en bases de datos

6

Historia y Evolución de SQL

7

Práctica: Creación de Tablas en SQL (CREATE TABLE)

Manipulación de Datos

8

Insertando Datos con SQL (INSERT)

9

Consultas y Selecciones en SQL (SELECT)

10

Práctica: SELECT en SQL

11

Actualización de Datos con SQL (UPDATE)

12

Eliminación de Datos con SQL (DELETE)

13

Práctica: CRUD con SQL

Manipulación Avanzada de Datos

14

Instalación de MySQL Server y MySQL Workbench

15

¿Qué es la cláusula WHERE de SQL?

16

Filtrar y Ordenar Datos en SQL (LIKE)

17

Práctica: Filtrar Datos con WHERE en SQL

18

Cláusulas de Comparación Textual en SQL (AND, NULL, IN, NOT)

19

Funciones de Aritmética Básica en SQL (COUNT, SUM, AVG)

20

Funciones de Aritmética Básica en SQL (MIN, MAX)

Agrupación de Datos

21

Agrupación de Datos en SQL: GROUP BY, HAVING y CASE para Análisis Avanzado

22

Práctica: Agrupamiento y Ordenamiento de Datos

23

Tipos de JOIN en SQL

24

¿Cómo funciona INNER JOIN, LEFT JOIN, RIGHT JOIN y FULL JOIN?

25

Práctica: LEFT JOIN en SQL

Transformación de Datos

26

Vistas Materializadas en SQL: Como optimizar tus consultas y reportes.

27

Práctica: Crear Vistas Materializadas en SQL

28

Optimización de Bases de Datos con SQL: CREATE INDEX y TRIGGER

29

Vistas Materializadas y Temporales en SQL

30

Expresiones de Tablas Comunes (CTE) en SQL

Procedimientos Almacenados

31

Procedimientos Almacenados en SQL

32

Procedimientos Almacenados en SQL: Gestión de Variables y Manejo de Excepciones

Administración de Base de Datos

33

Respaldos y Restauración de Bases de Datos

34

Seguridad en Bases de Datos SQL

Análisis de Datos Avanzados

35

Potenciando los Datos en la Nube: Data Science, Big Data, ML e AI

36

SQL para Análisis de Datos: Primeros pasos con Power BI

No tienes acceso a esta clase

¡Continúa aprendiendo! Únete y comienza a potenciar tu carrera

Práctica: LEFT JOIN en SQL

25/36

Aportes 69

Preguntas 0

Ordenar por:

¿Quieres ver más aportes, preguntas y respuestas de la comunidad?

Listo :) ```js SELECT courses.id AS `id`, courses.name AS `name`, teachers.id AS `teacher_id`, teachers.name AS `teacher_name` FROM courses INNER JOIN teachers ON courses.teacher_id = teachers.id ```
La consola tiene un problema y es que si no identas el código exactamente como lo espera, arroja error, así tengas toda la sintaxis bien y todo lo demás esté bien. Ni en MySQL Workbench ni en la terminal de mysql la identación importa. Si importa deberían dar una clase sobre eso.
\------------RESPUESTA--------------- ![](https://static.platzi.com/media/user_upload/Captura%20de%20pantalla%202024-07-05%20a%20la%28s%29%204.28.37p.m.-64359428-0b17-4665-975c-885f1f839485.jpg)
Que increible prueba, la he realizado con INNER Join asi: `SELECT` `courses.id AS id,` `courses.name AS name,` `courses.teacher_id AS teacher_id,` `teachers.name AS teacher_name` `FROM` `courses` `INNER JOIN` `teachers ON courses.teacher_id = teachers.id` `WHERE` `courses.teacher_id IS NOT NULL;`
\--Primero visualizamos las tablas SELECT \* FROM courses SELECT \* FROM teachers \-- hay que tener en cuenta que la que nos va a unir la clave foranea las es teacher\_id de courses y id de teaches-- El LEFT JOIN Seria: SELECT C.id id, C.name name, T.id teacher\_id, T.name teacher\_name FROM courses CLEFT JOIN teachers T ON C.teacher\_id = T.id; \-- Ahora RIGHT JOIN seria: SELECT C.id id, C.name name, T.id teacher\_id, T.name teacher\_name FROM courses CRIGHT JOIN teachers T ON C.teacher\_id = T.id; \-- FULL OUTTER JOIN SELECT C.id id, C.name name, T.id teacher\_id, T.name teacher\_name FROM courses CLEFT JOIN teachers T ON C.teacher\_id = T.id UNION SELECT C.id id, C.name name, T.id teacher\_id, T.name teacher\_name FROM courses CRIGHT JOIN teachers T ON C.teacher\_id = T.id;
```js SELECT C.ID, C.NAME, c.teacher_id, T.NAME AS teacher_name FROM courses AS C INNER JOIN TEACHERS AS T ON C.ID = T.course_id WHERE C.teacher_id IS NOT NULL; ```SELECT C.ID,C.NAME,c.teacher\_id,T.NAME AS teacher\_nameFROM courses AS CINNER JOIN TEACHERS AS T ON C.ID = T.course\_idWHERE C.teacher\_id IS NOT NULL;
![](https://static.platzi.com/media/user_upload/image-e972447f-f7e8-40c4-b7f9-2986cb5d5aed.jpg)
![](https://static.platzi.com/media/user_upload/image-c3bfb13b-4a5f-4786-9172-3ba00ae6d421.jpg)
![](https://static.platzi.com/media/user_upload/image-781c0b88-cd9a-4d09-86e1-28e30507348a.jpg) SELECT \* FROM courses as a1INNER JOIN teachers as b1 ON a1.id = b1.course\_id; SELECT a1.id, a1.name, b1.id, b1.name FROM courses as a1INNER JOIN teachers as b1 ON b1.course\_id= a1.id;
ya vi que hay un problema con la consola, deberian de arreglar eso
Este es mi codigo, aunque no se que este mal: SELECT courses.id AS id, courses.name AS name, courses.teacher\_id AS teacher\_id, teachers.name AS teacher\_name FROM courses INNER JOIN teachers ON courses.teacher\_id = teachers.id WHERE courses.teacher\_id IS NOT NULL;
```txt -- Tu código aquí 👇 SELECT c.id id, c.name name, t.id teacher_id, t.name teacher_name FROM courses c INNER JOIN teachers t ON c.teacher_id = t.id; ```-- Tu código aquí 👇SELECT c.id id, c.name name, t.id teacher\_id, t.name teacher\_name FROM courses cINNER JOIN teachers tON c.teacher\_id = t.id;
```txt SELECT courses.id AS id, courses.name AS name, courses.teacher_id AS teacher_id, teachers.name AS teacher_name FROM courses JOIN teachers ON courses.teacher_id = teachers.id WHERE courses.teacher_id IS NOT NULL; ```SELECT     courses.id AS id,     courses.name AS name,     courses.teacher\_id AS teacher\_id,     teachers.name AS teacher\_nameFROM     coursesJOIN     teachers ON courses.teacher\_id = teachers.idWHERE     courses.teacher\_id IS NOT NULL;
Aquí dejo una opción tomando una como tu left table la tabla cursos. Varios de los comentarios hacen el join desde la perspectiva de la tabla teachers además de utilizar inner join. select c.id, c.name, c.teacher\_id, t.name as teacher\_name from teachers t left join courses c on c.teacher\_id = t.id where c.teacher\_id is not null; Una variedad de respuestas es mas que genial, asi que aqui dejo una mas :)
El ejercicio esta bien, pero con este editor, con cualquier espacio arroja como error el desarrollo del codigo SELECT   courses.ID AS 'id'  ,courses.name AS 'name'  ,teachers.id AS 'teacher\_id'  ,teachers.name AS 'teacher\_name'FROM courses INNER JOIN teachers ON courses.teacher\_id = teachers.id
Se necesita un poco mas de información del nombre exacto de las columnas. Una sugerencia, seria la imagen del diagrama relacional.
```js SELECT c.id, c.name, t.id as teacher_id, t.name as teacher_name FROM courses c inner join teachers t on c.teacher_id = t.id ; ```SELECT c.id, c.name, t.id as teacher\_id, t.name as teacher\_name FROM courses c inner join teachers t on c.teacher\_id = t.id ;
![](https://static.platzi.com/media/user_upload/image-ba044188-f611-4196-8ac9-e84008185c3b.jpg)
Poner los alias con minúscula y sin comillas.
SELECT courses.id, courses.name, teachers.id AS teacher\_id, teachers.name AS teacher\_name FROM courses INNER JOIN teachers ON courses.teacher\_id = teachers.id; Por si le ayuda a alguien!!
Solución: SELECT c.id, c.name, t.id AS teacher\_id, t.name AS teacher\_nameFROM courses AS c INNER JOIN teachers AS t ON c.teacher\_id = t.id
Aqui va la respuesta. Pero antes: 1. Recuerda eliminar las lineas que propone previamente el ejericio. SELECT   courses.id, courses.name, teachers.id AS teacher\_id, teachers.name AS teacher\_name FROM courses INNER JOIN teachers ON   courses.teacher\_id = teachers.id;
logrado! SELECT  courses.id,  courses.name,  teachers.id AS teacher\_id,  teachers.name AS teacher\_nameFROM coursesLEFT JOIN teachers ON courses.teacher\_id = teachers.idWHERE courses.teacher\_id IS NOT NULL;
`select courses.id,courses.name,teachers.id,teachers.name from courses left join teachers on courses.id = techers.course_id;`
Respuesta: `SELECT c.id as id, c.name as name, t.id as teacher_id, t.name as teacher_name FROM courses cleft join teachers t on t.id = c.teacher_idwhere c.teacher_id is not null;`
Respuesta. `SELECT c.id as id, c.name as name, t.id as teacher_id, t.name as teacher_nameFROM courses cleft join teachers t on t.id = c.teacher_idwhere c.teacher_id is not null;`
```txt WITH courses_with_teachers AS ( SELECT * FROM courses WHERE teacher_id IS NOT NULL ) SELECT courses_with_teachers.id as id, courses_with_teachers.name as name, teachers.id as teacher_id, teachers.name as teacher_name FROM courses_with_teachers LEFT JOIN teachers ON courses_with_teachers.teacher_id = teachers.id ```
`SELECT ` `c.id,` ` c.name, ` `t.id as teacher_id, ` `t.name as teacher_name ` `from courses c   ` `inner join ` `teachers t ` `on ` `c.teacher_id=t.id`
![](https://static.platzi.com/media/user_upload/image-d8595f9e-5b25-4ccb-aba7-62935927b392.jpg) ```js -- Tu código aquí 👇 SELECT c.id,c.name,c.teacher_id,t.name teacher_name FROM courses c INNER JOIN teachers t on c.teacher_id= t.id; ```-- Tu código aquí 👇SELECT c.id,c.name,c.teacher\_id,t.name teacher\_name FROM courses c INNER JOIN teachers t on c.teacher\_id= t.id;
Estas tablas no estan normalizada, no se si sea para confundir al estudiante, pero considero que podria ser perjudicial cuando se tiene el desconocimiento de que algo este mal. Ambas tablas tienen foreing keys (tabla courses contiene teacher\_id y tabla teachers contiene course\_id) y y esto deberia ser aclarado en el modelo entidad relacion que parte de las reglas de negocio. Ejemplos. 1. Si un curso puede ser impartido por un solo profesor y un profesor al mismo tiempo puede impartir multiples cursos entonces el modelo ER, especificamente la cardinalidad nos diria que las `foreing keys` solo deberia estar en la tabla cursos. 1. Por otro lado si multiples profesores pueden impartir el mismo curso, por ejemplo una escuela donde varios maestros imparten la misma materia, pero para ese ciclo solo tienen asignada esa unidad de aprendizaje independientemente del grupo. Para este caso la `foreing key` deberia ir solo en la tabla teachers. 1. Por ultimo, si el modelo de negocio permite que multiples profesores impartan multiples cursos, las `foreing key` deberian de ir en una tabla intermedia donde se especifiquen todos los cursos relacionados con los profesores que las imparten. Espero que esta informacion sea de utilidad. 🤓
SELECT courses.id, courses.name, teachers.id AS teacher\_id, teachers.name AS teacher\_nameFROM courses INNER JOIN teachers ON courses.teacher\_id = teachers.id;
```js -- Tu código aquí 👇 SELECT c.id AS id, c.name AS name, t.id AS teacher_id, t.name AS teacher_name FROM courses AS c INNER JOIN teachers AS t ON c.teacher_id = t.id; ```-- Tu código aquí 👇SELECT c.id AS id, c.name AS name, t.id AS teacher\_id, t.name AS teacher\_nameFROM courses AS cINNER JOIN teachers AS tON c.teacher\_id = t.id;
SELECT c.\*, t.name as teacher\_name FROM courses cLEFT JOIN teachers t   ON c.teacher\_id = t.idWHERE teacher\_id IS NOT NULL
![]()![]()![](https://static.platzi.com/media/user_upload/image-c1c7ed00-6819-4e3b-b51e-5a187efe6c9b.jpg)
A mi forma de ver y teniendo en cuenta los datos de las tablas actuales, las búsquedas con INNER JOIN o LEFT JOIN con NOT NULL en el teacher\_id desprenden la misma información, pero en el caso de que tuviéramos filas con el campo teacher\_id que no existiera (por error o cualquier otra causa) en la tabla de profesores y se usara el INNER, se perderían correspondencias cuando el enunciado del ejercicio es: "Selecciona únicamente los cursos que tengan profe asociado en la columna `teacher_id`.". En ese caso no cumpliríamos el requerimiento solicitado por el ejercicio si usamos el INNER JOIN.
el ejercicio dice práctica Left Join y aqui pienso que lo correcto seria usar Inner join porque dice selecciona los cursos que tengan profe asociado el Inner Join devuelve coincidencia en ambas tablas en cambio left join devuelve registros de la tabla izquierda en este caso courses y de la derecha en este caso teachers y Si no hay coincidencia, se rellenan con valores nulos pruebenlo ustedes mismos: Con **inner join:** SELECT courses.id AS `id`, courses.name AS `name`, teachers.id AS `teacher\_id`, teachers.name AS `teacher\_name` FROM courses INNER JOIN teachers ON courses.teacher\_id = teachers.id **con left join:** SELECT courses.id AS `id`, courses.name AS `name`, teachers.id AS `teacher\_id`, teachers.name AS `teacher\_name` FROM courses LEFT JOIN teachers ON courses.teacher\_id = teachers.id aseguren de tener registros de cursos sin teachers asociados ok.
```js select c.id as id, c.name as name, t.id as teacher_id, t.name as teacher_name FROM courses AS c left JOIN teachers AS t ON c.teacher_id = t.id where c.teacher_id is not null; ```select c.id as id,       c.name as name,       t.id as teacher\_id,       t.name as teacher\_name   FROM courses AS cleft JOIN teachers AS t ON  c.teacher\_id = t.idwhere c.teacher\_id is not null;
También está fregada esta práctica
![](https://static.platzi.com/media/user_upload/image-05a75325-0266-400e-9a7a-dd7f64fe34ce.jpg)
Hecho! ![](https://static.platzi.com/media/user_upload/image-7818e02e-282c-425c-a2af-d4fdd88fe3d1.jpg)
![](https://static.platzi.com/media/user_upload/image-cad7c992-a752-4605-baa0-523cec0be126.jpg)
Dos maneras de resolverlo: ```txt -- Primer forma: SELECT c.id, c.name, t.id AS teacher_id, t.name AS teacher_name FROM courses c JOIN teachers t ON c.teacher_id = t.id WHERE c.teacher_id IS NOT NULL; -- Segunda forma SELECT c.id, c.name, t.id AS teacher_id, t.name AS teacher_name FROM courses c INNER JOIN teachers t ON c.teacher_id = t.id; ```
SELECT C.id AS id, C.name AS name, C.teacher\_id AS teacher\_id, T.name AS teacher\_name FROM courses AS C JOIN teachers AS T ON C.teacher\_id = T.id WHERE C.teacher\_id IS NOT NULL;
SELECT CS.id AS id, CS.name AS name, CS.teacher\_id AS teacher\_id, T.name AS teacher\_name FROM courses AS CS JOIN teachers AS T ON CS.teacher\_id = T.id WHERE CS.teacher\_id IS NOT NULL;
![](https://static.platzi.com/media/user_upload/image-4656848f-cc71-47a9-aa16-efecef34949b.jpg)
SOLUCION: ```js SELECT c.id as id, c.name as name, t.id as teacher_id, t.name as teacher_name FROM courses c INNER JOIN teachers t ON c.teacher_id = t.id; ```
Les comparto mi solución SELECT c.id, c.name, c.teacher\_id, t.name AS teacher\_nameFROM courses c INNER JOIN teachers t ON c.teacher\_id = t.id;
SELECT courses.id as id, courses.name as name, teachers.id as teacher\_id, teachers.name as teacher\_name FROM coursesLEFT JOIN teachers ON courses.teacher\_id = teachers.id where teacher\_name is not null
Mi Respuesta: SELECT c.id,c.name,t.id as teacher\_id,t.name teacher\_name  FROM  courses AS cLEFT JOINteachers AS t on C.teacher\_id =  t.idWhere c.teacher\_id is not null
```js SELECT c.id AS id, c.name AS name, teacher_id AS teacher_id, t.name AS teacher_name FROM teachers AS t LEFT JOIN courses AS c ON c.id = t.course_id WHERE c.id IS NOT NULL AND teacher_id IS NOT NULL ```SELECT c.id AS id, c.name AS name, teacher\_id AS teacher\_id, t.name AS teacher\_nameFROM teachers AS t LEFT JOIN courses AS c ON c.id = t.course\_idWHERE c.id IS NOT NULL AND teacher\_id IS NOT NULL
![](https://static.platzi.com/media/user_upload/image-5c55bcdd-e7d1-458c-8e48-8fbda79bdd39.jpg)![](https://static.platzi.com/media/user_upload/image-3d463594-fba1-452f-8e6a-1fc4f3a10b62.jpg)
Les recomiendo analizar las tablas (SELECT en ambas) para ver los nombres de las columnas y no tener problemas en las sintaxis. ![](https://static.platzi.com/media/user_upload/image-a4f9b52e-4964-4a70-83af-b624266258eb.jpg)
![](https://static.platzi.com/media/user_upload/image-275a3bba-40d5-435d-bd52-a47fc4105f4d.jpg)
![](https://static.platzi.com/media/user_upload/image-2f7bc656-0590-4fa2-9fa9-8e3a9c2d76d7.jpg)
No entiendo porque ese código da error, cuando trae lo mismo que la solución que da la plataforma ```js SELECT courses.id as id, courses.name as name, teachers.id as teacher_id , teachers.name as teacher_name FROM courses LEFT JOIN teachers ON courses.teacher_id = teachers.id WHERE teachers.name is not null; ```
Así lo hice yo: ![](https://static.platzi.com/media/user_upload/image-efd01d65-7703-4039-a700-c1800f242d7d.jpg)
```txt SELECT courses.id id, courses.name name, teachers.id teacher_id , teachers.name teacher_name FROM courses LEFT JOIN teachers ON courses.teacher_id = teachers.id WHERE teachers.name is not null ```
![](https://static.platzi.com/media/user_upload/image-84ead49f-fd3e-4529-adc7-4fc58e45614f.jpg)
Es la **guía mejor redactada** entre todos los ejercicios hasta ahora.
<https://github.com/iecgerman/9873-db-sql/blob/master/practica25.sql> ![](https://static.platzi.com/media/user_upload/Captura%20de%20pantalla%202024-07-03%20023425-0d2bea56-7687-4ad0-8a18-28eecbf2c9be.jpg)
![](https://static.platzi.com/media/user_upload/image-2d37ce5f-7f92-4d3d-b820-30651d41fd54.jpg)
```js courses.id AS id, courses.name AS name, courses.teacher_id AS teacher_id, teachers.name AS teacher_name FROM courses LEFT JOIN teachers ON courses.teacher_id = teachers.id WHERE courses.teacher_id IS NOT NULL; ```  courses.id AS id,  courses.name AS name,  courses.teacher\_id AS teacher\_id,  teachers.name AS teacher\_nameFROM coursesLEFT JOIN teachers ON courses.teacher\_id = teachers.idWHERE courses.teacher\_id IS NOT NULL;
![](https://static.platzi.com/media/user_upload/image-8fbfc672-a6a1-49e3-ae17-ac6d02ea4cbc.jpg)
SELECT  courses.id AS id,  courses.name AS name,  courses.teacher\_id AS teacher\_id,  teachers.name AS teacher\_name FROM courses LEFT JOIN teachers ON courses.teacher\_id = teachers.id WHERE courses.teacher\_id IS NOT NULL;
SELECT  c.id AS id, c.name AS name, c.teacher\_id AS teacher\_id,t.name AS teacher\_nameFROM courses cLEFT JOIN teachers t ON c.teacher\_id = t.idWHERE c.teacher\_id IS NOT NULL
![](https://static.platzi.com/media/user_upload/image-d302eb2f-33e0-4fc4-bc90-f08c5898e0cf.jpg) ![](https://static.platzi.com/media/user_upload/image-ca65b981-dea9-4f28-b085-255fa22f029c.jpg)
![](https://static.platzi.com/media/user_upload/Screenshot_20240622_142546-5e1daafc-6315-485f-a674-d834205a93c3.jpg) Aquí esta mi solución
Lo trabajé de esta manera `SELECT  ` `c.id AS id,  ` `c.name AS name,  ` `c.teacher_id AS teacher_id,  ` `t.name AS teacher_name` `FROM courses c` `LEFT JOIN teachers t ON c.teacher_id = t.id` `WHERE c.teacher_id IS NOT NULL`
```js select courses.id, courses.name, courses.teacher_id, teachers.name as teacher_name from courses inner join teachers on teachers.course_id = courses.id where courses.teacher_id is not null ```select courses.id, courses.name, courses.teacher\_id, teachers.name as teacher\_namefrom coursesinner join teachers on teachers.course\_id = courses.idwhere courses.teacher\_id is not null
undefined