Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --BEGIN RegenerateTables; End;
- --1.1
- CREATE TABLE workers(
- W_ID NUMBER(3) PRIMARY KEY,
- FIRST_NAME VARCHAR2(30) NOT NULL,
- LAST_NAME VARCHAR2(30) NOT NULL,
- SALARY NUMBER(7,2) CONSTRAINT SALARY_N NOT NULL,
- UNIQUE(FIRST_NAME, LAST_NAME));
- --1.2
- ALTER TABLE WORKERS
- DROP UNIQUE(FIRST_NAME, LAST_NAME);
- --1.3
- ALTER TABLE workers
- ADD CONSTRAINT W_LAST_NAME_CK CHECK (LENGTH(LAST_NAME)>2);
- insert into workers
- values (10, 'HUANG', 'LI', 4000);
- --1.4
- alter table workers
- drop constraint salary_n;
- --1.5
- alter table workers
- add BOSS_ID NUMBER(3)
- constraint boss_id_fk
- references workers(w_id);
- SELECT * FROM workers;
- --1.6
- INSERT INTO workers
- values (10, 'Jan', 'Kowalski', '4500', null);
- INSERT INTO workers
- values (20, 'Jacek', 'Malinowski', '3000', 10);
- --1.7
- DELETE FROM workers
- WHERE w_id = 10;
- --1.8
- ALTER TABLE workers
- DISABLE CONSTRAINT BOSS_ID_FK;
- --1.9
- ALTER TABLE workers
- ENABLE CONSTRAINT boss_id_fk;
- --1.10
- INSERT into workers
- values (10, 'Zbigniew', 'Malicki', 5000, null);
- --1.11
- delete from workers
- where w_id=10;
- --1.12
- alter table workers
- drop constraint boss_id_fk;
- --1.13
- alter table workers
- add constraint boss_id_fk_c
- FOREIGN KEY (boss_id)
- REFERENCES workers(w_id)
- ON DELETE CASCADE;
- --1.14
- select * from workers;
- delete from workers
- where w_id = 10;
- --1.15
- drop table workers;
- --------------------------------------------------
- --2.1
- create or replace view V_EMPLOYEES as
- select emp_id, last_name "EMP_NAME", dep_id
- from employees;
- --2.2
- DESCRIBE V_EMPLOYEES;
- SELECT * FROM V_EMPLOYEES;
- --2.3
- SELECT emp_name FROM V_EMPLOYEES
- WHERE dep_id = 30;
- --2.4
- UPDATE v_employees
- SET dep_id = 20
- WHERE emp_name = 'Davis';
- COMMIT;
- --2.5
- create view v_emp_dep_40
- ("EMP_NO", "EMPLOYEE", "DEP_NO")
- AS SELECT * FROM V_EMPLOYEES
- where DEP_ID = 40
- WITH CHECK OPTION;
- SELECT * FROM v_EMP_DEP_40;
- --2.7
- UPDATE v_emp_dep_40
- set dep_no = 20
- where employee = 'Nichols';
- --2.8
- update v_emp_dep_40
- set employee = 'Nicholson'
- where employee = 'Nichols';
- --2.9
- drop view v_emp_dep_40;
- drop view v_employees;
- ----------------------------------------------------
- --3.1
- create sequence dt_id_seq
- increment by 10
- start with 200
- maxvalue 1000
- nocache
- nocycle;
- --3.2
- create synonym dzialy
- for departments;
- --3.3
- select * from dzialy;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement