Nowe posty

Autor Wątek: ORACLE SQL Jak skonstruować warunki??  (Przeczytany 3279 razy)

Offline

  • Users
  • Nowy na forum
  • *
  • Wiadomości: 16
    • Zobacz profil
ORACLE SQL Jak skonstruować warunki??
« dnia: 2012-05-07, 17:26:40 »
witam wszystkich serdecznie. jestem trochę zielony z ORACLE SQL więc proszę o wyrozumiałość. potrzebuje przykładowych konstrukcji warunków które wymieniłem poniżej (są także zapisane jako komentarz w skrypcie).
oto mój skrypt bazy danych
DROP TABLE WIERSZE_ZAMOWIEN cascade constraints;
DROP TABLE ZAMOWIENIA cascade constraints;
DROP TABLE KLIENCI cascade constraints;
DROP TABLE CENY cascade constraints;
DROP TABLE WYROBY cascade constraints;
DROP TABLE POZIOMY_PENSJI cascade constraints;
DROP TABLE PRACOWNICY cascade constraints;
DROP TABLE ETATY cascade constraints;
DROP TABLE WYDZIALY cascade constraints;
DROP TABLE SIEDZIBY cascade constraints;


CREATE TABLE SIEDZIBY (
        ID_SIEDZIBY             NUMBER(3),
        ULICA          VARCHAR2(20));

CREATE TABLE WYDZIALY (
        ID_WYDZIALU           NUMBER(2),
        NAZWA                    VARCHAR2(20),
        ID_SIEDZIBY             NUMBER(3));

CREATE TABLE ETATY (
        ID_ETATU                  NUMBER(3),
        ETAT                VARCHAR2(30));

CREATE TABLE PRACOWNICY (
        ID_PRACOWNIKA             NUMBER(4),
        NAZWISKO               VARCHAR2(15),
        IMIE              VARCHAR2(15),
        DRUGIE_IMIE       VARCHAR2(15),
        PESEL             NUMBER (11),
        ID_ETATU                  NUMBER(3),
        ID_SZEFA              NUMBER(4),
        DATA_ZATRUDNIENIA               DATE,
        PENSJA                  NUMBER(7,2),
        PROWIZJA              NUMBER(7,2),
        ID_WYDZIALU           NUMBER(2));

CREATE TABLE POZIOMY_PENSJI (
        ID_POZIOMU                NUMBER(3),
        DOLNA_GRANICA             NUMBER(7,2),
        GORNA_GRANICA             NUMBER(7,2));

CREATE TABLE WYROBY (
        ID_PRODUKTU              NUMBER (6),
        NAZWA_PIWA             VARCHAR2 (20),
        ILOSC_ALK         NUMBER (2,1),
        EKSTRAKT          NUMBER (2),
        KOMENTARZ         VARCHAR2 (2000));
 
CREATE TABLE CENY (
        ID_PRODUKTU              NUMBER (6),
        CENA_NETTO              NUMBER (8,2),
        CENA_BRUTTO               NUMBER (8,2),
        DATA_WYPRODUKOWANIA              DATE,
        DATA_WYCOFANIA                DATE);

CREATE TABLE KLIENCI (
        ID_KLIENTA             NUMBER (6),
        NAZWA                    VARCHAR2 (45),
        ADRES                 VARCHAR2 (40),
        MIASTO                    VARCHAR2 (30),
        WOJEWODZTWO                   VARCHAR2 (15),
        KOD_POCZTOWY                VARCHAR2 (9),
        NR_KIERUNKOWY               NUMBER (3),
        TELEFON            NUMBER (9),
        ID_PRACOWNIKA          NUMBER (4));

CREATE TABLE ZAMOWIENIA  (
        ID_ZAMOWIENIA                NUMBER (4),
        DATA_ZAMOWIENIA              DATE,
        ID_KLIENTA             NUMBER (6),
        DATA_WYSYLKI               DATE,
        WARTOSC                   NUMBER (8,2));

CREATE TABLE WIERSZE_ZAMOWIEN  (
        ID_ZAMOWIENIA                NUMBER (4),
        ID_WIERSZA_ZAM                 NUMBER (4),
        ID_PRODUKTU              NUMBER (6),
        CENA            NUMBER (8,2),
        ILOSC                NUMBER (8),
        WARTOSC                   NUMBER (8,2));



