CREATE TABLE mobily ( 
    Kod_tel varchar2(50) NOT NULL,
    Vyrobce varchar2(50) NOT NULL,
    Typ varchar2(50) NOT NULL,
    Cena_bez_DPH number(8,2) NOT NULL,
  PRIMARY KEY (Kod_tel) ) ;

CREATE TABLE zakaznici (
  Kod_zak varchar2(50)  NOT NULL,
  Jmeno varchar2(15)  NOT NULL,
  Prijmeni varchar2(25) NOT NULL,
  Ulice varchar2(50)  ,
  Mesto varchar2(50) ,
  PSc varchar2(50) ,
  Telefon varchar2(15) ,
  PRIMARY KEY (Kod_zak)
) ;

CREATE TABLE objednavky (
  Kod_obj number(5) NOT NULL,
  Kod_zak varchar2(10) NOT NULL,
  Datum varchar2(12) NOT NULL,
  Zpusob_platby varchar2(50) NOT NULL,
  Splatnost varchar2(50) ,
  Vyrizeni varchar2(1) ,
  PRIMARY KEY (Kod_obj),
  CONSTRAINT obj_zak_fk FOREIGN KEY (Kod_zak) REFERENCES zakaznici (Kod_zak)
) ;


CREATE TABLE obsah (
  Kod_obj number(5) NOT NULL,
  Kod_tel varchar2(50) NOT NULL ,
  Pocet_kusu number(4) NOT NULL,
  PRIMARY KEY (Kod_obj,Kod_tel),
  CONSTRAINT obs_mob_fk FOREIGN KEY (Kod_tel) REFERENCES mobily (Kod_tel) ,
  CONSTRAINT obs_obj_fk FOREIGN KEY (Kod_obj) REFERENCES objednavky (Kod_obj)
) ;

INSERT INTO mobily VALUES ('1000','Nokia','1800',897);
INSERT INTO mobily VALUES('1001','Nokia','C2',2402);
INSERT INTO mobily VALUES    ('1002','Samsung','Galaxy 551',4577);
INSERT INTO mobily VALUES('1003','Sony','Experia X8',2977);
INSERT INTO mobily VALUES('1004','Apple','iPhone 4',16299);
INSERT INTO mobily VALUES('1005','Nokia','7650',9447);
INSERT INTO mobily VALUES('1006','BlackBerry','Bold 9780',10299);
INSERT INTO mobily VALUES('1007','Nokia','6101',3950);
INSERT INTO mobily VALUES('1008','BlackBerry','8520',4899);
INSERT INTO mobily VALUES('1009','Samsung','S5260 Star II',2370);
INSERT INTO mobily VALUES('1010','HTC','Desire HD',12299);
INSERT INTO mobily VALUES('1011','Sony','Experia mini',5777);
INSERT INTO mobily VALUES('1012','Motorola','Defy',6370);
INSERT INTO mobily VALUES('1013','HTC','Wildfire S',6199);
INSERT INTO mobily VALUES('1014','Nokia','C5',4699);
INSERT INTO mobily VALUES('1015','Sony Ericsson','K750i',4400);
INSERT INTO mobily VALUES('1016','Nokia','E7',12200);
INSERT INTO mobily VALUES('1017','Nokia','E1170i',599);
INSERT INTO mobily VALUES('1018','Samsung','Xcover 271',2899);
INSERT INTO mobily VALUES('1020','Huawei','Ideos X5',4977);
INSERT INTO mobily VALUES('1021','LG','P970 Optimus',7177);
INSERT INTO mobily VALUES('1022','LG','Optimus 3D',10337);
INSERT INTO mobily VALUES('1023','Nokia','N8',10977);
INSERT INTO mobily VALUES('1024','HTC','EVO 3D',13177);
INSERT INTO mobily VALUES('1025','Apple','iPhone 5',19765);
INSERT INTO mobily VALUES('1026','Samsung','S5660',2999);
INSERT INTO mobily VALUES('1027','Nokia','Lumia 520',2777);
INSERT INTO mobily VALUES('1028','Nokia','Lumia 625',4789);

