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