Requests for queries/Archives

~Kylu (u|t)

select * from cur 
where cur_namespace=0

UPDATE cur
SET cur.cur_text = replace(cur.cur_text, '[[Category:','[[Категория:')

SET cur.cur_text = replace(cur.cur_text, '[[:Category:','[[:Категория:')
SET cur.cur_text = replace(cur.cur_text, '[[Image:','[[Изображение:')
SET cur.cur_text = replace(cur.cur_text, '[[Template:','[[Шаблон:')
SET cur.cur_text = replace(cur.cur_text, '[[User:','[[Участник:')

SELECT cur_title
FROM cur
WHERE cur_namespace=0
AND cur_text LIKE '%stub}}%'
AND cur_text LIKE 'Croatia'
AND cur_text NOT LIKE '{{Croatia-stub}}'
AND cur_text NOT LIKE '{{Croatia-bio-stub}}'
AND cur_text NOT LIKE '{{Croatia-geo-stub}}';

SELECT page_title
FROM page, revision, text
WHERE page.page_id=revision.rev_page
AND revision.rev_text_id=text.old_id
AND page.page_namespace=0
AND page.page_is_redirect=0
AND text.old_text LIKE '%stub}}%'
AND text.old_text LIKE '%Bosnia%'
AND text.old_text LIKE '%Croat%'
AND text.old_text LIKE '%Macedonia%'
AND text.old_text LIKE '%Montenegr%'
AND text.old_text LIKE '%Serb%'
AND text.old_text LIKE '%Sloven%'
AND text.old_text LIKE '%Yugoslav%'
AND text.old_text NOT LIKE '%{{BiH-%'
AND text.old_text NOT LIKE '%{{BosniaHerzegovina-%'
AND text.old_text NOT LIKE '%{{Croatia-%'
AND text.old_text NOT LIKE '%{{Serbia%'
AND text.old_text NOT LIKE '%{{Slovenia-'%;

 SELECT c.cur_title FROM wikipedia.cur c
 WHERE c.cur_text LIKE "%[[Category:British MPs]]%";

select left(page_title,1),count(left(page_title,1)) from categorylinks,page where page_id=cl_from and cl_to='People_stubs' group by left(page_title,1) order by left(page_title,1);

 UPDATE categorylinks
 SET cl_sortkey = SUBSTRING(cl_sortkey,10)
 WHERE cl_sortkey LIKE 'Kategori:%';

select i.img_name from image i
 where not exists (
       select 1 from cur, links where cur_title in
         ('GFDL', 'Fairuse', 'Copyrighted', 'Unverified', 'Unknown', 'Verifieduse',
          'Noncommercial', 'PD') and cur_namespace = 8 and l_to = cur_id 
           and l_from = concat('Image:', img_name)
       )

CREATE TABLE wl_sj_tmp AS
SELECT  wl_title,  count(*) AS multiplicity
FROM watchlist
WHERE wl_namespace = 0
     (AND multiplicity > 1 )
GROUP BY (wl_title)

SELECT cur_title 
FROM cur
WHERE cur_namespace = 0
     AND  cur_title NOT IN  (SELECT wl_title FROM wl_sj_tmp)

SELECT cur_namespace, cur_title 
FROM cur 
LEFT OUTER JOIN watchlist 
 ON  cur_title = wl_title 
 AND cur_namespace = wl_namespace 
WHERE wl_title IS NULL;

UPDATE user SET user_name='akghetto' WHERE user_name='AKGhetto';

 select old_title as title, old_comment as comment, old_timestamp as timestamp from old
 where substr(old_user_text,0,7)='209.132'
 union
 select cur_title as title, cur_comment as comment, cur_timestamp as timestamp from cur
 where substr(cur_user_text,0,7)='209.132'

SELECT cur_id,cur_title FROM cur USE INDEX (cur_random) WHERE cur_namespace=0 AND cur_is_redirect=0 AND  cur_random>0.619759199498 ORDER BY cur_random LIMIT 1

1072: Key column 'cur_random' doesn't exist in table

select avg(char_length(cur_text)) from cur where cur_text like "%{{stub}}%"

select cur_title from cur where cur_text like "%{{stub}}%" and char_length(cur_text) > avg(char_length(cur_text))*2

SELECT old_title FROM old WHERE old_user="Ævar Arnfjörð Bjarmason" and old_namespace=0;

select old_namespace, old_title, old_user_text, old_timestamp, old_id from old where old_namespace=0 group by old_title having min(old_id)=old_id and old_user_text='Gerritholl';

