I am trying to write a Query in Exact Target to pull some report. I understand that what we call Tables in SQL is termed as 'Data Views' here. So where can we see those Data View definitions, I mean column names, primary key details and relationship to other tables? I am beginner for Marketing Cloud and couldn't find straight cut answers in SF Communities and please correct me if I have referred something wrongly.
Data Extensions in SFMC are equivalent to database tables. Those can include any columns that suit your requirements.
The Data Views (System Data Views), are read-only tables of Subscriber and Subscriber activity data.
The schemas for those are found on the Query Activity page.
Query Activities can only do SQL Select statements and add/update/append another Data Extension.
If you're looking for some syntax help, SFMC Query Activities follow Microsoft Transact-SQL (T-SQL) conventions (with a few exceptions).