User Tools

Site Tools


kurs:sql_depot

WETTENBÜRO DATENBANK TABLES ANLEGEN

CREATE TABLE ADM_admin (
     ADM_ID         NUMBER(12) NOT NULL,
     ADM_name       VARCHAR2(12) NOT NULL,
     ADM_password   VARCHAR2(12) NOT NULL,
     PRIMARY KEY(ADM_ID)
);
CREATE TABLE USR_user (
     USR_ID         NUMBER(12) NOT NULL,
     USR_password   VARCHAR2(12) NOT NULL,
     USR_name       VARCHAR2(12)NOT NULL,
     USR_email      VARCHAR2(20) NOT NULL,
     USR_mitglieds_datum  DATE NOT NULL,
     USR__ist_freigeschaltet  VARCHAR2(12)NOT NULL,
     USR__ist_deaktiviert    VARCHAR2(12)NOT NULL,
     CONSTRAINT PK_USR_user PRIMARY KEY(USR_ID)
);
CREATE TABLE MNN_mannschaft (
      MNN_ID         NUMBER(12) NOT NULL,
      MNN_name       VARCHAR(12)NOT NULL,
      PRIMARY KEY(MNN_ID)
);
CREATE TABLE WET_wette (
     WET_ID            NUMBER(12) NOT NULL,
     WET_anfangzeit    DATE NOT NULL,
     WET_endzeit       DATE NOT NULL,
     WET_quote_tipp_1  NUMBER(5,2) NOT NULL,
     WET_quote_tipp_2  NUMBER(5,2) NOT NULL,
     WET_quote_tipp_3  NUMBER(5,2) NOT NULL,
     WET_ergebnis      NUMBER(12) NOT NULL,
     WET_is_begonnen   NUMBER(2)NOT NULL,
     WET_is_beenden    NUMBER(2) NOT NULL,
     WET_is_processed  NUMBER(2) NOT NULL,
     WET_MNN_erste_ID        NUMBER(12) NOT NULL,
     WET_MNN_zweite_ID          NUMBER(12) NOT NULL,
     FOREIGN KEY(WET_MNN_erste_ID) REFERENCES MNN_mannschaft(MNN_ID),
     FOREIGN KEY(WET_MNN_zweite_ID) REFERENCES MNN_mannschaft(MNN_ID),
     CONSTRAINT PK_WET_ID PRIMARY KEY(WET_ID)
);
CREATE TABLE GWT_gespieltewetten (
       GWT_ID            NUMBER(12) NOT NULL,
       GWT_USR_ID        NUMBER(12) NOT  NULL,
       GWT_WET_ID        NUMBER(12) NOT NULL,
       PRIMARY KEY(GWT_ID),
       FOREIGN KEY(GWT_USR_ID) REFERENCES USR_user (USR_ID),
       FOREIGN KEY (GWT_WET_ID) REFERENCES WET_wette (WET_ID)
);
CREATE TABLE KNT_konto (
       KNT_ID            NUMBER(12) NOT NULL,
       KNT_USR_ID        NUMBER(12) NOT NULL,
       KNT_kontostand    NUMBER(12) NOT NULL,
       FOREIGN KEY(KNT_USR_ID) REFERENCES USR_user(USR_ID),
       PRIMARY KEY(KNT_ID)
);
CREATE TABLE KBW_kontobewegung(
      KBW_ID            NUMBER(12) NOT NULL,
      KBW_KNT_ID        NUMBER(12) NOT NULL,
      KBW_gewinn        NUMBER(12) NOT NULL,
      KBW_verlust       NUMBER(12) NOT NULL,
      KBW_einzahlung    NUMBER(12) NOT NULL,
      KBW_auszahlung    NUMBER(12) NOT NULL,
      KBW_bewegungs_datum    DATE NOT NULL,
      FOREIGN KEY (KBW_KNT_ID)REFERENCES KNT_konto(KNT_ID),
      PRIMARY KEY (KBW_ID)
);
CREATE TABLE SSN_session (
      SSN_ID         NUMBER(12) NOT NULL,
      SSN_USR_ID     NUMBER(12) NOT NULL,
      FOREIGN KEY(SSN_USR_ID) REFERENCES USR_user(USR_ID),
      PRIMARY KEY(SSN_ID)
);

DATEN INSERT FÜR WETTBÜRO TABLES

INSERT INTO ADM_admin 
    VALUES(100,'Peter','12ca');
INSERT INTO ADM_admin 
    VALUES(101, 'Valeria', '13ab');
INSERT INTO ADM_admin 
    VALUES(102, 'Evrim', '14dc');
INSERT INTO ADM_admin 
    VALUES(103, 'Carlo', '15df');
INSERT INTO ADM_admin 
    VALUES(104, 'Roger', '123k');
