Baza Oracle do ćwiczeń

/* Schemat bazy laboratoryjnej dla bazy Oracle Express*/

/* Usuwanie tabel, jeśli były poprzednio utworzone */
DROP TABLE Dostawy;
DROP SEQUENCE dostawy_seq;
DROP TABLE Oferty_dostawcow;
DROP TABLE Dostawcy;
DROP SEQUENCE dostawcy_seq;
DROP TABLE Pozycje_zamowien;
DROP TABLE Towary;
DROP SEQUENCE towary_seq;
DROP TABLE Zamowienia;
DROP SEQUENCE zamowienia_seq;
DROP TABLE Klienci;
DROP SEQUENCE klienci_seq;

/* Ustawienie formatu daty*/

ALTER SESSION SET nls_date_format = 'DD/MM/YYYY';

/* Tworzenie tabeli Klienci (w nazwach tabel i kolumn nie ma polskich znaków)
Nazwa_firmy, Adres, Miasto nie mogą być niewypełnione, trzeba je zawsze podać (ograniczenie not null) */
CREATE TABLE Klienci(
    Id_klienta      INTEGER,
    Nazwa_firmy     varchar2(40) NOT NULL,
    Adres       varchar2(80) NOT NULL,
    Miasto      varchar2(40) NOT NULL,
    Nr_tel      varchar2(20),
    Nr_faksu    varchar2(20)
);
/*Tworzenie sekwencji klienci_seq i trigera inkrementującego klucz główny*/
CREATE SEQUENCE klienci_seq;
CREATE OR REPLACE TRIGGER klienci_trg
BEFORE INSERT ON Klienci
FOR EACH ROW
BEGIN
  :NEW.Id_klienta := klienci_seq.NEXTVAL;
END;
/
/* Dodawanie do tabeli klucza głównego – kolumny o unikalnych wartościach dla każdego wiersza osobno */
ALTER TABLE Klienci ADD CONSTRAINT klienci_pk PRIMARY KEY(Id_klienta);
/* Dodawanie danych do tabeli Klienci
insert into Klienci(Id_klienta,Nazwa_firmy,Adres,Miasto,Nr_tel,Nr_faksu)
values(null, 'Sortex', 'ul. Dziwna 5', 'Warszawa', '223456789', '223456788');
insert into Klienci(Id_klienta,Nazwa_firmy,Adres,Miasto,Nr_tel,Nr_faksu)
values(null, 'Evtex', 'ul. Kolorowa 2', 'Kraków', '343456711', '343456710');
insert into Klienci(Id_klienta,Nazwa_firmy,Adres,Miasto,Nr_tel,Nr_faksu)
values(null, 'Hortex', 'ul. Fajowa 3', 'Poznań', '123456733', '123456734');
 */
--generuj dane dla klientów
CREATE OR REPLACE PROCEDURE klienci_gen AS
    ile NUMBER(10);
    TYPE TABSTR IS TABLE OF VARCHAR2(250);
    nazwa TABSTR;
    ulica TABSTR;
    miasto TABSTR;
    ile_nazw NUMBER(10);
    ile_ulic NUMBER(10);
    ile_miast NUMBER(10);
    telefon NUMBER(10);
    wybor_nazwy NUMBER(10);
    wybor_ulicy NUMBER(10);
    wybor_miasta NUMBER(10);
    ulnum NUMBER(10);
BEGIN
    ile := 200;
    nazwa := TABSTR ('Sortex','Diabex','Wilemix','Evtex','Hetex','Iluminaks','Werrtiks','Adiabex',
    'Fedex','Direx','Goeks','frex');
    ile_nazw := 12;
    ulica := TABSTR ('Dziwna ','Kolorowa ','Fajowa ','Genialna ');
    ile_ulic := 4;
    miasto := TABSTR ('Warszawa','Kraków','Łódź','Olsztyn','Lublin','Poznań','Szczecin','Zakopane','Gdynia','Jelenia Góra');
    ile_miast := 10;
    FOR i IN 1..ile LOOP
        wybor_nazwy := dbms_random.VALUE(1,ile_nazw);
        wybor_ulicy := dbms_random.VALUE(1,ile_ulic);
        wybor_miasta := dbms_random.VALUE(1,ile_miast);
        telefon := dbms_random.VALUE(100000000,999999999);
        ulnum := dbms_random.VALUE(1,99);
        INSERT INTO Klienci VALUES (NULL, nazwa(wybor_nazwy) || to_char(ulnum,'99'),
        'ul. ' || ulica(wybor_ulicy) || to_char(ulnum,'99'), miasto(wybor_miasta), telefon, telefon+1);
    END LOOP;
    DBMS_OUTPUT.put_line('Wszyscy klienci dodani.');
