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

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

Create Command in SQL server 2012

CREATE keyword is used to create database objects like databases,Tables,Stored Procedures,functions,views,indexes,triggers etc  

1.Syntax to create Database:  

1.  CREATE DATABASE <database_name> 

if we use this syntax we will automatically get primary and log files with default options

example:

create database srinivas                                                                      

               (or)

2. CREATE DATABASE <database_name>
   on
(
Name=Logical_name,
filename=path&fileName,
size=<size>,
maxsize=<size>,
FileGrowth=<size>)
log on
(
Name=Logical_name,
filename=path&fileName,
size=<size>,
maxsize=<size>,
FileGrowth=<size>
)

for the above syntax we can able to specify our own values and names to the primay and log files

example:
create database srinivas
   on
(
Name=srinivas,
filename='G:\srinivas.mdf',
size=5MB,
maxsize=15MB,
FileGrowth=1MB)
log on
(
Name=srinivas_log,
filename='G:\srinivas_log.ldf',
size=2MB,
maxsize=5MB,
FileGrowth=1MB
)

Note:

The above two examples will create a databse, whnever we create a database if we dont specify any options it will create two files named it as primary file and log file.

Primary  file will contain the objects that we create in our database,so this file contains all the tables,procedures,functions,views and all.

Log file contains all the commands that we executed over that database.

Size by using this attribute we will set the initial size of primary and log files.

MaxSize by using this attribute we will set the maximum size of primary and log files that they can store.

FileGrowth by using this attribute we will set the auto growth  size of primary and log files,once the primary and log files reaches to filled with data and unable to store next,the then it will update the size with this size automatically.

2.Syntax to Create a Table:

CREATE TABLE <TABLE_NAME>
(
COLUMN_NAME1 DATATYPE[SIZE],
COLUMN_NAME2 DATATYPE[SIZE],
COLUMN_NAME3 DATATYPE[SIZE],
.
.
)

Example

CREATE TABLE EMPLOYEE
(
EID INT,
ENAME VARCHAR(15),
SAL INT
)

NOTE:

Rules to be followed while we are giving names to columns and tables:

1.Table name and Column name should be one word,i.e the column name or table name con't contain any embedded space.

create table employee details
(
eid int,
ename varchar(15),
sal int
)

above one is invalid

create table employeedetails
(
eid int,
e name varchar(15),
sal int
)

above one is invalid
2.Names of columns or table can't start with a number

create table 2employeedetails
(
eid int,
ename varchar(15),
sal int
)

create table employeedetails
(
eid int,
2ename varchar(15),
sal int
)

Both of the above examples are invalid
3.The names of tables and columns should not start with special character

create table &employeedetails
(
eid int,
ename varchar(15),
sal int
)

create table _employeedetails
(
eid int,
ename varchar(15),
sal int
)

create table employeedetails
(
&eid int,
ename varchar(15),
sal int
)

All the above examples are invalid.

** underscore can be use in front of column name

we will see more examples of how to create procedures,views,indexes,functions in near future.

Thanks
GSV