Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE imp_tmp (
- kod_zeme varchar2(200),
- nazev_zeme varchar2(200),
- kod_meny varchar2(200),
- nazev_meny varchar2(200),
- platnost_kurzu_od varchar2(200),
- kurz varchar2(200),
- mena_do varchar2(200)
- );
- CREATE TABLE imp_tmp2
- (
- id_pobocky varchar2(200) ,
- nazev varchar2(200) ,
- adresa varchar2(200),
- mesto varchar2(200) ,
- telefon varchar2(200),
- zeme varchar2(200),
- jmeno varchar2(200) ,
- prijmeni varchar2(200) ,
- plat varchar2(200)
- ) ;
- CREATE TABLE imp_tmp3
- (
- id_zbozi varchar2(200) ,
- kategorie varchar2(200) ,
- typ varchar2(200),
- nรกzev varchar2(200) ,
- popis varchar2(200),
- mena varchar2(200),
- cena varchar2(200)
- ) ;
- CREATE TABLE imp_tmp4
- (
- id_zbozi varchar2(200) ,
- id_prodejny varchar2(200) ,
- datum_kurzu varchar2(200),
- datum_prodeje varchar2(200) ,
- kus varchar2(200),
- mena varchar2(200),
- cena varchar2(200)
- ) ;
- CREATE TABLE Kurzy
- (
- id NUMBER (4) ,
- platnost_od DATE ,
- kurz NUMBER (8) ,
- kurz_meny_z VARCHAR2 (10) ,
- kurz_meny_do VARCHAR2 (10)
- );
- ALTER TABLE Kurzy ADD CONSTRAINT Kurzy_Pk PRIMARY KEY ( id );
- ALTER TABLE Kurzy ADD CONSTRAINT fk_kurzy_mena_z FOREIGN KEY (kurz_meny_z) REFERENCES
- Meny (kod_meny);
- ALTER TABLE Kurzy ADD CONSTRAINT fk_kurzy_mena_do FOREIGN KEY (kurz_meny_do) REFERENCES
- Meny (kod_meny);
- CREATE TABLE Meny
- (
- kod_meny VARCHAR2 (10) ,
- nazev_meny VARCHAR2 (30)
- );
- ALTER TABLE Meny ADD CONSTRAINT Meny_Pk PRIMARY KEY ( kod_meny );
- CREATE TABLE Pobocky
- (
- id_pobocky NUMBER (5) ,
- nazev VARCHAR2 (30) ,
- adresa VARCHAR2 (50) ,
- mesto VARCHAR2 (20) ,
- telefon CHAR (12),
- kod_zeme varchar2(10)
- ) ;
- ALTER TABLE Pobocky ADD CONSTRAINT Pobocka_Pk PRIMARY KEY ( id_pobocky ) ;
- ALTER TABLE Pobocky ADD CONSTRAINT Zeme_Pobocky_Fk FOREIGN KEY (kod_zeme) REFERENCES
- Zeme (id_zeme);
- CREATE TABLE Pracovnici
- (
- id NUMBER(5) ,
- jmeno VARCHAR2 (15) ,
- prijmeni VARCHAR2 (20) ,
- plat NUMBER (5),
- id_pobocky NUMBER (5)
- );
- ALTER TABLE Pracovnici ADD CONSTRAINT Pracovnici_Pk PRIMARY KEY ( id ) ;
- ALTER TABLE Pracovnici ADD CONSTRAINT Pracovnici_Pobocky_Fk FOREIGN KEY (id_pobocky) REFERENCES
- Pobocky (id_pobocky);
- CREATE TABLE Prodeje
- (
- datum_prodeje DATE ,
- datum_kurzu DATE ,
- mnozstvi NUMBER (3) ,
- cena_za_ks NUMBER (8) ,
- id_pobocky NUMBER (5),
- kod_meny VARCHAR2 (10),
- id_zbozi NUMBER (4)
- ) ;
- ALTER TABLE Prodeje ADD CONSTRAINT Prodeje_Pobocky_Fk FOREIGN KEY (id_pobocky) REFERENCES
- Pobocky (id_pobocky);
- ALTER TABLE Prodeje ADD CONSTRAINT Pracovnici_Meny_Fk FOREIGN KEY (kod_meny) REFERENCES
- Meny (kod_meny);
- ALTER TABLE Prodeje ADD CONSTRAINT Pracovnici_Zbozi_Fk FOREIGN KEY (id_zbozi) REFERENCES
- Zbozi (id_zbozi);
- CREATE TABLE Zbozi
- (
- id_zbozi NUMBER (4) ,
- kategorie VARCHAR2 (20) ,
- nazev VARCHAR2 (20) ,
- typ VARCHAR2 (20) ,
- popis VARCHAR2 (30) ,
- cena NUMBER (8),
- kod_meny varchar2(10)
- ) ;
- ALTER TABLE Zbozi ADD CONSTRAINT Zbozi_Pk PRIMARY KEY ( id_zbozi ) ;
- ALTER TABLE Zbozi ADD CONSTRAINT Zbozi_Meny_fk FOREIGN KEY (kod_meny) REFERENCES Meny (kod_meny);
- CREATE TABLE Zeme
- (
- id_zeme varchar2(10),
- nazev varchar2(50),
- kod_meny varchar2(10),
- CONSTRAINT Zeme_Pk PRIMARY KEY (id_zeme)
- );
- ALTER TABLE Zeme ADD CONSTRAINT Zeme_meny_fk FOREIGN KEY (kod_meny) REFERENCES
- Meny (kod_meny);
- INSERT INTO Meny(kod_meny,nazev_meny)SELECT DISTINCT kod_meny, nazev_meny FROM imp_tmp;
- INSERT INTO Zeme(id_zeme,nazev,kod_meny)SELECT DISTINCT imp_tmp.KOD_ZEME, imp_tmp.NAZEV_ZEME, imp_tmp.KOD_MENY FROM imp_tmp;
- DROP SEQUENCE seq_imp_tmp;
- CREATE SEQUENCE seq_imp_tmp;
- INSERT INTO Kurzy(id,platnost_od,kurz,kurz_meny_z,kurz_meny_do)
- SELECT seq_imp_tmp.NEXTVAL, platnost, kurz, kod_meny, mena_do FROM (
- SELECT DISTINCT to_date(platnost_kurzu_od,'dd.mm.yy hh24:mi:ss') platnost, to_number(kurz) kurz , kod_meny, mena_do FROM imp_tmp);
- INSERT INTO Pobocky(id_pobocky ,nazev,adresa ,mesto,telefon,kod_zeme) SELECT DISTINCT to_number(id_pobocky) id_pobocky ,nazev,adresa ,mesto,telefon,zeme FROM imp_tmp2;
- INSERT INTO Pracovnici(id,jmeno,prijmeni,plat,id_pobocky) SELECT seq_imp_tmp.NEXTVAL,jmeno,prijmeni,plat,id_pobocky FROM (
- SELECT DISTINCT jmeno,prijmeni,plat, to_number(id_pobocky)id_pobocky FROM imp_tmp2);
- INSERT INTO Zbozi(id_zbozi ,kategorie, nazev, typ , popis,cena,kod_meny ) SELECT DISTINCT to_number(id_zbozi) id_zbozi ,kategorie, nรกzev, typ , popis,to_number(cena)cena,mena FROM imp_tmp3;
- INSERT INTO Prodeje(datum_prodeje,datum_kurzu,mnozstvi,cena_za_ks,id_pobocky ,kod_meny ,id_zbozi )
- SELECT DISTINCT to_date(datum_prodeje,'dd.mm.yy hh24:mi:ss') datum_prodeje, to_date(datum_kurzu,'dd.mm.yy hh24:mi:ss')datum_kurzu,
- kus,to_number(cena)cena,to_number(id_prodejny)id_prodejny ,mena , to_number(id_zbozi) id_zbozi FROM imp_tmp4;
- SELECT * FROM pobocky ORDER BY NAZEV ASC;
- SELECT Pracovnici.Jmeno, Pracovnici.Prijmeni FROM PRACOVNICI ORDER BY Prijmeni ASC;
- SELECT * FROM Prodeje WHERE DATUM_PRODEJE LIKE 'Fanta' BETWEEN TO_DATE ('1.2.2015', 'dd.mm.yy') AND TO_DATE ('15.2.2015', 'dd.mm.yy') ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement