queries-3-1-ch30.sql

-- DB PIXAR

DROP SCHEMA IF EXISTS Pixar;
CREATE SCHEMA Pixar;
USE Pixar;

CREATE TABLE Movies (
  id INTEGER auto_increment PRIMARY KEY NOT NULL,
  title VARCHAR(30) NOT NULL,
  director VARCHAR(30) NULL,
  year INT NOT NULL,
  length_mBoxOfficeMoviesinutes INT NOT NULL
);

CREATE TABLE BoxOffice (
  movie_id INTEGER UNIQUE,
  FOREIGN KEY (movie_id) REFERENCES Movies (id),
  rating DECIMAL(2,1) NOT NULL,
  domestic_sales INT NOT NULL,
  international_sales INT NOT NULL
);

INSERT INTO Movies(title, director, year, length_minutes)
  VALUES ('Toy Story', 'John Lasseter', 1995, 81),
         ('Vida de inseto', 'Andrew Staton', 1998, 95),
         ('ratatui', 'Brad Bird', 2010, 115),
         ('UP', 'Pete Docter', 2009, 101),
         ('Carros', 'John Lasseter', 2006, 117),
         ('Toy Story 2', 'John Lasseter', 1999, 93),
         ('Valente', 'Brenda Chapman', 2012, 98),
         ('Luca', 'Andrew Staton', 2021, 101),
         ('Frozen II', 'Brad Bird', 2016, 75);


INSERT INTO BoxOffice(movie_id, rating, domestic_sales, international_sales)
  VALUES (1, 8.3, 190000000, 170000000),
         (2, 7.2, 160000000, 200600000),
         (3, 7.9, 245000000, 239000000),
         (4, 6.1, 330000000, 540000000),
         (5, 7.8, 140000000, 310000000),
         (6, 5.8, 540000000, 600000000),
         (7, 7.5, 250000000, 190000000);
        

-- AULA

# SELECT director, json_arrayagg(title) AS filmes, count(*) AS quantidade FROM Pixar.Movies GROUP BY director;
# SELECT * FROM Pixar.Movies;
# Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pixar.Movies.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
# Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pixar.Movies.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

-- Tirar a média de duração de cada filme por diretor
# SELECT director, SUM(length_minutes) FROM Pixar.Movies GROUP BY director

# Agrupar clientes por loja
# SELECT store_id FROM sakila.customer GROUP BY store_id

# Contar quantos clientes estão em cada loja
# SELECT store_id, COUNT(*) FROM sakila.customer GROUP BY store_id

# Juntar os emails dos clientes de cada loja em arrays
# SELECT store_id, COUNT(*), json_arrayagg(email) FROM sakila.customer GROUP BY store_id

# SELECT rating, AVG(rental_rate)
# FROM sakila.film
# GROUP BY rating;

# SELECT rating, MIN(rental_rate)
# FROM sakila.film
# GROUP BY rating;

# SELECT rating, MAX(rental_rate)
# FROM sakila.film
# GROUP BY rating;

 # SELECT rating, SUM(rental_rate)
 # FROM sakila.film
 # GROUP by rating;

#SELECT SUBSTRING('Oi eu sou uma string', 5, 4);

# HAVING VS WHERE
# where => antes dos dados serem agrupados
# having => depois dos dados serem agrupados

# Ver apenas os endereços atualizados após às 22:34:00
# SELECT * FROM sakila.address WHERE TIME(last_update) > '22:34:00'

# Quantos endereços temos por distrito
# SELECT district, COUNT(*) as quantidade FROM sakila.address GROUP BY district ORDER BY quantidade DESC	

# Quantos endereços temos por distrito, contabilizando apenas endereços atualizados após 22:34:00
# SELECT district, COUNT(*) as quantidade 
# 	FROM sakila.address 
#   WHERE TIME(last_update) > '22:34:00'
#   GROUP BY district 
#   ORDER BY quantidade DESC	
    
# Quantos endereços temos por distrito, mostrando apenas distritos com mais de 5 endereços cadastrados
# SELECT district, COUNT(*) as quantidade
#	FROM sakila.address
#    WHERE TIME(last_update) > '22:34:00'
#    GROUP BY district
#    HAVING quantidade >= 2
#.   ORDER BY quantidade DESC

# Mostrar os filmes com as labels “Caro” ou “Barato” de acordo com o preço    
# SELECT title, rental_rate, IF(rental_rate <= 0.99, 'Tá barato', 'Na volta a gente ve') FROM sakila.film

# Mostrar label “Curto”, “Médio” e “Longo” para durações 60/150/+
SELECT 
	title, 
    rental_rate, 
    IF(rental_rate <= 0.99, 'Tá barato', 'Na volta a gente ve') AS 'Preço',
    length,
    CASE 
		WHEN length < 60
			THEN 'Curto'
		WHEN length >= 60 AND length < 150
			THEN 'Médio'
		ELSE 'Longo'
	END AS 'Duração'
FROM sakila.film