Skip to content

Instantly share code, notes, and snippets.

@isaacbatst
Last active October 19, 2023 16:58
Show Gist options
  • Save isaacbatst/34d1155cb7c7ff85417cf41b8f1d5c59 to your computer and use it in GitHub Desktop.
Save isaacbatst/34d1155cb7c7ff85417cf41b8f1d5c59 to your computer and use it in GitHub Desktop.
-- Encontre o ator Johnny Cage
SELECT * FROM sakila.actor
WHERE first_name = 'JOHNNY' AND last_name = 'CAGE';
-- Encontre os filmes que podem ser alugados exatamente por 6 dias
SELECT * FROM sakila.film WHERE rental_duration = 6
-- Encontre os filmes de classificação R que podem ser alugados exatamente por 6 dias
SELECT * FROM sakila.film WHERE rental_duration = 6 AND rating = 'R'
-- Encontre os filmes de classificação R ou PG-13 que podem ser alugados exatamente por 6 dias
SELECT * FROM sakila.film WHERE rental_duration = 6 AND (rating = 'R' OR rating = 'pg-13')
-- Encontre os filmes: AFFEIR PREJUDICE, AFFRICAN EGG, AGENT TRUMAN e AIRPLANE SIERRA.
SELECT * FROM sakila.film
WHERE title IN ('AFFAIR PREJUDICE', 'AFRICAN EGG', 'AGENT TRUMAN', 'AIRPLANE SIERRA')
-- Encontre todos os filmes menos os listados anteriormente: 'AFFAIR PREJUDICE', 'AFRICAN EGG', 'AGENT TRUMAN', 'AIRPLANE SIERRA'
SELECT * FROM sakila.film
-- WHERE NOT title IN ('AFFAIR PREJUDICE', 'AFRICAN EGG', 'AGENT TRUMAN', 'AIRPLANE SIERRA')
-- Encontre filmes com duração entre 120 e 150 minutos.
SELECT COUNT(*) AS 'quantidade', AVG(rental_duration), json_arrayagg(title) FROM sakila.film WHERE length BETWEEN 120 AND 150 ORDER BY length DESC;
SELECT now(); -- datetime
SELECT curdate() -- date
SELECT curtime() -- time
SELECT YEAR(now())
SELECT MONTH(now())
SELECT DAY()
-- Encontre aluguéis feitos às 00h07m11s do dia 26/05/2005.
SELECT * FROM sakila.rental WHERE rental_date = '2005-05-26 00:07:11'
-- Encontre aluguéis feitos às 00h07m11s de qualquer dia.
SELECT * FROM sakila.rental WHERE TIME(rental_date) = '00:07:11'
-- Encontre aluguéis feitos no dia 26/05/2005 a qualquer horário.
SELECT * FROM sakila.rental WHERE DATE(rental_date) = '2005-05-26'
-- Encontre alugueis que foram devolvidos entre 26/05/2005 e 27/06/2005.
SELECT * FROM sakila.rental WHERE return_date BETWEEN '2005-05-26' AND '2005-05-27 23:59:59'
-- Encontre atores com o primeiro nome contendo "rey"
SELECT * FROM sakila.actor WHERE first_name LIKE '%rey'
-- USE sakila;
SELECT * FROM actor WHERE first_name LIKE '%rey%'
-- Encontre atores com o primeiro nome com 3 caracteres, sendo o primeiro a letra B.
SELECT * FROM actor WHERE CONCAT(first_name, ' ', last_name)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment