Untitled query #16664

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

USE dawiki_p;  #just means 'use this wiki'
#SHOW TABLES;
#DESCRIBE page;
#SELECT * FROM page LIMIT 10;   #make sure to limit search, otherwise we'll get everything back
#SELECT * FROM revision LIMIT 10;  #this is from revision table - this is much bigger than the pages one
#SELECT * FROM revision ORDER by rev_timestamp DESC LIMIT 10;   #show in time order - the 'DESC' or 'ASC' call comes after
#SELECT rev_page FROM revision ORDER by rev_timestamp DESC limit 10;

	#I want rev_page, page_title, and rev_timestamp - however, page_title is in revision table and the others in page table
    #so I get in one and the join the other and say where to join
#SELECT rev_page, page_title, rev_timestamp FROM revision JOIN page ON rev_page = page_id ORDER by rev_timestamp ASC limit 10;

#SELECT * FROM user limit 10;	#note that most fields here I'm not authorised to access (e.g. password)

	#here's how to get information from two tables
#SELECT rev_page, page_title, rev_timestamp, rev_user, user_name FROM revision JOIN page ON rev_page = page_id JOIN user ON rev_user= user_id ORDER by rev_timestamp ASC limit 10;

	#COUNT counts how many occurrences there are of a given thing - here it's how many entries in rev_id are there where
    #  rev_user equals 66249?
#SELECT COUNT(rev_id) FROM revision WHERE rev_user = "12";

	#13. this puts everything with the same revision user together, and then it counts
#SELECT COUNT(rev_id) FROM revision GROUP by rev_user LIMIT 10;

	#14. this names the column with the editcounts 'editcounts' - top 10 users w/ largest number of edits
#SELECT COUNT(rev_id) AS editcounts FROM revision GROUP by rev_user ORDER BY editcounts DESC LIMIT 10;

	#15. this gives the counts from rev_id (renamed as editcounts) and rev_user, and then then adds user_name
    #	from the user table. Group the counts by user and order it by editcounts
#SELECT COUNT(rev_id) AS editcounts, rev_user, user_name FROM revision JOIN user ON user_id = rev_user GROUP by rev_user ORDER BY editcounts DESC LIMIT 10;

	#16. This kind of does the same as before, but excludes usernames that include bot - query needs revision to exclude
    #case sensitivity
#SELECT COUNT(rev_id) AS editcounts, rev_user, user_name, user_registration FROM revision JOIN user ON user_id = rev_user WHERE user_name NOT LIKE "%bot%" AND user_name NOT LIKE "%Bot%" GROUP by rev_user ORDER BY editcounts DESC LIMIT 10;

	#17. this can be used to find where a certain user names is something
#SELECT COUNT(rev_id) AS editcounts, page_title FROM revision JOIN user ON rev_user = user_id JOIN page ON page_id  = rev_page WHERE user_name="Auntof6" GROUP BY rev_page ORDER BY editcounts DESC LIMIT 10;

	#18. you can also specify that the namespace should be something specific!
#SELECT COUNT(rev_id) AS editcounts, page_title FROM revision JOIN user ON rev_user = user_id JOIN page ON page_id  = rev_page WHERE user_name="Auntof6" and page_namespace = "0" GROUP BY rev_page ORDER BY editcounts DESC LIMIT 10;

	#19. this does something else
#SELECT pl_from, page_title FROM pagelinks JOIN page ON page_id = pl_from WHERE pl_title = "Oxford" AND pl_namespace = "0" LIMIT 10;

	#20. quiz 1. this gets the page titles that are linked to from the oxford page
#SELECT  pl_title FROM pagelinks JOIN page ON page_id = pl_from WHERE page_title = "Oxford" AND pl_namespace = "0" and page_namespace = "0" LIMIT 10;

SELECT COUNT(*) AS linkscount, pl_title FROM pagelinks WHERE pl_namespace = "0" GROUP BY pl_title ORDER BY linkscount DESC LIMIT 10;
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...
