Laboratorium 4: Kolokwium B | Informatyka MIMUW

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:

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),
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.
wypisz identyfikatory (uporządkowane rosnąco) nieprawidłowych zgłoszeń, tzn. takich, które zostały przesłane poza czasem trwania konkursu
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ń,
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.
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
wypisz raport zawierający nazwę drużyny oraz sumę nagród uporządkowany malejąco według sumy nagród
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).

Rozwiązanie

/* zadanie 1 */
SELECT nazwa,poczatek,nagroda*liczba_nagrod
  FROM konkursy
ORDER BY nagroda*liczba_nagrod DESC, nazwa ASC;
 
 
/* zadanie 2 */
SELECT nazwa,
       (SELECT COUNT(*) FROM UczestnicyKonkursow WHERE nazwa=konkurs) as LiczbaDruzyn,
       (SELECT COUNT(DISTINCT zadanie) FROM Zgloszenia WHERE nazwa=konkurs AND ok=1) as RozwiazaneZadania
  FROM Konkursy;
 
 
/* zadanie 3 */
SELECT z.id,z.konkurs,z.zespol,z.zadanie
  FROM zgloszenia z,konkursy k
 WHERE z.konkurs=k.nazwa
   AND (z.data_zgloszenia<k.poczatek OR z.data_zgloszenia>k.koniec)
 ORDER BY z.id ASC;
 
 
/* zadanie 4 */
SELECT z.id,z.konkurs,z.zespol,z.zadanie
  FROM zgloszenia z
 WHERE EXISTS
  (SELECT * FROM zgloszenia zz 
    WHERE zz.konkurs=z.konkurs AND zz.zespol=z.zespol
      AND zz.zadanie=z.zadanie
      AND zz.ok=1 AND zz.data_zgloszenia<z.data_zgloszenia)
 ORDER BY z.data_zgloszenia ASC;
 
 
/* zadanie 5 */
SELECT z.konkurs,z.zadanie,MIN(z.data_zgloszenia-k.poczatek)*24*60 as Czas
  FROM zgloszenia z,konkursy k
 WHERE z.konkurs=k.nazwa AND z.ok=1
GROUP BY z.konkurs,z.zadanie 
 ORDER BY z.konkurs,z.zadanie;
 
/* zadanie 6 */
DROP VIEW tw_czasy;
CREATE VIEW tw_czasy AS
 SELECT z.konkurs,z.zespol,z.zadanie,z.czas_rozwiazania+20*(SELECT COUNT(*) FROM zgloszenia zz WHERE zz.konkurs=z.konkurs and zz.zespol=z.zespol AND zz.zadanie=z.zadanie AND zz.data_zgloszenia<z.data_rozwiazania) as czas_w_min
   FROM (
	SELECT z.konkurs,z.zespol,z.zadanie,
	       MIN(z.data_zgloszenia) as data_rozwiazania, 
               MIN(z.data_zgloszenia-k.poczatek)*24*60 as czas_rozwiazania
        FROM zgloszenia z,konkursy k
       WHERE z.konkurs=k.nazwa AND z.ok=1
       GROUP BY z.konkurs,z.zespol,z.zadanie
  ) z;
 
DROP VIEW tw_ranking;
CREATE VIEW tw_ranking AS
 SELECT uk.konkurs,uk.zespol,
	(SELECT COUNT(DISTINCT zadanie) FROM zgloszenia z WHERE z.konkurs=uk.konkurs AND z.zespol=uk.zespol AND ok=1) as LiczbaZadan,
        NVL((SELECT SUM(c.czas_w_min) FROM tw_czasy c WHERE c.konkurs=uk.konkurs AND c.zespol=uk.zespol),0) as Czas
 FROM UczestnicyKonkursow uk
ORDER BY uk.konkurs ASC,LiczbaZadan DESC,czas ASC;
 
