plik

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

BAZY DANYCH I

LABORATORIUM

Kierunek
studiów:

Informatyka

Rok studiów:

III

Numer grupy:

Rok akademicki: 2012/2013

Semestr:

Temat:

Ćwiczenie 4

Lp.

Nr indeksu

Imię i nazwisko

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.
Do relacji prac kopia wpisać tych pracowników, z relacji prac, którzy pracują na stanowisku
profesorskim i zostali zatrudnieni przed rokiem 1978.

Zapytanie:

insert into prac_kopia select * from prac
where etat = 'PROFESOR' and zatrudniony < '01-JAN-1978';

Wynik zapytania:

4 rows inserted.

Zad. 2.
Podnieść pensję pracownikom najgorzej zarabiającym do średniej pensji w zakładzie pracy.

Zapytanie:

update prac set placa_pod =
(select avg(placa_pod) from prac)
where placa_pod = (select min(placa_pod) from prac);

Wynik zapytania:

1 rows updated.

Zad. 3.
Uaktualnić pensję dodatkową pracownikom zespołu 20. Nowa pensja ma być równa średniej
pensji dodatkowej pracowników, których przełożonym jest ‘*****’.

Zapytanie:

update prac set placa_dod =
(select avg(nvl(placa_dod, 0)) from prac where szef = (select id_prac from prac where nazwisko =

'&NAZWISKO_PRZELOZONEGO'))

where id_zesp = 20;

Po uruchomieniu w okienku wpisujemy np. ABACKI
Wynik zapytania:

old:update prac set placa_dod =
(select avg(nvl(placa_dod, 0)) from prac where szef = (select id_prac from prac where nazwisko =
'&NAZWISKO_PRZELOZONEGO'))
where id_zesp = 20
new:update prac set placa_dod =
(select avg(nvl(placa_dod, 0)) from prac where szef = (select id_prac from prac where nazwisko =
'ABACKI'))
where id_zesp = 20
7 rows updated.

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 2

Możemy teraz sprawdzić pracowników o id_zesp = 20:

Zapytanie:

select id_zesp, placa_dod from prac where id_zesp = 20;

Wynik zapytania:

ID_ZESP PLACA_DOD
------- ---------
20 52.5
20 52.5
20 52.5
20 52.5
20 52.5
20 52.5
20 52.5

7 rows selected

Widzimy, że placa_dod każdego z pracowników od id_zesp = 20 jest taka sama.

Zad.4.
Zwiększyć  płacę  podstawową  do  120%  średniej  płacy  podstawowej  w  zespole  pracownika
oraz  zwiększyć  płacę  dodatkową  do  wartości  równej  maksymalnej  płacy  dodatkowej  w
zespole pracownika

. Operacji dokonać tylko dla pracowników zatrudnionych po 1990 roku.

Wskazówka: użyć funkcji nvl(wyrażenie, wartość) służącej do obsługi tzw. wartości pustych.

Zapytanie:

update prac p set
placa_pod = (select avg(placa_pod)*1.2 from prac where prac.id_zesp = p.id_zesp),
placa_dod = (select max(nvl(placa_dod, 0)) from prac where prac.id_zesp = p.id_zesp)
where zatrudniony > '31-DEC-1990';

Wynik zapytania:

6 rows updated.

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 3

ZMIENNE

Zad.1.
Wyświetlić nazwiska pracowników pracujących na etacie wprowadzonym interakcyjnie. W
zapytaniu nie rozróżniać małych i dużych liter.

Zapytanie:

select nazwisko from prac where upper(etat) = upper('&ETAT');

Wynik zapytania, dla którego w okienku interakcyjnym wprowadzamy słowo “ProfeSor”:

old:select nazwisko from prac where upper(etat) = upper('&ETAT')
new:select nazwisko from prac where upper(etat) = upper('ProfeSor')
NAZWISKO
---------------
BABACKI
CABACKI
DABACKI
EBACKI

Zad.2.
Wyświetlić nazwiska pracowników zatrudnionych między dwoma interakcyjnie podanymi
datami. Wykorzystać zmienne z pojedynczym znakiem &.

Zapytanie:

