No tienes acceso a esta clase

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

Curso Pr谩ctico de SQL

Curso Pr谩ctico de SQL

Israel V谩zquez Morales

Israel V谩zquez Morales

Seleccionar de un set de opciones

12/29
Recursos

Aportes 448

Preguntas 21

Ordenar por:

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

o inicia sesi贸n.

Y dec铆an que nunca iba a usar teor铆a de conjuntos!

SELECT *
FROM platzi.alumnos
WHERE id NOT IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id = 30
)
SELECT * 
    FROM platzi.alumnos
    OFFSET ( SELECT COUNT(*)/2 FROM platzi.alumnos );

Mi soluci贸n usando MySQL

SELECT * FROM platzi.alumnos
WHERE (
	id > (SELECT COUNT(id)/2 FROM platzi.alumnos)
);

Me pareci贸 tan sencillo colocar un diferente (<>) en vez de un igual (=), aunque creo que exsiste una palabra reservada NOT que cumplir铆a el cometido, yo lo hice as铆:

SELECT ROW_NUMBER() OVER() AS row_id, *
FROM platzi.alumnos
WHERE id NOT IN (
	SELECT id
    FROM platzi.alumnos
	WHERE tutor_id = 30
);

Tambi茅n pudo ser:
SELECT *
FROM platzi.alumnos
WHERE tutor_id = 30;

Otra forma de hacerlo es decir a lo que son diferentes de tutor 30
es decir tutor_id != 30

SELECT * 
FROM platzi.alumnos
WHERE id IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id != 30
)
<SELECT * 
FROM (
	  SELECT ROW_NUMBER() OVER() AS row_id, *
	  FROM platzi.alumnos
	  ORDER BY row_id
	 ) AS alumnos_with_row_num
WHERE row_id > 
(SELECT count(*)/2
FROM platzi.alumnos)> 

SELECT *
FROM platzi.alumnos
OFFSET 500;

estas son las tres formas que encontre para darle soluci贸n al problema del final de la clase:

SELECT *
FROM platzi.alumnos
WHERE id IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id <> 30
);

SELECT *
FROM platzi.alumnos
WHERE id NOT IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id = 30
);

SELECT *
FROM platzi.alumnos
WHERE tutor_id <> 30;

Dos formas

SELECT *
FROM (
	SELECT ROW_NUMBER() OVER() AS row_id, *
	FROM platzi.alumnos
) AS alumnos_with_row_num
WHERE row_id NOT IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id = 30
);

Segunda forma

SELECT * FROM platzi.alumnos
except
select * from platzi.alumnos where tutor_id=30;

Comparto mi soluci贸n al reto:

SELECT *
FROM platzi.alumnos
WHERE tutor_id <> 30;
select * from platzi.alumnos 
where id in(
select id
	from platzi.alumnos 
	where tutor_id != 30
);
SELECT *
FROM platzi.alumnos
EXCEPT 
	(SELECT *
		FROM platzi.alumnos
			WHERE id IN (
				SELECT id
				FROM platzi.alumnos
				WHERE tutor_id=30)
);

Mi soluci贸n, fue lo primero que pense cuando aun explicaba la clase.

SELECT *
FROM platzi.alumnos 
WHERE id IN ( 
	SELECT id 
	FROM platzi.alumnos
	WHERE tutor_id != 30
);
SELECT *
FROM PLATZI.ALUMNOS
WHERE ID IN
		(SELECT ID
			FROM PLATZI.ALUMNOS
			WHERE TUTOR_ID != 30);

TRAE LA SEGUNDA MITAD
SELECT *
FROM platzi.alumnos
OFFSET ( SELECT COUNT(*)/2 FROM platzi.alumnos );

Este es mi soluci贸n al reto:
select *
from (
SELECT count(*) over() as total_filas, *
FROM platzi.alumnos)
as consulta_externa
where id >= (total_filas / 2 )

Aunque entend铆 r谩pido c贸mo resolverlo, tuve problemas con el orden o la forma de colocar el count(*) junto con el * porque estaba poniendo de primero el asterisco鈥 al cambiarlo al final funcion贸.

Esta fue mi solucion para el reto

--- Traer la 2da mitad de la tabla Alumnos

