Untitled query #16665

SQL

#What databases exist?
	#SHOW DATABASES;
#Select a database
USE miwiki_p;

Select * from revision LIMIT 20;
Select * from page LIMIT 20;
Select * from user LIMIT 20;


SELECT COUNT(DISTINCT rev_user) AS usercounts, page_title FROM revision JOIN page ON page_id = rev_page WHERE page_namespace = "0" group by rev_page LIMIT 10;

#How many distinct editors does each page have?
#For each page in page (=article) with namespace = 0, count number of distinct editors that have edited each article (some have many editors, some have very few) - distinct humans…, from revision table



#Show all tables in the database
	#SHOW TABLES;
#Get info about a table
	#DESCRIBE page; 
#Select everything from a table (or some column), limit the number of entires like .head()
	#SELECT * FROM page LIMIT 10
#Order table
	#SELECT * FROM revision ORDER by rev_timestamp LIMIT 10
#Order table but descending
	#SELECT rev_page FROM revision ORDER by rev_timestamp DESC limit 10
#Join tables
	#SELECT rev_page, page_title, rev_timestamp FROM revision JOIN page ON rev_page = page_id ORDER by rev_timestamp ASC limit 10;
    
#16    
#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 DESC LIMIT 10000;

#18
#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;
SQL

Checking query status...