go through all article discussion sites
for every user found on the discussion site, increment his discussion_participation_var by one
ignore any more contributions a user has done on the same discussion site (thus, only one discussion counts for one discussion site)


SELECT cur_title AS title, cur_user_text AS user_text, cur_timestamp AS t
FROM cur
WHERE DATE(t) = '2006-02-23' AND user_text LIKE '62.171.%'
UNION ALL
SELECT old_title AS title, old_user_text AS user_text, old_timestamp AS t
FROM old
WHERE DATE(t) = '2006-02-23' AND user_text LIKE '62.171.%'

SELECT CONCAT('Image:',img_name) as cur_title, 'en' as cur_namespace, img_width, img_height,img_size FROM image
WHERE (img_width > 1000 OR img_height > 1000)
AND img_size > 1000000
ORDER BY img_size DESC
LIMIT 1000 

select page_title , page_id from page where page_namespace=0 and page_is_redirect = 0 and page_id not in (select ll_from from langlinks)

SELECT cur_title
FROM cur 
WHERE cur_is_redirect=0 
AND cur_namespace=0
AND cur_text NOT LIKE '%{{ébauche}}%'
AND cur_text NOT LIKE '%{{Homonymie}}%'
ORDER BY LENGTH(cur_text) ASC
LIMIT 500

SELECT cur_title 
FROM cur 
WHERE cur_is_redirect=1 
AND cur_text LIKE "%[[catégorie:%"
LIMIT 100

mysql> SELECT cur_title
    ->  FROM cur
    ->  WHERE cur_is_redirect=1
    ->  AND cur_text LIKE "%[[catégorie:%"
    ->  LIMIT 100
    -> ;
+---------------------------+
| cur_title                 |
+---------------------------+
| Liste_de_meubles          |
| Économie_comportementale |
| Fiscalité                |
| Astéroïdes_Troyens      |
+---------------------------+
4 rows in set (0.39 sec)

SELECT cur_title 
FROM cur 
WHERE cur_is_redirect=0 
AND LENGTH(cur_text)<100
AND cur_namespace=0
AND cur_text NOT LIKE '%{{ébauche}}%'
LIMIT 100

SELECT cur_title 
FROM cur 
WHERE cur_is_redirect=0 
AND cur_namespace=0
AND cur_text NOT LIKE '%{{ébauche}}%'
ORDER BY LENGTH(cur_text) ASC
LIMIT 100

SELECT cur_title
FROM cur 
WHERE cur_is_redirect=0 
AND cur_namespace=0
AND LENGTH(cur_text) <100 AND cur_text NOT LIKE '%{{ébauche}}%'
ORDER BY LENGTH(cur_text) ASC

select concat( '#[[', art_title, ']] (', links_to, ' articles)' )
from art
where art.art_is_redirect = 1
and art.art_is_stub = 0
and art.art_is_disambig = 0
order by links_to desc 

SELECT user_name
FROM user
ORDER BY user_id DESC
LIMIT 200

 SELECT concat('<br/>[[fr:', cur_title, ']]') as link
 FROM cur 
 WHERE cur_is_redirect=0 
 AND cur_namespace=0
 AND LENGTH(cur_text)<200
 AND cur_text NOT LIKE '%{{ébauche}}%'
 AND cur_text NOT LIKE '%{{homonymie}}%'
 ORDER BY LENGTH(cur_text) ASC
 LIMIT 1000

SELECT cur_title
FROM cur 
WHERE cur_is_redirect=0 
AND cur_namespace=0
AND LENGTH(cur_text) <100 AND cur_text NOT LIKE '%{{ébauche}}%'
AND cur_text NOT LIKE '%{{homonymie}}%'
ORDER BY LENGTH(cur_text) ASC

[
 select
  concat('#[http://fr.wikipedia.org/wiki/',
  replace(replace(bl_to,'\'','%27'), '"','%22'),
  ' ',bl_to,'] - ') as title,
  count(distinct brokenlinks.bl_from) as value
 from
  brokenlinks,
  categorylinks
 where bl_from = cl_from
 and cl_to like '%Égypte_antique%'
 group by bl_to
 order by value desc
 LIMIT 100;

select cur_title
from cur
where cur_namespace = 0
and cur_text like '%howstuffworks.com%'
limit 200;

SELECT cur_namespace, cur_title
  FROM cur
 WHERE cur_namespace=4
   AND cur_is_redirect=0
 ORDER BY cur_title
 LIMIT 2000 TEST;

SELECT cur_namespace, cur_title FROM cur
WHERE cur_title REGEXP '[Bb]ad';

SELECT page_title AS protectedUserPages FROM page WHERE page_namespace = "2" AND page_restrictions = "move=sysop:edit=sysop";
SELECT page_title AS protectedUserPages FROM page WHERE page_namespace = "2" AND page_restrictions = "move=autoconfirmed:edit=sysop";
SELECT page_title AS protectedUserPages FROM page WHERE page_namespace = "2" AND page_restrictions = "move=sysop:edit=autoconfirmed";
SELECT page_title AS protectedUserPages FROM page WHERE page_namespace = "2" AND page_restrictions = "move=:edit=autoconfirmed";
SELECT page_title AS protectedUserPages FROM page WHERE page_namespace = "2" AND page_restrictions = "move=autoconfirmed:edit=";
SELECT page_title AS protectedUserPages FROM page WHERE page_namespace = "2" AND page_restrictions = "move=sysop:edit=";
SELECT page_title AS protectedUserPages FROM page WHERE page_namespace = "2" AND page_restrictions = "move=:edit=sysop";

SELECT cur_title
FROM cur 
WHERE cur_is_redirect=0 
AND cur_namespace=0
AND cur_text NOT LIKE '%{{archive}}%' AND
page_restrictions = "move=sysop:edit=sysop";
ORDER BY LENGTH(cur_text) ASC
LIMIT 2000

SELECT cl.cl_to,ll.ll_title 
FROM categorylinks cl 
LEFT JOIN (select ll_from,ll_title from langlinks where ll_lang='zh') ll 
ON (cl.cl_from=ll.ll_from )
WHERE cl.cl_to LIKE '%China%' OR 
      cl.cl_to LIKE '%Chinese%' 

UPDATE user SET user_rights='sysop' WHERE user_name='DX';

   (SQL query hidden)

To do[edit]

Query Log[edit]

Update this for new formats on download site[edit]

Email address on kw.wiktionary[edit]

Interwiki link counts[edit]

Namespace title translation[edit]

Stubs mentioning Croatia[edit]

Passwords/email of Guanaco's accounts[edit]

English-language Wikipedia; list of non-surname alphabeticised British MPs[edit]

Count articles in en:Category:People stubs (completed)[edit]

Fix number of total articles in Portuguese Wikibooks[edit]

Fix sort keys for subcategories in Danish Wikipedia[edit]

Update webalizer statistics for English Wikipedia[edit]

Refresh the special pages on Chinese wiki (zh)[edit]

Refresh the special pages on Catalan wiki (ca)[edit]

Untagged images[edit]

Untagged images[edit]

Watchlists[edit]

Wanted Pages - Only wanted once[edit]

Fix my username[edit]

Conflict investigation[edit]

Fix Wiki Indices[edit]

Average length of stub articles[edit]

List of all articles created by user foo[edit]

Number of discussion sites a user contributed for[edit]

Double Redirects in zh wp[edit]

Untagged images on sv. Wikipedia[edit]

Un-Interwikied articles/ctgr on :sl WP[edit]

Undo all editsmade by a specific person[edit]

Number of watchers on de: WP[edit]

Articles with trademark/copyright symbols[edit]

Tracking down some anon vandals (en)[edit]

Looking for large images[edit]

List of most linked redirects[edit]

List of pages I created[edit]

List of categories with no interwiki links[edit]

Number of Records in categorylinks[edit]

List of articles without interwiki links in "el:", Greek wikipedia[edit]

List of links on redirection pages on :sl[edit]

"Viable article" criteria[edit]

List of the 1000 most important articles our local Wikipedia has omitted[edit]

Changing the native name[edit]

Completed[edit]

Small articles that don't contain Stub or Disambiguate message[edit]

Redirect with Category on fr:[edit]

Small article that don't contain Stub message[edit]

Simple: linked-to redirects[edit]

Last 200 new users[edit]

Small articles that don't contain Stub message[edit]

Most wanted articles on Ancien Egypt category[edit]

Article endings[edit]

Interwiki: 500 articles[edit]

Links to Howstuffworks.com[edit]

All Pages in Wikipedia Namespace[edit]

Meta titles with "bad" in them[edit]

List of protected userpages on enwiki[edit]

refreshing[edit]

Admin abuse[edit]

Protected Pages Without Archive Tag[edit]

Users with 400[edit]

List of China/Chinese-related categories in enwiki and corresponding titles (could be null) in Chinese[edit]

Rejected requests[edit]

Do this please: (to the English WP)[edit]

Please[edit]