SELECT * FROM tw_ranking;
 
 
/* zadanie 7 */
/* +0.000001 jest koniecznne ze wzglŤdu na b¸Ťdy arytmetyki przy por�wnywaniu kolumny Czas */
SELECT zespol,NVL(SUM(wygrana),0) as sumawygranych
FROM (
  SELECT uk.zespol,uk.konkurs,
   (SELECT SUM(k.nagroda)
   FROM Konkursy k
   WHERE uk.konkurs=k.nazwa
     AND (SELECT COUNT(*) FROM tw_ranking r,tw_ranking rr 
           WHERE r.konkurs=k.nazwa AND rr.konkurs=k.nazwa AND rr.zespol=uk.zespol
             AND (r.liczbaZadan>rr.liczbaZadan OR (r.liczbaZadan=rr.liczbaZadan AND r.Czas+0.000001<rr.Czas))
         )<k.liczba_nagrod
   ) as wygrana
  FROM UczestnicyKonkursow uk
)
GROUP BY zespol
ORDER BY NVL(SUM(wygrana),0) DESC;
 
 
/* zadanie 8 */
DROP VIEW tw_zgloszenia_z_czasem;
CREATE VIEW tw_zgloszenia_z_czasem
 AS SELECT z.*,
     24*60*(z.data_zgloszenia-k.poczatek)+20*(SELECT COUNT(*) FROM zgloszenia zz WHERE zz.konkurs=z.konkurs AND zz.zespol=z.zespol AND zz.zadanie=z.zadanie AND zz.data_zgloszenia<z.data_zgloszenia) as czas
 FROM Zgloszenia z,Konkursy k
 WHERE z.konkurs=k.nazwa
 ORDER by z.id;
 
SELECT z.*
  FROM tw_zgloszenia_z_czasem z
  WHERE
    z.ok=0
    AND EXISTS ( /* zgloszenie od druzyny ktora nie jest na pierwszym miejscu */
       SELECT * FROM tw_ranking r,tw_ranking rr WHERE r.konkurs=z.konkurs AND rr.konkurs=z.konkurs AND rr.zespol=z.zespol 
                 AND (r.liczbaZadan>rr.liczbaZadan OR (r.liczbaZadan=rr.liczbaZadan AND r.czas+0.00001<rr.czas))
    )
    AND (
      (
       /* zgloszenie zadania, ktore nie zostalo zaakceptowane */
       NOT EXISTS (SELECT * FROM zgloszenia zz WHERE zz.konkurs=z.konkurs AND zz.zespol=z.zespol AND zz.zadanie=z.zadanie AND zz.ok=1)
       AND 
       /* i przenosi na poczatek kolejki */
       NOT EXISTS (SELECT * FROM tw_ranking r,tw_ranking rr WHERE r.konkurs=z.konkurs AND rr.konkurs=z.konkurs AND rr.zespol=z.zespol 
                             AND ( r.liczbaZadan>rr.liczbaZadan+1 
                                   OR (r.liczbaZadan=rr.liczbaZadan+1 AND r.czas+0.00001<rr.czas+z.czas)
                                 )
                  )
      )
      OR
      (
       /* zgloszenie zadania, kt�re zosta¸o zaakceptowane, ale za p�zno */
       NOT EXISTS (SELECT * FROM zgloszenia zz 
                            WHERE zz.konkurs=z.konkurs AND zz.zespol=z.zespol AND zz.zadanie=z.zadanie 
                              AND zz.ok=1 AND z.data_zgloszenia>zz.data_zgloszenia)
       AND
       /* i przenosi na poczatek kolejki */
       NOT EXISTS (SELECT * FROM tw_ranking r,tw_ranking rr WHERE r.konkurs=z.konkurs AND rr.konkurs=z.konkurs AND rr.zespol=z.zespol 
                             AND ( r.liczbaZadan>rr.liczbaZadan 
                                   OR (r.liczbaZadan=rr.liczbaZadan AND r.czas+0.00001<rr.czas+z.czas-(SELECT MIN(zz.czas) FROM tw_zgloszenia_z_czasem zz WHERE z.konkurs=zz.konkurs AND z.zespol=zz.zespol AND z.zadanie=zz.zadanie AND zz.ok=1))
                                 )
                  )
      )
    )
  ORDER BY z.id ASC;

Kolokwium 2

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.

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:

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)
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.
wypisz identyfikatory (uporządkowane rosnąco) nieprawidłowych zgłoszeń, tzn. takich, które zostały przesłane po zakończeniu konkursu
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ń,
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.
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
wypisz raport zawierający nazwę drużyny oraz sumę nagród uporządkowany malejąco według sumy nagród
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).

