Category: SQL Server Tutorial

DDL COMMANDS :-

• Create
• Alter
• Drop
• Truncate.

Create Command in SQL Server

Creating a table from another table  :-

             Syntax :-  

                Select <collist> into <New tabname>

                From <old tabname>

                 [where <cond>]

Example:–     Select * into <new emp10 from emp.

{all stracture  &data is copyed from emp table to new table.}

Coping particular columns &particular records :

                  Select empno ,ename,sal

                  Into emp 11

Coping only structure but not data :

                   Select * into emp12

                   From emp where     1=2  {Here we can give  any false condition}

Coping only the data but not structure  :-

• It is not possible.

(2) Alter Command in SQL Server :-

• Alter command is used to modify structure of a table.
• Use alter command to do the following things  :

       1. Adding new column

       2. Droping column

       3. Modifying column ->[incr | DECR field size] -> [changing data type]

(i ) Adding new columns  :-   

Syntax : Alter table <tabname>

                  Add colname data type [size]

Example:   Alter table emp11

                       Add job varchar(20)

(ii)Drpping column  :  

Syntax  :         Alter table <tablename>

                           Drop column name.

Example:–  Alter table emp11

                          Drop column job.

{Note  :  A table is there atleast one column must there}

(iii)Modifying column   :    

Synatx : alter table <tabname>

                  Alter column colname data type(size)

Example:  Alter table emp

                      Alter column ename varchar(20)

Note: Decrement is possible up to max. length

Sp_help<tabneame>

Example: sp_help  emp.

• This command show structure of table.
• Sp stands for stored procedure.

Note: — To change a data type of the column,column must be empty.

Drop Command in SQL Server   :-

• Drop command is used to drop the table from the database.

Syntax   :  drop table<tabname>

Example: drop table EMP.

TRUNCATE Command in SQL Server :-

• To delete all records from table (or) makes table empty.

Syntax : Truncate table <tabname>

Example:   truncate table emp11.

Differences between Delete command and Truncate Command:

DELETE Command

(i) Delete command deletes all or particular Records

(ii) Delete will not release memory.

(iii) In delete, restoring is possible.

(iv) Delete will not reset identity

 TRUNCATE  Command

(i)  Truncate deletes only all records.

 (ii) Truncate releases memory.

 (iii) Truncate restoring is not possible.

 (iv)  Truncate will reset identity.

  DML COMMANDS :-

1. Insert
2. Update
3. Delete
4. Merge

Insert Command in SQL Example:

Copying the data from one table to another table:–

(i)   Syntax: insert into <target table>

Select * from <source table>

Note:   Both target table & source table structure must be same.

Example: insert into emp1

Select * from emp       {coping all records to emp1}

(2) copying the particular record :

 Example: insert into emp1

Select * from emp where deptno in(10,20)

(3) If structure is different :     

{emp1 having (Target Tab) 3 cols emp

Emp having  ( Source Tab) number of cols}

Insert into emp1

Select empno,ename,sal from emp.

(4). If emp1 table having : Tar. Table

 Empno ename sal dno hiredate

And emp table having : source table

Empno ename sal.

Example :insert into emp1(empno,ename,sal)

Select *from emp.

UPDATE COMMAND (Update Command in SQL Example) :–

• Update command is used to modify data in the table.

Syntax   :-

                    Update <tabname>

                     Set column name=value

                      [colname=value——–]

                      [where <condition>]

• update all employees commission is to 500.

Update employee set comm=500

• Update the employee commission is 500,whos commission is  null,”

      Update emp set comm=500

        Where comm is NULL.

• ”Increment employee commission by 200 whos commission is not null”.

      Update emp set comm =comm+200

             Where comm is not null.

• Increment employee salary by 10% those who are working for dept 10&20.

                 Update emp set sal=sal*1.1

                  Comm =comm*1.2

    Where dept in (10,20).

• Increment employee salary as follows:

     If clerk         10%