select nazwisko, zatrudniony from prac
where zatrudniony between '&DATA1' and '&DATA2';

Wynik zapytania, dla którego w pierwszym okienku interakcyjnym wprowadzamy datę w
postaci 01-JAN-

75, a w drugim datę w postaci 01-JAN-80:

old:select nazwisko, zatrudniony from prac
where zatrudniony between '&DATA1' and '&DATA2'
new:select nazwisko, zatrudniony from prac
where zatrudniony between '01-JAN-75' and '01-JAN-80'
NAZWISKO ZATRUDNIONY
--------------- -----------
CABACKI 01-SEP-77
EBACKI 15-SEP-75
FABACKI 01-SEP-77

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 4

Zad. 3.
Wyświetlić nazwiska, etaty, płace podstawowe pracowników nie będących asystentami,
pracujących w zespole, którego nazwa wprowadzana jest interakcyjnie. Uporządkować
zgodnie z malejącą datą.

Zapytanie:

select nazwisko, etat, placa_pod from prac
where etat not like 'ASYSTENT'
and id_zesp = (select id_zesp from zesp where upper(nazwa) = upper('&NAZWA_ZESPOLU'))
order by zatrudniony desc;

Wynik zapytania, dla którego w okienku interakcyjnym wprowadzamy słowo
“INFORMATYKA”:

old:select nazwisko, etat, placa_pod from prac
where etat not like 'ASYSTENT'
and id_zesp = (select id_zesp from zesp where upper(nazwa) = upper('&NAZWA_ZESPOLU'))
order by zatrudniony desc
new:select nazwisko, etat, placa_pod from prac
where etat not like 'ASYSTENT'
and id_zesp = (select id_zesp from zesp where upper(nazwa) = upper('INFORMATYKA'))
order by zatrudniony desc
NAZWISKO ETAT PLACA_POD
--------------- ---------- ---------
GABACKI ADIUNKT 1590
FABACKI ADIUNKT 1645.5
EBACKI PROFESOR 2830
DABACKI PROFESOR 2960

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 5

ZMIENNE GLOBALNE

Zad. 1.
Korzyst

ając z polecenia accept zdefiniować zmienną id_zesp oraz etat. Na podstawie

powyższych zmiennych wydać zapytanie wyświetlające dane o pracownikach wybranego
zespołu i pracujących na wybranym etacie.

Zapytanie:

accept id_zesp number(2,0) prompt 'ID_ZESP';
accept etat varchar2(10) prompt 'ETAT';
select * from prac where id_zesp = &id_zesp and upper(etat) = upper('&etat');

Wynik zapytania, dla którego w pierwszym okienku interakcyjnym wprowadzamy liczbę 20, a
w drugim wprowadzamy słowo “Profesor”:

old:select * from prac where id_zesp = &id_zesp and upper(etat) = upper('&etat')
new:select * from prac where id_zesp = 20 and upper(etat) = upper('Profesor')
ID_PRAC NAZWISKO ETAT SZEF ZATRUDNIONY PLACA_POD PLACA_DOD ID_ZESP
------- --------------- ---------- ---- ----------- --------- --------- -------
130 DABACKI PROFESOR 100 01-JUL-68 2960 52.5 20
140 EBACKI PROFESOR 130 15-SEP-75 2830 52.5 20

PERSPEKTYWY

Zad. 1.
Zdefiniować perspektywę wyświetlającą wszystkich przełożonych i nazwy zespołów, w
których pracują.

Zapytanie:

create view view1 as
(select nazwisko "PRZEŁOŻONY", nazwa "NAZWA ZESPOŁU" from prac p, zesp z where id_prac in(select

distinct szef from prac) and p.id_zesp = z.id_zesp)

with check option

Wynik zapytania:

view VIEW1 created.

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 6

LICZNIKI

Zad. 1.
Wpisać krotkę do relacji prac, wykorzystując licznik do generowania unikalnych numerów
pracowników. Licznik ma być acykliczny, ma rozpocząć zliczanie od wartości 4000 i
zwiększać się o 2 po każdym odczycie nowej wartości.

Tworzenie licznika:

create sequence seq_id_prac increment by 2 start with 4000;

Wynik zapytania:

sequence SEQ_ID_PRAC created.

