create table movie (movid int primary key, movtitle varchar(30), movyear varchar(30),…
- User
create table movie (mov_id int primary key, mov_title varchar(30), mov_year varchar(30), mov_time varchar(30), mov_lang varchar(30), mov_dt_rel varchar(30), mov_rel_country varchar (30)) insert into movie values (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' ) drop table movie select * from movie
select top 5 percent * from movie select * from movie where mov_year = '1985' select distinct mov_title from movie select distinct mov_year from movie select *from movie where mov_title = 'Blade Runner' select *from movie where mov_year <=1982 create table reviewer (rev_id int primary key, rev_name varchar (20)) insert into reviewer values(9001 , 'Righty Sock '), (9002 , 'Jack Malvern '), (9003 , 'Flagrant Baronessa '), (9004 , 'Alec Shaw '), (9005 , ' '), (9006 , 'Victor Woeltjen '), (9007 , 'Simon Wright '), (9008 , 'Neal Wruck '), (9009 , 'Paul Monks '), (9010 , 'Mike Salvati '), (9011 , ' '), (9012 , 'Wesley S. Walker '), (9013 , 'Sasha Goldshtein '), (9014 , 'Josh Cates '), (9015 , 'Krug Stillo '), (9016 , 'Scott LeBrun '), (9017 , 'Hannah Steele '), (9018 , 'Vincent Cadena '), (9019 , 'Brandt Sponseller '), (9020 , 'Richard Adams ')
select * from reviewer https://www.w3resource.com/sql-exercises/movie-database-exercise/basic-exercises-on-movie-database.php
create table actor (act_id int primary key, act_fname varchar(20), act_lname varchar(20), act_gender varchar(20))insert into actor values
(101 ,' James ' , ' Stewart ' , ' M' ), (102 ,' Deborah ' , ' Kerr ' , ' F' ), (103 ,' Peter ' , ' OToole ' , ' M' ), (104 ,' Robert ' , ' De Niro ' , ' M' ), (105 ,' F. Murray ' , ' Abraham ' , ' M' ), (106 ,' Harrison ' , ' Ford ' , ' M' ), (107 ,' Nicole ' , ' Kidman ' , ' F' ), (108 ,' Stephen ' , ' Baldwin ' , ' M' ), (109 ,' Jack ' , ' Nicholson ' , ' M' ), (110 ,' Mark ' , ' Wahlberg ' , ' M' ), (111 ,' Woody ' , ' Allen ' , ' M' ), (112 ,' Claire ' , ' Danes ' , ' F' ), (113 ,' Tim ' , ' Robbins ' , ' M' ), (114 ,' Kevin ' , ' Spacey ' , ' M' ), (115 ,' Kate ' , ' Winslet ' , ' F' ), (116 ,' Robin ' , ' Williams ' , ' M' ), (117 ,' Jon ' , ' Voight ' , ' M' ), (118 ,' Ewan ' , ' McGregor ' , ' M' ), (119 ,' Christian ' , ' Bale ' , ' M' ), (120 ,' Maggie ' , ' Gyllenhaal ' , ' F' ), (121 ,' Dev ' , ' Patel ' , ' M' ), (122 ,' Sigourney ' , ' Weaver ' , ' F' ), (123 ,' David ' , ' Aston ' , ' M' ), (124 ,' Ali ' , ' Astin ' , ' F' ) select * from actor select * from actor where act_fname = 'Mark' select * from actor where act_lname = 'Wahlberg' select * from actor where act_lname = '110' SELECT reviewer.rev_name FROM reviewer UNION(SELECT movie.mov_title FROM movie); from this create right join, left join, full outer join
- Assistant
To create the right join, left join, and full outer join, you can use the following queries:
- Right Join:
SELECT reviewer.rev_name, movie.mov_title FROM reviewer RIGHT JOIN movie ON reviewer.rev_id = movie.mov_id;- Left Join:
SELECT reviewer.rev_name, movie.mov_title FROM reviewer LEFT JOIN movie ON reviewer.rev_id = movie.mov_id;- Full Outer Join:
SELECT reviewer.rev_name, movie.mov_title FROM reviewer FULL OUTER JOIN movie ON reviewer.rev_id = movie.mov_id;Note: The syntax for joining tables may vary depending on the database management system you are using.
- User
From the following table, write a SQL query to find the director who directed a movie that featured a role in 'Eyes Wide Shut'. create movie director table, insert into movie director values
- Assistant
To find the director who directed a movie that featured a role in 'Eyes Wide Shut', you would first need to have a table that includes both movie and director information. Here's an example of how you can create and insert values into a "movie_director" table:
CREATE TABLE movie_director ( mov_id int, mov_title varchar(30), mov_year varchar(30), mov_time varchar(30), mov_lang varchar(30), mov_dt_rel varchar(30), mov_rel_country varchar(30), dir_id int, dir_name varchar(20) ); INSERT INTO movie_director (mov_id, mov_title, mov_year, mov_time, mov_lang, mov_dt_rel, mov_rel_country, dir_id, dir_name) VALUES (901, 'Vertigo', '1958', '128', 'English', '1958-08-24', 'UK', 1001, 'Alfred Hitchcock'), (902, 'The Innocents', '1961', '100', 'English', '1962-02-19', 'SW', 1002, 'Jack Clayton'), (903, 'Lawrence of Arabia', '1962', '216', 'English', '1962-12-11', 'UK', 1003, 'David Lean'), (904, 'The Deer Hunter', '1978', '183', 'English', '1979-03-08', 'UK', 1004, 'Michael Cimino'), (905, 'Amadeus', '1984', '160', 'English', '1985-01-07', 'UK', 1005, 'Milos Forman'), (906, 'Blade Runner', '1982', '117', 'English', '1982-09-09', 'UK', 1006, 'Ridley Scott'), (907, 'Eyes Wide Shut', '1999', '159', 'English', NULL, 'UK', 1007, 'Stanley Kubrick'), ... -- Continue inserting values for other movies and directorsOnce you have the "movie_director" table set up, you can use the following SQL query to find the director who directed a movie featuring a role in 'Eyes Wide Shut':
SELECT dir_name FROM movie_director WHERE mov_id IN ( SELECT mov_id FROM movie_director WHERE mov_title = 'Eyes Wide Shut' );This query will return the name of the director(s) who directed a movie that featured a role in 'Eyes Wide Shut'.