Salesman            15%

Manager              20%

Others                   5%

Update emp

         Set sal=case job

                      When ‘clerk’ then sal*1.1

                       When ‘salesman’ then sal*1.15

                        When ‘manager’ then sal*1.2 

                     Else

                         Sal *1.05

                            End

DELETE COMMAND (Delete Command in SQL Example) :–

• It is used to delete record(or)records from a table.

Syntax  :- delete from <tabname>[where <condition>]

Example:- delete from emp where empno=7369.

Delete from emp where ename like ‘s%’

ROW  FRAGMENT    :-

• Row fragmentation occurs when varchar fields are updated.
• The value belongs to one row stored in two different rows.
• Row fragmentation degrates performance.
• The fields which are not frequently updated there fields must be declared with varchar type.
• The fields which are frequently updated those fields must be declared with char type.

CASE STATEMENT :-

Case statement works like if then else.
It is a ANSII standard.
Case statements are two types  :

1. Simple case
2. Searched case

1) sample case statement in sql server   :–

• Syntax  :

Case      expression

When value1 then return expression1

When value2 then return expression2

——————————–

———————————

Else

      Return expewssion

   End

Example  :   Select  ename, Case job

                        When ‘clerk’ then ‘worker’

                       When ‘manager’ then ‘boss”

                       When ‘president’ then ‘big boss’

                Else

                         ‘Employee’

                         End as designation

                          From emp

Ename                    Designation

Smith                       worker

Arun                         boss

Chitty                         bigboss

Venky                         employee

B                                    employee

2) Searched case statement in sql server:-

• Syntax  :

                Case

                 When cond1 then return expr1

                  When   cond2    then   return exp2

                       ———————

                       Else

                    Return expr

                     End

Difference: 

• If condition is based on equal to operation then use simple case.
• If condition is based on other than equal operation  {like [<,>,<=,>=]} then use “searched case”.

Example: — Select ename,sal,

                      Case

                    When sal>3000 then  ‘highsal’

                       When sal<3000 then ‘lowsal’

                        Else

                       ‘Moderate sal’

                         End as sal range

                         From emp.

Example: student table

               Sno        sname         s1        s2        s3

               If  in eed like this

             Sname    stot       savg       result.

Select sname ,(s1+s2+s3) as stot

                        (s1+s2+s3)/3) as savg,

        Case

        When s1>=35 and sz>=35 and

                   S3>=35 then ‘pass’

Else

     ‘fail’

End as result

  From student

DISTINCT CLAUSE:

• The main purpose of using this distinct clause is to eliminates duplicates.

Syntax of  Distinct Clause : distinct <column>/<collist>

Example:  Select distinct job from emp

         Job

     Analyst

      Clerk

       Manager

       Salesman

Example:- Select distinct deptno, job from emp

Deptno                    job

10                           manager

10                           clerk

20                            manager

20                             hr

20                             clerk

Order by clause  :-

• It is used to sort data return by the select statement based on one (or) more columns.

• Order by clause sort data in descending order (or) In ascending order.

Syntax :

                Select <collist>from<tabname> [ where<cond>] Order by <collist>[asc\desc]

                        [Note : default in ascending order]

Sorting the emptable by salary desc.

Example  :-  select * from emp order by sal

Order by sal desc wise

•     Select * from emp Order by sal desc.

Sorting the emptable by ename wise   :–

•    Select  * from emp Order by ename (or) {we can give the column name (or) column number} 
• Select * from emp Order by 6

( The number is based select list not a table)

Example  :    select empno,ename, sal from emp Order by 3

Example  : Select empno,ename,sal*12 anusal From emp Order by anusal.

{Note   : Column alias are can be used in order by clause but not Used in where clause}

Arrange employee records department wise within the department salary wise

•          Select deptno,ename,sal,job from emp Order by dept no,sal desc

