daily pastebin goal
25%
SHARE
TWEET

Untitled

a guest May 23rd, 2018 180 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --CREATE TABLE EMP_IMares AS SELECT * FROM employees;
  2. --CREATE TABLE DEPT_IMares AS SELECT * FROM departments;
  3.  
  4. ALTER TABLE EMP_IMares
  5. ADD CONSTRAINT pk_emp_IMares PRIMARY KEY(employee_id);
  6.  
  7. ALTER TABLE DEPT_IMares
  8. ADD CONSTRAINT pk_dept_IMares PRIMARY KEY(department_id);
  9.  
  10. ALTER TABLE emp_IMares
  11. ADD CONSTRAINT fk_emp_dept_IMares
  12.   Foreign key(department_id) REFERENCES dept_IMares(department_id);
  13.  
  14. ALTER TABLE EMP_IMares
  15. ADD CONSTRAINT emp_sef_ang_IMares
  16.   foreign key(manager_id) references emp_IMares(employee_id);
  17.  
  18. ALTER TABLE DEPT_IMares
  19. ADD CONSTRAINT dept_IMares_sef_dept_IMares
  20.   FOREIGN KEY(manager_id) REFERENCES emp_IMares(employee_id);
  21.  
  22. INSERT INTO DEPT_IMares (department_id, department_name)
  23. VALUES (300,'Programare');
  24.  
  25. COMMIT;
  26.  
  27. INSERT INTO EMP_IMares VALUES(600,NULL,'Popa','popa@yahoo.com',NULL,to_date('23.03.2018','dd mm yyyy'),'IT_PROG',NULL,NULL,NULL,300);
  28.  
  29. INSERT INTO EMP_IMares (employee_id,last_name,email,hire_date,job_id,department_id)
  30. VALUES(601,'Popa','popa@yahoo.com',to_date('23.03.2018','dd mm yyyy'),'IT_PROG',300);
  31.  
  32. COMMIT;
  33.  
  34. INSERT INTO (SELECT employee_id,last_name,email,hire_date,job_id,department_id FROM emp_imares)
  35. VALUES(602,'Al 3 lea angajat','ang3rd@yahoo.com',to_date('23.03.2018','dd mm yyyy'),'IT_PROG',300);
  36.  
  37. INSERT INTO emp_imares (employee_id,last_name,email,hire_date,job_id,department_id)
  38. VALUES((select max(employee_id)+1 from emp_imares),'Al 4 lea angajat','ang4th@yahoo.com',to_date('23.03.2018','dd mm yyyy'),'IT_PROG',300);
  39.  
  40. COMMIT;
  41.  
  42. CREATE TABLE emp1_imares AS SELECT * FROM employees WHERE salary<0;
  43. commit;
  44. ALTER TABLE EMP1_IMares
  45. ADD CONSTRAINT pk_emp1_IMares PRIMARY KEY(employee_id);
  46.  
  47. INSERT INTO EMP1_IMares
  48. SELECT *
  49. FROM employees
  50. WHERE (commission_pct*salary) > salary/4  ;
  51.  
  52. COMMIT;
  53. --
  54.  
  55. SELECT *
  56. from employees join departments using(department_id);
  57.  
  58. INSERT INTO (SELECT employee_id,last_name,email,hire_date,job_id,department_id FROM emp_imares)
  59. VALUES(604,'Al 5 lea angajat','ang3rd@yahoo.com',to_date('23.03.2018','dd mm yyyy'),'IT_PROG',500);
  60.  
  61. -- trebuie respectata constrangerea! (nu se poate adauga angajat cu department_id nou)
  62.  
  63. --@script
  64.  
  65. INSERT INTO emp1_IMares(employee_id,first_name,last_name,hire_date,job_id,email)
  66. VALUES(&cod,'&&prenume','&&nume',SYSDATE,'&job',substr(&prenume,1,7)||substr(&nume,1,7));
  67. undefine nume;
  68. undefine prenume;
  69.  
  70. DELETE FROM emp1_IMares;
  71. COMMIT;
  72.  
  73. CREATE TABLE emp3_IMares
  74. as select *
  75. FROM employees
  76. WHERE 1=2;
  77.  
  78. commit;
  79.  
  80. --first/all
  81. INSERT all
  82. WHEN department_id = 80 THEN INTO emp0_IMares
  83. WHEN salary<5000 THEN INTO emp1_IMares
  84. WHEN salary BETWEEN 5000 AND 10000 THEN INTO emp2_IMares
  85. WHEN salary>10000 THEN INTO emp3_IMares
  86. SELECT * FROM employees;
  87.  
  88. COMMIT;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top