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 Lekarz i Szpital:

[Lekarz]
id | nazwisko | imie | kod_sz | staz
----+------------+--------+--------+------------
1 | Ambrożek | Olaf | MW | 81
2 | Chyda | Maria | WOJ |
3 | Majerska | Danuta | WOJ | 162
4 | Rychcik | Józef | MW | 24
5 | Steckowski | Józef | MW | 174

[Szpital]
id | nazwa | miasto
------+---------------+--------
WOJ | Wojewódzki | Gdańsk
PCK | PCK | Gdynia
MW | Mar. Wojennej | Gdynia
PKP | Kolejowy | Tczew

'id' jest kluczem głównym w tabeli Lekarz, jest nadawany automatycznie jako kolejna liczba
naturalna, 'kod_sz' jest kluczem obcym w tabeli Lekarz wskazującym na klucz główny 'id' w tabeli
Szpital, staż liczony jest w pełnych miesiącach.

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 szpital(

id varchar(5) primary key,

nazwa varchar(22),

miasto varchar(12)

);

create table lekarz(

id serial,

nazwisko varchar(22),

imie varchar(22),

kod_sz varchar(5) references szpital(id),

staz integer

);

insert into szpital (id,nazwa,miasto)

values('PCK','PCK','Gdynia');

insert into lekarz(nazwisko,imie,kod_sz,staz)

values('Chyda','Maria','WOJ',NULL);

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

Zad. 3. Jak b dzie wygl da wynik zapytania

ę

ą ł

SELECT kod_sz, COUNT(*) AS liczba_lekarzy FROM Lekarz GROUP BY

kod_sz HAVING COUNT(*)>1

Zad. 4. Jak b dzie wygl da wynik zapytania

ę

ą ł

SELECT imie,nazwisko from Lekarz
WHERE kod_sz IN (SELECT id FROM Szpital

WHERE miasto='Gdynia');

kod_sz | liczba_lekarzy

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

WOJ | 2

MW | 3

(2 rows)

imie | nazwisko

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

Olaf | Ambrożek

Józef | Rychcik

Józef | Steckowski

(3 rows)

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

ń

ę

ą

w tabeli lekarzy. W kolumnie ustaw dat dzisiejsz .

ę

ą

Zad. 6. Usu z systemu tabel ze szpitalami.

ń

ę

alter table Lekarz add column data_zatr date default

date('2008-15-24')

;

drop table Szpital 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 wszystkim lekarzom w szpitalu wojewódzkim sta o 2 miesi ce.

ę

ż

ą

Zad. 8. Wypisz bez powtórze imiona lekarzy ze szpitala

ń

wojewódzkiego w kolejno ci

ś

alfabetycznej.

Zad. 9. Wypisz nazwy szpitali, nie zatrudniaj ce adnych lekarzy z tabeli.

ą

ż

Zad. 10. Usu z bazy szpitale nie zatrudniaj ce adnego lekarza.

ń

ą

ż

Zad. 11. Podaj najwi kszy sta spo ród lekarzy pracuj cych w Gdyni.

ę

ż

ś

ą

Zad. 12. Podaj imi i nazwisko lekarza z Gdyni o najwi kszym sta u.

ę

ę

ż

Zad. 13. Usu dane o szpitalach gdy skich, o ile nie zatrudniaj lekarzy z tabeli.

ń

ń

ą

zad. 7 update lekarz set staz=staz+2 WHERE kod_sz

IN

(select id from

szpital where nazwa like 'Wojewódzki');

zad. 8 select distinct imie from lekarz WHERE kod_sz

IN

(select id

from szpital where nazwa like 'Wojewódzki') order by imie asc;

zad. 9 select nazwa from szpital where id not in (select kod_sz from

lekarz);

select nazwa from Szpital left join Lekarz on

kod_sz=Szpital.id where kod_sz is null;

select nazwa from Szpital where not exists (select 1 from

Lekarz where kod_sz=Szpital.id);

zad. 10 delete from szpital where id not in(select kod_sz from

lekarz);

zad. 11 select max(staz) from lekarz L inner join szpital Sz on

L.kod_sz=Sz.id where miasto='Gdynia';

zad. 12 select imie, nazwisko from lekarz L inner join szpital Sz on

L.kod_sz=Sz.id where miasto='Gdynia' and staz=(select max(staz) from

lekarz L inner join szpital Sz on L.kod_sz=Sz.id where

miasto='Gdynia');

zad. 13 delete from szpital where miasto='Gdynia' and id not in

(select kod_sz from lekarz);