Laboratoria

Laboratorium 1: podstawy SQL

create.sql

drop table wypozyczenia;
drop table czytelnicy;
drop table ksiazki;
 
create table ksiazki (
  nr_ew integer primary key,
  tytul varchar(128),
  autor varchar(64)
);
 
create table czytelnicy (
  pesel char(11) primary key,
  imie varchar(64),
  nazwisko varchar(64)
);
 
create table wypozyczenia (
  nr_ew integer references ksiazki,
  pesel char(11) references czytelnicy,
  data_wypozyczenia date,
  data_zwrotu date,
  primary key (nr_ew,pesel,data_wypozyczenia)
);

dane.sql

delete from wypozyczenia;
delete from ksiazki;
delete from czytelnicy;
 
insert into ksiazki (nr_ew,tytul,autor) values (1,'Teoria wszystkiego','J. Dziamdziak');
insert into ksiazki (nr_ew,tytul,autor) values (2,'Teoria prawie wszystkiego','J. Dziamdziak');
insert into ksiazki (nr_ew,tytul,autor) values (3,'Teoria całkowicie wszystkiego','J. Dziamdziak');
insert into ksiazki (nr_ew,tytul,autor) values (4,'Pan Tadeusz','A. Mickiewicz');
insert into ksiazki (nr_ew,tytul,autor) values (5,'Balladyna','J. Słowacki');
insert into ksiazki (nr_ew,tytul,autor) values (6,'Nieznana książka','Nieznany autor');
 
