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 page_title NOT LIKE 'Bazylika_św._Heleny_w_Birkirkara'
AND page_title NOT LIKE 'Wieża_Delimara'
AND page_title NOT LIKE 'Gaspare_Visconti'
AND page_title NOT LIKE 'Wieża_Bubaqra'
AND page_title NOT LIKE 'Wieża_Cavalier'
AND page_title NOT LIKE 'Wieża_św._Cecylii'
AND NOT EXISTS (SELECT 1
FROM page AS p2
WHERE p2.page_namespace = 0
AND p1.page_title = p2.page_title)
ORDER BY page_title;
#RESULTSET_2
USE plwiki_p;
SELECT CONCAT('# [[Dyskusja:',page_title,']]') AS page_title
FROM page, templatelinks
WHERE tl_from_namespace = 1
AND tl_namespace = 10
AND page_id = tl_from
AND tl_title LIKE 'Bez_infoboksu'
AND page_title IN (
SELECT page_title
FROM page, templatelinks
WHERE tl_from_namespace = 0
AND tl_namespace = 10
AND page_id = tl_from
AND tl_title LIKE '%infobox%'
)
ORDER BY page_len, page_title;
#RESULTSET_3
USE plwiki_p;
SELECT CONCAT('# [[:Kategoria:',cl_to,'|Kategoria:',cl_to,']]') AS cl_to
FROM page, categorylinks
WHERE page_id = cl_from
AND page_namespace = 0
AND cl_to NOT LIKE 'Polacy_odznaczeni_francuskim_Krzyżem_Wojennym'
AND cl_to NOT LIKE 'Majorzy_II_Rzeczypospolitej'
AND cl_to NOT LIKE 'Szemborowo'
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 cl_to;
#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
AND page_title NOT LIKE 'Club_Deportes_TrasANDino'
AND page_title NOT LIKE 'Club_Deportivo_Santamarina'
AND page_title NOT LIKE 'Naval_Talcahuano'
AND page_title NOT LIKE 'Club_Deportivo_y_Social_Naval'
AND page_title NOT LIKE 'Teodorshof'
AND page_title NOT LIKE 'Palazzo_Bubaqra'
AND page_title NOT LIKE 'Wieża_św._Lucjana'
AND page_title NOT LIKE 'Wieża_św._Lukiana'
AND page_title NOT LIKE 'Cavalier_Tower'
AND page_title NOT LIKE 'Wieża_Św._Jerzego'
ORDER BY page_title, rd_title;
#RESULTSET_5
USE plwiki_p;
SELECT CONCAT("# [[",page_title,"]]") AS page_title, CONCAT("[[",pl_title,"]]") AS pl_title
FROM pagelinks, page
WHERE pl_title NOT LIKE '%(%)%'
AND pl_title NOT LIKE '%:)%'
AND pl_title NOT LIKE '%:-)%'
AND pl_title NOT LIKE '%)))%'
AND pl_title NOT LIKE '%Pre)Thing%'
AND pl_FROM = page_id
AND pl_from_namespace = 0
AND pl_namespace = 0
AND (pl_title LIKE '%)%)%' OR pl_title LIKE '%(%(%' OR pl_title LIKE '%(%' OR pl_title LIKE '%)%')
#GROUP BY pl_title
ORDER BY page_title, pl_title;
#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
ORDER BY page_title;
#RESULTSET_7
USE plwiki_p;
SELECT DISTINCT CONCAT("# [[Wikipedia:" , page_title , ']]') AS page_title
FROM page
JOIN templatelinks ON page_id = tl_from
JOIN categorylinks ON page_id = cl_from
WHERE page_title LIKE 'Poczekalnia/%'
AND page_title NOT LIKE 'Poczekalnia/Załatwione%'
AND page_title NOT LIKE 'Poczekalnia/Zgłoszenia%'
AND page_title NOT LIKE 'Poczekalnia/preload%'
AND page_title NOT LIKE 'Poczekalnia/wstęp'
AND page_title NOT LIKE 'Poczekalnia/archiwa'
AND page_title NOT LIKE 'Poczekalnia/intro'
AND page_title NOT LIKE 'Poczekalnia/podstrona_załatwione'
AND page_title NOT LIKE 'Poczekalnia/Dyskusja_nad_szablonami'
AND page_title NOT LIKE 'Poczekalnia/Kategorie'
AND page_title NOT LIKE 'Poczekalnia/Szablony'
AND page_title NOT LIKE 'Poczekalnia/schowek_na_postacie_fikcyjne'
AND page_title NOT LIKE 'Poczekalnia/naprawa_załatwione_24'
AND page_title NOT LIKE 'Poczekalnia/naprawa'
AND page_namespace = 4
AND NOT EXISTS(
SELECT * FROM templatelinks t1 WHERE page_title = t1.tl_title #AND t1.tl_from_namespace = 4
#LIMIT 1
)
ORDER 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
ORDER BY page_title;
#RESULTSET_9
USE plwiki_p;
SELECT CONCAT('# [https://pl.wikipedia.org/w/index.php?title=Specjalna%3ALinkujące&hidelinks=1&target=Szablon:',tl_title,'&namespace=0 ',tl_title,']') AS tl_title
FROM page, templatelinks
WHERE 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'
AND tl_title NOT LIKE 'Francuska_okupacja_Malty'
AND tl_title NOT LIKE 'Mapa_dane_Polska'
AND NOT EXISTS
(SELECT * FROM page AS p1
WHERE p1.page_namespace = tl_namespace
AND p1.page_title = tl_title)
GROUP BY tl_title
ORDER BY tl_title;
#RESULTSET_10
USE plwiki_p;
SELECT concat("# [[Dyskusja:",page_title,"]]") AS page_title
FROM plwiki_p.page
WHERE page_namespace = 1 AND page_len = 0
ORDER BY page_title;
#RESULTSET_11
USE plwiki_p;
SELECT distinct CONCAT('# [[:Kategoria:',page_title,']]') AS page_title
FROM categorylinks#, page, revision
RIGHT JOIN page
ON cl_to = page_title
JOIN revision
ON rev_page = page_id
WHERE page_namespace = 14
#AND page_is_redirect = 1
AND rev_parent_id = 0
AND cl_to IS NULL
AND page_title NOT LIKE '?'
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łosowania_nad_Listami_na_medal%'
AND page_title NOT LIKE '%wydarzenia_w_sporcie%'
AND page_title NOT LIKE '%wydarzenia_sportowe%'
AND page_title NOT LIKE '%Zdegenerowana_galeria%'
ORDER BY page_title;
#RESULTSET_12
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
ORDER BY page_title, rd_title;
#RESULTSET_13
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_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 "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 'Joyce_Redman'
AND page_title NOT LIKE 'Zhu_Da'
#WHERE cl_to like "%dzimušie" AND cl_to NOT LIKE "%._gadā_dzimušie"
GROUP BY cl_from
HAVING COUNT(cl_to)>1;
#RESULTSET_15
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'
#WHERE cl_to like "%dzimušie" AND cl_to NOT LIKE "%._gadā_dzimušie"
GROUP BY cl_from
HAVING COUNT(cl_to)>1;
#RESULTSET_16
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
ORDER BY cat_pages DESC, cat_title ASC;
#RESULTSET_17
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 'Wikipedysta:Pablo000/dyskusja';
#RESULTSET_18
USE plwiki_p;
SELECT DISTINCT CONCAT('# [[',page_title,']]') AS page_title/*, CONCAT('# [[:Plik:',il_to,']]') AS il_to*/
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 tl_namespace, CONCAT('{{s|',tl_title,'}}') AS tl_title, COUNT(*) AS count_tl_title, CONCAT('[https://pl.wikipedia.org/w/index.php?title=Specjalna%3ALinkujące&hidelinks=1&target=Szablon:',tl_title,'&namespace=0 Linkujące]') AS tl_title_url
FROM page, templatelinks
WHERE 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'
AND tl_title NOT LIKE 'Lista_planetoid%'
AND NOT EXISTS
(SELECT * FROM page AS p1
WHERE p1.page_namespace = tl_namespace
AND p1.page_title = tl_title)
GROUP BY tl_title
ORDER BY count_tl_title DESC, tl_title 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.