INSERT INTO zakaznici VALUES ('0001','Jiri','Andiel','Jirská 6','Ostrava','70200','671222333');
INSERT INTO zakaznici VALUES('0002','Jan','Hajduk','Pod Kovou 13','Vratimov','73932','564323123');
INSERT INTO zakaznici VALUES('0003','Jan','Svrek','Jirská8','Ostrava-Vítkovice','70200','649159488');
INSERT INTO zakaznici VALUES('0004','Petra','Hajduková','Strma 2','Vratimov','73932','425115116');
INSERT INTO zakaznici VALUES('0005','Roman','Musialek','U lesa 7','Dolní Datyne','73905','875436999');
INSERT INTO zakaznici VALUES('0012','Jan','Jost','Smrkova 962','Senov','75651','545246546');
INSERT INTO zakaznici VALUES('0056','Ivo','Kuba',NULL,NULL,NULL,NULL);
INSERT INTO zakaznici VALUES('0057','Martin','Nový',NULL,'Ostrava',NULL,NULL);
INSERT INTO zakaznici VALUES('0100','Martin','Polasek','Okruzní 6','Ostrava-Vítkovice','74568','725669999');
INSERT INTO zakaznici VALUES('0987','Lucie','Ferfecká','Manon','Bohumín',NULL,NULL);
INSERT INTO zakaznici VALUES('1235','Irena','Pindová','Ke statku 161','Havířov','73934','596887700');
INSERT INTO zakaznici VALUES('2136','Petra','Janská','Smrková 6','Ostrava-Radvanice',NULL,NULL);
INSERT INTO zakaznici VALUES('2155','Karel','Nový','Lesní 5','Ostrava-Radvanice','72121','254555455');
INSERT INTO zakaznici VALUES('2222','Jan','Malý','Zelená 6','Ostrava-Dubina','72466',NULL);
INSERT INTO zakaznici VALUES('2356','Karla','Novotná',NULL,'Ostrava-Přívoz',NULL,'653637786');
INSERT INTO zakaznici VALUES('2365','Jan','Malý','Kratochvílova 5','Petrvald',NULL,'434357965');
INSERT INTO zakaznici VALUES('2754','Ivana','Hrubá','Lesní 6','Orlová', NULL,NULL);
INSERT INTO zakaznici VALUES('2875','Zuzana','Nejedlá','Okrajová 8','Orlová-Lutyne',NULL,'535479595');
INSERT INTO zakaznici VALUES('5689','Karel','Jan','Kolmá 8','Ostrava','70200',NULL);
INSERT INTO zakaznici VALUES('5690','Jan','Nový','Lomá','Ostrava',NULL,'354667975');
INSERT INTO zakaznici VALUES('5691','Marcela','Malá',NULL,'Havířov-Mesto',NULL,NULL);
INSERT INTO zakaznici VALUES('5698','Petr','Jan','U potoka 698','Petrvald',NULL,NULL);
INSERT INTO zakaznici VALUES('6456','Ondra','Malý','Pod horou 3','Petrvald','73965','698434654');
INSERT INTO zakaznici VALUES('6549','Jan','Nový','Strmá 65','Ostrava-Radvanice',NULL,NULL);
INSERT INTO zakaznici VALUES('9531','Petr','Pavel','Ostrý','Havířov-Bludovice','73601',NULL);
INSERT INTO zakaznici VALUES('9685','Petr','Kuba','Matiční 52','Ostrava-Moravská Ostrava',NULL,'255656879');

INSERT INTO objednavky VALUES (5,'0004','2018-01-16','Karta','60','0');
INSERT INTO objednavky VALUES(8,'2155','2015-03-04','Prevod','14','0');
INSERT INTO objednavky VALUES(9,'2222','2017-03-03','Cash','0','0');
INSERT INTO objednavky VALUES(10,'6456','2018-01-04','Prevod','21','0');
INSERT INTO objednavky VALUES(11,'9531','2017-04-02','Karta','14','0');
INSERT INTO objednavky VALUES(12,'0012','2015-04-04','Cash','0','0');
INSERT INTO objednavky VALUES(13,'0002','2018-06-16','Cash','0','0');
INSERT INTO objednavky VALUES(14,'0012','2017-03-09','Cash','0','0');
INSERT INTO objednavky VALUES(15,'0005','2017-07-04','Prevod','21','0');
INSERT INTO objednavky VALUES(16,'1235','2017-05-06','Karta','14','0');
INSERT INTO objednavky VALUES(17,'9685','2013-09-20','Cash','0','0');
INSERT INTO objednavky VALUES(19,'5698','2017-05-19','Cash','0','0');
INSERT INTO objednavky VALUES(20,'2136','2015-03-19','Prevod','14','0');
INSERT INTO objednavky VALUES(21,'0100','2017-09-20','Prevod','14','0');
INSERT INTO objednavky VALUES(22,'0005','2018-01-07','Prevod','21','0');
INSERT INTO objednavky VALUES(23,'0100','2017-08-24','Cash','0','0');
INSERT INTO objednavky VALUES(24,'0002','2018-02-01','Karta','14','0');
INSERT INTO objednavky VALUES(26,'0100','2013-06-19','Cash','0','0');
INSERT INTO objednavky VALUES(27,'0056','2018-02-21','Cash','0','0');
INSERT INTO objednavky VALUES(28,'0012','2017-05-02','Karta','14','0');
INSERT INTO objednavky VALUES(29,'0005','2018-02-20','Cash','0','0');
INSERT INTO objednavky VALUES(30,'9531','2017-09-22','Cash','0','0');
INSERT INTO objednavky VALUES(31,'5698','2018-01-10','Cash','0','0');
INSERT INTO objednavky VALUES(32,'0002','2018-03-31','Cash','0','0');
INSERT INTO objednavky VALUES(33,'9531','2018-05-19','Cash','0','0');
INSERT INTO objednavky VALUES(34,'2356','2017-01-18','Cash','0','0');

