Very useful Sql Queries
To select all records in the table.The query is below..
Select * from tablename
* indicates all the columns
Select column1,column2,column3 from table order by column1 desc
select column1,column2,column3 from table1 T1 inner join table2 T2 on T1.ID=T2.ID
To Create Table.The syntax is as follow..
Create table tablename ( columnname datatype NULL|NOT NULL )
To create table with primary key and Foreign Key.
Create table MyTable
( mytableID primarykey int identity,
Name varchar(200),
AddressLine1 varchar(500),
AddressLine2 varchar(500),
MytableId2 references MyTable2(mytableID) )
To add new field to existing column.The syntax is as follows.
Alter Table Tablename Add Columnname datatype
To delete all the records in the table. The Systax is as follows
Delete from tablename where column1=@value
To Drop table.The syntax is as follows Drop table tablename
To drop Database
DROP DATABASE database_name
To drop the stored procedure from database.The syntax is as follows
Drop procedure produrename
To truncate table
Truncate table tablename
To Count number of records in the table. The syntax is as follows.
Select count(*) from tablenameHow to use ISNULL.
If the particular field value is null then you can replace with 0.00 or customize it.
SELECT ProductName,UnitPrice* (UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products
To select distinct record from the table. The syntax is follows.
select distinct columnname1 from tablename
select distinct columnname1,* from tablename
To select records based the start and end date.The syntax is as follows
select * from tablename where diff(day,getdate(),enddate)=0
To select records based the years. The syntax is as follows.
select * from tablename where diff(yy,getdate(),enddate)=0
To select records based the years. The syntax is as follows.
select * from tablename where diff(mm,getdate(),enddate)=0
select maximum salary from the table.The syntax is as follows.
select max(columnname1) from tablename
To alter the column.The syntax is as follows.
Alter Table tablename Alter column columnname1 datatype