GRANT SELECT ON SIEDZIBY TO PUBLIC;
GRANT SELECT ON WYDZIALY TO PUBLIC;
GRANT SELECT ON ETATY TO PUBLIC;
GRANT SELECT ON PRACOWNICY TO PUBLIC;
GRANT SELECT ON POZIOMY_PENSJI TO PUBLIC;
GRANT SELECT ON WYROBY TO PUBLIC;
GRANT SELECT ON CENY TO PUBLIC;
GRANT SELECT ON KLIENCI TO PUBLIC;
GRANT SELECT ON ZAMOWIENIA TO PUBLIC;
GRANT SELECT ON WIERSZE_ZAMOWIEN TO PUBLIC;


CREATE UNIQUE INDEX I_SIEDZIBY$ID_SIEDZIBY ON SIEDZIBY (ID_SIEDZIBY);
CREATE UNIQUE INDEX I_WYDZIALY$ID_WYDZIALU ON WYDZIALY (ID_WYDZIALU);
CREATE UNIQUE INDEX I_ETATY$ID_ETATU ON ETATY (ID_ETATU);
CREATE UNIQUE INDEX I_PRACOWNICY$ID_PRACOWNIKA ON PRACOWNICY (ID_PRACOWNIKA);
CREATE UNIQUE INDEX I_POZIOMY_PENSJI$ID_POZIOMU ON POZIOMY_PENSJI (ID_POZIOMU);
CREATE UNIQUE INDEX I_WYROBY$ID_PRODUKTU ON WYROBY (ID_PRODUKTU);
CREATE UNIQUE INDEX I_CENY ON CENY (ID_PRODUKTU, DATA_WYPRODUKOWANIA);
CREATE UNIQUE INDEX I_KLIENCI$ID_KLIENTA ON KLIENCI (ID_KLIENTA);
CREATE UNIQUE INDEX I_ZAMOWIENIA$ID_ZAMOWIENIA ON ZAMOWIENIA (ID_ZAMOWIENIA);
CREATE UNIQUE INDEX I_WIERSZE_ZAMOWIEN ON WIERSZE_ZAMOWIEN (ID_ZAMOWIENIA, ID_WIERSZA_ZAM);


/*OGRANICZENIA WYBORU TABELI KLIENCI*/
ALTER TABLE KLIENCI ADD
   CHECK (ID_KLIENTA IS NOT NULL AND ID_KLIENTA > 0);
ALTER TABLE KLIENCI ADD
   CHECK (ID_PRACOWNIKA IS NOT NULL AND ID_PRACOWNIKA > 0); /*ID_PRACOWNIKA =< PRACOWNICY.ID_PRACOWNIKA*/
ALTER TABLE KLIENCI ADD
   CHECK (NAZWA IS NOT NULL);
ALTER TABLE KLIENCI ADD
   CHECK (ADRES IS NOT NULL);
ALTER TABLE KLIENCI ADD
   CHECK (MIASTO IS NOT NULL);
ALTER TABLE KLIENCI ADD
   CHECK (NR_KIERUNKOWY IS NOT NULL AND NR_KIERUNKOWY >0);
ALTER TABLE KLIENCI ADD
   CHECK (TELEFON IS NOT NULL AND TELEFON >=0); /*TELEFON >=0000000 (SIEDMIO LICZBOWY NR MINIMALNIE)*/
ALTER TABLE KLIENCI ADD
   CHECK (WOJEWODZTWO IS NOT NULL);
ALTER TABLE KLIENCI ADD
   CHECK (KOD_POCZTOWY IS NOT NULL AND KOD_POCZTOWY >0 ); /*KOD_POCZTOWY>=00000 (PIECIO LICZBOWY NR MINIMALNIE)*/

/*OGRANICZENIA WYBORU TABELI WYDZIALY*/
ALTER TABLE WYDZIALY ADD
   CHECK (ID_WYDZIALU IS NOT NULL AND ID_WYDZIALU > 0);
ALTER TABLE WYDZIALY ADD
   CHECK (NAZWA IS NOT NULL);
ALTER TABLE WYDZIALY ADD
   CHECK (ID_SIEDZIBY IS NOT NULL AND ID_SIEDZIBY > 0); /*ID_SIEDZIBY =< SIEDZIBY.ID_SIEDZIBY*/

