DevGym_02_Database_Next_Level

Question 1

Was ist Null?

Answer 1

Null ist weder gleich noch ungleich einer Sache. Es ist unbekannt.

Question 2

Wie kann ich die NULL Zeilen herausfinden?

Answer 2

Mit is null

select * from toys where Bla is null;

Question 3

Wie muss ich diese Abfrage anpassen, damit auch die NULL Zeilen gefunden werden?

select * from toys where volume_of_wood < 15;

Answer 3

select *

from toys

where volume_of_wood < 15 or volume_of_wood is null;

Question 4

Mit welchen Funktionen kann ORACLE bei der Behandlung von Nulls helfen?

Answer 4

• NVL
  
  • Diese Funktion benötigt zwei Argumente. Wenn das erste null ist, wird das zweite zurückgegeben:
• COALESCE
  
  • This is like NVL. But it can take any number of arguments. It returns the first non-null value it finds.
  • SELECT COALESCE(NULL,’A’,’B’) result FROM dual;
  • Result: A

Question 5

Was sind “magic values”?

Answer 5

Null verkompliziert Ihre Where-Klausel. Daher sind manche Leute geneigt, “ magic values” anstelle von null für fehlende oder nicht zutreffende Informationen zu verwenden.

Herr Pinguin ist zum Beispiel aus Plüsch gemacht, nicht aus Holz! Um also zu zeigen, dass volume_of_wood nicht zutrifft, könnten Sie diesen Wert auf den “unmöglichen” Wert von minus eins setzen.

update toys set volume_of_wood = -1wherevolume_of_woodisnull;

Auf den ersten Blick scheint dies Ihren Code zu vereinfachen. Aber es bringt Komplikationen an anderer Stelle. Denn dadurch funktioniert z.B. die Summenbildung nicht mehr korrekt, da die NULL-Werte mit den Minuswerten das Ergebnis verzerren.

Question 6

Welche der folgenden Gründe sprechen für die Verwendung von “magischen Werten” (wie -1) anstelle von Null für fehlende oder nicht zutreffende Informationen?

Sie ermöglichen Ihnen die Verwendung von standardmäßiger Zwei-Werte-Logik in Ihrer Where-Klausel

Answer 6

Ja.

Wenn eine Spalte Nullwerte enthalten kann, müssen Sie “ is null”-Bedingungen einfügen oder Funktionen wie NVL oder coalesce verwenden, um korrekte Ergebnisse zu gewährleisten. Wenn Sie magische Werte verwenden, können Sie Standard-Vergleichsbedingungen (=, , between, etc.) benutzen, um die korrekte Antwort zu erhalten.

Question 7

Korreliert vs. Unkorreliert

Wie verhält sich das bei IN und EXISTS?

Answer 7

Eine Unterabfrage ist korreliert, wenn sie mit einer Tabelle der übergeordneten Abfrage verknüpft ist. Ist dies nicht der Fall, ist sie unkorreliert.

Dies führt zu einem Unterschied zwischen IN und EXISTS. EXISTS gibt Zeilen aus der übergeordneten Abfrage zurück, sofern die Unterabfrage mindestens eine Zeile findet. Das folgende unkorrelierte EXISTS gibt also alle Zeilen in colours zurück:

• select * from colours*
• where exists (*
• select null from bricks);*

Hinweis: Bei der Verwendung von EXISTS spielt es keine Rolle, was Sie in der Unterabfrage auswählen, da nur geprüft wird, ob eine Zeile vorhanden ist, die mit der Where-Klausel übereinstimmt (falls es eine gibt). Sie können “select null” oder “select 1” oder eine aktuelle Spalte auswählen.

Dies liegt daran, dass die Abfrage bedeutet: Gib alle Farben zurück, wenn es mindestens eine Zeile in Bricks gibt.

Um alle Farbzeilen zu finden, die mindestens eine Zeile in Bricks der gleichen Farbe haben, müssen Sie in der Subquery verknüpfen. Normalerweise müssen Sie EXISTS-Unterabfragen mit einer Tabelle in der übergeordneten Tabelle korrelieren.

Question 8

Was ist die EXISTS-Klausel?

Answer 8

