I. Témakör: Lekérdezések kifejezése
relációs algebrában és SQL-ben (SELECT)

1. gyak. - Természetes lekérdezések kifejezése táblákkal
>> ...     
2. gyak. - Relációs modell és relációs algebra alapműveletei
>> P1. Relációs modell és a relációs algebra alapműveletei
>> Bevezetés. Egyszerű lekérdezések az SQL-ben
 
3. gyak. - Egy táblára vonatkozó lekérdezések az SQL-ben
>> 1.1. Vetítés (SELECT lista, DISTINCT)  
>> 1.2. Kiválasztás (WHERE feltétel) és
             a kiválasztott sorok rendezése (ORDER BY)

4. gyak. - Több táblára vonatkozó lekérdezések az SQL-ben
>> P2. Relációkra vonatkozó megszorítások     
>> 2.1. Relációsémák és megszorítások definiálása SQL-ben (create table)
>> 1.3A. Összekapcsolások az SQL-ben
>> 1.3B. Külső összekapcsolás az SQL-ben
>> 1.4. Halmazműveletek, UNION, INTERSECT, MINUS
 
5. gyak. - Alkérdések és korrelált alkérdések az Oracle-ben
>> P3. Relációs algebra, mint lekérdező nyelv,
            relációs algebra további műveletei, pl. hányados
>> 1.5A. Alkérdések a WHERE-ben  
>> 1.5B. Alkérdések a WHERE-ben és HAVING-ben  
>> 1.5C. Korrelált alkérdések. Hányados kifejezése SQL-ben

6. gyak. - Csoportosítás, kiterjeszett rel.alg. és rekurzió
>> 1.6. Függvények 
>> 1.7. Csoportosítás, a GROUP BY és HAVING záradék
>> P4. Kiterjesztett műveletek a relációs algebrában 
>> 1.9. Hierarchikus lekérdezések az Oracle-ben 
>> P5. Rekurzió az SQL99-ben
  
I.ZH (7.gyak)
    
_____________________________________________________________
Segédanyag, az előadások példái:
-- Előadás tematikája és segédanyagok menüpont 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. - Relációs modell és relációs algebra alapműveletei
 
>> P1. Relációs modell és a relációs algebra alapműveletei
>> Bevezetés. Egyszerű lekérdezések az SQL-ben

P1. Relációs modell és a relációs algebra alapműveletei
 
Relációs modell
[Tk1] 2.2. A relációs modell alapjai (22-29.o.)
>> Tk1_21_22_RelModell.pdf  
 
Relációs algebra alapműveletei: vetítés, kiválasztás, unió,
különbség, természetes összekapcsolás, átnevezés
[Tk1] 2.4. A relációs algebra alapműveletei (39-45.o.)
>> Tk1_24_RelAlgebra.pdf      

Táblák és feladatok: szeret_tabla.txt
 
Egyszerű lekérdezések az SQL-ben
>> 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!

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.

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?
_______________________________________________________
2. gyak. - Egy táblára vonatkozó lekérdezések az SQL-ben
 
Egy táblára vonatkozó lekérdezések az SQL-ben.
SELECT lista, oszlopok, számított oszlopok (kifejezések),
>> 1.1. Vetítés (SELECT lista, DISTINCT)  
WHERE feltétel, összehasonlító feltételek, BETWEEN AND,
IN (halmaz), LIKE, IS NULL, összetett keresési feltételek.
ORDER BY lekérdezett sorok rendezése.
>> 1.2. Kiválasztás (WHERE feltétel) és
      a kiválasztott sorok rendezése (ORDER BY)

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
   
1.2.Kiválasztás, szelekció

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

1.2.1. egyszerű értékvizsgálat

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

1.2.2. egyszerű értékek listája

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

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

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

1.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, akik a 10-es vagy a 20-as osztályon dolgoznak?
   (másik megoldással is: IN (halmaz) )
- 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)

_______________________________________________________
3. gyak. - Több táblára vonatkozó lekérdezések az SQL-ben
 
>> P2. Relációkra vonatkozó megszorítások     
>> 2.1. Relációsémák és megszorítások definiálása SQL-ben (create table)
>> 1.3A. Összekapcsolások az SQL-ben
>> 1.3B. Külső összekapcsolás az SQL-ben
>> 1.4. Halmazműveletek 
  
P2. Relációkra vonatkozó megszorítások
 
Integritási megszorítások a rel.modellben, relációs adatbázis sémák.
Kulcs, egyed- és hivatkozási integritási megszorítások.
[1UW] 2.5. Relációkra vonatkozó megszorítások  (61-68.o.)
>> 1UW02_RelConstraints.pdf 

