SQL
AخA
#RESULTSET_1
USE plwiki_p;
SELECT CONCAT("# [[Dyskusja:",p1.page_title,"]]") AS page_title
FROM page AS p1
WHERE p1.page_title NOT LIKE '%/%'
AND p1.page_namespace = 1
AND NOT EXISTS (SELECT 1
FROM page AS p2
WHERE p2.page_namespace = 0
AND p1.page_title = p2.page_title);
#RESULTSET_3
USE plwiki_p;
SELECT concat("# [[Dyskusja:",page_title,"]]") AS page_title
FROM plwiki_p.page
WHERE page_namespace = 1 AND page_len = 0;
#RESULTSET_4
USE plwiki_p;
SELECT CONCAT('# [[',page_title,']]') AS page_title, CONCAT('[[',rd_title,']]') AS rd_title
FROM page
JOIN redirect
ON rd_FROM = page_id
AND NOT EXISTS (SELECT * FROM page AS p1 WHERE p1.page_title = rd_title AND p1.page_namespace = rd_namespace)
AND page_namespace = 0
AND rd_namespace = 0;
#RESULTSET_6
USE plwiki_p;
SELECT CONCAT('# [[Wikipedysta:',page_title,']]') AS page_title
FROM page, templatelinks
WHERE tl_from_namespace = 2
AND tl_namespace = 10
AND page_id = tl_from
AND tl_title LIKE 'DNU'
AND page_title NOT LIKE '%Pablo000%'
GROUP BY page_title;
#RESULTSET_8
USE plwiki_p;
SELECT CONCAT("# [[",page_title,"]]") AS page_title
FROM page
WHERE page_namespace = 0 AND page_len = 0;
#RESULTSET_9
USE plwiki_p;
SELECT CONCAT('# [[Specjalna:Linkujące/Szablon:',tl_title,']]') AS tl_title
FROM templatelinks, page
WHERE page_latest = 0
AND tl_from_namespace = 0
AND tl_namespace = 10
AND page_id = tl_from
AND tl_title NOT LIKE 'Toc'
AND tl_title NOT LIKE 'Tc';
#RESULTSET_10
USE plwiki_p;
SELECT distinct CONCAT('# [[:Kategoria:',page_title,']]') AS page_title, rev_timestamp
FROM categorylinks
RIGHT JOIN page
ON cl_to = page_title
JOIN revision
ON rev_page = page_id
WHERE page_namespace = 14
AND rev_parent_id = 0
AND cl_to IS NULL
AND page_title NOT LIKE '?'
AND page_title NOT LIKE 'Łatki_-_Kilka_plików'
AND page_title NOT LIKE '%infobo%'
AND page_title NOT LIKE '%artykuł%'
AND page_title NOT LIKE '%Artykuł%'
AND page_title NOT LIKE '%Dyskusj%'
AND page_title NOT LIKE '%Stron%'
AND page_title NOT LIKE '%Hasł%'
AND page_title NOT LIKE '%Literatura_uzupełniająca%'
AND page_title NOT LIKE '%Plik%'
AND page_title NOT LIKE '%nieprawidłowej_przestrzeni%'
AND page_title NOT LIKE '%Moduł%'
AND page_title NOT LIKE '%Szablon%'
AND page_title NOT LIKE '%szablon%'
AND page_title NOT LIKE '%Kategori%'
AND page_title NOT LIKE '%kategori%'
AND page_title NOT LIKE '%Wikiprojekt%'
AND page_title NOT LIKE '%Podano_IUCN%'
AND page_title NOT LIKE '%Wikida%'
AND page_title NOT LIKE '%Wikipedyści%'
AND page_title NOT LIKE '%Parametr%'
AND page_title NOT LIKE '%łaściwoś%'
AND page_title NOT LIKE '%Linki%'
AND page_title NOT LIKE '%Coor%'
AND page_title NOT LIKE '%Zdjęci%'
AND page_title NOT LIKE '%autorytatywna%'
AND page_title NOT LIKE '%Wikipedii%'
AND page_title NOT LIKE '%PANDA%'
AND page_title NOT LIKE '%NPA%'
AND page_title NOT LIKE '%Ekspresowe_kasowanie%'
AND page_title NOT LIKE '%weryfikacji%'
AND page_title NOT LIKE '%Błęd%'
AND page_title NOT LIKE '%błęd%'
AND page_title NOT LIKE '%Medalowe%'
AND page_title NOT LIKE '%podano%'
AND page_title NOT LIKE '%Ombox%'
AND page_title NOT LIKE '%Pomniejsz%'
AND page_title NOT LIKE '%Przekierowania%'
AND page_title NOT LIKE '%brak%'
AND page_title NOT LIKE '%User%'
AND page_title NOT LIKE '%uzupełnienia%'
AND page_title NOT LIKE '%Przyszłe%'
AND page_title NOT LIKE '%Niezablokowane%'
AND page_title NOT LIKE '%Dorzecze_Tille%'
AND page_title NOT LIKE '%nieznane_parametry%'
AND page_title NOT LIKE '%Głosowani%'
AND page_title NOT LIKE '%wydarzenia_w_sporcie%'
AND page_title NOT LIKE '%wydarzenia_sportowe%'
AND page_title NOT LIKE '%Zdegenerowana_galeria%'
ORDER BY rev_timestamp;
#RESULTSET_11
USE plwiki_p;
SELECT CONCAT('# [[',page_title,']]') AS page_title, CONCAT('[[Wikipedysta:',rd_title,']]') AS rd_title
FROM page
JOIN redirect
ON rd_FROM = page_id
AND page_namespace = 0
AND rd_namespace = 2;
#RESULTSET_12
USE plwiki_p;
SELECT CONCAT('# [[',page_title,']]') AS page_title, pp_propname
FROM page_props
JOIN page
ON page_id = pp_page
WHERE page_namespace = 0
AND pp_propname IN ('index', 'newsectionlink', 'noeditsection', 'nonewsectionlink', 'notitleconvert')
AND page_title NOT LIKE '%według_pierwiastków%';
#RESULTSET_13
USE plwiki_p;
SELECT CONCAT("# [[",page_title,"]]") AS page_title, GROUP_CONCAT(cl_to SEPARATOR ',') AS cl_to
FROM categorylinks
JOIN page ON cl_from=page_id
WHERE cl_to rlike "Urodzeni_w_1.*"
AND page_title NOT LIKE '%_i_%'
AND page_title NOT LIKE 'Bracia%'
AND page_title NOT LIKE 'Honor_Blackman'
AND page_title NOT LIKE 'Samuel_Shelley'
AND page_title NOT LIKE 'Joyce_Redman'
AND page_title NOT LIKE 'Zhu_Da'
AND page_title NOT LIKE 'Thomas_Morley'
AND page_title NOT LIKE 'Stefan_Pogonowski'
AND page_title NOT LIKE 'Rojda_Felat'
AND page_title NOT LIKE 'Jan_Horodeński'
AND page_title NOT LIKE 'Jason_Derek_Brown'
AND page_title NOT LIKE 'Jan_Kanty_Szwedkowski'
GROUP BY cl_from
HAVING COUNT(cl_to)>1;
#RESULTSET_14
USE plwiki_p;
SELECT CONCAT("# [[",page_title,"]]") AS page_title, GROUP_CONCAT(cl_to SEPARATOR ',') AS cl_to
FROM categorylinks
JOIN page ON cl_from=page_id
WHERE cl_to rlike "Zmarli_w_1.*"
AND page_title NOT LIKE '%_i_%'
AND page_title NOT LIKE 'Bracia%'
AND page_title NOT LIKE 'Spytko_IV_z_Melsztyna'
GROUP BY cl_from
HAVING COUNT(cl_to)>1;
#RESULTSET_15
USE plwiki_p;
SELECT CONCAT('# [[:Kategoria:',cat_title,']]') AS cat_title, cat_pages
FROM page, category
WHERE page_title = cat_title
AND page_namespace = 14
AND page_is_redirect = 1;
#RESULTSET_16
USE plwiki_p;
SELECT CONCAT('# [[:Kategoria:',page_title,']]') AS page_title
FROM page
LEFT OUTER JOIN categorylinks
on cl_FROM = page_id
WHERE cl_FROM is null
AND page_namespace = 14
AND page_title NOT LIKE 'Kategorie'
AND page_title NOT LIKE '%wstawienia%'
AND page_title NOT LIKE 'Wikipedysta:Pablo000/dyskusja';
#RESULTSET_17
USE plwiki_p;
SELECT DISTINCT CONCAT('# [[',page_title,']]') AS page_title
FROM page
JOIN imagelinks
ON page_id = il_from
WHERE (NOT EXISTS(
SELECT 1
FROM image
WHERE img_name = il_to))
AND (NOT EXISTS(
SELECT
1
FROM commonswiki_p.page
WHERE page_title = il_to
AND page_namespace = 6))
AND page_namespace = 0;
#RESULTSET_19
USE plwiki_p;
SELECT CONCAT('# [[:Kategoria:',cl_to,'|Kategoria:',cl_to,']]') AS cl_to, COUNT(*) AS count_cl_to
FROM page, categorylinks
WHERE page_id = cl_from
AND page_namespace = 0
AND NOT EXISTS
(SELECT * FROM page AS p1
WHERE cl_to = p1.page_title
AND p1.page_namespace = 14)
GROUP BY cl_to
ORDER BY count_cl_to DESC, cl_to ASC;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.