view:
1:

CREATE VIEW FilmPopularitySummary AS
SELECT
    f.film_id,
    f.title,
    f.rating,
    COUNT(r.rental_id) AS total_rentals
FROM
    film AS f
LEFT JOIN inventory AS i ON f.film_id = i.film_id
LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id
GROUP BY
    f.film_id, f.title, f.rating;

select * from FilmPopularitySummary order by total_rentals desc limit 20

2:

CREATE VIEW CustomerRentalSummary AS
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    COUNT(distinct r.rental_id) AS total_rentals,
    COALESCE(SUM(p.amount), 0) AS total_payments
FROM
    customer AS c
LEFT JOIN rental AS r ON c.customer_id = r.customer_id
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
GROUP BY
    c.customer_id, c.first_name, c.last_name;

select * from CustomerRentalSummary order by total_payments desc limit 20

extra:

CREATE VIEW FilmAvailabilityPopularity AS
SELECT
    f.film_id,
    f.title,
    f.rating,
    COUNT(DISTINCT i.inventory_id) AS total_copies,
    COUNT(DISTINCT r.rental_id) AS total_rentals,
    COUNT(DISTINCT CASE WHEN r.return_date IS NULL THEN i.inventory_id END) AS copies_rented_out,
    COUNT(DISTINCT CASE WHEN r.return_date IS NOT NULL THEN i.inventory_id END) AS copies_available
FROM
    film AS f
JOIN inventory AS i ON f.film_id = i.film_id
LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id
GROUP BY
    f.film_id, f.title, f.rating;

select * from FilmAvailabilityPopularity

materialized
1:

CREATE MATERIALIZED VIEW StoreInventorySummary AS
SELECT
    s.store_id,
   
    COUNT(i.inventory_id) AS total_inventory,
    COUNT(r.rental_id) AS total_rented
FROM
    store AS s
LEFT JOIN inventory AS i ON s.store_id = i.store_id
LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id
GROUP BY
    s.store_id;
  
select * from StoreInventorySummary

2:

CREATE MATERIALIZED VIEW MostExpensiveMoviePerCustomer AS
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    f.title AS most_expensive_movie,
    f.rental_rate AS movie_price
FROM
    customer AS c
JOIN rental AS r ON c.customer_id = r.customer_id
JOIN inventory AS i ON r.inventory_id = i.inventory_id
JOIN film AS f ON i.film_id = f.film_id
WHERE f.rental_rate = (
    SELECT MAX(f2.rental_rate)
    FROM rental AS r2
    JOIN inventory AS i2 ON r2.inventory_id = i2.inventory_id
    JOIN film AS f2 ON i2.film_id = f2.film_id
    WHERE r2.customer_id = c.customer_id
);

select * from MostExpensiveMoviePerCustomer

function:
1:

create or replace function get_actor_by_filmID(input_filmID integer)
returns table  (
  first_name varchar(45),
  last_name varchar(45)
)
as $$
begin
    return query
    select
  actor.first_name,
  actor.last_name
    from
        actor join film_actor using(actor_id)
    where
        film_actor.film_id = input_filmID ;
end;
$$ language plpgsql;

select get_actor_by_filmID(133)

2:

create or replace function get_films_of_country(input_contry_of_film  varchar)
returns table  (
  film_id int,
  title varchar
)
as $$
begin
    return query
    select 
  distinct film.film_id,film.title
    from
        film join inventory using(film_id)
    join store using(store_id)
    join address using(address_id)
    join city using(city_id)
    join country using(country_id)
    where
        country = input_contry_of_film
  order by film_id asc;  
end;
$$ language plpgsql;

select *from  get_films_of_country('Canada')

3:

CREATE OR REPLACE FUNCTION FilmAvailability(film_id INT, store_id INT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (
        -- Check if there are available (unrented) copies in the store
        (SELECT COUNT(*) 
         FROM inventory i
         LEFT JOIN rental r ON i.inventory_id = r.inventory_id AND r.return_date IS NULL
         WHERE i.film_id = $1
         AND i.store_id = $2
         AND r.rental_id IS NULL) > 0
    );
END;
$$ LANGUAGE plpgsql;

SELECT FilmAvailability(10, 1);

extra:

CREATE OR REPLACE FUNCTION CustomerBalance(customer_id INT)
RETURNS DECIMAL AS $$
BEGIN
    RETURN (
        -- Total rental fees minus total payments
        (SELECT COALESCE(SUM(f.rental_rate), 0)
         FROM rental r
         JOIN inventory i ON r.inventory_id = i.inventory_id
         JOIN film f ON i.film_id = f.film_id
         WHERE r.customer_id = $1)
        -
        (SELECT COALESCE(SUM(amount), 0)
         FROM payment
         WHERE payment.customer_id = $1)
    );
END;
$$ LANGUAGE plpgsql;

SELECT CustomerBalance(12);

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);

trigger:
1:

CREATE OR REPLACE FUNCTION UpdateLastRentalDate()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE customer
    SET last_rental_date = NEW.rental_date
    WHERE customer_id = NEW.customer_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER UpdateCustomerLastRentalDate
AFTER INSERT ON rental
FOR EACH ROW
EXECUTE FUNCTION UpdateLastRentalDate();

2:

CREATE OR REPLACE FUNCTION CheckInventoryAvailability()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM rental
        WHERE inventory_id = NEW.inventory_id
        AND return_date IS NULL
    ) THEN
        RAISE EXCEPTION 'This inventory item is already rented out and has not been returned.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER PreventDoubleRental
BEFORE INSERT ON rental
FOR EACH ROW
EXECUTE FUNCTION CheckInventoryAvailability();

3:

-- First, create the trigger function
CREATE OR REPLACE FUNCTION CheckFilmReleaseYear()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the release year is less than 1900
    IF NEW.release_year < 1900 THEN
        -- Raise an exception to prevent insertion
        RAISE EXCEPTION 'Release year cannot be less than 1900.';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Now, create the trigger to call the function before inserting a new film
CREATE TRIGGER ValidateReleaseYear
BEFORE INSERT ON film
FOR EACH ROW
EXECUTE FUNCTION CheckFilmReleaseYear();

هرکسی هر تمرینی رو اگه برای من تلگرام فرستاد
روی تمرین یک ریپلای بزنید

هر کسی تمرین سری سوم sql inter رو برام توی پیوی فرستاده ریپلای بزنه

نمره نهایی حل تمرین درس در شیت وارد شد.
درصورت وجود هرگونه اشتباه یا مشکل به من پیام دهید

نمرات تا شب برای استاد ارسال میشه

سلام. برگه های پایگاه داده تصحیح شده است. و تا فردا ریز نمرات جمع و در اکسل اعلام می شود. فقط دوستان آمادگی داشته باشند که فردا و پس فردا دوشنبه و سه شنبه ساعت ۱۳:۳۰ الی ۱۴:۰۰ می توانید در محل دفتر بنده (ساختمان دانشکده برق) برگه ها را به صورت حضوری ببینید. دوستانی که امکان حضور ندارند ترجیحا به یکی از همکلاسی ها بسپارند که برای آنها برگه و پاسخ ها را چک کند.

نمرات نهایی پایگاه داده

دوستانی که اعتراض دارند نهایتا تا فردا ظهر به صورت حضوری در دفتر بنده حاضر باشند. در ضمن اگر امکان حضور ندارید به یکی از همکلاسی ها بسپارید و یا اگر باز هم مشکل داشتید به ایمیل بنده درخواست خود را ارسال نمایید. m.emadi@nit.ac.ir

ساعت حضور طبق اطلاعیه قبلی ساعت 13:30 الی 14:00 می باشد.

با سلام خدمت دوستان عزیزی که این ترم با ما همراه بودن، امیدواریم ترم خوبی براتون بوده باشه.

🔹️اگه انتقاد یا پیشنهادی دارین که بتونه تو ادامه بهمون کمک کنه خوشحال می‌شیم که باهامون در میون بذارین.

📌در آخر برای همتون در ادامه مسیر تحصیلی (و غیر تحصیلی) آرزوی سلامتی و موفقیت می‌کنیم.