Базы данных SQL-DDL и SQL-DML. Изучение транзакций. Программирование на языке SQL. Консольное приложение (Цикл лабораторных работ). Вариант № 2, страница 6

2.3. Выполнение работы

С помощью IBExpert была получена ER-диаграмма созданной БД (с помощью Database Designer), которая представлена на рис.1. С помощью IBExpert  автоматически сгенерированы данные для нескольких таблиц.

Индивидуальные SQL-запросы:

1)вывести всех актёров, которые снимались в тех же фильмах, в которых снимался указанный актёр;

2)вывести средние рейтинги фильмов, в которых Оскары получали канадские режиссёры (любая награда, любая страна);

3)добавить указанную награду композитору, который написал саундтреки к фильмам с рейтингом больше указанного, и последнему фильму, в котором он принимал участие.

Скрипт выполненных запросов приведен в листинге 4.

Листинг 4:

/******************************************************************************/

connect 'tiger.ftk.spbstu.ru:/var/lib/firebird/4081_1/filmbase.fdb' user 'SYSDBA' password 'masterkey';

/******************************************************************************/

/***      Following SET SQL DIALECT is just for the Database Comparer       ***/

/******************************************************************************/

SET SQL DIALECT 3;

drop View ACTORS;

drop View AVG_RET;

drop procedure NEW_PROCEDURE;

commit;

/******************************************************************************/

/***                                 Views                                  ***/

/******************************************************************************/

/* View: ACTORS */

CREATE VIEW ACTORS(

    NAME_,

    SURNAME_,

    FILM_)

AS

SELECT man.name, man.surname, film.name from man, film, part, career

WHERE man.man_id = part.man_id AND film.f_id = part.film_id and

part.film_id IN (SELECT DISTINCT film_id FROM part WHERE man_id = 7) and part.role_id = career.id and career.name = 'actor' and

man.man_id <> 7

;

/******************************************************************************/

/******************************************************************************/

/***      Following SET SQL DIALECT is just for the Database Comparer       ***/

/******************************************************************************/

SET SQL DIALECT 3;

/******************************************************************************/

/***                                 Views                                  ***/

/******************************************************************************/

/* View: AVG_RET */

CREATE VIEW AVG_RET(

    FILM_,

    MARK)

AS

SELECT film.name, AVG(rating.rating) from man, film, part, rating, award, premium, career, country WHERE award.award = 'Oscar' AND award.result = 'won' AND award.award_id = premium.award_id and premium.film_id = film.f_id and premium.man_id = man.man_id and rating.film_id = film.f_id AND man.country = country.c_id and country.name = 'Canada' AND man.man_id = part.man_id AND film.f_id = part.film_id and part.role_id = career.id and career.name = 'director'

GROUP BY film.name

;

SET TERM ^ ;

create or alter procedure NEW_PROCEDURE (

    NUMAWAR integer,

    AVGRET integer)

as

declare variable COMPOSER_ID integer;

BEGIN

    for SELECT distinct man.man_id from man, soundtrack, film

    WHERE soundtrack.author = man.man_id AND soundtrack.film =                        film.f_id AND film.f_id IN (SELECT film.f_id from film, rating

          WHERE film.f_id = rating.film_id

          GROUP BY film.f_id

         HAVING AVG(rating.rating)>= :avgret )

    into :composer_id do

    begin

         INSERT INTO premium values ((SELECT first 1 film.f_id from                film, soundtrack