I. Témakör

Lekérdezések kifejezése relációs nyelvekben. SQL SELECT.
1. gyak. - Előkészítés
>> P1. Természetes lekérdezések kifejezése táblákkal (papíron)
2. gyak.  - Relációs algebra
>> P2. Relációs algebra alapműveletei (formális bevezetés) (papíron)
3. gyak.  - Egytáblás lekérdezések az SQL-ben
>> Géptermi gyakorlat, lásd Adatbázisok elérése (gépes)
>> 1.1. Vetítés (gépes)
>> 1.2. Kiválasztás (gépes)
4. gyak. - Többtáblás lekérdezések az SQL-ben
>> 1.3. Összekapcsolások (gépes)
>> 1.4. Halmazműveletek (gépes)
>> P3. Relációs algebra további műveletei (papíron)
5. gyak. - Függvények és csoportosítás az Oracle-ben
>> 1.5. Függvények (gépes)
>> 1.6. Csoportosítás (gépes)
>> P4. Kiterjesztett műveletek a relációs algebrában (papíron)
6. gyak. - Alkérdések és hierarchikus lekérdezések az Oracle-ben
>> 1.7. Alkérdések a WHERE-ben és HAVING-ben  (gépes)
>> 1.8. Alkérdések a FROM-ban, WITH utasítás  (gépes)
>> 1.9. Hierarchikus lekérdezések az Oracle-ben  (gépes)
>> P5. Rekurzió az SQL99-ben (papíron)

Segédanyag, az előadások példái:
-- Előadás tematikája és segédanyagok menüpont 3-6 EA bemutatók példáit próbáljuk ki!
-- Ehhez előkészítésként a HR séma tábláihoz készítsük el a szinonimákat create_synonym
   
A gyakorlatok példáihoz:
-- Táblák és feladatok: szeret_tabla.txt       Létrehozása: create_szeret.txt
-- Táblák és feladatok: dolgozo_tabla.txt    Létrehozása:  create_dolgozo.txt

További gyakorlásra: lásd  Órai mintapélda adatbázissémák
Ullman-Widom: Adatbázisrendszerek. Alapvetés. Második átdolg.kiad., 2009 
-- Táblák és feladatok: termekek_tabla.txt    Létrehozása: create_termekek.txt
-- Táblák és feladatok: csatahajok_tabla.txt    Létrehozása: create_csatahajok.txt
Kinde-Nagy: Oracle példatár feladatai: Feladatok.pdf
-- Ehhez a dept és emp táblák közvetlen létrehozása: cr_dept_emp.sql 
-- A dept, emp, salgrade, dummy, customer, ord, item, product, price táblák
    és a sales nézettábla létrehozása: cr_dept_stb_sales.sql
WWW: lásd Ajánlott irodalom és hasznos linkek
-- SQL feladatok, megoldások: http://sqlzoo.net/
-- SQL Tutorial: http://www.sql-tutorial.net/
______________________________________________________
1. gyak. (II.12)  - Természetes lekérdezések kifejezése táblákkal.

P1. Természetes lekérdezések kifejezése táblákkal (Papíron)

Táblák és feladatok: csatahajok_tabla.txt
_______________________________________________________
2. gyak. (II.19)  - Relációs algebra alapműveletei (formális bevezetés)

P2. Relációs algebra alapműveletei (formális bevezetés) (Papíron)

Relációs algebra alapműveletei:
vetítés, kiválasztás, unió, különbség, természetes összekapcsolás, átnevezés.

Segédanyag: Ullman-Widom 2.4.fejezet >> Tk1_24_RelAlgebra.pdf

Táblák és feladatok: szeret_tabla.txt
_______________________________________________________
3. gyak. (II.26)  - SQL SELECT --> alapok

Géptermi gyakorlat:
-- Lásd Adatbázisok elérése  
-- Az első alkalommal írjuk át a jelszavunkat mind az oradb mind az ablinux csatlakozásnál!

1.1. Vetítés, projekció

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM    table;

Segédanyag: >> SELECT lista példáit próbáljuk ki!
-- Ehhez előkészítésként a HR séma tábláihoz készítsük el a szinonimákat create_synonym
   
Táblák és feladatok: dolgozo_tabla.txt    Létrehozása:  create_dolgozo.txt

Feladatok a vetítésre:
- Adjuk meg a dolgozók között előforduló foglalkozások neveit.

1.2.Kiválasztás, szelekció

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM   table
[WHERE condition(s)];

2.1. egyszerű értékvizsgálat

- aritmetikai összehasonlítás =, >, >=, <, <=, <>
- LIKE '_ %'
- IS NULL | IS NOT NULL

2.2. egyszerű értékek listája

