SELECT * FROM emp

(1)

I.

ZAPYTANIA SQL

1. Wybrać numery departamentów, nazwiska pracowników oraz numery pracownicze ich szefów z tabeli EMP.

SELECT ename, deptno, mgr FROM emp;

2. Wybrać wszystkie kolumny z tabeli emp;

SELECT * FROM emp;

3. Wyliczyć roczną pensje podstawową dla każdego pracownika.

SELECT ename, sal * 12 FROM emp;

4. Wyliczyć roczną pensje podstawową dla każdego pracownika, gdyby każdy dostał podwyżkę o 250.

SELECT ename, (sal + 250) * 12 FROM emp;

5. Wybrane wyrażenie SAL*12 zaetykietować nagłówkiem ROCZNA.

SELECT ename, sal*12 AS roczna FROM emp;

6. Wybrane wyrażenie SAL*12 zaetykietować nagłówkiem R PENSJA.

SELECT ename, sal*12 AS ”r pensja”

FROM emp;

Jeżeli chcemy użyć spacji w etykiecie kolumny, to używamy cudzysłowu ”, natomiast w wartościach typu tekstowego używamy apostrofu ‘.

7. Połączyć EMPNO i nazwisko, opatrzyć je nagłówkiem EMPLOYEE.

SELECT empno || ename AS employee FROM emp;

W MS SQL istnieje konieczność konwertowania wartości numerycznych do tekstowych (w Oracle nie). Operatorem konkatenacji jest w tym wypadku + a nie ||

SELECT CONVERT(VARCHAR, empno) + ename AS employee FROM emp;

8. Utworzyć zapytanie zwracające wynik w postaci np. „Kowalski pracuje w dziale 20”.

SELECT ename || ‘ pracuje w dziale nr ' || deptno FROM emp;

MS SQL:

SELECT ename + ‘ pracuje w dziale nr ' + CONVERT(VARCHAR,deptno) FROM emp;

9. Wyliczyć roczną pensję całkowitą dla każdego pracownika.

SELECT ename, (sal * 12 + NVL(comm,0)) FROM emp;

Użycie funkcji NVL jest niezbędne, gdyż kolumna comm u niektórych pracowników zawiera wartość NULL. Wykonywanie operacji arytmetycznych na wartości NULL daje również NULL np. 2 + NULL = NULL, natomiast 2 + NVL(NULL, 0) = 2.

W MS SQL odpowiednikiem funkcji NVL jest ISNULL:

(2)

SELECT ename, (sal * 12 + ISNULL(comm,0)) FROM emp;

10. Wyświetlić wszystkie numery departamentów występujące w tabeli EMP.

SELECT deptno FROM Emp;

11. Wyświetlić wszystkie różne numery departamentów występujące w tabeli EMP.

SELECT DISTINCT deptno FROM Emp;

12. Wybrać wszystkie wzajemnie różne kombinacje wartości DEPTNO i JOB.

SELECT DISTINCT deptno, job FROM Emp;

13. Posortować wszystkie dane tabeli EMP według ENAME.

SELECT * FROM emp

ORDER BY ename;

14. Posortować malejąco wszystkie dane tabeli EMP według daty zatrudnienia począwszy od ostatnio zatrudnionych.

SELECT * FROM emp ORDER BY hiredate DESC;

15. Posortować dane tabeli EMP według wzrastających wartości kolumn DEPTNO oraz malejących wartości kolumny SAL (bez wypisywania kolumny SAL).

SELECT empno, ename, job, mgr FROM emp

ORDER BY deptno, sal DESC;

Domyślnie przyjęty jest porządek rosnący i dlatego nie musimy pisać ASC po deptno.

16. Wybrać nazwiska, stanowiska pracy, płacę i numery departamentów wszystkich zatrudnionych na stanowisku CLERK.

SELECT ename, job, sal, deptno FROM emp WHERE job = 'CLERK';

17. Wybrać wszystkie nazwy i numery departamentów o numerze większym od 20.

SELECT deptno, dname FROM dept

WHERE deptno > 20;

18. Wybrać pracowników, których prowizja przekracza miesięczną pensję.

WHERE NVL(comm,0) > sal;

MS SQL:

WHERE ISNULL(comm,0) > sal;

19. Wybrać dane tych pracowników, których zarobki mieszczą się pomiędzy 1000 a 2000.

SELECT *

(3)

FROM emp

WHERE sal BETWEEN 1000 AND 2000;

20. Wybrać dane pracowników, których bezpośrednimi szefami są 7902, 7566 lub 7788.

WHERE mgr IN (7902, 7566, 7788);

Rozwiązanie bez użycia IN:

SELECT * FROM emp WHERE mgr = 7902 OR mgr = 7566 OR mgr = 7788;

21. Wybrać dane tych pracowników, których nazwiska zaczynają się na S.

WHERE ename LIKE 'S%';

22. Wybrać dane tych pracowników, których nazwiska są czteroliterowe.

WHERE LENGTH(ename) = 4;

Inne rozwiązanie:

WHERE ename LIKE ‘____’

MS SQL:

WHERE LEN(ename) = 4;

23. Wybrać dane tych pracowników, którzy nie posiadają szefa.

WHERE mgr IS NULL;

24. Wybrać dane tych pracowników, których zarobki są poza przedziałem <1000, 2000>.

WHERE NOT sal BETWEEN 1000 AND 2000;

Rozwiązanie bez użycia BETWEEN:

SELECT * FROM emp

WHERE sal < 1000 OR sal > 2000;

25. Wybrać dane tych pracowników, których nazwiska nie zaczynają się na M.

WHERE NOT ename LIKE 'M%';

26. Wybrać dane tych pracowników, którzy mają szefa.

WHERE mgr IS NOT NULL;

(4)

27. Wybrać dane tych pracowników zatrudnionych na stanowisku CLERK, których zarobki mieszczą się w przedziale <1000, 2000).

WHERE job = 'CLERK' AND sal >= 1000 AND sal < 2000;

28. Wybrać dane pracowników albo zatrudnionych na stanowisku CLERK, albo tych, których zarobki mieszczą się w przedziale <1000, 2000).

WHERE job = 'CLERK' OR (sal >= 1000 AND sal < 2000);

29. Wybrać wszystkich pracowników zatrudnionych na stanowisku MANAGER z pensją powyżej 1500 oraz wszystkich na stanowisku SALESMAN.

WHERE (sal > 1500 AND job = 'MANAGER') OR job = 'SALESMAN';

30. Wybrać wszystkich pracowników zatrudnionych na stanowisku MANAGER lub na stanowisku SALESMAN z pensją powyżej 1500.

WHERE job = 'MANAGER' OR (job = 'SALESMAN' AND sal >1500);

31. Wybrać wszystkich pracowników zatrudnionych na stanowisku MANAGER ze wszystkich departamentów wraz ze wszystkimi pracownikami zatrudnionymi na stanowisku CLERK w departamencie 10.

WHERE job = ‘MANAGER’ OR (job = ‘CLERK’ AND deptno = 10) 32. Wybrać wszystkie dane z tabeli SALGRADE.

SELECT * FROM salgrade

33. Wybrać wszystkie dane z tabeli DEPT SELECT *

FROM dept

34. Wybrać dane tych pracowników, których zarobki mieszczą się w przedziale

<1000,2000>

WHERE sal BETWEEN 1000 AND 2000

35. Wybrać numery i nazwy departamentów sortując według numerów departamentów SELECT deptno, dname

FROM dept

ORDER BY deptno

36. Wybrać wszystkie wzajemnie różne stanowiska pracy SELECT DISTINCT job

(5)

FROM emp

37. Wybrać dane pracowników zatrudnionych w departamentach 10 i 20 w kolejności alfabetycznej ich nazwisk

WHERE deptno = 10 OR deptno = 20 ORDER BY ename

38. Wybrać nazwiska i stanowiska pracy wszystkich pracowników z departamentu 20 zatrudnionych na stanowisku CLERK

SELECT ename, job FROM emp

WHERE deptno = 20 AND job = ‘CLERK’

39. Wybrać nazwiska tych pracowników, w których nazwisku występuje ciąg „TH” lub

„LL”

SELECT ename FROM emp

WHERE ename LIKE ‘%TH%’ OR ename LIKE ‘%LL%’

40. Wybrać nazwisko, stanowisko i pensję pracowników, którzy posiadają szefa SELECT ename, job, sal

