Kolokwium Bazy danych 1 – Jackowski

GRUPA A

Zasady

1. Jako odpowiedzi podajemy polecenia SQL realizujące zadanie zawarte w pytaniu

2. Polecenia SQL należy sformatować zgodnie z załączonym przykładem

SELECT

e

.

employee_id

,

id_pracownika

FROM

employees e

JOIN

--tabela ON

--warunki złączenia

WHERE

--warunek 1 AND
--warunek 2

3. Poprawna odpowiedź będzie oceniana punktowo

a) 2 punkty – odpowiedź poprawna, sformatowana zgodnie ze wzorcem

b) 1 punkt – odpowiedź poprawna, sformatowana niezgodnie ze wzorcem

c) 0 – brak odpowiedzi

d) -1 – odpowiedź z błędem

Pytania

1. Wyświetl dane wszystkich pracowników (imię, nazwisko, wynagrodzenie) oraz propozycję premii w

wysokości 15% wynagrodzenia zasadniczego. Posortuj listę w kolejności malejącej wg wartości

zaproponowanej premii.

SELECT

first_name AS imie, last_name AS nazwisko, salary AS wynagrodzenie, (salary*0.15) AS propozycja_premii

ORDER BY

(salary*0.15) desc

2. Wyświetl listę pracowników (imię, nazwisko), którzy nie mają kierownika (manager_id).

SELECT

first_name, last_name

FROM

employees

WHERE

manager_id IS NULL

3. Wyświetl listę pracowników (imię, nazwisko, wynagrodzenie zasadnicze, współczynnik premii

COMMISION_PCT) oraz dodatkowo wartość wynagrodzenie + wynagrodzenie *premia. Uwaga, w

przypadku braku premii ostatnia kolumna powinna zawierać wartość samego wynagrodzenia.

SELECT

first_name, last_name, salary, commision_pct, NVL(salary + salary*commision_pct, salary)

FROM

employees

4. Wyświetl listę pracowników (imię, nazwisko) oraz informację czy pracownik posiada kierownika (możliwe

dwie wartości TAK lub NIE)

SELECT

first_name, last_name, DECODE(manager_id, NULL, 'NIE', 'TAK')

FROM

employees

5. Wyświetl id managera oraz średnie zarobki jego pracowników

SELECT

manager_id, AVG(salary)

FROM

employees

WHERE

manager_id IS NOT NULL

GROUP BY

manager_id

6. Wyświetl id działu oraz informację o średnim wynagrodzeniu (zaokrąglić do dwóch miejsc po przecinku) na

poszczególnych stanowiskach w tych działach. Posortować wg id departamentu oraz kodu stanowiska pracy

SELECT

department_id, job_id, ROUND(AVG(salary), 2)

FROM

employees

GROUP BY

department_id, job_id

ORDER BY

department_id, job_id

7. Wyświetl listę pracowników (imiona, nazwiska) wraz z krajem, w których znajduje się departament, w którym

są zatrudnieni

SELECT

e.first_name, e.last_name, c.country_name

FROM

employees e, departments d, locations l, countries c

WHERE

e.department_id = d.department_id AND

d.location_id = l.location_id AND

l.country_id = c.country_id

8. Wyświetl listę departamentów (pełne nazwy) wraz z ilością pracowników w nich zatrudnionych

SELECT

d.department_name, count(e.employee_id)

FROM

employees e, departments d

WHERE

e.department_id = d.department_id

GROUP BY

d.department_name

9. Wyświetl imiona, nazwiska oraz wynagrodzenia pracowników zarabiających mniej niż 'Davies'

SELECT

e.first_name, e.last_name, e.salary

FROM

employees e

WHERE

e.salary < (SELECT e2.salary FROM employees e2 WHERE e2.last_name LIKE 'Davies')

10. Wyświetl imiona, nazwiska oraz nazwy departamentów pracy pracowników na takim samym stanowisku co

pracownik o numerze 141

SELECT

e.first_name, e.last_name, d.department_name, e.job_id

FROM

employees e, departments d

WHERE

e.department_id = d.department_id AND

e.job_id = (SELECT e2.job_id FROM employees e2 WHERE e2.employee_id = 141)