- BETWEEN...AND...
- IN (set) | NOT IN (set)

2.3. logikai kifejezések kombinációja AND, OR, NOT

- precedencia szabályok és zárójelezés

2.B. Kiválasztott sorok rendezése

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM   table
[WHERE condition(s)]
[ORDER BY column(s) [DESC]];

Segédanyag: >> WHERE feltétel példáit próbáljuk ki!
-- Ehhez előkészítésként a HR séma tábláihoz készítsük el a szinonimákat create_synonym
   
Táblák és feladatok: dolgozo_tabla.txt    Létrehozása:  create_dolgozo.txt

Feladatok a kiválasztásra:
- Kik azok a dolgozók, akiknek a fizetése > 2800?
- Kik azok a dolgozók, akik a 10-es vagy a 20-as osztályon dolgoznak?
- Kik azok a dolgozók, akik 1982.01.01 után léptek be a céghez?
   (Aktuális dátumformátumot lásd -> SYSDATE függvény)
- Kik azok a dolgozók, akiknek a jutaléka ismert? (vagyis nem NULL)
_______________________________________________________
 4. gyak. (III.5)  - SELECT --> Rel.algebrai alapműveletek kifejezése

1.3. Összekapcsolások

1.3.a. Descartes szorzat 

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM   table [, table, ...]
[WHERE condition(s)]
[ORDER BY column(s) [DESC]];

SELECT table1.column, table2.column
FROM    table1
[CROSS JOIN table2];

1.3.b. JOIN - Természetes összekapcsolás

SELECT table1.column, table2.column
FROM    table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
  ON (table1.column_name = table2.column_name)]

1.3.c. Theta összekapcsolás (nem-ekvijoin)

1.3.d. Külső összekapcsolás
       -- a 9i előtti szintaxis (+)
       -- 9i utáni aktuális szintaxis

SELECT table1.column, table2.column
[LEFT|RIGHT|FULL OUTER JOIN table2
  ON (table1.column_name = table2.column_name)]

Segédanyag: >> FROM_lista példáit próbáljuk ki!
-- Ehhez előkészítésként a HR séma tábláihoz készítsük el a szinonimákat create_synonym
   
Táblák és feladatok: dolgozo_tabla.txt    Létrehozása:  create_dolgozo.txt

Feladatok összekapcsolásokra:
Direkt szorzat (CROSS JOIN)
- Kik azok a dolgozók, akiknek a főnöke KING? (FROM táblanevek_listája)

Természetes összekapcsolás  (NATURAL JOIN)
- Kik azok a dolgozók, akik osztályának telephelye BOSTON vagy CHICAGO?

Külső összekapcsolás (OUTER JOIN)
- Melyik osztálynak nincs dolgozója?

1.4. Halmazműveletek

rövidítés: SFW = SELECT ... FROM ... WHERE ..

1.4.a. Unió (csak unió-kompatibilis táblákra)

- duplikációk nélkül | illetve minden duplikációval

SFW
  union | union all
SFW

1.4.b. Különbség (csak unió-kompatibilis táblákra)

SFW
  minus
SFW

