Question

1. Return the full names (first and last) of actors with “SON” in their last name, ordered by their first name.
2. Find all the addresses where the second address (i.e. address2) is not empty (i.e., contains some text), and return these second addresses sorted.
3. Your Professor wants you to find all the information from rental table associated with those staff (or staff ID) whose address ID is 4 (in staff table). Professor wants you to use Sub-query function.
4. Your Professor wants you to find out the first name and district of all the customers whose district ends on “ia” – such as California.
5. Your Professor wants you to find all the payment-related information of customers other than the customer whose ID is 1.
6. Your Professor wants you to find out the full name (i.e. combine the first and last name) and the district of all the customers that live in a district called California.
7. Your Professor wants you to find the title, film id and category name (such as action) of those movies that are of the action genre (i.e. category name is Action).
8. Return the first and last names of actors who played in a film involving a “Crocodile” and a “Shark”, along with the release year of the movie, sorted by the actors’ last names.
9. Find all the film categories in which there are between 55 and 65 films. Return the names of these categories and the number of films per category, sorted by the number of films.
10. In how many film categories is the average difference between the film replacement cost and the rental rate larger than 17?

Answer 1

1. Select Concat(first_name," ", last_name) as "Full Name" from actor where last_name LIKE '%SON%' order by first_name;
2. Select * from Address where Address2 IS NOT NULL order by Address2;
3. Select * from Rental where staff_id IN( Select staff_id from Staff where address_id = 4);
4. Select first_Name , District from Customer inner join Address on Customer.Address_id = Address.Address_id  where District like '%ia';
5. Select * from Payment where Customer_ID != 1;
6. Select Concat(first_Name," ", last_Name) as "Full Name"  , District from Customer inner join Address on Customer.Address_id = Address.Address_id  where District = 'California';
7. Select Title , Category.Name from Film , Film_Category, Category where Category.Name = 'Action';
8. Select first_name, last_name , release_year, title from film inner join  film_actor on film.film_id = film_actor.film_id inner join actor on film_actor.actor_id = actor.actor_id where Description like '%Crocodile%' and Description like '%Shark%' order by last_name;
9. Select Category.name , Count(film_id) as Number_of_Films from Film_Category inner join Category on Film_Category.category_id = Category.category_id  group by Category.Name having Count(film_id) between and 65 order by Count(film_id);
10. Select Category.name , Avg(replacement_cost) - Avg(rental_rate) from film_category inner join film on film_category.film_id = film.film_id inner join Category on film_category.category_id = Category.category_id group by Category.name having Avg(replacement_cost) - Avg(rental_rate) > 17;

Do ask if any doubt. Please up-vote.