SQL-Beispiele aus dem Miro-Board (05 SQL-Update)
SQL-Dateien
Cross-over Foreign Keys: Beispiel
START TRANSACTION;
CREATE TABLE a
( id INTEGER NOT NULL,
b INTEGER NOT NULL,
CONSTRAINT p_a
PRIMARY KEY (id)
);
CREATE TABLE b
( id INTEGER NOT NULL,
a INTEGER NOT NULL,
CONSTRAINT p_b
PRIMARY KEY (id),
CONSTRAINT f_b_a
FOREIGN KEY (a) REFERENCES a(id)
DEFERRABLE INITIALLY DEFERRED
);
/* Kann erst hier definiert werden, aber nicht direkt in TABLE a. */
ALTER TABLE a ADD
CONSTRAINT f_a_b
FOREIGN KEY (b) REFERENCES b(id)
DEFERRABLE INITIALLY DEFERRED;
COMMIT WORK;
/* Funktioniert nicht, wenn Auto-Commit je Befehl aktiv! */
/*
INSERT INTO a VALUES (1,2);
INSERT INTO b VALUES (2,1);
*/
/* Funktioniert nicht! */
/*
START TRANSACTION;
INSERT INTO a VALUES (1,2);
COMMIT WORK;
START TRANSACTION;
INSERT INTO b VALUES (2,1);
COMMIT WORK;
*/
/* Funktioniert wegen DEFERRABLE INITIALLY DEFERRED! */
START TRANSACTION;
INSERT INTO a VALUES (1,2);
INSERT INTO b VALUES (2,1);
COMMIT WORK;
/* Funktioniert wegen DEFERRABLE INITIALLY DEFERRED! */
START TRANSACTION;
DELETE FROM a;
DELETE FROM b;
COMMIT WORK;
/* Funktioniert nicht! */
/*
SET CONSTRAINTS f_a_b IMMEDIATE;
START TRANSACTION;
INSERT INTO a VALUES (1,2);
INSERT INTO b VALUES (2,1);
COMMIT WORK;
*/
/* Analog mit:
SET CONSTRAINTS f_a_b, f_b_a IMMEDIATE;
SET CONSTRAINTS ALL IMMEDIATE;
*/
/* Funktioniert da DEFERRABLE gesetzt wurde! */
SET CONSTRAINTS ALL DEFERRED;
START TRANSACTION;
INSERT INTO a VALUES (1,2);
INSERT INTO b VALUES (2,1);
COMMIT WORK;
Insert
INSERT INTO haendler (h_id, h_name, h_ortschaft)
VALUES (6, 'Schmidt', 'München'); -- korrekt
INSERT INTO haendler (h_id, h_name)
VALUES (7, 'Schmitt'); -- korrekt, da h_ortschaft NULLABLE ist
-- Für nicht-angegebenen Attribute werden die Defaultwerte
-- eingesetzt (DEFAULT .... oder NULL bei NULLABLE)
INSERT INTO haendler (h_id, h_name, h_ortschaft)
VALUES ((SELECT MAX(h_id)+1 FROM haendler),
'Kowarschick', 'Königsbrunn'
);
INSERT INTO haendler (h_id, h_name)
VALUES ((SELECT MAX(h_id)+1 FROM haendler),
'Kovac'
); -- zwei einzelne Befehle:
-- MAX(h_id)+1, da die Befehle
-- nacheinander ausgeführt werden
WITH ...
INSERT INTO haendler(h_id, h_name, h_ortschaft)
VALUES ((SELECT MAX(h_id)+1 FROM haendler),
'Maier eins', 'Königsbrunn'
),
((SELECT MAX(h_id)+2 FROM haendler),
'Maier zwo', 'Bobingen'
) -- ein Befehl:
-- MAX(h_id)+2, da alle Inserts
-- "gleichzeitig" ausgeführt werden
;
INSERT INTO haendler (h_id, h_name, h_ortschaft)
VALUES ((SELECT COALESCE(MAX(h_id)+1,0) FROM haendler),
'Kowarschick', 'Königsbrunn'
);
Aufgabe
-- Ergänzen Sie in derselben Transaktion einen INSERT-
-- Befehl, so dass Maier 1 und Maier zwo jeweils eine
-- Eieruhr liefern.
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO haendler(h_id, h_name, h_ortschaft)
VALUES ((SELECT MAX(h_id)+1 FROM haendler),
'Maier 1', 'Königsbrunn'
),
((SELECT MAX(h_id)+2 FROM haendler),
'Maier zwo', 'Bobingen'
);
COMMIT WORK;
-- Eigenständige Transaktion
INSERT INTO liefert(h_id, w_id, l_preis)
VALUES ((SELECT h_id
FROM haendler
WHERE h_name = 'Maier 1'
AND h_ortschaft = 'Königsbrunn'),
(SELECT w_id
FROM ware
WHERE w_typ = 'Sonstiges'
AND w_bezeichnung = 'Eieruhr'
),
6.00
),
((SELECT h_id
FROM haendler
WHERE h_name = 'Maier zwo'
AND h_ortschaft = 'Bobingen'
),
(SELECT w_id
FROM ware
WHERE w_typ = 'Sonstiges'
AND w_bezeichnung = 'Eieruhr'
),
5.50
);
COMMIT WORK;
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO haendler(h_id, h_name, h_ortschaft)
VALUES ((SELECT MAX(h_id)+1 FROM haendler),
'Maier 1a', 'Königsbrunn'
),
((SELECT MAX(h_id)+2 FROM haendler),
'Maier zwo a', 'Bobingen'
);
INSERT INTO liefert(h_id, w_id, l_preis)
VALUES ((SELECT MAX(h_id)-1 FROM haendler),
(SELECT w_id
FROM ware
WHERE w_typ = 'Sonstiges'
AND w_bezeichnung = 'Eieruhr'
),
6.00
),
((SELECT MAX(h_id) FROM haendler),
(SELECT w_id
FROM ware
WHERE w_typ = 'Sonstiges'
AND w_bezeichnung = 'Eieruhr'
),
5.50
);
COMMIT WORK;
Delete
DELETE
FROM haendler
WHERE h_name = 'Maier'; -- Lösche ALLE Maiers!
DELETE
FROM haendler; -- Lösche ALLE Händler!
DELETE
FROM haendler
WHERE TRUE; -- Lösche ALLE Händler!
-- Abhängige Tupel automatisch mit löschen.
CREATE TABLE liefert
(h_id INTEGER NOT NULL,
w_id INTEGER NOT NULL,
l_preis NUMERIC(8,2) NOT NULL,
l_lieferzeit SMALLINT, /* Tage */
CONSTRAINT pk_liefert
PRIMARY KEY (h_id, w_id, l_preis),
CONSTRAINT fk_liefert_haendler
FOREIGN KEY (h_id) REFERENCES haendler (h_id)
ON DELETE CASCADE,
-- Wenn Händler gelöscht werden,
-- dann werden die zugehörigen liefert-Tupel
-- automatisch ebenfalls gelöscht
CONSTRAINT fk_liefert_ware
FOREIGN KEY (w_id) REFERENCES ware (w_id)
ON DELETE CASCADE,
CONSTRAINT check_liefert_lieferzeit
CHECK (l_lieferzeit > 0)
);
DELETE
FROM haendler
WHERE h_name = 'Maier'; -- Lösche ALLE Maiers!
Update
UPDATE liefert
SET l_preis = ROUND(l_preis * 1.10, 2), -- Preis um 10% erhöhen
l_lieferzeit = l_lieferzeit + 1 -- Lieferzeit um einen Tag erhöhen
WHERE -- Händler ist irgendein Maier
h_id IN -- Semi-JOIN
(SELECT h_id
FROM haendler
WHERE h_name = 'Maier'
)
;
UPDATE liefert l
SET l_preis = ROUND(l_preis * 1.10, 2), -- Preis um 10% erhöhen
l_lieferzeit = l_lieferzeit + 1 -- Lieferzeit um einen Tag erhöhen
FROM haendler h -- kein Standard-SQL aber Postgres-SQL
WHERE l.h_id = h.h_id
AND h_name = 'Maier'
Denkaufgaben
DELETE
FROM liefert
WHERE l_preis >= (SELECT AVG(l_preis) FROM liefert)
UUIDs als Primary-Key-Werte
DROP TABLE IF EXISTS haendler CASCADE;
CREATE TABLE haendler
(h_id UUID NOT NULL DEFAULT gen_random_uuid(),
h_name VARCHAR(30) NOT NULL,
h_ortschaft VARCHAR(50),
CONSTRAINT pk_haendler
PRIMARY KEY (h_id),
CONSTRAINT unique_haendler_name_ortschaft
UNIQUE (h_name, h_ortschaft)
);
INSERT INTO haendler(h_name, h_ortschaft)
VALUES
('Maier', 'Königsbrunn'),
('Müller', 'Königsbrunn'),
('Maier', 'Augsburg'),
('Huber', NULL),
('Schmidt', 'Hamburg')
;
SELECT * FROM haendler;
DROP TABLE IF EXISTS haendler CASCADE;
CREATE TABLE ware
(w_id UUID NOT NULL DEFAULT gen_random_uuid(),
w_typ VARCHAR(30) NOT NULL DEFAULT 'Sonstiges',
w_bezeichnung VARCHAR(50) NOT NULL,
CONSTRAINT pk_ware
PRIMARY KEY (w_id),
CONSTRAINT unique_ware_typ_bezeichnung
UNIQUE (w_typ, w_bezeichnung)
);
INSERT INTO ware(w_typ, w_bezeichnung)
VALUES
((SELECT 'CPU', 'Pentium IV 3,8'),
((SELECT 'CPU', 'Celeron 2,6'),
((SELECT 'CPU', 'Athlon XP 3000+'),
((SELECT 'RAM', 'SDRAM 1GB'),
((SELECT 'Sonstiges', 'Eieruhr')
;
SELECT * FROM ware;
liefert-Tupel erstellen
SELECT id, COALESCE(h_name || ' in ' || h_ortschaft, h_name) FROM haendler;
<form>
<label for="haendler">Wählen Sie einen Händler:</label>
<select name="haendler" id="haendler">
<option value="c5e96204-c944-4cc4-b375-a4cebc52afb2">'Maier in Königsbrunn'</option>
<option value="f642a3e7-e8e8-4c88-848f-8a8964506747">'Müller in Königsbrunn'</option>
<option value="dfb5c92e-984e-4981-9080-033534f7473c">'Maier in Augsburg'</option>
<option value="5f30ac28-64a5-4fb0-96c6-fa89bb2a073a">'Huber'</option>
<option value="52f92ce1-8975-4835-b520-82384e97515e">'Schmidt in Hamburg'</option>
</select>
...
</form>
Tabelle „liefert” initialisieren
-- Funktioniert immer
INSERT INTO liefert(h_id, w_id, l_preis)
VALUES ((SELECT h_id
FROM haendler
WHERE h_name = 'Maier 1'
AND h_ortschaft = 'Königsbrunn'),
(SELECT w_id
FROM ware
WHERE w_typ = 'Sonstiges'
AND w_bezeichnung = 'Eieruhr'
),
6.00
),
((SELECT h_id
FROM haendler
WHERE h_name = 'Maier zwo'
AND h_ortschaft = 'Bobingen'
),
(SELECT w_id
FROM ware
WHERE w_typ = 'Sonstiges'
AND w_bezeichnung = 'Eieruhr'
),
5.50
);
COMMIT WORK;
BEGIN TRANSACTION;
DELETE
FROM liefert
;
CREATE TEMPORARY TABLE
ids
(id INTEGER NOT NULL PRIMARY KEY,
h_id UUID NOT NULL DEFAULT gen_random_uuid(),
w_id UUID NOT NULL DEFAULT gen_random_uuid()
)
ON COMMIT DROP
;
INSERT INTO ids
VALUES (1), (2), (3), (4), (5)
;
INSERT INTO haendler(h_id, h_name, h_ortschaft)
VALUES
((SELECT h_id FROM ids WHERE id=1), 'Maier', 'Königsbrunn'),
((SELECT h_id FROM ids WHERE id=2), 'Müller', 'Königsbrunn'),
((SELECT h_id FROM ids WHERE id=3), 'Maier', 'Augsburg'),
((SELECT h_id FROM ids WHERE id=4), 'Huber', NULL),
((SELECT h_id FROM ids WHERE id=5), 'Schmidt', 'Hamburg')
;
INSERT INTO ware(w_id, w_typ, w_bezeichnung)
VALUES
((SELECT w_id FROM ids WHERE id=1), 'CPU', 'Pentium IV 3,8'),
((SELECT w_id FROM ids WHERE id=2), 'CPU', 'Celeron 2,6'),
((SELECT w_id FROM ids WHERE id=3), 'CPU', 'Athlon XP 3000+'),
((SELECT w_id FROM ids WHERE id=4), 'RAM', 'SDRAM 1GB'),
((SELECT w_id FROM ids WHERE id=5), 'Sonstiges', 'Eieruhr')
;
INSERT INTO liefert(h_id, w_id, l_preis, l_lieferzeit)
VALUES
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=1), 200.00, 1),
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=1), 194.00, 6),
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=2), 100.00, NULL),
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=3), 150.00, 7),
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=4), 10.00, 1),
((SELECT h_id FROM ids WHERE id=1), (SELECT w_id FROM ids WHERE id=5), 5.00, 1),
((SELECT h_id FROM ids WHERE id=2), (SELECT w_id FROM ids WHERE id=1), 160.00, NULL),
((SELECT h_id FROM ids WHERE id=2), (SELECT w_id FROM ids WHERE id=1), 190.00, 1),
((SELECT h_id FROM ids WHERE id=2), (SELECT w_id FROM ids WHERE id=2), 180.00, NULL),
((SELECT h_id FROM ids WHERE id=2), (SELECT w_id FROM ids WHERE id=3), 170.00, 4),
((SELECT h_id FROM ids WHERE id=2), (SELECT w_id FROM ids WHERE id=5), 5.00, 1),
((SELECT h_id FROM ids WHERE id=3), (SELECT w_id FROM ids WHERE id=1), 195.00, 2),
((SELECT h_id FROM ids WHERE id=3), (SELECT w_id FROM ids WHERE id=2), 190.00, 1),
((SELECT h_id FROM ids WHERE id=4), (SELECT w_id FROM ids WHERE id=1), 150.00, 3),
((SELECT h_id FROM ids WHERE id=4), (SELECT w_id FROM ids WHERE id=3), 180.00, 5),
((SELECT h_id FROM ids WHERE id=4), (SELECT w_id FROM ids WHERE id=3), 199.99, 1)
;
COMMIT WORK;
