Question
Suppose you need to search a wiki for pages containing the string "Barack Obama". Write an SQL query the performs this search using the relevant tables:
- page
- revision
- text
Pages archive ar id ar namespace INT ar title VARCHARO255) ar text MEDIUMBLOB ar comment VARBINARY(767) ar ser INT ar ser text VARCHAR (255) ar timestamp BINARY(14) ar minor edit TINYINT ar flags TINYBLOB ar rev id INT text id INT ar deleted TINYINT ar len INT ar page_id INT ar parent id INT ar sha1 VARBINARY(32) ar content model VARBINARY(32) ar content format VARBINARY(64) ndexes revision rev id INT rev-page rev text id INT rev comment VARBINARY(767) rev user INT rev user text VARCHAR(255) rev timestamp BINARY(14) rev minor edit TINYINT rev deleted TINYINT rev len INT rev sha1 VARBINARY(32) rev content model VARBINARY(32 rev content format VARBINARY(64) ndexes text old id INT old text MEDIUMBLOB old flags TINYBLOB ndexes Page props page pp page INT NT page id pp propname VARBINARY(60) page. namespace INT pp value BLOB page title VARCHAR (255) Opp sortkey FLOAT page restrictions TINYBLOB ndexes page is redirect TINYINT page is new TINYINT page restrictions page random DOUBLE pr id INT page touched BINARY(14 page links updated VARBINARY(14) pr page INT pr type VARBINARY(60) page latest INT page len INT pr level VARBINARY(60) page content model VARBINARY(32 pr cascade TINYINT page. lang VARBINARY(35) pr user INT pr expiry VARBINARY(14) ndexes protected titles mespace INT redirect category pt title VARCHAR(255) rd from INT cat id IN user INT rd namespace INT cat title VARCHAR (255) pt reason VARCHAR (767) rd title VARCHAR(255) INT pt timestamp BINARY(14) cat subcats INT rd interwiki VARCHAR(32) pt expiry VARBINARY(14) rd fragment VARCHAR (255 cat files INT pt create perm VARBINARY(60) ndexes ndexes
Explanation / Answer
select page_namespace,page_title from page,revision,text where old_text like '%Barack Obama%' and old_id=rev_text_id and rev_page=page_id and page_latest=rev_id;
