-- Jugadores que son kickers
SELECT *
FROM jugador JOIN kicker ON jugador.id_jugador = kicker.id_jugador;
--Nombre y Apellido de jugadores ordenados por equipo
SELECT equipo.nombre_equipo, jugador.apellido_jugador, jugador.nombre_jugador
FROM (jugador JOIN jugador_equipo ON jugador.id_jugador =
jugador_equipo.id_jugador) JOIN equipo
ON jugador_equipo.id_equipo = equipo.id_equipo
ORDER BY equipo.nombre_equipo ASC;
-- Total de jugadores agrupados por equipo
SELECT equipo.nombre_equipo, count(jugador.id_jugador)
FROM (jugador JOIN jugador_equipo ON jugador.id_jugador =
jugador_equipo.id_jugador) JOIN equipo
ON jugador_equipo.id_equipo = equipo.id_equipo
GROUP BY equipo.nombre_equipo
ORDER BY equipo.nombre_equipo;
--Total de Equipos por ciudad
SELECT ciudad.nombre_ciudad, count(equipo.id_equipo)
FROM (equipo JOIN equipo_ciudad ON equipo.id_equipo = equipo_ciudad.id_equipo) JOIN
ciudad
ON ciudad.id_ciudad = equipo_ciudad.id_ciudad
GROUP BY ciudad.nombre_ciudad;
--Total de equipos y jugadores por ciudad
SELECT ciudad.nombre_ciudad, count(distinct(equipo.id_equipo)),
count(jugador_equipo.id_jugador)
FROM ciudad NATURAL JOIN equipo_ciudad NATURAL JOIN equipo NATURAL JOIN
jugador_equipo
GROUP BY ciudad.nombre_ciudad
-- Total de partidos de local y de visitante agrupados por equipo
(SELECT equipo.nombre_equipo, 'Local: ' || COUNT(partido.id_partido)
FROM equipo JOIN partido ON equipo.id_equipo = partido.id_equipo_l
GROUP BY equipo.nombre_equipo)
UNION
(SELECT equipo.nombre_equipo, 'Visitante: ' || COUNT(partido.id_partido)
FROM equipo JOIN partido ON equipo.id_equipo = partido.id_equipo_v
GROUP BY equipo.nombre_equipo)
ORDER BY nombre_equipo
-- �Qu� necesitamos para obtener el total de partidos ganados o perdidos?
SELECT equipo.nombre_equipo, 'Ganados Local: ' || COUNT(partido.id_partido)
FROM equipo JOIN partido ON partido.id_equipo_l = equipo.id_equipo
WHERE marcador_l > marcador_v
GROUP BY equipo.nombre_equipo
UNION
SELECT equipo.nombre_equipo, 'Ganados Visitante: ' || COUNT(partido.id_partido)
FROM equipo JOIN partido ON partido.id_equipo_v = equipo.id_equipo
WHERE marcador_v > marcador_l
GROUP BY equipo.nombre_equipo
ORDER BY nombre_equipo;
-- Nombre y apellidos de los jugadores que portan el n�mero m�s alto en su jersey
ordenados de manera descendente por apellido
SELECT jugador.nombre_jugador,jugador.apellido_jugador
FROM jugador JOIN jugador_equipo ON jugador.id_jugador = jugador_equipo.id_jugador
WHERE numero = (SELECT MAX(numero)
FROM jugador_equipo)
ORDER BY jugador.apellido_jugador DESC;
-- Equipo que ha ganado m�s partidos de local, HAVING nos permite tener funciones
de agregaci�n en la cl�usula "WHERE"
SELECT nombre_equipo
FROM equipo JOIN partido ON partido.id_equipo_l = equipo.id_equipo
WHERE marcador_l > marcador_v
GROUP BY equipo.nombre_equipo
HAVING COUNT(partido.id_partido) = (SELECT MAX([Link])
FROM ( SELECT equipo.nombre_equipo AS Equipo,
COUNT(partido.id_partido) AS Total
FROM equipo JOIN partido ON partido.id_equipo_l
= equipo.id_equipo
WHERE marcador_l > marcador_v
GROUP BY equipo.nombre_equipo) AS T1);
-- Nombre y apellido de los jugadores que portan el menor de los n�meros en su
jersey,son pateadores y juegan han jugado en alg�n estadio con capacidad mayor a
60000
SELECT nombre_jugador,apellido_jugador
FROM jugador JOIN jugador_equipo ON jugador.id_jugador = jugador_equipo.id_jugador
WHERE numero = (SELECT MIN(numero) FROM jugador_equipo)
AND id_equipo IN (SELECT id_equipo
FROM equipo_estadio
WHERE id_estadio IN (SELECT id_estadio
FROM estadio
WHERE capacidad > 60000))
AND jugador.id_jugador IN (SELECT id_jugador
FROM kicker);
-- Corredores(nombre y apellido) que han jugado m�s de 3 juegos agrupados por
equipo, ordenados de manera ascendente por el nombre del equipo
SELECT equipo.nombre_equipo,nombre_jugador,apellido_jugador
FROM (jugador JOIN jugador_equipo ON jugador.id_jugador =
jugador_equipo.id_jugador) JOIN equipo ON
equipo.id_equipo = jugador_equipo.id_equipo
WHERE juegos_jugados > 3 AND jugador.id_jugador IN (SELECT id_jugador FROM runner)
GROUP BY equipo.nombre_equipo,nombre_jugador,apellido_jugador
ORDER BY equipo.nombre_equipo ASC;
-- Equipo que ha jugado m�s partidos de visitante
SELECT equipo.nombre_equipo
FROM equipo JOIN partido ON equipo.id_equipo = partido.id_equipo_v
GROUP BY equipo.nombre_equipo
HAVING (COUNT (id_partido)) = (SELECT MAX(Total)
FROM
(SELECT equipo.nombre_equipo,COUNT(id_partido) AS
Total
FROM partido JOIN equipo ON partido.id_equipo_v =
equipo.id_equipo
GROUP BY equipo.nombre_equipo) AS T1);
-- Total de pateadores por ciudad
SELECT nombre_ciudad, COUNT (Kicker.id_jugador)
FROM Jugador JOIN Kicker ON Jugador.id_jugador = Kicker.id_jugador
JOIN Jugador_Equipo ON Jugador.id_jugador = Jugador_Equipo.id_jugador
JOIN Equipo ON Jugador_Equipo.id_equipo = Equipo.id_equipo
JOIN Equipo_Ciudad ON Equipo.id_equipo = Equipo_Ciudad.id_equipo
JOIN Ciudad ON Equipo_Ciudad.id_ciudad = Ciudad.id_ciudad
GROUP BY nombre_ciudad;
-- Total de receptores agrupados por ciudad cuyo equipo tiene record de mas de
tres perdidos y son de la conferencia nacional
SELECT nombre_ciudad, Count(Receiver.id_jugador)
FROM Jugador JOIN Receiver ON Receiver.id_jugador = Jugador.id_jugador
JOIN Jugador_Equipo ON Jugador_Equipo.id_jugador = Jugador.id_jugador
JOIN Equipo ON Equipo.id_equipo = Jugador_Equipo.id_equipo
JOIN Equipo_Ciudad ON Equipo_Ciudad.id_equipo = Equipo.id_equipo
JOIN Ciudad ON Ciudad.id_ciudad = Equipo_Ciudad.id_ciudad
WHERE Equipo.record_P > 3 and [Link] = 'Nacional'
GROUP BY nombre_ciudad;
-- Total de partidos de local agrupados por conferencia y que se han llevado a
cabo en estadio construidos antes del a�o 2000
SELECT [Link], count(partido.id_partido)
FROM estadio JOIN equipo_estadio ON estadio.id_estadio = equipo_estadio.id_estadio
JOIN equipo ON equipo_estadio.id_equipo = equipo.id_equipo
JOIN partido ON equipo.id_equipo = partido.id_equipo_L
WHERE estadio.anyo_construccion < 2000
GROUP BY [Link];
-- Nombre del entrenador cuyo equipo es el mas ganador de local
SELECT nombre_entrenador,apellido_entrenador
FROM entrenador JOIN equipo_entrenador ON entrenador.id_entrenador =
equipo_entrenador.id_entrenador
JOIN equipo ON equipo_entrenador.id_equipo = equipo.id_equipo
WHERE nombre_equipo IN (SELECT nombre_equipo
FROM equipo JOIN partido ON equipo.id_equipo=partido.id_equipo_L
WHERE marcador_L>marcador_V
GROUP BY nombre_equipo
HAVING count(id_partido)=(SELECT MAX(numpartido)
FROM (SELECT nombre_equipo AS nombreEquipo, count(id_partido)
AS numpartido
FROM equipo JOIN partido ON
equipo.id_equipo=partido.id_equipo_L
WHERE marcador_L>marcador_V
GROUP BY nombre_equipo) as t1));
-- Estadios donde se han llevado mas partidos
SELECT estadio.nombre_estadio
FROM estadio NATURAL JOIN equipo_estadio NATURAL JOIN equipo JOIN partido ON
equipo.id_equipo = partido.id_equipo_l
GROUP BY nombre_estadio
HAVING COUNT(partido.id_partido) = (SELECT MAX([Link])
FROM (SELECT estadio.nombre_estadio,
count(partido.id_partido) as cont
FROM estadio NATURAL JOIN equipo_estadio NATURAL JOIN
equipo JOIN partido ON equipo.id_equipo = partido.id_equipo_l
GROUP BY estadio.nombre_estadio) AS t1)
/*
Comandos especiales
*/
-- LIKE
SELECT *
FROM jugador
WHERE nombre_jugador LIKE 'Victor';
-- La igualdad es estricta
SELECT *
FROM jugador
WHERE nombre_jugador LIKE ' Victor ';
-- Como prefijo
SELECT *
FROM jugador
WHERE nombre_jugador LIKE ' A%';
-- Lo mismo, la igualdad es estricta
SELECT *
FROM jugador
WHERE nombre_jugador LIKE ' A%';
-- Como sufijo
SELECT *
FROM jugador
WHERE nombre_jugador LIKE ' %A';
-- Lo mismo, la igualdad es estricta �qu� pasa?
SELECT *
FROM jugador
WHERE nombre_jugador LIKE ' %A ';
-- No es lo mismo qu� ...
SELECT *
FROM jugador
WHERE nombre_jugador LIKE ' %a ';
-- Fecha actual
SELECT CURRENT_DATE;
-- Hora
SELECT CURRENT_TIME;
-- Funciones de fechas
SELECT DATE_PART('year',fecha_nacimiento::date) FROM jugador
-- CASE WHEN
SELECT CASE ((SELECT DATE_PART('year',CURRENT_DATE)) - (SELECT
DATE_PART('year',fecha_nacimiento::date))) WHEN 33 THEN 'MENOR' ELSE 'ADULTO' END
FROM jugador;
-- UPPER
SELECT UPPER(nombre_jugador)
FROM jugador;
-- LOWER
SELECT LOWER(nombre_jugador)
FROM jugador;
-- CONCAT
SELECT nombre_jugador || ' ' || apellido_jugador
FROM jugador;
-----------------------------------------------------------------------------------
-----------------------------------------
--BIEN El m�ximo record de juegos perdidos de un equipo que pertenece a alguna
ciudad con ingreso promedio mayor a 47
SELECT MAX(equipo.record_p)
FROM equipo NATURAL JOIN equipo_ciudad NATURAL JOIN ciudad
WHERE ciudad.ingreso_promedio > 47;
--BIEN Nombre, apellido, universidad y fecha de nacimiento del runner que menos
carries tiene
SELECT jugador.nombre_jugador, jugador.apellido_jugador, [Link],
jugador.fecha_nacimiento
FROM runner NATURAL JOIN jugador
WHERE carries = (SELECT MIN([Link])
FROM (SELECT carries FROM runner) AS T1)
--BIEN Nombre de las distintas universidades ordenadas de manera ascedente a las
que pertenecen los kickers que registran
--m�s fga que el promedio de los kicker y su a�o de nacimiento es menor a 1970
SELECT [Link]
FROM kicker NATURAL JOIN jugador
WHERE (fga > (SELECT AVG(fga) FROM kicker)) AND fecha_nacimiento < '1970-01-01'
GROUP BY [Link]
ORDER BY [Link] ASC
--BIEN El n�mero kickers por cada universidad cuyo porcentaje de efectividad
(fgm/fga)*100 es mayor a 70.
SELECT [Link], count(jugador.id_jugador)
FROM kicker NATURAL JOIN jugador
WHERE (fgm/fga)*100 > 75
GROUP BY [Link];
--BIEN El promedio de habitantes de las ciudades con a lo m�s un equipo.
SELECT AVG([Link])
FROM (SELECT COUNT(id_equipo) as num_eq, [Link] as hab
FROM ciudad NATURAL JOIN equipo_ciudad NATURAL JOIN equipo
GROUP BY id_ciudad) AS T1
WHERE T1.num_eq<=1
--BIEN El n�mero de yardas que suman todos los jugadores Runner que nacieron
despu�s de 1970.
SELECT SUM([Link])
FROM runner NATURAL JOIN jugador
WHERE fecha_nacimiento >= '1970-01-01';
--Apellido y nombre de todos los entrenadores con el n�mero de equipos que ha
dirigido, ordenados por apellido de manera descendente
SELECT entrenador.apellido_entrenador, entrenador.nombre_entrenador,
count(equipo_entrenador.id_equipo)
FROM entrenador NATURAL JOIN equipo_entrenador
GROUP BY entrenador.id_entrenador
ORDER BY entrenador.apellido_entrenador DESC
SELECT * FROM equipo_entrenador
ORDER BY id_entrenador
-----------------------------------------------------------------------------------
-----------------------------------------
--BIEN Nombre de los nuevos proyectos de estadio ordenados de manera ascendente de
los equipos que juegan en estadios cuya capacidad es mayor
--que el promedio de las capaciudades de los estadios eliminar el valor "none"
SELECT DISTINCT (proyecto_nuevo)
FROM estadio NATURAL JOIN equipo_estadio
WHERE [Link] > (SELECT AVG(capacidad) FROM estadio) AND proyecto_nuevo !
= 'none'
ORDER BY proyecto_nuevo ASC;
--El n�mero de partidos en los que gan� un equipo visitante con record de juegos
ganados mayor a 9
SELECT count(id_partido)
FROM equipo JOIN partido ON equipo.id_equipo = partido.id_equipo_v
WHERE (record_g > 9) AND (marcador_V>marcador_L);
SELECT count(id_partido)
FROM equipo JOIN partido ON equipo.id_equipo = partido.id_equipo_v
WHERE (record_g > 9) AND (marcador_v>marcador_l)
--GROUP BY id_equipo
--BIEN Nombre y apellido de los Runners que han jugado en dos equipos diferentes y
cuyo a�o de adquisici�n sea a partir de 1990
SELECT nombre_jugador, apellido_jugador
FROM runner NATURAL JOIN jugador NATURAL JOIN jugador_equipo
WHERE anyo_adquirido > 1990
GROUP BY jugador.id_jugador
HAVING COUNT(jugador_equipo.id_jugador) = 2;
--BIEN Identificador y nombre de los equipos que tienen m�s de 5 runners
registrados ordenados de manera descendente por el nombre del equipo
SELECT id_equipo, nombre_equipo
FROM equipo NATURAL JOIN jugador_equipo NATURAL JOIN jugador NATURAL JOIN runner
GROUP BY id_equipo
HAVING COUNT(id_jugador) >5
ORDER BY nombre_equipo DESC;
--Apellido del jugador que est� ligado a m�s equipos
SELECT apellido_jugador
FROM jugador_equipo NATURAL JOIN jugador
GROUP BY jugador.id_jugador
HAVING COUNT(id_equipo) = (SELECT MAX([Link])
FROM (SELECT COUNT(id_equipo) as cont
FROM jugador_equipo
GROUP BY id_jugador) AS t1);
--BIEN El n�mero jugadores Kicker por cada universidad cuyo porcentaje de
efectividad (fgm/fga)*100 es mayor a 75 .
EXPLAIN SELECT [Link], count(kicker.id_jugador)
FROM kicker NATURAL JOIN jugador
WHERE (fgm/fga)*100 > 75
GROUP BY [Link]
--Total de partidos agrupados por temporada en los que gan� el equipo visitante y
se realizaron despu�s de la semana 5 de cada temporada, ordenados de manera
ascendente por temporada
SELECT temporada, count(id_partido)
FROM partido
WHERE (semana > 5) AND (marcador_v > marcador_l)
GROUP BY temporada
ORDER BY temporada ASC;
-----------------------------------------------------------------------------------
-----------------------------------------------