13112019_MS_bazy_danych

--1
BEGIN RegenerateTables; END;
/

--2
INSERT INTO departments (dep_id, name, location)
VALUES(100, 'Zamówienia', 'Pokój 225');

--3
CREATE TABLE EMP_COPY
(
  EMP_ID      INTEGER,
  FIRST_NAME  VARCHAR2(20 BYTE)             NOT NULL,
  LAST_NAME   VARCHAR2(30 BYTE)             NOT NULL,
  HIRE_DATE   DATE  DEFAULT SYSDATE         NOT NULL,
  SALARY      NUMBER(7,2)                   NOT NULL,
  ALLOWANCE   NUMBER(7,2),
  MANAGER_ID  INTEGER,
  DEP_ID      INTEGER,
  CONSTRAINT PK_EMP_COPY_ID PRIMARY KEY (EMP_ID),
  CONSTRAINT FK_EMP_COPY_MANAGER_ID FOREIGN KEY (MANAGER_ID) REFERENCES EMP_COPY (EMP_ID),
  CONSTRAINT FK_EMP_COPY_DEP_ID FOREIGN KEY (DEP_ID) REFERENCES DEPARTMENTS (DEP_ID)
);

--4
INSERT INTO emp_copy (emp_id, first_name, last_name, hire_date, SALARY, ALLOWANCE, MANAGER_ID, DEP_ID)
VALUES(1, 'Johnny', 'Kopytko', TRUNC(SYSDATE), 1000, 400, NULL, 100);

--5
INSERT INTO emp_copy
VALUES(2, 'Filip', 'Tiruriru', TRUNC(SYSDATE), 2100, 500, 1, 100);
INSERT INTO emp_copy
VALUES(3, 'Szczepania', 'Kowalska', TRUNC(SYSDATE), 2500, 500, 1, 100);

--6
COMMIT;

--7
INSERT INTO emp_copy
(SELECT emp_id, first_name, last_name, hire_date, salary, allowance, manager_id, dep_id
FROM employees
WHERE dep_id IN (10, 20, 30));

--8
COMMIT;

--9
UPDATE emp_copy
SET manager_id = 100
WHERE last_name = 'Kopytko';

--10
UPDATE emp_copy
SET last_name = 'Johnson'
WHERE emp_id = 100;

--11
UPDATE emp_copy
SET allowance=NULL
WHERE dep_id = 20 OR dep_id = 30;

--12
UPDATE emp_copy
SET salary=6000
WHERE salary BETWEEN 5000 AND 6000;

--13
DELETE FROM emp_copy
WHERE last_name='Blake';

--14
UPDATE emp_copy
SET dep_id=(SELECT dep_id FROM emp_copy WHERE last_name='Kopytko')
WHERE first_name='Anne' AND last_name='Taylor';

--15
COMMIT;

--16
DELETE FROM emp_copy
WHERE dep_id=20;

--17
SAVEPOINT A;

--18
DELETE FROM emp_copy
WHERE dep_id=30;

--19
SAVEPOINT B;

--20
DELETE FROM emp_copy;

--21
ROLLBACK TO B;

--22
ROLLBACK TO A;

---------------------------------- DDL -----------------------------------------
--1
DROP TABLE dept;


CREATE TABLE DEPT
(DEPT_ID NUMBER(3),
NAME VARCHAR2(30),
LOCATION VARCHAR2(30)
);

--2
INSERT INTO dept
(SELECT * FROM departments);

--3
ALTER TABLE DEPT
ADD (phone CHAR(20) DEFAULT 'BRAK');
UPDATE dept
SET phone=0700
WHERE dept_id=30;

--4
RENAME dept TO DEPART;

--5
TRUNCATE TABLE depart;

--6
DROP TABLE depart;

--7
CREATE TABLE EMPL
AS SELECT emp_id, first_name, last_name, (salary+NVL(allowance,0)) AS "pay", (SELECT name
FROM departments
WHERE dep_id=outer.dep_id) AS "DEP_NAME"
FROM employees outer;

--8
ALTER TABLE emp1
MODIFY(last_name VARCHAR2(50));

--9
ALTER TABLE emp1
DROP COLUMN first_name;

--10
DROP TABLE emp1;