• Die SQL EXISTS-Klausel wird in Kombination mit einer Unterabfrage verwendet und gilt als erfüllt, wenn die Unterabfrage mindestens eine Zeile zurückgibt.
• Eine EXISTS-Bedingung testet auf das Vorhandensein von Zeilen in einer Unterabfrage.
• Sie kann in einer SELECT-, INSERT-, UPDATE- oder DELETE-Anweisung verwendet werden.
• WHERE EXISTS ( subquery );

Question 9

Worauf muss ich bei NOT IN achten?

Answer 9

Sie können die Umkehrung von IN & EXISTS durchführen, indem Sie ihnen NOT voranstellen. Dadurch erhalten Sie alle Zeilen aus der übergeordneten Abfrage, die keine Übereinstimmung in der Unterabfrage haben.

Damit die NOT IN-Bedingung wahr ist, muss der Vergleich aller Elemente mit der übergeordneten Tabelle false ergeben.

Aber denken Sie daran, dass ein Vergleich mit NULL ein unbekanntes Ergebnis liefert! Der gesamte Ausdruck ist also unbekannt und Sie erhalten keine Daten.

Um dieses Problem zu lösen, verwenden Sie entweder NOT EXISTS oder fügen Sie eine Where-Klausel hinzu, damit die Unterabfrage keine Nullwerte zurückgibt:

select * from farben c
where c.colour_name not in (
select b.color from ziegelsteine b
where b.Farbe is not null
);

Question 10

Was sind CTE?

Answer 10

Mit Common Table Expressions (CTEs) können Sie Unterabfragen mit einem Namen versehen. Auf diese können Sie dann wie auf normale Tabellen an anderer Stelle in Ihrer Abfrage verweisen. Dadurch wird Ihre SQL-Abfrage später einfacher zu schreiben und zu verstehen.

CTEs stehen in der with-Klausel über der select-Anweisung. Im Folgenden wird eine CTE definiert, die zählt, wie viele Zeilen jeder Farbe sich in der Bricks-Tabelle befinden:

with brick_colour_counts as

( select colour, count(*) from bricks group by colour )

select *

from brick_colour_counts ;

Question 11

Oracle Database unterstützt viele verschiedene Arten von Subqueries. Dazu gehören:

Answer 11

• Inline views
• Nested views
• Subquery factoring aka common table expressions or the with clause.
• Scalar subqueries

Question 12

Was ist eine Inline-View?

Answer 12

Eine Inline-View wird in die from-Klausel Ihrer Anweisung eingefügt. Sie ersetzt eine reguläre Datenbanktabelle. Der fettgedruckte Abschnitt unten ist zum Beispiel eine Inline-View:

select * from (

select * from subquery

) inline_view

Question 13

Nested Queries (verschachtelte Unterabfrage)

Answer 13

Eine verschachtelte Unterabfrage wird in die Where-Klausel einer Anweisung eingefügt. Damit können Sie Zeilen auf der Grundlage der Ausgabe einer anderen Abfrage filtern.

Sie können dies entweder mit IN oder EXISTS tun. Bei beiden kann die verschachtelte Unterabfrage korreliert oder unkorreliert sein.

IN

Diese vergleicht eine Spalte in der übergeordneten Tabelle mit einem Wert aus der Unterabfrage. Zum Beispiel:

select * from parent_tab pt where pt.col in ( select ins.col from in_subquery ins )

Exists

Dies ist der Fall, wenn die Unterabfrage mindestens eine Zeile zurückgibt. Sie hat die Form:

select * from parent_tab pt where exists ( select null from exists_subquery us )

Uncorrelated Subquery

Eine unkorrelierte Unterabfrage hat keine Verknüpfung mit der übergeordneten Tabelle:

select * from parent_tab pt where pt.col in ( select us.col from uncorrelated_subquery us )

Correlated Subquery

Eine korrelierte Unterabfrage führt eine Verknüpfung mit der übergeordneten Tabelle durch.

select * from parent_tab pt where pt.col in ( select cs.col from correlated_subquery cs where cs.col2 = pt.col2 )

Question 14

IN und EXISTS, kurze Zusammenfassung, darauf muss ich achten.

