zapytania_SQL.doc

Zapytania SQL

ZAPYTANIA SQL

W Bazie występują trzy tabele:

              Emp {empno, ename, deptno(FK), mgr(FK), sal, comm, hiredate, job}

              Dept {deptno, dname, loc}

              Salgrade {grade, losal, hisal}

Uruchomienie zapisywania sesji w pliku tekstowym:

Spool nazwa_pliku (rozszerzenie txt albo sql)

Wyłączenie:

Spool off

Wypisanie nazw tabel dostępnych dla użytkownika:

Select Table_name from user_Tables;

Wypisanie struktury tabeli:

Desc nazwa_tabeli;

Słowa kluczowe SQL, nazwy tabel i kolumn nie są „case sensitive”, czyli możemy je pisać dowolnie - dużymi lub małymi literami. Natomiast wpisy w tabelach SĄ wrażliwe na wielkość liter i ALA oraz ala to dwie różne wartości.

I.      Część

Proste zapytania zwracające wszystkie rekordy z jednej tabeli. Klauzule SELECT i FROM są obligatoryjne w kazdym zapytaniu.

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ą pensję podstawową dla każdego pracownika.

SELECT ename, sal * 12
FROM emp;

4.                  Wyliczyć roczną pensje podstawową dla każdego pracownika, jeśli każdy dostanie 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 Roczna
FROM emp;

6.                  Wybrane wyrażenie sal * 12 zaetykietować nagłówkiem Placa Roczna.

SELECT ename, sal * 12 „Placa Roczna”
FROM emp;

7.                  Połączyć numer pracownika i nazwisko i opatrzyć je nagłówkiem Zatrudniony.

SELECT empno||ename Zatrudniony
FROM emp;

8.                  Literał: Utworzyć zapytanie zwracające połączony numer i nazwisko pracownika, tekst „Pracownik pracuje w dziale nr” i numer działu  z nagłówkiem „Dział”.

SELECT empno||’ ‘||ename Pracownik, 'Pracuje w dziale nr', deptno Dzial
FROM emp;

9.                  Wyliczyć roczną pensję całkowitą dla każdego pracownika (z uwzględnieniem premii).

SELECT ename,  (sal*12 + NVL(comm,0)) “Roczne dochody”
FROM emp;

Uwaga: dwuargumentowa funkcja NVL zwraca wartość podaną jako drugi argument w miejscu wystąpienia pseudowartośći „null”

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.

Powtarzające się rekordy eliminuje klauzula DISTINCT (opcjonalna)

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ć rosnąco wszystkie dane tabeli EMP według ENAME.

SELECT *
FROM emp
ORDER BY ename;

Klauzula ORDER BY (opcjonalna) sortuje zwracane w zapytaniu rekordy rosnąco (ASC – domyślne, można opuścić) lub malejąco (DESC) względem kolumn (jednej lub kilku) wyspecyfikowanych po klauzuli. W zapytaniu może pojawić sie tylko raz, na końcu.

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ącej wartości kolumny DEPTNO oraz malejących wartości kolumny SAL.

SELECT *
FROM emp
ORDER BY deptno, sal DESC;

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

Klauzula WHERE (opcjonalna) ograniczająca zwracane rekordy do tych tylko, które spełniają podany po WHERE warunek logiczny. Może być on negacją NOT, koniunkcją AND lub alternatywą OR kilku różnych warunków.

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

17.              Wybrać wszystkie nazwy i numery departamentów większe od 20.

SELECT deptno, dname
FROM dept
WHERE deptno > 20;

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

SELECT ename
FROM emp
WHERE comm > sal;

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

SELECT *
FROM emp
WHERE sal BETWEEN 1000 AND 2000;

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

SELECT ename, mgr
FROM emp
WHERE mgr IN (7902,7566,7788);

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

Predykat LIKE oraz znaki uniwersalne ‘_’ (zastępuje dowolny znak) i “%” (zastępuje dowolny ciąg znaków).

SELECT *
FROM emp
WHERE ename LIKE 'S%';

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

SELECT *
FROM emp
WHERE RTRIM(ename) LIKE '____';

Funkcja Rtrim (Ltrim, Trim) obcina sapcje (odpowiednio za, przed i z obu stron stringu). Ważne dla pól z dziedziną Char (Varchar nie uzupełnia stringu spacjami).

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 M.

SELECT *
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 mieszczą się w przedziale <1000, 2000>.

SELECT *
FROM emp
WHERE job = 'CLERK' AND sal BETWEEN 1000 AND 2000;

28.              Wybrać dane tych pracowników, którzy albo są zatrudnieni  na stanowisku CLERK, albo ich zarobki 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 na stanowisku SALESMAN.

SELECT *
FROM emp
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.

SELECT *
FROM emp
WHERE sal > 1500 AND (job = 'MANAGER' OR job = 'SALESMAN');

Użycie nawiasów zmienia normalną hierarchię operatorów logicznych NOT, AND, OR

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...