1.4.c. Metszet (származtatott művelet:
       (A metszet B) = A-(A-B) = stb.

SFW
  intersect
SFW

Segédanyag: >> Halmazműveletek példáit próbáljuk ki!
-- Ehhez előkészítésként a HR séma tábláihoz készítsük el a szinonimákat create_synonym
   
Táblák és feladatok: dolgozo_tabla.txt    Létrehozása:  create_dolgozo.txt

Feladatok halmazműveletekre
Különbség (MINUS)
- Kik azok a dolgozók, akik osztályának telephelye nem BOSTON vagy CHICAGO?
Unió (UNION)
- Adjuk meg azoknak a nevét, akiknek a fizetése > 2000 vagy a 30-as osztályon dolgoznak

P3. Relációs algebra további műveletei (Papíron)

Relációs algebra további műveletei:
Metszet, Descartes szorzat, theta-összekapcsolás, külső összekapcsolások, hányados.
Relációs algebrai kifejezések kiértékelése. Kifejezésfa. 

Segédanyag: Ullman-Widom 2.4.fejezet >> Tk1_24_RelAlgebra.pdf
 
Táblák és feladatok: szeret_tabla.txt       Létrehozása: create_szeret.txt

Feladatok relációs algebrában 
Hányados kifejezése SQL-ben
- A 2.gyakorlat szeret táblára vonatkozó relációs algebrai feladatait nézzük meg
  SQL SELECT-tel. Hányados kifejezése a relációs algebrai alapműveletekkel,
  majd ezt átírva SQL SELECT-re (két MINUS halmazművelet segítségével).
  (Megj. később majd mégnézzük alkérdésekkel is az SQL SELECT-ben)

______________________________________________________
 5. gyak. (III.12)  - SELECT --> Oracle függvények

Új anyag: az SQL SELECT lekérdezési lehetősége bővebb, mint
amit a relációs algebrában láttunk. Erre végezzünk feladatokat:
SELECT  WHERE feltétele, GROUP BY csoportosítás,
és FÜGGVÉNYEK. (Az alkérdések a köv.héten lesznek.)

1.5. Oracle függvények 

A leggyakrabban használt Oracle függvényeket, lásd sql_anyag.txt [5.] (elég sok függvény,
ezeket nem kell tudni, csak segédlettel alkalmazni kell tudni a gépes feladatok megoldásánál).

Segédanyag: >> Sorfüggvények példáit próbáljuk ki!
-- Ehhez előkészítésként a HR séma tábláihoz készítsük el a szinonimákat create_synonym
   
Táblák és feladatok: dolgozo_tabla.txt    Létrehozása:  create_dolgozo.txt

Feladatok az Oracle függvényekhez
Beépített függvények
- Adjuk meg azon dolgozókat, akik nevének második betűje 'A'.
- Adjuk meg azon dolgozókat, akik nevében van legalább két 'L' betű. (INSTR)
- Adjuk meg a dolgozók fizetéseinek négyzetgyökét és 3-mal vett maradékát.
- Adjuk meg, hogy hány napja dolgozik a cégnél ADAMS és milyen hónapban lépett be.

Összesítő (aggregátor) függvények
- Mekkora a minimális/maximális fizetés a dolgozók között?
- Kinek a legkisebb/legnagyobb a fizetése? (alkérdés)
- Mennyi a dolgozók összfizetése?
- Mennyi a 20-as osztályon az átlagfizetés?
- Adjuk meg, hogy hány különböző foglalkozás fordul elő a dolgozók között.

1.6. Csoportosítás 

SELECT column,] group_function(column), ...
FROM   table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Segédanyag: >> Csoportosítás példáit próbáljuk ki!
-- Ehhez előkészítésként a HR séma tábláihoz készítsük el a szinonimákat create_synonym
   
Táblák és feladatok: dolgozo_tabla.txt    Létrehozása:  create_dolgozo.txt

Feladatok a csoportosításhoz 
Csoportképzés (GROUP BY)
- Adjuk meg osztályonként az átlagfizetést (OAZON, ATL_FIZ).
- Adjuk meg osztályonként a telephelyet és az átlagfizetést (OAZON, TELEPHELY, ATL_FIZ).
- Adjuk meg, hogy az egyes osztályokon hány ember dolgozik (azt is, ahol 0=senki) (OAZON, DB).

Csoportok szűrése (HAVING)
- Adjuk meg azokra az osztályokra az átlagfizetést, ahol ez nagyobb mint 2000.

P4. Kiterjesztett műveletek a relációs algebrában (Papíron)

Relációs műveletek multihalmazokon. Kiterjesztett műveletek  a relációs algebrában.

Segédanyag: Ullman-Widom 5.1.-5.2.fejezet  >> Tk1_51_52_KitRelAlg.pdf
_______________________________________________________
 6. gyak. (III.19)  - SELECT --> Alkérdések

1.7. Alkérdések WHERE-ben és HAVING-ben
      (a) t in (SFW)
      (b) t theta (SFW)
      (c) t theta ANY/ALL(SFW)
      (d) EXISTS (SFW)

Segédanyag: >> Alkérdések I.  és Alkérdések II.  példáit próbáljuk ki!
-- Ehhez előkészítésként a HR séma tábláihoz készítsük el a szinonimákat create_synonym
   
Táblák és feladatok: szeret_tabla.txt       Létrehozása: create_szeret.txt
Táblák és feladatok: dolgozo_tabla.txt    Létrehozása:  create_dolgozo.txt

Feladatok az alkérdésekhez
Skalár alkérdések WHERE-ben
- Kik azok a dolgozók, akiknek a fizetése nagyobb, mint  'ALLEN' fizetése?
- Kik azok a dolgozók, és milyen munkakörben dolgoznak, akiknek a legkisebb
   a fizetésük? (ugyanez a kérdés a legnagyobb fizetésre is).

Skalár alkérdések HAVING-ben
- Osztályonként írjuk ki a minimális fizetést, de csak azokat az osztályokét, ahol
   a minimális fizetés nagyobb, mint a 30-as osztályon dolgozók minimális fizetése.

Többsoros alkérdések az ANY és ALL használata
- Kik azok a dolgozók, akiknek a fizetése kisebb, mint valamelyik hivatalnok ('CLERK')
   fizetése. (ugyanez a kérdés, csak most minden hivatalnok fizetésénél kisebb)