FROM emp

WHERE mgr IS NOT NULL

41. Wybrać nazwiska i całkowite roczne zarobki wszystkich pracowników SELECT ename, sal*12 + NVL(comm, 0)

FROM emp MS SQL

SELECT ename, sal*12 + ISNULL(comm, 0) FROM emp

42. Wybrać ENAME, DEPTNO i HIREDATE tych pracowników, którzy zostali zatrudnieni w 1980r.

SELECT ename, deptno, hiredate FROM emp

WHERE hiredate >= ‘01/01/80’ AND hiredate <= ‘31/12/80’

Format daty może zależeć od ustawień serwera. Warto wcześniej sprawdzić, jaki format jest ustawiony na serwerze lub użyć funkcji konwertujących / porównujących daty.

43. Wybrać nazwiska, roczna pensję oraz prowizję tych wszystkich sprzedawców, których miesięczna pensja przekracza prowizję. Wyniki posortować według malejących zarobków, potem nazwisk

SELECT ename, sal * 12, comm FROM emp

WHERE sal > NVL(comm,0) ORDER BY sal DESC, ename MS SQL

SELECT ename, sal * 12, comm

(6)

FROM emp

WHERE sal > ISNULL(comm,0) ORDER BY sal DESC, ename

(7)

WYBIERANIEDANYCHZWIELU TABEL

1. Połącz dane z tabeli EMP i DEPT przy pomocy warunku złączenia w WHERE.

SELECT * FROM emp, dept

WHERE emp.deptno = dept.deptno;

Gdyby nie było warunku złączenia (WHERE emp.deptno = dept.deptno), to każdy pracownik zostałby wypisany kilkakrotnie, połączony z każdym działem.

2. Połącz dane z tabeli EMP i DEPT przy pomocy INNER JOIN.

SELECT *

FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;

To zapytanie zadziała dokładnie tak samo jak to z punktu 1. Różnica jest taka, że przy pomocy INNER JOIN możemy oddzielić warunki złączenia od pozostałych

warunków.

3. Wybierz nazwiska oraz nazwy departamentów wszystkich pracowników w kolejności alfabetycznej.

SELECT ename, dname FROM emp, dept

WHERE emp.deptno = dept.deptno ORDER BY ename

4. Wybierz nazwiska wszystkich pracowników wraz z numerami i nazwami departamentów w których są zatrudnieni.

SELECT ename, dname, emp.deptno FROM emp, dept

WHERE emp.deptno = dept.deptno;

Wybierając numery departamentów musimy określić, z której tabeli mają być one wybrane (emp.deptno), ponieważ kolumna deptno występuje zarówno w tabeli emp jak i w dept. Wszystko jedno, z której tabeli wybierzemy, ponieważ jak określa warunek WHERE, numery te są sobie równe.

5. Dla pracowników o miesięcznej pensji powyżej 1500 podaj ich nazwiska, miejsca usytuowania ich departamentów oraz nazwy tych departamentów.

SELECT ename, loc, dname FROM emp, dept

WHERE sal > 1500 AND emp.deptno = dept.deptno;

6. Utwórz listę pracowników podając ich nazwisko, zawód, pensję i stopień zaszeregowania.

SELECT ename, job, sal, grade FROM emp, salgrade

WHERE sal BETWEEN losal AND hisal;

W tym zapytaniu mamy do czynienia ze złączeniem nie po kluczach (klucz główny = klucz obcy) tylko po wartościach atrybutów.

7. Wybierz informacje o pracownikach, których zarobki odpowiadają klasie 3.

SELECT ename, sal, job FROM emp, salgrade

WHERE sal BETWEEN losal AND hisal AND grade = 3;

8. Wybierz pracowników zatrudnionych w Dallas.

SELECT ename, loc

(8)

FROM emp, dept

WHERE emp.deptno = dept.deptno AND loc = ‘DALLAS’

9. Wybierz nazwiska pracowników, nazwy działów i stopnie zaszeregowania SELECT ename, dname, grade

FROM emp

INNER JOIN dept ON emp.deptno = dept.deptno

INNER JOIN salgrade ON sal BETWEEN losal AND hisal

Oczywiście równie dobre byłoby rozwiązanie bez użycia INNER JOIN (z warunkami złączenia w WHERE).

