Ćwiczenie 2

I. Polecenie SELECT

Polecenie SELECT składa się zawsze ze słów kluczowych SELECT oraz FROM. Ponadto może zawierać klauzule:

1. WHERE - warunek wyboru wierszy

W klauzuli WHERE mogą wystąpić:

a) operatory relacji: =, < > , >, >=, <, <=,

np. SELECT * FROM ZESP WHERE ID_ZESP<40;

b) operatory specjalne:

LIKE - określenie wartości dla warunku

IN - wskazanie zbioru wartości dla warunku

BETWEEN ... AND - wskazanie zakresu wartości dla warunku

IS NULL

Uwaga: powyższe operatory mogą być negowane operatorem NOT np. IS NOT NULL

np. SELECT * FROM ZESP WHERE ADRES LIKE '_OS%';

Uwaga: znak % zastępuje sekwencję znaków, a podkreślenie "_" pojedynczy znak.

SELECT * FROM ETAT WHERE PLACA_MIN IN (1000, 2000, 3000);

SELECT NAZWISKO FROM PRAC

WHERE PLACA_POD BETWEEN 2000 AND 3000;

SELECT * FROM PRAC WHERE PLACA_DOD IS NULL;

c) operatory logiczne: AND, OR, NOT

np. SELECT * FROM PRAC

WHERE PLACA_POD>1500

AND (ETAT='ASYSTENT' OR ETAT ='SEKRETARKA');

2. DISTINCT - wyszukanie wierszy nie powtarzających się

np. SELECT DISTINCT ADRES FROM ZESP;

3. ORDER BY - porządkowanie alfabetyczne (domyślnie rosnąco)

np. SELECT * FROM PRAC ORDER BY NAZWISKO desc;

Uwaga: - asc oznacza porządek rosnący , desc - porządek malejący;

- klauzula ORDER BY wystąpić może tylko jako ostatnia;

- można wskazywać więcej niż jedną kolumnę w klauzuli ORDER BY, wskazaniem kolumny może być jej nazwa lub jej numer kolejny.

4. GROUP BY - grupuje wiersze o tej samej wartości wyszczególnionych kolumn. Po podziale, do każdej grupy można stosować tzw. funkcje grupowe np. count(*) - zliczającą ilości wystąpień, sum(a) - obliczenie sumy wartości kolumny a

np. SELECT ID_ZESP, count(*) FROM PRAC GROUP BY ID_ZESP;

Funkcje operujące na grupach krotek (funkcje kolumnowe):

avg([distinct|all]atr)	średnia
count([distinct|all]wyr)	zliczanie ilości wystąpień
max([distinct|all]wyr)
min([distinct|all]wyr)
stddev([distinct|all]atr)	odchylenie standardowe
sum([distinct|all]atr)
variance([distinct|all]atr)	wariancja

5. HAVING - umożliwia wybór informacji, dotyczących utworzonych grup

np. SELECT ID_ZESP, sum (PLACA_POD) FROM PRAC

GROUP BY ID_ZESP HAVING count(*) >3;

II. Wybieranie danych z wielu tabel.

Wybieranie danych z wielu tabel nazywa się złączeniem(ang. join). W klauzuli SELECT wymienia się kolumny, które chcemy wyświetlić, w klauzuli FROM określa się nazwy złączanych tabel a w klauzuli WHERE warunki złączenia.

1. Stosowanie aliasów w zapytaniu.

Aliasy definiuje się w celu skrócenia nazw tabel.

np. SELECT P.NAZWISKO, Z.ADRES

FROM PRACOWNICY P, ZESP Z

WHERE P.ID_ZESP = Z.IDZESP;

2. Klauzule UNION, UNION ALL, INTERSECT, MINUS

Powyższe klazule łącza dwa lub więcej zapytania SELECT z tą samą liczbą kolumn będących tego samego typu w jedną tabelę wynikową, posortowaną i nie zawierającą duplikatów. UNION stosuje się dla unii (sumy bez powtórzeń), UNION ALL - dla sumy z powtórzeniami, INTERSECT dla przekroju (iloczynu) a MINUS dla różnicy.

np. SELECT ETAT FROM PRAC

WHERE ID_ZESP = 10

intersect

SELECT ETAT FROM PRAC

WHERE ID_ZESP = 30;

Zadania do samodzielnego wykonania:

