Given names lacking an article

Fork of First words of non-redirect article titles with more than 50 occurrances by Ahecht

This query is marked as a draft This query has been published by Certes.

This query attempts to list given names lacking an article.  It's not foolproof but should be useful.  The Chinese bit filters out people such as Xu Chen, whose first name has no given-name article because it is a surname.  (Sadly, it also filters out names such as Lee which are both a given name and a Chinese surname.)

SQL

SELECT COUNT(DISTINCT Pb.page_id) AS people, SUBSTRING_INDEX(Pb.page_title, "_", 1) AS given_name
FROM categorylinks Cb
JOIN page Pb ON Pb.page_id = Cb.cl_from AND Pb.page_is_redirect = 0 AND Pb.page_namespace = 0
WHERE Cb.cl_type = 'page' AND Cb.cl_to <= "9999" AND Cb.cl_to REGEXP "^\\d+_births$"
GROUP BY given_name
HAVING given_name NOT REGEXP "\\.$" AND people >= 50
AND NOT EXISTS (SELECT 1 FROM page Pg, categorylinks Cg
 WHERE Pg.page_namespace = 0 AND Pg.page_title = given_name
 AND Cg.cl_from = Pg.page_id AND Cg.cl_type = 'page' AND Cg.cl_to REGEXP ("[gG]iven_name|(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
AND NOT EXISTS (SELECT 1 FROM page Pf, redirect, page Pr, categorylinks Cr
 WHERE Pf.page_namespace = 0 AND Pf.page_title = given_name
 AND rd_from = Pf.page_id AND rd_namespace = 0
 AND Pr.page_namespace = 0 AND Pr.page_title = rd_title
 AND Cr.cl_from = Pr.page_id AND Cr.cl_type = 'page' AND Cr.cl_to REGEXP ("[gG]iven_name|(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
AND NOT EXISTS (SELECT 1 FROM page Pg, categorylinks Cg
 WHERE Pg.page_namespace = 0 AND Pg.page_title = CONCAT(given_name, "_(given_name)")
 AND Cg.cl_from = Pg.page_id AND Cg.cl_type = 'page' AND Cg.cl_to REGEXP ("[gG]iven_name"))
AND NOT EXISTS (SELECT 1 FROM page Pf, redirect, page Pr, categorylinks Cr
 WHERE Pf.page_namespace = 0 AND Pf.page_title = CONCAT(given_name, "_(given_name)")
 AND rd_from = Pf.page_id AND rd_namespace = 0
 AND Pr.page_namespace = 0 AND Pr.page_title = rd_title
 AND Cr.cl_from = Pr.page_id AND Cr.cl_type = 'page' AND Cr.cl_to REGEXP ("[gG]iven_name"))
AND NOT EXISTS (SELECT 1 FROM page Pg, categorylinks Cg
 WHERE Pg.page_namespace = 0 AND Pg.page_title = CONCAT(given_name, "_(name)")
 AND Cg.cl_from = Pg.page_id AND Cg.cl_type = 'page' AND Cg.cl_to REGEXP ("[gG]iven_name|(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
AND NOT EXISTS (SELECT 1 FROM page Pf, redirect, page Pr, categorylinks Cr
 WHERE Pf.page_namespace = 0 AND Pf.page_title = CONCAT(given_name, "_(name)")
 AND rd_from = Pf.page_id AND rd_namespace = 0
 AND Pr.page_namespace = 0 AND Pr.page_title = rd_title
 AND Cr.cl_from = Pr.page_id AND Cr.cl_type = 'page' AND Cr.cl_to REGEXP ("[gG]iven_name|(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
 AND NOT EXISTS (SELECT 1 FROM page Pg, categorylinks Cg
 WHERE Pg.page_namespace = 0 AND Pg.page_title = CONCAT(given_name, "_(surname)")
 AND Cg.cl_from = Pg.page_id AND Cg.cl_type = 'page' AND Cg.cl_to REGEXP ("(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
AND NOT EXISTS (SELECT 1 FROM page Pf, redirect, page Pr, categorylinks Cr
 WHERE Pf.page_namespace = 0 AND Pf.page_title = CONCAT(given_name, "_(surname)")
 AND rd_from = Pf.page_id AND rd_namespace = 0
 AND Pr.page_namespace = 0 AND Pr.page_title = rd_title
 AND Cr.cl_from = Pr.page_id AND Cr.cl_type = 'page' AND Cr.cl_to REGEXP ("(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
ORDER BY people DESC, given_name;

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...