WebKit development DATABASES

CREATE TABLE employeesweb ( id_emp NUMBER(5,0) PRIMARY KEY, first_name VARCHAR2(20), last_name VARCHAR2(20), tel_number NUMBER(10,0), adress_emp VARCHAR2(20) );
CREATE TABLE customersweb ( id_customer NUMBER(5,0) PRIMARY KEY, customer_fname VARCHAR(20), customer_lname VARCHAR(20), adress_customer VARCHAR(10), email_customer VARCHAR(15), tel_customer NUMBER(10,0), reg_date DATE);
CREATE TABLE bonus ( id_bonus NUMBER(2,0) PRIMARY KEY, bonus_value INTEGER);
CREATE TABLE salary ( id_salary NUMBER(2,0) PRIMARY KEY, salary_val INTEGER);
CREATE TABLE total_salary (id_final_salary INTEGER PRIMARY KEY,id_salary NUMBER(2,0), FOREIGN KEY (id_salary) REFERENCES salary(id_salary),id_bonus NUMBER(2,0),FOREIGN KEY (id_bonus) REFERENCES bonus(id_bonus));
CREATE TABLE discountweb (id_discount NUMBER(2,0) PRIMARY KEY, discount_description VARCHAR2(10), discount_percent NUMBER(2,2));
CREATE TABLE servicesweb (id_services NUMBER(2,0) PRIMARY KEY, service_name VARCHAR2(10), service_price INTEGER, service_quantity NUMBER(2,0), service_status VARCHAR2(10));
CREATE TABLE ordersweb ( id_order NUMBER(2,0), PRIMARY KEY (id_order),id_services NUMBER(2,0), FOREIGN KEY (id_services) REFERENCES servicesweb(id_services),id_discount NUMBER(2,0),FOREIGN KEY (id_discount) REFERENCES discountweb(id_discount));
CREATE TABLE departmentsweb (id_department NUMBER(2,0) PRIMARY KEY, department_name VARCHAR2(10), department_location VARCHAR2(10), department_email VARCHAR2(10), department_phone NUMBER(10,0), department_manager VARCHAR2(20), department_workhours_s DATE, department_workhours_f DATE);
ALTER TABLE employeesweb ADD id_department NUMBER(2,0);
ALTER TABLE employeesweb ADD CONSTRAINT id_departments FOREIGN KEY (id_departments) REFERENCES departmentsweb(id_department);
ALTER TABLE employeesweb ADD CONSTRAINT id_department FOREIGN KEY (id_department) REFERENCES departmentsweb(id_department);
ALTER TABLE employeesweb ADD id_final_salary INTEGER;
ALTER TABLE employeesweb ADD CONSTRAINT id_final_salary FOREIGN KEY (id_final_salary) REFERENCES total_salary(id_final_salary);
ALTER TABLE customersweb ADD id_order NUMBER(2,0);
ALTER TABLE customersweb ADD CONSTRAINT id_order FOREIGN KEY (id_order) REFERENCES ordersweb(id_order);

INSERT INTO bonus VALUES (1,20);
INSERT INTO bonus VALUES (2,15);
INSERT INTO bonus VALUES (3,10);
INSERT INTO bonus VALUES (4,5);

INSERT INTO salary VALUES (1,5000);
INSERT INTO salary VALUES (2,3000);
INSERT INTO salary VALUES (3,2000);
INSERT INTO salary VALUES (4,1800);

INSERT INTO total_salary VALUES (6000,&id_salary,&id_bonus);
INSERT INTO total_salary VALUES (3450,&id_salary,&id_bonus);
INSERT INTO total_salary VALUES (2200,&id_salary,&id_bonus);
INSERT INTO total_salary VALUES (1890,&id_salary,&id_bonus);
ALTER TABLE total_salary DROP (id_final_salary);
ALTER TABLE total_salary
DROP PRIMARY KEY;

INSERT INTO total_salary(id_final_salary, id_salary, id_bonus)
SELECT id_salary, id_bonus as id_bonus FROM salary, bonus GROUP BY final_salary;


DELETE FROM total_salary;