11. Wyświetl listę pracowników (imię, nazwisko, nazwę regionu) pracowników zatrudnionych w dowolnym

departamencie, który znajduje się w lokalizacji o id = 1700

SELECT

e.first_name, e.last_name, r.region_name

FROM

employees e, departments d, locations l, countries c, regions r

WHERE

e.department_id = d.department_id AND

d.location_id = l.location_id AND

l.country_id = c.country_id AND

c.region_id = r.region_id AND

d.location_id = 1700

12. Wyświetl nazwiska pracowników oraz wynagrodzenia pracowników, których managerem jest 'King'

SELECT

e.last_name, e.salary

FROM

employees e

WHERE

e.manager_id IN (SELECT e2.employee_id FROM employees e2 WHERE e2.last_name LIKE 'King')

GRUPA B

Zasady

1. Jako odpowiedzi podajemy polecenia SQL realizujące zadanie zawarte w pytaniu

2. Polecenia SQL należy sformatować zgodnie z załączonym przykładem

SELECT

e

.

employee_id

,

id_pracownika

FROM

employees e

JOIN

--tabela ON
--warunki złączenia

WHERE

--warunek 1 AND

--warunek 2

3. Poprawna odpowiedź będzie oceniana punktowo

a) 2 punkty – odpowiedź poprawna, sformatowana zgodnie ze wzorcem

b) 1 punkt – odpowiedź poprawna, sformatowana niezgodnie ze wzorcem

c) 0 – brak odpowiedzi

d) -1 – odpowiedź z błędem

Pytania

1. Wyświetl listę pracowników (imię, nazwisko, wynagrodzenie) zatrudnionych w 1987 roku oraz zarabiających

poniżej 10000 oraz dodatkowo wszystkich zatrudnionych po roku 1993

SELECT

firs_name, last_name, salary

FROM

employees

WHERE

(hire_date BETWEEN '87/01/01' AND '87/12/31' AND salary < 10000) OR TO_CHAR(hire_date, 'YYYY')

> '1993'

2. Wyświetl pracowników (imię, nazwisko) oraz informacje o tym czy wynagrodzenie pracownika przekracza

6000 (możliwe dwie wartości TAK lub NIE)

SELECT

first_name, last_name, DECODE(salary > 6000, 'TAK', 'NIE')

FROM

employees

3. Wyświetl id działu oraz informacje o średnim wynagrodzeniu (zaokrąglić do dwóch miejsc po przecinku) na

poszczególnych stanowiskach w tych działach. Posortować wg id departamentu oraz kodu stanowiska pracy

SELECT

department_id, job_id, ROUND(AVG(salary), 2)

FROM

employees

GROUP BY

department_id, job_id

ORDER BY

department_id, job_id

4. Wyświetl minimalne i maksymalne wynagrodzenie (zaokrąglić do dwóch miejsc po przecinku) jakie jest

uzyskiwane na poszczególnych stanowiskach pracy w różnych działach. Posortować wg stanowiska pracy, id

departamentów

SELECT

ROUND(MIN(salary)), ROUND(MAX(salary))

FROM

employees

WHERE

department_id IS NOT NULL

GROUP BY

department_id, job_id

ORDER BY

department_id, job_id

5. Wyświetl nazwiska pracowników zatrudnionych później niż Aleksander Humold

SELECT

e.last_name

FROM

employees e

WHERE

hire_date > (SELECT e2.hire_date FROM employees e2 WHERE e2.last_name LIKE 'Humold' AND

first_name LIKE 'Aleksander')

6. Wyświetl nazwiska pracowników zatrudnionych na tym samym stanowisku co David Austin

SELECT

e.last_name

FROM

employees e

WHERE

job_id = (SELECT e2.job_id FROM employees e2 WHERE e2.first_name LIKE 'David' AND e2.last_name

LIKE 'Austin')

7. Wyświetl listę pracowników (nazwiska) wraz z nazwiskami wszystkich pracowników pracujących na tym

samym stanowisku

SELECT

e.last_name, e2.last_name

FROM

employees e, employees e2

WHERE

e.job_id = e2.job_id

8. Wyświetl listę pracowników (imię, nazwisko, data zatrudnienia, miasto oraz pełna nazwa kraju) pracowników