</code >
<code sql>
INSERT INTO USR_user  
    VALUES(2450, 'cad123', 'Mike', 'mike@yahoo.at', '12-MAI-98', 
'true', 'false');
INSERT INTO USR_user  
    VALUES(2451, 'abc256', 'Paul', 'paul@gmx.at', '11-JAN-99', 'true', 
'false');
INSERT INTO USR_user  
    VALUES(2452, 'pot458', 'Susane', 'hint@gmx.at', '02-JUN-01', 
'true', 'false');
INSERT INTO USR_user  
    VALUES(2453, 'car569', 'Paco', 'music@hotmail.at', '05-AUG-02', 
'true', 'false');
INSERT INTO USR_user  
    VALUES(2454, 'boli51', 'Sandro', 'rab@firma.at', '08-SEP-02', 
'true', 'false');
INSERT INTO MNN_mannschaft
    VALUES(30, 'RAPID');
INSERT INTO MNN_mannschaft
    VALUES(40, 'AUSTRIA');
INSERT INTO MNN_mannschaft
      VALUES(50, 'GAK');
INSERT INTO MNN_mannschaft
      VALUES(10, 'TIROL');
INSERT INTO MNN_mannschaft
      VALUES(60, 'ADMIRA');
INSERT INTO MNN_mannschaft
      VALUES(70, 'STURM GRAZ');
INSERT INTO MNN_mannschaft
     VALUES(80, 'RIED');
INSERT INTO MNN_mannschaft
     VALUES(20, 'SALZBURG');
INSERT INTO MNN_mannschaft
      VALUES(90, 'PASCHING');
INSERT INTO WET_wette
     VALUES(5, to_date('10-02-2006 11:50','DD-MM-YYYY HH24:MI'), 
to_date('11-02-2006 14:00','DD-MM-YYYY HH24:MI'),1,2,3,
          1, 5,10,1,30,50);
INSERT INTO WET_wette
     VALUES(6,to_date('11-03-2006 10:00','DD-MM-YYYY 
HH24:MI'),to_date('12-03-2006 15:00','DD-MM-YYYY HH24:MI'), 1, 0, 4,
            2, 3,4,6,40,10);
INSERT INTO WET_wette
     VALUES(7,to_date('20-02-2006 20:00','DD-MM-YYYY 
HH24:MI'),to_date('21-02-2006 21:30','DD-MM-YYYY HH24:MI'),1 , 5, 3,
            1,5,6,8,50,70);
INSERT INTO WET_wette
      VALUES(8,to_date('24-02-2006 20:30','DD-MM-YYYY 
HH24:MI'),to_date('24-02-2006 22:00','DD-MM-YYYY HH24:MI'),1, 2, 4,
                0,3,5,7 , 20,50);
INSERT INTO WET_wette
      VALUES(9,to_date('20-03-2006 14:00','DD-MM-YYYY 
HH24:MI'),to_date('29-03-2006 16:00','DD-MM-YYYY HH24:MI'), 1, 2, 0,
               2,6,9,6, 10,90);
INSERT INTO GWT_gespieltewetten
     VALUES(200, 2450,5);
INSERT INTO GWT_gespieltewetten
      VALUES(201, 2451,6);
INSERT INTO GWT_gespieltewetten
      VALUES(202, 2452,7);
INSERT INTO GWT_gespieltewetten
      VALUES(203,2453,8);
INSERT INTO GWT_gespieltewetten
      VALUES(204, 2454,9);
INSERT INTO KNT_konto
    VALUES(1263, 2450, 1500);
INSERT INTO KNT_konto
    VALUES(1223, 2451, 2.000);
INSERT INTO KNT_konto
    VALUES(1289, 2452, 3000);
INSERT INTO KNT_konto
    VALUES(1256, 2453, 1000);
INSERT INTO KNT_konto 
VALUES(1277, 2454, 1500);
INSERT INTO KBW_kontobewegung
     VALUES(8963, 1263, 100 , 150 , 50 ,80 , 
to_Date('03-03-2006','DD:MM:YYYY'));
INSERT INTO KBW_kontobewegung
     VALUES(8964, 1223, 500 , 50 , 50 , 800 , 
to_Date('01-02-2006','DD:MM:YYYY'));
INSERT INTO KBW_kontobewegung
     VALUES(8965, 1289, 110 , 60 , 70 , 100 , 
to_Date('12-02-2006','DD:MM:YYYY'));
INSERT INTO KBW_kontobewegung
     VALUES(8966, 1256, 400 , 30 , 35 , 60 , to_Date('24 
-05-2006','DD:MM:YYYY'));
INSERT INTO KBW_kontobewegung
      VALUES(8967, 1277, 300 , 50 , 20 , 80 , 
to_Date('28-01-2006','DD:MM:YYYY'));
INSERT INTO SSN_session
    VALUES(71, 2450); 
INSERT INTO SSN_session
    VALUES(72, 2451); 
INSERT INTO SSN_session
    VALUES(73, 2452); 
INSERT INTO SSN_session
    VALUES(74, 2453);
INSERT INTO SSN_session
    VALUES(75, 2454); 
The Bernardino
kurs/sql_depot.txt · Last modified: 2014/09/10 21:22 (external edit)