SELECT *
FROM alumnos
OFFSET (
	SELECT COUNT(id)/2
	FROM alumnos);```

select *
from platzi.alumnos
where tutor_id != 30

SELECT *
FROM platzi.alumnos
WHERE id IN (
	SELECT id
	FROM platzi.alumnos
	WHERE NOT tutor_id = 30 AND NOT id IN (1,5,10,12,15,20)
);
SELECT *
FROM platzi.alumnos
WHERE id >= 500
;

SELECT *
FROM platzi.alumnos
LIMIT 500 OFFSET 499
;

Creo que era mas f谩cil hacer un
top 50 percent con un order by platzi . alumnos . id asc

Consulta ordenada sobre tutor_id

USE platzi
GO

SELECT *
FROM platzi.alumnos
WHERE id not IN(
	SELECT id
	FROM platzi.alumnos
	WHERE
		tutor_id = 30
	)
ORDER BY tutor_id ASC;
SELECT *
FROM platzi.alumnos
WHERE id NOT IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id = 30
		AND carrera_id= 31
); 
SELECT *
FROM platzi.alumnos 
	where tutor_id <> 30
;

SELECT *
FROM platzi.alumnos
WHERE id IN (SELECT id
FROM platzi.alumnos
WHERE tutor_id <>30);

Mi aporte al segundo desaf铆o, que son los select inversos a los mostrados:

SELECT *
FROM platzi.alumnos
WHERE id IN(
 SELECT id
 from platzi.alumnos
  WHERE tutor_id <>30);
 
SELECT
*
from 
(select ROW_NUMBER() over() AS row_id, * 
from platzi.alumnos)
as alumnos_with_row_num
where row_id  not in (1,5,10,12,15,20);

select *
from platzi.alumnos
where id >= (
select count(*)/2 as cuenta
from platzi.alumnos

);

select *
from platzi.alumnos
where id in (select id
from platzi.alumnos
where tutor_id !=30);

Soluci贸n al ejercicio anterior obtener la segunda mitad de registros de la tabla alumnos

select *
from platzi.alumnos
offset (select count(id)/2
from platzi.alumnos)
select \* from platzi.alumnos where id not in ( select id from platzi.alumnos where tutor\_id=20 );
select \* from platzi.alumnos where id > ( select count(\*)/2 as a from platzi.alumnos );

Minuto 7:58 - Easter egg : el numero de rows afectadas es igual al numero tutor_id requerido =)

Reto:

SELECT *
FROM platzi.alumnos
OFFSET (SELECT COUNT(*) FROM platzi.alumnos) / 2

Mi solucion al reto:

-- Este es el primer reto, para traer los datos de un set
SELECT ROW_NUMBER() OVER(), *
FROM platzi.alumnos
WHERE id IN (1,5,9,12);

-- Este es el segundo reto, para traer los datos de un set
SELECT *
FROM(
	SELECT ROW_NUMBER() OVER() AS row_id, *
	FROM platzi.alumnos
	WHERE id IN (1,5,9,12)
) AS alumnos_with_id;


-- Este es el tercer reto, para no traer los datos de un set
SELECT *
FROM(
	SELECT ROW_NUMBER() OVER() AS row_id, *
	FROM platzi.alumnos
	WHERE id NOT IN (1,5,9,12)
) AS alumnos_with_id;

hola.
SELECT *
FROM platzi.alumnos
ORDER BY id
OFFSET (SELECT COUNT(*) FROM platzi.alumnos) / 2;

SELECT * --1
FROM platzi.alumnos
WHERE id NOT IN (
    SELECT id 
    FROM platzi.alumnos
    WHERE tutor_id = 30);


SELECT * --2
FROM platzi.alumnos
WHERE id IN (
    SELECT id 
    FROM platzi.alumnos
    WHERE tutor_id <> 30);
select * from platzi.alumnos
where id not in (
	select id
	from platzi.alumnos
	where tutor_id = 30
);

Hola, se me ocurri贸 un peque帽o cambio, (el = lo convert铆 en !=), que opinan?

SELECT * 
FROM platzi.alumnos
WHERE id IN (
	SELECT id 
	FROM platzi.alumnos
	WHERE tutor_id != 30
);

RETO: Presentar la segunda mitad de la tabla.

WITH counted AS (
    SELECT COUNT(*) AS total_rows
    FROM platzi.alumnos
)
SELECT *
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY colegiatura) AS row_id, *
    FROM platzi.alumnos
    CROSS JOIN counted
) AS numbered_alumnos
WHERE row_id > total_rows / 2
ORDER BY row_id; 

En mi caso lo hice as铆.

SELECT *
FROM alumnos 
WHERE id  in(
	SELECT id from alumnos
    WHERE tutor_id <> 30
)
select *
from platzi.alumnos a
offset 500

Mi forma:

select *from 
alumnos
where id >= (
SELECT round((count(*)/2),0) As Cuenta
FROM alumnos
)

Para la solucion al reto hay dos formas muy sencillas. La negacion y que sea diferente de, por ejemplo, asi:
.
Con la clausula NOT

SELECT *
FROM platzi.alumnos
WHERE id NOT IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id = 30
);

Y con el signo diferente, quedaria asi:

SELECT *
FROM platzi.alumnos
WHERE id IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id != 30
);

Y listo, nos muestra la lista que no hace parte de la primera consulta.

SELECT *
FROM platzi.alumnos
WHERE id IN(
SELECT ID
FROM platzi.alumnos
Where tutor_id <> 30
);

SELECT * 
FROM platzi.alumnos
WHERE tutor_id <> 30 and carrera_id <> 31
;

Me gusta simplificar:

SELECT *
FROM platzi.alumnos
WHERE tutor_id <> 30;

comparto las dos soluciones que se me ocurrieron

<select distinct tutor_id, id from platzi.alumnos where id in (
select id from platzi.alumnos where tutor_id not in (1)
)

select tutor_id,id from platzi.alumnos where tutor_id not in (1)> 

Al principio pens茅 que se me dificultar铆an mucho m谩s estos ejercicios, sin embargo revisando la documentaci贸n y jugando con el codigo se puede llegar a los resultados

<SELECT * 
FROM platzi.alumnos
WHERE id IN(
	SELECT id
	FROM platzi.alumnos
	WHERE  tutor_id NOT IN (30)
		OR carrera_id NOT IN (31)		
);> 
select * from (
	select row_number() over() as row_id, * from alumnos
) as alumnos_with_row_num
where row_id <= (select  count(*)/2 from alumnos )

RETO 2:
SELECCIONAR LA SEGUNDA MITAD DE LA TABLA ALUMNOS.

SELECT *
FROM platzi.alumnos
OFFSET (
SELECT COUNT(DISTINCT(id))/2
FROM platzi.alumnos
)

SELECT * FROM platzi.alumnos
WHERE id > 
(
	SELECT COUNT(*) FROM platzi.alumnos)/2 ;

Esta fue mi soluci贸n

<SELECT*
FROM platzi.alumnos
ORDER BY id DESC
LIMIT 500> 

SQL SERVER

Ac谩 adem谩s de resolver el ejercicio, coloqu茅 una comprobaci贸n con un conteo de los registros.

SELECT	*
FROM	Platzi.Alumnos
WHERE Id	IN	(	SELECT Id FROM Platzi.Alumnos WHERE TutorId = 30	)

SELECT	*
FROM	Platzi.Alumnos
WHERE Id	NOT	IN	(	SELECT Id FROM Platzi.Alumnos WHERE TutorId = 30	)
	
DECLARE @TotalRows INT = (SELECT COUNT(Id) FROM Platzi.Alumnos)
DECLARE @TotalRowsWithTutorId30 INT = (SELECT COUNT(Id) FROM Platzi.Alumnos WHERE Id	IN	(	SELECT Id FROM Platzi.Alumnos WHERE TutorId = 30	))
DECLARE @TotalRowsWithoutTutorId30 INT = (SELECT COUNT(Id) FROM Platzi.Alumnos WHERE Id	NOT	IN	(	SELECT Id FROM Platzi.Alumnos WHERE TutorId = 30	))

SELECT @TotalRows TotalAlumnos, @TotalRowsWithTutorId30, @TotalRowsWithoutTutorId30 

Mi soluci贸n:

SELECT *
FROM PLATZI.ALUMNOS
WHERE ID NOT IN
		(SELECT ID
			FROM PLATZI.ALUMNOS
			WHERE TUTOR_ID = 30 );

Yo la hice de esta manera:

SELECT * FROM alumnos 
	WHERE NOT(id IN (
		SELECT id FROM alumnos
			WHERE tutor_id = 30)) 

12. Seleccionar de un set de opciones

SELECT *
FROM platzi.alumnos
WHERE id IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id = 30
		AND carrera_id = 31
);

-- RETO 

SELECT *
FROM platzi.alumnos
WHERE id NOT IN (
  SELECT id
  FROM platzi.alumnos
  WHERE tutor_id = 30
);

/segunda mitad de la tabla sql server/
select *
from (
select *, NTILE(2) OVER (ORDER BY id )
AS GRUPO
FROM dbo.alumnos2
) AS SUBSCONSULTA
WHERE GRUPO = 2;

Para obtener como resultado los elementos que no se encuentren en el set solo es necesario colocar NOT delante de IN

SELECT *
FROM platzi.alumnos
--Los id's que se encuentren en una lista
WHERE id NOT IN (
		SELECT id
		FROM platzi.alumnos
		WHERE tutor_id=30 );

鉁 Comparto mi soluci贸n

SELECT *
FROM platzi.alumnos
WHERE id NOT IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id IN(15,22,8,20)
);

Hice este y no funcion贸. Me sent铆 un completo imb茅cil.

SELECT * 
FROM platzi.alumnos
WHERE tutor_id NOT IN 30;

Luego ca铆 en cuenta de que le falt贸 algo.

SELECT * 
FROM platzi.alumnos
WHERE tutor_id NOT IN (30);

Y me segu铆 sitiendo un completo imb茅cil. JAJAJAJAJA

Fue realmente lo primero qeu se me vino a la cabeza:

SELECT *
FROM platzi.alumnos AS total_tabla
INNER JOIN (
	SELECT *
	FROM platzi.alumnos 
	WHERE id <= 500
) AS mitad_tabla
ON total_tabla.id = mitad_tabla.id;
---Pr谩ct 3 CURSO PR脕CTICO SQL PLATZI
-- Indicaci贸n- traer todas las rows que no se encuentren dentro de una subquery

SELECT * FROM platzi.alumnos
WHERE id NOT IN ( 
	SELECT id FROM platzi.alumnos 
	WHERE colegiatura > 4000
);

Respuesta al desaf铆o:

SELECT * FROM platzi.alumnos AS grupoA
INNER JOIN (
	SELECT id
	FROM platzi.alumnos	
	WHERE tutor_id = 30) AS grupoB
ON grupoA.id = grupoB.id;

鈥1
SELECT *
FROM platzi.alumnos
WHERE id NOT IN(
SELECT id
FROM platzi.alumnos
WHERE tutor_id = 30
);

鈥2
SELECT *
FROM platzi.alumnos
WHERE id IN(
SELECT id
FROM platzi.alumnos
WHERE tutor_id != 30
);


SELECT * 
FROM PLATZI.ALUMNOS 
WHERE ID IN (
select id 
	from platzi.alumnos 
	where tutor_id != 30 
);

Esta respuesta estaba regaladisima:

SELECT *
FROM platzi.alumnos
WHERE id IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id <> 30
	AND carrera_id <> 31
)

Que opinan 馃榿

comparto

SELECT *
FROM (
    SELECT DISTINCT ON (tutor_id) *
    FROM   platzi.alumnos
    WHERE  tutor_id != 30
    )AS resultado;

Mi soluci贸n 馃榿

SELECT *
FROM platzi.alumnos
WHERE platzi.alumnos.id > ((SELECT COUNT(*) FROM platzi.alumnos)/2);

SELECT*
FROM PLATZI.ALUMNOS
FETCH FIRST (
SELECT COUNT(*)/2 AS MITAD_CONTEO
FROM PLATZI.ALUMNOS) ROWS ONLY;

SELECT *
FROM platzi.alumnos
WHERE id BETWEEN 500 AND 1000

SELECT *
FROM platzi.alumnos
WHERE id >= 500

SELECT *
FROM platzi.alumnos
WHERE id >0
AND id <1001

facilito XD

select * from platzi.alumnos
where id not in (select id 
			 from platzi.alumnos
			where tutor_id = 30)

asi lo hice:

select * from platzi.alumnos
limit (select count (*)/2 from platzi.alumnos)

SELECT *
FROM platzi.alumnos
WHERE id NOT IN (1,5,6,10,20,30,31);

El resultado del reto:

SELECT *
FROM platzi.alumnos
WHERE id IN(
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id <>30);

mi aporte usando subquerys

SELECT *
FROM platzi.alumnos
WHERE id IN (SELECT id FROM platzi.alumnos WHERE NOT tutor_id = 30);

joins

SELECT *
FROM platzi.alumnos AS a
INNER JOIN (SELECT id FROM platzi.alumnos WHERE NOT tutor_id = 30) AS b
ON a.id = b.id

Buenas noches!
La forma que hice fue hacer por aparte un count para saber cuantas registros habian.
y luego hice esto :
SELECT * FROM platzi.alumnos
limit 500 offset 500;

Comparto mi soluci贸n al ejercicio 鈥淨uedarse con la segunda mitad de los registros鈥:

USE platzi;

SELECT * FROM(SELECT(SELECT COUNT(*) FROM alumnos AS a2
        WHERE a1.id >= a2.id ) AS row_num,  
        (SELECT COUNT(*)/2 FROM alumnos) AS mitad_registros, 
        a1.*
FROM alumnos AS a1) AS a3
WHERE a3.row_num >= a3.mitad_registros;

Select * FROM (
SELECT ROW_NUMBER () OVER() AS row_id, *
FROM platzi.alumnos
)AS alumnos_with_row_nums
WHERE row_id > (Select count(id) from platzi.alumnos)/2
;

select * from alumnos where not tutor_id=30 order by tutor_id desc;

Mi aporte al reto:

--- filtrar y traer los datos distintos al registros con una caracteristica

SELECT id
FROM curso.alumnos
WHERE id != 3;

--- filtrar y traer los datos distintos a los registros con 2 o mas caracteristicas

SELECT *
FROM curso.alumnos
Where ID NOT IN (
	SELECT id
	FROM curso.alumnos
	Where tutor_id =30
)

--- otra opcion sin subindex
SELECT id, tutor_id
FROM curso.alumnos
WHERE tutor_id not in (1,4,9);

SELECT *
FROM (
SELECT ROW_NUMBER() OVER() row_id,*
FROM platzi.alumnos
) AS alumnos_with_row_num
WHERE row_id NOT IN (1,5,10,12,15,20)
AND tutor_id NOT IN (30);

-- reto: lo mismo que hiciste en clase pero lo contrario
select *
from (
	select row_number() over() as row_id, *
	from platzi.alumnos
) as alumnos_with_row_id
where row_id not in (1, 5, 10, 12, 15, 20);

select *
from platzi.alumnos
where id not in (
	select id
	from platzi.alumnos
	where tutor_id = 30
		and carrera_id = 31
)

select *
from platzi.alumnos
where id not in (22);
SELECT *
FROM platzi.alumnos
WHERE id NOT IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id = 30
	AND carrera_id = 31
);

Consulta que trae los datos restantes, des los especificados

SELECT *
	FROM (
		SELECT ROW_NUMBER() OVER() AS row_id, *
		FROM platzi.alumnos
	) AS alumnos_with_row_num
		WHERE row_id NOT IN (1,5,10,15,20);

trae la mitad de registros

SELECT nombre FROM usuarios 
	OFFSET (
		SELECT COUNT(*)/2 FROM usuarios
	) LIMIT (
		SELECT COUNT(*)/2 FROM usuarios
);

Soluci贸n con NOT IN

SELECT *
FROM platzi.alumnos	
WHERE id NOT IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id = 30
);

Soluci贸n con <>

SELECT *
FROM platzi.alumnos	
WHERE id IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id <> 30
);

Soluci贸n con diferente !

SELECT *
FROM platzi.alumnos	
WHERE id IN (
	SELECT id
	FROM platzi.alumnos
	WHERE tutor_id != 30
);

Mis respuestas del reto:

SELECT * 
FROM (
	SELECT *, ROW_NUMBER() OVER(ORDER BY id) AS rn, COUNT(*) OVER() AS total_rows
    FROM alumnos
) AS subquery
WHERE rn > total_rows / 2;
SELECT * 
FROM platzi.alumnos
WHERE id IN (
	SELECT id 
	FROM platzi.alumnos 
	WHERE NOT tutor_id = 30
);```

NO LO S脡 RICK SIENTO QUE ESTOY DESAPRENDIENDO, NO ES MEJOR APRENDER LA FORMA M脕S EFICAZ EN HACER LAS COSNULTAS?

Realice el ejercicio de la tarea de una forma creo un poco m谩s revuelta jaja

SELECT *
FROM platzi.alumnos
WHERE id >= ((
	SELECT COUNT (colegiatura) 
	FROM platzi.alumnos
	)/2);