BAZY DANYCH

Dr inż. Wikarek Jarosław

Katedra Systemów Sterowania i Zarządzania

E-MAIL: [e-mail]

1

Język SQL

PRZYKŁADY

Bazy danych

Struktura systemu

Pracownicy

etaty

## id_prac

VARCHAR2(3)

## etat

VARCHAR2(20)

nazwisko

VARCHAR2(20)

plac_min

NUMERIC(6,2)

2

## id_prac

VARCHAR2(3)

## etat

VARCHAR2(20)

nazwisko

VARCHAR2(20)

plac_min

NUMERIC(6,2)

# etat

VARCHAR2(20)

plac_max NUMERIC(6,2)

# id_szefa

VARCHAR2(3)

zatrudniony DATE

zespoly

placa_pod NUMERIC(6,2)

## id_zesp

VARCHAR2(3)

placa_dod NUMERIC(6,2)

nazwa

VARCHAR2(20)

# id_zesp

VARCHAR2(3)

adres

VARCHAR2(60)

Bazy danych

Przykładowe dane - pracownicy

3

Bazy danych

Przykładowe dane - zespoły

4

Bazy danych

Przykładowe dane - etaty

5

Bazy danych

NAPISAĆ NASTĘPUJĄCE ZAPYTANIA

6

NAPISAĆ NASTĘPUJĄCE ZAPYTANIA

Bazy danych

Zapytanie 1

Wyświetl: nazwiska, etaty, numery zespołów i

7

Wyświetl: nazwiska, etaty, numery zespołów i
nazwy zespołów wszystkich pracowników.

Bazy danych

Zapytanie 1

8

select p.nazwisko, p.etat, p.id_zesp, z.nazwa

from pracownicy p, zespoly z
where p.id_zesp=z.id_zesp

Bazy danych

Zapytanie 2

Wyświetl wszystkich pracowników z ul. NISKA 10.

9

Wyświetl wszystkich pracowników z ul. NISKA 10.
Uporządkuj wyniki według nazwisk pracowników.

Bazy danych

Zapytanie 2 (rozwiązanie 1)

select p.nazwisko

from pracownicy p, zespoly z

10

from pracownicy p, zespoly z
where

p.id_zesp = z.id_zesp and
adres = ‘NISKA 10'

order by nazwisko

Bazy danych

Zapytanie 2 (rozwiązanie 2)

select nazwisko

from pracownicy

11

from pracownicy
where id_zesp in

( select id_zesp

from zespoly
where adres = ‘NISKA 10‘

)

order by nazwisko

Bazy danych

Zapytanie 3

Wyświetl nazwiska, miejsca pracy oraz nazwy

12

Wyświetl nazwiska, miejsca pracy oraz nazwy
zespołów tych pracowników, których miesięczna
pensja przekracza 400.

Bazy danych

Zapytanie 3

select p.nazwisko, z.adres, z.nazwa

13

select p.nazwisko, z.adres, z.nazwa

from pracownicy p, zespoly z
where

p.id_zesp=z.id_zesp and
p.placa_pod >400

Bazy danych

Zapytanie 4

Dla każdego pracownika wyświetl jego kategorię

14

Dla każdego pracownika wyświetl jego kategorię
płacową i widełki płacowe w jakich mieści się
pensja pracownika.

Bazy danych

Zapytanie 4

15

select

p.nazwisko, p.etat, e.plac_min,
p.placa_pod, e.plac_max

from

pracownicy p, etaty e

where p.etat=e.etat

Bazy danych

Zapytanie 5

Wyświetl nazwiska i etaty pracowników, których

16

Wyświetl nazwiska i etaty pracowników, których
rzeczywiste

zarobki

odpowiadają

widełkom

płacowym przewidzianym dla sekretarek.

Bazy danych

Zapytanie 5

select p.nazwisko, p.etat

17

select p.nazwisko, p.etat

from pracownicy p
where placa_pod between

(select plac_min from etaty where etat='SEKRETARKA')

and

(select plac_max from etaty where etat='SEKRETARKA')

Bazy danych

Zapytanie 6

Wyświetl nazwiska, etaty, wynagrodzenia, kategorie

18

Wyświetl nazwiska, etaty, wynagrodzenia, kategorie
płacowe

i

nazwy

zespołów

pracowników

nie

będących asystentami. Wyniki uszereguj zgodnie z
malejącym wynagrodzeniem.

Bazy danych

Zapytanie 6

select

p.nazwisko, p.etat, p.placa_pod,

19

select