10. Wypisz dane wszystkich działów oraz ich pracowników tak, aby dane działu pojawiły się, nawet jeśli nie ma w dziale żadnego pracownika.

SELECT * FROM dept

LEFT JOIN emp ON emp.deptno = dept.deptno;

LEFT JOIN określa, że warunek złączenia może, ale nie musi być spełniony aby rekord z tabeli dept został wyświetlony. Jeżeli warunek nie jest spełniony (np. dla działu 40 w którym nikt nie pracuje), wyświetlą się dane działu, a pozostałe pola (te z tabeli emp) będą ustawione na NULL.

11. Wybierz pracowników z działu 20 i 30 (nazwisko i nr działu – wypisz dział 40 bez nazwisk).

SELECT ename, deptno FROM emp WHERE deptno = 20 UNION SELECT ‘’, deptno FROM emp WHERE deptno = 30;

Dwa apostrofy w drugim zapytaniu są potrzebne, ponieważ obydwa zapytania w unii muszą mieć tyle samo pól (pola muszą sobie odpowiadać).

Możemy jednak zauważyć, że wypisał się tylko jeden pracownik z działu 30, a z pewnością jest ich więcej. Jest to spowodowane tym, że UNION usuwa duplikaty podobnie do DISTINCT, więc wszystkie rekordy (‘’, 30) zostają zbite w jeden rekord.

Aby pozbyć się tego problemu, musimy dodać jakieś pole, które rozróżni rekordy, np.

empno lub użyć UNION ALL.

SELECT ename, deptno, empno FROM emp WHERE deptno = 20 UNION SELECT ‘’, deptno, empno FROM emp WHERE deptno = 30;

12. Wypisz stanowiska występujące w dziale 10 oraz 30.

SELECT job FROM emp WHERE deptno = 10

UNION SELECT job FROM emp WHERE deptno = 30;

Oczywiście można to też zrobić bez użycia UNION np.:

SELECT DISTINCT job FROM emp WHERE deptno IN (10, 30);

13. Wypisz stanowiska występujące zarówno w dziale 10 jak w dziale 30.

INTERSECT SELECT job FROM emp WHERE deptno = 30;

INTERSECT nie występuje w standardzie SQL i nie zadziała również w MS SQL.

Aby rozwiązać to zadanie bez INTERSECT, należałoby użyć podzapytań (omówione w dziale 4).

14. Wypisz stanowiska występujące w dziale 10 a nie występujące w dziale 30.

MINUS SELECT job FROM emp WHERE deptno = 30;

MINUS nie występuje w standardzie SQL i nie zadziała również w MS SQL. Aby

(9)

rozwiązać to zadanie bez MINUS, należałoby użyć podzapytań (omówione w dziale 4).

15. Wybierz pracowników, którzy zarabiają mniej od swoich kierowników.

SELECT pracownik.ename, pracownik.sal FROM emp pracownik, emp kierownik

WHERE pracownik.mgr = kierownik.empno AND pracownik.sal < kierownik.sal W tym zapytaniu musimy złączyć tabelę emp z samą sobą (mgr jest kluczem obcym z tabeli emp i wskazuje kierownika). Musimy więc użyć dwukrotnie tabeli emp po FROM. Aby wiedzieć, do której z tabel emp się odwołujemy, musimy nadać aliasy (pracownik i kierownik) a następnie odwoływać się do nich tak, jakby były to normalne tabele.

16. Dla każdego pracownika wypisz jego nazwisko oraz nazwisko jego szefa. Posortuj według nazwiska szefa.

SELECT pracownik.ename, kierownik.ename FROM emp pracownik, emp kierownik

WHERE pracownik.mgr = kierownik.empno ORDER BY kierownik.ename

(10)

III. FUNKCJE GRUPUJĄCE 1. Oblicz średni zarobek w firmie.

SELECT AVG(sal) FROM emp;

2. Znajdź minimalne zarobki na stanowisku CLERK.

SELECT MIN(sal) FROM emp

WHERE job = ‘CLERK’

3. Znajdź ilu pracowników zatrudniono w departamencie 20.

SELECT COUNT(*) FROM emp

WHERE deptno = 20;

4. Obliczyć średnie zarobki na każdym ze stanowisk pracy.

