SQL Basic Command

In this blog you will learn SQL basic commands which will help new learners to understand SQL basic queries.

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.

SELECT "column_name" FROM "table_name"

Distinct

Find Distinct Column in table.

SELECT DISTINCT "column_name" FROM "table_name"

Where

Select Column in table using where condition.

SELECT "column_name" FROM "table_name" WHERE "condition"

And/Or

Add And/Or condition and find record in table.

SELECT "column_name" FROM "table_name"
WHERE "simple condition" {[AND|OR] "simple condition"}

Create Table

 

Create table using below query.

CREATE TABLE "table_name"
("column 1" "data type of column 1" [column 1 constraint(s)],
"column 2" "data type of column 2" [column 2 constraint(s)] )

Insert Into

Insert Record in table using below value.

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

SELECT Date

Select Date in SQL

Select GETDATE()  -- Current date with  time
Select CURDATE() -- Current date
Select CURTIME()  -- Current time

Insert Into

Using insert into we can insert one table Column to another table as per the below syntax.

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4” FROM "table2"

Update

Update column in table using where condition.

UPDATE "table_name"
SET "column_1" = [new value], "column_2" = [new value]
WHERE "condition"

Delete

Delete column in table using where condition.

DELETE FROM "table_name" WHERE "condition"

Drop Table Statement

DROP TABLE "table_name"

Truncate Table Statement

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

ALTER TABLE "table_name"
ADD "column_name" "Data Type"

Modify or Alter Column

ALTER TABLE "table_name"
ALTER COLUMN "column_name" "New Data Type"

Rename Column

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

Drop Column

ALTER TABLE "table_name"
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

Create index index_name
on table_name(column1,column2)

Create Unique Index

Create unique index index_name
on table_name (column1,column2)

Add Index

ALTER TABLE "table_name"
ADD INDEX "index_name" (column_name)

Drop Index

ALTER TABLE "table_name"
DROP INDEX "index_name"

Add Constraint

ALTER TABLE "table_name"
ADD [CONSTRAINT_NAME] [CONSTRAINT_TYPE] [CONSTRAINT_CONDITION]
ALTER TABLE Customer
ADD CONSTRAINT Con_First UNIQUE (Address)

Drop Constraint

ALTER TABLE "table_name"
DROP [CONSTRAINT_NAME]
ALTER TABLE Customer
DROP CONSTRAINT Con_First

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

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

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

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

Copy Structure with Data:

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

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

Insert into newtable select * from oldtable

Copy table from another database,

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

Select max(column_name) from table_name where Condition

Min

Select min(column_name) from table_name where condition

Count

Select count(column_name) from table_name where condition

Average

Select Avg(column_name) from table_name where condition

Sum

Select Sum(column_name) from table_name where condition

Like

SELECT "column_name" FROM "table_name"
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)

select "column_name" FROM "table_name"
WHERE "column_name" IN ('value1', 'value2'.)

select "column_name" FROM "table_name"
WHERE "column_name" IN (select column_name from table_name) --combined two table result

Between

select "column_name" FROM "table_name" WHERE "column_name"
BETWEEN 'value1' AND 'value2'

Order By

By default ascending order. 

select "column_name" FROM "table_name" [WHERE "condition"]
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. 

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

Having

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

UNION

union eliminates command record in both table and number of columns. For both, union table must be the same. 

Select column_name1,column_nme2 from table_name1
UNION
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

Select column_name1,column_nme2 from table_name1
UNION all
Select column_name1,column_nme2 from table_name2

Self Join

select column_name from table t1, table t2
Where condition

Exists Operator

Select the second sub query record which exists in the first select record. 

Select column_name from table_name where Exists
(select Column_name from table_name1 where condition)

Select column_name from table_name where NOT Exists
(select Column_name from table_name1 where condition)