POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

BAZY DANYCH I

LABORATORIUM

Kierunek
studiów:

Informatyka

Rok studiów:

III

Numer grupy:

L4

Rok akademicki: 2012/2013

Semestr:

V

Temat:

Ćwiczenie 3

Lp.

Nr indeksu

Imię i nazwisko

1.

78202

Kurasz Arkadiusz

Termin zajęć:

Prowadzący:

dzień:

Wtorek

mgr inż. Alina Stefanowska-Kędzia

godzina:

10:05

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 1

Zad. 1.
Znaleźć pracowników, którzy mają zarobki wyższe niż najwyższe zarobki w zespole
INFORMATYKA.

Zapytanie:

select * from prac where placa_pod >

(select max(placa_pod) from prac p where p.id_zesp =

(select id_zesp from zesp where nazwa = 'INFORMATYKA'));

Wynik zapytania:

ID_PRAC NAZWISKO ETAT SZEF ZATRUDNIONY PLACA_POD PLACA_DOD ID_ZESP
------- --------------- ---------- ---- ----------- --------- --------- -------
110 BABACKI PROFESOR 100 01-MAY-73 3350 210 40
120 CABACKI PROFESOR 100 01-SEP-77 3070 30

Zad. 2.
Wyświetlić zespoły, które nie zatrudniają pracowników stosując podzapytanie.

Zapytanie:

select * from zesp where id_zesp not in

(select id_zesp from prac group by id_zesp);

Zapytanie bez użycia
podzapytania, ale z użyciem
operatora połączenia
zewnętrznego:

select z.* from zesp z, prac p
where z.id_zesp = p.id_zesp(+) and p.id_zesp is null;

Wynik zapytania:

ID_ZESP NAZWA ADRES
------- -------------------- --------------------
50 ELEKTROTECHNIKA LUBOSZYCKA 9

Zad. 3.
Wyświetlić numery zespołów zatrudniających więcej niż 3 pracowników.

Zapytanie:

select id_zesp, count(*) "LICZBA PRACOWNIKÓW" from prac group by id_zesp having count(*) > 3;

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 2

Wynik zapytania:

ID_ZESP LICZBA PRACOWNIKÓW
------- ------------------
30 4
20 7

Zad.4.
Wykorzystując grupowanie krotek sprawdzić, czy wszystkie numery pracowników sa unikalne.

Zapytanie:

select id_prac from prac group by id_prac having count(*) > 1;

Wynik zapytania:

no rows selected

Komentarz:
Po zgrupowaniu wg id_prac, zliczamy liczebność tych grup i wybieramy tylko te, których
liczebność jest większa od 1. Nie wybrano żadnych rekordów, co oznacza, że każde id_prac
jest unikalne.

Zad.5.
Wyświetlić najniższą płacę podstawową pracowników w grupie każdego przełożonego.
Pominąć grupy, w których minimalne płace podstawowe są niższe od 1000. Wyniki
uporządkować wg rosnących zarobków.

Zapytanie:

select szef, min(placa_pod) "NAJNIŻSZA PŁACA PODSTAWOWA"
from prac group by szef
having min(placa_pod) > 1000
order by min(placa_pod) asc;

Wynik zapytania:

SZEF NAJNIZSZA PLACA PODSTAWOWA
---- --------------------------
140 1208
130 1250
100 1410.2
110 1480
120 1480
2730

6 rows selected

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 3

Zad.6.
Bez korzystania z podzapytania skorelowanego wyświetlić najkrócej zatrudnionych
pracowników każdego zespołu. Wyniki uszeregować w kolejności zatrudnienia.

Zapytanie:

select id_zesp, nazwisko "NAJKRÓCEJ ZATRUDNIONY", zatrudniony from prac
where zatrudniony in
(select max(zatrudniony) from prac group by id_zesp) order by zatrudniony;

Wynik zapytania:

ID_ZESP NAJKRÓCEJ ZATRUDNIONY ZATRUDNIONY
------- --------------------- -----------
40 BABACKI 01-MAY-73
10 JOTBACKA 20-FEB-85
20 MABACKI 01-OCT-93
30 KABACKI 15-JUL-94