Example-       deptno                 ename                sal                   job

                              10                           A                  500                clerk

                               10                           B                  400              clerk

                                20                           C                  600            manager

                                 20                           B                   300           manager

                                  30                           D                   200          manager  

Display employee records working for 10 on 20^th departments and sort the results salary wise descending order.

•                  Select * from emp Where deptno in (10,20)  Order by sal desc.

Like Operator in Sql Server Example:-

• It is used to compare column value with character patterns.

Syntax: Like’pattern’

                 Not like ‘pattern’.

• The pattern consists of    a-z , 0-9,  
• Metacharacters -> %  (this is to replace none (or) meta characters).
• _ [Under score] -> to replace exactly one character
• [  ]   -> to replace range (or) set of characters

Display employee records name starts with ‘S’

• Select * from emp   Where ename like’S%’

Name ends with ‘S’

• Select * from emp Where ename like ‘%S’.

Name starts with ‘S’& ends with ‘S’

• Select * from emp  Where ename like ‘S%S’

If ‘S’ contain any place:

• Select * from emp Where ename like ‘%S%’.

Where ‘a’ is the second character in the name

• Select * from emp  Where  ename like’-a’

Display employee records name starts between a and p.

• Select * from emp  Where ename ,like ‘[a-p]%’

Where name doesn’t start with a and p

• Select * from emp Where ename like ‘[^ a-p]%’  (or)  Not like ‘[a-p]%’.

Display employee records names starts with

       A (or)  b (or) s (or) t

•    Select *from emp  Where ename like’[abst]%’
•    Select * from emp  Where ename like ‘[^abst]%’

Escape Operator in Sql Server  :-

Display the _names

•      Select * from emp Where ename like ‘%\_%’
•    Escape ‘\’.

Use escape option the string contains metacharacter.

Is Operator in Sql Server   :-

• Used to compare column value with null (or)not null

Syntax :

                Is null

                Is not null

”Display emplist whose commission is NULL”

• Select * from emp Where comm. Is null
• Select * from emp Where comm. Is not null.

In operation :-

• It is used to compare column values with multiple values.

Syntax: – in (v1, v2, v3—)

                      Not in (v1, v2, v3—)

Example:- Select * from emp where job in (‘clerk’,’manager’)

• Display employee list who are not working for 10 (or) 20th departments.

Example:- select * from emp where deptno not in (10, 20)

Display employee records earning between 2000&5000 and earning multiple of 50 working as clerk (or) manager & not dept in 10&20.

Example:– Select * from emp where sal between2000 and 5000 and Sal%50=0 and Job in(‘clerk’,’manager’) and Deptno not in (10, 20)

Between operation:-

• Between operation is used to compare column Value with range of values [it includes lower & upper values]

Syntax:-Between v1 and v2    (or) Not between v1 and v2.

Example:-Select * from emp  Where sal between 2000 and 5000

Note: – Between operator always works with lower limit & Upper limit but not upper limit & lower limit.

Display employee records working as clerk (or) manager

Select * from emp Where job=’clerk’ on job=’manager’

Clauses in SQL SERVER:-

Different clauses in sql server

1. Where
2. Order by
3. Distinct
4. Group by
5. Having
6. With
7. On

where clauses in sql server  :-

• Where clauses is used to get particular records from the table based on a condition

Syntax: Select<collist>from<Tabname> where <condition>.

Condition in Where clause query:

• Colname operatior value:  It must be relational (or) special operatior
• Display employeerecord whose empid=2

                        Select *from emp

Where empid=2

Order of declaration  :- [user follows this one]

Means which clause

•                      Select
•                      From
•                      Where
•                      Group by
•                      Having
•                      Order by

Order of execution    : – [system follows]

•   From
•   Where
•   Group by
•    Having
•    Select

Display employee records earning more than 2000.

                  Select * from emp where    sal>2000.

Display employee records earning more than 2000 and less than 5000

                Select * from emp where sal>2000 and sal<5000