ZADANIA

• Proste zapytania SQL

1. Wybrać numery departamentów (deptno), nazwiska pracowników (ename) oraz numery pracownicze (mgr) ich szefów z tabeli EMP.

SELECT deptno, ename, mgr FROM emp;

2. Wybrać wszystkie kolumny z tabeli EMP .

SELECT * FROM emp;

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

SELECT ename, sal*12 AS "Pensja podstawowa" FROM emp;

4. Wyliczyć roczną pensję podstawową dla każdego pracownika po podwyżce płacy miesięcznej o 250.

SELECT ename, sal*12+250 AS "Pensja podstawowa po podwyżce" FROM emp;

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

SELECT ename, sal*12 AS "ROCZ" FROM emp;

6. Wybrane wyrażenie SAL*12 zaetykietować nagłówkiem Roczna Pensja.

SELECT ename, sal*12 AS 'Roczna Pensja' FROM emp;

7. Połączyć numer pracowniczy (empno) i nazwisko (ename) w jednej kolumnie i opatrzyć ją nagłówkiem EMPLOYEE.

SELECT empno || ename AS "EMPLOYEE" FROM emp;

8. Utworzyć zapytanie zwracające wynik w postaci „Pracownik Kowalski pracuje w dziale nr 20 i zarabia miesięcznie 1230”. Kolumnę wynikową Nazwać „Informacje o pracowniku”.

SELECT 'Pracownik ' || ename || ' pracuje w dziale ' || deptno || ' i zarabia miesięcznie ' || sal AS "Informacje o pracowniku" FROM emp;

9. Wyliczyć całkowite roczne dochody 12 x pensja (sal) + prowizja (comm) dla każdego pracownika.

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

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

SELECT deptno FROM emp;

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

SELECT DISTINCT deptno FROM emp;

12. Wybrać wszystkie wzajemnie różne kombinacje numerów departamentów (deptno) i stanowisk (job).

SELECT deptno, job FROM emp;

13. Posortować wszystkie dane tabeli EMP według nazwisk (ename).

SELECT * FROM emp

ORDER BY ename;

14. Posortować malejąco wszystkie dane tabeli EMP według daty zatrudnienia (hiredate) 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, hiredate, comm, deptno FROM emp

ORDER BY deptno ASC, sal DESC;

16. Wybrać nazwiska (ename), numery pracowników (empno), stanowiska pracy (job) i numery departamentów (deptno) wszystkich pracowników zatrudnionych na stanowisku CLERK

SELECT ename, empno, job, deptno FROM emp

WHERE job='CLERK';

17. Wybrać wszystkie nazwy (dname) i numery departamentów(deptno) większe od nr 20

SELECT dname, deptno FROM dept

WHERE deptno>'20';

18. Wybrać dane pracowników, których prowizja (comm) przekracza miesięczną pensję (sal)

SELECT * FROM emp

WHERE comm > sal;

19. Wybrać dane tych pracowników, których zarobki mieszczą się w przedziale <1000 ; 2000>.

SELECT * FROM emp

WHERE sal BETWEEN 1000 AND 2000;

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

SELECT * FROM emp

WHERE mgr in ('7788', '7902', '7566');

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

SELECT * FROM emp

WHERE ename LIKE 'S%';

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

SELECT * FROM emp

WHERE length(ename) = 4;

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

SELECT * FROM emp

WHERE mgr IS NULL;

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

SELECT * FROM emp

WHERE sal NOT BETWEEN 1000 AND 2000;

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

SELECT ename FROM emp

WHERE ename NOT LIKE 'M%';

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

SELECT * FROM emp

WHERE mgr IS NOT NULL;

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

SELECT * FROM emp

WHERE job = 'CLERK' AND sal BETWEEN 1000 AND 2000;

28. Wybrać dane pracowników, którzy albo są zatrudnieni na stanowisku CLERK, albo ich zarobki sal mieszczą się w przedziale <1000 ; 2000).

SELECT * FROM emp

WHERE job = 'CLERK' OR sal BETWEEN 1000 AND 2000;

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

SELECT * FROM emp

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

30. Wybrać wszystkich pracowników zatrudnionych na stanowisku MANAGER lub na stanowisku SALESMAN, lecz zarabiających powyżej 1500

