Post
Ask Question

SQL Basic Command

SQL is a standard language for accessing and manipulating databases.
  • SQL can execute queries against a database
  • SQL can retrieve, insert, update, delete data from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

SQL Command

Basic SQL Query is as follows:

Select

To Select Particular Column in table.
  1. SELECT "column_name" FROM "table_name"  

Distinct

Find Distinct Column in table.

  1. SELECT DISTINCT "column_name" FROM "table_name"  

Where

Select Column in table using where condition.

  1. SELECT "column_name" FROM "table_name" WHERE "condition"  

And/Or

Add And/Or condition and find record in table.
  1. SELECT "column_name" FROM "table_name"  
  2. WHERE "simple condition" {[AND|OR] "simple condition"}  
Create Table
 
Create table using below query.
  1. CREATE TABLE "table_name"  
  2. ("column 1" "data type of column 1" [column 1 constraint(s)],  
  3. "column 2" "data type of column 2" [column 2 constraint(s)] )  

Insert Into

Insert Record in table using below value.
  1. INSERT INTO "table_name" ("column1", "column2", ...)  
  2. VALUES ("value1", "value2", ...)  

SELECT Date

Select Date in SQL
  1. Select GETDATE()  -- Current date with  time   
  2. Select CURDATE() -- Current date   
  3. Select CURTIME()  -- Current time   

Insert Into

Using insert into we can insert one table Column to another table as per the below syntax.
  1. INSERT INTO "table1" ("column1", "column2", ...)  
  2. SELECT "column3", "column4” FROM "table2"  

Update

Update column in table using where condition.

  1. UPDATE "table_name"  
  2. SET "column_1" = [new value], "column_2" = [new value]  
  3. WHERE "condition"  

Delete

Delete column in table using where condition.

  1. DELETE FROM "table_name" WHERE "condition"  

Drop Table Statement

  1. DROP TABLE "table_name"

Truncate Table Statement

  1. TRUNCATE TABLE "table_name"

Alter Table Query

 
Sometimes we need to alter table definition (alter Column,add column,Add index,etc.). We can do this using the below syntax: 

Add Column

  1. ALTER TABLE "table_name"  
  2. ADD "column_name" "Data Type"  

Modify or Alter Column

  1. ALTER TABLE "table_name"  
  2. ALTER COLUMN "column_name" "New Data Type"  

Rename Column

  1. ALTER TABLE "table_name"  
  2. RENAME COLUMN "column 1" TO "column 2"  

Drop Column

  1. ALTER TABLE "table_name"  
  2. DROP COLUMN "column_name"  

Index : We can create Index on Table to faster retrieval of Record.

There are two types of Indexes in SQL:
 
Unique Index 
Non-Clustered Index
 
Create Index

  1. Create index index_name  
  2. on table_name(column1,column2)  

Create Unique Index

  1. Create unique index index_name  
  2. on table_name (column1,column2)  
Add Index
  1. ALTER TABLE "table_name"  
  2. ADD INDEX "index_name" (column_name)  
Drop Index
  1. ALTER TABLE "table_name"  
  2. DROP INDEX "index_name"  

Add Constraint

  1. ALTER TABLE "table_name"  
  2. ADD [CONSTRAINT_NAME] [CONSTRAINT_TYPE] [CONSTRAINT_CONDITION]  
  1. ALTER TABLE Customer  
  2. ADD CONSTRAINT Con_First UNIQUE (Address)  
Drop Constraint
  1. ALTER TABLE "table_name"
  2. DROP [CONSTRAINT_NAME] 
  1. ALTER TABLE Customer
  2. DROP CONSTRAINT Con_First

Copy Table Structure from one table to another(For all Columns),

  1. Select Top 0 * into newtable from Oldtable  
  2. Select * into newtable from oldtable where 1=2  
  3. Create table newtable as select * from oldtable where 0=1  

Copy Table Structure from one table to another(For Some Columns),

  1. Select Top 0 col1, col2, col3 into newtable from oldtable  

Copy Structure with Data:

  1. select * into newtable from oldtable  
  2. select * into newtable from oldtable where 1=1  
  3. Create table newtable as select * from oldtable  

If you already have a table with same structure and you want to just copy data,

  1. Insert into newtable select * from oldtable  

Copy table from another database,

  1. select * into newtablename from databasename.oldtablename  

Functions

 
In Sql there are different functions to find max, min , count, etc.Syntax for that is as follows :

Max

  1. Select max(column_name) from table_name where Condition  

Min

  1. Select min(column_name) from table_name where condition  

Count

  1. Select count(column_name) from table_name where condition  

Average

  1. Select Avg(column_name) from table_name where condition  

Sum

  1. Select Sum(column_name) from table_name where condition  

Like

  1. SELECT "column_name" FROM "table_name"  
  2. WHERE "column_name" LIKE '%a%'  

a% : Start with A

%a : end with a

%aa% : Contain aa in word

a_%_% : start with a and 3 characters in length

a%o: Start with a and end with o

In (behave like multiple or clause)

  1. select "column_name" FROM "table_name"  
  2. WHERE "column_name" IN ('value1', 'value2'.)  
  1. select "column_name" FROM "table_name"  
  2. WHERE "column_name" IN (select column_name from table_name) --combined two table result   
Between

  1. select "column_name" FROM "table_name" WHERE "column_name"  
  2. BETWEEN 'value1' AND 'value2'  

Order By

By default ascending order. 

  1. select "column_name" FROM "table_name" [WHERE "condition"]  
  2. ORDER BY "column_name" [ASC, DESC]  

Group By

Group by clause is a must when we use aggregate function in select query for a column which is not used in aggregate function. 

  1. select "column_name1", SUM("column_name2") FROM "table_name"  
  2. GROUP BY "column_name1"  

Having

  1. select "column_name1", [Function("column_name2")] FROM "table_name"  
  2. [GROUP BY "column_name1"]  
  3. HAVING (arithematic function condition)  

UNION

union eliminates command record in both table and number of columns. For both, union table must be the same. 
  1. Select column_name1,column_nme2 from table_name1  
  2. UNION  
  3. Select column_name1,column_nme2 from table_name2  
UNION ALL

Union all selects all records in a table including command record and number of columns.  In both, table must be the same

  1. Select column_name1,column_nme2 from table_name1  
  2. UNION all  
  3. Select column_name1,column_nme2 from table_name2  
Self Join

  1. select column_name from table t1, table t2  
  2. Where condition  

Exists Operator

Select the second sub query record which exists in the first select record. 
  1. Select column_name from table_name where Exists  
  2. (select Column_name from table_name1 where condition)  
  1. Select column_name from table_name where NOT Exists  
  2. (select Column_name from table_name1 where condition)