UPDATE Command in SQL SERVER 2012

UPDATE:

It is used to modify the contents in the table,It is used to work with rows or records in the table.

We can update single column data or multiple columns data.Specific data  can be updated based on some condition,this condition is specified using where clause.

Syntax:

UPDATE <TABLE_NAME>
SET <COLUMN_NAME>=<EXPR/VALUE>
[WHERE CLAUSE]
SELECT * FROM EMP20

UPDATE EMP20
SET JOB='ANALYST' --will update the column job with 'ANALYST

SELECT * FROM EMP20

UPDATE EMP20
SET JOB='MANAGER'
WHERE ENO=1004 --Will update the job of employee whose number is 1004 with 'MANAGER'

SELECT * FROM EMP20

UPDATE EMP20
SET JOB='SALESMANAGER'
WHERE ENAME='VINAY' --Will update the job of employee whose name is 'VINAY' with 'MANAGER'

UPDATE EMP20
SET JOB='PROGRAMMERS'
WHERE JOB='ANALYST'--Will update the job of employee whose job is 'ANALYST' with
'PROGRAMMERS'

SELECT * FROM EMP20

Example2
CREATE TABLE STUDENT
(
SNO INT,
SNAME VARCHAR(15),
MAT INT,
PHYS INT,
CHEM INT,
TOTAL INT,
AVEG DECIMAL(4,2)
)
SELECT * FROM STUDENT

INSERT INTO STUDENT(SNO,SNAME,MAT,PHYS,CHEM) VALUES(101,'VINAY',50,70,80)
INSERT INTO STUDENT(SNO,SNAME,MAT,PHYS,CHEM) VALUES(102,'ANIL',60,90,72)
INSERT INTO STUDENT(SNO,SNAME,MAT,PHYS,CHEM) VALUES(103,'PAVAN',58,71,86)
INSERT INTO STUDENT(SNO,SNAME,MAT,PHYS,CHEM) VALUES(104,'SURESH',90,82,66)
INSERT INTO STUDENT(SNO,SNAME,MAT,PHYS,CHEM) VALUES(105,'RAMESH',85,77,73)

UPDATE STUDENT
SET TOTAL=MAT+PHYS+CHEM,
AVEG=(MAT+PHYS+CHEM)/3--'Here we are going to update the AVEG column by evaluating the expression with the help of MAT/PHY and CHEM columns.

UPDATE STUDENT
SET MAT=70
WHERE SNAME='VINAY'--'we are updating the maths marks of a student whose name is 'VINAY'
UPDATE STUDENT
SET SNAME='RAJESH'
WHERE SNO=105--'we are updating the name of a student whose student id 105

SELECT * FROM STUDENT

 **If we don't use where clause in UPDATE Command the update will reflect to all the table based on set clause.

Thanks
GSV

Identity in SQL SERVER 2012

IDENTITY:
It is used to generate unique values in sequential order.it can be called as autonumber.

If we use identity to a column there is no need of inserting values into that column. 
Syntax:

IDENTITY(<INITIAL_VALUE>/SEED,<INCREMENT>)

Example:

CREATE TABLE EMP1
(
ENO INT IDENTITY(1000,10),
ENAME VARCHAR(15)
)
Here we are specifying that eno is automatically generated when ever there is an insert is performed the eno starts with 1000 and increments with 10
INSERT INTO EMP1(ENAME) VALUES('RAMU')

SELECT * FROM EMP1

INSERT INTO EMP1(ENAME) VALUES('SRINIVAS')
INSERT INTO EMP1(ENAME) VALUES('ASHOK')
INSERT INTO EMP1(ENAME) VALUES('SURESH')

INSERT INTO EMP1(ENAME) VALUES('RAMESH')
INSERT INTO EMP1(ENAME) VALUES('RAHUL')

SELECT * FROM EMP1

DELETE FROM EMP1
WHERE ENO=1050

SELECT * FROM EMP1

INSERT INTO EMP1(ENAME) VALUES('RAHUL')

SELECT * FROM EMP1

DELETE FROM EMP1
Here we deleted all the records from emp1 table,but identity will not be reset .

SELECT * FROM EMP1

INSERT INTO EMP1(ENAME) VALUES('RAHUL')

TRUNCATE TABLE EMP1
if we use truncate identity will be reset.
SELECT * FROM EMP1

INSERT INTO EMP1(ENAME) VALUES('RAHUL')

This insert will start with seed
We can also use command to reset the identity instead of using truncate but best approach is we can reset using truncate.

Synatx:

DBCC CHECKIDENT('<TABLE_NAME>',RESEED,<INITIAL_VALUE>)