END klienci_gen;
/
DELETE FROM Klienci;
EXECUTE klienci_gen();
SELECT * FROM klienci;



/* Tworzenie tabeli kursy */
CREATE TABLE Zamowienia(
    Nr_zam      INTEGER,
    Data_zam    DATE NOT NULL,
    Id_klienta      INTEGER,
    Wartosc_zam     NUMBER(20,2)
);
/*Tworzenie sekwencji zamowienia_seq i trigera inkrementującego klucz główny*/
CREATE SEQUENCE zamowienia_seq;
CREATE OR REPLACE TRIGGER zamowienia_trg
BEFORE INSERT ON Zamowienia
FOR EACH ROW
BEGIN
  :NEW.Nr_zam := zamowienia_seq.NEXTVAL;
END;
/
/* Dodawanie do tabeli klucza głównego – kolumny z indeksem i ograniczeniem unique */
ALTER TABLE Zamowienia ADD CONSTRAINT zamowienia_pk PRIMARY KEY(Nr_zam);
/* Dodawanie do tabeli klucza obcego – kolumny z powtarzającymi się wartościami pobranymi z klucza głównego innej tabeli (poprzez słowo kluczowe references) */
ALTER TABLE Zamowienia ADD CONSTRAINT zamowienia_fk1 FOREIGN KEY(Id_klienta) REFERENCES Klienci(Id_klienta);


/* Dodawanie danych do tabeli Zamowienia */
INSERT INTO Zamowienia(Nr_zam,Data_zam,Id_klienta,Wartosc_zam)
VALUES(NULL,TO_DATE('10/02/2015'),1,20000);
INSERT INTO Zamowienia(Nr_zam,Data_zam,Id_klienta,Wartosc_zam)
VALUES(NULL,TO_DATE('11/02/2015'),2,3000);
INSERT INTO Zamowienia(Nr_zam,Data_zam,Id_klienta,Wartosc_zam)
VALUES(NULL,TO_DATE('12/03/2015'),3,10000);
INSERT INTO Zamowienia(Nr_zam,Data_zam,Id_klienta,Wartosc_zam)
VALUES(NULL,TO_DATE('13/03/2015'),1,10000);
INSERT INTO Zamowienia(Nr_zam,Data_zam,Id_klienta,Wartosc_zam)
VALUES(NULL,TO_DATE('14/03/2015'),3,10000);
INSERT INTO Zamowienia(Nr_zam,Data_zam,Id_klienta,Wartosc_zam)
VALUES(NULL,TO_DATE('15/03/2015'),2,10000);
INSERT INTO Zamowienia(Nr_zam,Data_zam,Id_klienta,Wartosc_zam)
VALUES(NULL,TO_DATE('16/03/2015'),3,10000);


/* Wypisywanie zawartości tabeli Klienci */
SELECT * FROM Klienci;
/* wypisywanie zawartości tabeli Zamowienia */
SELECT * FROM Zamowienia;

/* Tworzenie tabeli Towary */
CREATE TABLE Towary(
Nr_katalog_towaru   INTEGER,
Rodzaj_towaru       varchar2(40),
Nazwa_producenta    varchar2(40),
Model           varchar2(40) NOT NULL,
Cena_sprzed     NUMBER(20,2),
Ilosc_w_mag     INTEGER
);
/*Tworzenie sekwencji towary_seq i trigera inkrementującego klucz główny*/
CREATE SEQUENCE towary_seq;
CREATE OR REPLACE TRIGGER towary_trg
BEFORE INSERT ON Towary
FOR EACH ROW
BEGIN
  :NEW.Nr_katalog_towaru := towary_seq.NEXTVAL;
END;
/

/* Dodawanie do tabeli klucza głównego – kolumny z indeksem i ograniczeniem unique */
ALTER TABLE Towary ADD CONSTRAINT towary_pk PRIMARY KEY(Nr_katalog_towaru);
/* Dodawanie ograniczenia check – innej wartości niż dni tygodnia nie da się wpisać */
ALTER TABLE Towary ADD CONSTRAINT towary_rodzaj_towaru_check
CHECK (Rodzaj_towaru IN ('Samochód', 'Rower', 'Namiot', 'Plecak', 'Śpiwór'));



/* Dodawanie danych do tabeli Towary */
INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Samochód','Honda','SX',40000,12);
INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Rower','PRS','FX',4000,32);
INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Plecak','PRS','EXtra',900,100);
INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Namiot','PRS','Extra',500,30);
INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Śpiwór','PRS','Extra',400,200);

INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Plecak','TWX','Super',800,100);
INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Namiot','TWX','Super',300,30);
INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Śpiwór','TWX','Super',200,200);

INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Plecak','XYZ','Sen',400,100);
INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Namiot','XYZ','Sen',200,30);
INSERT INTO Towary(Nr_katalog_towaru,Rodzaj_towaru,Nazwa_producenta,Model,Cena_sprzed,Ilosc_w_mag)
VALUES(NULL,'Śpiwór','XYZ','Sen',100,200);
/* Wypisywanie zawartości tabeli Towary */
SELECT * FROM Towary;

/* Tworzenie tabeli Pozycje_zamowien */
CREATE TABLE Pozycje_zamowien(
  Nr_zam        INTEGER,
  Nr_katalog_towaru     INTEGER,
  Ilosc_zam     INTEGER
);

/* Dodawanie do tabeli klucza obcego – kolumny z powtarzającymi się wartościami pobranymi z klucza głównego innej tabeli (poprzez słowo kluczowe references) */
ALTER TABLE Pozycje_zamowien ADD CONSTRAINT pozycje_zamowien_fk1 FOREIGN KEY(Nr_zam) REFERENCES Zamowienia(Nr_zam);
/* Dodawanie do tabeli klucza obcego – kolumny z powtarzającymi się wartościami pobranymi z klucza głównego innej tabeli (poprzez słowo kluczowe references) */
ALTER TABLE Pozycje_zamowien ADD CONSTRAINT pozycje_zamowien_fk2 FOREIGN KEY(Nr_katalog_towaru) REFERENCES Towary(Nr_katalog_towaru);
/* Dodawanie do tabeli klucza głównego – kolumny z indeksem i ograniczeniem unique */
ALTER TABLE Pozycje_zamowien ADD CONSTRAINT pozycje_zamowien_pk PRIMARY KEY(Nr_zam,Nr_katalog_towaru);
DELETE FROM Pozycje_zamowien;
/* Dodawanie danych do tabeli Pozycje_zamowien */
INSERT INTO Pozycje_zamowien(Nr_zam,Nr_katalog_towaru,Ilosc_zam)
VALUES(1,1,10);
INSERT INTO Pozycje_zamowien(Nr_zam,Nr_katalog_towaru,Ilosc_zam)
VALUES(1,2,1);
INSERT INTO Pozycje_zamowien(Nr_zam,Nr_katalog_towaru,Ilosc_zam)
VALUES(1,3,5);
INSERT INTO Pozycje_zamowien(Nr_zam,Nr_katalog_towaru,Ilosc_zam)
VALUES(1,4,7);
INSERT INTO Pozycje_zamowien(Nr_zam,Nr_katalog_towaru,Ilosc_zam)
VALUES(1,5,15);
INSERT INTO Pozycje_zamowien(Nr_zam,Nr_katalog_towaru,Ilosc_zam)
VALUES(1,6,15);


/* Tworzenie tabeli Dostawcy */
CREATE TABLE Dostawcy(
    Id_dostawcy     INTEGER,
    Nazwa_firmy     varchar2(40) NOT NULL,
    Adres       varchar2(80) NOT NULL,
    Miasto      varchar2(40) NOT NULL,
    Kraj        varchar2(40) NOT NULL
);
/*Tworzenie sekwencji dostawcy_seq i trigera inkrementującego klucz główny*/
CREATE SEQUENCE dostawcy_seq;
CREATE OR REPLACE TRIGGER dostawcy_trg
BEFORE INSERT ON Dostawcy
FOR EACH ROW
BEGIN
  :NEW.Id_dostawcy := dostawcy_seq.NEXTVAL;
END;
/
/* Dodawanie do tabeli klucza głównego – kolumny z indeksem i ograniczeniem unique */
ALTER TABLE Dostawcy ADD CONSTRAINT dostawcy_pk PRIMARY KEY(Id_dostawcy);
/* Dodawanie danych do tabeli Dostawcy */
INSERT INTO Dostawcy(Id_dostawcy,Nazwa_firmy,Adres,Miasto,Kraj)
VALUES(NULL,'Intrex','ul. Felgi 3','Warszawa','Polska');
INSERT INTO Dostawcy(Id_dostawcy,Nazwa_firmy,Adres,Miasto,Kraj)
VALUES(NULL,'Solexus','ul. Gitza 3','Wiedeń','Austria');
INSERT INTO Dostawcy(Id_dostawcy,Nazwa_firmy,Adres,Miasto,Kraj)
VALUES(NULL,'Denvus','ul. Kościuszki 3','Washington','USA');

/* Tworzenie tabeli Oferty_dostawcow */
CREATE TABLE Oferty_dostawcow(
  Id_dostawcy       INTEGER,
  Nr_katalog_towaru     INTEGER,
  Cena          INTEGER
);

/* Dodawanie do tabeli klucza obcego – kolumny z powtarzającymi się wartościami pobranymi z klucza głównego innej tabeli (poprzez słowo kluczowe references) */
ALTER TABLE Oferty_dostawcow ADD CONSTRAINT oferty_dostawcow_fk1 FOREIGN KEY(Id_dostawcy) REFERENCES Dostawcy(Id_dostawcy);
/* Dodawanie do tabeli klucza obcego – kolumny z powtarzającymi się wartościami pobranymi z klucza głównego innej tabeli (poprzez słowo kluczowe references) */
ALTER TABLE Oferty_dostawcow ADD CONSTRAINT oferty_dostawcow_fk2 FOREIGN KEY(Nr_katalog_towaru) REFERENCES Towary(Nr_katalog_towaru);
/* Dodawanie do tabeli klucza głównego – kolumny z indeksem i ograniczeniem unique */
ALTER TABLE Oferty_dostawcow ADD CONSTRAINT oferty_dostawcow_pk PRIMARY KEY(Id_dostawcy,Nr_katalog_towaru);

/* Dodawanie danych do tabeli Oferty_dostawcow */
INSERT INTO Oferty_dostawcow(Id_dostawcy,Nr_katalog_towaru,Cena)
VALUES(1,1,150);
INSERT INTO Oferty_dostawcow(Id_dostawcy,Nr_katalog_towaru,Cena)
VALUES(2,1,350);
INSERT INTO Oferty_dostawcow(Id_dostawcy,Nr_katalog_towaru,Cena)
VALUES(3,1,150);
INSERT INTO Oferty_dostawcow(Id_dostawcy,Nr_katalog_towaru,Cena)
VALUES(1,2,100);
INSERT INTO Oferty_dostawcow(Id_dostawcy,Nr_katalog_towaru,Cena)
VALUES(2,2,500);
INSERT INTO Oferty_dostawcow(Id_dostawcy,Nr_katalog_towaru,Cena)
VALUES(3,2,200);
INSERT INTO Oferty_dostawcow(Id_dostawcy,Nr_katalog_towaru,Cena)
VALUES(1,4,111);

/* Tworzenie tabeli Dostawy */
CREATE TABLE Dostawy(
    Nr_dostawy  INTEGER,
    Data_zam    DATE NOT NULL,
    Data_przyjecia  DATE,
    Id_dostawcy     INTEGER NOT NULL,
    Nr_katalog_towaru INTEGER NOT NULL,
    Ilosc       INTEGER NOT NULL
);
/*Tworzenie sekwencji dostawy_seq i trigera inkrementującego klucz główny*/
CREATE SEQUENCE dostawy_seq;
CREATE OR REPLACE TRIGGER dostawy_trg
BEFORE INSERT ON Dostawy
FOR EACH ROW
BEGIN
  :NEW.Nr_dostawy := dostawy_seq.NEXTVAL;
END;
/
/* Dodawanie do tabeli klucza głównego – kolumny z indeksem i ograniczeniem unique */
ALTER TABLE Dostawy ADD CONSTRAINT dostawy_pk PRIMARY KEY(Nr_dostawy);
/* Dodawanie do tabeli klucza obcego – kolumny z powtarzającymi się wartościami pobranymi z klucza głównego innej tabeli (poprzez słowo kluczowe references) */
ALTER TABLE Dostawy ADD CONSTRAINT dostawy_fk1 FOREIGN KEY(Id_dostawcy) REFERENCES Dostawcy(Id_dostawcy);
/* Dodawanie do tabeli klucza obcego – kolumny z powtarzającymi się wartościami pobranymi z klucza głównego innej tabeli (poprzez słowo kluczowe references) */
ALTER TABLE Dostawy ADD CONSTRAINT dostawy_fk2 FOREIGN KEY(Nr_katalog_towaru) REFERENCES Towary(Nr_katalog_towaru);

/* Dodawanie danych do tabeli Dostawy */
INSERT INTO Dostawy(Nr_dostawy,Data_zam,Data_przyjecia,Id_dostawcy,Nr_katalog_towaru,Ilosc)
VALUES(NULL,TO_DATE('10/12/2014'),TO_DATE('20/01/2015'),1,1,2);
INSERT INTO Dostawy(Nr_dostawy,Data_zam,Data_przyjecia,Id_dostawcy,Nr_katalog_towaru,Ilosc)
VALUES(NULL,TO_DATE('14/12/2014'),TO_DATE('24/02/2015'),2,10,20);