Working on retrieval Queries!

Audio
http://yourlisten.com/channel/content/16904992/db2
Video of the screen demo is in this link
http://youtu.be/ckwUl3i3EVI


 In the last post i was discussing a scenario about viewing your own url feeds and the public feed by others.To know the schema of the module please refer to the previous post.
Now let us deal with some of the queries which i worked on today for retrieving from the table.
The tables that i already have are feed,tag,category,feed_tag..
U have to also include a users table which has id has one of the attribute.

Now the u_id in the feeds is related to the id in user.So set the foreign key as
 alter table feed add constraint foreign key(u_id) references users(id);
The cat_id in feed is related to cat_id in category.

alter table feed add constraint foreign key(cat_id) references category(cat_id);

The tag_id in tag is referenced in tag_id of tag_feed table.
alter table tag_feed add constraint foreign key(tag_id)references tag(tag_id);

The f_id in feed is related to the feed_id in tag_feed.
alter table tag_feed add constraint foreign key(feed_id)references feed(f_id);

Now that we have set the foreign key constraints between the tables we will work on the queries.


1)To get the number of feeds for a given category.
query:
    select cat_name,count(*) from category,feed where feed.cat_id=category.cat_id group by cat_name;

2)Display the no of tags for a given feed.

 select feed_url,count(*)as num_of_tags from tag_feed,feed where feed.f_id=tag_feed.feed_id group by feed_url;

3)To display all the public feeds along with the category.
select cat_name,feed_url from category,feed where category.cat_id=feed.cat_id and feed.access="public";

4)To display all the private feeds along with the category.
select cat_name,feed_url from category,feed where category.cat_id=feed.cat_id and feed.access="private";

5)To specify the various user's public feeds along with the category.
select users.user_name,cat_name,feed_url from category,feed,users where users.id=feed.u_id and category.cat_id=feed.cat_id and feed.access="public";

6)To specify the various users private feeds along with the category.

select users.user_name,cat_name,feed_url from category,feed,users where users.id=feed.u_id and category.cat_id=feed.cat_id and feed.access="private";

7)To display the category names along with the count of the tags of the associated feeds.

select DISTINCT cat_name,tag.tag_name from category,feed,tag_feed,tag where category.cat_id=feed.cat_id and feed.f_id=tag_feed.feed_id and tag.tag_id=tag_feed.tag_id;

8)To display the category names along with the count of the tags of the associated feeds.

select cat_name,count(*) from category,tag where (cat_name,tag_name) IN (select DISTINCT cat_name,tag.tag_name from category,feed,tag_feed,tag where category.cat_id=feed.cat_id and feed.f_id=tag_feed.feed_id and tag.tag_id=tag_feed.tag_id)group by cat_name;




How do i link the various modules which the users work on different servers??
I have always wondered and found an answer today.It is using the database dump file.You give the command,

mysqldump db_name -u user_name -p password.
Hurray and the dump file is created.

Now you can put it in a text file using the command
mysqldump db_name -u user_name -p password >> filedump.txt;
And this file can be used by others to create their schema :)

1)Queries using max:

Used to group with the maximum in each.
select type,max(val) from tab1 group by(type);

2)Queries using min:

Used to group with the minimum in each.
select type,min(val) from tab1 group by(type);

Used to group with the avg in each.
select type,avg(val) from tab1 group by(type);

WHERE CLAUSES

1)AND..You can give multiple conditions in where clauses where both are to be true.
2)Or..You can use it to combine multiple clauses where any of them can be true.
3)Not..Used to specify the negation of the where clause..
4)Like..Used to identify the patterns in the column,
WHERE column_name LIKE pattern
5)Not LIKE..Used to identify those columns which doesnt match a pattern.

6)NULL..
NULL values represent missing unknown data. By default, a table column can holdNULL values.
7)NOT NULL..
The NOT NULL constraint enforces a column to NOT accept NULL values. 
OTHER CLAUSES
ORDER by:
This is used to sort in the ascending or the descending order of a particular column.
Offset:This is from where the retrieval should start..
Limit:This is used to limit the number of records to be retreived.
syntax: sql statement LIMIT [N]


limit and offset can be combined by using comma in limit clause.






Hope it helped.



- Sourcebits University
Cloud Computing
www.sourcebits.com