EXAMPLE:

DBCC CHECKIDENT('EMP1',RESEED,1000)

Database console check point by using this we can able to reset

INSERT INTO EMP1(ENAME) VALUES('SRINIVAS')
INSERT INTO EMP1(ENAME) VALUES('ASHOK')
INSERT INTO EMP1(ENAME) VALUES('SURESH')

INSERT INTO EMP1(ENAME) VALUES('RAMESH')

SELECT * FROM EMP1

Thanks
GSV

TRUNCATE in SQL SERVER 2012

TRUNCATE:

It is used to delete all the records from the table without deleting the table structure.

It clears the auto generated numbers also where delete will delete all or few records without clearing the auto generation of numbers.
Syntax for truncate:

TRUNCATE TABLE  <TABLE_NAME>

example:

truncate table emp20--It will delete all the rows from the table,we can't use where clause.

select * from emp20-herew e wont get any data.

we will see more about auto numbers in next article.

Thanks
GSV

DELETE Command in SQL SERVER 2012

DELETE :

Delete command is used to delete the rows from a table,we can delete specific rows by using where clause.

Without where clause delete command will delete all rows without disturbing the table structure.

Syntax for delete command  :

DELETE [TOP(N)] [FROM]  <TABLE_NAME>
 
[WHERE <CLAUSE>]

Examples:

SELECT * FROM EMP20

DELETE FROM EMP20

it will delete all the employees from the table.

SELECT * FROM EMP20 --will get empty table

DROP TABLE EMP20--going to drop a table
 
It will drop the table permanently.

SELECT * INTO EMP20 FROM EMP--creating a table again and dumping the rows into new table.

SELECT * FROM EMP20

Now the table contains all the records.

DELETE FROM EMP20
WHERE ENO=1001
 
we are going to drop a record with eno equal to 1001

SELECT * FROM EMP20

DELETE FROM EMP20
WHERE ENAME='RAMESH'

we are going to drop a record with ename equal to 'RAMESH'

SELECT * FROM EMP20

DELETE FROM EMP20
WHERE JOB='CLERK'

we are going to drop a record with JOB equal to 'CLERK'

DELETE TOP(20) FROM EMP20-- will delete top 20 records from the table.

SELECT * FROM EMP20

DELETE TOP(2) FROM EMP20--will drop top 2records from the table.

SELECT * FROM EMP20

Thanks
GSV

ALTER Command in SQL SERVER 2012

ALTER: It is used to modify the definition or the structure of the table or definition of the database object.

Syntax:

ALTER TABLE <TABLE_NAME>
[ADD <COLUMN_NAME> DATATYPE[SIZE]]
[ALTER <COLUMN_NAME> DATATYPE[SIZE]]
[DROP COLUMN <COLUMN_NAME>]
[ADD CONSTRAINT <CONSTRAINT_NAME> (COLUMN_NAME)]
[DROP CONSTRAINT <CONSTRAINT>]

1.TO ADD A NEW COLUMN into existing table:

Example:

ALTER TABLE EMP20
ADD TOTALSAL INT

new row is added to the table emp20

Whenever we created a new column it always come at the end of the table, we can't place at beginning or at middle of the table.

SELECT * FROM EMP20

ALTER TABLE EMP20
ADD email_id varchar(15)

new row is added to the table emp20

SELECT * FROM EMP20

Can we add multiple columns to existing table ?

yes, we can by separating comma for column name and data type value pairs as shown below.

ALTER TABLE EMP20
ADD email_id varchar(15),TOTALSAL INT

SELECT * FROM EMP20

2.To modify the data type or size of the existing column
ALTER TABLE EMP20
ALTER COLUMN EMAIL_ID VARCHAR(20)

size of the column capacity got changed with the above query.

ALTER TABLE EMP20
ALTER COLUMN EMAIL_ID INT

the data type of column email_id converted from varchar to int
sp_help emp20

ALTER TABLE EMP20
ALTER COLUMN EMAIL_ID SMALLINT

the data type of column email_id converted from int to smallint
sp_help emp20

3.TO DROP A COLUMN from existing table:
ALTER TABLE EMP20
DROP COLUMN EMAIL_ID

column email_id got dropped

 SELECT * FROM EMP20

ALTER TABLE EMP20
DROP COLUMN TOTALSAL

column TOTALSAL got dropped

SELECT * FROM EMP20

 Can we Drop multiple columns from existing table ?

yes we can as shown below

ALTER TABLE EMP20
DROP COLUMN TOTALSAL,COLUMN EMAIL_ID