Zad. 7.
Wyświetlić pracowników zarabiających najmniej w ramach swoich grup etatowych. Wyniki
uszeregować w malejącym porządku zarobków. Wykorzystać podzapytanie skorelowane.

Zapytanie:

select * from prac p
where placa_pod = (select min(placa_pod) from prac where etat = p.etat)
order by placa_pod desc;

Wynik zapytania:

ID_PRAC NAZWISKO ETAT SZEF ZATRUDNIONY PLACA_POD PLACA_DOD ID_ZESP
------- --------------- ---------- ---- ----------- --------- --------- -------
140 EBACKI PROFESOR 130 15-SEP-75 2830 105 20
100 ABACKI DYREKTOR 01-JAN-68 2730 420.5 10
160 GABACKI ADIUNKT 130 01-MAR-85 1590 20
180 JOTBACKA SEKRETARKA 100 20-FEB-85 1410.2 10
190 IBACKI ASYSTENT 140 01-SEP-93 1371 20
200 KABACKI STAZYSTA 140 15-JUL-94 1208 30

6 rows selected

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 4

Zad. 8.
Wyświetl pracowników, którzy nie mają szefa, wypisując dodatkowo tekst 'brak szefa'.

Zapytanie:

select rpad(nazwisko, length(nazwisko) + length('brak szefa '), ' brak szefa') "PRACOWNIK"
from prac
where szef is null;

Wynik zapytania:

PRACOWNIK
--------------------
ABACKI brak szefa

Zad. 9.
Wyświetlić w poniższej formie informacje o wszystkich pracownikach

Pracownik i jego etat
--------------------------------------------------------
BABACKI********&&&&&&&&ADIUNKT

Zapytanie:

select
rpad(nazwisko, length(nazwisko) + length(etat) + 16,
lpad(etat, length(etat) + 16, '********&&&&&&&&'))

"PRACOWNIK I JEGO ETAT"

from prac;

Wynik zapytania:

PRACOWNIK I JEGO ETAT
-----------------------------------
ABACKI********&&&&&&&&DYREKTOR
BABACKI********&&&&&&&&PROFESOR
CABACKI********&&&&&&&&PROFESOR
DABACKI********&&&&&&&&PROFESOR
EBACKI********&&&&&&&&PROFESOR
FABACKI********&&&&&&&&ADIUNKT
GABACKI********&&&&&&&&ADIUNKT
HABACKI********&&&&&&&&ASYSTENT
IBACKI********&&&&&&&&ASYSTENT
JOTBACKA********&&&&&&&&SEKRETARKA
KABACKI********&&&&&&&&STAZYSTA
LABACKI********&&&&&&&&STAZYSTA
MABACKI********&&&&&&&&ASYSTENT
NABACKI********&&&&&&&&ASYSTENT

14 rows selected

Ten sam wynik możemy uzyskać dużo łatwiej – wykorzystując funkcję concat, która nie była
podana w materiałach.

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 5

Zapytanie:

select concat(concat(nazwisko,'********&&&&&&&&'), etat) "PRACOWNIK I JEGO ETAT" from prac;

Zad. 10.
Wyświetlić nazwiska pracowników oraz ich kod złożony z dwóch pierwszych liter nazwiska
i

numeru pracownika zawartego w atrybucie id_prac w następujący sposób:

NAZWISKO

KOD

-----------------------------
ABACKI

AB1010

Zapytanie:

select nazwisko,

rpad(substr(nazwisko, 0, 2), 6, substr(id_prac, 0, 2))

"KOD"

from prac;

Wynik zapytania:

NAZWISKO KOD
--------------- ------
ABACKI AB1010
BABACKI BA1111
CABACKI CA1212
DABACKI DA1313
EBACKI EB1414
FABACKI FA1515
GABACKI GA1616
HABACKI HA1717
IBACKI IB1919
JOTBACKA JO1818
KABACKI KA2020
LABACKI LA2121
MABACKI MA2222
NABACKI NA2323

14 rows selected