Task 6: Create SQL code for the following queries: All your tables should be ins

Question

Task 6:

Create SQL code for the following queries: All your tables should be inserted with at least 4 rows of data inserted before any queries were made

a. Sales reports;

i. by age group,

ii. by gender

b. Inventory reports

i. by genre (category),

ii. by price range,

iii. most profitable movies (highest to lowest),

iv. total sales by month and year,

v. retail sales (most successful)

c. Streaming reports:

i. by length

ii. by time of day

d. Downloading reports

i. By length

ii. By time of day

e. Create StoredProcedures:

i. GetTotalSales that displays the salesperson name as well as their total sales.

ii. Create a stored procedure called GetTotalSalesGreaterThanFive that displays the online movie name as well as their total movie download for all movie producers who have had more than one sale this year.

iii. Create Stored procedure called FindtheMovieProducer which accepts @ProducerName VARCHAR parameter and filters results by movie category

iv. Create Stored procedure called FindtheMovieForKids which accepts @ProducerName VARCHAR parameter and filters results by kids age  

Explanation / Answer

Tables:

Movie => movieId, title, runningtime, Genre, year, length, rating,price,time, producer

Customer=> CustId(pk),movieId(fk),Cust_name, Cust_Address, Cust_Phone, cust_age, gender

Supplier => SupId, sup_name, sup_address, sup_phone

Employee =>EmpId, emp_name, emp_address,emp_phone,totalSales   

a. Sales Reports:

i)Select CustId, movieId, age, gender from Customer inner join Movie

On Customer.movieId=Movie.movieId

group by Customer.age

ii) Select CustId, movieId, age, gender from Customer inner join Movie

On Customer.movieId=Movie.movieId

group by Customer.gender

b. Inventory Reports:

i)By Genre:

Select *from Movie group by Genre

ii)By Price range:

Select *from Movie where price between 100 and 1000 //(you may change 100and 1000accordingly)

iii) Most Profitable Movies:

Select movieId,title,price from movie order by price asc

iv)Total sales by month and year:

Select movieId,title,price from movie where year between 2016 and 2017

v)Retails   sales:

Select (count)price ,title from movie group by rating

C) Streaming reports:

i) By length:

Select * from movie group by length

ii) By time of day:

Select movieId,title from movie group by time

d) Downloading Reports:

i)By length:

Select * from movie group by length

ii) By time of day:

Select movieId,title from movie group by time

e) StoredProcedures:

i) Create procedure GetTotalSales(@name varchar(20),@total int)

As

Select * from employee where emp_name=@name, totalsales=@total

End

iii) Create procedure FindtheMovieProducer(@producerName varchar(20))

As

Select movieId,title,Genre from Movie where producer=@producerName

End

iv) Create procedure FindtheMovieForKids(@producerName varchar(20))

As

Select ,title,Genre,age from Movie inner join Customer where Customer.age between 1 and 14 and producer=@producerName

End

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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