Wednesday, August 15, 2012

Below is a simple case scenario to test your query writing skills. Please put your answer in comments section...
/*
Case:
I need to store news/article information. The news can also have comments and tags. News can be submitted/posted by multiple users.
*/

According to the above scenario, I have created 5 tables and inserted some data into them :-
Use the following queries to built the database and related table:


create database demo
use demo

CREATE TABLE comments(
id int identity,
news_id bigint NOT NULL,
[user_id] bigint NOT NULL,
detail varchar(max) NOT NULL,
status bit NOT NULL default 1,
comment_date bigint NOT NULL,
PRIMARY KEY (id)
)


INSERT INTO comments(news_id, [user_id],detail, status, comment_date) VALUES(1, 2, 'nice post :)', 1, 1273431296)

INSERT INTO comments(news_id, [user_id],detail, status, comment_date) VALUES( 2, 2, 'hahaha', 1, 1273431420)

INSERT INTO comments(news_id, [user_id],detail, status, comment_date) VALUES( 3, 2, '3123123', 1, 1273431452)

INSERT INTO comments(news_id, [user_id],detail, status, comment_date) VALUES( 1, 2, 'thank You', 1, 1273431475)

INSERT INTO comments(news_id, [user_id],detail, status, comment_date) VALUES( 2, 1, 'congratulations!', 1, 1273431500)


---------------------------------------------------------------

CREATE TABLE news (
id bigint identity NOT NULL,
[user_id] bigint NOT NULL,
title varchar(max) NOT NULL,
detail varchar(max) NOT NULL,
visit int NOT NULL default '0',
status bit NOT NULL default '1',
created_date bigint NOT NULL,
PRIMARY KEY (id)
)

INSERT INTO news ([user_id], title, detail, visit, status, created_date) VALUES(1, 'Extra Content', 'fasdfasdf', 2, 1, 1273431296)

INSERT INTO news ([user_id], title, detail, visit, status, created_date) VALUES(1, 'My My Question', 'this is jpt question.. :D', 16, 1, 1273431389)

INSERT INTO news ([user_id], title, detail, visit, status, created_date) VALUES(2, 'Am I ram?', 'I am ram..yahoo !!', 3, 1, 1273431420)

----------------------------------------------------------


CREATE TABLE tags (
id bigint identity NOT NULL ,
name varchar(255) NOT NULL,
slug varchar(255) NOT NULL,
PRIMARY KEY (id)
)

INSERT INTO tags (name, slug) VALUES('sagarmatha', 'sagarmatha')
INSERT INTO tags (name, slug) VALUES('nepal', 'nepal')
INSERT INTO tags (name, slug) VALUES('gautam buddha', 'gautam-buddha')
INSERT INTO tags (name, slug) VALUES('testing', 'testing')
INSERT INTO tags (name, slug) VALUES('tags', 'tags')
INSERT INTO tags (name, slug) VALUES('tasty apple', 'tasty-apple')
INSERT INTO tags (name, slug) VALUES('banana', 'banana')

------------------------------------------------------------

CREATE TABLE tags_news (
id bigint identity NOT NULL,
news_id bigint NOT NULL,
tags_id bigint NOT NULL,
PRIMARY KEY (id)
)


INSERT INTO tags_news (news_id, tags_id) VALUES(1, 1)
INSERT INTO tags_news (news_id, tags_id) VALUES(1, 2)
INSERT INTO tags_news (news_id, tags_id) VALUES(1, 3)
INSERT INTO tags_news (news_id, tags_id) VALUES(2, 4)
INSERT INTO tags_news (news_id, tags_id) VALUES(2, 5)
INSERT INTO tags_news (news_id, tags_id) VALUES(2, 6)
INSERT INTO tags_news (news_id, tags_id) VALUES(3, 6)
INSERT INTO tags_news (news_id, tags_id) VALUES(3, 7)

-----------------------------------------------------------

CREATE TABLE users (
id bigint identity NOT NULL,
[user_id] bigint NOT NULL,
firstname varchar(100) NOT NULL,
lastname varchar(100) NOT NULL,
PRIMARY KEY (id)
)


INSERT INTO users ([user_id], firstname, lastname) VALUES(1, 'Mukesh', 'Chapagain')

INSERT INTO users ([user_id], firstname, lastname) VALUES(2, 'Christopher', 'Gayle')

INSERT INTO users ([user_id], firstname, lastname) VALUES(3, 'Brian', 'Lara')




-- Questions...

  1. SELECT NEWS WITH COMMENTS COUNT FOR EACH NEWS
  2. SELECT NEWS POSTED BY ANY PARTICULAR USER (HERE, user_id = 1)
  3. SELECT NEWS POSTED BY ANY PARTICULAR USER (WITH USER’S FIRSTNAME AND LASTNAME) (HERE, user_id = 1)
  4. SELECT NEWS POSTED BY ANY PARTICULAR USER (WITH COMMENTS COUNT FOR EACH NEWS) (HERE, user_id = 1)
  5. SELECT COMMENTS FOR ANY PARTICULAR NEWS (HERE, news_id =1)
  6. SELECT COMMENTS FOR ANY PARTICULAR NEWS (ALONG WITH USER INFORMATION) (HERE, news_id = 1)
  7. SELECT TAGS FOR ANY PARTICULAR NEWS (HERE, news_id = 1)
  8. SELECT NEWS BY TAG NAME (HERE, tag = ‘nepal’)
  9. SELECT NEWS BY TAG NAME (ALONG WITH COMMENTS COUNT) (HERE, tag = ‘nepal’) 
  10. SELECT TAG CLOUD

Hope this scenario helped you all...........

0 comments:

Post a Comment