INSERT INTO obsah VALUES (5,'1005',10);
INSERT INTO obsah VALUES(9,'1012',10);
INSERT INTO obsah VALUES(11,'1008',11);
INSERT INTO obsah VALUES(11,'1011',12);
INSERT INTO obsah VALUES(12,'1004',1);
INSERT INTO obsah VALUES(12,'1007',11);
INSERT INTO obsah VALUES(13,'1002',10);
INSERT INTO obsah VALUES(13,'1011',5);
INSERT INTO obsah VALUES(14,'1003',3);
INSERT INTO obsah VALUES(14,'1004',5);
INSERT INTO obsah VALUES(14,'1009',6);
INSERT INTO obsah VALUES(15,'1010',10);
INSERT INTO obsah VALUES(15,'1012',5);
INSERT INTO obsah VALUES(16,'1007',10);
INSERT INTO obsah VALUES(16,'1008',8);
INSERT INTO obsah VALUES(16,'1010',3);
INSERT INTO obsah VALUES(16,'1011',5);
INSERT INTO obsah VALUES(19,'1001',5);
INSERT INTO obsah VALUES(19,'1006',5);
INSERT INTO obsah VALUES(20,'1008',10);
INSERT INTO obsah VALUES(21,'1005',10);
INSERT INTO obsah VALUES(21,'1010',5);
INSERT INTO obsah VALUES(22,'1011',10);
INSERT INTO obsah VALUES(22,'1012',5);
INSERT INTO obsah VALUES(23,'1013',15);
INSERT INTO obsah VALUES(23,'1014',10);
INSERT INTO obsah VALUES(24,'1007',5);
INSERT INTO obsah VALUES(24,'1013',6);
INSERT INTO obsah VALUES(24,'1014',13);
INSERT INTO obsah VALUES(26,'1001',1);
INSERT INTO obsah VALUES(26,'1010',10);
INSERT INTO obsah VALUES(26,'1011',13);
INSERT INTO obsah VALUES(26,'1013',10);
INSERT INTO obsah VALUES(26,'1014',15);
INSERT INTO obsah VALUES(27,'1014',1);
INSERT INTO obsah VALUES(27,'1017',1);
INSERT INTO obsah VALUES(27,'1018',1);
INSERT INTO obsah VALUES(27,'1020',1);
INSERT INTO obsah VALUES(27,'1022',1);
INSERT INTO obsah VALUES(27,'1023',1);
INSERT INTO obsah VALUES(27,'1024',1);
INSERT INTO obsah VALUES(28,'1004',1);
INSERT INTO obsah VALUES(28,'1008',1);
INSERT INTO obsah VALUES(28,'1010',1);
INSERT INTO obsah VALUES(28,'1023',1);
INSERT INTO obsah VALUES(28,'1024',1);
INSERT INTO obsah VALUES(29,'1000',10);
INSERT INTO obsah VALUES(29,'1001',10);
INSERT INTO obsah VALUES(29,'1005',10);
INSERT INTO obsah VALUES(30,'1013',10);
INSERT INTO obsah VALUES(31,'1006',10);
INSERT INTO obsah VALUES(31,'1010',10);
INSERT INTO obsah VALUES(31,'1013',10);
INSERT INTO obsah VALUES(31,'1014',10);
INSERT INTO obsah VALUES(32,'1002',1);
INSERT INTO obsah VALUES(32,'1013',1);
INSERT INTO obsah VALUES(32,'1025',1);
INSERT INTO obsah VALUES(33,'1013',1);
INSERT INTO obsah VALUES(33,'1025',1);
INSERT INTO obsah VALUES(33,'1027',1);
INSERT INTO obsah VALUES(34,'1003',1);
INSERT INTO obsah VALUES(34,'1004',1);
INSERT INTO obsah VALUES(34,'1006',1);
INSERT INTO obsah VALUES(34,'1010',1);
INSERT INTO obsah VALUES(34,'1022',1);
INSERT INTO obsah VALUES(34,'1023',1);
INSERT INTO obsah VALUES(34,'1024',1);
INSERT INTO obsah VALUES(34,'1025',1);
INSERT INTO obsah VALUES(34,'1026',1);

alter table objednavky add datum_zad date;

update objednavky set datum_zad= to_date(DATUM,'YYYY-mm-dd') ;

alter table objednavky drop column datum;