Fork of
Untitled query #10058
by Sfan00 IMG
This query is marked as a draft
This query has been published
by ShakespeareFan00.
SQL
x
USE enwiki_p;
SELECT p.page_id,p.page_len,CONCAT("File:",p.page_title) FROM page p
LEFT JOIN page_restrictions ON pr_page = p.page_id
LEFT JOIN categorylinks c2 ON c2.cl_from = p.page_id AND c2.cl_to IN (
'Wikipedia_restricted_images',
'DYK_images',
'Wikipedia_image_placeholders',
'Wikipedia_restricted_images',
'Featured_pictures',
'Wikipedia_former_featured_pictures',
'Featured_pictures_on_Mathematics_Portal',
'Featured_pictures_of_Star_Portal',
'Wikipedia_Picture_of_the_day_files',
'All_non-free_media',
'Wikipedia_awards',
'All_replaceable_non-free_use_Wikipedia_files',
'Non-free_images_for_NFUR_review',
'All_redirects_for_discussion',
'Wikipedia_files_for_discussion',
'All_files_proposed_for_deletion',
'Duplicate_Wikipedia_files',
'Wikipedia_files_requiring_splitting',
'Unclaimed_images_thought_to_be_uploader',
'All_Wikipedia_files_missing_evidence_of_permission',
'All_Wikipedia_files_with_unknown_copyright_status',
'All_Wikipedia_files_with_unknown_source',
'All_Wikipedia_files_with_the_same_name_on_Wikimedia_Commons',
'All_Wikipedia_files_with_a_different_name_on_Wikimedia_Commons',
'Candidates_for_speedy_deletion',
'Candidates_for_speedy_deletion_as_copyright_violations',
'Candidates_for_speedy_deletion_as_copyright_violations',
'Candidates_for_uncontroversial_speedy_deletion',
'Items_with_OTRS_permission_confirmed',
'Copy_to_Wikimedia_Commons_(inline-identified)',
'Self-published_work',
##'Copy_to_Wikimedia_Commons',
##'Copy_to_Wikimedia_Commons_(bot-assessed)',
##'Copy_to_Wikimedia_Commons_reviewed_by_ShakespeareFan00'
'Public_domain_images_no_longer_eligible_for_claim_of_authorship',
'Stamp_images_published_abroad_that_are_in_the_public_domain_in_the_United_States',
'Author_died_more_than_70_years_ago_public_domain_files',
'Images_in_the_public_domain_in_the_United_States',
'Pre-1978_without_copyright_notice_US_public_domain_images',
'Public_domain_files_ineligible_for_copyright',
'Public_domain_images_ineligible_for_copyright_(shape)',
'Public_domain_images_ineligible_for_copyright_(logo)',
'Public_domain_images_ineligible_for_copyright_(simple)',
'Author_died_more_than_100_years_ago_public_domain_files',
'United_States_government_images',
'NASA_images',
'National_Oceanic_and_Atmospheric_Administration_images',
'United_States_Air_Force_images',
'Images_of_United_States_military_awards',
'United_States_military_images',
'United_States_Navy_images',
'United_States_Army_images',
'United_States_Army_Center_of_Military_History_documents_and_images',
'United_States_Coast_Guard_images',
'United_States_Geological_Survey_images',
## 'United_States_Department_of_Justice_images',
## 'United_States_Department_of_State_images',
## 'CIA_World_Factbook_images',
## 'Centers_for_Disease_Control_and_Prevention_images',
## 'United_States_Environmental_Protection_Agency_images',
## 'National_Park_Service_images',
## 'National_Institutes_of_Health_images',
## 'United_States_Census_Bureau_images',
## 'United_States_Department_of_Transportation_images',
## 'United_States_Environmental_Protection_Agency_images',
'Screenshots_of_Wikipedia',
'Creative_Commons_Attribution_files',
'Creative_Commons_Attribution_2.0_files',
'Creative_Commons_Attribution_2.5_files',
'Creative_Commons_Attribution_3.0_files',
'Creative_Commons_Attribution_4.0_files',
'Creative_Commons_Attribution-ShareAlike_files',
'Creative_Commons_Attribution-ShareAlike_2.5_files',
'Creative_Commons_Attribution-ShareAlike_2.0_files',
'Creative_Commons_Attribution-ShareAlike_3.0_files',
'Creative_Commons_Attribution-ShareAlike_4.0_files',
'CC-zero_files',
'Copyright_holder_released_public_domain_files',
'GFDL_files',
'FAL_files',
'CopyrightedFreeUse-Link'
)
LEFT JOIN templatelinks t ON t.tl_from = p.page_id
AND t.tl_namespace = 10
AND t.tl_title IN (
## featured content
'Featured_sound',
'Former_featured_sound',
'FSC',
'Gujarat_selected_picture',
'FPCold',
'VPCold',
'Thailandportal',
'Asian_Games_selected_picture',
'Picture_of_the_day',
'SP_Massachusetts',
'SP_India_UP',
'Nanotech_selected',
'Fishportal',
'Brazilian_selected_picture',
'USMCportal',
'PISPC',
## Other local tags for files at Commons (and eslewhere)
'Qxz-adnavbox',
'DYKfile',
'Wikipedia-adnavbox',
## OTRS
##'OTRS_pending',
##'OTRS_received',
## Non-free media
'NFUR_not_needed',
## 'Out_of_copyright_in',
## 'License tags needing manual review
'PD-user',
'PD-US-1923-abroad',
'Information',
'Image_information_art',
'Painting',
'Artwork',
'Spoken_article_entry',
'Google_Art_Project'
)
WHERE t.tl_from IS NULL
AND c2.cl_from IS NULL
AND pr_page IS NULL
AND p.page_is_redirect = FALSE
AND p.page_namespace = 6
AND p.page_len > 0
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.