Beispiele der Vorlesung 4

SQL-Beispiele aus dem Miro-Board (04 SQL-Vertiefung)

Dreiwertige Logik: Beispiele

DROP TABLE IF EXISTS t;

CREATE TABLE t
(a INTEGER,
 b INTEGER,
 c INTEGER
);

INSERT INTO t
VALUES (3, 4, null)
;

SELECT a, b, c,
       (a > b AND b > c) AS ergebnis1,
       (a > b OR  b > c) AS ergebnis2,
       (a < b OR  b < c) AS ergebnis3,
       (c = c)           AS ergebnis4,
       NOT(c = c)        AS ergebnis5,
       NOT(c = NULL)     AS ergebnis6,
       NOT(c IS NULL)    AS ergebnis7,
       (c IS NOT NULL)   AS ergebnis8,
       (NOT c IS NULL)   AS ergebnis9
FROM   t
;

Beispiel aus der Olympia-Aufgabe

/*
** Welche Sportler ('w' und 'm') sind disqualifiziert,
** aber nicht gedopt worden?

name     vorname     geburtstag  land
-------------------------------------
Maier    Josef       1982-01-01  GER
*/

SELECT DISTINCT s.name, s.vorname, s.geburtstag, s.land
FROM            sportler s JOIN teilnahme t ON s.id = t.s_id
WHERE           t.doping IS NOT TRUE AND t.disqualifiziert IS TRUE
             -- t.doping IS FALSE  reicht nicht!!!
;

Boolesche Operatoren

SELECT (DATE '2001-02-16', DATE '2001-12-21') 
       OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');

Outer Join

DROP TABLE IF EXISTS r;
DROP TABLE IF EXISTS s;

CREATE TABLE r
(h_name  VARCHAR,
 ware    VARCHAR
);

CREATE TABLE s
(h_name  VARCHAR,
 tel     INTEGER
);

INSERT INTO r(h_name, ware)
VALUES ('Maier',  'Lampen'),
       ('Müller', 'Rechner');

INSERT INTO s(h_name, tel)
VALUES ('Maier', 123),
       ('Huber', 234);

SELECT * FROM r       JOIN s ON r.h_name = s.h_name;
SELECT * FROM r LEFT  JOIN s ON r.h_name = s.h_name;
SELECT * FROM r RIGHT JOIN s ON r.h_name = s.h_name;
SELECT * FROM r FULL  JOIN s ON r.h_name = s.h_name;

SELECT * FROM r       JOIN s USING (h_name);
SELECT * FROM r LEFT  JOIN s USING (h_name);
SELECT * FROM r RIGHT JOIN s USING (h_name);
SELECT * FROM r FULL  JOIN s USING (h_name);

-- sollte vermieden werden
SELECT * FROM r NATURAL       JOIN s;
SELECT * FROM r NATURAL LEFT  JOIN s;
SELECT * FROM r NATURAL RIGHT JOIN s;
SELECT * FROM r NATURAL FULL  JOIN s;

Joinoperationen: Union Join

SELECT r.h_name AS h_name_ware, r.ware, s.h_name AS h_name_tel
FROM   r FULL JOIN s ON FALSE   -- entspricht r UNION JOIN s

Mengenoperationen

Beispiel

SELECT h_id AS id, h_name AS name, h_ortschaft  -- Ma./K., Ma./A., Mü/.K
FROM   haendler
WHERE  h_name LIKE 'M%'

UNION ALL -- UNION/INTERSECT/EXCEPT ALL/DISTINCT

SELECT h_id,       h_name,         h_ortschaft  -- Ma./A., S./H.
FROM   haendler
WHERE  h_ortschaft <> 'Königsbrunn'