Question

Need MySql commands for

1. Use a UNION statement. Display the address ID, address, and district from the address table where the district is California or Alberta. Display the address ID, address, and district only from the address table where the address contains Jol.

2. Display the language ID and name from the language table and the film ID from the film table. Display all languages whether they have an associated film or not. Order results by language ID in ascending order.

3. List the actor ID, their first and last name of all actors and a count of the films they have done. Result set should be sorted descending by the actor’s first name and new column should be named FilmCount. The objective you are to show here is how to use a subquery in an expression.

4. Show the customer ID, their first and last name, and the date of their last payment (name this column RecentPayment) of all those that are inactive customers sorted descending by last name and then ascending by first name. The objective you are to show here is how to use a subquery in an expression.

5. List the film ID, title, and description from the film_text table for those films that have Zero Cage as the actor. Order your results set by the film description. The objective you are to show here is how to use a subquery in a filter.

Answer 1

1. SELECT address ID, address, district

FROM address WHERE district = "California" OR district = "Alberta"

UNION

SELECT  address ID, address, district

FROM address WHERE address = "Jol" ;

2.

SELECT language.language ID, language.name,film.film ID

FROM language,film

ORDER BY language ID ASC;

The later questions will need the name of the columns of each table, primary key and foreign key description to write executable SQL query.