SELECT * FROM EMP20

4.How to change the column name of a table ?  
syntax:

SP_RENAME '<TABLE_NAME>.<COLUMN_NAME>','<NEW_COLUMN_NAME>'

SP_RENAME 'EMP20.SAL', 'SALARY'

we will saw how to work with constraints with alter in soon.

Thanks
GSV

Select Command in SQL SERVER 2012

DQL (Data Query Language)/DRL (Data Retrieval Language) has one command "SELECT" which is used to display a set of rows from one table/more than one table.

We can use SELECT command by using different options as shown below:

1.Version1 of select:
syntax:

SELECT * FROM <TABLE_NAME>

Example:

SELECT * FROM EMPLOYEE

By using the above syntax and example we can able to get all the columns in a table and and all the rows from table.

2.Version2 of Select:

syntax:

SELECT <COLUMNS_LIST> FROM <TABLE_NAME>

Examples:

1.SELECT ENAME FROM EMPLOYEE

2.SELECT ENAME,SAL FROM EMPLOYEE

3.SELECT EID,SAL FROM EMPLOYEE

4.SELECT EID,ENAME,SAL FROM EMPLOYEE

all the above are valid.

By using the above syntax or examples we can able to get specific columns from a table/more than one table with all the row values of a specific columns.

3.Version3 of Select:

syntax:

SELECT <COLUMNS_LIST>/ * FROM <TABLE_NAME>

WHERE <CONDITION>

Examples:

1.SELECT * FROM  EMPLOYEE
   WHERE EID=1000

2.SELECT * FROM  EMPLOYEE
   WHERE EID=1001

3.SELECT * FROM  EMPLOYEE
   WHERE ENAME='srinivas'

4.SELECT ENAME FROM  EMPLOYEE
   WHERE EID=1000

5.SELECT ENAME FROM  EMPLOYEE
   WHERE EID=1001

6.SELECT SAL FROM  EMPLOYEE
   WHERE EID=1002

By using the above syntax or examples we can able to get all the columns /few columns from table or from more than one table (using joins) but we can only the rows which are satisfied by the where clause.

4.Version 4 of Select:

syntax:

 SELECT <COLUMNS_LIST>/ * FROM <TABLE_NAME>
[WHERE <CONDITION>]
ORDER BY <COLUMN_NAME> [DESC]  

Examples:

1.SELECT * FROM EMP

   ORDER BY SAL

2.SELECT * FROM EMP

   ORDER BY SAL DESC,ENAME

3.SELECT * FROM EMP

   ORDER BY ENAME

4.SELECT * FROM EMP

   ORDER BY ENAME DESC

5.SELECT * FROM EMP

   WHERE JOB='CLERK'

   ORDER BY SAL DESC

6.SELECT * FROM EMP

   ORDER BY JOB,ENAME

7.SELECT * FROM EMP

   ORDER BY JOB,ENAME DESC

8.SELECT * FROM EMP

   WHERE DEPTNO=10

   ORDER BY SAL DESC

ORDER By clause is used to sort any one or more columns of a result set in ascending or descending order by default  ascending order will come. 

NOTE:if we specify two column names in order by clause the first column will come in ascending or descending order for the column if any equal values came more than once for them second column will perform sorting and give the order.

5.Version 5 of Select:

Syntax:

SELECT TOP N <COLUMNS_LIST>/ * FROM <TABLE_NAME>
[WHERE CLAUSE ]
[ORDER BY ]

it will return top n rows from result set

Examples:

1.SELECT TOP 2 * FROM EMP

2.SELECT TOP 3 * FROM EMP
ORDER BY SAL DESC

3.SELECT TOP 3 * FROM EMP
ORDER BY SAL

top clause is the last one which will execute along with the query once after all the clauses are executed and result set is prepared then top clause will be applied on result set and that will come as final result set.

watch this space for more versions of SELECT command and let me know if you have any doubts

Thanks

GSV

DROP Command in SQL SERVER 2012

Drop command is used to delete the Database or DataBaseObject  (table,view,
function,indexes,procedures,triggers etc) from Sql Server permanently.

1.DROPPING A DATABASE

Syntax:

DROP DATABASE <DATABASE_NAME>

Example:

DROP DATABASE SRINIVASS

2.DROPPING A TABLE FROM DATABASE

Syntax:

DROP TABLE <TABLE_NAME>
example:

DROP TABLE EMPLOYEE

We can able to drop more than one table at a time also by separating comma

DROP TABLE EMPLOYEE1,EMPLOYEE2

we will see more examples in near future...

Thanks
GSV