CREATE TABLE SCOTT.kartoteka1
(
cislo_karty     NUMBER(6) NOT NULL PRIMARY KEY,
datum_zalozeni  DATE NOT NULL,
nazev           VARCHAR (50) NOT NULL,
cena 		    NUMBER (8,2) NOT NULL,
baleni          VARCHAR (8),
CHECK (cena>0)
);


CREATE TABLE SCOTT.zasoba
(
polozka        NUMBER(8) NOT NULL PRIMARY KEY,
cislo_karty    NUMBER(6) NOT NULL,
datum          DATE NOT NULL,
popis          VARCHAR (50) NOT NULL,
prijem         NUMBER (9,2),
vydej          NUMBER (9,2),
zasoba         NUMBER (9,2),
FOREIGN KEY(cislo_karty) REFERENCES kartoteka(cislo_karty)
);


CREATE TABLE filialky 
(
  deptno NUMBER(2) NOT NULL,
  dname VARCHAR2(14),
  loc VARCHAR2(13)
);


CREATE TABLE emp1 
(
  empno    NUMBER(4) NOT NULL,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2) NOT NULL,
  FOREIGN KEY (deptno) REFERENCES dept(deptno) 
);


CREATE TABLE "SCOTT"."ZASOBA" 
( 
    "POLOZKA" NUMBER(8), 
    "CISLO_KARTY" NUMBER(6), 
    "DATUM" DATE, 
    "POPIS" VARCHAR2(50), 
    "PRIJEM" NUMBER, 
    "VYDAJ" NUMBER, 
    "ZASOBA" NUMBER, 
     PRIMARY KEY ("POLOZKA") VALIDATE , 
     FOREIGN KEY ("CISLO_KARTY") REFERENCES "SCOTT"."KARTOTEKA" ("CISLO_KARTY") VALIDATE )

CREATE OR REPLACE VIEW "SCOTT"."POHLED_EMP" AS SELECT SCOTT.DEPT.DNAME, SCOTT.EMP.ENAME, SCOTT.EMP.JOB 
FROM SCOTT.DEPT, SCOTT.EMP 
WHERE ((( SCOTT.DEPT.DEPTNO = '10') 
OR (SCOTT.DEPT.DEPTNO = '20' )) 
AND (EMP.DEPTNO = DEPT.DEPTNO))


CONNECT /AS SYSDBA
ALTER USER SH ACCOUNT UNLOCK; 
ALTER USER SH IDENTIFIED BY ORACLE; 
CONNECT SH/ORACLE;



CREATE TABLE SH.videokazety
(
    ev_cislo  NUMBER(4) PRIMARY KEY,
    nazev     VARCHAR2(15) 
);



INSERT INTO videokazety VALUES(1, 'Pocahontas');
INSERT INTO videokazety VALUES(2, 'Lion King');
INSERT INTO videokazety VALUES(3, 'Cinderella');

CREATE TABLE SH.uhrady
(
    id_uhrady NUMBER(6) PRIMARY KEY,
    ev_cislo  NUMBER(6),
    uhrada    NUMBER(9,2)
);

INSERT INTO uhrady VALUES (1,1, 320);
INSERT INTO uhrady VALUES (2,2, 260);
INSERT INTO uhrady VALUES (3,3, 350);
INSERT INTO uhrady VALUES (4,1, 170);
INSERT INTO uhrady VALUES (5,2, 180);
INSERT INTO uhrady VALUES (6,3, 210);

CREATE MATERIALIZED VIEW LOG ON videokazety
  WITH PRIMARY KEY,
  ROWID(nazev)
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON uhrady
  WITH PRIMARY KEY,
  ROWID(ev_cislo, uhrada)
  INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW pujcovna_mv
REFRESH FAST ON COMMIT
AS SELECT v.nazev, SUM(u.uhrada) AS suma_uhrad
FROM uhrady u, videokazety v
WHERE u.ev_cislo = v.ev_cislo
GROUP BY v.nazev;

CREATE TABLE pobocky1 
(
  id_pobocky NUMBER(5) NOT NULL,
  nazev      VARCHAR2(20),
  mesto      VARCHAR2(13)
);


