Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE uczestnicy ( ID_Ucz NUMBER(10) PRIMARY KEY, Imie VARCHAR2(20) NOT NULL, Nazwisko VARCHAR2(20) NOT NULL);
- CREATE TABLE referat (ID_Ref NUMBER(10) PRIMARY KEY, Nazwa_ref VARCHAR2(50) NOT NULL, Lang VARCHAR2(10), Rzutnik NUMBER(1));
- CREATE TABLE UczRef (ID NUMBER(10) PRIMARY KEY, ID_Ucz NUMBER(10) NOT NULL, ID_Ref NUMBER(10) NOT NULL, Gdzie VARCHAR2 (50) NOT NULL, Kiedy DATE NOT NULL);
- ALTER TABLE UczRef ADD CONSTRAINT FK_Ucz Foreign KEY ( ID_Ucz) references uczestnicy (ID_Ucz);
- ALTER TABLE UczRef ADD CONSTRAINT FK_Ref Foreign KEY ( ID_Ref) references referat (ID_Ref);
- --separator insert uczestnicy
- INSERT INTO uczestnicy VALUES (
- 1,
- 'Marcin',
- 'Mielecki'
- );
- INSERT INTO uczestnicy VALUES (
- 2,
- 'Karol',
- 'Jeziorowski'
- );
- INSERT INTO uczestnicy VALUES (
- 3,
- 'David',
- 'Halup'
- );
- INSERT INTO uczestnicy VALUES (
- 4,
- 'Michal',
- 'Kaczynski'
- );
- --Separator insert referat:
- INSERT INTO referat VALUES (
- 1,
- 'Nazwa 1',
- 'ENG',
- 0
- );
- INSERT INTO referat VALUES (
- 2,
- 'Nazwa 2',
- 'GER',
- 1
- );
- INSERT INTO referat VALUES (
- 3,
- 'Nazwa 3',
- 'PL',
- 1
- );
- INSERT INTO referat VALUES (
- 4,
- 'Nazwa 4',
- 'ENG',
- 0
- );
- --Separator insert UczRef
- INSERT INTO uczref VALUES (
- 1,
- 1,
- 1,
- 'Tu',
- TO_DATE('01.02.2006','dd.mm.yy')
- );
- INSERT INTO uczref VALUES (
- 2,
- 1,
- 2,
- 'Tu',
- TO_DATE('01.02.2006','dd.mm.yy')
- );
- INSERT INTO uczref VALUES (
- 3,
- 2,
- 1,
- 'Tu',
- TO_DATE('01.02.2006','dd.mm.yy')
- );
- INSERT INTO uczref VALUES (
- 4,
- 2,
- 2,
- 'Nie wiem',
- TO_DATE('09.02.2006','dd.mm.yy')
- );
- INSERT INTO uczref VALUES (
- 5,
- 3,
- 2,
- 'gdzies',
- TO_DATE('03.02.2006','dd.mm.yy')
- );
- INSERT INTO uczref VALUES (
- 6,
- 3,
- 3,
- 'Tu',
- TO_DATE('08.02.2006','dd.mm.yy')
- );
- INSERT INTO uczref VALUES (
- 7,
- 4,
- 1,
- 'Tam',
- TO_DATE('06.02.2006','dd.mm.yy')
- );
- INSERT INTO uczref VALUES (
- 8,
- 4,
- 3,
- 'Tu',
- TO_DATE('04.02.2006','dd.mm.yy')
- );
- INSERT INTO uczref VALUES (
- 9,
- 4,
- 4,
- 'Tam',
- TO_DATE('02.02.2006','dd.mm.yy')
- );
- --separator widok:
- CREATE VIEW widok AS
- SELECT ur.id, u.Imie, U.Nazwisko, r.Nazwa_ref, ur.gdzie, ur.kiedy FROM UczRef ur, uczestnicy u, referat r WHERE u.ID_Ucz = ur.ID_Ucz AND r.ID_Ref = ur.ID_Ref ORDER BY id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement