Question

Ad Campaign
Description
Suppose you are running an advertising campaign in Canada for which you need the film_ids and titles of all the films released in Canada. List the films in the alphabetical order of their titles.

Answer 1

Answer:

SELECT distinct f.Film_id,

      f.Title

from FILM as f

inner join inventory as i

on i.film_id=f.film_id

inner join store as s

on s.store_id=i.store_id

inner join address as a

on a.address_id=s.address_id

inner join city

on a.city_id=city.city_id

inner join country as c

on c.country_id=city.country_id

where c.country="Canada"

order by Title asc;

Explanation:using distinct is very important because if you don't use this then it contains duplicate values.

Answer 2

Answer:

Explanation:

Concept:

Each of the linked tables for this specific film table are taken from the Sakila DB, and an inner join is done.

Solution:

Get the film ids and names of the movies that were released in Canada using this query.

select Film_id, Title

   from film

inner join inventory

   using (film_id)

inner join store

   using (store_id)

inner join address

   using (address_id)

inner join city

   using (city_id)

inner join country

   using (country_id)

where country = 'Canada'

group by film_id, title

order by title;

• The film table is where we are getting the title and film id.
• Then, using film id from the inventory database, we are conducting an inner joining.
• Store id from the store tab and address id from the address table are next.
• Following country id from the nation table comes city id from the city table.
• These joins are mostly carried out to match the country id with the film id across the database, hence they are carried out to join with the corresponding columns.
• Last but not least, we sorted by title, which will arrange this alphabetically.

#SPJ3