--Создаем таблицу "Клиенты"
CREATE TABLE Clients (id_klienta NUMBER(5),
Familiya varchar2(30) NOT NULL,
Imya varchar2(15) NOT NULL,
Otchestvo varchar2(30) NOT NULL,
Nomer_telefona NUMBER(11) NOT NULL UNIQUE,
CONSTRAINT PK_klient PRIMARY KEY (id_klienta));

--Создаем таблицу "Услуги"
CREATE TABLE Uslugi (id_uslugi NUMBER(2),
razmer varchar2(5) NOT NULL UNIQUE,
tsena NUMBER(3) NOT NULL,
CONSTRAINT PK_usluga PRIMARY KEY (id_uslugi));

--Создаем таблицу "Фотографы"
CREATE TABLE Fotografy (id_fotografa NUMBER(3),
Imya_fotografa varchar2(15) NOT NULL,
CONSTRAINT PK_fotograf PRIMARY KEY (id_fotografa));

--Создаем таблицу "Заказы"
CREATE TABLE Zakazy (id_zakaza NUMBER(5),
id_uslugi NUMBER(2),
id_klienta NUMBER(5),
data_vyp_zakaza DATA,
kol_vo NUMBER(4) NOT NULL,
id_fotografa NUMBER(3),
CONSTRAINT FK_PK_fotograf FOREIGN KEY (id_fotografa) REFERENCES Fotografy (id_fotografa),
CONSTRAINT FK_PK_usluga FOREIGN KEY (id_uslugi) REFERENCES Uslugi (id_uslugi),
CONSTRAINT FK_PK_klient FOREIGN KEY (id_klienta) REFERENCES Clients (id_klienta),
CONSTRAINT PK_nomer_zakaza PRIMARY KEY (id_zakaza));

--Проверяем таблицы по заданию лабы №1
DESCRIBE Clients;
DESCRIBE Uslugi;
DESCRIBE Fotografy;
DESCRIBE Zakazy;

--Заполняем таблицу "Клиенты"
INSERT INTO Clients VALUES (1, 'Petrov', 'Nikolay', 'Nikolaevich', 9330305520);
INSERT INTO Clients VALUES (2, 'Ivanov', 'Ivan', 'Nikolaevich', 9620306022);
INSERT INTO Clients VALUES (3, 'Vasilyev', 'Petr', 'Andreevich', 9200335033);
INSERT INTO Clients VALUES (4, 'Semenova', 'Olga', 'Vladimirovna', 9600999024);
INSERT INTO Clients VALUES (5, 'Sokolova', 'Lyudmila', 'Olegovna', 9620305020);

--Заполняем таблицу "Услуги"
INSERT INTO Uslugi VALUES (1, '10x15', 20);
INSERT INTO Uslugi VALUES (2, '15x20', 40);
INSERT INTO Uslugi VALUES (3, '20x30', 60);
INSERT INTO Uslugi VALUES (4, '30x40', 80);

--Заполняем таблицу "Фотографы"
INSERT INTO Fotografy VALUES (1, 'Irina');
INSERT INTO Fotografy VALUES (2, 'Andrey');
INSERT INTO Fotografy VALUES (3, 'Alexandr');
INSERT INTO Fotografy VALUES (4, 'Marina');
INSERT INTO Fotografy VALUES (5, 'Nikolay');
INSERT INTO Fotografy VALUES (6, 'Edgar');

--Заполняем таблицу "Заказы"
INSERT INTO Zakazy VALUES (1, 4, 5, to_data ('11/06/05','DD/MM/YY'), 5, 4);
INSERT INTO Zakazy VALUES (2, 2, 4, to_data ('12/06/05','DD/MM/YY'), 15, 6);
INSERT INTO Zakazy VALUES (3, 1, 1, to_data ('12/06/05','DD/MM/YY'), 30, 2);
INSERT INTO Zakazy VALUES (4, 3, 3, to_data ('12/06/05','DD/MM/YY'), 1, 1);
INSERT INTO Zakazy VALUES (5, 1, 2, to_data ('16/06/05','DD/MM/YY'), 7, 3);
INSERT INTO Zakazy VALUES (6, 2, 1, to_data ('21/06/05','DD/MM/YY'), 10, 5);
INSERT INTO Zakazy VALUES (7, 3, 4, to_data ('21/06/05','DD/MM/YY'), 12, 1);
INSERT INTO Zakazy VALUES (8, 1, 3, to_data ('22/06/05','DD/MM/YY'), 6, 3);
INSERT INTO Zakazy VALUES (9, 2, 2, to_data ('24/06/05','DD/MM/YY'), 4, 2);
INSERT INTO Zakazy VALUES (10, 3, 5, to_data ('26/06/05','DD/MM/YY'), 2, 4);

--Выводим все четыре таблицы на экран для проверки
SELECT * FROM Clients;
SELECT * FROM Uslugi;
SELECT * FROM Fotografy;
SELECT * FROM Zakazy;

 --Подсчитываем кол-во клиентов, заказавших фото 10х15, сортируя по id клиента