pracujących w tym samym dziale co dowolny z pracowników, który w nazwisku lub imieniu posiada literę 'k'

SELECT

e.first_name, e.last_name, e.hire_date, l.city, c.country_name

FROM

employees e, departments d, locations l, countries c

WHERE

e.department_id = d.department_id AND

d.location_id = l.location_id AND

l.country_id = c.country_id AND

e.department_id = (SELECT e2.department_id FROM employees e2 WHERE e2.first_name LIKE '%k%'

OR e2.last_name LIKE '%k%')

9. Wyświetl imiona, nazwiska oraz wynagrodzenia pracowników zarabiających mniej niż Davies

SELECT

first_name, last_name, salary

FROM

employees

WHERE

salary < (SELECT salary FROM employees WHERE last_name LIKE 'Davies')

10. Wyświetl imiona i nazwiska pracowników zarabiających najmniej w kraju, w którym są zatrudnieni

SELECT

first_name, last_name, salary

FROM

employees

WHERE

salary IN (SELECT MIN(e.salary) FROM employees e, departments d, locations l, countries c WHERE

e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id

GROUP BY country_id)

11. Nadaj 15% premię wszystkich pracownikom nie posiadającym jeszcze premii (COMMISION_PCT) i którzy

pracują w Kanadzie

UPDATE

employees

SET

commision_pct = 0,15

WHERE

last_name IN(SELECT e.last_name FROM employees e, departments d, locations l, countries c WHERE

e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id AND

e.comission_pct IS NULL AND c.country_name LIKE 'Canada')

12. Zrównaj wynagrodzenia wszystkich pracowników zatrudnionych w 'Seattle' na stanowisku 'IT_PROG' tak, by

wszyscy zarabiali najwyższe wynagrodzenie ze swojej grupy

UPDATE

employees

SET

salary = (SELECT MAX(e.salary) FROM employees e, departments d, locations l WHERE e.department_id

= d.department_id AND d.location_id = l.location_id AND e.job_id LIKE 'IT_PROG' AND c.city LIKE

'Seattle')

GRUPA C

Zasady

1. Jako odpowiedzi podajemy polecenia SQL realizujące zadanie zawarte w pytaniu

2. Polecenia SQL należy sformatować zgodnie z załączonym przykładem

SELECT

e

.

employee_id

,

id_pracownika

FROM

employees e

JOIN

--tabela ON

--warunki złączenia

WHERE

--warunek 1 AND
--warunek 2

3. Poprawna odpowiedź będzie oceniana punktowo

a) 2 punkty – odpowiedź poprawna, sformatowana zgodnie ze wzorcem

b) 1 punkt – odpowiedź poprawna, sformatowana niezgodnie ze wzorcem

c) 0 – brak odpowiedzi

d) -1 – odpowiedź z błędem

Pytania

1. Wyświetl listę pracowników (imię, nazwisko, id departamentu), którzy mają w nazwisku literę a lub k

(wielkość oraz położenie liter nie mają znaczenia)

SELECT

first_name, last_name, department_id

FROM

employees

WHERE

last_name LIKE '%a%' OR

last_name LIKE '%k%'

2. Wyświetl listę pracowników (imiona i nazwiska) oraz wynagrodzenie wyświetlane zawsze za pomocą 10

znaków. Brakujące cyfry powinny być zastąpione znakiem *. Przykład ******2400

SELECT

first_name, last_name, LPAD(salary, 10, '*')

FROM

employees

3. Wyświetl minimalne wynagrodzenie pracowników w firmie zaokrąglone w dół, do jednego złotego.

SELECT

FLOOR(MIN(salary))

FROM

employees

4. Wyświetl maksymalne wynagrodzenie pracowników w firmie zaokrąglone w górę, do jednego złotego.

SELECT

CEIL(MAX(salary))

FROM

employees

5. Wyświetl listę miast (pełne nazwy), w których mieszczą się oddziały firmy, wraz z ilością pracowników w nich

zatrudnionych.

SELECT

c.city, d.department_id, count(e.employee_id)

FROM

employees e, department d, locations l, countries c

WHERE

e.department_id = d.department_id AND

d.location_id = l.location_id AND

l.country_id = c.country_id

GROUP BY

c.city

