Advertisement
Guest User

Untitled

a guest
Nov 20th, 2019
105
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.35 KB | None | 0 0
  1. --BEGIN RegenerateTables; End;
  2.  
  3. --1.1
  4. CREATE TABLE workers(
  5. W_ID NUMBER(3) PRIMARY KEY,
  6. FIRST_NAME VARCHAR2(30) NOT NULL,
  7. LAST_NAME VARCHAR2(30) NOT NULL,
  8. SALARY NUMBER(7,2) CONSTRAINT SALARY_N NOT NULL,
  9. UNIQUE(FIRST_NAME, LAST_NAME));
  10.  
  11. --1.2
  12. ALTER TABLE WORKERS
  13. DROP UNIQUE(FIRST_NAME, LAST_NAME);
  14.  
  15. --1.3
  16. ALTER TABLE workers
  17. ADD CONSTRAINT W_LAST_NAME_CK CHECK (LENGTH(LAST_NAME)>2);
  18.  
  19. insert into workers
  20. values (10, 'HUANG', 'LI', 4000);
  21.  
  22. --1.4
  23. alter table workers
  24. drop constraint salary_n;
  25.  
  26. --1.5
  27. alter table workers
  28. add BOSS_ID NUMBER(3)
  29. constraint boss_id_fk
  30. references workers(w_id);
  31.  
  32. SELECT * FROM workers;
  33.  
  34. --1.6
  35. INSERT INTO workers
  36. values (10, 'Jan', 'Kowalski', '4500', null);
  37. INSERT INTO workers
  38. values (20, 'Jacek', 'Malinowski', '3000', 10);
  39.  
  40. --1.7
  41. DELETE FROM workers
  42. WHERE w_id = 10;
  43.  
  44. --1.8
  45. ALTER TABLE workers
  46. DISABLE CONSTRAINT BOSS_ID_FK;
  47.  
  48. --1.9
  49. ALTER TABLE workers
  50. ENABLE CONSTRAINT boss_id_fk;
  51.  
  52. --1.10
  53. INSERT into workers
  54. values (10, 'Zbigniew', 'Malicki', 5000, null);
  55.  
  56. --1.11
  57. delete from workers
  58. where w_id=10;
  59.  
  60. --1.12
  61. alter table workers
  62. drop constraint boss_id_fk;
  63.  
  64. --1.13
  65. alter table workers
  66. add constraint boss_id_fk_c
  67. FOREIGN KEY (boss_id)
  68. REFERENCES workers(w_id)
  69. ON DELETE CASCADE;
  70.  
  71. --1.14
  72. select * from workers;
  73.  
  74. delete from workers
  75. where w_id = 10;
  76.  
  77. --1.15
  78. drop table workers;
  79.  
  80. --------------------------------------------------
  81.  
  82. --2.1
  83. create or replace view V_EMPLOYEES as
  84. select emp_id, last_name "EMP_NAME", dep_id
  85. from employees;
  86.  
  87. --2.2
  88. DESCRIBE V_EMPLOYEES;
  89. SELECT * FROM V_EMPLOYEES;
  90.  
  91. --2.3
  92. SELECT emp_name FROM V_EMPLOYEES
  93. WHERE dep_id = 30;
  94.  
  95. --2.4
  96. UPDATE v_employees
  97. SET dep_id = 20
  98. WHERE emp_name = 'Davis';
  99.  
  100. COMMIT;
  101.  
  102. --2.5
  103. create view v_emp_dep_40
  104. ("EMP_NO", "EMPLOYEE", "DEP_NO")
  105. AS SELECT * FROM V_EMPLOYEES
  106. where DEP_ID = 40
  107. WITH CHECK OPTION;
  108.  
  109. SELECT * FROM v_EMP_DEP_40;
  110.  
  111. --2.7
  112. UPDATE v_emp_dep_40
  113. set dep_no = 20
  114. where employee = 'Nichols';
  115.  
  116. --2.8
  117. update v_emp_dep_40
  118. set employee = 'Nicholson'
  119. where employee = 'Nichols';
  120.  
  121. --2.9
  122. drop view v_emp_dep_40;
  123. drop view v_employees;
  124.  
  125. ----------------------------------------------------
  126. --3.1
  127. create sequence dt_id_seq
  128. increment by 10
  129. start with 200
  130. maxvalue 1000
  131. nocache
  132. nocycle;
  133.  
  134. --3.2
  135. create synonym dzialy
  136. for departments;
  137.  
  138. --3.3
  139. select * from dzialy;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement