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('# [[User:',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 '%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%'
ORDER BY page_title;
#Resultset_12
USE plwiki_p;
SELECT CONCAT('# [[',page_title,']]') AS page_title, CONCAT('[[User:',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;
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.