Rozwiązanie

/* zadanie 1 */
SELECT z.id,z.konkurs,z.zespol,z.zadanie,TO_CHAR(z.data_zgloszenia,'YYYY-MM-DD HH24:MI:SS') as data
  FROM zgloszenia z
 WHERE z.ok=1
 ORDER BY z.data_zgloszenia, z.zespol;
 
 
/* zadanie 2 */
CREATE OR REPLACE VIEW tw_zadania_druzyn AS 
    SELECT distinct zespol,zadanie,konkurs FROM zgloszenia WHERE ok=1;
 
SELECT uk.zespol,
       COUNT(*) as LiczbaKonkursow,
       (SELECT COUNT(*) FROM tw_zadania_druzyn tmp WHERE uk.zespol=tmp.zespol) as RozwiazaneZadania
  FROM UczestnicyKonkursow uk GROUP by uk.zespol ORDER by uk.zespol;
 
/* zadanie 3 */
SELECT z.id,z.konkurs,z.zespol,z.zadanie
  FROM zgloszenia z,konkursy k
 WHERE z.konkurs=k.nazwa
   AND (z.data_zgloszenia>k.koniec)
 ORDER BY z.id ASC;
 
 
/* zadanie 4 */
SELECT z.id,z.konkurs,z.zespol,z.zadanie
  FROM zgloszenia z
 WHERE NOT EXISTS
  (SELECT * FROM zgloszenia zz 
    WHERE zz.konkurs=z.konkurs AND zz.zespol=z.zespol
      AND zz.zadanie=z.zadanie
      AND zz.ok=1 AND zz.data_zgloszenia<z.data_zgloszenia)
 ORDER BY z.data_zgloszenia ASC;
 
 
/* zadanie 5 */
SELECT z.konkurs,z.zadanie,MAX(z.data_zgloszenia-k.poczatek)*24*60 as Czas
  FROM zgloszenia z,konkursy k
 WHERE z.konkurs=k.nazwa AND z.ok=1
   AND NOT EXISTS (SELECT * FROM zgloszenia zz WHERE zz.konkurs=z.konkurs AND zz.zespol=z.zespol AND zz.zadanie=z.zadanie
                      AND zz.ok=1 AND zz.data_zgloszenia<z.data_zgloszenia)
GROUP BY z.konkurs,z.zadanie 
 ORDER BY z.konkurs,z.zadanie;
 
/* zadanie 6 */
DROP VIEW tw_czasy2;
CREATE OR REPLACE VIEW tw_czasy2 AS
	SELECT z.konkurs,z.zespol,z.zadanie,
           SUM(z.data_zgloszenia-k.poczatek)*24*60 as czas_w_min
        FROM zgloszenia z,konkursy k
       WHERE z.konkurs=k.nazwa
         AND NOT EXISTS (SELECT * FROM zgloszenia zz WHERE z.konkurs=zz.konkurs AND z.zadanie=zz.zadanie AND z.zespol=zz.zespol
                         AND zz.ok=1 AND zz.data_zgloszenia<z.data_zgloszenia)
         AND EXISTS (SELECT * FROM zgloszenia zz WHERE z.konkurs=zz.konkurs AND z.zadanie=zz.zadanie AND z.zespol=zz.zespol
                         AND zz.ok=1 AND zz.data_zgloszenia>=z.data_zgloszenia)
         GROUP BY z.konkurs,z.zespol,z.zadanie
  ;
 
DROP VIEW tw_ranking2;
CREATE OR REPLACE VIEW tw_ranking2 AS
 SELECT uk.konkurs,uk.zespol,
	(SELECT COUNT(DISTINCT zadanie) FROM zgloszenia z WHERE z.konkurs=uk.konkurs AND z.zespol=uk.zespol AND ok=1) as LiczbaZadan,
        NVL((SELECT SUM(c.czas_w_min) FROM tw_czasy2 c WHERE c.konkurs=uk.konkurs AND c.zespol=uk.zespol),0) as Czas
 FROM UczestnicyKonkursow uk
ORDER BY uk.konkurs ASC,LiczbaZadan DESC,czas ASC;
 
