271 lines
6.5 KiB
Plaintext
271 lines
6.5 KiB
Plaintext
a) Was machen die Schlüsselwörter UNION, UNION ALL, INTERSECT und EXCEPT? Geben Sie jeweils ein Beispiel an.
|
|
|
|
UNION:
|
|
Mit UNION kann man zwei SELECT Ergebinisse in einem kombinieren.
|
|
|
|
Beispiel:
|
|
|
|
SELECT
|
|
col1,
|
|
col2
|
|
FROM
|
|
table1
|
|
UNION
|
|
SELECT
|
|
col3,
|
|
col4
|
|
FROM
|
|
table2;
|
|
|
|
Mit diesem Syntax wird die erste SELECT Abfrage mit der zewiten kombiniert.
|
|
UNION hat diverse Regeln, die befolgt werden müssen:
|
|
- Gleiche Anzahl an Spalten. Die SELECT Abfragen müssen die genau gleiche Anzal Spalten haben
|
|
- Kompatible Datentypen. Die entsprechenden Spalten in jeder SELECT Abfrage müssen kompatible Datentypen aufweisen
|
|
- Spaltennamen. Die Spaltennamen der zweiten Abfrage bestimmen die Spaltennamen der resultierenden Tabelle
|
|
- Reihenfolge. UNION führt die SELECT-Anweisungen unabhängig voneinander aus und kombiniert die Ergebnismengen
|
|
- Ergebinisse Sortieren. Verwendung von ORDER BY nach der zweiten Abfrage um die Zeilen zu Sortieren
|
|
|
|
UNION ALL:
|
|
Mit UNION ALL werden im Ergebniss auch die Duplicate angezeigt.
|
|
|
|
Beispiel:
|
|
|
|
Tabelle A:
|
|
| id |
|
|
|-------|
|
|
| 1 |
|
|
| 2 |
|
|
|
|
Tabelle B:
|
|
| id |
|
|
|-------|
|
|
| 2 |
|
|
| 3 |
|
|
|
|
Verwendung von UNION Verwendung von UNION ALL
|
|
SELECT SELECT
|
|
id id
|
|
FROM FROM
|
|
a a
|
|
UNION UNION ALL
|
|
SELECT SELECT
|
|
id id
|
|
FROM FROM
|
|
b; b;
|
|
|
|
Ergebiniss: Ergebiniss:
|
|
| id | | id |
|
|
|-------| |-------|
|
|
| 1 | | 1 |
|
|
| 2 | | 2 |
|
|
| 3 | | 2 |
|
|
| 3 |
|
|
|
|
|
|
INTERSECT:
|
|
INTERSECT findet die gemeinsamen Zeilen der Ergebnismengen zweier SELECT Abfragen.
|
|
|
|
Syntax:
|
|
SELECT
|
|
col1,
|
|
col2
|
|
FROM
|
|
table1
|
|
INTERSECT
|
|
SELECT
|
|
col1,
|
|
col2
|
|
FROM
|
|
table2;
|
|
|
|
Mit diesem Syntax werden nur die Zeilen mit Gemeinsamkeiten angezeigt.
|
|
INTERSECT muss die gleichen Regelen wie UNION befolgen.
|
|
|
|
Beispiel:
|
|
|
|
Tabelle A:
|
|
| id |
|
|
|-------|
|
|
| 1 |
|
|
| 2 |
|
|
| 3 |
|
|
|
|
Tabelle B:
|
|
| id |
|
|
|-------|
|
|
| 2 |
|
|
| 3 |
|
|
| 4 |
|
|
|
|
SELECT
|
|
id
|
|
FROM
|
|
a
|
|
INTERSECT
|
|
SELECT
|
|
id
|
|
FROM
|
|
b;
|
|
|
|
Ergebiniss:
|
|
| id |
|
|
|-------|
|
|
| 2 |
|
|
| 3 |
|
|
|
|
|
|
EXCEPT:
|
|
EXCEPT ist das gegenstück von INTERSECT. Es entfent Gemeinsamkeiten.
|
|
|
|
Syntax:
|
|
SELECT
|
|
col1,
|
|
col2
|
|
FROM
|
|
table1
|
|
EXCEPT
|
|
SELECT
|
|
col1,
|
|
col2;
|
|
|
|
Mit diesem Syntax werden nur die Zeilen ohne Gemeinsamkeiten angezeigt.
|
|
EXCEPT muss die gleichen Regelen wie UNION befolgen.
|
|
|
|
Beispiel:
|
|
|
|
Tabelle A:
|
|
| id |
|
|
|-------|
|
|
| 1 |
|
|
| 2 |
|
|
| 3 |
|
|
|
|
Tabelle B:
|
|
| id |
|
|
|-------|
|
|
| 2 |
|
|
| 3 |
|
|
| 4 |
|
|
|
|
SELECT
|
|
id
|
|
FROM
|
|
a
|
|
EXCEPT
|
|
SELECT
|
|
id
|
|
FROM
|
|
b;
|
|
|
|
Ergebiniss:
|
|
| id |
|
|
|-------|
|
|
| 1 |
|
|
| 4 |
|
|
|
|
|
|
b) Was versteht man unter einem "Partial Index"?
|
|
|
|
Partial Index:
|
|
Ein Parial Index eine Funktion, die dazu dient, die Abfrageleistung zu verbessern und gleichzeitig die Indexgrösse zu minimieren.
|
|
Sie ermöglichen die spezifische Angabe, welche Zeilen indexiert werden sollen. Abfragen, die WHERE Bedingugen mit konstanten Werten verwenden, werden deutlich beschleunigt.
|
|
|
|
Beispiel:
|
|
|
|
Tabelle A
|
|
| id | status |
|
|
|-------|-----------|
|
|
| 1 | 1 |
|
|
| 2 | 0 |
|
|
| 3 | 0 |
|
|
|
|
CREATE INDEX idx_status
|
|
ON a(status)
|
|
WHERE status = 0;
|
|
|
|
Ein Index wird erstellt, aber, wegen der Bedingug "WHERE status = 0" nur auf die Spalte status.
|
|
|
|
Eine SELECT Abfrage ist wegen dem Index leistungsfähiger.
|
|
|
|
SELECT id
|
|
FROM a
|
|
WHERE status = 0;
|
|
|
|
|
|
c) Was ist eine View? Nennen Sie eine Anwendungsmöglichkeit in der Bank-Datenbank.
|
|
|
|
SQL Views:
|
|
Views sind gespeicherte Abfragen im Datenbank System. Das DBMS speichert Views nicht als Tabelle sondern nur ihre Definition, der SQL Code.
|
|
Wenn man einen View abfragt, werden die Daten aus der Datenbank geholt. Der Anwendungsfall ist für immerwiederkehrende Abfragen.
|
|
|
|
In der Bank Datenbank könne man eine View dafür verwenden, um den Gesamvermögen eines Kunden über all seine Konten zusammenzutragen.
|
|
In der Aufgabe 2. 14) war die verwendung einer Sub-Query sinnvoll. Diese Sub-Query hätte man als View speichern können:
|
|
|
|
CREATE VIEW balance AS
|
|
SELECT
|
|
cust_id,
|
|
SUM(avail_balance) AS balance
|
|
FROM account
|
|
GROUP BY cust_id
|
|
ORDER BY balance DESC
|
|
LIMIT 1;
|
|
|
|
Im DBMS ist nun eine View "balance" gepseichrt. Jetzt kann man von dieser Abfragen machen.
|
|
|
|
SELECT
|
|
COALESCE(b.name, i.fname || ' ' || i.lname) customer_name
|
|
FROM balance
|
|
...;
|
|
|
|
|
|
d) Wie kann man in Postgres eine Primärschlüsselspalte erzeugen, die automatisch fortlaufende (ganzzahlige) IDs erzeugt?
|
|
|
|
In SQL wird diese Funktion als "auto increment" bezeichnet. Der Prozess vereinfacht die generierung eindeutiger IDs.
|
|
In Postgres wird dafür der Schlüsselbegriff "SERIAL" verwendet. Es gibt drei verschiedene Variationen:
|
|
- SMALLSERIAL: verwendet 2 Bytes und speichert Wete von 1 bis 32'767
|
|
- SERIAL: verwendet 4 Bytes und speichert Werte von 1 bis 2'147'483'647
|
|
- BIGSERIAL: verwendet 8 Bytes und speichert Werte von 1 bis 9'223'372'036'854'775'807
|
|
|
|
Syntax:
|
|
|
|
CREATE TABLE table_name(
|
|
id SERIAL
|
|
);
|
|
|
|
|
|
e) Was versteht man unter einem Trigger, und wo kann man diese einsetzen? Geben Sie ein Beispiel für die Bank-Datenbank an.
|
|
|
|
Mit Triggers können vordefiniderte Funktionen als Reaktion auf ein bestimmtes Ereignis ausgeführt werden.
|
|
Ein Trigger ist immer mit einer bestimmten Tabelle assoziiert. Löscht man die Tabelle, löscht man auch alle dazugehörigen Trigger.
|
|
|
|
Ein Trigger wird entweder vor oder nach dem folgenden Ereignis aufgerufen:
|
|
- INSERT: wenn eine neue Zeile hinzugefügt wird
|
|
- UPDATE: wenn eine Zeile geändert wird
|
|
- DELETE: wenn eine Zeile gelöscht wird
|
|
- TRUNCATE: wenn alle Zeilen aus einer Tabelle entfernt werden
|
|
|
|
In der Bank Datenbank könnte man einen Trigger erstellen, der bei der erstellung eines neuen Kunden auch automatisch ein neues Konto erstellt.
|
|
|
|
Syntax:
|
|
|
|
CREATE TRIGGER trigger_name
|
|
[BEFORE|AFTER] event
|
|
ON table_name trigger_type
|
|
BEGIN
|
|
-- trigger_logic
|
|
END;
|
|
|
|
Beispiel:
|
|
|
|
In einem Online-Shop soll die Verfügbarkeit von Produkten überwacht werden.
|
|
Jedes Mal, wenn eine Bestellung in die Datenbank eingegeben wird, sollte der Lagerbestand für das bestellte Produkt automatisch aktualisiert werden.
|
|
|
|
CREATE TRIGGER update_inventory
|
|
AFTER INSERT
|
|
ON orders
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE products
|
|
SET stock = stock - NEW.quantity
|
|
WHERE product_id = NEW.product_id;
|
|
END; |