pease can anyone helps me with these questions any help is much more appriciated
ID: 3705460 • Letter: P
Question
pease can anyone helps me with these questions any help is much more appriciated.
(2) Using the view s18h7a, define another view s18h7 to provide information about a film, including its title, category, number actors and number of renters, as shown in the example here:
(3) Using the view s18h7, list the films in the category ‘Comedy’ with the top 10 number of renters. If there is a tie in the #10 spot, list all films.
(4) Using the view s18_h7, write a function S18h7_4 that counts the number of films in a category within a specific range of number of actors. It takes three parameters:
1. Category: the name of a category
2. min_actors: the minimum number of actors a film must have in order to be counted.
3. min_actors: the maximum number of actors a film must not exceed in order to be counted.
1 select 2 from s18h7a 3 limit 10; fim (210) film_id num_renters 23 12 7 distinct customers have rented the film with id 2. 4 12 21 15 18 12 23 7 9 10Explanation / Answer
1.
CREATE VIEW s18h7a AS
select film_id , sum(renters) as sum_renters
FROM customers
Group by film_id
2.
CREATE VIEW s18h7 AS
select s.film_id, c.title, c.cateogry, sum(c.actors) as num_actors, s.num_renters
FROM customers c, s18h7a s
Where s.film_id = c.film_id
Group by c.film_id
3.
select film_id, title, num_renters
From s18h7
where category = 'Comedy'
limit 10;
4.
CREATE FUNCTION S18h7_4
( @category varchar(10), @min_actors INT, @max_actors INT )
RETURNS INT
AS
BEGIN
DECLARE @NoOfFilm VARCHAR(50);
select count(*) INTO @NoOfFilm
From s18h7
where category = @category
and num_actors >= @min_actors
and num_actors <= @max_actors
RETURN @NoOfFilm;
END;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.