LV 7

/*Zadatak 1*/
CREATE TABLE tabela( id VARCHAR2(25) NOT NULL,
                     naziv VARCHAR2(10) NOT NULL,
                     opis CHAR(15),
                     datum DATE NOT NULL,
                     korisnik VARCHAR2(30) NOT NULL,
                     napomena VARCHAR2(10));

ALTER TABLE tabela RENAME TO odjel;
SELECT * FROM odjel;
SELECT * FROM departments;

/*Zadatak2*/
INSERT INTO odjel( id, naziv, opis, datum, korisnik, napomena)
  SELECT department_id, SubStr(department_name, 1, 10), 'Test', SYSDATE, 'Korisnik', 'Napomena'
  FROM departments;

/*Zadatak3*/
ALTER TABLE odjel MODIFY(naziv VARCHAR(30));

/*Zadatak 3*/
ALTER TABLE odjel
ADD(lokacija NUMBER,
    menadzer_id NUMBER);

UPDATE odjel o
SET(naziv, lokacija, menadzer_id) = (SELECT d.department_name, d.location_id, d.manager_id
                                     FROM departments d
                                     WHERE d.department_id = o.id);

/*Zadatak 4*/
CREATE TABLE zaposleni(id NUMBER(4) NOT NULL,
                       sifra_zaposlenog VARCHAR2(5) NOT NULL,
                       naziv_zaposlenog CHAR(50),
                       godina_zaposlenja NUMBER(4) NOT NULL,
                       mjesec_zaposlenja CHAR(2) NOT NULL,
                       sifra_odjela VARCHAR2(5),
                       naziv_odjela VARCHAR2(15) NOT NULL,
                       grad CHAR(10) NOT NULL,
                       sifra_posla VARCHAR2(25),
                       naziv_posla CHAR(50) NOT NULL,
                       iznos_dodatak_na_platu NUMBER(5),
                       plata NUMBER(6) NOT NULL,
                       kontinent VARCHAR2(20),
                       datum_unosa DATE NOT NULL,
                       korisnik_unio CHAR(20) NOT NULL);

SELECT * FROM employees;

/*Zadatak 5*/
INSERT INTO zaposleni
       SELECT e.employee_id, SubStr(e.last_name || e.first_name, 1, 5), e.first_name || ' ' || e.last_name,
              To_Char(e.hire_date, 'YYYY'), To_Char(e.hire_date, 'MM'), e.department_id, Nvl(SubStr(d.department_name, 1, 15), ' '), SubStr(l.city, 1, 10),
              e.job_id, j.job_title, Nvl(e.salary*e.commission_pct, 0), e.salary, SubStr(r.region_name, 1, 20), SYSDATE, USER
       FROM employees e, jobs j, departments d, locations l, countries c, regions r
       WHERE e.job_id = j.job_id AND e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id AND c.region_id = r.region_id;

SELECT * FROM zaposleni;

/*Zadatak 6*/
CREATE TABLE zaposleni2 AS SELECT * FROM zaposleni;

/*Zadatak 7*/
ALTER TABLE zaposleni2 ADD(zaposleni_kolona VARCHAR(70),
                           odjel VARCHAR(20),
                           posao VARCHAR(75));



UPDATE zaposleni2 z1
SET(zaposleni_kolona, odjel, posao) = (SELECT Concat(Concat(z1.sifra_zaposlenog, ' '), z1.naziv_zaposlenog),
                                              Concat(Concat(Nvl(sifra_odjela,0), ' '), z1.naziv_odjela),
                                              Concat(Concat(z1.sifra_posla, ' '), z1.naziv_posla)
                                       FROM zaposleni2 z2
                                       WHERE z1.id = z2.id);

ALTER TABLE zaposleni2
DROP (sifra_zaposlenog, sifra_odjela, sifra_posla);
SELECT * FROM zaposleni2;

/*Zadatak 8*/
ALTER TABLE zaposleni2 RENAME TO zaposleni_backup;
SELECT * FROM zaposleni_backup;

/*Zadatak 9*/
COMMENT ON TABLE zaposleni IS 'Tabela zaposlenih';
COMMENT ON TABLE zaposleni_backup IS 'Backup tabela zaposlnih';

/*Zadatak 10*/
COMMENT ON COLUMN zaposleni.korisnik_unio IS 'Korisnik koji je unio dati red u tabelu';

/*Zadatak 11*/
ALTER TABLE zaposleni_backup SET unused (datum_unosa, korisnik_unio);

/*Zadatak 12*/
SELECT * FROM user_tab_comments;
SELECT * FROM user_col_comments;

/*Zadatak 13*/
ALTER TABLE zaposleni_backup
DROP unused COLUMNS;