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

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

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