SQL exercises on movie Database: Find all the movies which released in the country other than UK
3. From the following table, write a SQL query to find those movies that have been released in countries other than the United Kingdom. Return movie title, movie year, movie time, and date of release, releasing country.
Sample table: movie
mov_id | mov_title | mov_year | mov_time | mov_lang | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo | 1958 | 128 | English | 1958-08-24 | UK
902 | The Innocents | 1961 | 100 | English | 1962-02-19 | SW
903 | Lawrence of Arabia | 1962 | 216 | English | 1962-12-11 | UK
904 | The Deer Hunter | 1978 | 183 | English | 1979-03-08 | UK
905 | Amadeus | 1984 | 160 | English | 1985-01-07 | UK
906 | Blade Runner | 1982 | 117 | English | 1982-09-09 | UK
907 | Eyes Wide Shut | 1999 | 159 | English | | UK
908 | The Usual Suspects | 1995 | 106 | English | 1995-08-25 | UK
909 | Chinatown | 1974 | 130 | English | 1974-08-09 | UK
910 | Boogie Nights | 1997 | 155 | English | 1998-02-16 | UK
911 | Annie Hall | 1977 | 93 | English | 1977-04-20 | USA
912 | Princess Mononoke | 1997 | 134 | Japanese | 2001-10-19 | UK
913 | The Shawshank Redemption | 1994 | 142 | English | 1995-02-17 | UK
914 | American Beauty | 1999 | 122 | English | | UK
915 | Titanic | 1997 | 194 | English | 1998-01-23 | UK
916 | Good Will Hunting | 1997 | 126 | English | 1998-06-03 | UK
917 | Deliverance | 1972 | 109 | English | 1982-10-05 | UK
918 | Trainspotting | 1996 | 94 | English | 1996-02-23 | UK
919 | The Prestige | 2006 | 130 | English | 2006-11-10 | UK
920 | Donnie Darko | 2001 | 113 | English | | UK
921 | Slumdog Millionaire | 2008 | 120 | English | 2009-01-09 | UK
922 | Aliens | 1986 | 137 | English | 1986-08-29 | UK
923 | Beyond the Sea | 2004 | 118 | English | 2004-11-26 | UK
924 | Avatar | 2009 | 162 | English | 2009-12-17 | UK
926 | Seven Samurai | 1954 | 207 | Japanese | 1954-04-26 | JP
927 | Spirited Away | 2001 | 125 | Japanese | 2003-09-12 | UK
928 | Back to the Future | 1985 | 116 | English | 1985-12-04 | UK
925 | Braveheart | 1995 | 178 | English | 1995-09-08 | UK
Sample Solution:
-- Selecting columns 'mov_title', 'mov_year', 'mov_time', 'mov_dt_rel', 'mov_rel_country'
-- from the 'movie' table
-- Filtering results where 'mov_rel_country' is not in the specified list ('UK')
SELECT mov_title, mov_year, mov_time,
mov_dt_rel AS Date_of_Release,
mov_rel_country AS Releasing_Country
FROM movie
WHERE mov_rel_country NOT IN ('UK');
Sample Output:
mov_title | mov_year | mov_time | date_of_release | releasing_country
----------------------------------------------------+----------+----------+-----------------+-------------------
The Innocents | 1961 | 100 | 1962-02-19 | SW
Annie Hall | 1977 | 93 | 1977-04-20 | USA
Seven Samurai | 1954 | 207 | 1954-04-26 | JP
(3 rows)
Code Explanation:
The said query in SQL which selects the movie title, year, duration, date of release, and releasing country from the 'movie' table, but only for movies that were not released in the UK.
Alternative Solutions:
Using IN Operator:
SELECT mov_title, mov_year, mov_time,
mov_dt_rel AS Date_of_Release,
mov_rel_country AS Releasing_Country
FROM movie
WHERE mov_rel_country NOT IN ('UK');
Explanation:
This solution uses the NOT IN operator to exclude movies released in the UK from the result set.
EXISTS Clause:
SELECT mov_title, mov_year, mov_time,
mov_dt_rel AS Date_of_Release,
mov_rel_country AS Releasing_Country
FROM movie m
WHERE NOT EXISTS (
SELECT 1
FROM movie
WHERE mov_rel_country = 'UK' AND mov_id = m.mov_id
);
Explanation:
This solution uses the EXISTS clause to check for the existence of a movie with a UK release in a subquery. If no such movie is found, it includes the record in the final result set.
Using a JOIN:
SELECT m.mov_title, m.mov_year, m.mov_time,
m.mov_dt_rel AS Date_of_Release,
m.mov_rel_country AS Releasing_Country
FROM movie m
JOIN (
SELECT mov_id
FROM movie
WHERE mov_rel_country <> 'UK'
) AS filtered ON m.mov_id = filtered.mov_id;
Explanation:
This solution performs an inner join between the movie table and a subquery that selects mov_id for movies not released in the UK. This ensures that only relevant records are included in the final result set.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: From the following tables, write a SQL query to find the director who directed a movie that casted a role for 'Eyes Wide Shut'. Return director first name, last name.
Next: From the following tables, write a SQL query to find those movies where reviewer is unknown. Return movie title, year, release date, director first name, last name, actor first name, last name.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.