iannis123

Untitled

May 27th, 2018
97
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.36 KB | None | 0 0
  1. 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) );
  2. 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);
  3.  
  4.  
  5. ALTER TABLE employeeskit ADD CONSTRAINT id_departmentt FOREIGN KEY (id_department) REFERENCES departmentsweb(id_department);
  6. ALTER TABLE employeeskit ADD salary NUMBER(6);
  7. ALTER TABLE ordersweb ADD TOTAL_VALUE NUMBER(6);
  8.  
  9. SET SERVEROUTPUT ON
  10. DECLARE
  11. exavg_price EXCEPTION;
  12. v_id_order NUMBER;
  13. v_avg_price NUMBER;
  14. v_pret NUMBER;
  15. CURSOR curs IS SELECT id_order, ROUND(AVG(total_value)), total_value
  16. FROM ordersweb
  17. GROUP BY id_order, total_value HAVING ROUND(AVG(total_value)) > 0;
  18. BEGIN
  19. OPEN curs;
  20. LOOP
  21. FETCH curs into v_id_order, v_avg_price, v_pret;
  22. BEGIN
  23. IF v_avg_price > 450 THEN
  24. DBMS_OUTPUT.PUT_LINE('For the order id ' || v_id_order ||' the avg price is ' || v_avg_price);
  25. ELSE
  26. RAISE exavg_price;
  27. END IF;
  28. EXCEPTION WHEN exavg_price;
  29. THEN IF v_pret < 450
  30. THEN DBMS_OUTPUT.PUT_LINE('For the order ' v_id_order ' the income is smaller than 50);
  31. END IF;
  32. END IF;
  33. END;
  34. EXIT WHEN curs%NOTFOUND;
  35. END LOOP;
  36. END;
  37.  
  38.  
  39. INSERT INTO employeeskit VALUES(2,12 ,'Iannis','Paris',0721615778,'Ana Ipatescu 12',6000);
  40. INSERT INTO employeeskit VALUES(3,13,'Bogdan','Niculae',0721615771,'Calea Grivitei 81',3450);
  41. INSERT INTO employeeskit VALUES(5,11,'Gina','Enache',0721615775,'Drumul Taberei 72',2200);
  42. INSERT INTO employeeskit VALUES(4,10,'Emil','Gheorghe',0721615777,'Dorobantilor 22',1890);
  43.  
  44. desc employeeskit;
  45. select * from employeeskit;
  46. select * from departmentsweb;
  47. select * from customersweb;
  48.  
  49. ALTER TABLE customersweb ADD CONSTRAINT id_order FOREIGN KEY (id_order) REFERENCES ordersweb(id_order);
  50.  
  51. 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);
  52. ALTER TABLE departmentsweb MODIFY (department_location VARCHAR2(20), department_email VARCHAR2(30), department_name VARCHAR2(30));
  53. ALTER TABLE departmentsweb MODIFY (DEPARTMENT_WORKHOURS_S VARCHAR(20),DEPARTMENT_WORKHOURS_f VARCHAR(20));
  54. ALTER TABLE employeeskit ADD CONSTRAINT id_department FOREIGN KEY (id_department) REFERENCES departmentsweb(id_department);
  55. SELECT * FROM departmentsweb;
  56. SELECT * FROM employeeskit;
  57. INSERT INTO departmentsweb VALUES (10,'HR','Victoriei','hr@gmail.com',0721615779,'Emil Gheorghe','10:30:00','16:30:00');
  58. INSERT INTO departmentsweb VALUES (11,'PR','Victoriei','pr@gmail.com',0721615779,'Gina Enache','10:30:00','16:30:00');
  59. INSERT INTO departmentsweb VALUES (12,'ITfront','Victoriei','itfront@gmail.com',0721615779,'Paris Iannis','10:30:00','16:30:00');
  60. INSERT INTO departmentsweb VALUES (13,'ITback','Victoriei','itback@gmail.com',0721615779,'Niculae Bogdan','10:30:00','16:30:00');
  61.  
  62.  
  63. INSERT INTO customersweb VALUES(91,88,'Ion','Popa','Grivita','ion@gmail.com',0721615122,TO_DATE('17/12/2015', 'DD/MM/YYYY'));
  64. INSERT INTO customersweb VALUES(92,66,'Doru','Lungoci','Dorobanti','doru@gmail.com',0721615122,TO_DATE('02/12/2016', 'DD/MM/YYYY'));
  65. INSERT INTO customersweb VALUES(93,77,'Mihai','Dabuleanu','Unirii','mihai@gmail.com',0721615122,TO_DATE('30/10/2017', 'DD/MM/YYYY'));
  66. INSERT INTO customersweb VALUES(94,55,'Ionel','Petrisor','Basarab','ionel@gmail.com',0721615122,TO_DATE('21/4/2017', 'DD/MM/YYYY'));
  67.  
  68. CREATE TABLE discountweb (id_discount NUMBER(2,0) PRIMARY KEY, discount_description VARCHAR2(30), discount_percent NUMBER(2,2));
  69. 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));
  70.  
  71. 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));
  72. select * from customersweb;
  73. ALTER TABLE ordersweb ADD id_emp NUMBER(2);
  74. ALTER TABLE ordersweb ADD id_customer NUMBER(2);
  75. ALTER TABLE ordersweb ADD firm_name NUMBER(2);
  76. ALTER TABLE ordersweb modify firm_name VARCHAR2(20);
  77. ALTER TABLE ordersweb ADD CONSTRAINT id_emp FOREIGN KEY (id_emp) REFERENCES employeeskit(id_emp);
  78. ALTER TABLE ordersweb ADD CONSTRAINT id_customer FOREIGN KEY (id_customer) REFERENCES customersweb(id_customer);
  79.  
  80. select * from ordersweb;
  81. select * from employeeskit;
  82. select * from customersweb;
  83.  
  84. INSERT INTO ordersweb VALUES(55,3,44,2);
  85. INSERT INTO ordersweb VALUES(66,2,41,3);
  86. INSERT INTO ordersweb VALUES(77,4,42,4);
  87. INSERT INTO ordersweb VALUES(88,1,43,5);
  88.  
  89. INSERT INTO ordersweb VALUES(12,3,41,2,91,'Dores');
  90. INSERT INTO ordersweb VALUES(34,2,41,3,92,'Petrochim');
  91. INSERT INTO ordersweb VALUES(56,4,41,4,94,'Hidroelectrica');
  92. INSERT INTO ordersweb VALUES(78,1,43,5,93,'Petrom');
  93.  
  94.  
  95. SELECT * FROM ordersweb;
  96.  
  97. ALTER TABLE discountweb MODIFY (discount_description VARCHAR2(30), DISCOUNT_PERCENT NUMBER(2));
  98.  
  99. INSERT INTO discountweb(id_discount, discount_description, discount_percent) VALUES(41,'Christmas sales',10);
  100. INSERT INTO discountweb(id_discount, discount_description, discount_percent) VALUES(42,'Winter sales',15);
  101. INSERT INTO discountweb(id_discount, discount_description, discount_percent) VALUES(43,'Easter sales',10);
  102. INSERT INTO discountweb(id_discount, discount_description, discount_percent) VALUES(44,'BlackFriday sales',35);
  103.  
  104. INSERT INTO servicesweb VALUES(1,'Hosting',150,1,'OK');
  105. INSERT INTO servicesweb VALUES(2,'Hosting+',450,1,'FULL');
  106. INSERT INTO servicesweb VALUES(3,'WebforYou',1500,1,'OK');
  107. INSERT INTO servicesweb VALUES(4,'Care+',550,1,'NA');
  108.  
  109. select * from discountweb;
  110. select * from servicesweb;
  111. select * from ordersweb;
  112.  
  113. desc ordersweb;
  114. desc employeeskit;
  115. desc customersweb;
  116. desc discountweb;
  117. desc servicesweb;
  118.  
  119. INSERT INTO employeeskit VALUES(5,12 ,'Iannis','Paris',0721615778,'Ana Ipatescu 12',6000);
  120. INSERT INTO employeeskit VALUES(6,13,'Bolloc','Niculae',0721615771,'Calea Grivitei 81',3450);
  121. INSERT INTO employeeskit VALUES(7,11,'Eduard','Enache',0721615775,'Drumul Taberei 72',2200);
  122. INSERT INTO employeeskit VALUES(8,10,'Vlad','Gheorghe',0721615777,'Dorobantilor 22',1890);
  123.  
  124. select * from ordersweb;
  125. select * from employeeskit;
  126. desc employeeskit;
  127.  
  128.  
  129. SET SERVEROUTPUT ON
  130. DECLARE
  131. v_id_order NUMBER;
  132. v_firm VARCHAR2(30);
  133. v_id_emp NUMBER;
  134. BEGIN
  135. SELECT id_order, firm_name INTO v_id_order, v_firm FROM ordersweb WHERE firm_name LIKE '%chim%';
  136. DBMS_OUTPUT.PUT_LINE('Firm ' || v_firm || ' order ' || v_id_order);
  137. EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('not found');
  138. WHEN TOO_MANY_ROWS THEN
  139. SELECT id_order, firm_name, id_emp INTO v_id_order, v_firm, v_id_emp
  140. FROM ordersweb WHERE firm_name LIKE '%chim%';
  141. DBMS_OUTPUT.PUT_LINE('Firm ' || v_firm || ' order ' || v_id_order);
  142. END;
  143. DECLARE
  144. v_order_id NUMBER := 55;
  145. BEGIN
  146. UPDATE ordersweb
  147. SET id_discount = 43
  148. WHERE id_order = v_order_id;
  149. DBMS_OUTPUT.PUT_LINE ('Numarul de inregistrari modificate este: ' || SQL%ROWCOUNT );
  150. IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('not found!');
  151. END;
  152.  
  153. SELECT * FROM ordersweb;
  154. select * from customersweb;
  155. select * from servicesweb;
  156.  
  157. SELECT s.service_price, c.customer_fname,
  158.  
  159. INSERT INTO ordersweb VALUES(34,3,41,2,91,'Dores');
  160. INSERT INTO ordersweb VALUES(35,3,41,2,91,'Dores');
  161. INSERT INTO ordersweb VALUES(36,3,41,2,91,'Dores');
  162. INSERT INTO ordersweb VALUES(37,3,41,2,91,'Dores');
  163. INSERT INTO ordersweb VALUES(38,3,41,2,91,'Dores');
  164.  
  165. INSERT INTO ordersweb VALUES(39,2,41,2,91,'Dores');
  166. INSERT INTO ordersweb VALUES(40,2,41,2,91,'Dores');
  167. INSERT INTO ordersweb VALUES(41,2,41,2,91,'Dores');
  168. INSERT INTO ordersweb VALUES(42,2,41,2,91,'Dores');
  169. INSERT INTO ordersweb VALUES(43,2,41,2,91,'Dores');
  170.  
  171. INSERT INTO ordersweb VALUES(44,2,41,2,91,'Dores',550);
  172. INSERT INTO ordersweb VALUES(45,2,41,2,91,'Dores',550);
  173. INSERT INTO ordersweb VALUES(46,2,41,2,91,'Dores',550);
  174. INSERT INTO ordersweb VALUES(47,2,41,2,91,'Dores',550);
  175. INSERT INTO ordersweb VALUES(48,2,41,2,91,'Dores',550);
  176.  
  177.  
  178. INSERT INTO ordersweb VALUES(49,2,41,2,91,'Dores',350);
  179. INSERT INTO ordersweb VALUES(50,2,41,2,91,'Dores',250);
  180. INSERT INTO ordersweb VALUES(51,2,41,2,91,'Dores',150);
  181. INSERT INTO ordersweb VALUES(52,2,41,2,91,'Dores',850);
  182. INSERT INTO ordersweb VALUES(53,2,41,2,91,'Dores',950);
  183. SELECT * FROM ordersweb;
  184.  
  185. SET SERVEROUTPUT ON
  186. DECLARE
  187. exceptie_pret EXCEPTION;
  188. v_id_order NUMBER;
  189. v_nume VARCHAR2(30);
  190. v_pret NUMBER;
  191. CURSOR curs IS SELECT o.id_order, c.customer_fname,
  192. s.service_price FROM customersweb c, ordersweb o, servicesweb s
  193. WHERE c.id_customer = o.id_customer;
  194. BEGIN
  195. OPEN curs;
  196. LOOP
  197. FETCH curs INTO v_id_order, v_nume, v_pret;
  198. BEGIN
  199. IF v_pret < 550
  200. THEN
  201. DBMS_OUTPUT.PUT_LINE('Id '||v_id_order || ' legal person name '|| v_nume ||' valoare serviciu '||v_pret);
  202. ELSE
  203. RAISE exceptie_pret;
  204. END IF;
  205. EXCEPTION WHEN exceptie_pret THEN
  206. DBMS_OUTPUT.PUT_LINE('Legal person name ' || v_nume || ' value is too high' );
  207. END;
  208. EXIT WHEN curs%NOTFOUND;
  209. END LOOP;
  210. END;
  211.  
  212. SELECT * FROM ordersweb;
  213.  
  214. SET SERVEROUTPUT ON
  215. DECLARE
  216. exavg_price EXCEPTION;
  217. v_id_order NUMBER;
  218. v_avg_price NUMBER;
  219. v_pret NUMBER;
  220. CURSOR curs IS SELECT id_order, ROUND(AVG(total_value)), total_value
  221. FROM ordersweb
  222. GROUP BY id_order, total_value HAVING ROUND(AVG(total_value)) > 0;
  223. BEGIN
  224. OPEN curs;
  225. LOOP
  226. FETCH curs into v_id_order, v_avg_price, v_pret;
  227. BEGIN
  228. IF v_avg_price > 450 THEN
  229. DBMS_OUTPUT.PUT_LINE('For the order id ' || v_id_order ||' the avg price is ' || v_avg_price);
  230. ELSE
  231. RAISE exavg_price;
  232. END IF;
  233. EXCEPTION WHEN exavg_price;
  234. THEN IF v_pret < 450
  235. THEN DBMS_OUTPUT.PUT_LINE('For the order ' v_id_orderd ' the income is smaller than 50);
  236. END IF;
  237. END IF;
  238. END;
  239. EXIT WHEN curs%NOTFOUND;
  240. END LOOP;
  241. END;
  242.  
  243. SET SERVEROUTPUT ON
  244. DECLARE
  245. exavg_price EXCEPTION;
  246. v_id_order NUMBER;
  247. v_avg_price NUMBER;
  248. v_pret NUMBER;
  249. CURSOR curs IS SELECT id_order, ROUND(AVG(total_value)), total_value
  250. FROM ordersweb
  251. GROUP BY id_order, total_value HAVING ROUND(AVG(total_value)) > 0;
  252. BEGIN
  253. OPEN curs;
  254. LOOP
  255. FETCH curs into v_id_order, v_avg_price, v_pret;
  256. BEGIN
  257. IF v_avg_price > 450 THEN
  258. DBMS_OUTPUT.PUT_LINE('For the order id ' || v_id_order ||' the avg price is ' || v_avg_price);
  259. ELSE
  260. RAISE exavg_price;
  261. END IF;
  262. EXCEPTION WHEN exavg_price;
  263. THEN IF v_pret < 450
  264. THEN DBMS_OUTPUT.PUT_LINE('For the order ' v_id_order ' the income is smaller than 50);
  265. END IF;
  266. END IF;
  267. END;
  268. EXIT WHEN curs%NOTFOUND;
  269. END LOOP;
  270. END;
  271.  
  272.  
  273.  
  274.  
  275. sadadsasdddasdasd
  276. SET SERVEROUTPUT ON
  277. DECLARE
  278. exavg_price EXCEPTION;
  279. v_id_order NUMBER;
  280. v_avg_price NUMBER;
  281. v_pret NUMBER;
  282. CURSOR curs IS SELECT id_order, ROUND(AVG(total_value)), total_value
  283. FROM ordersweb
  284. GROUP BY id_order, total_value HAVING ROUND(AVG(total_value)) > 0;
  285. BEGIN
  286. OPEN curs;
  287. LOOP
  288. FETCH curs into v_id_order, v_avg_price, v_pret;
  289. BEGIN
  290. IF v_avg_price > 450 THEN
  291. DBMS_OUTPUT.PUT_LINE('For the order id ' || v_id_order ||' the avg price is ' || v_avg_price);
  292. ELSE
  293. RAISE exavg_price;
  294. END IF;
  295. EXCEPTION WHEN exavg_price;
  296. THEN IF v_pret < 450
  297. THEN DBMS_OUTPUT.PUT_LINE('For the order ' v_id_orderd ' the income is smaller than 50);
  298. END IF;
  299. END IF;
  300. END;
  301. EXIT WHEN curs%NOTFOUND;
  302. END LOOP;
  303. END;
Add Comment
Please, Sign In to add comment