Databases Homework

1. Databases Homework • Go to http://sqlzoo.net/ • Do as much as you can in 2 hours • Let me know what you didby email • No need to send me lots of screen shots • Just summarize your experience (good, bad, ugly)

2. Cloud Computing: Cosmos, Scope http://research.microsoft.com/en-us/um/people/jrzhou/pub/Scope.pdf SELECT query, COUNT(*) AS count FROM "search.log" USING LogExtractor GROUP BY query HAVING count > 1000 ORDER BY count DESC; OUTPUT TO "qcount.result";

3. SQL: Stupid Query Languagehttp://www.skrause.org/humor/computerjargon.shtml http://www.w3schools.com/sql/default.asp

4. 125M Tweets mysql -utfinin -ppsdHsyxwNXNJTMFttfininshow tables; describe statuses;select text from statuses limit 10;select text from statuses like ‘%joke%’ limit 10; select name from users where name > ‘aaa’ and name < ‘aab’describe statuses; describe users;select id,name from users limit 10;select user_id,text from statuses limit 10;select count(*) from users;select count(*) from statuses;

5. Joins select name, text from users u, statuses s where u.id = s.user_id and text like '%joke%' limit 10; select user_id, count(*) as n from statuses group by user_id having n > 10000 order by n desc; create view heavy_users as select user_id, count(*) as n from statuses group by user_idhaving n > 10000; select * from heavy_users; select name,n from users,heavy_userswhere users.id = heavy_users.user_idorder by n desc; select name,text from users,statuses where users.id = statuses.user_id and name = 'freelancejobz' limit 100;

6. Database Indexes: B-Trees (1-7  d1-d7)http://en.wikipedia.org/wiki/B%2B_tree

7. Blist (B-Trees in Python)http://pypi.python.org/pypi/blist