ALTER TABLE departmentsweb MODIFY (department_location VARCHAR2(20), department_email VARCHAR2(30), department_name VARCHAR2(30));
ALTER TABLE departmentsweb MODIFY (DEPARTMENT_WORKHOURS_S VARCHAR(20),DEPARTMENT_WORKHOURS_f VARCHAR(20));


INSERT INTO departmentsweb VALUES (10,'HR','Victoriei','hr@gmail.com',0721615779,'Emil Gheorghe','10:30:00','16:30:00');
INSERT INTO departmentsweb VALUES (11,'PR','Victoriei','pr@gmail.com',0721615779,'Gina Enache','10:30:00','16:30:00');
INSERT INTO departmentsweb VALUES (12,'ITfront','Victoriei','itfront@gmail.com',0721615779,'Paris Iannis','10:30:00','16:30:00');
INSERT INTO departmentsweb VALUES (13,'ITback','Victoriei','itback@gmail.com',0721615779,'Niculae Bogdan','10:30:00','16:30:00');

UPDATE PRODUCT_INFORMATION SET min_price =(SELECT list_price FROM product_information WHERE product_id = 1787) WHERE category_id = 15;

INSERT INTO employeesweb VALUES(20,'Iannis','Paris',0721615778,'Ana Ipatescu 12',12,6000);
INSERT INTO employeesweb VALUES(25,'Bogdan','Niculae',0721615771,'Calea Grivitei 81',13,3450);
INSERT INTO employeesweb VALUES(30,'Gina','Enache',0721615775,'Drumul Taberei 72',11,2200);
INSERT INTO employeesweb VALUES(35,'Emil','Gheorghe',0721615777,'Dorobantilor 22',10,1890);

ALTER TABLE discountweb MODIFY (discount_description VARCHAR2(30), DISCOUNT_PERCENT NUMBER(2));

INSERT INTO discountweb VALUES(41,'Christmas sales',10);
INSERT INTO discountweb VALUES(42,'Winter sales',15);
INSERT INTO discountweb VALUES(43,'Easter sales',10);
INSERT INTO discountweb VALUES(44,'BlackFriday sales',35);

INSERT INTO servicesweb VALUES(1,'Hosting',150,1,'OK');
INSERT INTO servicesweb VALUES(2,'Hosting+',450,1,'FULL');
INSERT INTO servicesweb VALUES(3,'WebforYou',1500,1,'OK');
INSERT INTO servicesweb VALUES(4,'Care+',550,1,'NA');


INSERT INTO ordersweb VALUES(&id_order,'&id_services',&id_discount);


ALTER TABLE customersweb MODIFY (EMAIL_CUSTOMER VARCHAR(40));

INSERT INTO customersweb VALUES(91,'Ion','Popa','Grivita','ion@gmail.com',0721615122,TO_DATE('17/12/2015', 'DD/MM/YYYY'),42);
INSERT INTO customersweb VALUES(92,'Doru','Lungoci','Dorobanti','doru@gmail.com',0721615122,TO_DATE('02/12/2016', 'DD/MM/YYYY'),51);
INSERT INTO customersweb VALUES(93,'Mihai','Dabuleanu','Unirii','mihai@gmail.com',0721615122,TO_DATE('30/10/2017', 'DD/MM/YYYY'),71);
INSERT INTO customersweb VALUES(94,'Ionel','Petrisor','Basarab','ionel@gmail.com',0721615122,TO_DATE('21/4/2017', 'DD/MM/YYYY'),58);


SELECT email_customer,tel_customer,reg_date FROM customersweb;
SELECT * FROM ordersweb;
SELECT id_emp, first_name, tel_number, id_department, final_salary, salary_id, salary_val FROM employeesweb, salary;

SELECT employeesweb.id_emp,employeesweb.first_name,departmentsweb.department_name, employeesweb.id_department FROM employeesweb INNER JOIN departmentsweb ON employeesweb.id_department=departmentsweb.ID_DEPARTMENT;



SELECT * FROM employeesweb;
SELECT * FROM customersweb;
SELECT * FROM bonus;
SELECT * FROM salary;
SELECT * FROM total_salary;
SELECT * FROM discountweb;
SELECT * FROM servicesweb;
SELECT * FROM ordersweb;
SELECT * FROM departmentsweb;