nirmalwork-blog

[nirmalwork-blog]

SQL (1- 10)

1. DDL(Data Definition Language) : DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

Examples of DDL commands:

CREATE – is used to create the database or its objects (like table,…

[nirmalwork-blog]

SQL Topics

DDL (Data Definition Language)

DML (Data Manipulated Language)

DQL(Data Query Language)

DCL(Data Control Language)

TCL(Transaction Control Language)

Joins

Data types

Tables

Constraint

Indexes

Union and Union all

Intersect and Except

Pivot and Unpivot

Aggregation

Group by | Having | Order by

Exists and Not Exists

Sub query

While loop

If

#temp | ##temp | @temp

Trigger

Cursor

View and Materialize view

Try and Catch

Dynamic…

[nirmalwork-blog]

Merge

Insert

Update

Delete

[nirmalwork-blog]

Dynamic SQL

Declare @SQL nvarchar(1000) Set @SQL =’Select * from employee’ Execute Sp_excute sql @sql

[nirmalwork-blog]

Try and Catch

Begin try <statement> End try Begin Catch <statement> End Catch

[nirmalwork-blog]

View

View is a Virtual table we can update,delete and insert the view, if will effect the base table and it will work in single table view

Materialized view

[nirmalwork-blog]

Cursor

Data processing by the multiple columns with row by row Types of cursor

Forward only

Static

Key set

Dynamic

[nirmalwork-blog]

Types of Trigger

Trigger

DDL (Create,Alter and Drop)

DML

After(insert update delete)

Instance

[nirmalwork-blog]

#temp,##temp,@temp

#temp — Local variable — used in single session ##temp — Gobal Variable — Visible in multiple session @temp — Table variable temporary table

[nirmalwork-blog]

If Statement

If (expression 1) Begin Statement 1 end else if (expression2) begin Statement 2 end else begin default Expression 3 end

[nirmalwork-blog]

While

While is execute query line by line

While condition begin <Statement> end;

Condition — True/ False Statement — Execute to Pass

declare @count int set @count =0; while @count<=10 begin print(‘I am winner’) set @count +=1 end

While with Break

declare @count int set @count =0; while @count<=10 begin print(‘I am winner’) if @count =5 break set @count +=1 end

[nirmalwork-blog]

Sub query

Types of Sub query

In

Correlated Sub query

Non Correlated Sub query

Correlated and non correlated A sub query can contain a reference to an objects defined in parent statement this is called outer reference A Sub query contain an outer reference is called Correlated sub query A Sub query not contain any parent statement called non correlated sub query

[nirmalwork-blog]

Exists and Not Exists

Exists and Not Exists

Exists — By the condition the data is True Not Exits — By the condition the data is False

Select name,age from tab1 A where exists (select 1 from tab2 where A.Name = B.Name) Select name,age from tab2 A where not exists (select 1 from tab3 B where A.Name = B.Name)

[nirmalwork-blog]

Aggregation,Group by and Having

Aggregation,Group by and Having

Select name,sum(amount) from table1 group by name having sum(amount)>1000 order by name

Having filter records that work on summarized group of result having is group of records Where is individual records Having required group by

[nirmalwork-blog]

Pivot and Unpivot

Pivot is used when we transfer data from row level to column level

Unpivot is used when we transfer data from column level to row level

select year,A,B,C from (select name,year,sales from table1) as TA1 Pivot (sum(sales) For Name in [A,B,C]) as Ta2 order by TA2.year

select name,year,sales from Ta1 t unpivot (sales from Name in [ A,B,C]) as Ta2

[nirmalwork-blog]

Intersect /Except

[nirmalwork-blog]

Union and Union all

Union and Union all

Union is distinct record

Union all is combined with duplicate record