/****************************************************************************** * 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 'mmp%' 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 ;