/*OGRANICZENIA WYBORU TABELI PRACOWNICY*/  
ALTER TABLE PRACOWNICY ADD
   CHECK (ID_PRACOWNIKA IS NOT NULL AND ID_PRACOWNIKA > 0);
ALTER TABLE PRACOWNICY ADD
   CHECK (NAZWISKO IS NOT NULL);
ALTER TABLE PRACOWNICY ADD
   CHECK (IMIE IS NOT NULL);
ALTER TABLE PRACOWNICY ADD
   CHECK (DRUGIE_IMIE IS NOT NULL OR DRUGIE_IMIE IS NULL);
ALTER TABLE PRACOWNICY ADD
   CHECK (ID_WYDZIALU IS NOT NULL AND ID_WYDZIALU > 0); /*ID_WYDZIALU =< WYDZIALY.ID_WYDZIALU*/
ALTER TABLE PRACOWNICY ADD
   CHECK (PESEL IS NOT NULL); /*PESEL = 11*/
ALTER TABLE PRACOWNICY ADD
   CHECK (ID_ETATU IS NOT NULL AND ID_ETATU > 0); /*ID_ETATU =< ETATY.ID_ETATU*/
ALTER TABLE PRACOWNICY ADD
   CHECK (ID_SZEFA IS NOT NULL AND ID_SZEFA > 0); /*ID_SZEFA =< PRACOWNICY.ID_PRACOWNIKA*/
ALTER TABLE PRACOWNICY ADD
   CHECK (DATA_ZATRUDNIENIA IS NOT NULL AND DATA_ZATRUDNIENIA >= TO_DATE('01/09/2003', 'DD/MM/YYYY'));
ALTER TABLE PRACOWNICY ADD
   CHECK (PENSJA IS NOT NULL AND PENSJA >= 1500); /*PENSJA >=PENSJA.DOLNA_GRANICA AND PENSJA =ALTER TABLE PRACOWNICY ADD
   CHECK (PROWIZJA IS NOT NULL OR PROWIZJA IN NULL);
ALTER TABLE PRACOWNICY ADD
   CHECK (PROWIZJA > 0 AND PROWIZJA < 500);

/*OGRANICZENIA WYBORU TABELI WIERSZE_ZAMOWIEN*/  
ALTER TABLE WIERSZE_ZAMOWIEN ADD
   CHECK (ID_ZAMOWIENIA IS NOT NULL AND ID_ZAMOWIENIA >0); /*ID_ZAMOWIENIA =< ZAMOWIENIA.ID_ZAMOWIENIA*/
ALTER TABLE WIERSZE_ZAMOWIEN ADD
   CHECK (ID_WIERSZA_ZAM IS NOT NULL AND ID_WIERSZA_ZAM >0);
ALTER TABLE WIERSZE_ZAMOWIEN ADD
   CHECK (ID_PRODUKTU IS NOT NULL AND ID_PRODUKTU >0); /*ID_PRODUKTU =< WYROBY.ID_PRODUKTU*/
ALTER TABLE WIERSZE_ZAMOWIEN ADD
   CHECK (CENA IS NOT NULL AND CENA >0.0 AND CENA<15.00); /*CENA=CENY.CENA_BRUTTO PRZYPISANE WARTOSCI AUTOMATYCZNE*/
ALTER TABLE WIERSZE_ZAMOWIEN ADD
   CHECK (ILOSC IS NOT NULL AND ILOSC >0);
ALTER TABLE WIERSZE_ZAMOWIEN ADD
   CHECK (WARTOSC IS NOT NULL AND WARTOSC=CENA*ILOSC);

/*OGRANICZENIA WYBORU TABELI ETATY*/  
ALTER TABLE ETATY ADD
   CHECK (ID_ETATU IS NOT NULL AND ID_ETATU > 0);
ALTER TABLE ETATY ADD
   CHECK (ETAT IS NOT NULL);

/*OGRANICZENIA WYBORU TABELI SIEDZIBY*/  
ALTER TABLE SIEDZIBY ADD
   CHECK (ID_SIEDZIBY IS NOT NULL AND ID_SIEDZIBY > 0);
ALTER TABLE SIEDZIBY ADD
   CHECK (ULICA IS NOT NULL);
   