Answer 14

Sie können verschachtelte Unterabfragen mit IN oder EXISTS schreiben.

IN vergleicht Werte aus der übergeordneten Abfrage mit denen, die von der Unterabfrage zurückgegeben werden. EXISTS ist wahr, wenn die Unterabfrage mindestens eine Zeile zurückgibt.

Sie können diese mit NOT negieren. Sie müssen jedoch aufpassen, wenn Sie NOT IN verwenden, weil die Unterabfrage Nullwerte zurückgeben kann. Wenn dies der Fall ist, ist der gesamte Ausdruck unbekannt. Die Abfrage schließt also die passenden übergeordneten Zeilen aus.

Question 15

Wie ich kann mit order by eine bestimmte Reihenfolge gewährleisten?

Answer 15

Es gibt zwei Spielzeuge zum Preis von 9,99 (Miss Snuggles & Blauer Dinosaurier) und zwei zu 29,99 (Känguru & Lila Ninja). Wenn Sie nur nach dem Preis sortieren, kann die Datenbank jedes dieser Produkte in beliebiger Reihenfolge zurückgeben.

Um dies zu vermeiden und eine bestimmte Reihenfolge zu gewährleisten, fügen Sie weitere Spalten zu Ihrer Sortierung nach hinzu. Tun Sie dies, bis jeder Satz von Werten in nur einer Zeile erscheint.

Sie können dies hier tun, indem Sie toy_name hinzufügen:

select toy_name, price from toys order by price, toy_name;

Question 16

Wie wird NULL bei der Sortierung behandelt?

Answer 16

In Oracle Datenbanken kommt NULL standardmäßig am Schluss.

Das kann geändert werden mit NULLS FIRST.

select * from toys order by last_lost_date nulls first;

Question 17

Wie kann eine benutzerdefinierte Sortierung vorgegeben werden?

z.B. soll Miss Snuggles immer ganz oben erscheinen.

Answer 17

Manchmal möchten Sie Daten auf eine Weise ordnen, die nicht den Standardsortierregeln entspricht. Nehmen wir zum Beispiel an, Sie möchten die Spielzeuge nach Namen sortieren. Da Miss Snuggles aber Ihr Lieblingsspielzeug ist, soll es immer ganz oben erscheinen. Alle weiteren Spielzeuge sollen in alphabetischer Reihenfolge erscheinen.

Dazu müssen Sie Miss Snuggles einen Wert zuweisen, der niedriger ist als der aller anderen Spielzeuge. Zum Beispiel: 1 für Miss Snuggles und 2 für alles andere.

Sie können dies mit einem Case-Ausdruck in Ihrem order by tun. Zum Beispiel:

select * from toys

order by

case when toy_name = ‘Miss Snuggles’ then 1 else 2 end, toy_name;

Question 18

Eine Top-N-Abfrage gibt die ersten N Zeilen in einem sortierten Datensatz zurück. Zum Beispiel, um die drei billigsten Spielzeuge zu finden.

Welche Möglichkeiten gibt es?

Answer 18

• Rownum
• Row_number
• Fetch first

Question 19

Was macht Rownum?

Answer 19

Rownum ist eine Oracle-spezifische Funktion. Sie weist jeder Zeile, die Sie abrufen, eine aufsteigende Zahl zu.

Wenn Sie diese Funktion jedoch in einer Where-Klausel vor dem Order By verwenden, erhalten Sie unerwartete Ergebnisse. Im Folgenden wird zum Beispiel versucht, die drei teuersten Spielzeuge zu ermitteln:

select * from toys

where rownum <= 3

order by price desc;

Aber das billigste, Baby Turtle, ist auch dabei! Das liegt daran, dass die Datenbank order by nach where verarbeitet. Es werden also drei beliebige Zeilen ermittelt. Dann werden sie sortiert.

Um dies zu beheben, sortieren Sie die Daten in einer Unterabfrage. Filtern Sie dann die Ergebnisse dieser Abfrage:

select * from

( select * from toys t order by price desc )

where rownum <= 3;

Question 20

Was ist Row_number?

Answer 20