SELECT job, AVG(sal) FROM emp

GROUP BY job;

5. Obliczyć średnie zarobki na każdym ze stanowisk pracy z wyjątkiem stanowiska MANAGER.

WHERE NOT job = ‘MANAGER’

GROUP BY job;

6. Obliczyć średnie zarobki na każdym ze stanowisk pracy w każdym departamencie.

SELECT deptno, job, AVG(sal) FROM emp

GROUP BY deptno, job;

7. Dla każdego stanowiska oblicz maksymalne zarobki.

SELECT MAX(sal), job FROM emp

GROUP BY job;

8. Wybrać średnie zarobki tylko tych departamentów, które zatrudniają więcej niż trzech pracowników.

SELECT deptno, AVG(sal) FROM emp

GROUP BY deptno HAVING COUNT(*) > 3;

9. Wybrać tylko te stanowiska, na których średni zarobek wynosi 3000 lub więcej.

GROUP BY job

HAVING AVG(sal) >= 3000;

--- -

10.Znajdź średnie miesięczne pensje oraz średnie roczne zarobki dla każdego stanowiska, pamiętaj o prowizji.

(11)

SELECT AVG(sal), AVG(sal*12 + NVL(comm,0)) FROM emp

GROUP BY job;

MS SQL

SELECT AVG(sal), AVG(sal*12 + ISNULL(comm,0)) FROM emp

GROUP BY job;

11.Znajdź różnicę miedzy najwyższą i najniższa pensją.

SELECT MAX(sal) - MIN(sal) FROM emp

12.Znajdź departamenty zatrudniające powyżej trzech pracowników.

SELECT deptno, COUNT(*) FROM emp

GROUP BY deptno

HAVING COUNT (*) > 3;

13.Sprawdź, czy wszystkie numery pracowników są rzeczywiście wzajemnie różne.

SELECT MAX(COUNT(*)) FROM emp

GROUP BY empno;

Aby sprawdzić, czy numery empno są rzeczywiście wzajemnie różne, grupujemy po nich i liczymy ile jest rekordów w każdej grupie. Następnie bierzemy maksimum z tych numerów. Jeżeli jest 1 to ok., jeżeli np. 2 to znaczy, że istnieje 2 pracowników o takim samym numerze.

14.Podaj najniższe pensje wypłacane podwładnym swoich kierowników. Wyeliminuj grupy o minimalnych zarobkach niższych niż 1000. Uporządkuj według pensji.

SELECT mgr, MIN(sal) AS najnizsza FROM emp

GROUP BY mgr

HAVING MIN(sal) > 1000 ORDER BY najnizsza;

--- 15.Wypisz ilu pracowników ma dział mający siedzibę w DALLAS.

SELECT COUNT(*) FROM emp, dept

WHERE dept.loc = ‘DALLAS’ AND emp.deptno = dept.deptno;

16.Podaj maksymalne zarobki dla każdej klasy zarobkowej.

SELECT MAX(sal), grade FROM emp, salgrade

WHERE sal BETWEEN losal AND hisal GROUP BY grade;

17.Sprawdź, które wartości zarobków powtarzają się.

SELECT sal, COUNT(*) FROM emp

GROUP BY sal

HAVING COUNT(*) > 1;

(12)

18.Podaj średni zarobek pracowników z drugiej klasy zarobkowej.

SELECT AVG(sal) FROM emp, salgrade

WHERE grade = 2 AND sal BETWEEN losal AND hisal;

19.Sprawdź, ilu podwładnych ma każdy kierownik.

SELECT mgr, COUNT(*) FROM emp

GROUP BY mgr;

20.Podaj sumę, którą zarabiają razem wszyscy pracownicy z pierwszej klasy zarobkowej.

SELECT SUM(sal) FROM emp, salgrade

WHERE sal BETWEEN losal AND hisal AND grade = 1;

(13)

IV. PODZAPYTANIA

1. Znaleźć pracowników z pensją równą minimalnemu zarobkowi w firmie.

WHERE sal = (SELECT MIN(sal) FROM emp);

2. Znaleźć wszystkich pracowników zatrudnionych na tym samym stanowisku co BLAKE.

WHERE job = (SELECT job FROM emp WHERE ename = ‘BLAKE’);

