List all the details of the language table ordered by language_id in descending
ID: 3891365 • Letter: L
Question
List all the details of the language table ordered by language_id in descending order in sql.
For each movie, list the movie_id and the total cost of the movie in terms of actor payments, but list only those movie_id’s that have a total cost of less than 50,000. Order by movie_id. Rename the total cost column to ‘Total Cost’.
For each movie language_id, list the language_id and the minimum star rating for that language_id, but only include movies that have a rating of ‘PG’. Order by language_id. Rename the minimum star_rating to column to ‘Min Star Rating’.
Using only a WHERE to perform the join (instead of a JOIN...ON or a NATURAL JOIN), list the actor’s first and last name and the payment given to all the actors in the movie with the movie_id of 359. Order by actor first name.
List the category_name of movies for the actor with the first name of ‘Helen’. Use subqueries and IN clauses and ensure that each category name is listed only once. Order by category name.
List the title, rating and the number of actors in each movie, but only list those movies that have more than 12 actors. Order the result set by rating and then by descending title order. Use a JOIN…ON clause to perform the join. Rename the number of actors column to ‘Number of Actors’.
List the star_rating, length, movie title, and rating of the movie(s) in each star rating value (0-5) with the maximum length and order by the star rating and then title. Use a correlated subquery on the star rating.
Explanation / Answer
-------------------------------------------------------------
List all the details of the language table ordered by language_id in descending order in sql.
SELECT * FROM language
ORDER BY language_id DESC
-------------------------------------------------------------
For each movie, list the movie_id and the total cost of the movie in terms of actor payments, but list only those movie_id’s that have a total cost of less than 50,000. Order by movie_id. Rename the total cost column to ‘Total Cost’.
SELECT M.movie_id, SUM(AP.payment) "Total Cost" FROM movie AS M, actor_payments AS AP
WHERE M.movie_id = AP.movie_id
GROUP BY M.movie_id
HAVING SUM(AP.payment) < 50000
ORDER BY movie_id
-------------------------------------------------------------
Using only a WHERE to perform the join (instead of a JOIN...ON or a NATURAL JOIN), list the actor’s first and last name and the payment given to all the actors in the movie with the movie_id of 359. Order by actor first name.
SELECT A.first_name, A.last_name, A.payment FROM actor AS A, actor_payments AS AP
WHERE A.actor_id = AP.actor_id AND AP.movie_id = 359
GROUP BY A.actor_id
ORDER BY A.first_name
-------------------------------------------------------------
List the category_name of movies for the actor with the first name of ‘Helen’. Use subqueries and IN clauses and ensure that each category name is listed only once. Order by category name.
SELECT category_name FROM category
WHERE category_id IN(
SELECT category_id FROM movie
WHERE movie_id IN(
SELECT movie_id FROM actor_payments
WHERE actor_id = (
SELECT actor_id FROM actor
WHERE first_name = 'Helen')))
ORDER BY category_name
-------------------------------------------------------------
List the title, rating and the number of actors in each movie, but only list those movies that have more than 12 actors. Order the result set by rating and then by descending title order. Use a JOIN…ON clause to perform the join. Rename the number of actors column to ‘Number of Actors’.
SELECT M.title, M.rating, COUNT(AP.actor_id) "Number of Actors" FROM movie AS M
INNER JOIN actor_payments AS AP ON M.movie_id = AP.movie_id
GROUP BY M.movie_id
HAVING COUNT(AP.actor_id) > 12
-------------------------------------------------------------
List the star_rating, length, movie title, and rating of the movie(s) in each star rating value (0-5) with the maximum length and order by the star rating and then title. Use a correlated subquery on the star rating.
SELECT star_rating, length, title, rating FROM movie
ORDER BY rating DESC, title ASC
Note: As you have not attached you DB I issumed at myself:
language
language_id
language_name
category
category_id
category_name
movie
movie_id
title
desc
category_id
language_id
rating
star_rating
actor
actor_id
first_name
last_name
actor_payments
actor_payment_id
actor_id
movie_id
payment
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.