Khan Academy 3

Durante estos días hemos tenido que hacer un curso de programación a través de la plataforma de Khan Academy 3. A continuación dejo pantallazos sobre las medallas y el avance:

Esto es lo que he puesto en el último proyecto:

CREATE TABLE stars (id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    birthdate TEXT,
    couple INTEGER,
    number_movies NUMERIC);

INSERT INTO stars
    VALUES (1, "Pablo", "Escobar", "1987-06-19", 1, 15);
INSERT INTO stars 
    VALUES (2, "Joaquín", "Guzman", "1969-12-11", 10, 3);
INSERT INTO stars
    VALUES (3, "Amado", "Carrillo", "1997-11-23", 4, 50);
INSERT INTO stars 
    VALUES (4, "Kun", "Sa", "1980-01-03", 7, 7);
INSERT INTO stars 
    VALUES (5, "Caro", "Quintero", "1942-07-09", 8, 4);
INSERT INTO stars
    VALUES (6, "Dawood", "Ibrahim", "1956-10-02", 50, 9);
INSERT INTO stars
    VALUES (7, "Frank", "Lucas", "1992-04-08", 8, 1);
INSERT INTO stars
    VALUES (8, "Juan", "Ramón", "1997-10-13", 1, 90);

CREATE TABLE movies (id INTEGER PRIMARY KEY, 
    name TEXT,
    release_year NUMERIC,
    cast TEXT);

INSERT INTO movies
    VALUES (1, "El Ryan", 2024, "Pablo");
INSERT INTO movies
    VALUES (2, "corre bro", 2018, "Pablo");
INSERT INTO movies
    VALUES (3, "El cacas", 2015, "Pablo");
INSERT INTO movies
    VALUES (4, "Golpe de Estado", 1998, "Juan");
INSERT INTO movies
    VALUES (5, "dino", 1978, "Juan");
INSERT INTO movies
    VALUES (6, "john dick", 2000, "Juan");
INSERT INTO movies
    VALUES (7, "pussy money", 2015, "Juan");
INSERT INTO movies
    VALUES (8, "El rayas", 2001, "Caro");
INSERT INTO movies
    VALUES (9, "GOT", 2009, "Joaquín");
INSERT INTO movies
    VALUES (10, "vente", 2015, "Joaquín");
INSERT INTO MOVIES
    VALUES (11, "Tu Loli", 2015, "Amado");
INSERT INTO movies
    VALUES (12, "Buen Marinero", 1998, "Amado");
INSERT INTO movies
    VALUES (13, "Papelas", 2001, "Amado");
INSERT INTO movies
    VALUES (14, "Tuma", 2006, "Amado");
INSERT INTO movies
    VALUES (15, "Miak", 1998, "Kun");
INSERT INTO movies
    VALUES (16, "corre forest", 1995, "Dawood");
INSERT INTO movies
    VALUES (17, "mercenata", 2021, "Dawood");
INSERT INTO movies
    VALUES (18, "Simulacion", 2007, "Frank");

SELECT * FROM stars;

SELECT * FROM movies;

/* self join*/
SELECT stars.first_name AS stars_name, couple.first_name AS couple_name
    FROM stars
    JOIN stars couple
    ON stars.id = couple.couple;
    
/* outer join */
SELECT stars.first_name, stars.last_name, movies.name, movies.release_year
    FROM stars
    JOIN movies
    ON stars.first_name = movies.cast
ORDER BY release_year DESC;

/* filtro */
SELECT * FROM movies WHERE release_year = "2007";

/* ordenacion */
SELECT * FROM movies WHERE release_year > 2006 ORDER BY release_year DESC;

/* subconsulta con in*/
SELECT name FROM movies WHERE name IN ("Forrest Gump", "The hunger games", "The Da Vinci Code", "Maleficient");

/* agregacion */
SELECT first_name, SUM(number_movies) AS total_movies FROM stars GROUP BY first_name;

/*AGREGACIÓN con SUM y COUNT*/
SELECT COUNT(*) AS total_movies, COUNT(number_movies), SUM(number_movies) FROM stars;

/* filtro con condicion like */
SELECT * FROM movies WHERE release_year IN ( SELECT release_year FROM movies WHERE release_year LIKE "2007");

/* Resultados restringidos en valores agrupados mediante HAVING */
SELECT first_name, SUM(number_movies) AS total_movies FROM stars
    GROUP BY first_name
    HAVING total_movies > 12;

/* Consulta AND u OR */
SELECT * FROM movies WHERE release_year > 1998 AND name = "Maleficient" OR name = "The hunger games";


/*Cálculo de resultados con CASE*/
SELECT first_name, last_name,
    CASE
        WHEN number_movies > 21 THEN "a lot"
        WHEN number_movies <= 21 THEN "not many"
        ELSE "few"
    END AS  "movies_quantity"
FROM stars
GROUP BY "movies_quantity";


/*Contar cuantos registros están en cada una de las subclasificaciones del CASE anterior */
SELECT COUNT(*),
    CASE
        WHEN number_movies > 10 THEN "a lot"
        WHEN number_movies <= 10 THEN "not many"
        ELSE "few"
    END AS  "movies_quantity"
FROM stars

GROUP BY "movies_quantity";