p.nazwisko, p.etat, p.placa_pod,
e.plac_min, e.plac_max, z.nazwa

from

pracownicy p, etaty e, zespoly z

where

p.etat=e.etat and p.id_zesp=z.id_zesp
and p.etat <>'ASYSTENT'

order by p.placa_pod desc

Bazy danych

Zapytanie 7

Wyświetl informacje o w układzie: nazwisko, etat,
placa_pod, plac_min, plac_max, nazwa zespołu

20

placa_pod, plac_min, plac_max, nazwa zespołu
tych pracownikach, którzy są asystentami lub
adiunktami i których roczne dochody przekraczają
5500. Roczne dochody to dwunastokrotność płacy
podstawowej powiększona o ewentualną płacę
dodatkową. Dane posortuj malejąco według pensji
podstawowej.

Bazy danych

Zapytanie 7

select

p.nazwisko, p.etat, p.placa_pod,
e.plac_min, e.plac_max, z.nazwa

21

e.plac_min, e.plac_max, z.nazwa

from

pracownicy p, etaty e, zespoly z

where p.etat=e.etat and p.id_zesp=z.id_zesp
and ( p.etat ='ASYSTENT' or p.etat ='ADIUNKT')
and (p.placa_pod*12 + nvl(p.placa_dod,0))

>5500

order by p.placa_pod desc

Bazy danych

Zapytanie 8

Wyświetl nazwiska i numery pracowników wraz z

22

Wyświetl nazwiska i numery pracowników wraz z
numerami i nazwiskami ich szefów.

Bazy danych

Zapytanie 8

select

p.id_prac as nr_pracownika,

23

select

p.id_prac as nr_pracownika,
p.nazwisko as naz_pracownika,
t.id_prac as nr_szefa,
t.nazwisko as naz_szefa

from pracownicy p, pracownicy t
where p.id_szefa=t.id_prac

Bazy danych

Zapytanie 9

Zmodyfikuj powyższe zlecenie w ten sposób, aby

24

Zmodyfikuj powyższe zlecenie w ten sposób, aby
było

możliwe

wyświetlenie

pracownika

Asinski

(który nie ma szefa).

Bazy danych

Zapytanie 9

select

p.id_prac as nr_pracownika,

25

select

p.id_prac as nr_pracownika,
p.nazwisko as naz_pracownika,
t.id_prac as nr_szefa,
t.nazwisko as naz_szefa

from pracownicy p, pracownicy t
where p.id_szefa=t.id_prac(+)

Bazy danych

Zapytanie 10

Dla każdego zespołu wyświetl liczbę zatrudnionych

26

Dla każdego zespołu wyświetl liczbę zatrudnionych
w nim pracowników i ich średnią płacę.

Bazy danych

Zapytanie 10

select

id_zesp,count(*),

27

select

id_zesp,count(*),
round(avg(nvl(placa_pod,0)),2)

from

pracownicy

group by id_zesp

Bazy danych

Zapytanie 11

Dla

każdego

pracownika

posiadającego

28

Dla

każdego

pracownika

posiadającego

podwładnych wyświetl ich liczbę. Wyniki posortuj
zgodnie z malejącą liczbą podwładnych.

Bazy danych

Zapytanie 11 – źle

select

id_szefa, count(*) as ile

29

select

id_szefa, count(*) as ile

from pracownicy
group by id_szefa
order by ile desc

Bazy danych

Zapytanie 11

select id_szefa, count(*) as ile

30

select id_szefa, count(*) as ile

from pracownicy
where id_szefa is not null
group by id_szefa
order by ile desc

Bazy danych

Zapytanie 12

Wyświetl

nazwiska

i

daty

zatrudnienia

31

Wyświetl

nazwiska

i

daty

zatrudnienia

pracowników, którzy zostali zatrudnieni nie później
niż 10 lat (3650 dni) po swoich przełożonych.

Bazy danych

Zapytanie 12

select p.nazwisko,

32

select p.nazwisko,

to_char(p.zatrudniony,'dd-mm-yyyy') as od_kiedy

from pracownicy p,pracownicy t
where

p.id_szefa=t.id_prac and
p.zatrudniony-t.zatrudniony <3650

Bazy danych

Zapytanie 13

Wyświetl

nazwy

etatów,

na

które

przyjęto

33

Wyświetl

nazwy

etatów,

na

które

przyjęto

pracowników zarówno w 1992 jak i 1993 roku.

Bazy danych

Zapytanie 13

select distinct etat

34

select distinct etat