Relációsémák és megszorítások megadása (create table)
[1UW] 2.3. Relációsémák definiálása SQL-ben (30-39.o.)
>> SQL_21_Tablak.pdf    
  
1.3A. Összekapcsolások az SQL-ben

1.3.1. 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.2. 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.3. Theta összekapcsolás (nem-ekvijoin)

1.3.4. 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.3B Külső összekapcsolás az SQL-ben

Néhány specialitás, amikről érdemes egy-két szót ejteni:
>> A DUAL tábla
>> Külső join  művelet
 
1.4. Halmazműveletek

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

1.4.1. 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.2. Különbség (csak unió-kompatibilis táblákra)

SFW
  minus
SFW

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

_______________________________________________________
4. gyak. - Alkérdések az Oracle-ben

>> P3. Relációs algebra, mint lekérdező nyelv,
      relációs algebra további műveletei, pl. hányados
>> 1.5. Alkérdések a WHERE-ben
>> 1.5B. Alkérdések a WHERE-ben és HAVING-ben  
>> 1.5C. Korrelált alkérdések. Hányados kifejezése SQL-ben
 
 P3. Relációs algebra további műveletei (Papíron)

Segédanyag:   1UW02_RelAlg_06SQL  
Relációs algebra további műveletei, mint pl. hányados.
Relációs algebrai kifejezések kiértékelése. Kifejezésfa. 
 
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 szeret táblára vonatkozó relációs algebrai feladatait nézzük meg  algebrában
  és 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)

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

Segédanyag: >> Alkérdések I.  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?

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'?

 
1.5B. 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, é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'?

1.5C. Korrelált alkérdések. Hányados kifejezése NOT EXISTS-el
      
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.5D. (2.5) Alkérdések FROM-ban. WITH utasítás

II.Témakörben a nézettáblák létrehozásánál és használatnál található (INLINE nézet)


______________________________________________________
5. gyak. - Függvények és csoportosítás az Oracle-ben
 
>> 1.6. Függvények 
>> 1.7. Csoportosítás 
>> P4. Kiterjesztett műveletek a relációs algebrában 
 
Ú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, HAVING,
és FÜGGVÉNYEK. (Az alkérdésekkel együtt a köv.héten lesznek.)

1.6. Oracle függvények 

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.7. Csoportosítás és összesítések az SQL-ben

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  >> 1UW05_KitRelAlg.pdf
_______________________________________________________
6. gyak. - Hierarchikus lekérdezések az Oracle-ben
 
>> 1.9. Hierarchikus lekérdezések az Oracle-ben 
>> P5. Rekurzió az SQL99-ben

 
1.9. Hierarchikus lekérdezések az Oracle-ben (CONNECT BY PRIOR)
      Családfák. SFW START WITH ... CONNECT BY PRIOR ...
 
Segédanyag:
>> Nikovits Tibor: hiera_lekerd.txt
>> 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
 
Feladatok a hierarchikus lekérdezésekhez
19hier_01
    - Táblák:  dolgozo_tabla.txt  Létrehozása:  create_dolgozo.txt
    - 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.

19hier_02
     - Tranzitív lezárt kifejezése PL/SQL programmal
        Az alábbi feladat a tankönyv (Ullman-Widom kék könyv) 10.2 szakaszára épül.
        JARATOK(legitarsasag, honnan, hova, koltseg, indulas, erkezes)
        táblában repülőjáratok adatait tároljuk.
        - Készítsetek ebből saját példányt: Jaratok_tabla.sql és az alapján dolgozzatok!
        - Mely városokba lehet eljutni Dallasból? ('DAL'='Dallas', hierarchikus lekérdezéssel)
        - Átszállásokkal mely városokba lehet eljutni San Franciscoból? ('SF'='San Francisco')
        - Ez a lekérdezés addig működik, amíg nincs a gráfban kör. Szúrjatok be a saját
           táblátokba még egy sort, ami után már irányított kör is lesz a táblában:
                 INSERT INTO jaratok VALUES('LH', 'CHI', 'DEN', 2000, 1900, 2100);
           (Ezután már nem működik a START WITH, CONNECT BY típusú lekérdezés)
       
P5. Rekurzió az SQL99-ben (Papíros feladat)

Segédanyag:
>> Tk1./10.2.fejezet: 1UW_10.2_Rekurzio.pdf

Feladat:
-- Tegyük fel, hogy az alábbi reláció
                     Járatok(légitársaság, honnan, hova, költség, 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ÖV.HÉTEN I.ZH (7.gyak)
_______________________________________________________

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