please using oracle sql!!! Use only the following tables: movie table customer_t

Question

please using oracle sql!!!

Use only the following tables: movie table

customer_target

genre

activity

movie_fact

1. What are the title and plot description of the movie that lost the most money? (How much money was lost is, to me, budget - gross.)

2. What is the name of one movie that has the most genres associated with it? Your query should take into account that several different movies

might have the same name.

3. What are the different genre names of the movie found in the previous question? Please assume that only one movie has this name.

4. For each possible genre, how many movies with that genre are there, and what is the gross amount for the top-grossing movie of that genre?

5. How many movies received ratings under two (or more) different genres from a given customer?

Explanation / Answer

Table construction:

CREATE TABLE movie_table (
movie_id int PRIMARY KEY NOT NULL,
year varchar(255),
budget Number,
gross Number,
plot_summary number;
);

Insert the values

INSERT INTO movie_table (movie_id, year, budget, gross, plot_summary)
VALUES ('1', '2017', '20000', '20', '4006', '100');

INSERT INTO movie_table (movie_id, year, budget, gross, plot_summary)
VALUES ('2', '2018', '20001', '21', '4001', '101');

*********************************

cust_id   is_target

CREATE TABLE customer_target(

cust_id int PRIMARY KEY NOT NULL,

is_target varchar(15)

);

INSERT INTO customer_target(cust_id,is_target)
VALUES('1','SUCCESS');

INSERT INTO customer_target(cust_id,is_target)
VALUES('2','FAILURE');

********************************

CREATE TABLE genre(
genre_id int PRIMARY KEY NOT NULL,

name varchar

);

INSERT INTO genre(genre_id,name)
VALUES('1','WATSON');

INSERT INTO genre(genre_id,name)
VALUES('2','SHERLOCK');

****************************************

CREATE TABLE activity(
activity_id int PRIMARY KEY NOT NULL,
name varchar

);

INSERT INTO activity(activity_id,name)
VALUES('1','daniel');

INSERT INTO activity(activity_id,name)
VALUES('2','sam');

********************************

CREATE TABLE movie_fact(
cust_id int,
movie_id int,
genre_id int,

time_id int,
recommended varchar(10),
activity_id int,

rating varchar(100),

sales varchar

);

INSERT INTO movie_fact(cust_id,movie_id,time_id,recommended,activity_id,rating,sales)
VALUES(
'1','1','1','YES','1','AWESOME','HIGH'

);
INSERT INTO movie_fact(cust_id,movie_id,time_id,recommended,activity_id,rating,sales)
VALUES(
'2','2','2','NO','2','FAILURE','LOW'

);

*****************************

Queries for the table

1.select title,plot_summary from movie_table where gross in ('4006','4001')

2.select name
from genre join activity
on genre.name = activity.name

3.select distinct name from genre
where genre_id in ('1','2');

4.

5.select count(name) from
movie_fact join genre
on movie_fact.genre_id=genre.genre_id
where movie_fact.rating in ('SUCCESS','FAILURE')

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.