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