Actor (actor_id, first_name, last_name, last_update) Address (address_id, address, address2, district, postal_code, phone, last_update, city_id) Category (category_id, name, last_update) City (city_id, city, last_update, country_id) Country (country_id, country, last_update) Customer (customer_id, first_name, last_name, email, active, create_date, last_update, address_id, store_id) Film (film_id, title, description, release_year, rental_duration, rental_rate, length, replacement_cost, rating, special_features, language_id, original_language_id) Film_Actor (actor_id, film_id, last_update) Film_Category (film_id, category_id, last_update) Film_Text (film_id, title, description) Inventory (inventory_id, last_update, store_id, film_id) Language (language_id, name, last_update) Payment (payment_id, amount, payment_date, last_update, rental_id, customer_id, staff_id) Rental (rental_id, rental_date, return_date, last_update, staff_id, inventory_id, customer_id) Staff (staff_id, first_name, last_name, picture, email, active, username, password, last_update, store_id, address_id) Store (store_id, last_update, manager_staff_id, address_id)
Mostrar toda la información de la tabla film PROJECT (Film / film_id, title, description, release_year, rental_duration, rental_rate, length, replacement_cost, rating, special_features) SELECT * FROM film;
Mostrar sólo el título y el rating de todos las películas PROJECT (Film / title, rating) SELECT title, rating FROM film;
Mostrar sólo las películas con rating R PROJECT (SELECT (Film / rating = ‘R’) / title, rating) SELECT title, rating FROM film WHERE rating = 'R';
Mostrar sólo las películas con rating R que duren entre 90 y 120 minutos inclusive. PROJECT (SELECT (Film / rating = ‘R’ ^ length >= 90 ^ length <= 120 ) / title, rating, length) SELECT title, rating, length FROM film WHERE rating = 'R' AND length>=90 AND length<=120;
Obtener en cuántas películas ha participado cada actor PROJECT (actor JOIN (actorid = actorid) film_actor / lastname, firstname, CUENTA film_id (film_actor)) SELECT CONCAT(actor.last_name,', ',actor.first_name) Actor, COUNT(film_actor.film_id) FROM actor INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id GROUP BY actor.actor_id, actor ORDER BY actor;
Calcular cuánto ha gastado un cliente en renta de películas PROJECT (customer JOIN (customer_id = customer_id) payment / firstname, lastname, SUMA amount (payment)) SELECT CONCAT(first_name,' ',last_name) Cliente, SUM(amount) 'Gasto Total' FROM customer INNER JOIN payment ON customer.customer_id = payment.customer_id GROUP BY Cliente; Generar una consulta que regresa todas las películas que duran 2 horas junto con los actores que participaron en cada película.
PROJECT (SELECT (film JOIN (film_id = film_id= film_actor) (film_actor JOIN (actor_id = actor_id) actor) / length = 120) / title, firstname, lastname, length) SELECT title, GROUP_CONCAT(CONCAT(first_name,' ',last_name)) Actores, length FROM film INNER JOIN film_actor ON film.film_id = film_actor.film_id INN ER JOIN actor ON film_actor.actor_id = actor.actor_id WHERE length = 120 GROUP BY title ORDER BY title;
Se desea saber con cuáles actores han trabajado con cierto actor SELECT (actor JOIN (actor_id = actor_id) film_actor / actor_id!=163 ^ film_id IN PROJECT(SELECT (actor / actor_id = 163) / film_id)
SELECT DISTINCT actor.actor_id AS 'ID', CONCAT(first_name,' ',last_name) AS Actor FROM actor INNER JOIN film_actor ON (actor.actor_id=film_actor.actor _id) WHERE actor.actor_id!=163 AND film_id IN (SELECT film_id FROM film_actor WHERE actor_id=163) ORDER BY Actor;
Mostrar la consulta y el resultado que obtenga el par de actores que han trabajado en más películas juntos. Por ejemplo, quizá Cristopher y Bela Walken han trabajado en 50 películas. La consulta debe regresar a los dos actores y el número de películas. PROJECT (SELECT (actor JOIN (film_actor JOIN (actor_id = actor_id) (film_id = film_id ^actor_id ¡= actor_id) (film_actor AS colega JOIN (actor_id = actor_id) actor AS actor_colega)) / actor.first_name, actor.last_name, actor_colega.first_name, actor_colega.last_name) SELECT actor.first_name, actor.last_name, actor_colega.first_name, actor_colega.last_name, COUNT(*) as n from actor INNER JOIN fil m_actor on (actor.actor_id = film_actor.actor_id) INNER JOIN film_actor colega on (film_actor.film_id=colega.film_id and actor.actor_id <> colega.actor_id) INNER JOIN actor actor_colega on (colega.actor_id=actor_colega.actor_id) GROUP BY actor.first_name, a ctor.last_name, actor_colega.first_name, actor_colega.last_name ORDER BY n DESC LIMIT 1;
Realizar un desplegado de películas en cuya descripción se encuentre la palabra great PROYECT(SELECT (film, description LIKE %great%)/ title, description) SELECT title, description FROM film WHERE description LIKE '%great%'; Realizar un desplegado de películas en cuya descripción se encuentre la palabra great good PROYECT(SELECT (film, description LIKE %great good%)/ title, description) SELECT title, description FROM film WHERE description LIKE '%great good%';
Realizar un desplegado de películas en cuya descripción se encuentre la palabra very good PROYECT(SELECT (film, description LIKE %very good%)/ title, description) SELECT title, description FROM film WHERE description LIKE '%very good%';
Realizar un desplegado de películas en cuya descripción se encuentre la palabra bad PROYECT(SELECT (film, description LIKE %bad%)/ title, description) SELECT title, description FROM film WHERE description LIKE '%bad%';
Obtener el promedio de rentas por día de la semana select weekday(solo_fecha) as NumDia, dayname(solo_fecha) as Dia, avg(rentas) as 'Promedio de Rentas', count(*) as 'N Dias', avg(rentas) * count(*) as Total from (select date(rental_date) as solo_fecha, count(*) as rentas from rental group by solo_fecha) agrupado_por_dia group by Dia, NumDia order by NumDia;