insert into czytelnicy (pesel,imie,nazwisko) values ('001','Jan','Kowalski');
insert into czytelnicy (pesel,imie,nazwisko) values ('002','Adam','Nowak');
insert into czytelnicy (pesel,imie,nazwisko) values ('003','Alojzy','Buczkowski');
insert into czytelnicy (pesel,imie,nazwisko) values ('004','Witold','Iksiński');
insert into czytelnicy (pesel,imie,nazwisko) values ('005','Anna','Ygregkowska');
insert into czytelnicy (pesel,imie,nazwisko) values ('006','Julia','Capuletti');
insert into czytelnicy (pesel,imie,nazwisko) values ('007','James','Bond');
insert into czytelnicy (pesel,imie,nazwisko) values ('008','Lidia','Montecchi');
 
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (1,'001','01-SEP-2007','15-SEP-2007');
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (1,'002','01-SEP-2006','15-SEP-2006');
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (1,'003','01-SEP-2004','15-SEP-2004');
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (1,'002','01-OCT-2007',NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (2,'005','01-SEP-2007','15-SEP-2007');
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (2,'006','04-OCT-2007',NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (2,'002',TO_DATE('2007-09-02','yyyy-mm-dd'),TO_DATE('2007-09-15','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'006',TO_DATE('2007-10-03','yyyy-mm-dd'),NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'007',TO_DATE('2007-09-04','yyyy-mm-dd'),TO_DATE('2007-09-15','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'001',TO_DATE('2007-09-20','yyyy-mm-dd'),TO_DATE('2007-09-25','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (3,'002',TO_DATE('2007-10-01','yyyy-mm-dd'),TO_DATE('2007-10-02','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (4,'008',TO_DATE('2007-10-02','yyyy-mm-dd'),NULL);
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (4,'005',TO_DATE('2007-09-07','yyyy-mm-dd'),TO_DATE('2007-09-12','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (4,'006',TO_DATE('2007-09-02','yyyy-mm-dd'),TO_DATE('2007-09-05','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (5,'006',TO_DATE('2007-09-05','yyyy-mm-dd'),TO_DATE('2007-09-10','yyyy-mm-dd'));
insert into wypozyczenia (nr_ew,pesel,data_wypozyczenia,data_zwrotu) values 
    (5,'002',TO_DATE('2007-09-19 12:30','yyyy-mm-dd hh24:mi'),TO_DATE('2007-09-20','yyyy-mm-dd'));

select.sql

-- inicjalizacja pomocniczych zmiennych

set pagesize 100;
set trimspool on;
set trimout on;
set echo on; -- wyświetlanie poleceń SQL
set linesize 100;

-- ustawienie domyślnych rozmiarów kolumn

column tytul format a30;
column autor format a20;

-- lista książek

SELECT nr_ew,tytul FROM ksiazki;

-- lista książek konkretnego autora

SELECT * FROM ksiazki WHERE autor LIKE 'J. Dziam%';

-- liczba wypożyczeń każdej z książek

SELECT k.tytul,count(*) as ile
  FROM wypozyczenia w,ksiazki k 
 WHERE w.nr_ew=k.nr_ew
 GROUP BY w.nr_ew,k.tytul
 ORDER BY ile DESC;

-- jeszcze nie oddane książki + dane wypożyczających

SELECT TO_CHAR(w.data_wypozyczenia,'yyyy-mm-dd hh24:mi') as data_wypozyczenia,w.nr_ew,k.tytul,c.imie||' '||c.nazwisko as wypozyczajacy 
  FROM wypozyczenia w,ksiazki k,czytelnicy c
 WHERE w.nr_ew=k.nr_ew AND w.pesel=c.pesel
   AND w.data_zwrotu IS NULL
 ORDER BY w.data_wypozyczenia ASC;

-- wyświetlanie dokładnego sposobu obliczania zapytania

set autotrace on;
select k.tytul,w.pesel,w.data_wypozyczenia,w.data_zwrotu from wypozyczenia w,ksiazki k WHERE w.nr_ew=k.nr_ew;
set autotrace off;

Laboratorium 2: pytania SQL

Plan zajęć:


  • przygotuj bazę zdefiniowaną w skrypcie

  • dodaj własne dane,
  • wykonaj podane niżej ćwiczenia.
  • Przydatne informacje:


    • funkcje dt. dat
    • sysdate -- zmienna zwracająca bieżąca date,
    • NVL(x,y) -- zwraca x jeśli x!=NULL, wpp. y,

    Ćwiczenie. Wypisz dokładne daty wypożyczeń (łącznie z godziną) książek które jeszcze nie zostały zwrócone.

    Ćwiczenie.Sprawdź poprawność wprowadzonych dat wypożyczeń i zwrotów książek, w szczególności:

    • czy data_zwrotu < data_wypożyczenia
    • czy data_wypożyczenia i data_zwrotu < aktualna data

    Ćwiczenie.Dla każdego wypożyczenia wypisz jego czas (również dla tych, które są jeszcze wypożyczone).



    Ćwiczenie.Wyznacz najdłużej wypożyczoną książkę (w tym te które są jeszcze wypożyczone).

    Ćwiczenie.Dla każdej książki wyznacz jest najkrótszy i najdłuższy czas wypożyczenia.

    Ćwiczenie.Dla każdej osoby wyznacz dla niej średni czas wypożyczenia książek (pomiń trwające wypożyczenia), posortuje wg. malejącej średniej.

    Ćwiczenie.Dla każdej książki wyznacz ile różnych osób ją wypożyczyło.

    Ćwiczenie.Wypisz tytuł i autora najczęściej wypożyczonej książki (lub książek jeśli jest wiele spełniających ten warunek).

    Ćwiczenie.Podaj książki, które były u czytelników w momencie 2008-09-15 12:00.

    Ćwiczenie.Podaj datę (najlepiej z dokładną godziną) podczas której było wypożyczonych najwięcej książek.

    Ćwiczenie.Sprawdź czy jakaś książka nie była wypożyczona dwa razy w tym samym momencie czasu.

    Laboratorium 3: Kolokwium A

    Kolokwium 1


    Baza zawiera dane o pewnej firmie. Są to informacje o pracownikach i ich bezpośrednich szefach (Pracownicy). Tabela ListaObcenosci zawiera dane o listach obecności, które codziennie są uzupełniane przez pracowników, którzy zapisują w nich swoje przyjście i wyjście z pracy (można założyć, że wejscie\(<\)wyjscie). Tabele Projekty i UczestnicyProjektow zawieraj¡ informacje o projektach, które są rozwijane w firmie.

    CREATE TABLE Pracownicy ( 
      id number PRIMARY KEY, 
      imie varchar(10) NOT NULL, 
      nazwisko varchar(10) NOT NULL, 
      pensja number DEFAULT 0 NOT NULL, 
      szef number REFERENCES Pracownicy
    );
    CREATE TABLE ListaObecnosci ( 
      pracownik number REFERENCES Pracownicy NOT NULL, 
      wejscie date NOT NULL, 
      wyjscie date
    );
    CREATE TABLE Projekty ( 
      id number PRIMARY KEY, 
      nazwa varchar(10) NOT NULL, 
      szef number REFERENCES Pracownicy, 
      rozpoczecie date NOT NULL, 
      zakonczenie date NOT NULL
    );
    CREATE TABLE UczestnicyProjektow ( 
      projekt number REFERENCES Projekty NOT NULL, 
      pracownik number REFERENCES Pracownicy NOT NULL, 
      PRIMARY KEY (projekt,pracownik)
    );

    Napisz następujące zapytania SQL:

    1. Wypisać zestawienie: imię, nazwisko, pensję pracownika, oraz łączną liczbę projektów, w których bierze (lub brał) on udział. Proszę pamiętać, również o tych pracownikach, którzy mają tę liczbę równą 0. Przez biorącego udział w projekcie rozumiemy osobę, dla której istnieje odpowiedni wpis w tabeli UczestnicyProjektow.
    2. Wypisać imię i nazwisko pracownika (lub pracowników jeśli będzie takich osób więcej) biorącego udział w największej liczbie projektów.
    3. Wypisać id, imię i nazwisko tych osób, które nigdy nie pojawiły się w pracy.
    4. Wypisać pracowników "oszustów" (imię + nazwisko), tzn. takich, dla których istnieją co najmniej dwa różne wpisy w ListaObecnosci obejmujące okresy mające niezerową część wspólną.
    5. Wypisać dla każdego pracownika (nawet "oszusta") łączną liczbę godzin przez niego przepracowanych (ale licząc już wg sprawiedliwych zasad, czyli "wielokrotnaobecność liczy się tylko raz). Zestawienie powinno obejmować: imię, nazwisko i liczbę godzin.
    6. Wypisać pracowników (imię + nazwisko), którzy mają co najmniej dwóch podwładnych (pośrednich lub bezpośrednich).
    7. Wypisać imiona, nazwiska pracowników, łączną liczbę ich bezpośrednich i pośrednich podwładnych.
    8. Wypisać maksymalny okres (tzn. początek i koniec tego okresu), w którym nikogo nie było w pracy (na podstawie tabeli ListaObecnosci). Przyjmujemy, że zajmujemy się jedynie okresami zawartymi w 1.01.2007-31.12.2007.

    Kolokwium 2


    Struktura bazy danych:

    DROP TABLE elementy_zestawow;
    DROP TABLE czesci;
    DROP TABLE zestawy;
     
    CREATE TABLE czesci (
        id int PRIMARY KEY,
        nazwa varchar(16),
        rodzaj varchar(16), /* np. cpu, karta graficzna, */
        cena number(10,2), /* koszt jednej części */
        magazyn int /* liczba elementów w magazynie, może być NULL */
    );
     
    CREATE TABLE zestawy (
        id int PRIMARY KEY,
        nazwa varchar(16),
        cena number(10,2) /* cena którą klient musi zapłacić za cały zestaw */
    );
     
    CREATE TABLE elementy_zestawow (
        zestaw int REFERENCES zestawy,
        czesc int REFERENCES czesci,
        liczba int,
        constraint c_liczba CHECK (liczba>0 AND liczba <10),
        UNIQUE(zestaw,czesc)
    );

    Baza przedstawia system magazynowy pewnej firmy sprzedającej zestawy komputerowe.
    Napisz następujące zapytania SQL:

    1. Wypisz dla każdego rodzaju części minimalną i maksymalną cenę części tego rodzaju.
    2. Wypisz części które występują w tabeli czesci, ale brak ich w magazynie.
    3. Wypisz id, nazwę, cenę oraz koszt zestawów (przez koszt rozumiemy sumę kosztów elementów zestawu).
    4. Wypisz id i nazwę zestawów, które mogą być wykonane w części znajdujących się w magazynie (pamięta o sprawdzeniu czy w magazynie jest odpowiednia liczba części).
    5. Wypisz pary zestawów, które składają się z dokładnie takiego samego zbioru części (pomijamy liczbę części).
    6. Wypisz części, które nie są wykorzystywane w żadnym zestawie ale znajdują się w magazynie.
    7. Wypisz id i nazwę części oraz liczbę zestawów w których jest ona wykorzystywana.
    8. Wypisz id i nazwę zestawu, oraz liczbę różnych części, które należy dokupić do magazynu, aby było możliwe wykonanie zestawu (np. jeśli brakuje 5 części o id 100 i 6 części o id 101, to odpowiedzią jest 2).
    9. Wypisz id i nazwę zestawu, oraz liczbę różnych rodzajów części wchodzących w jego skład.
    10. Wypisz id i nazwy zestawów, które można wykonać przy założeniu, że możemy wymienić brakujące części przez inne (ale tego samego rodzaju i w tej samej ilości). Pamiętaj, że w skład zestawu może wchodzić więcej niż jedna część jednego rodzaju.

    Laboratorium 4: Kolokwium B

    Kolokwium 1


    Baza zawiera informacje o przebiegu konkursów informatycznych. Są to informacje o konkursach (Konkursy), uczestnikach konkursów (UczestnicyKonkursow) oraz zgłoszeniach (Zgloszenia). W każdym konkursie najlepsze zespoły otrzymują nagrodę pieniężną (w wysokości zdefiniowanej przez kolumnę nagroda),
    liczba nagród jest jest zapisana w kolumnie liczba_nagrod. W przypadku remisów pomiędzy drużynami oczywiście przyznawana jest odpowiednio większa liczba nagród.

    W tabli UczestnicyKonkursow zawarta jest informacja o drużynach biorących udział w poszczególnych konkursach.

    Tabela Zgloszenia zawiera informacje o wszystkich zgłoszeniach. Kolumna data_zgloszenia zawiera informację o dokładnym czasie wysłania rozwiązania.

    Natomiast kolumna ok zawiera informację czy zgłoszenie zostało zaakceptowane (wartość 1) czy nie (wartość 0). Należy zwrócić uwagę, że w trakcie trwania konkursu drużna mogła przez przypadek zgłosić kilka rozwiązań, jednak po pierwszym zaakceptowaniu zadania wszystkie kolejna zgłoszenia powinny być ignorowane.

    Konkurs wygrywa drużyna, która rozwiąże największą liczbę zadań, jeśli jest wiele takich drużyn, to drugim kryterium jest najmniejszy czas:

    • dla wszystkich rozwiązanych przez drużynę zadań, sumowany jest czas od momentu rozpoczęcia konkursu do pierwszego zaakceptowanego rozwiązania tego zadania, dodatkowo za każde wcześniejsze zgłoszenie doliczana jest kara 20 minut.

    Przykładowo, jeśli drużyna rozwiązała zadanie A (nieprawidłowe zgłoszenia w 10 i 15 minucie, prawidłowe zgłoszenie w 25 minucie) i zadanie B (prawidłowe zgłoszenie w 60 minucie) to karny czas wynosi 25+2*20+60=125 minut.

    create table Konkursy (                                                                           
      nazwa varchar(20) primary key,                                                                  
      nagroda number default '100' not null                                                           
          check (nagroda between 1 and 1000),                                                         
      liczba_nagrod number default '1' not null                                                       
          check (liczba_nagrod between 1 and 10),                                                     
      poczatek date not null,                                                                          
      koniec date not null                                                                            
    );                                                                                                
     
    create table UczestnicyKonkursow (                                                                
      konkurs varchar(20) references Konkursy not null,                                               
      zespol varchar(20) not NULL,                                                                    
      primary key (konkurs,zespol)                                                                    
    );                                                                                                
     
    create table Zgloszenia (                                                                         
      id number primary key,                                                                          
      konkurs varchar(20) not null,                                                                   
      zespol varchar(20) not null,                                                                    
      zadanie char(1) not null,                                                                       
      data_zgloszenia date not null,                                                                  
      ok number not null                                                                              
          check (ok between 0 and 1),                                                                 
      foreign key (konkurs,zespol) references UczestnicyKonkursow                                     
    );

    Napisz następujące zapytania SQL:

    1. wypisz nazwy i daty rozpoczęcia konkursów, uporządkowane malejąco według puli nagród (w przypadku takie samej puli, rosnąco według nazw),
    2. wypisz nazwy konkursów, liczbę drużyn które brały w nim udział, oraz liczbę zadań, które zostały rozwiązane. Wynik uporządkuj rosnąco według nazw konkursów.
    3. wypisz identyfikatory (uporządkowane rosnąco) nieprawidłowych zgłoszeń, tzn. takich, które zostały przesłane poza czasem trwania konkursu
    4. wypisz identyfikator, konkurs, drużynę i zadanie dla zgłoszeń przesłanych już po zaakceptowaniu innego zgłoszenia tego samego zadania (oczywiście przez ten sam zespół, w tym samym konkursie) uporządkowane rosnąco według czasów zgłoszeń,
    5. wypisz raport zawierający nazwę konkursu, nazwę zadania, oraz czas (w minutach) po którym po raz pierwszy rozwiązano to zadanie w tym konkursie. Pomiń zadania które nie zostały nigdy rozwiązane.
    6. wypisz raport zawierający nazwę konkursu, nazwę drużyny, liczbę rozwiązanych zadań (w tym konkursie), oraz karny czas, uporządkowany rosnąco według nazwy konkursu, oraz pozycji w rankingu konkursowym
    7. wypisz raport zawierający nazwę drużyny oraz sumę nagród uporządkowany malejąco według sumy nagród
    8. wypisz identyfikatory (uporządkowane rosnąco) zgłoszeń dla których zamiana oceny z 0 na 1 powodowałaby, że drużyna, która poprzednio nie wygrała konkursu, zmieniła by swoją pozycję w rankingu na pierwsze miejsce (tzn. żadna inna drużyna nie byłaby w takim przypadku lepsza).

    Kolokwium 2


    Baza zawiera informacje o przebiegu konkursów informatycznych. Są to informacje o konkursach (Konkursy), uczestnikach konkursów (UczestnicyKonkursow) oraz zgłoszeniach (Zgloszenia). W każdym konkursie najlepsze zespoły otrzymują nagrodę pieniężną (w wysokości zdefiniowanej przez kolumnę nagroda),
    liczba nagród jest jest zapisana w kolumnie liczba_nagrod. W przypadku remisów pomiędzy drużynami oczywiście przyznawana jest odpowiednio większa liczba nagród.

    W tabli UczestnicyKonkursow zawarta jest informacja o drużynach biorących udział w poszczególnych konkursach.

    Tabela Zgloszenia zawiera informacje o wszystkich zgłoszeniach. Kolumna data_zgloszenia zawiera informację o dokładnym czasie wysłania rozwiązania. Natomiast kolumna ok zawiera informację czy zgłoszenie zostało zaakceptowane (wartość 1) czy nie (wartość 0). Należy zwrócić uwagę, że w trakcie trwania
    konkursu drużna mogła przez przypadek zgłosić kilka rozwiązań, jednak po pierwszym zaakceptowaniu zadania wszystkie kolejna zgłoszenia powinny być ignorowane.

    Konkurs wygrywa drużyna, która rozwiąże największą liczbę zadań, jeśli jest wiele takich drużyn, to drugim kryterium jest najmniejszy czas:

    • dla wszystkich zgłoszeń zadań rozwiązanych przez drużynę, sumowany jest czas od momentu rozpoczęcia konkursu do czasu zgłoszenia pierwszego zaakceptowanego rozwiązania.

    Przykładowo, jeśli drużyna rozwiązała zadanie A (nieprawidłowe zgłoszenia w 10 i 15 minucie, prawidłowe zgłoszenie w 25 minucie) i zadanie B (prawidłowe zgłoszenie w 60 minucie) to karny czas wynosi 10 + 15 + 25 + 60 = 110 minut.

    create table Konkursy (                                                                           
      nazwa varchar(20) primary key,                                                                  
      nagroda number default '100' not null                                                           
          check (nagroda between 1 and 1000),                                                         
      liczba_nagrod number default '1' not null                                                       
          check (liczba_nagrod between 1 and 10),                                                     
      poczatek date not null,                                                                          
      koniec date not null                                                                            
    );                                                                                                
     
    create table UczestnicyKonkursow (                                                                
      konkurs varchar(20) references Konkursy not null,                                               
      zespol varchar(20) not NULL,                                                                    
      primary key (konkurs,zespol)                                                                    
    );                                                                                                
     
    create table Zgloszenia (                                                                         
      id number primary key,                                                                          
      konkurs varchar(20) not null,                                                                   
      zespol varchar(20) not null,                                                                    
      zadanie char(1) not null,                                                                       
      data_zgloszenia date not null,                                                                  
      ok number not null                                                                              
          check (ok between 0 and 1),                                                                 
      foreign key (konkurs,zespol) references UczestnicyKonkursow                                     
    );

    Napisz następujące zapytania SQL:

    1. wypisz identyfikatory zaakceptowanych zgłoszeń, uporządkowane rosnąco według czasu zgłoszenia, oraz nazwy drużyny (w tym wypadku należy wypisać nawet te zaakceptowane zgłoszenia, które powinny być ignorowane)
    2. wypisz nazwy drużyn, liczbę konkursów w których brały udział, oraz liczbę zadań, które zostały rozwiązane. Wynik uporządkuj rosnąco według nazw drużyn.
    3. wypisz identyfikatory (uporządkowane rosnąco) nieprawidłowych zgłoszeń, tzn. takich, które zostały przesłane po zakończeniu konkursu
    4. wypisz identyfikator, konkurs, drużynę i zadanie dla zgłoszeń które nie zostały zignorowane (czyli nie zostały przesyłane po zaakceptowaniu innego zgłoszenia tego samego zadania) uporządkowane rosnąco według czasów zgłoszeń,
    5. wypisz raport zawierający nazwę konkursu, nazwę zadania, oraz czas (w minutach) po którym po raz ostatni rozwiązano to zadanie w tym konkursie (oczywiście pomijamy ignorowane zgłoszenia). Pomiń zadania które nie zostały nigdy rozwiązane.
    6. wypisz raport zawierający nazwę konkursu, nazwę drużyny, liczbę rozwiązanych zadań (w tym konkursie), oraz karny czas, uporządkowany rosnąco według nazwy konkursu, oraz pozycji w rankingu konkursowym
    7. wypisz raport zawierający nazwę drużyny oraz sumę nagród uporządkowany malejąco według sumy nagród
    8. wypisz identyfikatory (uporządkowane rosnąco) zgłoszeń dla których zamiana oceny z 0 na 1 powodowałaby, że drużyna, która poprzednio nie wygrała konkursu, zmieniła by swoją pozycję w rankingu na pierwsze miejsce (tzn. żadna inna drużyna nie byłaby w takim przypadku lepsza).

    Skrypty tworzące bazę danych


    create table Konkursy (
      nazwa varchar(20) primary key,
      nagroda number default '100' not null
          check (nagroda between 1 and 1000),
      liczba_nagrod number default '1' not null
          check (liczba_nagrod between 1 and 10),
      poczatek date not null,
      koniec date not null
    );
     
    create table UczestnicyKonkursow (
      konkurs varchar(20) references Konkursy not null,
      zespol varchar(20) not NULL,
      primary key (konkurs,zespol)
    );
     
    create table Zgloszenia (
      id number primary key,
      konkurs varchar(20) not null,
      zespol varchar(20) not null,
      zadanie char(1) not null,
      data_zgloszenia date not null,
      ok number not null
          check (ok between 0 and 1),
      foreign key (konkurs,zespol) references UczestnicyKonkursow
    );

    Przykładowe dane


    delete from Zgloszenia;
    delete from UczestnicyKonkursow;
    delete from Konkursy;
     
    insert into Konkursy values ('BD2010',50,2,'25-OCT-10','26-OCT-10');
    insert into Konkursy values ('AISD2010',100,1,'1-OCT-10','20-OCT-10');
    insert into Konkursy values ('BD2009',60,3,'25-OCT-09','26-OCT-09');
     
    insert into UczestnicyKonkursow values ('BD2010','Z1');
    insert into UczestnicyKonkursow values ('BD2010','Z2');
    insert into UczestnicyKonkursow values ('BD2010','Z3');
    insert into UczestnicyKonkursow values ('BD2010','Z4');
    insert into UczestnicyKonkursow values ('BD2010','Z6');
    insert into UczestnicyKonkursow values ('AISD2010','Z1');
    insert into UczestnicyKonkursow values ('AISD2010','Z2');
    insert into UczestnicyKonkursow values ('AISD2010','Z5');
    insert into UczestnicyKonkursow values ('AISD2010','Z6');
    insert into UczestnicyKonkursow values ('AISD2010','Z7');
     
    insert into Zgloszenia values(1,'BD2010','Z1','A','25-OCT-10',0);
    insert into Zgloszenia values(2,'BD2010','Z1','A',to_date('2010-10-26 00:00', 'yyyy-mm-dd hh24:mi'),1);
    insert into Zgloszenia values(3,'BD2010','Z2','A',to_date('2010-10-25 00:00', 'yyyy-mm-dd hh24:mi'),0);
    insert into Zgloszenia values(4,'BD2010','Z2','A',to_date('2010-10-25 00:11', 'yyyy-mm-dd hh24:mi'),0);
    insert into Zgloszenia values(5,'BD2010','Z2','A',to_date('2010-10-25 00:22', 'yyyy-mm-dd hh24:mi'),1);
    insert into Zgloszenia values(6,'BD2010','Z2','A',to_date('2010-10-25 00:33', 'yyyy-mm-dd hh24:mi'),0);
    insert into Zgloszenia values(7,'BD2010','Z2','B',to_date('2010-10-25 01:42', 'yyyy-mm-dd hh24:mi'),1);
    insert into Zgloszenia values(8,'BD2010','Z6','C',to_date('2010-10-26 00:20', 'yyyy-mm-dd hh24:mi'),1);
     
     
    insert into Zgloszenia values(10,'AISD2010','Z1','A',to_date('2010-10-01 00:10', 'yyyy-mm-dd hh24:mi'),0);
    insert into Zgloszenia values(11,'AISD2010','Z1','A',to_date('2010-10-01 00:20', 'yyyy-mm-dd hh24:mi'),0);
    insert into Zgloszenia values(12,'AISD2010','Z1','A',to_date('2010-10-01 01:00', 'yyyy-mm-dd hh24:mi'),1);
    insert into Zgloszenia values(13,'AISD2010','Z2','A',to_date('2010-10-01 01:10', 'yyyy-mm-dd hh24:mi'),0);
    insert into Zgloszenia values(14,'AISD2010','Z2','A',to_date('2010-10-01 02:10', 'yyyy-mm-dd hh24:mi'),1);
    insert into Zgloszenia values(15,'AISD2010','Z5','A',to_date('2010-10-01 01:30', 'yyyy-mm-dd hh24:mi'),0);
    insert into Zgloszenia values(16,'AISD2010','Z5','A',to_date('2010-10-01 03:00', 'yyyy-mm-dd hh24:mi'),1);
    insert into Zgloszenia values(17,'AISD2010','Z6','B',to_date('2010-10-01 00:11', 'yyyy-mm-dd hh24:mi'),1);
    insert into Zgloszenia values(18,'AISD2010','Z7','C',to_date('2010-10-01 00:01', 'yyyy-mm-dd hh24:mi'),0);

    Laboratorium 5: SQL PLUS

    Podstawowa składnia


    [
     DECLARE
        deklaracje (np. zmiennych, stałych)
    ]
    BEGIN
      polecenia programu
    [
     EXCEPTION
        obsługa błędów
    ]
    END;

    Zmienne


    Dostępne typy zmiennych:

    • liczbowe: BINARY_INTEGER, DEC, DECIMAL, FLOAT, INT, INTGER, NATURAL, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, REAL, SMALLINT,
    • znakowe: CHAR, CHARACTER, LONG, NCHAR, NVARCHAR2, RAW, STRING, VARCHAR, VARCHAR2,
    • logiczne: BOOLEAN,
    • czasowe: DATE, TIMESTAMP, INTERVAL,
    • czasowe: DATE, TIMESTAMP, INTERVAL,
    • wskaźnikowe: REF CURSOR, REF object_type

    Przykaady deklaracji:

    DECLARE
     i NUMBER(10);
     j INT := 10;
     DATA DATE NOT NULL DEFAULT DATE '2007-01-01';
     ok BOOLEAN NOT NULL DEFAULT FALSE;
     s VARCHAR(100) := 'ala ma kota';

    Zmienne złożone:

    DECLARE
     TYPE t_punkt IS RECORD (
      x INT,
      y INT
     );
     p1 t_punkt;

    Dostęp do poszczególnych pól jak w Pascalu/C (czyli np. p1.x).
    Atrybuty %TYPE i %ROWTYPE służą do sprawdzania typu określonych pól/wierszy w tabelach.

    DECLARE
     zmienna tabela.pole%TYPE;
     wiersz tabela%ROWTYPE;

    Stałe

    DECLARE
      nazwa CONSTANT typ := wartość;

    Interakcja z użytkownikiem


    Blok może zawierać zmienne podstawienia (czyli nazwy zmiennych poprzedzone znakiem '&'), przed uruchomieniem bloku użytkownik jest proszony o podanie wartości wszystkich zmiennych podstawieniowych.

    s:=&abc;
    i:=&identyfikator;

    Do wypisywania komunikatów służy funkcja dbms_output.put_line()

    dbms_output.put_line('i='||i||' s='||s);

    Instrukcje warunkowe


    Składnia polecania IF:

    IF warunek THEN
      polecenia
    (
      ELSIF warunek_x THEN
        polecenia
    )*
    [
      ELSE
        polecenia
    ]
    END IF;

    Składnia polecania CASE:

    CASE wyrażenie
      WHEN wartość THEN polecenia1
      WHEN wartość_2 THEN polecenia2
    [
      ELSE
        polecenia
    ]
    END [ CASE ];
     
    CASE
      WHEN warunek_1 THEN polecenia
      WHEN warunek_2 THEN polecenia2
    [
      ELSE
        polecenia
    ]
    END [ CASE ];

    Jeśli CASE jest traktowane jako wyra»enie to można kończyć ENDem, jeśli jako
    polecenie to trzeba je zakończyć END CASE.

    Pętle


    Składnia polecenia LOOP:

    LOOP
      sekwencja
      [ IF warunek THEN EXIT; END IF; ]
      [ EXIT WHEN warunek ; ]
    END LOOP;

    Składnia polecania WHILE:

    WHILE warunek LOOP
      polecenia
    END LOOP;

    Składnia polecenia FOR:

    FOR licznik IN [ REVERSE ] start..koniec LOOP
      polecenia
    END LOOP;

    Nie jest konieczne definiowanie zmiennej licznik.

    Różności


    • instrukcja NULL (instrukcja która nic nie robi, wypełniacz pustych bloków),

    Zapytania SQL w PL/SQL


    SELECT lista pól INTO lista zmiennych FROM ... ;
     
    INSERT INTO tabela (lista pól) VALUES (lista warto±ci);
     
    INSERT INTO tabela VALUES zmienna_wierszowa;
     
    /* pseudokolumna ROW */
     
    UPDATE tabela SET row = zmienna_wierszowa;

    Kursory


    DECLARE
      CURSOR nazwa [ (parametry) ] IS
        { zapytanie_sql | RETURN typ złożony }
        [ FOR UPDATE [ OF lista pól ] ]

    Przykłady:

    DECLARE
      CURSOR cur_osoby IS SELECT * FROM osoby;
      CURSOR cur_osoby2 RETURN osoby%ROWTYPE;
      CURSOR cur_wypozyczenia (p_pesel wypozyczenia%TYPE) IS
        SELECT * FROM wypozycznia WHERE pesel=p_pesel;

    Operacje na kursorach:

    OPEN nazwa_kursora [ ( warto±ci parametrów) ];
     
    FETCH nazwa_kursora INTO { lista_zmiennych | zmienna wierszowa } ;
     
    CLOSE nazwa_kursora;

    Atrybuty kursora:

    %ISOPEN
    %FOUND
    %NOTFOUND
    %ROWCOUNT

    Pętle FOR z użyciem kursorów:

    FOR { zmienna rekordowa | zmienna wierszowa } IN nazwa_kursora LOOP
      polecenia
    END LOOP;

    Nie jest konieczne otwieranie lub zamykanie kursora. Nie jest równie» konieczne
    definiowanie zmiennej licznikowej.

    Pętle FOR z zapytaniem SQL:

    FOR { zmienna rekordowa | zmienna wierszowa } IN ( zapytanie SQL ) LOOP
      polecenia
    END LOOP;

    Kursor niejawny ma nazwę SQL (ostatnie wykonywane polecenie SELECT INTO,
    INSERT, UPDATE, DELETE).

    Obsługa wyjątków


    DECLARE
      wyj1 EXCEPTION;
      wyj2 EXCEPTION;
    BEGIN
      polecenia ...
      RAISE wyjątek;
    EXCEPTION
      WHEN wyj1 THEN
        polecenia1
      WHEN wyj2 THEN
        polecenia2
    [ WHEN OTHERS THEN
        polecenia..      ]
    END;

    Predefiniowane wyjątki:

    CASE_NOT_FOUND
    CURSOR_ALREADY_OPEN
    DUP_VAL_ON_INDEX
    INVALID_CURSOR
    NO_DATA_FOUND
    TOO_MANY_ROWS
    VALUE_ERROR
    ZERO_DIVIDE

    Przydatne informacje:

    • polecenie set serveroutput on aktywuje wypisywanie komunikatów przez funkcję put_line,

    Ćwiczenie. Wypisz przy użyciu PL/SQLa bieżącą datę i godzinę

    Ćwiczenie. Oblicz 15-tą liczbę Fibonacciego.

    Ćwiczenie. Zmodyfikuj poprzednie rozwiązanie, tak by użytkownik mógł wskazywać która liczbę fibonacciego należy obliczyć.

    Ćwiczenie. Wypisz reprezentację słowną liczby podanej przez użytkownika (jeśli liczba nie będzie z zakresu 0..99, możesz wypisać komunikat o błędzie).

    Ćwiczenie. Wypisz liczbę wierszy w tabeli ksiazki.

    Ćwiczenie. Dla każdej tabeli zdefiniowanej w bazie danych, wpisz jej nazwę i liczbę wierszy (wskazówka: użyj widoku systemowego USER_TABLES lub tabeli ALL_TABLES)

    Ćwiczenie. Wypisz dla każdej książki jej najdłuższy czas wypożyczenia.

    Ćwiczenie. Sprawdź czy w bazie istnieje czytelnik o numerze pesel 999 i jeśli brak takiej osoby dodaj wiersz z jej opisem.

    Ćwiczenie. Wyświetl trzy najczęściej wypożyczne książki (jeśli jest wiele książek o tej samej liczbie wypożyczeń, posortuj wyniki wg. tytułu). Wskazówka: uwzględnij atrybut %ROWCOUNT.

    Ćwiczenie. Sprawdź czy istnieje książka o numerze ewidecyjnym 15. Użyj obsługi wyjątków.

    Laboratorium 6: kursory, wyzwalacze, procedury

    Przydatne informacje:


    • sprawdzanie błędów kompilacji: show errors procedure nazwaProcedury (bez średnika na końcu)
    • informacje o zdefiniowanych procedurach/funkcjach/wyzwalaczach są przechowywane w tabelach USER_PROCEDURES, USER_TRIGGERS.

    Tabele


    Deklaracje

    DECLARE
      TYPE t_tab IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
      tab t_tab;

    Operacje na tabelach:

    tab(i)           --i-ty element
    tab.EXISTS(i)    --czy i-ty jest określony
    tab.COUNT        --liczba elementów
    tab.FIRST        --pierwszy element tabeli
    tab.LAST         --ostatni element tabeli
    tab.PRIOR(i)     --poprzednik i-tego
    tab.NEXT(i)      --nastepnik i-tego
    tab.DELETE       --usuń zawartość tabeli
    tab.DELETE(i)    --usuń i-ty element
    tab.DELETE(i,j)  --usuń elementy od i do j

    Kursory służące do zmiany danych


    DECLARE
      CURSOR nazwaKursora
          IS zapytanieSQL
      FOR UPDATE OF lista_kolumn;

    Różni się od zwykłego kursora zakładaniem blokady na modyfikowane pola.

    Przykład:

    DECLARE
      CURSOR c_ksiazki IS SELECT * FROM ksiazki FOR UPDATE OF tytul;
    BEGIN
      FOR k IN c_ksiazki LOOP
        dbms_output.put_line('ksiazka '||k.nr_ew);
        UPDATE ksiazki SET tytul=tytul||'_a' WHERE nr_ew=k.nr_ew;
      END LOOP;
    END;
    /

    Procedury


    PROCEDURE nazwa [(parametr 1[, parametr 2, ...])] IS
       deklaracje zmiennych
    BEGIN
      treść procedury
    [EXCEPTION
       obsauga wyjątków]
    END [nazwa];

    Denicja parametry ma następującą postać:

    nazwa parametru [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] typ
        [{:= | DEFAULT} wyrażenie]
    • IN - przekazanie parametru przez wartość,
    • OUT - parametr musi być zmienn¡, zachowuje się jak niezainicjalizowana
      zmienna (pocz¡tkowo przechowuje pust¡ wartość),
    • IN OUT - parametr musi być zmienn¡, zachowuje si¦ jak zainicjalizowana
      zmienna,
    • NOCOPY - przekazywanie parametrów przez referencje.

    Komunikaty o błędach kompilacji można zobaczyć wykonuj¡c polecenie:

    SHOW errors procedure nazwa;

    Aby wywołać procedurę z poziomu sqlplusa, należy wykonać polecenie:

    execute nazwaProcedury(wartość1,wartość2);
    execute nazwaProcedury(nazwa1 => wartość1,nazwa2 => wartość2);
    execute nazwaProcedury; /* jeśli procedura nie ma parametrów */

    W niektórych przypadkach można najpierw zadeklarować funkcję, a dopiero
    później podać jej treść, np.

    PROCEDURE nazwa ( .. definicja parametrów .. );

    Zgłaszanie błędów, działanie programów można przerwać za pomoc¡ funkcji
    raise_application_error, np.

    RAISE_APPLICATION_ERROR(kod,komunikat);
    RAISE_APPLICATION_ERROR(-20000,'bardzo ważny błąd');

    Kody od -20999..-20000 są zarezerwowane dla użytkowników.

    Funkcje


    FUNCTION nazwa [(parametr 1,[ parametr 2, ...])] RETURN typ IS
       [deklaracje zmiennych]
    BEGIN
       treść
    [EXCEPTION
       obsługa wyjątków]
    END [nazwa];

    W treści funkcji należy użyć polecenia RETURN, które zwraca wynik i kończy
    działanie funkcji (dokładnie tak jak w C/C++).

    Komunikaty o błędach kompilacji można zobaczyć wykonując polecenie:

    SHOW errors FUNCTION nazwa;

    Informacje o procedurach i funkcjach można otrzymać za pomoc¡ poleceń:

    DESCRIBE PROCEDURE nazwa
    DESCRIBE FUNCTION nazwa

    Pakiety


    CREATE PACKAGE nazwa AS
       PROCEDURE procedura1 (...);
       PROCEDURE precedura2;
       ...
    END emp_actions;
     
    CREATE PACKAGE BODY nazwa AS
       PROCEDURE procedura1 (...) IS
       BEGIN
       END procedura1;
     
       PROCEDURE procedura2 IS
       BEGIN
       END procedura2;
       ...
    END nazwa;

    Wyzwalacze


    Wyzwalacze to fragmenty programów wykonywane w przypadku zajęcia określonych operacji na bazie danych. Wyzwalacze s¡ wykonywane automatycznie przez bazie danych.

    Do czego mogą służyć wyzwalacze:

    • kontroli poprawno±ci danych,
    • logowania zmian na bazie,
    • sprawdzania uprawnień do wykonywania poszczególnych operacji.
    CREATE [OR REPLACE] TRIGGER nazwa
        {BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON nazwa tabeli
        [REFERENCING [NEW AS nazwa_nowego_wiersza] [OLD AS nazwa_starego_wiersza]]
        [FOR EACH ROW [WHEN (warunek)]]
        treść wyzwalacza

    Wyzwalacze mogą być uruchamiane:

    • jednokrotnie dla każdego wiersza (klauzula FOR EACH ROW),
    • jednokrotnie dla każdego polecenia,

    Kolejność wykonywania wyzwalaczy:

    • przed instrukcj¡,
    • przed pierwszym operowanym wierszem
    • po pierwszym wierszu ...
    • przed ostatnim wierszem
    • po ostatnim wierszu
    • po instrukcji

    W wyzwalaczach nie wolno używać operacji związanych z transakcjami (COM-MIT, ROLLBACK).
    Po zdefiniowaniu wyzwalacza, baza może stwierdzić, że kompilacja się nie powiodła. Komunikaty o błędach można zobaczyć wykonuj¡c polecenie:

    show errors trigger nazwaWyzwalacza;

    Inne polecenia dotycz¡ce wyzwalaczy:

    • select trigger_name from user_triggers - lista zdeniowanych wyzwalaczy,
    • select trigger_type, triggering_event, table_name, referencing_names,
      trigger_body from user_triggers where trigger_name = 'bazwa' - dokaadne informacje konkretnym wyzwalaczu,
    • drop trigger nazwaWyzwalacza - usuwanie,
    • alter trigger nazwaWyzwalacza {disable|enable} - wyłączenie/włączenie wyzwalacza.

    Odwołania do wartości zmienianych wierszy:

    • :OLD.nazwa_pola - wartości przed zmian¡/usunięciem,
    • :NEW.nazwa_pola - wartości po zmianie/wstawieniu,

    Można definiować jeden wyzwalacz dla wielu zdarzeń, np.

    CREATE OR REPLACE TRIGGER TestowyWyzwalacz
        BEFOR INSERT OR UPDATE OR DELETE ON tabela
        FOR EACH ROW
    BEGIN
      IF INSERTING THEN
        ...
      ELSIF DELETING THEN
        ...
      ELSIF UPDATING THEN
        ...
      END IF;
    END;

    SQL - różności


    Widoki:

    CREATE VIEW nazwa AS zapytanie_sql;

    Sekwencje:

    CREATE SEQUENCE nazwa
    [   INCREMENT BY wartość ]
    [   START WITH wartość ]
    ;
    SELECT nazwa_sekwencji.NEXTVAL FROM dual; /* zwraca wartość + zwiększa licznik */
    SELECT nazwa_sekwencji.CURRVAL FROM dual; /* zwraca wartość + bez zwiększania licznika */

    Ćwiczenie. Napisz procedurę wypisującą ostatnie 10 wypożyczeń książek.

    Ćwiczenie. Napisz funkcję, która otrzymuje jako parametr numer ewidencyjny książki i zwraca ile razy była ona wypożyczona.

    Ćwiczenie. Sprawdź jakie procedury i funkcje są zdefiniowane w twojej bazie danych.

    Ćwiczenie. Usuń wybraną procedurę lub funkcję (np. procedurę PokazWypozyczenia).

    Ćwiczenie. Dodaj wyzwalacz do tabeli wypożyczenia, który będzie blokował wypożyczanie książek, które jeszcze nie zostały zwrócone.

    Ćwiczenie. Usuń wyzwalacz SprawdzWypozyczenia z tabeli wypożyczenia.

    Ćwiczenie. Dodaj wyzwalacz do tabeli wypożyczenia, który będzie uniemożliwiał wprowadzanie wypożyczeń z datą zwrotu mniejszą niż data wypożyczenia.

    Ćwiczenie. Dodaj wyzwalacz do tabeli wypożyczenia, który będzie uniemożliwiał wypożyczanie więcej niż 10 książek jednocześnie dla jednej osoby.

    Ćwiczenie. Dodaj wyzwalacz do tabeli książki, który będzie automatycznie nadawał kolejne numery ewidencyjne.

    Laboratorium 7: klucze zewnętrzne, warunki spójności

    SQL


    • opcje kluczy obcych - domyślnie, baza uniemożliwia usuwanie wierszy na które wskazują klucze obce, możemy jednak wskazać inne zachowanie, tzn. on delete cascade powoduje usuwanie wierszy przy kasowaniu obiektów na które wskazują klucze obce, lub on delete set null powoduje zamienie wartości przez NULL przy kasowaniu obiektów na które wskazują klucze obce:
    k_id varchar(16) REFERENCES tab1, 
    k_id2 varchar(16) REFERENCES tab2 ON DELETE cascade, 
    k_id3 varchar(16) REFERENCES tab2 ON DELETE SET NULL,
    • klucze obce składające się w wielu kolumn
    CREATE TABLE a (
      id integer,
      a integer,
      b integer,
      CONSTRAINT fk_abc FOREIGN KEY (a,b) REFERENCES inna_tabela (id,kod)
    );
    • usuwanie tabeli wraz z wszystkimi tabelami zależnymi:
    drop table nazwa cascade constraints;
    • zamiana definicji tabeli,
    ALTER TABLE nazwa_tabeli ADD (nazwa_kolumny typ);
    ALTER TABLE nazwa_tabeli MODIFY (nazwa_kolumny typ);
    ALTER TABLE nazwa_tabeli DROP (nazwa_kolumny);
    ALTER TABLE nazwa_tabeli ADD (constraint nazwa definicja);
    ALTER TABLE nazwa_tabeli enable constraint nazwa;
    ALTER TABLE nazwa_tabeli disable constraint nazwa; -- czasowe wyłączenie więzów spójności
    • tworzenie i usuwanie indeksów
    • CREATE [ UNIQUE ] INDEX nazwa_indeksu ON nazwa_tabeli (kol1 [ ASC|DESC ], kol2 ... );
      DROP INDEX nazwa_indeksu; -- usuwanie
      • pseudokoluman ROWNUM
      SELECT ROWNUM,id FROM tabela;
      SELECT * FROM tabela WHERE ROWNUM <10;
      -- uwaga!! ale nie SELECT * FROM tabela WHERE ROWNUM <10;
      • synonimy (skrócone nazwy tabeli i perspektyw)
      CREATE SYNONYM nazwa FOR nazwa_tabeli;
      DROP SYNONYM nazwa;
      • Typy danych: BLOB (binarne duże obiekty), CLOB (tekstowe duże obiekty), LOB (binarne lub testowe).
        Funkcje EMPTY_BLOB(), EMPTY_CLOB() zwracają puste obiekty.

      PL/SQL



      Ćwiczenie. Zmień rozmiar pola imię w tabeli czytelnicy z 64 znaków na 32 znaki.

    Ćwiczenie.Zmień definicję kluczy obcych w bazie biblioteki, tak by były typu ON DELETE CASCADE. Sprawdź jak teraz działa usuwanie elementów tablicy czytelnicy.

    Ćwiczenie. Dodaj indeksy: idx_data_w zawierający pole data_wypozyczenia, oraz idx_data_z zawierający pole data_zwrotu, do tabeli wypożyczenia .

    Ćwiczenie. Wypisz książki których tytuły składają się z co najmniej dwóch wyrazów.

    Ćwiczenie. Wczytaj i wypisz zawartość strony stencel.mimuw.edu.pl.

    Ćwiczenie. Wypisz wszystkie odnośniki ze strony stencel.mimuw.edu.pl

    Laboratorium 8: Oracle Designer

    Projektowanie baz danych przy pomocy Oracle Designer


    Przydatne adresy:

    Laboratorium 10: łącze oracle PHP

    Smarty


    Pear


    Laboratorium 11: javascript

    JavaScript i jQuery


    Podstawowe cechy

    • Język programowania stosowany do opisywania dynamicznych elementów stron WWW.
    • Interpretowany
    • Obiektowy (prototypy)
    • Skryptowy
    • Funkcje są „obywatelami pierwszej klasy”
    • Dynamiczny
    • Słabo typowany

    Hello World

    1. Otwórz jakąś przeglądarkę
    2. Wpisz w pasku adresu:
      javascript:alert(”Hello, World!”);

    Hello World w pliku

    • Sposób 1:
      <script type="text/javascript" src="hello.js"></script>
    • W hello.js:
      alert(”Hello, World!”);
    • Sposób 2:
      <script type="text/javascript">
       alert(”Hello, World!”);
      </script>

    Zmienne

    • Słabe typowanie
    • Deklarujemy następująco:
      var foo;
    • Można zainicjalizować:
      var foo = ”bar”;
    • Przypisania:
      foo = 8;

    Prymitywy

    • Boolean
      var foo = true;
      var bar = false;
    • Numeric
      var dwa = 2;
      var trzyipol = 3.5;
    • String
      var s = ”String”;

    Tablice

    • Tworzenie:
      var fib = new Array(0, 1, 1, 2, 3);
      var fib = [0, 1, 1, 2, 3];
    • Można mieszać typy:
      fib[1] = ”jeden”;

    Operatory

    • Takie same, jak w Javie.
    • ===, !== - identyczność, bądź jej brak
    • typeof x – string opisujący typ x
    • o instanceof c - sprawdza, czy obiekt o został stworzony przez konstruktor c
    • delete x – usuwa obiekt
    • new cl – nowy obiekt typu cl. cl musi być konstruktorem

    Funkcje

    • W pliku hello.js:
       function helloWorld() {
         alert("Hello, World!");
       }
    • W pasku adresu:
         javascript:helloWorld();

    Ulubiony przykład

    function fib(n) {
      if (n == 0) {
        return 0;
      } else if (n == 1) {
        return 1;
      } else {
        return fib(n-1) + fib(n-2);
      }
    }

    Lambda abstrakcja

    var twice = function(n) {
      return 2 * n;
    }

    Obiekty

    • Są tworzone za pomocą konstruktorów:
      function Osoba(imie) {
        this.imie = imie;
      }
       
      var Jan = new Osoba(”Jan”);
    • Ich pola są domyślnie inicjalizowane przez wartości określone w prototypach
      Osoba.prototype.nazwisko = ”Kowalski”;
       
      Jan = new Osoba(”Jan”);
       
      //teraz Jan.nazwisko == ”Kowalski”
    • Można to wykorzystać do dziedziczenia.
    • Inna notacja pozwalająca tworzyć obiekty – literały obiektów:
      Jan = { 'imię': 'Jan', 'nazwisko': 'Kowalski' }

    Obsługa zdarzeń (Event handling)

    • Specjalne atrybuty zaczynające się na on:
      var helloWorld = function(evt) {
        alert("Hello, World!");
      }
       
      document.onclick = helloWorld;
    • Bardzo użyteczny jest window.onload, który jest wywoływany po załadowaniu dokumentu.

    DHTML

    • Dynamic HTML
    • Drzewo DOM (Dynamic Object Model) – umożliwia dowolną modyfikację całego dokumentu HTML.

    Znajdowanie elementów

    • Służą do tego metody obiektu document:
      document.getElementById(”id”);
      document.getElementsByTagname(”p”);

    Dodawanie elementów

    • Do tworzenia elementów służy metoda obiektu document:
      var div = document.createElement(”div”);
      div.id = ”someDiv”;
      div.class = ”someClass”;
    • Można teraz dodać nowy element do dokumentu:
      document.documentElement.appendChild(div);

    Modyfikacja elementów

    • Można zmieniać atrybuty HTML za pomocą atrybutów javascriptowych obiektów:
      document.getElementById(”someDiv”).class = ”otherClass”;
    • Atrybut style modyfikuje styl css
      document.getElementById('someDiv').style.color = 'red';

    Usuwanie elementów

    • Metoda removeChild obiektu dokument:
      div = document.getElementById(”someDiv”);
      document.removeChild(div);

    jQuery

    • Biblioteka JavaScript, która wszystko ułatwia.
    • Przenośność między przeglądarkami.
    • Łatwa nawigacja po drzewie DOM.
    • Łatwa obsługa eventów.
    • Łatwa dynamiczna zmiana stylów CSS.
    • Łatwy AJAX
    • Efekty i animacje.
    • Narzędzia ułatwiające pracę.
    • Dużo pluginów.

    Pierwszy przykład

    1. Dodaj między <body></body>:
      <a href="http://jquery.com/">jQuery</a>
    2. Dodaj między <script></script>:
      $(document).ready(function() {
        $(”a”).click(function(event) {
         alert(”Hello, jQuery!”);
         event.preventDefault();
        });
      });

    jQuery i DOM

    • Selektory umożliwiają łatwe wybieranie elementów z drzewa DOM:
      $(".someClass").css("border", "red");
      $("#someId").html(”Modified with jQuery”);
    • Różne rodzaje selektorów:
      $(”#someId > p”) - wszystkie elementy typu p, które są dziećmi elementu o Id someId
      $("p[@class]") – wszystkie elementy typu p z atrybutem class 
      $("p.foo[a]") – wszystkie elementy typu p, klasy foo, które zawierają element typu a.

    jQuery i eventy

    $(”p.shy”).click(function() {
      $(this).hide(”slow”);
    });
    • Wszystkie paragrafy klasy shy schowają się po kliknięciu.

    jQuery i AJAX

    JavaScript


    Czym jest JavaScript

    • Językiem stworzonym przez Netscape w 1995 roku
    • Interpretowanym językiem skryptowym działającym po stronie klienta
    • i obiektowym
    • i wspierającym programowanie funkcyjne (domknięcia i funkcje wyższego rzędu)

    Do czego można stosować JS

    • Dynamiczne zmienianie wyglądu i zawartości strony w odpowiedzi na akcje użytkownika
    • Kontrolowanie ramek, apletów i pluginów
    • Otwieranie nowych okien, pobieranie informacji o przeglądarce, tworzenie plików cookies
    • Przetwarzanie żądań przed ich wysłaniem do serwera WWW

    Do czego nie można stosować JS

    • Uruchamiania zewnętrznych aplikacji
    • Odczytywania i zapisywania plików na dysku użytkownika
    • Wysyłania sekretnych e-maili ze strony
    • Zmiany parametrów przeglądarki, przycisków nawigacyjnych

    Elementy z których składa się JS

    • Jądro(EMAScript) - definiuje składnie, typy, wyrażenia...
    • DOM - programistyczny interfejs do obsługi html i xml
    • BOM - programistyczny interfejs do obsługi przeglądarki

    Składnia

    • Wszystko jest case-sensitive
    • Zmienne są słabo typowaIne
    • Nie trzeba stawiać średników na końcu linii
    • Komentarze jak w Javie (/* */ i //)
    • Bloki kodu są zawarte w { }

    Zmienne

    • Definiowanie za pomocą słowa kluczowego var
    • Można im zmieniać typy
    • Nazwy zaczynają się od litery, $ albo_

    Instrukcje

    • if then else, switch, for ..., while..., do cos while... jak w C++
    • for (zmienna in object) cos

    Przykład 1

    <head>
    <script type="text/javascript">
    function notEmpty() {
      var myTextField = document.getElementById('myText');
      if(myTextField.value != " ")
        alert("You entered: "+ myTextField.value)
      else
        alert("Would you please enter some text?")
    } </script>
    </head>
    <body>
    <script type="text/javascript">
    document.write("Wpisz tekst:");
    </script>
    <input type='text' id='myText' /> <input type='button'
    onclick='notEmpty()' value='Form Checker' /> </body>

    Przykład 2 - suma

    function sum(numbers) {
      var total = 0
      forEach(numbers, function (number) {
        total += number
      })
      return total
    }
    alert(sum([1, 10, 100]));

    Klasa Object

    Właściwości:

    • constructor - referencja do funkcji która stworzyła obiekt
    • prototype - referencja do prototypu obiektu

    Metody:

    • hasOwnProperty(property) - czy obiekt ma własność
    • isPrototypeOf(object) - czy jest prototypem
    • toString() - konwersja na String
    • propertylsEnumerable(property) czy właściwości można użyć w for

    Przykład 3 - Obiekt

    var cat = {colour: "grey", name: "Spot", size: 46};
    cat.speak = function(line) {
     print("The cat says '", line, " " ');
    }
    cat.size = 
    yellow";
    alert(cat.size);
    delete cat.size;
    alert(cat.size);
    alert(cat.speak("Cześć"));
    alert(cat);

    Przykład 4 - Obiekt

    function Rabbit(adjecitve) {
      this.adjective = adjective;
      this.speak = function(line) {
        print("The ", this.adjective, " rabbit says '", line, " " ');
      };
    }
    var = new Rabbit("krolik");
    function makeRabbit(adjective) {
      return {
        adjective: adjective,
        speak: function(line) { tresc funkcji }
      };
    }
    var blackRabbit = makeRabbit("black");

    Klasa Array

    • var a = new Array(100), b = new Array(), c = new Array("a", "b", "c")
    • a.length - długość tablicy
    • sort() - sortuje toString
    • slice(), concat(), reverse()
    • push() i pop() robia z tablicy stos

    inne klasy

    • Function
    • Date
    • Global
    • Math
    • RegExp

    Przykład 5

    function negate(func) {
      return function(x) {
        return !func(x)
      };
    }
    var isOdd = negate(isEven);

    Czym jest jQuery

    • Cross-browserowa biblioteka JavaScript napisana aby ułatwiać pisanie własnych skryptów
    • Składnia zaprojektowana jest aby łatwo posługiwać się DOM, tworzyć animacje, obsługiwać zdarzenia
    • Duże wsparcie dla AJAX
    • <script type="text/javascript" source="jquery.js"/>

    Przykłady

    $("#someId").html("<b>So Bold!</b>");
    $(document).ready(function() {
     $("a").click(function(event) {
      alert("Thanks for visiting');
     });
    }
    $(document).ready(function() {
     $("#orderedlist li:last").hover(function() {
      $(this).addClass("green");
     },function(){
     $(this).removeClass("blue");
     });
    });
    $.ajax({
     type: "POST",
     url: "some.php",
     data: "name=John&location=Boston",
     success: function(msg){
      alert( "Data Saved: " + msg );
     }
    });
    $.post("test.php", { name: "John", time: "2pm" },
     function(data){
      alert("Data Loaded: " + data);
    });
    $.get("test.php", { name: "John", time: "2pm" },
     function(data){
      alert("Data Loaded: " + data);
    });

    Dostęp do baz danych ORACLE za pomocą PHP


    Moduły OCI8 i ORA

    • Do dyspozycji mamy dwa moduły:
    • - Oracle Call-Interface functions (OCI8)
    • - Oracle 7 functions (ORA)
    • OCI8 – zoptymalizowany, duże możliwości, na nim się skupimy
    • ORA – przestarzały, nowsze wersje PHP go nie zawierają – nie używać.

    Połączenie z Bazą danych

    • oci_connect(”login”, ”hasło”, ”baza”)
    • - zwraca identyfikator połączenia lub false w przypadku niepowodzenia
    • oci_new_connect
    • - jeśli chcemy kolejnego połączenia z tą samą bazą(dostaniemy nowy identyfikator)
    • oci_close(połączenie)

    Obsługa błędów

    • oci_error()
    • - zwraca tablicę opisującą błąd lub false, jeśli żadnego nie zarejestrowano.
    • Pola w tablicy:
    • - code
    • - message
    • - offset
    • - sqltext

    Zapytania PL/SQL:

    • oci_parse(połączenie, polecenie sql/plsql)
    • - zwraca id deklaracji lub false w przypadku błędu
    • - nie sprawdza poprawności zapytania
    • - SQL – nie stawiamy średnika na końcu polecenia
    • - PL/SQL – stawiamy średnik
    • oci_free_statement(deklaracja)
    • - zwalnia zasoby

    Dostęp do zmiennych:

    • oci_bind_by_name(deklaracja, ”:nazwa”, zmiennaPHP)
    • - zwraca true/false
    • - wygodne i bezpieczne
    • oci_bind_by_name($s, ":bind1", $var1);

    Wykonywanie deklaracji:

    • oci_execute(deklaracja, opcja)
    • - zwraca true/false
    • Opcje:
    • - OCI_COMMIT_ON_SUCCES
    • - OCI_NO_AUTO_COMMIT

    Zatwierdzanie zmian:

    • Jeśli zamkniemy połączenie to przywracana jest wersja bazy sprzed wprowadzenia zmian
    • oci_commit(połączenie)
    • - zwraca true/false
    • oci_rollback(połączenie)
    • - cofa niezatwierdzone zmiany

    Przetwarzanie wyników:

    • oci_fetch(deklaracja)
    • - przechwytuje następny wiersz
    • - zwraca true dopóki jest jeszcze coś do przeczytania
    • oci_result(deklaracja, pole)
    • - zwraca wartość pola w aktualnej linii

    Przetwarzanie wyników:

    • oci_fetch_all(deklaracja, zmienna_php, ile_pomijamy, ile_czytamy, flagi)
    • - zwraca ilość wierszy lub false w przypadku niepowodzenia
    • Flagi:
    • - OCI_FETCHSTATEMENT_BY_ROW
    • - OCI_FETCHSTATEMENT_BY_COLUMN
    • - OCI_NUM
    • - OCI_ASSOC

    Przykładowe użycie:

    • Całość na:
      https://students.mimuw.edu.pl/~rb291513/bd/przyklad
    • Połączenie z bazą danych:
      $c = oci_connect("rb291513", "XXXXXX");
      if($c){
        echo "Jest polaczenie.<br><br>";
      }else{
        $err = oci_error();
        echo "Oracle Connect Error " . $err['text'];
        exit(0);
      }

    Przykładowe użycie

    • Wypisywanie zawartości:
    • $r = oci_parse($c, "SELECT * FROM wypozyczenia");
      oci_execute($r);
      $rowCount = oci_fetch_all($r, $all, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
      echo "Zawartosc tabeli WYPOZYCZENIA: <br>";
      echo "<pre>".print_r($all, true)."</pre>";

      Przykładowe użycie:

      • Powiązanie zmiennych:
      $var2 = "Scott";
      $tab = array(2,8,19,32);
      $s = oci_parse($c, "insert into tab1 values (:bind1, :bind2)");
      oci_bind_by_name($s, ":bind1", $var1, 20);
      oci_bind_by_name($s, ":bind2", $var2);
      foreach($tab as $var1){
        oci_execute($s);
      }

      Przykładowe użycie:

      • Przetwarzanie wierszy jeden po drugim:
      • $s = oci_parse($c, "select * from tab1");
        oci_execute($s);
        while(oci_fetch($s)){
          echo "COL1 = " . oci_result($s, "COL1") .
            ", COL2 = " . oci_result($s, "COL2") . "<br>";
        }

        Przykładowe użycie:

        • PL/SQL:
        • $s = oci_parse($c, "create or replace procedure powieksz(p1 in number, p2 out number) as
                              begin p2 := p1 + 1; end;");
          oci_execute($s);
          $p1 = 233;
          $s = oci_parse($c, "begin powieksz(:x1, :x2); end;");
          oci_bind_by_name($s, ":x1", $p1);
          oci_bind_by_name($s, ":x2", $p2, 40);
          oci_execute($s);
          echo $p1 . " + 1 = " . $p2; // 233 + 1 = 234

          Dodatki:

          Przydatne linki


        • Firebug -- przydatne rozszerzenie do przegladarki
        • szablony stron: csszengarden.com freecsstemplates.org

    Laboratorium 12: zadanie końcowe

    Zadanie 1


    Święty Mikołaj postanowił skomputeryzować swoje biuro. Twoim zadaniem jest przygotowanie projektu bazy danych, która będzie pełnić to zadanie.

    Święty Mikołaj zna wszystkie dzieci, ze sobie tylko znanych źródeł wie, które z nich były grzeczne, a które nie. Grzeczne dzieci przesyłają Mikołajowi listę prezentów, które chciałby otrzymać.

    Natomiast niegrzeczne mogą liczyć jedynie na rózgi.

    Przy dostarczaniu prezentów Mikołaj korzysta z całej sieci pomocników. Każdy z nich musi po świętach, przygotować szczegółowe sprawozdanie dotyczące wydanych prezentów. Natomiast Mikołaj chciałby mieć możliwość monitorowania postępu prac pomocników.
    Kolejnym problem z którym styka się Mikołaj to przygotowanie zamówień do fabryki zabawek. Zamówienia są przygotowywane na podstawie listy zamówień pochodzących od dzieci.

    • rozszerz funkcjonalność bazy o jeden (dowolnie wybrany) dodatkowy element (np. 1 lub 2 encje),
    • przygotuj diagram encji,
    • opisz dodatkowe, przyjęte przez ciebie, założenia funkcjonalne.

    Encje: TODO

    Zadanie 2


    Dana jest baza o schemacie \( \displaystyle \ R = \ ABCDEFG\) i zależnościach funkcyjnych:

    \( \displaystyle \ F = \{BCD \to A,BEF \to D,AE \to B,A \to C,CDE \to A,DE \to AD,BC \to F,CE \to F,CE \to B,E \to F, \} \)

    • Wyznacz wszystkie klucze.
    • Wyznacz domkniecie zbioru atrybutów ABC.
    • Podaj postać minimalna \( \displaystyle \mathbb{F}\).
    • Przedstaw rozkład \( \displaystyle \ R \) do trzeciej postaci normalnej.
    • Czy relacja \( \displaystyle \ R_2 = \ ABCD \) z zależnościami funkcyjnymi

      \( \displaystyle \ F_2 = \{ABC \to D,AD \to A,AB \to C,BC \to A\}\)
      jest w postaci Boyce’a-Codda? Odpowiedz uzasadnij.