SELECT * FROM emp

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.

SELECT * FROM emp

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>

SELECT * FROM emp

WHERE sal BETWEEN 1000 AND 2000;

35. Wybrać numery i nazwy departamentów sortując wyniki 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 FROM emp;

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

SELECT * FROM emp

WHERE deptno IN('10','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. Znaleźć pracowników , w których nazwisku występuje ciąg „TH” lub „LL”.

SELECT * 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))AS "Roczne zarobki" FROM emp;

42. Wybrać nazwisko (ename), numer departamentu (deptno) i datę zatrudnienia (hiredate) tych pracowników, którzy zostali zatrudnieni w 1980r.

SELECT ename, deptno, hiredate FROM emp

WHERE hiredate BETWEEN '80/01/01' AND '80/12/31';

43. Wybrać nazwiska, roczną pensję oraz prowizję tych wszystkich sprzedawców (SALESMAN), których miesięczna pensja jest większa od prowizji. Wyniki posortować według malejących zarobków, potem nazwisk.

SELECT ename, sal*12, comm FROM emp

WHERE job = 'SALESMAN' AND sal > comm

ORDER BY sal*12 DESC, ename DESC;

• Wybieranie danych z wielu tabel

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

SELECT * FROM emp, dept

WHERE dept.deptno = emp.deptno;

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

SELECT * FROM emp INNER JOIN dept

ON dept.deptno = emp.deptno;

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

SELECT ename, dname FROM emp INNER JOIN dept

ON dept.deptno = emp.deptno

ORDER BY ename;

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

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

ON dept.deptno = emp.deptno;

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

SELECT ename, dname, loc FROM emp INNER JOIN dept

ON dept.deptno= emp.deptno

WHERE sal > 1500;

6. Utwórz listę pracowników podając ich nazwisko, zawód, pensję i grupę zarobkową.

SELECT ename, sal, grade FROM emp INNER JOIN salgrade

ON sal BETWEEN losal AND hisal;

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

SELECT * FROM emp INNER JOIN salgrade

ON sal BETWEEN losal AND hisal

WHERE grade = '3';

8. Wybierz pracowników zatrudnionych w Dallas.

SELECT * FROM emp INNER JOIN dept

ON dept.deptno = emp.deptno

WHERE dept.loc = 'DALLAS';

9. Wybierz nazwiska pracowników, nazwy działów i grupy zarobkowe.

SELECT ename, dname, grade FROM emp INNER JOIN dept

ON emp.deptno = dept.deptno

INNER JOIN salgrade

ON sal BETWEEN losal AND hisal

WHERE grade = '3';

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 dept.deptno, dept.dname, dept.loc, emp.ename FROM dept LEFT JOIN emp

ON dept.deptno = emp.deptno;

----------------------------------------------------------------------------------------------------------

11. Wybierz nazwiska i nazwę działu dla pracowników z działu 30 oraz w tym samym zapytaniu nazwę działu 20 bez nazwisk zatrudnionych tam pracowników.

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

SELECT DISTINCT job from emp

WHERE deptno IN('10','30');

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

SELECT * FROM emp p INNER JOIN emp k

ON p.mgr = k.empno

WHERE p.sal > k.sal;

14. Dla każdego pracownika wypisz jego nazwisko oraz nazwisko jego szefa. Posortuj według nazwiska szefa, kolumny nazwij odpowiednio Pracownik i Szef.

SELECT kier.ename AS "Szef", prac.ename AS "Podwladny" FROM emp kier INNER JOIN emp prac

ON kier.empno = prac.mgr;

• FUNKCJE GRUPUJĄCE

1. Oblicz średni zarobek w firmie.

SELECT AVG(sal) AS "Zarobek średni" FROM emp;

2. Znajdź minimalne zarobki na stanowisku CLERK.

SELECT MIN(sal) FROM emp

WHERE job IN('CLERK');

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

SELECT COUNT(empno) FROM emp

WHERE deptno = '20';

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

SELECT job, AVG(sal) FROM emp

GROUP BY job;

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

SELECT job, AVG(sal)FROM emp

WHERE job NOT IN('MANAGER')

GROUP BY job;

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

7. Dla każdego stanowiska oblicz maksymalne zarobki.

SELECT job, MAX(sal)FROM emp

GROUP BY job;

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

