SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Branch_offices (Branch_code INT PRIMARY KEY, Location_ varchar2(255), Opening_hours varchar2(255), Phone_number char(5) ); CREATE TABLE Departments (Department_code INT PRIMARY KEY, Department_name varchar2(255), Opening_hours varchar2(255), Branch INT, Equipment INT, CONSTRAINT Branch_offices_Departments_FK FOREIGN KEY (Branch) REFERENCES Branch_offices(Branch_code) ); CREATE TABLE Staff ( Personal_access_code int PRIMARY KEY, Full_name varchar2(255), Date_of_birth date, Position_ varchar2(255), Phone_number char(5), Department int, CONSTRAINT Departments_Staff_FK FOREIGN KEY (Department) REFERENCES Departments(Department_code) ); CREATE TABLE Clients (Client_code int PRIMARY KEY, Full_name varchar2(255), TIN int, Passport_information int, Personal_bank_account int, Personal_manager int, CONSTRAINT Staff_Clients_FK FOREIGN KEY (Personal_manager) REFERENCES Staff(Personal_access_code) ); CREATE TABLE Active_customer_transactions (Transaction_number int PRIMARY KEY, Date_of_transaction date, Organisation int, Transaction_amount int, Assignment_o_the_operation varchar2(255), CONSTRAINT Clients_Active_customer_transactions_FK FOREIGN KEY (Organisation) REFERENCES Clients(Client_code) ); insert all into Branch_offices(Branch_code ,Location_ , Opening_hours, Phone_number) values ('100001', 'Апрелевка', '8:00-22:00', '35212') into Branch_offices(Branch_code ,Location_ , Opening_hours, Phone_number) values ('100002', 'Нарофоминск', '8:00-20:00', '35224') into Branch_offices(Branch_code ,Location_ , Opening_hours, Phone_number) values ('100003', 'Московский', '8:00-23:00', '35567') into Branch_offices(Branch_code ,Location_ , Opening_hours, Phone_number) values ('100004', 'Зеленоград', '10:00-20:00', '35876') into Branch_offices(Branch_code ,Location_ , Opening_hours, Phone_number) values ('100005', 'Иваново', '10:00-20:00', '35123') SELECT * FROM dual; insert all into Departments(Department_code, Department_name, Opening_hours, Branch, Equipment) values ('101', 'Финансовый отдел 1', '8:00-22:00', '100001', '2') into Departments(Department_code, Department_name, Opening_hours, Branch, Equipment) values ('202', 'Финансовый отдел 2', '8:00-22:00', '100002', '5') into Departments(Department_code, Department_name, Opening_hours, Branch, Equipment) values ('303', 'Финансовый отдел 3', '8:00-23:00', '100003', '20') into Departments(Department_code, Department_name, Opening_hours, Branch, Equipment) values ('404', 'Финансовый отдел 4', '9:00-21:00', '100004', '4') into Departments(Department_code, Department_name, Opening_hours, Branch, Equipment) values ('505', 'Финансовый отдел 5', '9:00-20:00', '100005', '3') SELECT * FROM dual; insert all into Staff(Personal_access_code, Full_name, Date_of_birth, Position_ , Phone_number, Department) values ('4001', 'Грицун Игорь Валерьевич', '22-MAR-04', 'Менеджер', '75321', '101') into Staff(Personal_access_code, Full_name, Date_of_birth, Position_ , Phone_number, Department) values ('4065', 'Сысоев Владимир Анатольевич', '19-DEC-96', 'Кассир', '85635', '202') into Staff(Personal_access_code, Full_name, Date_of_birth, Position_ , Phone_number, Department) values ('4078', 'Железняк Борис Аркадьевич', '15-APR-00', 'Старший менеджер', '67890', '303') into Staff(Personal_access_code, Full_name, Date_of_birth, Position_ , Phone_number, Department) values ('4021', 'Жуков Игорь Витальевич', '20-DEC-89', 'Менеджер', '65436', '404') into Staff(Personal_access_code, Full_name, Date_of_birth, Position_ , Phone_number, Department) values ('4056', 'Мартыненко Анатолий Викторович', '30-JUN-90', 'Старший менеджер', '98987', '505') SELECT * FROM dual; insert all into Clients(Client_code, Full_name, TIN, Passport_information, Personal_bank_account, Personal_manager) values ('80000001', 'Кошелева Виктория Андреевна', '781633333333', '4524655578', '102485', '4001') into Clients(Client_code, Full_name, TIN, Passport_information, Personal_bank_account, Personal_manager) values ('80000025', 'Максимова Кристина Владиславовна', '781633458625', '4524487562', '102985', '4065') into Clients(Client_code, Full_name, TIN, Passport_information, Personal_bank_account, Personal_manager) values ('80000065', 'Баранцева Надежда Антоновна', '452658954521', '4518499965', '102456', '4078') into Clients(Client_code, Full_name, TIN, Passport_information, Personal_bank_account, Personal_manager) values ('80000087', 'Тамаренко Павел Вадимович', '542632518476', '4519485632', '103524', '4021') into Clients(Client_code, Full_name, TIN, Passport_information, Personal_bank_account, Personal_manager) values ('80000091', 'Шевцов Артемий Ильдарович', '457982645478', '4520859789', '108524', '4056') SELECT * FROM dual; insert all into Active_customer_transactions (Transaction_number, Date_of_transaction, Organisation, Transaction_amount, Assignment_o_the_operation) values ('50000001', '10-NOV-24', '80000001', '102865', 'Прокупка товаров') into Active_customer_transactions (Transaction_number, Date_of_transaction, Organisation, Transaction_amount, Assignment_o_the_operation) values ('50000078', '11-NOV-24', '80000025', '787956', 'Перевод средств') into Active_customer_transactions (Transaction_number, Date_of_transaction, Organisation, Transaction_amount, Assignment_o_the_operation) values ('50001526', '12-NOV-24', '80000065', '202500', 'Оплата услуг') into Active_customer_transactions (Transaction_number, Date_of_transaction, Organisation, Transaction_amount, Assignment_o_the_operation) values ('50012498', '13-NOV-24', '80000087', '500000', 'Перевод средств') into Active_customer_transactions (Transaction_number, Date_of_transaction, Organisation, Transaction_amount, Assignment_o_the_operation) values ('50012852', '14-NOV-24', '80000091', '120000', 'Покупка товаров') SELECT * FROM dual; alter table Staff ADD (email varchar2(255) default 'Unknown'); update staff set email = case when Personal_access_code = 4001 then 'artem.gyrgyfr@yandex.ru' when Personal_access_code = 4065 then 'sysoev.deu@mail.ru' when Personal_access_code = 4078 then 'ABZhelezn@yandex.ru' when Personal_access_code = 4021 then 'Jukov.AV@gmail.ru' when Personal_access_code = 4056 then 'Martynenko.fr@mail.ru' end; alter table Clients add (Passport_information_2 VARCHAR2(255)); update Clients set Passport_information_2 = TO_CHAR(Passport_information); alter table Clients drop column Passport_information; alter table Clients rename column Passport_information_2 to Passport_information; update Staff set Position_ = case when Personal_access_code = 4065 then 'Менеджер' when Personal_access_code = 4021 then 'Старший менеджер' else Position_ end; select * from Branch_offices; select * from Departments; select * from Staff; select * from Clients; select * from Active_customer_transactions;