from pracownicy
where

to_char(zatrudniony,'yy')='92' or
to_char(zatrudniony,'yy')='93'

Bazy danych

Zapytanie 14

Wyświetl

numer

zespołu,

który

nie

zatrudnia

35

Wyświetl

numer

zespołu,

który

nie

zatrudnia

żadnych pracowników.

Bazy danych

Zapytanie 14

select id_zesp

36

select id_zesp

from zespoly
where id_zesp not in

(select id_zesp from pracownicy)

Bazy danych

Zapytanie 15

Wyświetl nazwiska i etaty pracowników pracujących

37

Wyświetl nazwiska i etaty pracowników pracujących
w tym samym zespole co pracownik o nazwisku
Csinski.

Bazy danych

Zapytanie 15

select nazwisko, etat

38

select nazwisko, etat

from pracownicy
where id_zesp=

(select id_zesp

from pracownicy
where nazwisko=‘Csinski')

Bazy danych

Zapytanie 15

Wyświetl

najdłużej

pracujących

pracowników

39

Wyświetl

najdłużej

pracujących

pracowników

każdego zespołu. Uszereguj wyniki zgodnie z
kolejnością zatrudnienia.

Bazy danych

Zapytanie 15

select id_prac, id_zesp,

to_char(zatrudniony,'dd-mm-yyyy') od_kiedy

40

to_char(zatrudniony,'dd-mm-yyyy') od_kiedy

from pracownicy
where (zatrudniony) in

(

select max(zatrudniony) from pracownicy

group by id_zesp)

order by od_kiedy

DLACZEGO ZŁE ???

Bazy danych

Zapytanie 15

select id_prac, id_zesp,

to_char(zatrudniony,'dd-mm-yyyy') od_kiedy

41

to_char(zatrudniony,'dd-mm-yyyy') od_kiedy

from pracownicy
where (zatrudniony, id_zesp) in

(select max(zatrudniony), id_zesp

from pracownicy group by id_zesp)

order by od_kiedy

DLACZEGO ŹLE POSORTOWANE ???

Bazy danych

Zapytanie 16

Wyświetl

nazwiska

pracowników

zarabiających

42

Wyświetl

nazwiska

pracowników

zarabiających

więcej niż średnia pensja dla ich etatu.

Bazy danych

Zapytanie 16

select nazwisko

43

select nazwisko

from pracownicy p,

(select round(avg(placa_pod),2) kwota, etat

from pracownicy
group by etat)

t

where

p.etat=t.etat and
placa_pod >kwota

Bazy danych

Zapytanie 17

Wyświetl nazwiska i pensje pracowników którzy

44

Wyświetl nazwiska i pensje pracowników którzy
zarabiają co najmniej 75% pensji swojego szefa.

Bazy danych

Zapytanie 17

select p.nazwisko, p.placa_pod

45

select p.nazwisko, p.placa_pod

from pracownicy p, pracownicy t

where p.id_szefa=t.id_prac and

p.placa_pod >0.75*t.placa_pod

Bazy danych

Zapytanie 18

Wyświetl

numer

zespołu

wypłacającego

46

Wyświetl

numer

zespołu

wypłacającego

miesięcznie

swoim

pracownikom

najwięcej

pieniędzy.

Bazy danych

Zapytanie 18

select numer from

(select id_zesp numer, sum(placa_pod) kw

47

(select id_zesp numer, sum(placa_pod) kw

from pracownicy group by id_zesp)

t1,

(select max(kw_zesp) kw_max

from

(select sum(placa_pod) kw_zesp

from pracownicy group by id_zesp)

)

t2

where t1.kw=t2.kw_max

Bazy danych

Zapytanie 19

Wyświetl dla każdego roku liczbę zatrudnionych w

48

Wyświetl dla każdego roku liczbę zatrudnionych w
nim pracowników. Wynik uporządkuj zgodnie z
liczbą zatrudnionych.

Bazy danych

Zapytanie 19

select count(*) liczba, zatrudniony rok

49

select count(*) liczba, zatrudniony rok

from pracownicy
group by zatrudniony

Dlaczego złe ???

Bazy danych

Zapytanie 19

select lata, count (*) ilu_prac

50

select lata, count (*) ilu_prac

from (

select to_char(zatrudniony,'yyyy') lata
from pracownicy

)

group by lata
order by ilu_prac

Bazy danych

Zapytanie 20

Zmodyfikuj powyższe zapytanie w ten sposób, aby

51

Zmodyfikuj powyższe zapytanie w ten sposób, aby
wyświetlać tylko rok, w którym przyjęto najwięcej
pracowników.

Bazy danych

Zapytanie 20

select lata from

( select lata, count (*) ilu_prac from

(select to_char(zatrudniony,'yyyy') lata from pracownicy)

52

(select to_char(zatrudniony,'yyyy') lata from pracownicy)

group by lata

)

where ilu_prac=

( select max (ilu_prac) from

(select lata, count (*) ilu_prac from

(select to_char(zatrudniony,'yyyy') lata from pracownicy )

group by lata)

)

Bazy danych

Zapytanie 21

Wyświetl nazwiska pracowników, którzy zarabiają

53

Wyświetl nazwiska pracowników, którzy zarabiają
mniej niż średnia płaca dla ich etatu.

Bazy danych

Zapytanie 21

select nazwisko from pracownicy p,

54

select nazwisko from pracownicy p,

(select etat, round(avg(placa_pod),2) srednia

from pracownicy group by etat)

t

where p.etat=t.etat and p.placa_pod<srednia
order by nazwisko

Bazy danych

Zapytanie 21

Wyświetl

nazwiska

profesorów

i

liczbę

ich

55

Wyświetl

nazwiska

profesorów

i

liczbę

ich

podwładnych.

Wyświetl

tylko

profesorów

zatrudnionych na Piotrowie.

Bazy danych

Zapytanie 21

select nazwisko, liczba_prac from

(select id_szefa,count(*) liczba_prac from

56

(select id_szefa,count(*) liczba_prac from

pracownicy where id_szefa in

( select id_prac from pracownicy where

etat=‘PROFESOR‘ )

group by id_szefa)

t1,

pracownicy t2
where t1.id_szefa=t2.id_prac

Co jest źle ???

Bazy danych

Zapytanie 21

select nazwisko, liczba_prac from

(select id_szefa,count(*) liczba_prac from

57

(select id_szefa,count(*) liczba_prac from

pracownicy where id_szefa in

( select id_prac from pracownicy where

etat=‘PROFESOR‘ )

group by id_szefa)

t1,

pracownicy t2
where t1.id_szefa(+)=t2.id_prac

Jeszcze gorzej

Bazy danych

Zapytanie 21

select nazwisko, liczba_prac from

(select id_szefa,count(*) liczba_prac from

58

pracownicy where id_szefa in

( select id_prac from pracownicy where

etat=‘PROFESOR‘ )

group by id_szefa)

t1,

pracownicy t2
where t1.id_szefa(+)=t2.id_prac AND
t2.etat='PROFESOR'

Bazy danych

Zapytanie 22

Dla każdego profesora wyświetl jego nazwisko,

59

Dla każdego profesora wyświetl jego nazwisko,
średnią płacą w jego zespole i największą płacę w
zespole.

Bazy danych

Zapytanie 22

select nazwisko, plac_max, plac_sre

from pracownicy p,

60

(select id_szefa szef , round(max(placa_pod),2) plac_max,

round(avg(placa_pod),2) plac_sre

from pracownicy where id_szefa in

(select id_prac from pracownicy where etat='profesor' )

group by id_szefa)

t

where p.id_prac=t.szef

A gdzie jeden profesor ????

Bazy danych

Zapytanie 22

select nazwisko, plac_max, plac_sre

from pracownicy p,

61

(select id_szefa szef , round(max(placa_pod),2) plac_max,

round(avg(placa_pod),2) plac_sre

from pracownicy where id_szefa in

(select id_prac from pracownicy where etat='profesor' )

group by id_szefa)

t

where p.id_prac=t.szef(+) AND p.etat='PROFESOR'

Bazy danych

Zapytanie 23

Wyświetl nazwiska tych profesorów, którzy wśród

62

Wyświetl nazwiska tych profesorów, którzy wśród
swoich podwładnych nie mają żadnych stażystów.

Bazy danych

Zapytanie 23

select nazwisko from pracownicy p,

(select distinct id_szefa from pracownicy

63

(select distinct id_szefa from pracownicy

where id_szefa not in

( select id_szefa from pracownicy

where etat='stazysta' )

) t

where p.id_prac =t.id_szefa and etat='profesor‘

I znów brakuje jednego profesora

Bazy danych

Zapytanie 23

select nazwisko from pracownicy p ,

(select distinct t.szef from pracownicy p,

64

(select distinct t.szef from pracownicy p,

(select id_prac szef from pracownicy where

etat='profesor')

t

where p.id_szefa(+)=t.szef and t.szef not in

(

select id_szefa from pracownicy where

etat=‘STAZYSTA'

)

) t1 where p.id_prac=t1.szef

Bazy danych

HAVING

65

HAVING pozwala określić, które ze zgrupowanych rekordów mają być
wyświetlone.

Po zgrupowaniu rekordów klauzulą GROUP BY

pokazywane są te

rekordy, które spełniają kryteria klauzuli HAVING.

Bazy danych

Składnia instrukcji SQL

SELECT lista_pól

FROM wyrażenie_tabelowe

66

WHERE kryteria
GROUP BY pola_grupowania
HAVING kryteria_grupowania

Bazy danych

Składnia instrukcji SQL

Kryteria_grupowania
Określają, które ze zgrupowanych rekordów mają zostać pokazane
Lista_pól

67

Lista_pól
Nazwy pól z danymi, które mają być pobrane

Pola_grupowania
Nazwy pól użytych do zgrupowania rekordów. Kolejność tych nazw
określa ich znaczenie.

Wyrażenie_tabelowe
Nazwa tabeli lub tabel zawierających pobierane dane

Bazy danych

Where - Having

Klauzula HAVING działa podobnie do WHERE i jest
używana do wybrania tylko grup spełniających warunek
przy HAVING.

68

przy HAVING.

Właściwie WHERE odrzuca niepotrzebne wiersze przed
grupowaniem i agregacją, podczas gdy HAVING robi to
po grupowaniu.

Dlatego WHERE nie może odnosić się do rezultatu funkcji
agregujących.

Z drugiej strony nie ma sensu zapis warunku HAVING,
który

nie

dotyczy

funkcji

agregujących.

W

takim

wypadku lepiej zastosować WHERE.

Bazy danych

69

PRZYKŁADY

Bazy danych

Zapytanie 24

Podać numery pracowników którzy mają więcej niż

70

Podać numery pracowników którzy mają więcej niż
jednego podwładnego

Bazy danych

Zapytanie 24

select id_szefa, count(*)

71

select id_szefa, count(*)

from pracownicy
group by id_szefa
having count(*) >1

Bazy danych

Zapytanie 25

Numery stanowisk dla których średnia płaca jest

72

Numery stanowisk dla których średnia płaca jest
większa od 510

Bazy danych

Zapytanie 25

select id_zesp, round(avg(placa_pod),2) kwot

73

select id_zesp, round(avg(placa_pod),2) kwot

from pracownicy
group by id_zesp
having round(avg(placa_pod),2) >510

Bazy danych

Zapytanie 26

Nazwy etatów na których pracuje więcej niż 2

74

Nazwy etatów na których pracuje więcej niż 2
pracowników

Bazy danych

Zapytanie 26

select etat, count(*)

75

select etat, count(*)

from pracownicy
group by etat
having count(*)> 2

Bazy danych

PERSPEKTYWY

76

PERSPEKTYWY

Perspektywa jest „wirtualną tabelą”, czyli relacją, która jest definiowana
w oparciu o zawartość innych tabel (relacji) i perspektyw.

Bazy danych

Perspektywy

Tworzenie

CREATE VIEW nazwa [lista_kolumn]

77

CREATE VIEW nazwa [lista_kolumn]

AS
(

instrukcja [
WITH [CASCADED | LOCAL] CHECK OPTION

)

Bazy danych

Perspektywy

Tworzenie

WITH CHECK OPTION
stosowane

tylko

w

modyfikowalnych

widokach,

powoduje

78

stosowane

tylko

w

modyfikowalnych

widokach,

powoduje

sprawdzanie czy instrukcje

UPDATE

lub

INSERT

nie wstawiają do

zasadniczej tabeli wierszy, które powinny zostać włączone do
widoku

WITH CASCADED CHECK OPTION
Powoduje że sprawdzane są wszystkie kryteria według których
pobierane są wiersze do widoku (może istnieć kilka warstw
widoków między tworzonym widokiem a tabelą)

WITH LOCAL CHECK OPTION
Powoduje że sprawdzane są tylko lokalne kryteria danego
widoku

Bazy danych

Perspektywy

Przykład

create view szef

79

create view szef

(id_szef, nazwisko_szefa, id_prac, nazwisko_prac)

as

select p.id_prac, p.nazwisko, t.id_prac, t.nazwisko

from pracownicy p, pracownicy t
where p.id_prac(+)=t.id_szefa

Bazy danych

Perspektywy

Przykład

select * from szef

80

select * from szef