kurs:sql_depot
Differences
This shows you the differences between two versions of the page.
| — |
kurs:sql_depot [2014/09/10 21:22] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | |||
| + | |||
| + | ====== WETTENBÜRO DATENBANK TABLES ANLEGEN ====== | ||
| + | |||
| + | <code sql> | ||
| + | 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) | ||
| + | ); | ||
| + | |||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | 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) | ||
| + | ); | ||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | create table MNN_mannschaft ( | ||
| + | MNN_ID NUMBER(12) NOT NULL, | ||
| + | MNN_name VARCHAR(12)NOT NULL, | ||
| + | PRIMARY KEY(MNN_ID) | ||
| + | ); | ||
| + | </code> | ||
| + | <code sql> | ||
| + | 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) | ||
| + | ); | ||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | 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) | ||
| + | ); | ||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | 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) | ||
| + | ); | ||
| + | |||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | 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) | ||
| + | ); | ||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | 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) | ||
| + | ); | ||
| + | </code> | ||
| + | |||
| + | |||
| + | |||
| + | ====== DATEN INSERT FÜR WETTBÜRO TABLES====== | ||
| + | |||
| + | <code sql> | ||
| + | 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'); | ||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | 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'); | ||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | 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); | ||
| + | |||
| + | </code> | ||
| + | <code sql> | ||
| + | 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); | ||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | 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); | ||
| + | |||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | 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')); | ||
| + | |||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | 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); | ||
| + | </code> | ||
| + | |||
| + | == The Bernardino == | ||
kurs/sql_depot.txt · Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