SELECT job, AVG(sal), COUNT(empno) FROM emp

GROUP BY job

HAVING COUNT(empno) > 3;

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

SELECT job, AVG(sal) FROM emp

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.

SELECT job, AVG(sal) AS "Miesieczna", AVG((sal*12) + NVL(comm,0)) AS "Roczna" FROM emp

GROUP BY job;

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

SELECT MAX(sal) - MIN(sal) AS "Różnica" FROM emp;

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

SELECT deptno, COUNT(ename) FROM emp

GROUP BY deptno

HAVING COUNT(empno) > 3;

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

SELECT empno FROM emp

GROUP BY empno

HAVING COUNT(*) > 1;

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) FROM emp

GROUP BY mgr

HAVING MIN (sal) > =1000

ORDER BY MIN(sal);

-------------------------------------------------------------------------------------------------------------

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

SELECT loc, COUNT(empno) AS Pracownicy FROM emp, dept

WHERE loc IN('DALLAS') AND emp.deptno = dept.deptno

GROUP BY loc;

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

SELECT MAX(sal), grade FROM emp INNER JOIN salgrade

ON sal BETWEEN losal AND hisal

GROUP BY grade;

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

SELECT sal FROM emp

GROUP BY sal

HAVING COUNT(*) > 1;

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

SELECT AVG(sal), grade FROM emp INNER JOIN salgrade

ON sal BETWEEN losal AND hisal

WHERE grade = '2'

GROUP BY grade;

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

SELECT COUNT(*) FROM emp

GROUP BY mgr;

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

SELECT SUM(sal), grade FROM emp INNER JOIN salgrade

ON sal BETWEEN losal AND hisal

WHERE grade = '1'

GROUP BY grade;

• Podzapytania

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

SELECT * FROM emp

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

2. Znajdź wszystkich pracowników zatrudnionych na tym samym stanowisku co pracownik o nazwisku BLAKE.

SELECT * FROM emp

WHERE emp.job=(SELECT emp.job FROM emp WHERE emp.ename = 'BLAKE');

3. Znajdź pracowników, których zarobki znajdują się na liście najniższych zarobków osiąganych w poszczególnych departamentach.

SELECT ename, sal, deptno FROM emp

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

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

SELECT ename, sal, deptno FROM emp

WHERE sal IN

(SELECT MIN(sal) FROM emp

GROUP BY deptno);

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

SELECT * FROM emp

WHERE sal > ANY

(SELECT sal FROM emp WHERE deptno = '30');

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

SELECT ename, sal FROM emp

WHERE sal > ALL

(SELECT sal FROM emp WHERE deptno = '30');

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

SELECT deptno, AVG(sal) FROM emp

GROUP BY deptno

HAVING AVG(sal) >

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

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

SELECT job, AVG(sal) FROM emp

GROUP BY job

HAVING AVG(sal) =

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

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

SELECT ename, sal FROM emp

WHERE sal >

(SELECT MAX(sal) FROM emp WHERE deptno =

(SELECT deptno FROM dept WHERE dname = 'SALES'));

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

SELECT * FROM emp a

WHERE sal >

(SELECT AVG(sal) FROM emp b

WHERE a.deptno= b.deptno);

11. Za pomocą operatora EXISTS znajdź pracowników, którzy posiadają podwładnych.

SELECT * FROM emp k

WHERE EXISTS

(SELECT 'x' FROM emp p WHERE p.mgr = k.empno)

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

------------------------------------------------------------------------------------------------------------

13. Dla każdego stanowiska wypisz maksymalną pensję z dopiskiem „Maksymalna” oraz minimalną z dopiskiem „Minimalna”. Posortuj wynik według stanowiska.

SELECT job, MAX(sal) AS maxymalna, MIN(sal) minimalna FROM emp

GROUP BY job

ORDER BY job;

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

15. Wskaż dla każdego departamentu ostatnio zatrudnionych pracowników. Uporządkuj według dat zatrudnienia.

16. Podaj 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 a.deptno = b.deptno);

17. Stosując podzapytanie znajdź departamenty, w których nikt nie pracuje.

SELECT deptno, dname FROM dept

WHERE NOT EXISTS

(SELECT 'x' FROM emp WHERE dept.deptno = emp.deptno);

---