6. Wyświetl średnie zarobki w departamentach (pełne nazwy) oraz na danych stanowiskach pracy (pełna nazwa).

Zarobki zaokrąglić w dół do jednego złotego. Wyniki posortować zgodnie z wysokością średnich

wynagrodzeń.

SELECT

d.department_name, e.job_id, AVG(e.salary)

FROM

employees e, departments d

WHERE

e.department_id = d.department_id

GROUP BY

e.department_id, e.job_id

ORDER BY

AVG(e.salary)

7. Wyświetl listę pracowników (nazwiska) wraz z nazwiskami wszystkich pracowników pracujących w tym

samym dziale.

SELECT

e.last_name, e2.last_name

FROM

employees e, employees e2

WHERE

e.department_id = e2.department_id

8. Wyświetl listę pracowników (imię, nazwisko, data zatrudnienia) pracowników pracujących w tym samym

dziale co pracownik o nazwisku Davies.

SELECT

e.first_name, e.last_name, e.hire_date

FROM

employees e

WHERE

e.department_id = (SELECT e2.department_id FROM employees e2 WHERE e2.last_name LIKE 'Davies')

9. Wyświetl nazwiska pracowników oraz wynagrodzenia pracowników, których managerem jest „King”

SELECT

e.last_name, e.salary

FROM

employees e

WHERE

e.manager_id IN (SELECT e2.employee_id FROM employees e2 WHERE e2.last_name LIKE 'King')

10. Wyświetl listę pracowników (imiona, nazwiska, nazwa departamentu, wynagrodzenie wraz z premią), których

wynagrodzenia wraz z premią przekraczają średnie wynagrodzenie wraz z premią w dziale, w którym

pracownik jest zatrudniony.

SELECT

e.first_name, e.last_name, d.department_name, (e.salary + (e.salary*e.comission_pct))

FROM

employees e, departments d

WHERE

e.department_id = d.department_id AND

(e.salary + (e.salary*e.comission_pct)) > (SELECT AVG(e2.salary + (e2.salary*e2.commision_pct)) FROM

employees e2 WHERE e.department_id = e2.department_id)

11. Zwiększ o 10% wynagrodzenia pracowników pracujących w mieście 'Southlake'

UPDATE

employees

SET

salary = 1.1*salary

WHERE

last_name IN (SELECT e.last_name FROM employees e, departments d, locations l WHERE

e.department_id = d.department_id AND d.location_id = l.location_id AND c.city LIKE 'Southlake')

12. Zrównaj wynagrodzenia wszystkich pracowników zatrudnionych w 'Seattle' na stanowisku 'IT_PROG' tak, aby

wszyscy zarabiali najwyższe wynagrodzenie ze swojej grupy.

UPDATE

employees

SET

salary = (SELECT MAX(e.salary) FROM employees e, departments d, locations l WHERE e.department_id

= d.department_id AND d.location_id = l.location_id AND c.city LIKE 'Seattle' AND e.job_id LIKE

'IT_PROG')

Rozwiązania list:

Podstawy

1. Wyświetl listę pracowników (imię, nazwisko, datę zatrudnienia) sortując zgodnie z nazwiskiem i imieniem

SELECT

first_name

AS

imie

,

last_name

AS

nazwisko

,

hire_date

AS

zatrudniony

FROM

employees

ORDER

BY

last_name

ASC

,

first_name

ASC

2. Wyświetl listę pracowników (id, imię, nazwisko, wynagrodzenie) wraz z wynagrodzeniem powiększonym o

15% premię.

SELECT

employee_id

AS

id

,

first_name

AS

imie

,

last_name

AS

nazwisko

,

salary

*

1

.

15

AS

wynagrodzenie_z_premia

FROM

employees

3. Wyświetl imiona i nazwiska pracowników z pierwszą dużą literą oraz wszystkimi pozostałymi małymi (np. Jan

Nowak)

SELECT

initcap

(

first_name

)

AS

imie

,

initcap

(

last_name

)

AS

nazwisko

FROM

employees

4. Wyświetl listę nazwisk pracowników z kolumną zawierającą ilość liter w nazwisku

SELECT

last_name

AS

nazwisko

,

length

(

last_name

)

AS

dlugosc_nazwiska

FROM

employees

