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.

In this post I have explained some simple queries with an explanation. This may help ful for beginners.

SELECT QUERY:

select fieldname(s) from tablename

eg: select itemid,itemname,itemdesc from tbl_Item

UPDATE QUERY:

update tablename set col1=@param1,col2=@param2

eg: update tbl_Item set itemname = @itemname,itemdesc = @desc where itemid = @itemid

Here you can use the where condition to update a particular column in the table. If you need to update the values without any condition then you can have the same query but with out the where condition. Please see below for your reference,

eg: update tbl_Item set itemname = @itemname,itemdesc = @desc

Note: @itemname,@desc are the parameter.

DELETE QUERY:

delete from tablename

eg: delete from tbl_Item where itemid = @itemid

here you can delete the particular rows of a table. Thats is you can delete rows or data for particular itemid. But you can delete whole table with out anycondition you can use the same with out where condition.

TRUNCATE TABLE:

truncate table tablename

eg: truncate table tbl_Item

DIFF BETWEEN DELETE AND TRUNCATE:


Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command. Simply said it will reset the table with the new one. But it wont affect the structure of the table.

INSERT QUERY:

insert into tablename(col1,col2,col3) values(@param1,@param2,@param3)

eg:  insert into tbl_Item(itemname,desc) values(@itemname,@desc)