percedure 1: CREATE OR REPLACE PROCEDURE InsertNewFilm( IN film_title… — Database 4031 — TG.ME

percedure 1: CREATE OR REPLACE PROCEDURE InsertNewFilm( IN film_title VARCHAR, IN release_year INT, IN language_name VARCHAR ) LANGUAGE plpgsql AS $$ DECLARE lang_id INT; BEGIN -- Find the language ID based on the language name SELECT language_id INTO lang_id FROM language WHERE name = language_name; -- If the language does not exist, raise an exception IF lang_id IS NULL THEN RAISE EXCEPTION 'Language % not found in the language table.', language_name; END IF; -- Insert the new film with the given title, release year, and language ID INSERT INTO film (title, release_year, language_id) VALUES (film_title, release_year, lang_id); RAISE NOTICE 'Film % has been successfully added.', film_title; END; $$; CALL InsertNewFilm('Inception', 2010, 'English'); 2: CREATE OR REPLACE PROCEDURE UpdateFilmRentalRate( IN film_ids INT, IN new_rental_rate DECIMAL ) LANGUAGE plpgsql AS $$ BEGIN UPDATE film SET rental_rate = $2 WHERE film_id = $1; -- Optional: You could add checks here to confirm if the film exists and raise exceptions if not END; $$; CALL UpdateFilmRentalRate(10, 4); select * from film where film_id=10 3: create or replace procedure films_of_each_customer(in customerID INT) language plpgsql as $$ declare filmRecord RECORD; begin for filmRecord in select film.film_id, title, rental_duration, rental_rate, inventory_id, film.last_update from film join inventory using(film_id) join store using(store_id) join customer using(store_id) where customer_id = customerID loop raise notice 'FilmID: %, Title: %, Rental duration: %, Rental rate: %, InventoryID: %, Last update: %', filmRecord.film_id, filmRecord.title, filmRecord.rental_duration, filmRecord.rental_rate, filmRecord.inventory_id, filmRecord.last_update; end loop; end; $$; call films_of_each_customer(2) extra: ALTER TABLE rental ADD COLUMN late_fee DECIMAL DEFAULT 0; CREATE OR REPLACE PROCEDURE ApplyFlatLateFee( IN flat_fee DECIMAL ) LANGUAGE plpgsql AS $$ DECLARE rows_updated INT; BEGIN -- Update late fees for overdue rentals with a single flat fee UPDATE rental AS r SET late_fee = flat_fee FROM film AS f, inventory AS i WHERE r.inventory_id = i.inventory_id AND i.film_id = f.film_id AND r.return_date IS NULL AND CURRENT_DATE > (r.rental_date + INTERVAL '1 day' * f.rental_duration); -- Get the number of rows affected by the update GET DIAGNOSTICS rows_updated = ROW_COUNT; -- Display a message indicating how many rentals had a flat late fee applied RAISE NOTICE '% rentals were updated with a flat late fee.', rows_updated; END; $$; CALL ApplyFlatLateFee(5.00);