/*OGRANICZENIA WYBORU TABELI CENY*/  
ALTER TABLE CENY ADD
   CHECK (ID_PRODUKTU IS NOT NULL AND ID_PRODUKTU > 0); /*ID_PRODUKTU =< WYROBY.ID_PRODUKTU*/
ALTER TABLE CENY ADD
   CHECK (DATA_WYPRODUKOWANIA IS NOT NULL);
ALTER TABLE CENY ADD
   CHECK (CENA_NETTO > 0 AND CENA_BRUTTO > 0);
ALTER TABLE CENY ADD
   CHECK (CENA_BRUTTO=CENA_NETTO*1.23);
ALTER TABLE CENY ADD
   CHECK (CENA_NETTO IS NULL OR CENA_BRUTTO IS NULL OR CENA_NETTO <= CENA_BRUTTO);
ALTER TABLE CENY ADD
   CHECK (DATA_WYCOFANIA IS NULL AND DATA_WYCOFANIA IS NOT NULL OR DATA_WYPRODUKOWANIA <= DATA_WYCOFANIA);
ALTER TABLE CENY ADD
   CHECK (DATA_WYCOFANIA >=TO_DATE('01/01/2012', 'DD/MM/YYYY') AND DATA_WYPRODUKOWANIA >=TO_DATE('01/01/2012', 'DD/MM/YYYY') );

/*OGRANICZENIA WYBORU TABELI WYROBY*/
ALTER TABLE WYROBY ADD
   CHECK (ID_PRODUKTU IS NOT NULL AND ID_PRODUKTU > 0);
ALTER TABLE WYROBY ADD
   CHECK (NAZWA_PIWA IS NOT NULL);
ALTER TABLE WYROBY ADD
   CHECK (ILOSC_ALK IS NOT NULL AND ILOSC_ALK >0.0);
ALTER TABLE WYROBY ADD
   CHECK (EKSTRAKT IS NOT NULL AND EKSTRAKT >=10);

/*OGRANICZENIA WYBORU TABELI POZIOMY_PENSJI*/    
ALTER TABLE POZIOMY_PENSJI ADD
   CHECK (ID_POZIOMU IS NOT NULL AND ID_POZIOMU > 0);
ALTER TABLE POZIOMY_PENSJI ADD
   CHECK (DOLNA_GRANICA >=1500.00 AND GORNA_GRANICA >=2000.00);
/*ALTER TABLE POZIOMY_PENSJI ADD
   CHECK (DOLNA_GRANICA =<3501.99 AND GORNA_GRANICA =<9999.99);*/
ALTER TABLE POZIOMY_PENSJI ADD
   CHECK ((DOLNA_GRANICA IS NULL AND GORNA_GRANICA IS NOT NULL) OR (DOLNA_GRANICA IS NOT NULL AND GORNA_GRANICA IS NULL) OR (DOLNA_GRANICA <= GORNA_GRANICA));

/*OGRANICZENIA WYBORU TABELI ZAMOWIENIA*/
ALTER TABLE ZAMOWIENIA ADD
   CHECK (ID_ZAMOWIENIA IS NOT NULL AND ID_ZAMOWIENIA > 0);
ALTER TABLE ZAMOWIENIA ADD
   CHECK (ID_KLIENTA IS NOT NULL AND ID_KLIENTA >0); /*ID_KLIENTA =< KLIENCI.ID_KLIENTA*/
ALTER TABLE ZAMOWIENIA ADD
   CHECK (DATA_ZAMOWIENIA >=TO_DATE('01/01/2012', 'DD/MM/YYYY') AND DATA_WYSYLKI >=TO_DATE('01/01/2012', 'DD/MM/YYYY'));
ALTER TABLE ZAMOWIENIA ADD
   CHECK ((DATA_ZAMOWIENIA IS NOT NULL AND DATA_WYSYLKI IS NOT NULL) OR (DATA_WYSYLKI IS NULL) OR (DATA_ZAMOWIENIA>=DATA_WYSYLKI));
ALTER TABLE ZAMOWIENIA ADD
   CHECK (WARTOSC >= 0); /*WARTOSC=WIERSZE_ZAMOWIEN.WARTOSC PRZYPISANE WARTOSCI AUTOMATYCZNE*/

/*KLUCZE PODSTAWOWE*/
ALTER TABLE KLIENCI ADD
   PRIMARY KEY (ID_KLIENTA);
