CREATE SEQUENCE S22423_seqCLIENT;
CREATE SEQUENCE S22423_seqORDER;
CREATE SEQUENCE S22423_seqTASK;
CREATE SEQUENCE S22423_seqKINDOFTASK;
CREATE SEQUENCE S22423_seqBRIGADE;
CREATE SEQUENCE S22423_seqSTAFF;
CREATE SEQUENCE S22423_seqSTAFFSPECIALIZATION;

CREATE TABLE S22423_CLIENT(
CLIENT_ID number(8) primary key,
NAME varchar2(30) not null,
CONTACTS varchar2(30) not null
);
INSERT INTO S22423_CLIENT (CLIENT_ID, NAME, CONTACTS) VALUES (S22423_ seqCLIENT.NEXTVAL, 'RegionHouse',’22-14-22’);
INSERT INTO S22423_CLIENT (CLIENT_ID, NAME, CONTACTS) VALUES (S22423_ seqCLIENT.NEXTVAL, '21-Century',’55-55-13’);
INSERT INTO S22423_CLIENT (CLIENT_ID, NAME, CONTACTS) VALUES (S22423_ seqCLIENT.NEXTVAL, ‘Mikhalkov',’-’);
INSERT INTO S22423_CLIENT (CLIENT_ID, NAME, CONTACTS) VALUES (S22423_ seqCLIENT.NEXTVAL, ‘CityBlock',’77-87-77’);


CREATE TABLE S22423_ORDER(
ORDER_ID number(8) primary key,
CLIENT_ID number(3),
LOCATION varchar2(30) not null,
FOREIGN KEY (CLIENT_ID)  REFERENCES S22423_CLIENT(CLIENT_ID)
)
INSERT INTO S22423_ ORDER (ORDER_ID, CLIENT_ID, LOCATION) VALUES (S22423_seqORDER.NEXTVAL, 1,’Irkutsk, Telmana 54’);
INSERT INTO S22423_ ORDER (ORDER_ID, CLIENT_ID, LOCATION) VALUES (S22423_seqORDER.NEXTVAL, 3,’Irkutsk, Lesnaya 17’);
INSERT INTO S22423_ ORDER (ORDER_ID, CLIENT_ID, LOCATION) VALUES (S22423_seqORDER.NEXTVAL, 2,’Irkutsk, Lesnaya 22’);
INSERT INTO S22423_ ORDER  (ORDER_ID, CLIENT_ID, LOCATION) VALUES (S22423_seqORDER.NEXTVAL, 4,’Irkutsk, Polyakova 224a’);

CREATE TABLE S22423_KIND_OF_TASK(
KIND_ID number(8) primary key,
TYPE_OF_TASK varchar2(30) not null,
)
INSERT INTO S22423_ KIND_OF_TASK (KIND_ID, TYPE_OF_TASK) VALUES (S22423_seqKINDOFTASK.NEXTVAL, ‘Full repear‘);
INSERT INTO S22423_ KIND_OF_TASK (KIND_ID, TYPE_OF_TASK) VALUES (S22423_seqKINDOFTASK.NEXTVAL, ‘Repear some parts‘);
INSERT INTO S22423_ KIND_OF_TASK (KIND_ID, TYPE_OF_TASK) VALUES (S22423_seqKINDOFTASK.NEXTVAL, ‘Full bulding‘);
INSERT INTO S22423_ KIND_OF_TASK (KIND_ID, TYPE_OF_TASK) VALUES (S22423_seqKINDOFTASK.NEXTVAL, ‘Bulding some parts ‘);

CREATE TABLE S22423_TASK(
TASK_ID number(8) primary key,
ORDER_ID number(8),
KIND_ID number(8),
COST NUMBER(15) not null,
FOREIGN KEY (KIND_ID)  REFERENCES S22423_KIND_OF_TASK (KIND_ID) ,
FOREIGN KEY (ORDER_ID)  REFERENCES S22423_ORDER (ORDER_ID)
)
INSERT INTO S22423_ TASK (TASK_ID, ORDER_ID, KIND_ID, COST ) VALUES (S22423_ seqTASK.NEXTVAL, 4, 1, 700000);
INSERT INTO S22423_ TASK (TASK_ID, ORDER_ID, KIND_ID, COST ) VALUES (S22423_ seqTASK.NEXTVAL, 3, 2, 450000);
INSERT INTO S22423_ TASK (TASK_ID, ORDER_ID, KIND_ID, COST ) VALUES (S22423_ seqTASK.NEXTVAL, 2, 3, 1520000);
INSERT INTO S22423_ TASK (TASK_ID, ORDER_ID, KIND_ID, COST ) VALUES (S22423_ seqTASK.NEXTVAL, 1, 4, 1050000);

CREATE TABLE S22423_BRIGADE(
BRIGADE_ID number(8) primary key,
TASK_ID number(8),
NAME varchar2(30) not null,
SURNAME varchar2(30) not null,
FOREIGN KEY (TASK_ID)  REFERENCES S22423_TASK(TASK_ID)
)
INSERT INTO S22423_BRIGADE (BRIGADE_ID, TASK_ID,NAME, SURNAME) VALUES (S22423_ seqBRIGADE, 1, ‘Kuznetsov’, ‘Ivan’);
INSERT INTO S22423_BRIGADE (BRIGADE_ID, TASK_ID, NAME, SURNAME) VALUES (S22423_ seqBRIGADE, 2, ‘Petrov’, ‘Vasya’);
INSERT INTO S22423_BRIGADE (BRIGADE_ID, TASK_ID, NAME, SURNAME) VALUES (S22423_ seqBRIGADE, 3, ‘Kotov’, ‘Petr’);
INSERT INTO S22423_BRIGADE (BRIGADE_ID, TASK_ID, NAME, SURNAME) VALUES (S22423_ seqBRIGADE, 4, ‘Morozov’, ‘Dmitry’);

CREATE TABLE S22423_STAFF_SPECIALIZATION(
SPECIALIZATION _ID number(8) primary key,
SPECIALIZATION_NAME varchar2(30) not null,
)
INSERT INTO S22423_ STAFF_SPECIALIZATION (seqSTAFFSPECIALIZATION, SPECIALIZATION_NAME) VALUES (S22423_ seqSTAFFSPECIALIZATION, ‘Builder’);
INSERT INTO S22423_ STAFF_SPECIALIZATION (seqSTAFFSPECIALIZATION, SPECIALIZATION_NAME) VALUES (S22423_ seqSTAFFSPECIALIZATION, ‘Electric’);
INSERT INTO S22423_ STAFF_SPECIALIZATION (seqSTAFFSPECIALIZATION, SPECIALIZATION_NAME) VALUES (S22423_ seqSTAFFSPECIALIZATION, ‘Malyar’);
INSERT INTO S22423_ STAFF_SPECIALIZATION (seqSTAFFSPECIALIZATION, SPECIALIZATION_NAME) VALUES (S22423_ seqSTAFFSPECIALIZATION, ‘Plotnik’);


CREATE TABLE S22423_BRIGADE_STAFF(
STAFF_ID number(8) primary key,
BRIGADE_ID number(8),
SPECIALIZATION _ID number(8),
NAME varchar2(30) not null,
SURNAME varchar2(30) not null
FOREIGN KEY (BRIGADE_ID)  REFERENCES S22423_ BRIGADE(BRIGADE_ID),
FOREIGN KEY (SPECIALIZATION _ID)  REFERENCES S22423_ SPECIALIZATION (SPECIALIZATION _ID)
)
INSERT INTO S22423_ BRIGADE_STAFF (STAFF_ID, BRIGADE_ID, SPECIALIZATION _ID, NAME, SURNAME) VALUES (S22423_ seqSTAFF, 1, 4, ‘Petrov’, ‘Sergey’);
INSERT INTO S22423_ BRIGADE_STAFF (STAFF_ID, BRIGADE_ID, NAME, SURNAME) VALUES (S22423_ seqSTAFF, 2, 3,‘Sokolov’, ‘Ivan’);
INSERT INTO S22423_ BRIGADE_STAFF (STAFF_ID, BRIGADE_ID, NAME, SURNAME) VALUES (S22423_ seqSTAFF, 3,2, ‘Tomilov’, ‘Alexey’);
INSERT INTO S22423_ BRIGADE_STAFF (STAFF_ID, BRIGADE_ID, NAME, SURNAME) VALUES (S22423_ seqSTAFF, 4, 1,‘Panasenko’, ‘Lev’);

CREATE VIEW 22423_VIEW AS
SELECT C.NAME ORGANIZATION, O.LOCATION LOCATION, T.TYPE_OF_TASK TASK, K.COST, B.SURNAME BRIGADIER
FROM S22423_CLIENT C , S22423_ORDER O, S22423_TASK T, S22423_KINDOFTASK K, S22423_BRIGADE B
WHERE MOD(TO_NUMBER(TO_CHAR(sysdate, 'SS')), 2) = 0 AND
C.CLIENT_ID = O.CLIENT_ID AND O.ORDER_ID = T.ORDER_ID AND T.KIND_ID = K.KIND_ID AND
 T.TASK_ID  = B.TASK_ID;

SELECT 22423_VIEW.*,TO_NUMBER(TO_CHAR(sysdate, 'SS')) FROM 22423_VIEW