INSERT INTO pobocky1 VALUES(seq_id.NEXTVAL, 'Finance','Praha');
INSERT INTO pobocky1 VALUES(seq_id.NEXTVAL, 'Vvoj','Brno');
INSERT INTO pobocky1 VALUES(seq_id.NEXTVAL, 'Marketing','Praha');
INSERT INTO pobocky1 VALUES(seq_id.NEXTVAL, 'Public Relation','Ostrava');


CREATE TABLE TEST_INDEXU_01 
(
  ev_cislo_01 INT, 
  jmeno VARCHAR(25), 
  narod DATE, 
  funkce VARCHAR(25), 
  vek INT, 
  mzda DECIMAL(9,2)
);

CREATE SEQUENCE ev_cislo_01 INCREMENT BY 1;
CREATE INDEX ix_ev_cislo_01 ON TEST_INDEXU_01 (ev_cislo_01);

BEGIN
  FOR ctr IN 1..100000 LOOP
   INSERT INTO TEST_INDEXU_01 VALUES (ev_cislo_01.nextval, 'neznm oban', SYSDATE, 'neznm',30, 15200);
  END LOOP;
END; 


CREATE TABLE TEST_INDEXU_02 
(
  ev_cislo_02 INT, 
  jmeno VARCHAR(25), 
  narod DATE, 
  funkce VARCHAR(25), 
  vek INT, 
  mzda DECIMAL(9,2)
);

CREATE SEQUENCE ev_cislo_02 INCREMENT BY 1;
CREATE INDEX ix_ev_cislo_02 ON TEST_INDEXU_02 (ev_cislo_02);
CREATE INDEX ix_jmeno ON TEST_INDEXU_02 (jmeno);
CREATE INDEX ix_narod ON TEST_INDEXU_02 (narod);
CREATE INDEX ix_funkce ON TEST_INDEXU_02 (funkce);
CREATE INDEX ix_vek ON TEST_INDEXU_02 (vek);
CREATE INDEX ix_mzda ON TEST_INDEXU_02 (mzda);

BEGIN
  FOR ctr IN 1..100000 LOOP
   INSERT INTO TEST_INDEXU_02 VALUES (ev_cislo_02.nextval, 'neznm oban', SYSDATE, 'neznm',30, 15200);
  END LOOP;
END; 


CREATE TABLE ubytovani_studenti 
(
  jmeno           VARCHAR2(25),
  pohlavi         CHAR(1) 
  CHECK (pohlavi IN ('M', 'Z'))
);


CREATE TABLE part_hovory
(
  kdy DATE, 
  kdo VARCHAR(25), 
  cena   DECIMAL(9,2)
)
PARTITION BY RANGE(kdy)
(
   PARTITION hovory_01_2001 VALUES LESS THAN (TO_DATE ('01.02.2006', 'dd.mm.YYYY')),
   PARTITION hovory_02_2001 VALUES LESS THAN (TO_DATE ('01.03.2006', 'dd.mm.YYYY')),
   PARTITION hovory_03_2001 VALUES LESS THAN (TO_DATE ('01.04.2006', 'dd.mm.YYYY')),
   PARTITION hovory_04_2001 VALUES LESS THAN (TO_DATE ('01.05.2006', 'dd.mm.YYYY'))
);


CREATE TABLE part_pobocky
(
  nazev VARCHAR(25), 
  sidlo VARCHAR(25)
)
PARTITION BY LIST(sidlo)
(
   PARTITION oblast_praha VALUES ('Praha', 'Koln'),
   PARTITION oblast_brno VALUES ('Brno', 'Olomouc'),
   PARTITION oblast_plzen VALUES ('Plze', 'Pbram')
);


CREATE TABLE part_telefon
(
  kdy DATE, 
  kdo VARCHAR(25), 
  cena   DECIMAL(9,2)
)
PARTITION BY HASH(kdy)
PARTITIONS 6;
STORE IN (tel_1, tel_2, tel_3, tel_4, tel_5, tel_6);