5. Wyświetl imię, nazwisko oraz datę zatrudnienia pracownika oraz nazwę dnia tygodnia, w którym został

zatrudniony

SELECT

first_name

AS

imie

,

last_name

AS

nazwisko

,

hire_date

AS

zatrudniony

,

to_char

(

hire_date

,

'DAY'

)

AS

w_dniu

FROM

employees

6. Wyświetl nazwiska, imiona oraz pensję pracowników zarabiających powyżej 12000

SELECT

last_name

AS

nazwisko

,

first_name

AS

imie

,

salary

AS

wynagrodzenie

FROM

employees

WHERE

salary

>

12000

7. Podaj numer departamentu, w którym pracują pracownicy, których nazwiska zaczynają się od litery S

SELECT

distinct

department_id

AS

id_departamentu

FROM

employees

WHERE

last_name

LIKE

'S%'

8. Wyświetl listę pracowników zatrudnionych w trzecim kwartale 1998 roku lub w roku 1993

SELECT

first_name

AS

imie

,

last_name

AS

nazwisko

,

to_czar

(

hire_date

,

'RR.MM.DD'

)

as

ZATRUDNIONY

FROM

employees

WHERE

hire_date

BETWEEN

'93/00/01'

AND

'98/12/31'

OR

hire_date

BETWEEN

'93/01/01'

AND

'93/12/31'

9. Wyświetl listę pracowników tak, by wszystkie informacje (imię, nazwisko, data zatrudnienia oraz id

stanowiska) były umieszczone w jednej kolumnie (poszczególne elementy powinny zostać oddzielone

przecinkiem)

SELECT

(

first_name

||

','

||

last_name

||

','

||

hire_date

||

','

||

employee_id

)

AS

lista

FROM

employees

10. Wyświetl listę pracowników, którzy zostali zatrudnieni w 1987 roku i zarabiali powyżej 5000 lub takich,

którzy zostali zatrudnieni po roku 1993 i zarabiali powyżej 10000

SELECT

last_name

AS

nazwisko

,

hire_date

AS

zatrudniony

,

salary

AS

wynagrodzenie

FROM

employees

WHERE

hire_date

BETWEEN

'87/01/01'

AND

'87/12/31'

AND

salary

>

5000

)

OR

(

hire_date

>

'93/12/31'

AND

salary

>

10000

)

11. Wyświetl pracowników, którzy nie mają kierownika

SELECT

last_name AS nazwisko, manager_id AS manager

FROM

employees

WHERE

manager_id IS NULL

12. Wyświetl pracowników, którzy mają w nazwisku literę a lub e

SELECT

first_name AS imie, last_name AS nazwisko

FROM

employees

WHERE (lower(first_name) LIKE '%a%') OR (lower(last_name) LIKE '%e%')

13. Wyświetl pracowników, którzy otrzymują premie (COMMISSION_ PCT ), wraz z pensją, wysokością premii

(w PLN) oraz wynagrodzeniem całkowitym (pensja + premia)

SELECT

first_name AS imie, last_name AS nazwisko, salary AS wynagrodzenie, comission_pct*salary AS premia,

salary + (comission_pct*salary) AS w _sumie

FROM

employees

WHERE

commision_pct IS NOT NULL

14. Zmień zadanie powyższe tak, by przedstawić listę wszystkich pracowników. Dla tych, którzy nie otrzymują

premii, wartość w ostatnim polu powinna być równa pensji zasadniczej

SELECT

first_name AS imie, last_name AS nazwisko, salary AS wynagrodzenie, nvl(comission_pct*salary, 0) AS

premia, nvl(salary + (comission_pct*salary), salary) AS w _sumie

FROM

employees

WHERE

commision_pct IS NOT NULL

Funkcje agregujące

1. Wyświetl ilość pracowników firmy zatrudnionych po roku 1997.

SELECT

count(employee_id), hire_date

FROM

employees

WHERE

hire_date > '97/12/31'

2. Wyświetl ilość pracowników zatrudnionych w departamencie 50

SELECT

count(employee_id), department_id

FROM

employees

WHERE

department_id = 50

3. Wyświetl maksymalne i minimalne wynagrodzenie wszystkich pracowników w firmie.

SELECT

MIN(salary), MAX(salary)

