plik

Kolokwium – Bazy danych, SQL

Nazwisko:

WZÓR

Imię:

indeks:

Dane są następujące tabele Lekarz i Szpital:

'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

(select id from

szpital where nazwa like 'Wojewódzki');

zad. 8 select distinct imie from lekarz WHERE kod_sz

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