Zad. 1. Zdefiniować tablicę PRAC_KOPIA posiadająca taki sam schemat jak tablica PRAC i zawierającą tylko te wiersze z tablicy PRAC, których wartość atrybutu etat jest równa `STAZYSTA'.

CREATE TABLE KOPIA_PRAC AS SELECT * FROM PRAC WHERE ETAT='STAZYSTA';

Zad. 2. Wyświetlić wszystkie etaty, na których są zatrudnieni pracownicy.

SELECT ETAT FROM PRAC;

Zad. 3. Wyświetlić pracowników zatrudnionych od 1980 do 1985 roku.

SELECT * FROM PRAC WHERE ZATRUDNIONY BETWEEN '80/01/01' AND '85/12/31'

Zad. 4. Wyświetlić średnią płacę na każdym z etatów. //avg

SELECT ETAT, AVG(PLACA_POD+NVL(placa_dod,0)) AS SREDNIA_PLACA FROM PRAC GROUP BY ETAT;

Zad. 5. Znaleźć etat, na którym zatrudniono pracownika w pierwszej połowie 1984 roku lub w pierwszej połowie 1985.

SELECT ETAT FROM PRAC WHERE ZATRUDNIONY BETWEEN '84/01/01' AND '84/06/30' OR ZATRUDNIONY BETWEEN '85/01/01' AND '85/06/30';

Zad. 6. Wyświetlić nazwisko, etat, płacę podstawową pracowników zespołu 10 i 20 zgodnie z alfabetycznym porządkiem nazwisk.

SELECT NAZWISKO,ETAT,PLACA_POD FROM PRAC WHERE (ID_ZESP=10 OR ID_ZESP=20) ORDER BY NAZWISKO ASC;

Zad. 7. Wyświetlić nazwiska i sumaryczną płacę wszystkich asystentów

SELECT NAZWISKO FROM PRAC WHERE ETAT='ASYSTENT';

SELECT SUM(PLACA_POD+NVL(PLACA_DOD,0)) AS SUMA FROM PRAC WHERE ETAT='ASYSTENT';

Zad. 8. Wyświetlić pracowników, których nazwiska rozpoczynają się od litery M lub P.

SELECT * FROM PRAC WHERE (NAZWISKO LIKE 'M%' OR NAZWISKO LIKE 'P%');

Zad. 9. Wyświetlić posortowane nazwiska pracowników zarabiających od 2000 do 3000

SELECT NAZWISKO FROM PRAC WHERE(PLACA_POD+NVL(PLACA_DOD,0)) BETWEEN 2000 AND 3000 ORDER BY NAZWISKO ASC;

Zad. 10.Wyświetlić nazwiska pracowników, nazwy i adresy zespołów pracowników, których miesięczna pensja przekracza 2000 PLN.

SELECT PRAC.NAZWISKO,ZESP.NAZWA,ZESP.ADRES FROM PRAC,ZESP WHERE (PRAC.PLACA_POD+NVL(PRAC.PLACA_DOD,0)) > 2000 AND PRAC.ID_ZESP=ZESP.ID_ZESP;

Zad. 11. Wyświetlić pracowników z ulicy Sosnkowskiego w rosnącym porządku alfabetycznym ich nazwisk.

SELECT PRAC.NAZWISKO,ZESP.NAZWA,ZESP.ADRES FROM PRAC,ZESP WHERE ADRES LIKE 'SOSNKOWSKIEGO%' AND PRAC.ID_ZESP=ZESP.ID_ZESP ORDER BY PRAC.NAZWISKO ASC;

Zad. 12. Wyświetlić zespoły, które nie zatrudniają pracowników.

SELECT NAZWA,ADRES FROM ZESP WHERE ID_ZESP NOT IN (SELECT ID_ZESP FROM PRAC);

Zad. 13. Wyświetlić minimalne i maksymalne wynagrodzenie pracowników w poszczególnych grupach etatowych

SELECT ETAT, min(PLACA_POD+NVL(PLACA_DOD,0)), max(PLACA_POD+NVL(PLACA_DOD,0)) FROM PRAC GROUP BY ETAT;

Zad. 14. Wyświetlić liczbę profesorów wśród pracowników //Mount

SELECT count(*) AS "LICZBA PROF." FROM PRAC WHERE ETAT='PROFESOR';

LABORATORIUM z BAZ DANYCH

ORACLE

5

---