3. Znaleźć pracowników o pensjach z listy najniższych zarobków osiągalnych w departamentach.

WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY dept);

4. Znaleźć pracowników o najniższych zarobkach w ich departamentach.

SELECT * FROM emp a

WHERE sal = (SELECT MIN(sal) FROM emp b WHERE a.deptno = b.deptno);

5. Stosując operator ANY wybrać pracowników zarabiających powyżej najniższego zarobku z departamentu 30.

WHERE sal > ANY (SELECT sal FROM emp WHERE dept = 30);

6. Znaleźć pracowników, których zarobki są wyższe od pensji każdego pracownika z departamentu 30.

WHERE sal > ALL (SELECT sal FROM emp WHERE dept = 30);

7. Wybrać departamenty, których średnie zarobki przekraczają średni zarobek departamentu 30.

SELECT DISTINCT deptno FROM emp

GROUP BY deptno

HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno = 30);

8. Znaleźć stanowisko, na którym są najwyższe średnie zarobki.

SELECT job FROM emp GROUP BY job

HAVING AVG(sal) = (SELECT MAX(AVG(sal)) FROM emp GROUP BY job);

W MS SQL nie można używać MAX(AVG(…)). Możemy jednak to zadanie rozwiązać inaczej:

SELECT job FROM emp GROUP BY job

HAVING AVG(sal) >= ALL (SELECT AVG(sal) FROM emp GROUP BY job);

(14)

9. Znaleźć pracowników, których zarobki przekraczają najwyższe pensje z departamentu SALES.

WHERE sal > (SELECT MAX(sal) FROM emp, dept WHERE emp.deptno = dept.deptno AND dname = ‘SALES’);

10.Znaleźć pracowników, którzy zarabiają powyżej średniej w ich departamentach.

WHERE sal > (SELECT AVG(sal) FROM emp b WHERE a.deptno = b.deptno);

11.Znaleźć pracowników, którzy posiadają podwładnych za pomocą operatora EXISTS.

WHERE EXISTS (SELECT * FROM emp b WHERE b.mgr = a.empno);

12.Znaleźć pracowników, których departament nie występuje w tabeli DEPT.

WHERE deptno NOT IN (SELECT deptno FROM dept);

Wynik ‘No rows selected’ jest normalny, ponieważ w demonstracyjnej bazie (demobld) nie ma takich pracowników.

--- -

13.Wskazać dla każdego departamentu ostatnio zatrudnionych pracowników. Uporządkować według dat zatrudnienia.

WHERE hiredate = (SELECT MAX(hiredate) FROM emp b WHERE a.deptno = b.deptno)

ORDER BY hiredate;

14.Podać ENAME, SAL i DEPTNO dla pracowników, których zarobki przekraczają średnią ich departamentów.

SELECT ename, sal, deptno FROM emp a

WHERE sal > (SELECT AVG(sal) FROM emp b WHERE b.deptno = a.deptno);

15.Stosując podzapytanie znaleźć departamenty, w których nikt nie pracuje.

SELECT * FROM dept

WHERE deptno NOT IN (SELECT deptno FROM emp);

16.Dla każdego stanowiska wypisać maksymalną pensję z dopiskiem „Maksymalna” oraz minimalną z dopiskiem „Minimalna”. Posortować wynik według stanowiska.

SELECT job, zarobki, minmax FROM

(SELECT job, MAX(sal) AS zarobki, ‘Maksymalna’ AS minmax FROM emp GROUP BY job

UNION SELECT job, MIN(sal) AS zarobki, ‘Minimalna’ AS minmax FROM emp GROUP BY job) abc

ORDER BY job;

(15)

Zapytania SQL można używać po FROM zamiast tabeli. Trzeba wtedy nadać takiemu podzapytaniu alias (w tym wypadku abc).

17.Napisz zapytanie zwracające procentowy udział liczby pracowników w każdym dziale.

SELECT deptno, w_dziale / razem * 100

FROM (SELECT COUNT(*) AS razem FROM emp) a,

(SELECT deptno, COUNT(*) AS w_dziale FROM emp GROUP BY deptno) b W MS SQL istnieje konieczność konwersji wyniku do typu zmiennopozycyjnego, ponieważ COUNT zwraca typ INT, a wynik dzielenia INT / INT jest również typu INT.