Extra excersises for those who is just starting with SQL

1. Tables: Create a little schema for blogging application: an article table, a tag table, a comment table ( hierarchical), and tables linking all three. Also, comments table might need some normalization, depending on how you let people comment and whether you moderate the blog. There might be a whole module around it. What module should it be? what other tables will be joining the comments table? Create these tables as an extra bonus. We are not archiving.

2. Fields: Each table should have an artificial key that will serve as a primary key. What data type would you use for the primary key for the article table? For the tag table? for the comment table? Estimate total amount of blogs you will be publishing, how many "keywords" that you will expose as tags and how many total comments you expect. If you have other tables - do the same calculations. To consider data types for the other fields: a. please, decide if you have a one-language blog. In lieu of that - would you consider unicode data types or non-unicode? b. for date data types : if you are international - do you want your dates be universal datetime? if not - do you care for "time component"? c. do you have fields with exact number of characters?

What consideration you will put into deciding which fields are text - do you want to make them as long as it takes? what is varchar (max)?

3. Nulls - decide which fields you want to be NULLS. Nulls are very hard to operate on but sometimes are necessary. Do you really need any nulls in this database? Will tag table fields have any nulls? Will comments table fields have any nulls? Do you think you need them because the data will be unknown at the time of entry? Any other tables? Do an experiment: create an articleView table that will count the page views. At some points, you will not know the values in the viewCount field. Insert three values into it, with Article1 with 10 views, Article2 with 100 views and Article3 with NULL. Try running the query: SELECT * from artileView where not (viewCount = 10) How many results do you see? How would you write the quiery to see all the rows that have different from 10 views values, inlcuding those that you do not know? 4. Constraints: a. foreigh keys: create foreign keys between tables. Do you think you might have a situation when you do not need them? b. unique indexes. Create index on the tag table that does not allow entering duplicate key value. Do you want your database to be case-sensitive? Research topic - read about collations. Would any other tables have unique indexes? why/why not? Think about if your commenter tries to post a comment twice from the site. Do you allow her/him to post duplicate? What if different people want to post exact same comment, like "wow!" Where do you maintain the "tie"? do you need to search and retrive an id for the comment? Why is it bad? Why is it good? How many different comments there might be? How many will there be "repeating" comments? What are the popular candidates for repeating comments? Do you really need to normalize comments - and prevent duplication? Besides unique index, how else could you prevent duplicate posting? Research internet to understand implications of length of a unique field on preformance. Is there going to be one? Make an experiment - introduce unique index on the comment field and insert a lot of rows, go past a million. c. defaults: How many tables will have inserted date/by, and how many need updated date/by? do you allow updating a comment? a tag? What kinds of defaults will you introduce for these fields? d. calculated fields: create a field that calculates average number of views per month. e. check constraints: make sure that people do not blog on Christmas Eve.

5. Some SQL.

retrieve all todays' comments for all the articles retieve all today's comments for today's article

retrieve the most visited blog - and make it a function

retrieve the most used keywords retrieve articles of the length that is greater than 500 characters select keywords that have a pattern "sea" in it select blogs that have 0 comments select blogs with the greatest number of keywords select blogs that have at least three keywords in common select comments with the greatest depth select comments that have no children

create a procedure that either inserts # of views into ArticleView table, or updates existing #of views in this table.