FROM

employees

4. Podaj liczbę pracowników nie posiadających managera

SELECT

count(employee_id)

FROM

employees

WHERE

manager_id IS NULL

5. Wyświetl ilość pracowników oraz średnie wynagrodzenie w każdym z działów (średnie wynagrodzenie

powinno zostać podane z dokładnością do 2 miejsc po przecinku)

SELECT

department_id, count(employee_id), ROUND(AVG(salary),2)

FROM

employees

GROUP BY

department_id

6. Pokaż liczbę departamentów zatrudniających powyżej 5 osób

SELECT

count(department_id)

FROM

employees

HAVING

count(employee_id) > 5

7. Wyświetl następujące informacje odnośnie zarobków uzyskiwanych na poszczególnych stanowiskach

a) Minimalne wynagrodzenie zaokrąglone w dół do jednego dolara

SELECT

FLOOR(MIN(salary))

FROM

employees

GROUP BY

job_id

b) Średnie wynagrodzenie zaokrąglone do jednego dolara

SELECT

ROUND(AVG(salary))

FROM

employees

GROUP BY

job_id

c) Maksymalne wynagrodzenie zaokrąglone w górę do jednego dolara

SELECT

CEIL(MAX(salary))

FROM

employees

GROUP BY

job_id

8. Wyświetl ilość osób pełniących funkcję managera

SELECT

count(manager_id)

FROM

employees

WHERE

manager_id IS NOT NULL

GROUP BY

manager_id

9. Wyświetl różnicę w minimalnych i maksymalnych zarobkach w firmie

SELECT

MAX(salary) – MIN(salary) AS roznica

FROM

employees

10. Wyświetl id managera oraz minimalne zarobki osiągane przez któregokolwiek z jego pracowników. W

zestawieniu należy usunąć osoby nie posiadające kierownika.

SELECT

manager_id, MIN(salary)

FROM

employees

WHERE

manager_id IS NOT NULL

GROUP BY

manager_id

11. Wyświetl średnie zarobki pracowników na poszczególnych stanowiskach. Z zestawienia usuń osoby

zarabiające poniżej 6000.

SELECT

AVG(salary), job_id

FROM

employees

WHERE

salary > 6000

GROUP BY

job_id

12. Wyświetl minimalne zarobki pracowników w poszczególnych działach. Z zestawienia usuń działy, w których

wynagrodzenie maksymalne przekracza 10000.

SELECT

MIN(salary), department_id

FROM

employees

HAVING

MAX(salary) < 10000

GROUP BY

department_id

13. Wyświetl maksymalne średnie wynagrodzenie z wszystkich działów.

SELECT

MAX(AVG(salary))

FROM

employees

GROUP BY

department_id

14. Wyświetl średnie zarobki osiągane przez pracowników na poszczególnych stanowiskach w poszczególnych

latach. Posortuj latami i stanowiskami

SELECT

AVG(salary), TO_CHAR(hire_date, 'YYYY'), job_id

FROM

employees

GROUP BY

hire_date, job_id

ORDER BY

hire_date, job_id

15. Wyświetl listę pracowników zatrudnionych w poszczególnych latach (1995, 1996, 1997, 1998) Uwaga: ilość

zatrudnionych w poszczególnych latach powinna zostać podana w kolumnach a nie wierszach.

SELECT

SUM(SUM(DECODE(TO_CHAR(hire_date,'YYYY'),1995, 1,0))) AS zatrudniony_w_1995,

SUM(SUM(DECODE(TO_CHAR(hire_date,'YYYY'),1996, 1,0))) AS zatrudniony_w_1996,

SUM(SUM(DECODE(TO_CHAR(hire_date,'YYYY'),1997, 1,0))) AS zatrudniony_w_1997,

SUM(SUM(DECODE(TO_CHAR(hire_date,'YYYY'),1998, 1,0))) AS zatrudniony_w_1998,

FROM

employees

GROUP BY

TO_CHAR(hire_date,'YYYY')

Złączenia

1. Wyświetl listę przedstawicieli handlowych (Sales Representative) wraz z nazwami departamentów, w których

pracują.

SELECT

first_name, last_name, department_name

FROM

employees e, departments d

WHERE