Zapytanie wykorzystujące stworzony przed chwilą licznik:

insert into prac
values(seq_id_prac.nextval, 'BABACKI', 'ASYSTENT', 100, '20-JUN-1990', 1000, NULL, 10);

Wynik zapytania:

1 rows inserted.

ZADANIA Z PLIKU views.txt

a) Zdefiniować perspektywę wyświetlającą wszystkich przełożonych i nazwy zespołów, w których
pracują.

Zapytanie:

create view view_a
(przelozony, nazwa_zesp)
as
select nazwisko, nazwa from prac, zesp
where
id_prac in (select szef from prac)
and

prac.id_zesp = zesp.id_zesp;

Wynik zapytania:

view VIEW_A created.

Sprawdzenie:

select * from view_a;

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 7

Wynik sprawdzenia:

PRZELOZONY NAZWA_ZESP
--------------- --------------------
ABACKI ADMINISTRACJA
BABACKI AUTOMATYKA
CABACKI BUDOWNICTWO
DABACKI INFORMATYKA
EBACKI INFORMATYKA

b) Zdefiniować perspektywę wyświetlającą listę etatów (bez duplikatów) na których zatrudnieni są
pracownicy uczelni.

Zapytanie:

create view view_b
as
select distinct etat from prac;

--lub

create view view_b
as
select etat from prac group by etat;

Wynik zapytania:

view VIEW_B created.

Sprawdzenie:

select * from view_b;

Wynik sprawdzenia:

ETAT
----------
ADIUNKT
PROFESOR
DYREKTOR
STAZYSTA
ASYSTENT
SEKRETARKA

6 rows selected

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 8

c) Zdefiniować perspektywę wyświetlającą wszystkie dane o pracownikach zespołów 30 i 40 w
kolejności malejących zarobków.

Zapytanie:

create view view_c
as
select * from prac where id_zesp in(20, 30) order by placa_pod desc;

Wynik zapytania:

view VIEW_C created.

Sprawdzenie:

select * from view_c;

Wynik sprawdzenia:

ID_PRAC NAZWISKO ETAT SZEF ZATRUDNIONY PLACA_POD PLACA_DOD ID_ZESP
------- --------------- ---------- ---- ----------- --------- --------- -------
  120 CABACKI PROFESOR 100 01-SEP-77 3070 30
  130 DABACKI PROFESOR 100 01-JUL-68 2960 20
  140 EBACKI PROFESOR 130 15-SEP-75 2830 105 20
  150 FABACKI ADIUNKT 130 01-SEP-77 1645.5      20
  160 GABACKI ADIUNKT 130 01-MAR-85 1590 20
  230 NABACKI ASYSTENT 120 01-SEP-92 1480 90 30

  220 MABACKI ASYSTENT 110 01-OCT-93 1480 20
  170 HABACKI ASYSTENT 130 01-OCT-92 1439.7 80.5 20
  190 IBACKI ASYSTENT 140 01-SEP-93 1371 20
  210 LABACKI STAZYSTA 130 15-OCT-93 1250 170.6 30

200 KABACKI STAZYSTA 140 15-JUL-94 1208 30

11 rows selected

d) Zdefiniować perspektywę wyświetlającą nazwiska, daty zatrudnienia i etaty asystentów
zatrudnionych w 1992 lub 1993 roku.

Zapytanie:

create view view_d
as
select nazwisko, zatrudniony, etat from prac
where
  etat = 'ASYSTENT'
  and
  zatrudniony between '01-JAN-1992' and '31-DEC-1993';

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 9

Wynik zapytania:

view VIEW_D created.

Sprawdzenie:

select * from view_d;

Wynik sprawdzenia:

NAZWISKO ZATRUDNIONY ETAT
--------------- ----------- ----------
HABACKI 01-OCT-92 ASYSTENT
IBACKI 01-SEP-93 ASYSTENT
MABACKI 01-OCT-93 ASYSTENT
NABACKI 01-SEP-92 ASYSTENT

e) Zdefiniować perspektywę wyświetlającą nazwiska i płace pracowników powiększone o 15% i
zaokrąglone do liczb całkowitych.

Zapytanie:

