1 Write a query in SQL to find the full name of the Actorswho appeare...

1. Write a query in SQL to find the full name of the â€œActorsâ€ who appeared...

80.2K

Verified Solution

Question

Programming

1. Write a query in SQL to find the full name of the â€œActorsâ€who appeared in the movie titled Star Wars (using JOIN ).
2.Write a SQL query to find the movie Title that has receivedLowest rating from reviewers, but whose actors have received anaward for their contribution in movie. (Expected Output: FantasticBeasts and Where to Find Them)
3.Write a SQL query that display the list of genres, Number ofmovies in that genre grouped by year and genre.
4.Write a SQL query that display movie titles that have receivedAt least 3 awards.
5.Write a SQL query that display the titles, release date,distributor information of all movies whose rating is not provided(Number of star not given). Sort this list by released year.
6.Write a SQL query that display the list of movies and thecategory of the awards received by movies that are distributed byâ€œDisneyâ€
7.Write a SQL query that display all the different professionsin movie industry exclude the professions that start with letterR.
PROVIDED Tables and Values:
CREATE TABLE `artists` (
`artist_id` INT NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`contact_no` varchar(15) DEFAULT NULL,
`Profession` TEXT DEFAULT NULL,
`birth_date` datetime DEFAULT NULL,
`address` TEXT DEFAULT NULL,
PRIMARY KEY (`artist_id`)
) ;
CREATE TABLE `movies` (
`movie_id` INT NOT NULL AUTO_INCREMENT,
`title` varchar(45) NOT NULL,
`genre` varchar(25) NOT NULL,
`gross` decimal(6,2) DEFAULT NULL,
`Distributor` varchar(45) NOT NULL,
`release_date` datetime DEFAULT NULL,
PRIMARY KEY (`movie_id`),
KEY `genre_fk_idx` (`genre`)
) ;
CREATE TABLE `awards` (
`awards_id` INT NOT NULL AUTO_INCREMENT,
`movie_id` INT DEFAULT NULL,
`person_id` INT DEFAULT NULL,
`category` varchar(45) DEFAULT NULL,
PRIMARY KEY (`awards_id`),
KEY `person_id_idx` (`person_id`),
KEY `movie_id_idx` (`movie_id`),
CONSTRAINT `movie_awards_fk` FOREIGN KEY (`movie_id`) REFERENCES`movies` (`movie_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `person_awards_fk` FOREIGN KEY (`person_id`) REFERENCES`artists` (`artist_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ;
CREATE TABLE `movies_ratings` (
`rating_id` INT NOT NULL AUTO_INCREMENT,
`movie_id` INT NOT NULL,
`person_id` INT NOT NULL,
`number_of_stars` INT DEFAULT NULL,
PRIMARY KEY (`rating_id`),
KEY `movies__ratings_fk_idx` (`movie_id`),
KEY `artist_ratings_fk_idx` (`person_id`),
CONSTRAINT `artist_ratings_fk` FOREIGN KEY (`person_id`) REFERENCES`artists` (`artist_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `movies__ratings_fk` FOREIGN KEY (`movie_id`) REFERENCES`movies` (`movie_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ;

CREATE TABLE `movie_cast` (
`id` INT NOT NULL AUTO_INCREMENT,
`movie_id` INT DEFAULT NULL,
`person_id` INT DEFAULT NULL,
`category` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `person_id_idx` (`person_id`),
KEY `movie_id_idx` (`movie_id`),
CONSTRAINT `movie_cast_fk` FOREIGN KEY (`movie_id`) REFERENCES`movies` (`movie_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `person_cast_fk` FOREIGN KEY (`person_id`) REFERENCES`artists` (`artist_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ;

INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (1,'Idrissa Akuna ','Elba','4706091972','Actor','1972-09-0600:00:00','CA');
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (2,'ChristopherHemsworth','Hemsworth','3408111983','Actor','1983-08-1100:00:00','IL');
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (3,'Zoe ','Saldana','4106191978','Actor','1978-06-1900:00:00','AZ');
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (4,'Christopher','Townsend','3903251976','VisualEffects','1976-03-25 00:00:00',NULL);
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (5,'Guy','Williams','3209211985','Reviewer','1985-09-2100:00:00',NULL);
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (6,'Jonathan','Fawkner','3011131990','Reviewer','1990-11-1300:00:00',NULL);
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (7,'Dan','Sudick','4701201972','Visual Effects','1972-01-2000:00:00','NJ');
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (8,'Tom','Holland','2401061996','Actor','1996-06-0100:00:00','CA');
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (9,'Justin ','Marks','2804021992','Screenplay','1992-04-0200:00:00','LA');
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (10,'Adam','McGay','5104171968','Director','1968-04-1700:00:00','PA');
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (11,'Adam','McGay','5104171968','Actor','1968-04-1700:00:00','PA');
INSERT INTO artists(`artist_id`,`first_name`,`last_name`,`contact_no`,`Profession`,`birth_date`,`address`)VALUES (12,'Justin ','Marks','2804021992','Screenplay','1992-04-0200:00:00','LA');
INSERT INTO movies(`movie_id`,`title`,`genre`,`gross`,`Distributor`,`release_date`)VALUES (1,'Star Wars','Sci Fi',2.06,'Disney','2015-12-1800:00:00');
INSERT INTO movies(`movie_id`,`title`,`genre`,`gross`,`Distributor`,`release_date`)VALUES (2,'Jurassic World','Sci Fi',1.67,'Universal','2015-05-2900:00:00');
INSERT INTO movies(`movie_id`,`title`,`genre`,`gross`,`Distributor`,`release_date`)VALUES (3,'Minions','Comedy',1.15,'Universal','2015-07-1000:00:00');
INSERT INTO movies(`movie_id`,`title`,`genre`,`gross`,`Distributor`,`release_date`)VALUES (4,'The Jungle Book','Adventure',9.66,'Disney','2016-04-0400:00:00');
INSERT INTO movies(`movie_id`,`title`,`genre`,`gross`,`Distributor`,`release_date`)VALUES (5,'Fantastic Beasts and Where to FindThem','Fantasy',8.14,'Warner Bros','2016-09-10 00:00:00');
INSERT INTO movies(`movie_id`,`title`,`genre`,`gross`,`Distributor`,`release_date`)VALUES (6,'Zootopia','Comedy',1.02,'Disney','2016-02-1300:00:00');
INSERT INTO movies(`movie_id`,`title`,`genre`,`gross`,`Distributor`,`release_date`)VALUES (7,'Beauty and theBeast','Fantasy',1.26,'Disney','2017-02-23 00:00:00');
INSERT INTO movies(`movie_id`,`title`,`genre`,`gross`,`Distributor`,`release_date`)VALUES (8,'Spider-Man: Homecoming','SuperHero',8.80,'Sony','2017-06-28 00:00:00');
INSERT INTO movies(`movie_id`,`title`,`genre`,`gross`,`Distributor`,`release_date`)VALUES (9,'Guardians of the Galaxy Vol. 2','SuperHero',8.60,'Disney','2017-04-10 00:00:00');
INSERT INTO movies(`movie_id`,`title`,`genre`,`gross`,`Distributor`,`release_date`)VALUES (10,'Thor: Ragnarok','Super Hero',8.54,'Disney','2017-10-1000:00:00');

INSERT INTO awards(`awards_id`,`movie_id`,`person_id`,`category`) VALUES(1,1,1,'Actor');
INSERT INTO awards (`awards_id`,`movie_id`,`person_id`,`category`)VALUES (2,1,2,'Actor');
INSERT INTO awards (`awards_id`,`movie_id`,`person_id`,`category`)VALUES (3,1,4,'Visual Effects');
INSERT INTO awards (`awards_id`,`movie_id`,`person_id`,`category`)VALUES (4,2,5,'Visual Effects');
INSERT INTO awards (`awards_id`,`movie_id`,`person_id`,`category`)VALUES (5,2,3,'Actor');
INSERT INTO awards (`awards_id`,`movie_id`,`person_id`,`category`)VALUES (6,5,10,'Director');
INSERT INTO awards (`awards_id`,`movie_id`,`person_id`,`category`)VALUES (7,4,9,'Screenplay');
INSERT INTO awards (`awards_id`,`movie_id`,`person_id`,`category`)VALUES (8,5,2,'Actor');
INSERT INTO awards (`awards_id`,`movie_id`,`person_id`,`category`)VALUES (9,5,10,NULL);

INSERT INTO movie_cast(`id`,`movie_id`,`person_id`,`category`) VALUES(1,1,1,'Actor');
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (2,1,2,'Actor');
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (3,1,4,'Visual Effects');
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (4,2,5,'Visual Effects');
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (5,2,3,'Actor');
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (6,5,10,'Director');
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (7,4,9,'Screenplay');
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (8,5,2,'Actor');
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (9,5,10,NULL);
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (10,1,10,'Director');
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (11,2,2,'Actor');
INSERT INTO movie_cast (`id`,`movie_id`,`person_id`,`category`)VALUES (12,2,7,'Visual Effects');

INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(1,1,5,5);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(2,2,5,5);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(3,3,5,5);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(4,4,5,4);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(5,5,5,4);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(6,6,5,4);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(7,7,5,4);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(8,8,5,4);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(9,1,6,5);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(10,2,6,5);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(11,3,6,5);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(12,4,6,5);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(13,7,6,4);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(14,8,6,4);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(15,9,6,4);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(16,10,6,4);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(17,9,5,NULL);
INSERT INTO movies_ratings(`rating_id`,`movie_id`,`person_id`,`number_of_stars`) VALUES(18,10,5,NULL);

Answer & Explanation Solved by verified expert

3.8 Ratings (482 Votes)

AnswerHello I have written all the required SQL QURIES Please findthem below1SQLQUERYselect concatfirstname lastname FullName fromartists join moviecast ON artistsartistid moviecastpersonidwhere movieid in select See Answer

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member

1. Write a query in SQL to find the full name of the â€œActorsâ€ who appeared...