Kolokwium – Bazy danych, SQL

Nazwisko:

WZÓR

WZÓR

WZÓR

WZÓR

WZÓR

WZÓR

Imię:

indeks:

Dane są następujące tabele Wycieczka i Biuro:

[Wycieczka]
 id | miejsce   | kraj    | kod_b | cena
----+-----------+---------+-------+--------
  1 | Liguarre  | Francja | QRK   | 1740.34
  2 | Orzysz    | Polska  | DRYL  | 
  3 | Rzym      | Włochy  | QRK   | 1620.52
  4 | Jylland   | Szwecja | DRYL  | 2402.56
  5 | St. Malo  | Francja | QRK   | 1850.20

[Biuro]
  id   |   nazwa             | miasto
-------+---------------------+--------
 QRK   | Q.R.K.              | Sopot
 TROP  | Tropiki dla Każdego | Gdańsk
 DRYL  | Błekitny Dryl       | Rumia
 QUEST | Quest               | Sopot

'id' jest kluczem głównym w tabeli Wycieczka, jest nadawany automatycznie jako kolejna liczba 
naturalna , 'kod_b' jest kluczem obcym w tabeli Wycieczka wskazującym na klucz główny 'id' w tabeli 
Biuro.

Zad. 1. Zapisz w SQL definicje tych tabel (tylko definicje, nie konkretn  tre

).

ą

ść

Zad. 2. Wstaw do ka dej z tabel drugi od góry wiersz z odpowiedniej tabeli powy ej.

ż

ż

create table Biuro( 

id varchar(6) primary key, 

nazwa varchar(22), 

miasto varchar(22) 

); 

create table Wycieczka( 

id serial, 

miejsce varchar(22), 

kraj varchar(12), 

kod_b varchar(6) references Biuro(id), 

cena numeric (7,2) 

); 

insert into Biuro (id,nazwa,miasto) 

values('TROP','Tropiki dla Każdego','Gdańsk'); 

insert into Wycieczka (miejsce,kraj,kod_b,cena) 

values('Orzysz','Polska','DRYL',NULL); 

Odwołujemy się do treści tabel podanej w zad.1

Zad. 3.  Jak b dzie wygl da  wynik zapytania

ę

ą ł

SELECT kod_b, COUNT(*) AS liczba_wycieczek FROM Wycieczka GROUP BY 
kod_b HAVING COUNT(*)>1;

Zad. 4.  Jak b dzie wygl da  wynik zapytania

ę

ą ł

SELECT miejsce,kraj from Wycieczka 

WHERE kod_b IN (SELECT id FROM Biuro WHERE miasto='Sopot');

 kod_b | liczba_wycieczek 

-------+------------------ 

 QRK   |                3 

 DRYL  |                2 

(2 rows) 

 miejsce  |  kraj   

----------+--------- 

 Liguarre | Francja 

 Rzym     | Włochy 

 St. Malo | Francja 

(3 rows) 

Zad. 5. Zmie  definicj  tabel z zad. 1 wstawiaj c dodatkowy atrybut dla daty wycieczki

ń

ę

ą

 

w tabeli wycieczek.  Domy lna data wycieczki niech b dzie dat  dzisiejsz .

ś

ę

ą

ą

Zad. 6. Usu  z systemu tabel  z biurami podró y.

ń

ę

ż

alter table Wycieczka add column data_wyc date default '2008-5-24'; 

drop table Biuro cascade; 

Posługując się strukturą tabel z zad. 1 skonstruuj zapytania SQL wykonujące polecenia opisane 
poniżej. Zapytania powinny zwracać poprawne wyniki bez względu na to jakie dane znajdują się w 
tabelach (tzn. nie mogą odwoływać się bezpośrednio do konkretnych danych).

Zad. 7. Zwi ksz cen  wszystkich wycieczek biura Q.R.K o 5%.

ę

ę

Zad. 8. Wypisz bez powtórze  kraje docelowe wycieczek z biura podró y Q.R.K. w

ń

ż

 

kolejno ci  alfabetycznej.

ś

Zad. 9. Wypisz nazwy biur nie maj cych  adnych wycieczek w ofercie.;

ą

ż

Zad. 10. Usu  biura jak wy ej.

ń

ż

Zad. 11. Podaj najwi ksz  cen  spo ród wycieczek organizowanych przez biura sopockie.

ę

ą

ę

ś

Zad. 12. Podaj miejsce i kraj najdro szej wycieczki organizowanej przez biuro sopockie.

ż

Zad. 13. Usu  dane biur sopockich, ale tylko pod warunkiem,  e nie organizuj   adnych

ń

ż

ą ż

 

wycieczek.

zad. 7 update Wycieczka set cena=cena*1.05 WHERE kod_b IN (select id 

from Biuro where nazwa like 'Q.R.K.');

zad. 8  select distinct kraj from Wycieczka WHERE kod_b IN (select id 

from Biuro where nazwa like 'Q.R.K.'); 

zad. 9 select nazwa from Biuro where id not in (select kod_b from 

Wycieczka); 

        select nazwa from Biuro left join Wycieczka on kod_b=Biuro.id 

where kod_b is null; 

        select nazwa from Biuro where not exists (select 1 from 

Wycieczka where kod_b=Biuro.id); 

zad. 10 delete from Biuro where id not in (select kod_b from 

Wycieczka); 

zad. 11 select max(cena) from Wycieczka W inner join Biuro B on 

W.kod_b=B.id where miasto='Sopot'; 

zad. 12 select miejsce,kraj from Wycieczka W inner join Biuro B on 

W.kod_b=B.id where miasto='Sopot' and cena=(select max(cena) from 

Wycieczka W inner join Biuro B on W.kod_b=B.id where miasto='Sopot'); 

zad. 13 delete from Biuro where  miasto='Sopot' and id not in (select 

kod_b from Wycieczka);