create view view_e
(nazwisko, placa_pod_pow_o_15_proc)
as
select nazwisko, round(placa_pod*1.15) from prac;

Funkcja round(liczba) zaokrągla:
•

w górę jeśli część dziesiętna liczby >= 0.5

•

w dół jeśli część dziesiętna liczby < 0.5

Wynik zapytania:

view VIEW_E created.

Sprawdzenie:

select * from view_e;

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 10

Wynik sprawdzenia:

NAZWISKO PLACA_POD_POW_O_15_PROC
--------------- -----------------------
ABACKI 3140
BABACKI 3853
CABACKI 3531
DABACKI 3404
EBACKI 3255
FABACKI 1892
GABACKI 1829
HABACKI 1656
IBACKI 1577
JOTBACKA 1622
KABACKI 1389
LABACKI 1438
MABACKI 1702
NABACKI 1702
BABACKI 1150

15 rows selected

f) Zdefiniować perspektywę wyświetlającą dla każdego szefa jego podwładnych.

Zapytanie:

create view view_f
(szef, podwladny)
as
select s.nazwisko, p.nazwisko from prac s, prac p
where s.id_prac = p.szef
order by s.nazwisko, p.nazwisko;

Wynik zapytania:

view VIEW_F created.

Sprawdzenie:

select * from view_f;

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 11

Wynik sprawdzenia:

SZEF PODWLADNY
--------------- ---------------
ABACKI   BABACKI
ABACKI BABACKI
ABACKI CABACKI
ABACKI DABACKI
ABACKI JOTBACKA
BABACKI MABACKI
CABACKI NABACKI
DABACKI EBACKI
DABACKI FABACKI
DABACKI GABACKI
DABACKI HABACKI
DABACKI LABACKI
EBACKI IBACKI
EBACKI KABACKI

14 rows selected

g) Zdefiniować perspektywę wyświetlającą wyświetlającą jaki mamy dziś dzień tygodnia.

Zapytanie:

create view view_g
("today_is")
as
select to_char(current_date, 'Day') from dual;

Wynik zapytania:

view VIEW_G created.

Sprawdzenie:

select * from view_g;

Wynik sprawdzenia:

today_is
---------
Sunday

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 12

h) Zdefiniować perspektywę wyświetlającą dla każdego pracownika - informację o tym, czy jego pensja
jest mniejsza niż, równa lub większa niż 2480 złotych.

Zapytanie:

create view view_h
as
select
nazwisko,
case
when placa_pod > 2400 then 'większa niż 2480 zł'
when placa_pod < 2400 then 'mniejsza niż 2480 zł'

else 'równa 2480 zł'

end as "PŁACA"
from prac;

Wynik zapytania:

view VIEW_H created.

Sprawdzenie:

select * from view_h;

Wynik sprawdzenia:

NAZWISKO PŁACA
--------------- --------------------
ABACKI większa niż 2480 zł
BABACKI większa niż 2480 zł
CABACKI większa niż 2480 zł
DABACKI większa niż 2480 zł
EBACKI większa niż 2480 zł
FABACKI     mniejsza niż 2480 zł
GABACKI mniejsza niż 2480 zł
HABACKI mniejsza niż 2480 zł
IBACKI mniejsza niż 2480 zł
JOTBACKA mniejsza niż 2480 zł
KABACKI mniejsza niż 2480 zł
LABACKI mniejsza niż 2480 zł
MABACKI mniejsza niż 2480 zł
NABACKI mniejsza niż 2480 zł
BABACKI mniejsza niż 2480 zł

15 rows selected

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 13

i) Zdefiniować perspektywę wyświetlającą najniższą pensję na uczelni.

Zapytanie:

create view view_i
("NAJNIŻSZA PENSJA NA UCZELNI")
as
select min(placa_pod) from prac;

Wynik zapytania:

view VIEW_I created.

Sprawdzenie:

select * from view_i;

Wynik sprawdzenia:

NAJNIŻSZA PENSJA NA UCZELNI
---------------------------
1000

j) Zdefiniować perspektywę wyświetlającą najwyższą pensję w każdym zespole.

Zapytanie:

