JOIN QUERIES:

INNER JOIN:

select col1,col2 from tablename1 as aliasname1 inner join tablename2 as aliasname2 on aliasname1.field1 = aliasname2.field1

eg: here I have given the detaild explanation below,

to use the join queries we need atleast two tables. We need a common field in both tables. otherwise we can say like, we need a forign key table and primary key table.
Let us take a tbl_user table has primary key of userid. and let us take another table tbl_Item has the foregn key of same userid which has refered by tbl_Item.
If we need to get item which has been uploaded by a particular user. for this scenario we can use below query,

select I.itemid,I.itemname,U.username,U.Firstname,U.Lastname from tbl_User U INNER JOIN tbl_Item I on I.userid = U.userid

This inner join query is used to get the records or data present in the both column refered by a common column or field.
There are many other joins like left outer join, right outer join.
These type of joins is used to get the whole values including the common values.
Let me explain with a simple scinario.

Let us take we are showing a item listing page. In this page we are showing all the items with item name, item description along with total sales count of the particular item.
Here you may not use INNER JOIN. Since if you are using inner join we can get only the details which the item sales more than one time.
So the net result is we can not showing the items which has 0 sales count. For this you can use the Left outer join or Right Outer Join instead of INNER JOIN.
So it will give the results including the 0 sales count.

GET DATE:

If you need to get the currentdate then you can use the following query,

select getdate() as DATETIME

DATEADD:
If you need to get the past 6 Months record you can use the following,

SELECT * FROM tbl_User WHERE createddate > DATEADD(m, -6, current_timestamp);

SORTING:

Keyword: desc ----> It will give the records in DESCENDING

select ItemID, UserID, Name,Description from tbl_item where isfeatured = 1 order by CreatedDate DESC

Keyword: ASC ----> It will give the records in ASCENDING

select ItemID, UserID, Name,Description from tbl_item where isfeatured = 1 order by CreatedDate ASC


LIKE QUERY:

SELECT * FROM tbl_Item WHERE itemname like @sql+'%';

or

SELECT * FROM tbl_Item WHERE itemname like 'saravan%';

In this Like condition we should use LIKE and %. That is these two are the main key words.
From the above Query if we are using % at last with the parameter, then it will give all the values which starts with the parameter.
If you see the second example you can get the clear idea about this.

SELECT * FROM tbl_Item WHERE itemname like '%'+@sql+'%';

Here you can get the values which has contain the word @sql or parameter as an Output.

You can also show the records which has not start with some particular letters.

eg: SELECT * FROM tbl_Item WHERE itemname not like 'H%';

also you can use the query to get the values with some exclude data like,

NOT EQUAL TO:(<>)

SELECT Company, Country FROM Customers WHERE Country <> 'USA'

Here you can get the company name and country name from customers except USA.