-- find publications for an author
SELECT
  dblp_type, title, booktitle,
  journal, pages, ee, year
FROM publ
  NATURAL JOIN auth
WHERE author = 'Chris Mayfield'
  AND dblp_type <> 'www'
ORDER BY year DESC;

-- list proceedings of SIGMOD 2010
SELECT
  dblp_key, title, pages, ee
FROM publ
  NATURAL JOIN cite
WHERE crossref = 'conf/sigmod/2010'
ORDER BY split_part(pages, '-', 1)::integer;

-- example FTS query (no index)
SELECT * FROM publ
WHERE title @@ 'clean data';

-- better FTS query (with index)
SELECT *
FROM publ
  NATURAL JOIN publ_fts
WHERE title_tsv @@ plainto_tsquery('clean data');

-- example of ts_stat function
SELECT *
FROM ts_stat('SELECT title_tsv FROM publ_fts')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 100;

-- FTS function in FROM clause
SELECT
  dblp_key,
  ts_headline(title, tsq) AS body,
  ts_rank(title_tsv, tsq) AS rank
FROM publ NATURAL JOIN publ_fts,
  plainto_tsquery('approach for cleaning data') AS tsq
WHERE title_tsv @@ tsq
ORDER BY rank DESC;

-- frequency based spell check
SELECT * FROM stats
WHERE word % 'algorithum'
ORDER BY ndoc DESC
LIMIT 5;