create view view_j
("NAZWA ZESPOŁU", "NAJWYŻSZA PENSJA")
as
select nazwa, max(placa_pod) from prac p, zesp z
where p.id_zesp = z.id_zesp
group by nazwa;

Wynik zapytania:

view VIEW_J created.

Sprawdzenie:

select * from view_j;

Wynik sprawdzenia:

NAZWA ZESPOŁU NAJWYŻSZA PENSJA
-------------------- ----------------
INFORMATYKA 2960
ADMINISTRACJA 2730
BUDOWNICTWO 3070
AUTOMATYKA 3350

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 14

k) Zdefiniować perspektywę wyświetlającą informację o kwocie różnicy dzielącej najlepiej i najgorzej
zarabiających pracowników.

Zapytanie:

create view view_k
(RÓŻNICA)
as
select max(placa_pod)-min(placa_pod) from prac;

Wynik zapytania:

view VIEW_K created.

Sprawdzenie:

select * from view_k;

Wynik sprawdzenia:

RÓŻNICA
-------
2350

l) Zdefiniować perspektywę wyświetlającą średnie pensje dla wszystkich etatów.

Zapytanie:

create view view_l
(etat, "ŚREDNIA PENSJA")
as
select etat, avg(placa_pod) from prac
group by etat
order by etat;

Wynik zapytania:

view VIEW_L created.

Sprawdzenie:

select * from view_l;

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 15

Wynik sprawdzenia:

ETAT ŚREDNIA PENSJA
---------- --------------
ADIUNKT 1617.75
ASYSTENT 1354.14
DYREKTOR 2730
PROFESOR 3052.5
SEKRETARKA 1410.2
STAZYSTA 1229

6 rows selected

ł) Zdefiniować perspektywę wyświetlającą średnie pensje dla wszystkich zespołów.

Zapytanie:

create view view_ł
(zesp, "ŚREDNIA PŁACA")
as
select nazwa, avg(placa_pod) from prac p, zesp z
where z.id_zesp = p.id_zesp
group by nazwa order by nazwa;

Wynik zapytania:

view VIEW_Ł created.

Sprawdzenie:

select * from view_ł;

Wynik sprawdzenia:

ZESP ŚREDNIA PŁACA
-------------------- -------------
ADMINISTRACJA 1713.4
AUTOMATYKA 3350
BUDOWNICTWO 1752
INFORMATYKA 1902.314286

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 16

m) Zdefiniować perspektywę wyświetlającą liczbę profesorów zatrudnionych w każdym z zespołów.

Zapytanie:

create view view_m
(nazwa, "LICZBA PROFESORÓW")
as
select nazwa, count(p.id_prac)
from prac p, zesp z
where
  etat = 'PROFESOR'
  and
  z.id_zesp = p.id_zesp
group by nazwa;

Wynik zapytania:

view VIEW_M created.

Sprawdzenie:

select * from view_m;

Wynik sprawdzenia:

NAZWA LICZBA PROFESORÓW
-------------------- -----------------
INFORMATYKA 2
BUDOWNICTWO 1
AUTOMATYKA 1

n) Zdefiniować perspektywę wyświetlającą numery zespołów, które zatrudniają więcej niż 3
pracowników.

Zapytanie:

create view view_n
as
select id_zesp from prac
group by id_zesp having count(*) > 3;

Wynik zapytania:

view VIEW_N created.

Sprawdzenie:

select * from view_n;

POLITECHNIKA OPOLSKA

WYDZIAŁ ELEKTROTECHNIKI, AUTOMATYKI I INFORMATYKI

strona 17

Wynik sprawdzenia:

ID_ZESP
-------
30
20

o) Zdefiniować perspektywę wyświetlającą dla każdego szefa - pensję najgorzej zarabiającego jego
podwładnego.

Zapytanie:

create view view_o
(szef, "NAJGORZ ZARAB PODWŁ")
as
select szef, min(placa_pod) from prac
where szef is not null
group by szef
order by szef;

Wynik zapytania:

view VIEW_O created.

Sprawdzenie:

select * from view_o;

Wynik sprawdzenia:

SZEF NAJGORZ ZARAB PODWŁ
---- -------------------
100 1000
110 1480
120 1480
130 1250
140 1208