CREATE VIEW - Egyszerű
és
összetett
nézettáblák
létrehozása és használata.
Kipróbálni. Mikor lehetséges az
alaptáblák karbantartása a
nézeteken
keresztül?
További részletek, lásd SQL
Language Reference -> Nézettáblák
létrehozása
Feladatok: 1.feladat:
(Nikovits Tibor) A lekérdezésekhez NIKOVITS
felhasználó
tulajdonában levő táblákat
használjuk!
Elérése: tulajdonos.táblanév
-- Melyik piros színű cikket szállítják a
legtöbb projekthez?
A lekérdezést adjuk meg
nézettáblák
létrehozásával valamint úgy
is, hogy
ne kelljen nézetet létrehozni, a
"nézet" csak a lekérdezés
idejére jön létre:
adjuk meg a lekérdezést INLINE
nézettel, majd ugyanez WITH-del is.
Megoldása:
Nezet_feladatok.txt
(WITH munkatáblák
használatára is
példa) Figyelem! Ez az Oracle WITH
utasítás nem ugyanaz, mint az
előadáson
és a kék-Tk 10.2
fejezetében szereplő WITH RECURSIVE
utasítás!
Rekurzió az SQL-ben feladatokra is
fogunk nézni a gyakorlaton példákat!
Képezzük osztályonként az
összfizetést, vegyük ezen
számok átlagát, és adjuk
meg, hogy mely osztályokon nagyobb ennél az
átlagnál az összfizetés.
További
gyakorló feladatok:
lásd Példatár
"6.fejezet. Nézettáblák" feladatai
valamint "3.fejezet. Allekérdezések a FROM
utasításrészben" feladatai illetve
összetett feladatai és a
"7.fejezet. Összetett
SQL-feladatsorok" feladatai
2.5.
Felső-N
elemzés ROWNUM segítségével
Felső-N elemzés
(például a ZH-n is hasznos,
amikor túl nagy az eredménytábla
és csak az első 5 sorát kell beküldeni).
a felső 5
sor
kiíratása az alábbi módon:
SELECT *
FROM
(SELECT... FROM ...WHERE...
ORDER BY
<oszlop> DESC)
WHERE
ROWNUM <= 5
-----
További részletek az Oracle
11gR2 doksiban SQL
Language Reference.11.2.pdf
2 Pseudocolumns fejezetben (44/1508) ROWNUM Pseudocolumn
Innen: For example, the following query returns the employees with the 10 smallest
employee numbers. This is sometimes referred to as top-N reporting:
SELECT *
FROM
(SELECT * FROM employees
ORDER BY employee_id)
WHERE ROWNUM < 11;
ROWNUM - pszeudooszlop a kiválasztásra
kerülő
sorokhoz hozzárendeli
a kiválasztásuk sorszámát,
de a WHERE
feltételben nem állhat fent egyenlőség
(ez nem sorszám, hanem a lekérdezés
eredményének a sorrendjét adja meg).
WHERE feltételben ROWNUM < N illetve ROWNUM <=
N
használható!
de WHERE-ben ROWNUM > N ill. ROWNUM = N
(hibás! nem
ad vissza sort)
-----
ÚJ!!! Oracle 12c doksiban SQL
Language Reference.12.1.pdf
Innen: Row Limiting: Examples The following statement returns
the 5 employees with the lowest employee_id values:
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY; --- Oracle 11gR2-ben még nincs!
----- Megj:Példatár 6.fejezet
elméleti összefoglalóban szerepel:
"felső-N
analízis"
2.6.
Hierarchikus
lekérdezések az Oracle-ben (CONNECT
BY)
Családfák. SFW
START WITH
... CONNECT BY PRIOR ...
Feladatok a hierarchikus lekérdezésekhez
- Táblák és feladatok: dolgozo_tabla.txt
Ehhez a táblák
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.
-----
További részletek az Oracle
11gR2 doksiban SQL
Language Reference.11.2.pdf
Innen: Chapter 9 - SQL Queries and Subqueries
Hierarchical Queries 9-3
Hierarchical Query Examples 9-5
Megj:Példatár 3.fejezet
elméleti összefoglalóban "Hierarchikus
adatszerkezet
megjelenítése" 2.7.
Eljut feladat, Rekurzió az SQL szabványban (WITH RECURSIVE)
Tankönyvben:
- Rekurzió: [Ullman-Widom]
10.2.
Rekurzió az SQL-ben (466-474.o.)
- AB1EA: LEK6 - with
recursive Tk.10.2. Rekurzió az SQL-ben (Az "Eljut"-feladat)
2.7.1. AZ "ELJUT"-FELADAT
- Az
alábbi feladat a
tankönyv (Ullman-Widom kék
könyv) 10.2 szakaszára épül.
- Az Eljut-feladat: Jaratok(legitarsasag,
honnan, hova, koltseg, indulas, erkezes)
táblában repülőjáratok
adatait
tároljuk. Azt keressük, hogy Dallasból
mely
városokba
tudunk eljutni (átszállás
nélkül közvetlenül vagy egy vagy
több átszállással).
-
Készítsünk ebből
saját példányt: jaratok_tabla.txt
és az
alapján
dolgozzunk
DROP TABLE jaratok;
CREATE TABLE jaratok(
legitarsasag CHAR(2),
honnan VARCHAR2(10),
hova VARCHAR2(10),
koltseg NUMBER,
felszallas NUMBER,
erkezes NUMBER);
INSERT INTO jaratok VALUES('UA', 'SF','DEN', 1000, 930,1230);
INSERT INTO jaratok VALUES('AA', 'SF','DAL', 10000, 900,1430);
INSERT INTO jaratok VALUES('UA','DEN','CHI', 500, 1500,1800);
INSERT INTO jaratok VALUES('AA','DEN','DAL', 2000, 1400,1700);
INSERT INTO jaratok VALUES('AA','DAL','CHI', 600, 1530,1730);
INSERT INTO jaratok VALUES('AA','DAL', 'NY', 2000, 1500,1930);
INSERT INTO jaratok VALUES('AA','CHI', 'NY', 3000, 1900,2200);
INSERT INTO jaratok VALUES('UA','CHI', 'NY', 2000, 1830,2130);
- Az alapfeladat, hogy adjuk meg mely (x,
y) várospárokra
lehetséges
egy vagy több
átszállással
eljutni x városból y városba? Ezt
egy relációs algebrai
kifejezésként
nem tudjuk megadni zárt alakban,
klasszikus SQL
SELECT utasítással sem tudjuk
kifejezni, csak azt tudjuk, hogy
átszállás
nélkül, egy
átszállással, két
átszállással, stb...
select distinct honnan, hova
from
jaratok
union
select j1.honnan, j2.hova
from jaratok j1, jaratok j2
where j1.hova=j2.honnan
union
select j1.honnan, j3.hova
from jaratok j1, jaratok j2,
jaratok j3
where j1.hova=j2.honnan
and
j2.hova=j3.honnan
-- union stb... de ez nem rel.alg.kif.
- Viszont ezt ciklussal a PL/SQL-ben könnyen meg tudjuk majd valósítani, ennek
a feladatnak több változatát is meg fogjuk majd nézni PL/SQL programmal: Rek1.feladat:
- Mely (x, y)
várospárokra lehetséges
egy vagy több átszállással
eljutni x városból y városba? Rek2.feladat:
- Mely (x,y)
város
párokra hány
átszállással és
milyen költségekkel
lehetséges egy vagy
több
átszállással eljutni x
városból y városba? Rek3.feladat:
- Tegyük fel, hogy nemcsak
az
érdekel, 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.
(Tegyük fel, hogy nincs
egy
napnál hosszabb utazás) Rek4.feladat:
- A fenti feladatokat oldjuk meg PL/SQL-ben úgy is, hogy ne
csak a várospárokat,
hanem a teljes útvonalat is
listázzuk ki.
2.7.2. Rekurzió az SQL szabványban (WITH RECURSIVE)
- (Papíros feladat) Fejezzük ki az SQL3
szabványban szereplő WITH RECURSIVE
utasítással, hogy mely (x,y)
város
párokra lehetséges
közvetlenül, egy vagy
több
átszállással eljutni
x
városból y városba? (Csak
papíron! Oracle nem támogatja).
- Megoldás:
WITH RECURSIVE eljut AS
(SELECT honnan, hova FROM jaratok
UNION
SELECT eljut.honnan, jaratok.hova
FROM eljut, jaratok
WHERE eljut.hova = jaratok.honnan)
SELECT hova FROM eljut WHERE honnan='DAL';
- Fontos: UNION (halmaz)
és UNION
ALL (multihalmaz) közötti
különbség!!!
2.8.
Az Eljut feladat Oracle
CONNECT BY hierarchikus lekérdezéssel
- Az Eljut-feladat: Jaratok(legitarsasag,
honnan, hova, koltseg, indulas, erkezes)
táblában repülőjáratok
adatait
tároljuk. Azt keressük, hogy Dallasból
mely
városokba
tudunk eljutni (átszállás
nélkül közvetlenül vagy egy vagy
több átszállással).
-
Készítsünk ebből
saját példányt: jaratok_tabla.txt
és az
alapján
dolgozzunk
(az első lépésben ne legyen kör a gráfban, hierarchikus szerkezetű legyen)
- Oracle hierarchikus lekérdezések
CONNECT BY PRIOR,
Amennyiben azt
szeretnénk
megtudni, hogy mely
városokba lehet eljutni Dallasból
ezt a következő
hierarchikus lekérdezéssel kapjuk meg
('DAL'='Dallas')
SELECT DISTINCT hova
FROM
jaratok
WHERE
HOVA <> 'DAL'
START WITH honnan = 'DAL'
CONNECT BY PRIOR hova = honnan;
- Most szúrjunk be 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);
Ekkor a fenti hierarchikus
lekérdezés nem
működik, viszont NOCYCLE-lel igen:
SELECT DISTINCT hova
FROM
jaratok
WHERE
HOVA <> 'DAL'
START WITH honnan = 'DAL'
CONNECT BY NOCYCLE PRIOR hova = honnan;
-
Átszállásokkal mely
városokba lehet eljutni
San Franciscoból ('SF'='San Francisco')
SELECT LPAD(' ', 4*level)
||honnan, hova,
level-1 Atszallasok
FROM jaratok
WHERE
HOVA <> 'SF'
START WITH honnan = 'SF'
CONNECT BY NOCYCLE PRIOR hova =
honnan;
- A hierarchikus lekérdezésben további
pszeudo
oszlopokat is használhatunk, amint
azt az alábbi
példában
láthatjuk az útvonal
megadását
SELECT
hova,
sys_connect_by_path(honnan||'->'||hova, '/'),
connect_by_isleaf, connect_by_iscycle
FROM
jaratok
START
WITH honnan = 'SF'
CONNECT BY NOCYCLE PRIOR hova
= honnan;
2.9.
Az Eljut feladat Oracle 11gR2-ben lekérdezéssel
WITH
utasítással
-- Oracle 11gR2 megoldás:
with eljut (honnan, hova) as
(select honnan, hova from jaratok
union all
select jaratok.honnan, eljut.hova
from jaratok, eljut
where jaratok.hova=eljut.honnan
)
SEARCH DEPTH FIRST BY honnan SET SORTING
CYCLE honnan SET is_cycle TO 1 DEFAULT 0
select distinct honnan, hova from eljut order by honnan;
-----
További részletek az Oracle
11gR2 doksiban SQL
Language Reference.11.2.pdf
Innen 19-36 Subquery Factoring: Example
The following statement creates the query names
dept_costs and avg_cost
for the initial query block containing a join, and then uses
the query names
in the body of the main query.
WITH
dept_costs AS
(
SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS
(
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total > (SELECT avg FROM avg_cost)
ORDER BY department_name;
Előző 7.gyak
Következő 9.gyak