From the following tables, write a SQL query to find the movies with year and genres

  • برمجة سي كيو ال sql

From the following tables, write a SQL query to find the movies with year and genres. Return movie title, movie year and generic title.

 

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
gen_id |      gen_title
--------+----------------------
   1001 | Action
   1002 | Adventure
   1003 | Animation
   1004 | Biography
   1005 | Comedy
   1006 | Crime
   1007 | Drama
   1008 | Horror
   1009 | Music
   1010 | Mystery
   1011 | Romance
   1012 | Thriller
   1013 | War
 mov_id | gen_id
--------+--------
    922 |   1001
    917 |   1002
    903 |   1002
    912 |   1003
    911 |   1005
    908 |   1006
    913 |   1006
    926 |   1007
    928 |   1007
    918 |   1007
    921 |   1007
    902 |   1008
    923 |   1009
    907 |   1010
    927 |   1010
    901 |   1010
    914 |   1011
    906 |   1012
    904 |   1013

الأجوبة

SELECT mov_title, mov_year, gen_title
FROM movie
NATURAL JOIN movie_genres
NATURAL JOIN genres;

Sample Output:
                     mov_title                      | mov_year |      gen_title
----------------------------------------------------+----------+----------------------
 Aliens                                             |     1986 | Action
 Deliverance                                        |     1972 | Adventure
 Lawrence of Arabia                                 |     1962 | Adventure
 Princess Mononoke                                  |     1997 | Animation
 Annie Hall                                         |     1977 | Comedy
 The Usual Suspects                                 |     1995 | Crime
 The Shawshank Redemption                           |     1994 | Crime
 Seven Samurai                                      |     1954 | Drama
 Back to the Future                                 |     1985 | Drama
 Trainspotting                                      |     1996 | Drama
 Slumdog Millionaire                                |     2008 | Drama
 The Innocents                                      |     1961 | Horror
 Beyond the Sea                                     |     2004 | Music
 Eyes Wide Shut                                     |     1999 | Mystery
 Spirited Away                                      |     2001 | Mystery
 Vertigo                                            |     1958 | Mystery
 American Beauty                                    |     1999 | Romance
 Blade Runner                                       |     1982 | Thriller
 The Deer Hunter                                    |     1978 | War
 Aliens                                             |     1986 | Action
 Deliverance                                        |     1972 | Adventure
 Lawrence of Arabia                                 |     1962 | Adventure
 Princess Mononoke                                  |     1997 | Animation
 Annie Hall                                         |     1977 | Comedy
 The Usual Suspects                                 |     1995 | Crime
 The Shawshank Redemption                           |     1994 | Crime
 Seven Samurai                                      |     1954 | Drama
 Back to the Future                                 |     1985 | Drama
 Trainspotting                                      |     1996 | Drama
 Slumdog Millionaire                                |     2008 | Drama
 The Innocents                                      |     1961 | Horror
 Beyond the Sea                                     |     2004 | Music
 Eyes Wide Shut                                     |     1999 | Mystery
 Spirited Away                                      |     2001 | Mystery
 Vertigo                                            |     1958 | Mystery
 American Beauty                                    |     1999 | Romance
 Blade Runner                                       |     1982 | Thriller
 The Deer Hunter                                    |     1978 | War
 Aliens                                             |     1986 | Action
 Deliverance                                        |     1972 | Adventure
 Lawrence of Arabia                                 |     1962 | Adventure
 Princess Mononoke                                  |     1997 | Animation
 Annie Hall                                         |     1977 | Comedy
 The Usual Suspects                                 |     1995 | Crime
 The Shawshank Redemption                           |     1994 | Crime
 Seven Samurai                                      |     1954 | Drama
 Back to the Future                                 |     1985 | Drama
 Trainspotting                                      |     1996 | Drama
 Slumdog Millionaire                                |     2008 | Drama
 The Innocents                                      |     1961 | Horror
 Beyond the Sea                                     |     2004 | Music
 Eyes Wide Shut                                     |     1999 | Mystery
 Spirited Away                                      |     2001 | Mystery
 Vertigo                                            |     1958 | Mystery
 American Beauty                                    |     1999 | Romance
 Blade Runner                                       |     1982 | Thriller
 The Deer Hunter                                    |     1978 | War
 Aliens                                             |     1986 | Action
 Deliverance                                        |     1972 | Adventure
 Lawrence of Arabia                                 |     1962 | Adventure
 Princess Mononoke                                  |     1997 | Animation
 Annie Hall                                         |     1977 | Comedy
 The Usual Suspects                                 |     1995 | Crime
 The Shawshank Redemption                           |     1994 | Crime
 Seven Samurai                                      |     1954 | Drama
 Back to the Future                                 |     1985 | Drama
 Trainspotting                                      |     1996 | Drama
 Slumdog Millionaire                                |     2008 | Drama
 The Innocents                                      |     1961 | Horror
 Beyond the Sea                                     |     2004 | Music
 Eyes Wide Shut                                     |     1999 | Mystery
 Spirited Away                                      |     2001 | Mystery
 Vertigo                                            |     1958 | Mystery
 American Beauty                                    |     1999 | Romance
 Blade Runner                                       |     1982 | Thriller
 The Deer Hunter                                    |     1978 | War
 Aliens                                             |     1986 | Action
 Deliverance                                        |     1972 | Adventure
 Lawrence of Arabia                                 |     1962 | Adventure
 Princess Mononoke                                  |     1997 | Animation
 Annie Hall                                         |     1977 | Comedy
 The Usual Suspects                                 |     1995 | Crime
 The Shawshank Redemption                           |     1994 | Crime
 Seven Samurai                                      |     1954 | Drama
 Back to the Future                                 |     1985 | Drama
 Trainspotting                                      |     1996 | Drama
 Slumdog Millionaire                                |     2008 | Drama
 The Innocents                                      |     1961 | Horror
 Beyond the Sea                                     |     2004 | Music
 Eyes Wide Shut                                     |     1999 | Mystery
 Spirited Away                                      |     2001 | Mystery
 Vertigo                                            |     1958 | Mystery
 American Beauty                                    |     1999 | Romance
 Blade Runner                                       |     1982 | Thriller
 The Deer Hunter                                    |     1978 | War
 Aliens                                             |     1986 | Action
 Deliverance                                        |     1972 | Adventure
 Lawrence of Arabia                                 |     1962 | Adventure
 Princess Mononoke                                  |     1997 | Animation
 Annie Hall                                         |     1977 | Comedy
 The Usual Suspects                                 |     1995 | Crime
 The Shawshank Redemption                           |     1994 | Crime
 Seven Samurai                                      |     1954 | Drama
 Back to the Future                                 |     1985 | Drama
 Trainspotting                                      |     1996 | Drama
 Slumdog Millionaire                                |     2008 | Drama
 The Innocents                                      |     1961 | Horror
 Beyond the Sea                                     |     2004 | Music
 Eyes Wide Shut                                     |     1999 | Mystery
 Spirited Away                                      |     2001 | Mystery
 Vertigo                                            |     1958 | Mystery
 American Beauty                                    |     1999 | Romance
 Blade Runner                                       |     1982 | Thriller
 The Deer Hunter                                    |     1978 | War
 Aliens                                             |     1986 | Action
 Deliverance                                        |     1972 | Adventure
 Lawrence of Arabia                                 |     1962 | Adventure
 Princess Mononoke                                  |     1997 | Animation
 Annie Hall                                         |     1977 | Comedy
 The Usual Suspects                                 |     1995 | Crime
 The Shawshank Redemption                           |     1994 | Crime
 Seven Samurai                                      |     1954 | Drama
 Back to the Future                                 |     1985 | Drama
 Trainspotting                                      |     1996 | Drama
 Slumdog Millionaire                                |     2008 | Drama
 The Innocents                                      |     1961 | Horror
 Beyond the Sea                                     |     2004 | Music
 Eyes Wide Shut                                     |     1999 | Mystery
 Spirited Away                                      |     2001 | Mystery
 Vertigo                                            |     1958 | Mystery
 American Beauty                                    |     1999 | Romance
 Blade Runner                                       |     1982 | Thriller
 The Deer Hunter                                    |     1978 | War
(133 rows)
سجل الدخول لتستطيع الإجابة
هل كان المحتوى مفيد؟

0 نعم

0 لا

أسئلة مشابهة

From the following tables, write a SQL query to find the years when most of the ‘Mystery Movies’ produced. Count the number of generic title and compute their average rating. Group the result set on movie release year, generic title
From the following tables, write a SQL query to find all the movies with year, genres, and name of the director
From the following tables, write a SQL query to find the highest-rated ‘Mystery Movies’. Return the title, year, and rating
From the following table, write a SQL query to compute the average time and count number of movies for each genre

القوائم الدراسية التي ينتمي لها السؤال

معلومات ذات صلة

تبحث عن مدرس اونلاين؟

ابحث عن مدرسك
محتاج مساعدة باختيار المدرس الافضل؟ تواصل مع فريقنا الان لمساعدتك بتأمين افضل مدرس
ماهو التخصص الذي تبحث عنه؟
اكتب هنا...