ALTER TABLE WYDZIALY ADD
   PRIMARY KEY (ID_WYDZIALU);
ALTER TABLE PRACOWNICY ADD
   PRIMARY KEY (ID_PRACOWNIKA);
ALTER TABLE WIERSZE_ZAMOWIEN ADD
   PRIMARY KEY (ID_ZAMOWIENIA, ID_WIERSZA_ZAM);
ALTER TABLE ETATY ADD
   PRIMARY KEY (ID_ETATU);
ALTER TABLE SIEDZIBY ADD
   PRIMARY KEY (ID_SIEDZIBY);
ALTER TABLE CENY ADD
   PRIMARY KEY (ID_PRODUKTU, DATA_WYPRODUKOWANIA);
ALTER TABLE WYROBY ADD
   PRIMARY KEY (ID_PRODUKTU);
ALTER TABLE POZIOMY_PENSJI ADD
   PRIMARY KEY (ID_POZIOMU);
ALTER TABLE ZAMOWIENIA ADD
   PRIMARY KEY (ID_ZAMOWIENIA);

/*KLUCZE OBCE*/
ALTER TABLE KLIENCI ADD
   FOREIGN KEY (ID_PRACOWNIKA) REFERENCES PRACOWNICY;
ALTER TABLE WYDZIALY ADD
   FOREIGN KEY (ID_SIEDZIBY) REFERENCES SIEDZIBY;
ALTER TABLE PRACOWNICY ADD
   FOREIGN KEY (ID_ETATU) REFERENCES ETATY;
ALTER TABLE PRACOWNICY ADD
   FOREIGN KEY (ID_SZEFA) REFERENCES PRACOWNICY;
ALTER TABLE PRACOWNICY ADD
   FOREIGN KEY (ID_WYDZIALU) REFERENCES WYDZIALY;
ALTER TABLE WIERSZE_ZAMOWIEN ADD
   FOREIGN KEY (ID_ZAMOWIENIA) REFERENCES ZAMOWIENIA;
ALTER TABLE WIERSZE_ZAMOWIEN ADD
   FOREIGN KEY (ID_PRODUKTU) REFERENCES WYROBY;
ALTER TABLE CENY ADD
   FOREIGN KEY (ID_PRODUKTU) REFERENCES WYROBY;
ALTER TABLE ZAMOWIENIA ADD
   FOREIGN KEY (ID_KLIENTA) REFERENCES KLIENCI;

exit;
1) jak skonstruować warunek który będzie odwoływał się do innej tabeli i nie będzie przekraczana jej wartość. przykład:
ALTER TABLE ZAMOWIENIA ADD 
   CHECK (ID_KLIENTA IS NOT NULL AND ID_KLIENTA >0); /*ID_KLIENTA =< KLIENCI.ID_KLIENTA*/
2)wartość obliczona w innej tabeli będzie przypisywana do atrybutu w innej tabeli z uwzględnieniem  jednego zamowienia (id_zamowienia w tabeli wiersze_zamowienia) przykład:
ALTER TABLE ZAMOWIENIA ADD 
   CHECK (WARTOSC >= 0); /*WARTOSC=WIERSZE_ZAMOWIEN.WARTOSC PRZYPISANE WARTOSCI AUTOMATYCZNE*/
3) przypisanie wartości z innej tabeli automatyczne. przykład:
ALTER TABLE WIERSZE_ZAMOWIEN ADD 
   CHECK (CENA IS NOT NULL AND CENA >0.0 AND CENA<15.00); /*CENA=CENY.CENA_BRUTTO PRZYPISANE WARTOSCI AUTOMATYCZNE*/
4) wpisanie dokładnie takiej ilości znaków jaka jest podana np w peselu. przykład:
ALTER TABLE PRACOWNICY ADD 
   CHECK (PESEL IS NOT NULL); /*PESEL = 11*/
5) proszę o poprawienie składni bo wywala mi błąd
ALTER TABLE POZIOMY_PENSJI ADD 
   CHECK (DOLNA_GRANICA =<3501.99 AND GORNA_GRANICA =<9999.99);
bardzo proszę o pomoc. jest to projekt na zaliczenie przedmiotu na studiach. z góry dziękuje wszyskim za pomoc:-D