e.department_id = d.department_id AND e.job_id = 'SA_REP'

2. Wyświetl ilość departamentów (pełna nazwa) w każdym z krajów

SELECT

country_name, count(department_name)

FROM

departments d, locations l, countries c

WHERE d.location_id = l.location_id AND l.country_id = c.country_id

GROUP BY

country_name

3. Wyświetl informacje o historii zatrudnienia zawierającą: imię i nazwisko pracownika, data zatrudnienia i

zwolnienia na danym stanowisku, stanowiska (pełna nazwa).

SELECT

e.first_name, e.last_name, j.start_date, j.end_date, jo.job_title

FROM

employees e, job_history j, jobs jo

WHERE e.employee_id = j.employee_id AND e.job_id = jo.job_id

4. Wyświetl pracowników (imię, nazwisko) oraz miasto oraz kraj (pełna nazwa) wszystkich pracowników

zatrudnionych w kraju, którego pełna nazwa zaczyna się na literę U

SELECT

e.first_name, e.last_name, c.country_name, l.city

FROM

employees e, departments d, locations l, countries c

WHERE

e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id

AND c.country_name LIKE 'U%'

5. Wyświetl tabelę z następującymi informacjami: imię i nazwisko pracownika, stanowisko pracy (pełna nazwa),

departament (pełna nazwa), miasto, kraj (pełna nazwa), region (pełna nazwa).

SELECT

e.first_name, e.last_name, jo.job_title, d.department_name, l.city, c.country_name, r.region_name

FROM

employees e, jobs jo, departments d, locations l, countries c, regions r

WHERE e.job_id = jo.job_id AND e.department_id = d.department_id AND d.location_id = l.location_id

AND l.country_id = c.country_id AND c.region_id = r.region_id

6. Wyświetl listę departamentów (nazwa) wraz z ilością pracowników w nich zatrudnionych

SELECT

d.department_name, count(e.employee_id)

FROM

employees e, departments d

WHERE

e.department_id = d.department_id

GROUP BY

department_name

7. Wyświetl listę pracowników (imiona i nazwiska) wraz z imieniem i nazwiskiem ich managera. Jeżeli

pracownik nie ma managera w miejscu jego imienia i nazwiska powinien się wyświetlić napis „brak"

SELECT

e.first_name, e.last_name, decode(e2.last_name, null, 'brak', e2.first_name || ' ' || e2.last_name)

FROM

employees e1 LEFT OUTER JOIN employees e2 ON e1.manager_id = e2.employee_id

8. Wyświetl średnie zarobki na każdym stanowisku pracy (stanowiska podane pełną nazwą)

SELECT

ROUND(AVG(e.salary)), jo.job_name

FROM

employees e, jobs jo

WHERE

e.job_id = jo.job_id

GROUP BY

job_name

9. Wyświetl najmniejsze i największe zarobki (zaokrąglone do dwóch miejsc po przecinku) w każdym kraju

(pełne nazwy)

SELECT

c.country_name, ROUND(MIN(e.salary), 2) ROUND(MAX(e.salary),2)

FROM

employees e, departments d, locations l, countries c

WHERE

e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id

GROUP BY c.country_name

10. Wyświetl następującą listę: lokalizacja (nazwa miasta), stanowisko pracy (pełna nazwa) ilość zatrudnionych

pracowników.

SELECT

l.city, jo.job_title, count(e.employee_id)

FROM

employees e, departments d, locations l, jobs jo

WHERE

e.job_id = jo.job_id AND e.department_id = d.department_id AND d.location_id = l.location_id

GROUP BY

city, job_title

11. Wyświetl imiona i nazwiska pracowników zatrudnionych później niż „Davies"

SELECT

e1.first_name, e1.last_name, e1.hire_date

FROM

employees e

WHERE

e1.hire_date > (SELECT e2.hire_date FROM employees e2 WHERE e2.last_name LIKE 'Davies')

12. Wyświetl parami wszystkich pracowników (podaj ich nazwiska) zatrudnionych w tym samym departamencie

wg. poniższego przykładu

SELECT

e1.department_id, e1.last_name, e2.last_name

FROM

employee e1, employee e2

WHERE

e1.department_id = e2.department_id AND e1.employee_id <> e2.department_id

