Main pl.wiki query 2

This query is marked as a draft This query has been published by The Polish.
SQL

#RESULTSET_10
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;
#RESULTSET_11
SELECT CONCAT('# [[',page_title,']]') as page_title
FROM page
LEFT JOIN categorylinks
ON cl_from = page_id
WHERE cl_from IS NULL
AND page_namespace = 0
AND page_is_redirect = 0;
#RESULTSET_12
SELECT CONCAT('# [[',p1.page_title,']]') AS page_title
FROM page p1
LEFT OUTER JOIN categorylinks
ON cl_from = p1.page_id
LEFT JOIN page p2
ON cl_to = p2.page_title AND p2.page_namespace = 14
LEFT JOIN page_props
ON p2.page_id = pp_page AND pp_propname = 'hiddencat'
WHERE p1.page_namespace = 0
AND p1.page_is_redirect = 0
AND p1.page_title NOT LIKE '%/%'
AND p1.page_title NOT LIKE 'Lista_planetoid%'
GROUP BY p1.page_title
HAVING COUNT(cl_from)-COUNT(pp_propname) = 0;
#RESULTSET_13
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, COUNT(*) AS count_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 count_tl_title DESC, tl_title ASC;
#RESULTSET_14
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 ASC, pl_title ASC;
#RESULTSET_15
SELECT CONCAT('# [[User:',page_title,']]') AS page_title
FROM page
LEFT JOIN user ON user_name = REPLACE(page_title, '_', ' ')
WHERE page_namespace = 2
AND page_is_redirect = 0
AND page_title NOT LIKE '%/%'
AND page_title NOT RLIKE '[0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}'
AND page_title NOT RLIKE '^([0-9A-F]{1,4}:){7,7}[0-9A-F]{1,4}'
AND user_name IS NULL
ORDER BY page_len DESC;
#RESULTSET_16
SELECT CONCAT('# [[User talk:',page_title,']]') AS page_title
FROM page
LEFT JOIN user ON user_name = REPLACE(page_title, '_', ' ')
WHERE page_namespace = 3
AND page_is_redirect = 0
AND page_title NOT LIKE '%/%'
AND page_title NOT RLIKE '[0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}'
AND page_title NOT RLIKE '^([0-9A-F]{1,4}:){7,7}[0-9A-F]{1,4}'
AND user_name IS NULL
ORDER BY page_len DESC;
#RESULTSET_17
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;
#RESULTSET_18
SELECT  CONCAT('# [[Dyskusja:',page_title,']]') AS page_title, page_len
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_len < 40
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;
#RESULTSET_19
SELECT DISTINCT CONCAT("# [[Wikipedia:" , page_title , ']]') as page_title
FROM page
JOIN templatelinks ON page_id = tl_from
WHERE page_title LIKE 'Poczekalnia/%2021:%'
AND page_namespace = 4
AND NOT EXISTS(
	SELECT * FROM templatelinks t1 WHERE page_title = t1.tl_title AND t1.tl_from_namespace = 4
);
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