Row_number ist eine analytische Funktion. Wie rownum weist sie einen inkrementierenden Zähler zu. Dieser wird durch die Sortierung bestimmt, die in der order by-Klausel der over-Klausel definiert ist.

Um diese Funktion in einer Top-N-Abfrage zu verwenden, müssen Sie auch eine Unterabfrage verwenden:

select * from

( select t.*, row_number() over (order by price desc) rn from toys t )

where rn <= 3 order by rn;

Question 21

Was ist Fetch first?

Answer 21

Mit Oracle Database 12c wurde die ANSI-konforme fetch first-Klausel eingeführt. Diese Klausel folgt auf die order by-Klausel und macht die Verwendung einer Unterabfrage überflüssig:

select * from toys

order by price desc

fetch first 3 rows only;

Question 22

Bei einer Top-N-Abfrage, die nach nicht eindeutigen Werten sortiert, müssen Sie eine wichtige Frage beantworten:

Answer 22

Möchten Sie genau N Zeilen, alle Zeilen für die ersten N Werte oder N Zeilen zusammen mit allen, die denselben Wert wie der N-te haben?

Zum Beispiel haben sowohl Miss Snuggles als auch Blue Dinosaur einen Preis von 9,99. Wenn du also die drei teuersten Spielzeuge holst, könntest du eines von beiden bekommen!

Wenn du FETCH FIRST benutzt, kannst du garantieren, dass du beide bekommst. Du musst nur ONLY mit WITH TIES austauschen. Dies gibt Ihnen N Zeilen zurück, plus alle, die denselben Wert für die Reihenfolge nach Spalten haben wie die letzte. Sie erhalten also beide Spielzeuge zum Preis von 9,99:

select toy_name, price from toys

order by price desc

fetch first 3 rows with ties;

Sie können denselben Effekt mit einer Unterabfrage erzielen, indem Sie row_number gegen rank austauschen (1,2,2,4):

select * from ( select t.*, rank() over ( order by price desc ) rn from toys t ) where rn <= 3 order by rn;

Wenn Sie alle Zeilen für die ersten drei Werte benötigen, verwenden Sie stattdessen dense_rank in der Unterabfrage (1,2,2,3):

select * from ( select t.*, dense_rank() over ( order by price desc ) rn from toys t ) where rn <= 3 order by rn;

Question 23

Ist das eine korrekte Abfrage?

select toy_name from toys rt where row_number() over ( order by weight ) <= 2;

Answer 23

Nein, Sie können keine analytischen Funktionen in der Where-Klausel verwenden! Diese Abfrage löst den Fehler “ORA-30483: window functions are not allowed here” aus.

Question 24

Was ist der Unterschied zwischen Analytischen und Aggregatfunktionen?

Beispiel mit count.

Answer 24

Mit Aggregat- und Analytischen Funktionen kann man Berechnungen über mehrere Zeilen vornehmen. Aggregatfunktionen komprimieren die Ausgabe auf eine Zeile pro Gruppe.

Das folgende Beispiel zählt die Gesamtanzahl der Zeilen in der Tabelle. Es gibt eine Zeile zurück:

select count(*) from bricks;

Durch Hinzufügen der over-Klausel wird sie in eine analytische Funktion umgewandelt. Dadurch bleiben die Eingabezeilen erhalten. Sie erhalten also alle sechs, jeweils mit dem Wert sechs:

select b.*, count(*) over () total_count from bricks b;

Question 25

Wie kann ich so ein Ergebnis mit SQL erhalten?

Answer 25

select b.*,
count(*) over (
partition by colour
) bricks_per_colour,
sum ( weight ) over (
partition by colour
) weight_per_colour
from bricks b;

Question 26

Wie kann ich die kumulierenden Beträge erhalten?

Answer 26

Normalerweise sollten laufende Summen nur Werte aus früheren Zeilen des Datensatzes enthalten. Daher muss rows anstatt range verwendet werden.

Damit es deterministisch wird, müssen soviele Spalten zu ORDER BY hinzugefügt werden, bis jedes Set nur einmal vorkommt in der Sortierung. Ansonsten könnte die Sortierung bei jeder Abfrage anders sein.