ORDER BY e1.department_id

Podzapytania

1. Wyświetl listę pracowników (imię, nazwisko, data zatrudnienia) pracowników pracujących w tym samym

dziale co pracownik o nazwisku 'Davies'.

SELECT

first_name, last_name, hire_date

FROM

employees

WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Davies')

2. Wyświetl listę pracowników (imię, nazwisko, pełna nazwa działu) pracowników, których wynagrodzenie

(salary) przekracza średnie wynagrodzenie wszystkich pracowników w firmie.

SELECT

e.first_name, e.last_name, d.department_name, e.salary

FROM

employees e, departments d

WHERE

e.department_id = d.department_id AND salary > (SELECT AVG(salary) FROM employees)

3. Wyświetl listę pracowników (imię, nazwisko, datę zatrudnienia, miasto oraz pełną nazwę kraju) pracowników

pracujących w tym samym dziale co dowolny z pracowników, który w nazwisku lub imieniu posiada liter ,,k".

SELECT

e.first_name, e.last_name, e.hire_date, c.country_name, c.city

FROM

employees e, departments d, locations l, countries c

WHERE

e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id AND

e.department_id IN (SELECT department_id FROM employees WHERE (first_name LIKE '%k%') OR

(last_name LIKE '%k%'))

4. Wyświetl listę pracowników (imię, nazwisko, nazwę regionu) pracowników zatrudnionych w dowolnym

departamencie, który znajduje się w lokalizacji o id=1700.

SELECT

e.first_name, e.last_name, r.region_name

FROM

employees e, departments d, locations l, coutries c, regions r

WHERE

e.department_id = d.department_id AND

d.location_id = l.location_id AND

l.country_id = c.country_id AND

c.region_id = r.region_id AND

d.department_id IN (SELECT d.department_id FROM departments d, locations l WHERE d.location_id =

l.location_id AND l.location_id = 1700)

--ostatni AND może być zapisany: d.location_id = 1700

5. Wyświetl nazwiska pracowników oraz ich wynagrodzenia pracowników, których managerem jest ,,King"

SELECT

last_name, salary

FROM

employees

WHERE

manager_id IN (SELECT employee_ID FROM employees WHERE last_name LIKE 'King')

6. Wyświetl nazwy departamentów, nazwiska i imiona pracowników zatrudnionych na stanowisku "Executive"

SELECT

d.department_name, e.last_name, e.first_name, jo.job_title

FROM

employees e, departments d

WHERE

e.department_id = d.department_id AND

e.job_id = jo,job_id AND

jo.job_title like 'Executive'

7. Wyświetl imiona, nazwiska oraz wynagrodzenia pracowników zarabiających mniej niż,,Davies"

SELECT

first_name, last_name, salary

FROM

employees

WHERE

salary < (SELECT salary FROM employees WHERE last_name LIKE 'Davies')

8. Wyświetl imiona, nazwiska oraz nazwy departamentów pracy pracowników na takim samym stanowisku co

pracownik o numerze 141

SELECT

e.first_name, e.last_name, d.department_name

FROM

employees e, departments d

WHERE

e.department_id = d.department_id AND

e.job_id = (SELECT job_id FROM employees WHERE employee_id = 141)

9. Wyświetl listę pracowników (imiona, nazwiska, nazwa departamentu, wynagrodzenie wraz z premią)

pracowników, których wynagrodzenia wraz z premią przekraczaj średnie wynagrodzenie wraz z premią w

dziale, w którym pracownik jest zatrudniony.

SELECT

e.first_name, e.last_name, d.department_name, e.salary + (e.salary*e.commision_pct)

FROM

employees e, departments d

WHERE

e.department_id = d.department_id AND

(e.salary + (e.salary*e.commision_pct) ) > (SELECT AVG(e2.salary + (e2.salary*e2.commision_pct)) FROM

employees e2 WHERE e.department_id = e2.department_id)

10. Wyświetl imiona i nazwiska pracowników zarabiających najmniej w kraju, w którym jest zatrudniony.

SELECT

first_name, last_name, salary

FROM

employees

WHERE

salary IN (SELECT MIN(e.salary) FROM employees e, departments d, locations l, countries c WHERE

e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id

GROUP BY country_id)

Podsumowanie