The relations are:
● ACTOR (actor_id, first_name, last_name, last_update)
● LANGUAGE (language_id, name, last_update)
● CATEGORY (category_id, name, last_update)
● FILM (film_id, title, description, release_year, language_id,
length, rating, last_update)
● FILM_ACTOR(actor_id, film_id, last_update)
● FILM_CATEGORY (film_id, category_id, last_update)
● COMMENTS (review_id, film_id, reviewer_name, comment, score,
last_update)
(5 points) Retrieve the title of all the Movies in Italian and
without actors with first
name “JOHN”, ordered alphabetically.
This is my current SQL
select film.title
from film join language
on film.language_id = language.language_id
join film_actor
on film.film_id = film_actor.film_id
join actor
on film_actor.actor_id = actor.actor_id
where lower(language.name)= 'italian' and actor.first_name <>
'JOHN'
order by film.title asc;
This is suppose to be my query
|
ALABAMA DEVIL |
|
ARMAGEDDON LOST |
|
BENEATH RUSH |
|
BUCKET BROTHERHOOD |
|
CHISUM BEHAVIOR |
|
CONEHEADS SMOOCHY |
|
DANGEROUS UPTOWN |
|
DOUBLE WRATH |
|
ELIZABETH SHANE |
| FIRE WOLVES |
| GO PURPLE |
|
HARPER DYING |
|
HUNCHBACK IMPOSSIBLE |
| LIFE TWISTED |
But I keep getting this as my query
| ALABAMA DEVIL |
| ALABAMA DEVIL |
| ALABAMA DEVIL |
| ALABAMA DEVIL |
| ALABAMA DEVIL |
| ALABAMA DEVIL |
| ALABAMA DEVIL |
| ALABAMA DEVIL |
| ALABAMA DEVIL |
| ARMAGEDDON LOST |
| ARMAGEDDON LOST |
| ARMAGEDDON LOST |
| ARMAGEDDON LOST |
| ARMAGEDDON LOST |
| ARMAGEDDON LOST |
| ARMAGEDDON LOST |
| BENEATH RUSH |
| BENEATH RUSH |
| BENEATH RUSH |
| BENEATH RUSH |
| BENEATH RUSH |
| BENEATH RUSH |
| BENEATH RUSH |
| BUCKET BROTHERHOOD |
| BUCKET BROTHERHOOD |
| BUCKET BROTHERHOOD |
| BUCKET BROTHERHOOD |
| BUCKET BROTHERHOOD |
| BUCKET BROTHERHOOD |
| CHISUM BEHAVIOR |
| CHISUM BEHAVIOR |
| CHISUM BEHAVIOR |
| CHISUM BEHAVIOR |
| CONEHEADS SMOOCHY |
| CONEHEADS SMOOCHY |
| CONEHEADS SMOOCHY |
| CONEHEADS SMOOCHY |
| CONEHEADS SMOOCHY |
| CONEHEADS SMOOCHY |
| CONEHEADS SMOOCHY |
| CONEHEADS SMOOCHY |
| CONEHEADS SMOOCHY |
| DANGEROUS UPTOWN |
| DANGEROUS UPTOWN |
| DANGEROUS UPTOWN |
| DANGEROUS UPTOWN |
| DANGEROUS UPTOWN |
| DANGEROUS UPTOWN |
| DANGEROUS UPTOWN |
| DANGEROUS UPTOWN |
| DOUBLE WRATH |
| DOUBLE WRATH |
| DOUBLE WRATH |
| DOUBLE WRATH |
| DOUBLE WRATH |
| DOUBLE WRATH |
| DOUBLE WRATH |
| DOUBLE WRATH |
| ELIZABETH SHANE |
| ELIZABETH SHANE |
| ELIZABETH SHANE |
| FIRE WOLVES |
| FIRE WOLVES |
| FIRE WOLVES |
| FIRE WOLVES |
| FIRE WOLVES |
| GO PURPLE |
| GO PURPLE |
| GO PURPLE |
| HARPER DYING |
| HARPER DYING |
| HARPER DYING |
| HARPER DYING |
| HUNCHBACK IMPOSSIBLE |
| HUNCHBACK IMPOSSIBLE |
| JERSEY SASSY |
| JERSEY SASSY |
| JERSEY SASSY |
| JERSEY SASSY |
| JERSEY SASSY |
| JERSEY SASSY |
| JERSEY SASSY |
| JERSEY SASSY |
| LIFE TWISTED |
| LIFE TWISTED |
| LIFE TWISTED |
| LIFE TWISTED |
| LIFE TWISTED |
| LIFE TWISTED |
| LIFE TWISTED |
| LIFE TWISTED |
By looking at the results, we can see the results are being repeated. We need to use SELECT DISTINCT in the query to get rid of repeated values. SQL is based on multiple relational data sets and using distinct will eliminate duplicate values retrieved from JOiN operations from multiple tables. Please do comment for any queries or modification and I can explain accordingly.
The relations are: ● ACTOR (actor_id, first_name, last_name, last_update) ● LANGUAGE (language_id, name, last_update) ● CATEGORY...