SELECT COUNT(id_klienta) FROM Zakazy
WHERE id_uslugi = 1
ORDER BY id_klienta ASC;

 --Получаем сведения о клиентах и фотографах, где в заказе фото 10х15
SELECT id_klienta, id_fotografa FROM Zakazy
WHERE id_uslugi = 1
GROUP BY id_klienta
ORDER BY id_klienta ASC;

 --Выводим список клиентов, сделавших заказ 21 или 22 июня
SELECT id_klienta FROM Zakazy
WHERE data_vyp_zakaza = '21/06/05' OR data_vyp_zakaza = '22/06/05';

 --Получаем ФИО, умножаем цену на кол-во фото и прибавляем +10%
SELECT Familiya, Imya, Otchestvo FROM Clients
WHERE (SELECT tsena FROM Uslugi
        WHERE (SELECT kol_vo FROM Zakazy
            WHERE (SELECT kol_vo * tsena * 1.1 AS stoimost) ) )
GROUP BY Familiya;

--Добавляем данные о новом клиенте и проверяем
INSERT INTO Clients VALUES (6, 'Globa', 'Tatyana', 'Dmitrievna', 9333027733);
SELECT * FROM Clients;

--Делаем скидку 2% для тех, кто сделал заказ 12 июня (умножая цену на кол-во и отнимая 2%)
SELECT id_klienta, data_vyp_zakaza, kol_vo FROM Zakazy
WHERE ( (data_vyp_zakaza = '12/06/05') AND (SELECT tsena FROM Uslugi
    WHERE (SELECT kol_vo * tsena AS stoimost2
        WHERE (stoimost2 - (stoimost2 * 2 / 100)) ) ))
GROUP BY id_klienta;

--Создаем явный курсор, выводящий данные клиентов, имеющих фамилии, оканчивающиеся на "-ов"
SET serveroutput ON DECLARE
cursor get_surname IS
SELECT * FROM Clients
WHERE Familiya LIKE '%ov';
a get_surname%rowtype;
BEGIN
    OPEN get_surname; loop
    dbms_output.enable;
    dbms_output.put_line('Familii klientov, okanchivauschiesya na "-ov":'||a.Familiya||' '||a.Imya||' '||a.Nomer_telefona);
    fetch get_surname INTO a; exit WHEN get_surname%notfound; END loop; close get_surname; END;
/

--Создаем такой же явный курсор, но с параметром
SET serveroutput ON DECLARE
cursor get_surname (inage Familiya) IS
SELECT * FROM Clients
WHERE Familiya = inage;
a get_surname%rowtype;
BEGIN
    OPEN get_surname('ov'); loop
    dbms_output.enable;
    dbms_output.put_line('Familii klientov, okanchivauschiesya na "-ov":'||a.Familiya||' '||a.Imya||' '||a.Nomer_telefona);
    fetch get_surname INTO a; exit WHEN get_surname%notfound; END loop; close get_surname; END;
/

--Создаем триггер, позволяющий учитывать, кто и когда вносил данные в таблицу "Заказы"
CREATE OR REPLACE
TRIGGER adt
after INSERT OR DELETE OR UPDATE
ON Zakazy
FOR each ROW DECLARE
BEGIN
    INSERT INTO audit1(Familiya, TIME) VALUES (USER, sysdate);
END adt;

--Создаем триггеры ко всем таблицам, позволяющим автоматически
-- вводить данные для полей, являющихся первичным ключом и имеющих числовой тип.
-- Для таблицы "Клиенты":
CREATE SEQUENCE avto START WITH 1 INCREMENT BY 1
/
CREATE OR REPLACE
TRIGGER ident_k
BEFORE INSERT ON Clients
FOR each ROW
DECLARE
BEGIN
    SELECT avto.NEXTVAL
    INTO :NEW.id_klienta
    FROM dual;
END ident_k;
/

--Для таблицы "Услуги"
CREATE SEQUENCE avto START WITH 1 INCREMENT BY 1
/
CREATE OR REPLACE
TRIGGER ident_u
BEFORE INSERT ON Uslugi
FOR each ROW
DECLARE
BEGIN
    SELECT avto.NEXTVAL
    INTO :NEW.id_uslugi
    FROM dual;
END ident_u;
/

--Для таблицы "Фотографы"
CREATE SEQUENCE avto START WITH 1 INCREMENT BY 1
/
CREATE OR REPLACE
TRIGGER ident_f
BEFORE INSERT ON Fotografy
FOR each ROW
DECLARE
BEGIN
    SELECT avto.NEXTVAL
    INTO :NEW.id_fotografa
    FROM dual;
END ident_f;
/

--Для таблицы "Заказы"
CREATE SEQUENCE avto START WITH 1 INCREMENT BY 1
/
CREATE OR REPLACE
TRIGGER ident_z
BEFORE INSERT ON Zakazy
FOR each ROW
DECLARE
BEGIN
    SELECT avto.NEXTVAL
    INTO :NEW.id_zakaza
    FROM dual;
END ident_z;
/