select b.*,
count(*) over (
order by weight, brick_id
rows between unbounded preceding and current row
) running_total,
sum ( weight ) over (
order by weight, brick_id
rows between unbounded preceding and current row
) running_weight
from bricks b
order by weight, brick_id;

Question 27

Wie kann ich diese Ausgabe verändern, damit die Orte als Spalte ausgegeben werden?

Answer 27

Die erste Möglichkeit verwendet CASE

select

count ( case when location = ‘Snowley’ then 1 end ) snowley,

count ( case when location = ‘Coldgate’ then 1 end ) coldgate,

count ( case when location = ‘Dorwall’ then 1 end ) dorwall,

count ( case when location = ‘Newdell’ then 1 end ) newdell

from match_results;

Ein bessere Möglichkeit ist PIVOT

with rws as (select location from match_results)
select * from rws
pivot (
count(*) for location in (‘Snowley’ , ‘Coldgate’ , ‘Dorwall’ , ‘Newdell’) );

Question 28

Wie kann ich so eine Abfrage erstellen? Die Anzahl der Spiele je Team?

Answer 28

with rws as (
select home_team_name from match_results
)
select * from rws
pivot (
count (*)
for home_team_name
in (‘Underrated United’, ‘Average Athletic’, ‘Terrible Town’, ‘Champions City’ )
);

Question 29

Wie bekomme ich die Anzahl der Spiele je Monat?

Answer 29

with rws as (
select to_char ( match_date, ‘MON’ ) match_month
from match_results
)
select * from rws
pivot (
count (*) for match_month in (
‘JAN’, ‘FEB’, ‘MAR’
)
);

Question 30

Angenommen, Sie möchten eine Tabelle mit der Anzahl der Spiele anzeigen, die jeden Monat an jedem Ort ausgetragen werden. Sie können dies mit tun:

Answer 30

with rws as

( select location, to_char ( match_date, ‘MON’ ) match_month from match_results )

select * from rws

pivot ( count (*) for match_month in ( ‘JAN’, ‘FEB’, ‘MAR’ ) )

Question 31

Angenommen, Sie möchten eine Tabelle mit der Anzahl der Spiele anzeigen, die jeden Monat an jedem Ort ausgetragen werden. Außerdem möchten Sie die Suche auf die Orte beschränken, die im Januar mindestens einen Treffer hatten,

Answer 31

with rws as ( select location, to_char ( match_date, ‘MON’ ) match_month from match_results )

select * from rws

pivot ( count (*) for match_month in ( ‘JAN’ jan, ‘FEB’ feb, ‘MAR’ mar ) )

where “‘JAN’” > 0

Question 32

Sie können viele Werte pivotieren. Nehmen wir zum Beispiel an, Sie möchten für jeden Monat Spalten anzeigen:

• die Anzahl der ausgetragenen Spiele
• die von der Heimmannschaft insgesamt erzielten Punkte
• die Gesamtpunktzahl der Auswärtsmannschaft

Answer 32

with rws as ( select location, to_char ( match_date, ‘MON’ ) match_month , home_team_points, away_team_points from match_results )

select * from rws

pivot ( count (*) matches, sum ( home_team_points ) home_points, sum ( away_team_points ) away_points for match_month in ( ‘JAN’ jan, ‘FEB’ feb, ‘MAR’ mar ) );

Question 33

Kann man dynamische Listen für pivots verwenden?

Answer 33

Leider ist dies bei normalen Pivots nicht möglich. Die Werte in der IN-Liste sind fest. Sie können keine Unterabfragen oder Variablen verwenden, um sie zu ändern.

Glücklicherweise können Sie mit XML-Pivoting eine dynamische IN-Liste erstellen. Damit können Sie Werte aus einer Unterabfrage übergeben. Oder Sie generieren Summen für jeden Wert in der Pivot-Spalte mit ANY:

Aber Sie erhalten die pivotierten Werte als ein einziges XML-Dokument! Um die Werte zu extrahieren, müssen Sie also die XML-Datei manipulieren.

with rws as ( select location from match_results )

select xmlserialize ( – formats an XML document document location_xml as clob indent size=2 ) location_xml from rws pivot xml ( count (*) matches for location in ( any ) );