Alkérdések IN-el beágyazva
-Kik azok a dolgozók, akiknek ugyanaz a főnökük és ugyanazon az osztályon dolgoznak,  
  mint 'MARTIN'?

EXISTS és NOT EXISTS alkérdések
Korrelált alkérdések
- Melyek azok az osztályok, ahol nem dolgozik senki (most NOT EXISTS-el)

Hányados kifejezése SQL-ben
- A 2.gyakorlat szeret táblára vonatkozó relációs algebrai feladatait nézzük meg
  SQL SELECT-tel. Hányados kifejezése a relációs algebrát átírva (két MINUS)
  illetve két egymásba ágyazott korrelált NOT EXITST alkérdéssel is írjuk fel!

1.8. Alkérdések FROM-ban és a WITH utasítás az Oracle-ben

Ez csak később lesz, II.Témakörben majd a táblák, nézettábláknál létrehozásánál.

1.9. Hierarchikus lekérdezések az Oracle-ben (CONNECT BY PRIOR)
   Családfák. SFW start with ... connect by ...

SELECT [LEVEL], column, expr...
FROM   table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;

ahol WHERE condition:
       expr comparison_operator expr

Fa bejárása
Kiindulás: START WITH column1 = value
CONNECT BY PRIOR column1 = column2
  Felülről-lefelé: Column1 = Parent Key
                   Column2 = Child Key
  Alulról-felfelé: Column1 = Child Key
                   Column2 = Parent Key

Segédanyag: >> Hierarchikus.pdf  példáit próbáljuk ki!
-- Ehhez előkészítésként a HR séma tábláihoz készítsük el a szinonimákat create_synonym
   
Táblák és feladatok: dolgozo_tabla.txt    Létrehozása:  create_dolgozo.txt

Feladatok a hierarchikus lekérdezésekhez
-  Listázzuk ki az dolgozo tábla alapján a főnökökhöz tartozó beosztottak nevét és osztályukat.
   a.) A dolgozo tábla önamagára való hivatkozásával (többtáblás lekérdezés sorváltozókkal).
   b.) A CONNECT BY utasításrész használatával, a hierarchikus szerkezetet 'KING'-től
         felülről lefelé bejárva.
   c.)  Alulról felfelé járjuk be a hierarchikus szerkezet egy ágát 'SMITH'-től kezdve.

P5. Rekurzió az SQL99-ben (Papíros feladat)

Segédanyag:
>> Tk1./10.2.fejezet: Tk1_102_Rekurzio.pdf

77rek_02
-- Tegyük fel, hogy az alábbi reláció
                     Járatok(légitársaság, honnan, hova, indulás, érkezés)
    azt tartalmazza, hogy melyik légitársaság melyik városból melyik városba mikor indít járatokat.
-- Mely (x, y) várospárokra lehetséges egy vagy több átszállással eljutni x városból y városba?
    Tegyük fel, hogy nemcsak az érdekel minket, hogy el tudunk-e jutni az egyik városból a másikba,
    hanem az is, hogy utazásunk során az átszállások is ésszerűek legyenek, vagyis ha több járattal
    utazunk és átszállásra van szükségünk, akkor az érkező járatnak legalább egy órával a rá következő
    indulás előtt meg kell érkeznie. Feltehetjük, hogy nincs egy napnál hosszabb utazás!
    a.) Írjuk fel erre a rekurzív Datalog szabályokat!
    b.) Írjuk át ezt a rekurziót az SQL-99 szabvány szerint! (Ezt is papíron!)
_______________________________________________________

KÖVETKEZŐ HÉTEN ZH!  lásd Előzetes információ az I.Zh-ról

További lehetőségek a gyakorlásra: lásd  Órai mintapélda adatbázissémák
Ullman-Widom: Adatbázisrendszerek. Alapvetés. Második átdolg.kiad., 2009 
-- Táblák és feladatok: termekek_tabla.txt    Létrehozása: create_termekek.txt
-- Táblák és feladatok: csatahajok_tabla.txt    Létrehozása: create_csatahajok.txt
Kinde-Nagy: Oracle példatár feladatai: Feladatok.pdf
-- Ehhez a dept és emp táblák közvetlen létrehozása: cr_dept_emp.sql 
-- A dept, emp, salgrade, dummy, customer, ord, item, product, price táblák
    és a sales nézettábla létrehozása: cr_dept_stb_sales.sql
WWW: lásd Ajánlott irodalom és hasznos linkek
-- SQL feladatok, megoldások: http://sqlzoo.net/
-- SQL Tutorial: http://www.sql-tutorial.net/

Lap tetejére        Gyak oldalára        Vissza a Kezdőlapra