SELECT * FROM tw_ranking2;
 
 
/* zadanie 7 */
/* +0.000001 jest koniecznne ze wzglŤdu na b¸Ťdy arytmetyki przy por�wnywaniu kolumny Czas */
SELECT zespol,NVL(SUM(wygrana),0) as sumawygranych
FROM (
  SELECT uk.zespol,uk.konkurs,
   (SELECT SUM(k.nagroda)
   FROM Konkursy k
   WHERE uk.konkurs=k.nazwa
     AND (SELECT COUNT(*) FROM tw_ranking2 r,tw_ranking2 rr 
           WHERE r.konkurs=k.nazwa AND rr.konkurs=k.nazwa AND rr.zespol=uk.zespol
             AND (r.liczbaZadan>rr.liczbaZadan OR (r.liczbaZadan=rr.liczbaZadan AND r.Czas+0.000001<rr.Czas))
         )<k.liczba_nagrod
   ) as wygrana
  FROM UczestnicyKonkursow uk
)
GROUP BY zespol
ORDER BY NVL(SUM(wygrana),0) DESC;
 
 
/* zadanie 8 */
 
DROP VIEW tw_zgloszenia_z_czasem2;
CREATE OR REPLACE VIEW tw_zgloszenia_z_czasem2
 AS SELECT z.*,
     (SELECT SUM(zz.data_zgloszenia-k.poczatek)*24*60 FROM zgloszenia zz WHERE zz.konkurs=z.konkurs AND zz.zespol=z.zespol AND zz.zadanie=z.zadanie AND zz.data_zgloszenia<z.data_zgloszenia) as czas
 FROM Zgloszenia z,Konkursy k
 WHERE z.konkurs=k.nazwa
 ORDER by z.id;
 
SELECT z.*
  FROM tw_zgloszenia_z_czasem2 z
  WHERE
    z.ok=0
    AND EXISTS ( /* zgloszenie od druzyny ktora nie jest na pierwszym miejscu */
       SELECT * FROM tw_ranking2 r,tw_ranking2 rr WHERE r.konkurs=z.konkurs AND rr.konkurs=z.konkurs AND rr.zespol=z.zespol 
                 AND (r.liczbaZadan>rr.liczbaZadan OR (r.liczbaZadan=rr.liczbaZadan AND r.czas+0.00001<rr.czas))
    )
    AND (
      (
       /* zgloszenie zadania, ktore nie zostalo zaakceptowane */
       NOT EXISTS (SELECT * FROM zgloszenia zz WHERE zz.konkurs=z.konkurs AND zz.zespol=z.zespol AND zz.zadanie=z.zadanie AND zz.ok=1)
       AND 
       /* i przenosi na poczatek kolejki */
       NOT EXISTS (SELECT * FROM tw_ranking2 r,tw_ranking2 rr WHERE r.konkurs=z.konkurs AND rr.konkurs=z.konkurs AND rr.zespol=z.zespol 
                             AND ( r.liczbaZadan>rr.liczbaZadan+1 
                                   OR (r.liczbaZadan=rr.liczbaZadan+1 AND r.czas+0.00001<rr.czas+z.czas)
                                 )
                  )
      )
      OR
      (
       /* zgloszenie zadania, kt�re zosta¸o zaakceptowane, ale za p�zno */
       NOT EXISTS (SELECT * FROM zgloszenia zz 
                            WHERE zz.konkurs=z.konkurs AND zz.zespol=z.zespol AND zz.zadanie=z.zadanie 
                              AND zz.ok=1 AND z.data_zgloszenia>zz.data_zgloszenia)
       AND
       /* i przenosi na poczatek kolejki */
       NOT EXISTS (SELECT * FROM tw_ranking2 r,tw_ranking2 rr WHERE r.konkurs=z.konkurs AND rr.konkurs=z.konkurs AND rr.zespol=z.zespol 
                             AND ( r.liczbaZadan>rr.liczbaZadan 
                                   OR (r.liczbaZadan=rr.liczbaZadan AND r.czas+0.00001<rr.czas+z.czas-(SELECT MIN(zz.czas) FROM tw_zgloszenia_z_czasem2 zz WHERE z.konkurs=zz.konkurs AND z.zespol=zz.zespol AND z.zadanie=zz.zadanie AND zz.ok=1))
                                 )
                  )
      )
    )
  ORDER BY z.id ASC;

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);