/******************************************************************************
 * Finde alle Dokumente vom Autor 'Kowarschick':
 ******************************************************************************/

SELECT DISTINCT did, autor, titel, inhalt
FROM   dokindex
WHERE  wort = 'kowarschick' AND attr = 'autor'
;

/******************************************************************************
 * Finde alle Dokumente vom Autor 'Märtin':
 ******************************************************************************/

SELECT DISTINCT did, autor, titel, inhalt
FROM   dokindex
WHERE  wort = 'maertin' AND attr = 'autor'
;

/******************************************************************************
 * Finde alle Dokumente, die 'Vorlesung' oder 'Datenbank%' enthalten.
 ******************************************************************************/

SELECT DISTINCT did, autor, titel, inhalt
FROM   dokindex 
WHERE  wort = 'vorlesung' OR wort LIKE 'datenbank%'
;

/******************************************************************************
 * Finde alle Dokumente, die 'MMP%' und 'Datenbank%' enthalten.
 ******************************************************************************/

SELECT DISTINCT d1.did, d1.autor, d1.titel, d1.inhalt
FROM   dokindex d1 JOIN dokindex d2 ON d1.did = d2.did
WHERE  d1.wort like 'mm%' AND d2.wort LIKE 'datenbank%'
;

/******************************************************************************
 * Finde alle Dokumente, die 'Vorlesung' und 'Kowa%' enthalten.
 ******************************************************************************/

SELECT DISTINCT d1.did, d1.autor, d1.titel, d1.inhalt
FROM   dokindex d1 JOIN dokindex d2 ON d1.did = d2.did
WHERE  d1.wort = 'vorlesung' AND d2.wort LIKE 'kowa%'
;

/******************************************************************************
 * Finde alle Dokumente, die 'Vorlesung', 'Kowa%' und 'WiSe' enthalten.
 ******************************************************************************/

SELECT DISTINCT d1.did, d1.autor, d1.titel, d1.inhalt
FROM   dokindex d1 JOIN dokindex d2 ON d1.did = d2.did
                   JOIN dokindex d3 ON d1.did = d3.did
WHERE  d1.wort = 'vorlesung' AND d2.wort LIKE 'kowa%' AND d3.wort = 'wise'
;

/******************************************************************************
 * Finde alle Dokumente, die 'PM' oder 'Datenbank%' enthalten.
 ******************************************************************************/

SELECT DISTINCT d1.did, d1.autor, d1.titel, d1.inhalt
FROM   dokindex d1
WHERE  d1.wort = 'pm' OR d1.wort LIKE 'datenbank%'
;

/******************************************************************************
 * Finde alle Dokumente, die 'Vorlesung' und 'Kowa%',
 * aber nicht 'MMProg' enthalten.
 ******************************************************************************/

SELECT DISTINCT d1.did, d1.autor, d1.titel, d1.inhalt
FROM   dokindex d1 JOIN dokindex d2 ON d1.did = d2.did
WHERE  d1.wort = 'vorlesung' AND d2.wort LIKE 'kowa%' AND
       NOT EXISTS (SELECT * 
                   FROM   dokindex d3
                   WHERE  d1.did = d3.did AND d3.wort = 'mmprog'
                  )
;

/******************************************************************************
 * Finde alle Dokumente, die 'Michael Lutz' (als Phrase) enthalten.
 ******************************************************************************/

SELECT DISTINCT d1.did, d1.autor, d1.titel, d1.inhalt
FROM   dokindex d1 JOIN dokindex d2 ON d1.did = d2.did
WHERE  d1.aid = d2.aid AND
       d1.wort = 'michael' AND d2.wort = 'lutz' AND
       d1.pos+1 = d2.pos 
;

/******************************************************************************
 * Finde alle Dokumente, die 'MM % % WiSe' (als Phrase) enthalten.
 ******************************************************************************/

SELECT DISTINCT d1.did, d1.autor, d1.titel, d1.inhalt
FROM   dokindex d1 JOIN dokindex d2 ON d1.did = d2.did
WHERE  d1.aid = d2.aid AND
       d1.wort = 'mm' AND d2.wort = 'wise' AND
       d1.pos+3 = d2.pos
;

/******************************************************************************
 * Finde alle Dokumente, die '%systeme' enthalten.
 ******************************************************************************/

SELECT DISTINCT did, autor, titel, inhalt
FROM   dokindex
WHERE  wort LIKE '@emetsys%'
;

-- nicht (da diese Anfrage bei großen Texten SEHR langsam ist)
SELECT DISTINCT did, autor, titel, inhalt
FROM   dokindex
WHERE  wort LIKE '%systeme'
;

/******************************************************************************
 * Häufigskeitsverteilung der Wörter
 ******************************************************************************/

SELECT   wid, wort, COUNT(*) as anzahl
FROM     dokindex
GROUP BY wid, wort
ORDER BY anzahl DESC, wort ASC
;

SELECT   aid, attr, wid, wort, COUNT(*) as anzahl
FROM     dokindex
GROUP BY aid, attr, wid, wort
ORDER BY aid ASC, anzahl DESC, wort ASC
;