Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE employeeskit ( id_emp NUMBER(5,0) PRIMARY KEY, id_department NUMBER(2,0),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,id_order NUMBER(5,0) ,customer_fname VARCHAR(30), customer_lname VARCHAR(30), adress_customer VARCHAR(30), email_customer VARCHAR(30), tel_customer NUMBER(10,0), reg_date DATE);
- ALTER TABLE employeeskit ADD CONSTRAINT id_departmentt FOREIGN KEY (id_department) REFERENCES departmentsweb(id_department);
- ALTER TABLE employeeskit ADD salary NUMBER(6);
- ALTER TABLE ordersweb ADD TOTAL_VALUE NUMBER(6);
- SET SERVEROUTPUT ON
- DECLARE
- exavg_price EXCEPTION;
- v_id_order NUMBER;
- v_avg_price NUMBER;
- v_pret NUMBER;
- CURSOR curs IS SELECT id_order, ROUND(AVG(total_value)), total_value
- FROM ordersweb
- GROUP BY id_order, total_value HAVING ROUND(AVG(total_value)) > 0;
- BEGIN
- OPEN curs;
- LOOP
- FETCH curs into v_id_order, v_avg_price, v_pret;
- BEGIN
- IF v_avg_price > 450 THEN
- DBMS_OUTPUT.PUT_LINE('For the order id ' || v_id_order ||' the avg price is ' || v_avg_price);
- ELSE
- RAISE exavg_price;
- END IF;
- EXCEPTION WHEN exavg_price;
- THEN IF v_pret < 450
- THEN DBMS_OUTPUT.PUT_LINE('For the order ' v_id_order ' the income is smaller than 50);
- END IF;
- END IF;
- END;
- EXIT WHEN curs%NOTFOUND;
- END LOOP;
- END;
- INSERT INTO employeeskit VALUES(2,12 ,'Iannis','Paris',0721615778,'Ana Ipatescu 12',6000);
- INSERT INTO employeeskit VALUES(3,13,'Bogdan','Niculae',0721615771,'Calea Grivitei 81',3450);
- INSERT INTO employeeskit VALUES(5,11,'Gina','Enache',0721615775,'Drumul Taberei 72',2200);
- INSERT INTO employeeskit VALUES(4,10,'Emil','Gheorghe',0721615777,'Dorobantilor 22',1890);
- desc employeeskit;
- select * from employeeskit;
- select * from departmentsweb;
- select * from customersweb;
- ALTER TABLE customersweb ADD CONSTRAINT id_order FOREIGN KEY (id_order) REFERENCES ordersweb(id_order);
- 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 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));
- ALTER TABLE employeeskit ADD CONSTRAINT id_department FOREIGN KEY (id_department) REFERENCES departmentsweb(id_department);
- SELECT * FROM departmentsweb;
- SELECT * FROM employeeskit;
- 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');
- INSERT INTO customersweb VALUES(91,88,'Ion','Popa','Grivita','ion@gmail.com',0721615122,TO_DATE('17/12/2015', 'DD/MM/YYYY'));
- INSERT INTO customersweb VALUES(92,66,'Doru','Lungoci','Dorobanti','doru@gmail.com',0721615122,TO_DATE('02/12/2016', 'DD/MM/YYYY'));
- INSERT INTO customersweb VALUES(93,77,'Mihai','Dabuleanu','Unirii','mihai@gmail.com',0721615122,TO_DATE('30/10/2017', 'DD/MM/YYYY'));
- INSERT INTO customersweb VALUES(94,55,'Ionel','Petrisor','Basarab','ionel@gmail.com',0721615122,TO_DATE('21/4/2017', 'DD/MM/YYYY'));
- CREATE TABLE discountweb (id_discount NUMBER(2,0) PRIMARY KEY, discount_description VARCHAR2(30), discount_percent NUMBER(2,2));
- CREATE TABLE servicesweb (id_services NUMBER(2,0) PRIMARY KEY, service_name VARCHAR2(30), service_price NUMBER(10,0), service_quantity NUMBER(3,0), service_status VARCHAR2(20));
- 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));
- select * from customersweb;
- ALTER TABLE ordersweb ADD id_emp NUMBER(2);
- ALTER TABLE ordersweb ADD id_customer NUMBER(2);
- ALTER TABLE ordersweb ADD firm_name NUMBER(2);
- ALTER TABLE ordersweb modify firm_name VARCHAR2(20);
- ALTER TABLE ordersweb ADD CONSTRAINT id_emp FOREIGN KEY (id_emp) REFERENCES employeeskit(id_emp);
- ALTER TABLE ordersweb ADD CONSTRAINT id_customer FOREIGN KEY (id_customer) REFERENCES customersweb(id_customer);
- select * from ordersweb;
- select * from employeeskit;
- select * from customersweb;
- INSERT INTO ordersweb VALUES(55,3,44,2);
- INSERT INTO ordersweb VALUES(66,2,41,3);
- INSERT INTO ordersweb VALUES(77,4,42,4);
- INSERT INTO ordersweb VALUES(88,1,43,5);
- INSERT INTO ordersweb VALUES(12,3,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(34,2,41,3,92,'Petrochim');
- INSERT INTO ordersweb VALUES(56,4,41,4,94,'Hidroelectrica');
- INSERT INTO ordersweb VALUES(78,1,43,5,93,'Petrom');
- SELECT * FROM ordersweb;
- ALTER TABLE discountweb MODIFY (discount_description VARCHAR2(30), DISCOUNT_PERCENT NUMBER(2));
- INSERT INTO discountweb(id_discount, discount_description, discount_percent) VALUES(41,'Christmas sales',10);
- INSERT INTO discountweb(id_discount, discount_description, discount_percent) VALUES(42,'Winter sales',15);
- INSERT INTO discountweb(id_discount, discount_description, discount_percent) VALUES(43,'Easter sales',10);
- INSERT INTO discountweb(id_discount, discount_description, discount_percent) 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');
- select * from discountweb;
- select * from servicesweb;
- select * from ordersweb;
- desc ordersweb;
- desc employeeskit;
- desc customersweb;
- desc discountweb;
- desc servicesweb;
- INSERT INTO employeeskit VALUES(5,12 ,'Iannis','Paris',0721615778,'Ana Ipatescu 12',6000);
- INSERT INTO employeeskit VALUES(6,13,'Bolloc','Niculae',0721615771,'Calea Grivitei 81',3450);
- INSERT INTO employeeskit VALUES(7,11,'Eduard','Enache',0721615775,'Drumul Taberei 72',2200);
- INSERT INTO employeeskit VALUES(8,10,'Vlad','Gheorghe',0721615777,'Dorobantilor 22',1890);
- select * from ordersweb;
- select * from employeeskit;
- desc employeeskit;
- SET SERVEROUTPUT ON
- DECLARE
- v_id_order NUMBER;
- v_firm VARCHAR2(30);
- v_id_emp NUMBER;
- BEGIN
- SELECT id_order, firm_name INTO v_id_order, v_firm FROM ordersweb WHERE firm_name LIKE '%chim%';
- DBMS_OUTPUT.PUT_LINE('Firm ' || v_firm || ' order ' || v_id_order);
- EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('not found');
- WHEN TOO_MANY_ROWS THEN
- SELECT id_order, firm_name, id_emp INTO v_id_order, v_firm, v_id_emp
- FROM ordersweb WHERE firm_name LIKE '%chim%';
- DBMS_OUTPUT.PUT_LINE('Firm ' || v_firm || ' order ' || v_id_order);
- END;
- DECLARE
- v_order_id NUMBER := 55;
- BEGIN
- UPDATE ordersweb
- SET id_discount = 43
- WHERE id_order = v_order_id;
- DBMS_OUTPUT.PUT_LINE ('Numarul de inregistrari modificate este: ' || SQL%ROWCOUNT );
- IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('not found!');
- END;
- SELECT * FROM ordersweb;
- select * from customersweb;
- select * from servicesweb;
- SELECT s.service_price, c.customer_fname,
- INSERT INTO ordersweb VALUES(34,3,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(35,3,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(36,3,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(37,3,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(38,3,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(39,2,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(40,2,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(41,2,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(42,2,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(43,2,41,2,91,'Dores');
- INSERT INTO ordersweb VALUES(44,2,41,2,91,'Dores',550);
- INSERT INTO ordersweb VALUES(45,2,41,2,91,'Dores',550);
- INSERT INTO ordersweb VALUES(46,2,41,2,91,'Dores',550);
- INSERT INTO ordersweb VALUES(47,2,41,2,91,'Dores',550);
- INSERT INTO ordersweb VALUES(48,2,41,2,91,'Dores',550);
- INSERT INTO ordersweb VALUES(49,2,41,2,91,'Dores',350);
- INSERT INTO ordersweb VALUES(50,2,41,2,91,'Dores',250);
- INSERT INTO ordersweb VALUES(51,2,41,2,91,'Dores',150);
- INSERT INTO ordersweb VALUES(52,2,41,2,91,'Dores',850);
- INSERT INTO ordersweb VALUES(53,2,41,2,91,'Dores',950);
- SELECT * FROM ordersweb;
- SET SERVEROUTPUT ON
- DECLARE
- exceptie_pret EXCEPTION;
- v_id_order NUMBER;
- v_nume VARCHAR2(30);
- v_pret NUMBER;
- CURSOR curs IS SELECT o.id_order, c.customer_fname,
- s.service_price FROM customersweb c, ordersweb o, servicesweb s
- WHERE c.id_customer = o.id_customer;
- BEGIN
- OPEN curs;
- LOOP
- FETCH curs INTO v_id_order, v_nume, v_pret;
- BEGIN
- IF v_pret < 550
- THEN
- DBMS_OUTPUT.PUT_LINE('Id '||v_id_order || ' legal person name '|| v_nume ||' valoare serviciu '||v_pret);
- ELSE
- RAISE exceptie_pret;
- END IF;
- EXCEPTION WHEN exceptie_pret THEN
- DBMS_OUTPUT.PUT_LINE('Legal person name ' || v_nume || ' value is too high' );
- END;
- EXIT WHEN curs%NOTFOUND;
- END LOOP;
- END;
- SELECT * FROM ordersweb;
- SET SERVEROUTPUT ON
- DECLARE
- exavg_price EXCEPTION;
- v_id_order NUMBER;
- v_avg_price NUMBER;
- v_pret NUMBER;
- CURSOR curs IS SELECT id_order, ROUND(AVG(total_value)), total_value
- FROM ordersweb
- GROUP BY id_order, total_value HAVING ROUND(AVG(total_value)) > 0;
- BEGIN
- OPEN curs;
- LOOP
- FETCH curs into v_id_order, v_avg_price, v_pret;
- BEGIN
- IF v_avg_price > 450 THEN
- DBMS_OUTPUT.PUT_LINE('For the order id ' || v_id_order ||' the avg price is ' || v_avg_price);
- ELSE
- RAISE exavg_price;
- END IF;
- EXCEPTION WHEN exavg_price;
- THEN IF v_pret < 450
- THEN DBMS_OUTPUT.PUT_LINE('For the order ' v_id_orderd ' the income is smaller than 50);
- END IF;
- END IF;
- END;
- EXIT WHEN curs%NOTFOUND;
- END LOOP;
- END;
- SET SERVEROUTPUT ON
- DECLARE
- exavg_price EXCEPTION;
- v_id_order NUMBER;
- v_avg_price NUMBER;
- v_pret NUMBER;
- CURSOR curs IS SELECT id_order, ROUND(AVG(total_value)), total_value
- FROM ordersweb
- GROUP BY id_order, total_value HAVING ROUND(AVG(total_value)) > 0;
- BEGIN
- OPEN curs;
- LOOP
- FETCH curs into v_id_order, v_avg_price, v_pret;
- BEGIN
- IF v_avg_price > 450 THEN
- DBMS_OUTPUT.PUT_LINE('For the order id ' || v_id_order ||' the avg price is ' || v_avg_price);
- ELSE
- RAISE exavg_price;
- END IF;
- EXCEPTION WHEN exavg_price;
- THEN IF v_pret < 450
- THEN DBMS_OUTPUT.PUT_LINE('For the order ' v_id_order ' the income is smaller than 50);
- END IF;
- END IF;
- END;
- EXIT WHEN curs%NOTFOUND;
- END LOOP;
- END;
- sadadsasdddasdasd
- SET SERVEROUTPUT ON
- DECLARE
- exavg_price EXCEPTION;
- v_id_order NUMBER;
- v_avg_price NUMBER;
- v_pret NUMBER;
- CURSOR curs IS SELECT id_order, ROUND(AVG(total_value)), total_value
- FROM ordersweb
- GROUP BY id_order, total_value HAVING ROUND(AVG(total_value)) > 0;
- BEGIN
- OPEN curs;
- LOOP
- FETCH curs into v_id_order, v_avg_price, v_pret;
- BEGIN
- IF v_avg_price > 450 THEN
- DBMS_OUTPUT.PUT_LINE('For the order id ' || v_id_order ||' the avg price is ' || v_avg_price);
- ELSE
- RAISE exavg_price;
- END IF;
- EXCEPTION WHEN exavg_price;
- THEN IF v_pret < 450
- THEN DBMS_OUTPUT.PUT_LINE('For the order ' v_id_orderd ' the income is smaller than 50);
- END IF;
- END IF;
- END;
- EXIT WHEN curs%NOTFOUND;
- END LOOP;
- END;
Add Comment
Please, Sign In to add comment