SQL Server



SQL Server

SQL Server is a structured Query language Based on Client Server RDBMS

Structured Query language is used to communicate between Client & Database

Data: - Information that is been stored and used in the computer system is called as data.

Database: - Collection of meaningful Information stored in an Organized Manner.

DBMS: - It is a collection of interrelated data and set of programs to access and modify the data.

RDBMS: - It is also DBMS but the relation will be stored into the database

WHAT IS A DBMS?

Every individual and company has data, which is to be stored and manipulated. Manipulating includes adding as new data, deleting unwanted data, and changing the existing data. So to store and manipulate data efficiently, we need a set of programs called as database management system.(DBMS) 

WHAT IS AN RDBMS

         Maintain Relationship between Two OR More Data Bases

Features of SQL Server

  • Very fast 
  • Different Data Types  tiny int ,small int , int, Big int, table…….
  • It supports XML
  • Unique is not accepting Multiple Null Values
  • We can Modify Data Base
  • Installation is Very Easy
  • Data integrity
  • Integrate security
  • Scalability ( Single Program can shared on all environments )



Create usedefined database

Create database srihitha
on
(
Name = 'sri-dat',
Filename = 'e:\pri_dat.mdf',
Size=15, maxsize=50, filegrowth=5)
Log on
(
Name = 'sri-log',
Filename = 'e:\tr_log.ldf',
Size=10, maxsize=25, filegrowth =5)

------------------------------------------------------

Note:

Primary Data File (.mdf)

  • This File contains the startup information for the database and is used to store data
  • Every DB has one primary data file

Secondary Data File (.ndf)

  • These files hold all of the data that does not fit in the primary data file.
  • If the primary file can hold all of the data in the DB, DB do not need to have secondary data files.

Transaction Log File (.ldf)

  • These Files hold the log information used to recover the database.
  • There must be at least one log file for each DB
------------------------------------------------------

To view the Data Base Details: -

Sp_helpdb   srihitha



Alter Data Base

A )Modofy file name

Alter database srihitha
Modify file
(
Name = 'sri-dat',
Newname = 'xyz'
)

B )Modofy file size

Alter database srihitha
Modify file
(
Name = 'xyz',
Size=20
)

 Rename Data Base

sp_renamedb ‘srihitha’, 'satish'

 Now

sp_helpdb srihitha          --- not working

sp_helpdb satish              ---  working



Deleting Data Base

Drop database satish 
                   -- U can not drop from that db

------------------------------------------------------

System Defined databases are 4 Types

Master

  • The master DB records all of the sys level info for a SQL Server System
  • It records all login accounts and all sys config setting
Model

  • The Model DB is used as the template for all databases created on a sys
  • When a CREATE DATABASE statement is issued, the first part of the DB is created by copying  in the model DB
MSDB

  • The MSDB database is used by SQL Server Agent for scheduling alerts and Jobs, and recording Operators.
Temp DB

  • Temp DB holds all temporary tables and temporary stored procedures.
  • Temp DB is a global resource
  • Temp DB is recreated every time SQL Server is started so the system starts with a clean copy of the DB.


Character

Character data consists of any combination of letters, symbols, and numeric characters.

Char Data must have the same fixed length (up to 8 KB).

varchar Data can vary in the number of characters, but the length cannot exceed 8 KB.

Text Data can be ASCII characters that exceed 8 KB.
--------------------------------------------------------------------
Date and time

Date and time data consists of valid date or time combinations. There are no separate time and date data types for storing only times or only dates.

Date time Date data should range from January 1, 1753 through December 31, 9999 (requires 8 bytes).

Smalldatetime Date data should range from January 1, 1900 through June 6, 2079 (requires 4 bytes).

--------------------------------------------------------------------

Integer

Integer data consists of negative and positive numbers,

Bigint Storage size is 8 bytes. Range -263 to 263 – 1.

Int Storage size is 4 bytes. Range -231 to 231 – 1.

Smallint Storage size is 2 bytes. Range -215 to 215 – 1.

Tinyint Storage size is 1 bytes. Range 0 to 255.

--------------------------------------------------------------------

Floating point

Float Data is a floating-point number.

Real Data is a floating-point number.

--------------------------------------------------------------------
Decimal

Decimal data consists of data that is stored to the least significant digit.

Decimal Data can be a maximum of 38 digits, the data type stores an exact representation of the number; there is no    approximation of the stored value.

Numeric in SQL Server, the numeric data type is equivalent to the decimal data type.

--------------------------------------------------------------------

Monetary

Monetary data represents positive or negative amounts of money.

Money Data is a monetary value in the range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807                                                                                                                                                                                     (requires 8 bytes)

Small money range from -214,748.3648 to 214,748.3647 (requires 4 bytes)

--------------------------------------------------------------------

 Unicode    
Using Unicode data types, a column can store any character defined by the Unicode Standard, which includes all of the characters defined in the various character sets. Unicode data types take twice as much storage space as non-Unicode data types.

Nchar Data must have the same fixed length (up to 4000 Unicode characters).

Nvarchar Data can vary in the number of Unicode characters (up to 4000).

Ntext Data can exceed 4000 Unicode characters.

--------------------------------------------------------------------

Miscellaneous
Special data consists of data that does not fit in any of the other categories of data.

Bit Data consists of either a 1 or a 0. Use the bit data type when representing TRUE or FALSE or YES or NO.

Cursor This data type is used for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. Any variables created with the cursor data type are null able.

Timestamp        
   It is used to indicate the sequence of SQL Server activity on a row and is represented as an increasing number in a binary format.

Unique identifier
 Data consists of a 16-byte hexadecimal number indicating a globally unique identifier (GUID). The GUID is useful when a row must be unique among many other rows.

SQL_variant
This data type stores values of various SQL Server–supported data types except text, ntext, timestamp, image, and sql_variant.

Table
This data type is used to store a result set for later processing. The table data type can be used only to define local variables of type table or the return value of a user-defined function.

Create Tables

Create table emp
(
eno tinyint,
ename varchar (20),
eage tinyint,
ecity varchar (20)
)

Implicit Insertion 

 Insert into emp values ( 1 , 'srinu' , 20 , 'hyd' )

Explicit Insertion

Ex 1 : Insert emp (eno , ename , eage , ecity )  Values ( 2 , ’ramesh’ , 30 , ’vsp’)

Ex 2 : Insert emp ( ename , ecity , eno , eage)  Values ( 'abcd' , 'gunt' , 2 , 25)

See the data in a table

Select * from emp

Select eno, ename from emp

Select * from emp where eno = 1

See the structure of the table

sp_help emp

Create another table

Create table student
(
sno tinyint,
sname varchar(20),
Sage tinyint,
scity varchar(20)
)

Sp_tables ----to see the number of tables in DB

Update Table

Update emp set eage = 45     ---- all rows effected

Update emp set eage = 45, ename = 'xyz' 
                                        --- All rows effected

Update Table with condition

Update emp set eage = 50 where eno = 1 
                                              --- One row effected

Deleting Data from Tables

Delete from emp    --- all rows deleted (one by one)

Delete from emp where eno = 1 – one row deleted

Truncate table EMP --- all rows deleted (at a time)

 * We can not give condition in truncate

Alter Table

Changing datatype

1) Alter table emp ALTER COLUMN eno varchar(5)

2) Alter table emp ALTER COLUMN ename varchar(10)

Changing NULLS

Alter table emp ALTER COLUMN eno varchar(10) not null

sp_help emp  --- nullable – no - at  ‘ eno ‘

Adding Columns

Alter table emp ADD sal tinyint

Rename Table

Sp_rename emp , xyz

Select * from emp    --- not working

Select * from xyz      --- working

Rename column

sp_rename ‘xy.eno’, 'enumber'

sp_help emp

Removing the table

Drop table emp 

Identity (Auto Number Increment)

Create table emp
(
eno tinyint identity(1,1),
ename varchar (20),
eage tinyint,
)

Insert into emp values ( 'raju' , 20  )

Select * from emp

Insert into emp values ( 'ramu' , 25  )

Select * from emp


Simple Primary Key

Create table emp
(
eno tinyint constraint pk_const primary key,
ename varchar(20),
eage tinyint
)

Insert into emp values ( 1 , 'srinu' , 20  )  - Pass

Insert into emp values ( 2 , 'ramesh' , 20  ) - Pass

Insert into emp values ( 1 , 'ramu' , 20  ) – Fail

Insert emp (ename , eage  )
                             Values ( 'ramesh' , 30 ) - Fail

Composit Primary Key

Create table login
(
uname varchar(8),
pwd varchar(8),
Constraint cpk_const primary key ( uname , pwd)
)

Insert into login values ( 'ramu','ramu'  ) -Pass

Insert into login values ( 'ramu','abcd'  ) -Pass

Insert into login values ( 'abcd','ramu'  ) -Pass

Insert into login values ( 'ramu','ramu'  ) -Fail

Drop table emp

Unique

Create table emp
(
eno tinyint constraint uni_const unique,
ename varchar(20),
eage tinyint
)

Insert into emp values (1 , 'srinu' , 20  )  - Pass

Insert into emp values ( 1 , 'Raju' , 20  )  - Fail

Insert emp (ename , eage  )
                           Values ( 'ramesh' , 30 ) - Pass

Select * from emp  -- eno accepts NULL

Dropping Constraints

Alter table emp
Drop constraint uni_const

Drop table emp

NOT NULL

Create table emp
(
eno tinyint constraint notnull_const not Null,
ename varchar(20),
eage tinyint
)

Insert into emp values ( 1 , 'srinu' , 20  )  - Pass

Insert into emp values ( 1 , 'Raju' , 20  )  - Pass

Insert emp (ename , eage  )
                           Values ( 'ramesh' , 30 ) - Fial

Drop table emp

Check

Create table emp
(
eno       tinyint constraint pk_const primary key ,

ename  varchar (20) ,

eage      tinyint constraint ck_const1 check     (eage>15 and eage <25) ,

deptno  tinyint constraint ck_const2 check  ( deptno in (10,20,30))

)

Insert into emp values ( 1 , 'Raju' , 20 ,10  ) –Pass

Insert into emp values ( 1 , 'srinu' , 20 ,10  ) –Fail
                    (Pk_const) - Error

Insert into emp values ( 2 , 'srinu' , 13 ,10  ) – Fail
                      ( ck_const1 ) - Error
Insert into emp values ( 2 , 'srinu' , 20 ,5  ) – Fail
                        ( ck_const2 ) - Error



Default

Create table student
(
sno tinyint constraint pk_const primary key,
sname varchar(20),
sage tinyint constraint def_const default 15
)

Implicit Insertion

Insert into student values ( 1 , 'srinu' )
                                                   Not  working



Insert into student values ( 1 , 'srinu',default )
                                                     Working

Select * from student

Explicit Insertion

Insert student (sno , sname  )
                         Values ( 2, 'ramesh' ) - Pass

Select * from student

Foreign Key

Student Personal Det (SPD) –Indipendent table

Student Marks Det (SMD) – Dependent Table

Put a Foreign Key in Dependent Table (SMD)

Ex: -

Create table spd
(
sno tinyint constraint pk_const3 primary key,
sname varchar(20),
sage tinyint
)
create table smd
(
sno tinyint constraint fk_const foreign Key references spd(sno),
smarks smallint
)

Insert into spd values ( 1 , 'srinu',20 )

Insert into smd values ( 1 , 50  ) 

Delete from spd  - Fail

Delete from smd - Pass

Now

Delete from spd – Pass

* If the data In Dependent (SMD) Table Then U can not delete the Related data in Independent (SPD) table

Drop table emp

Add Constraint

Create table emp
(
eno tinyint,
ename varchar (20),
eage tinyint,
ecity varchar (20)
)

alter table emp
add constraint pk_const6 primary Key (eno)         -- Fail

Before it Eno should be Null

Alter table emp
alter column eno tinyint not null   now

alter table emp
add constraint pk_const6 primary Key (eno)                   Pass

Sub queries

Ex 1

select * from spd where sno = (select sno from smd where smarks>80)

Ex 2

select * from spd where sno in (select sno from smd where smarks>10)

select * from spd order by sage

select * from spd order by sage desc

select  * from spd where sname like 'r%'

select  * from spd where sname like '%u'

select  * from spd where sname like 'r%u'

select  * from spd where sname like 'r_mu'        (  _ indicates indicates single char missing)

select  * from spd where sname like '[p-t]%'

select  * from spd where sname like '[p-t][a-d]%'



create rule rule1
as
@me > 15 and @me <25

Press F5 (Execute Rule)

create table emp
(
eno tinyint,
ename varchar(20),
eage tinyint
)

insert into emp values (1,'xyz',10)

sp_bindrule rule1,'emp.eage'

Previous Records are not affected after Rule.


insert into emp values (1,'xyz',10)  - Fail

insert into emp values (1,'xyz',20)  - Pass

select * from emp

Unbind rule

sp_unbindrule 'emp.eage'

Drop rule

Drop rule rule1

Note : Before Drop The Rule , We Must be   Un Bind with all columns


create table emp
(
eno tinyint,
ename varchar(20),
eage tinyint
)

--------------------------------------

create  Default def1
as 15

----------------------------------------

insert into emp values (1,'xyz',default)

select * from emp

Then u will get   Default = NULL

You Have to Bind it

 Binding Default to emp

Sp_bindefault   def1,'emp.eage'

----------------------

Implicit insertion

insert into emp values (2,'abc',default)

select * from emp

Now Default =15

Explicit insertion

Insert emp (eno,ename) values ( 3 , 'pqr')

select * from emp

Un Bind & Drop Default

sp_unbindefault   'emp.eage'

drop default def1

* Before Drop The Default , We Must be   Un Bind with all columns



Joins

1 ) Inner Join or equi join :

A join which is based on equalities is called equi join , table should have common column to perform equi join.

create table studentdet
(
sno tinyint ,
sname varchar(20),
sage tinyint
)

create table studentmarks
(
sno tinyint ,
smarks smallint
)

insert into studentdet values ( 1,'A' , 15)
                   
insert into studentmarks values ( 1 , 50 )
                               

Inner Join Ex :

select * from studentdet
join
studentmarks
on studentdet.sno = studentmarks.sno

----------------------

select studentdet.*,studentmarks.smarks from studentdet
join
studentmarks
on studentdet.sno = studentmarks.sno

2) Outer Join

Outer join is an extension for equi join. It is used to retrieve unmatched records along with matched records.

 Left outer Join:

  All Records in left table and matching records in right table

Ex:

select * from studentdet
left outer join
studentmarks
on studentdet.sno = studentmarks.sno

Right outer Join:

All Records in right table and matching records in left table

Ex :

select * from studentdet
right outer join
studentmarks
on studentdet.sno = studentmarks.sno

Full outer Join :

  All Records from both tables

EX :

select * from studentdet
full outer join
studentmarks
on studentdet.sno = studentmarks.sno

3) Cross Join

In cross join every row of the first table joins with every row of the second table

EX:

Create table paints
(
sno tinyint ,
Comp varchar(5),
)

Create table color
(
colorname varchar(5)
)

Insert into paints values ( 1 , 'A' )   ( 3 records)

Insert into color values (‘Red’)   (3 records)

3*3 = output 9 records

Select * from paints
Cross join
Color

4) Self Join

Joining the table itself is called self join.

Create table emp1
(
eno tinyint ,
ename varchar (20) ,
mgrno tinyint
)

Insert into emp1 values ( 1,'A',5)

Insert emp1 (eno,ename) values ( 6,'F')
                                             

Select * from emp1

Select * into emp2 from emp1

                  - Emp2 is dummy Table select * from emp2

Ex (Self Join):

Select distinct emp2.eno, emp2.ename as Manager
From emp1, emp2
Where emp1.mgrno = emp2.eno


Dummy Tables
1) Select Into:

One Dummy Table created by Sql server and
Data will be copied

Select * into emp2 from emp1

Select * from emp2

We can give condition on dummy Table

Ex:

Select * into emp3 from emp1 where eno <4

Select * from emp3

2) Insert – Select:

Note: We have to create table

Create table emp4
(
eno tinyint ,
ename varchar (20) ,
mgrno tinyint
)

 Ex 1:

Insert emp4 select * from emp1

Select * from emp4

Delete from emp4 

Ex 2:

Insert emp4 select eno, ename, mgrno from emp1

Note: If you want to Skip Columns in Duplicate Table Then Use It


Views

A View is a virtual table, which gives access to a subset of columns from one or more Tables

Create table emp
(
eno tinyint,
ename varchar (20),
eage tinyint,
esal smallint
)

Insert into emp values (1,’ramu’, 20, 2000)

Select * from EMP

To avoid esal

-----------------------------------------------------------------------

Create view view1
As
Select eno, ename, eage from EMP



Select * from view1

it is Dynamic Result Set ( Not a Table )

sp_helptext view1

 (Gives the Definition of View)


Alter View

Alter view view1
With Encryption
As
Select eno, ename, eage from EMP

F5 - execute

Now

sp_helptext view1   (is not working)
-----------------------------------------------------------------------

Again 

Alter view view1
As
Select eno, ename, eage from EMP

Now
sp_helptext view1   (is working)

-----------------------------------------------------------------------

Sp_rename EMP, emp1

F5

Now

Select * from view1   -- not working

Again

sp_rename emp1, EMP

Now

Select * from view1   -- working

-----------------------------------------------------------------------

Drop table EMP

Select * from view1   -- not working
-----------------------------------------------------------------------

If Base table is Dropped or Renamed Then View is Not Working

We can create View without Base table, but we can not work with that view

 Drop View

Drop view view1

Schema Binding

If we put Schema Binding then we can not drop Base table until drop view

Create table EMP
(
eno tinyint,
ename varchar (20),
eage tinyint,
esal smallint
)

Insert into emp values ( 1 ,'ramu' , 20 , 2000 )

-----------------------------------------------------------------------

Create view view1
As
Select * from EMP
-----------------------------------------------------------------------

Select * from view1

-----------------------------------------------------------------------

Alter view view1
With Schema binding
As
Select eno, ename, eage, esal from DBO.emp

-----------------------------------------------------------------------

Drop table EMP -- not working

-----------------------------------------------------------------------

Check

Drop view view1
-----------------------------------------------------------------------

Create view view1
As
Select * from EMP
Where esal<=3000

-----------------------------------------------------------------------

Select * from view1

Select * from EMP

-----------------------------------------------------------------------

We can insert, delete, Update Records in View

Ex: (insert)

Insert view1 values (4,’suresh’, 20, 5000)

Select * from view1 -- u can not see 5000 Rec

Select * from EMP -- u can see 5000 Rec


Ex: (Delete)

Delete from view1 where eno=1

Select * from view1

We can delete, if the rec is available in View

Ex: (Update)

Update view1 set esal=2000 where eno=2

Select * from view1

We can update, if the rec is available in View


Index

It allows to fast access
It is 2 Types

Clustered Index

 Physical order or records is rearranged according to index

  • Unique clustered Index
  • Non- Unique clustered Index
  • Non clustered Index

Physical order or records never change according to index

  • Unique Non clustered Index
  • Non- Unique Non clustered Index
  • Unique clustered Index
EX:

Create table EMP
(
eno tinyint ,
ename varchar(20) ,
eage tinyint
)

----------------------------

Create unique clustered index index1
On EMP (eno)

----------------------------

Insert into EMP values (2,'ramu', 20) - Pass

Insert into EMP values (1,'raju', 22) – Pass

Select * from EMP  

Insert into EMP values (1,'srinu', 20) – Fail

Drop index emp.index1

Delete from EMP   

Non Unique clustered Index
EX:

Create clustered index index1
On EMP (eno)

----------------------------

Insert into EMP values (2,'ramu', 20) - Pass

Insert into EMP values (1,'raju', 22) – Pass

Select * from EMP  

Insert into EMP values (1,'srinu', 20) – Pass

 ----------------------------

Drop index emp.index1

Delete from EMP   

Unique Non clustered Index

EX:

Create unique nonclustered index index1
  On EMP (eno)

----------------------------

Insert into EMP values (2,'ramu', 20) - Pass

Insert into EMP values (1,'raju', 22) – Pass

Select * from EMP  

Insert into EMP values (1,'srinu', 20) – Fail

Non Unique Non clustered Index
EX:

Create nonclustered index index1
On EMP (eno)

----------------------------

Insert into EMP values (2,'ramu', 20) - Pass

Insert into EMP values (1,'raju', 22) – Pass

Select * from EMP 

Insert into EMP values (1,'srinu', 20) – Pass


Variables

Some memory location which contain Data
2 Types of Variables

  • System Defined Variables
  • User Defined Variables

Ex System Defined Variables:

@@IDLE

Returns the time in milliseconds that SQL Server has been idle since last started.

-----------------

Ex User Defined Variables:

declare
@a int,
@b int,
@c int
begin
set @a=100
set @b=50
set @c=@a+@b
print @c
end

Ex – control of flow statements

Ex if :

declare
@a int,@b int
begin
set @a=55
set @b=122
if @a>@b
print ' a is big'
else
print ' b is big'
end

-----------------------------------------------------------------------

Ex While :

declare
@a int
begin
set @a=1
while (@a<=10)
begin
print @a
set @a=@a+1
end
end
----------------------------

Ex Select Case:

Create table student
(
sno tinyint ,
sname varchar(25),
smarks tinyint )

-----------------------------------------------------------------------

Insert into student values (1,'ramu', 70)
Insert into student values (2,'ramu', 50)
Insert into student values (3,'raju', 20)

-----------------------------------------------------------------------

Select sno,sname,case
When smarks > 60 then 'first class'
When smarks > 40 and smarks <= 60 then 'second class'
else ‘fail’
End as Result
From student

Normal Forms

1st Normal Form (1NF)
Each attribute must be atomic
• No repeating columns within a row.
• No multi-valued columns.
1NF simplifies attributes
• Queries become easier.
sql_img1


-------------------------------

Second Normal Form (2NF)
Each attribute must be functionally dependent on the primary key.
• Functional dependence - the property of one or more attributes that uniquely determines the value of other attributes.
• Any non-dependent attributes are moved into a smaller (subset) table.
2NF improves data integrity.
• Prevents update, insert, and delete anomalies.




Functional Dependence

sql_img2


Name, detune, and dept_name are functionally dependent on emp_no.  
Skills are not functionally dependent on emp_no since it is not unique to each emp_no.


sql_img8

----------------------------------------


Third Normal Form (3NF)
Remove transitive dependencies.
• Transitive dependence - two separate entities exist within one table.
• Any transitive dependencies are moved into a smaller (subset) table.
3NF further improves data integrity.
• Prevents update, insert, and delete anomalies.


Transitive Dependence

sql_img6


Dept_no and dept_name are functionally dependent on emp_no however, department can be considered a separate entity.


sql_img7
Stored Procedures

  • It is collection of pre compiled Statements
  • For perform a task. It never returns values.

Advantages

  • High Performance  ( only Execution)
  • Reusability
  • Better Maintenance     (Small Modules)  
  • Remote Execution  (SP in Server, We can  execute it from Client )
  • Reduce the network Trafic

Stored Procedures are 4 types

  • With out Arguments
  • With Arguments
  • Default Arguments
  • Out Put Parameters

Ex – With Out Arguments

Create procedure proc1
As
Begin
Select * from EMP
End

----------------------------

Exec proc1

----------------------------

Ex – With Arguments

Ex 1

Create proc proc2 ( @x tinyint , @y tinyint)
As
Begin
Declare @z smallint
Set @z = @x +@y
Print @z
End

----------------------------

exec proc2 10,10

----------------------------

Ex2

Create proc proc3 (@x tinyint)
As
Begin
Select * from EMP where eno=@x
End
----------------------------

Exec proc3 1

----------------------------

Ex – With Default Arguments

Create proc proc4 (@x tinyint = 10, @y tinyint = 10)
As begin
Declare @z smallint
Set @z = @x + @y
Print @z
End
----------------------------

Exec proc4

Exec proc4 default,default

Exec proc4 default, 30

Exec proc4 20, default

----------------------------

Ex – Out Put Parameters

Create proc proc5 (@x tinyint , @y tinyint, @z smallint output)
As begin
Set @z = @x + @y
End
----------------------------

Declare @a smallint
Exec proc5 10,10,@a output
Print @a

----------------------------

Alter Procedure

Alter proc proc5 (@x tinyint, @y tinyint, @z smallint output)
As begin
Set @z = @x - @y
End

----------------------------

Declare @a smallint
Exec proc5 20,10,@a output
Print @a

----------------------------

To see the procedure definition

sp_helptext proc5

----------------------------

2 Types of stored procedures

  • Standard  Stored Procedure
  • Temporary  Stored Procedure


Ex - Temporary Stored Procedure

Create proc #proc6
As begin
Print ' Hai it is Temp Stored Procedure’
End

----------------------------

Sp_helptext #proc6    -- it is not working in ur DB

Go to Temp DB

Sp_helptext #proc6    -- it is  working in Temp DB

----------------------------

Exec   #proc6 -- working in ur DB & in Temp DB

----------------------------
Note :  Once u close SQL Server then Temp SP is Destroyed


Functions

Function is set of pre Compiled SQL Statements which are design to do a particular Task

Functions are 2 Types

  • System Defined Functions
  • User Defined Functions
  • User Defined Functions are 2 Types

Scalar (Return single value)
Table Valued (Return Table)
A. Scalar

It is 3 Types

  • With Out Arguments
  • With Arguments
  • Default Arguments
------------------------

Ex - Function With Out Arguments

Create function fun1 ()
Returns tinyint
As
Begin
Return 10
End
----------------------------------------------------

Select dbo.fun1()
----------------------------------------------------

Ex - Function With Arguments

Create function fun2 (@x tinyint, @y tinyint)
Returns tinyint
As
Begin
Declare @z smallint
Set @z = @x + @y
Return @z
End
----------------------------------------------------

Select dbo.fun2 (10, 10)
----------------------------------------------------
Select is used to Display the Data.
We can execute function with using Exec
              Ex: 
                        Exec fun1
   But No result will Display

Ex – Function with Default Arguments

Create function fun3 (@x tinyint =10, @y tinyint=10)
Returns tinyint
As
Begin
Declare @z smallint
Set @z = @x + @y
Return @z
End
----------------------------------------------------

Select dbo.fun3 (default,default)

Select dbo.fun3 (default, 5)

Select dbo.fun3 (30, default)

Select dbo.fun3 (15, 20)

----------------------------------------------------

B . Table Valued

This Function Returns Table

It is 2 Types

In Line
Multi Line


Ex – In Line

Create function fun4 ()
Returns table
As
Return (select * from EMP)

----------------------------------------------------

Select * from fun4 ()

Select eno,ename from fun4()

Select max (eage) from fun4 ()

Alter In Line Function

Alter function fun4 (@x tinyint)
Returns table
As
Return (select * from EMP where eno=@x)

----------------------------------------------------

Select * from fun4 (1)

----------------------------------------------------

Ex – Multi Line

Create function fun6 ()
Returns @x table (ename varchar(20),eage tinyint )
As begin
Insert into @x values ('ramu', 30)
Insert into @x values ('raju', 40)
Insert into @x values ('ramesh', 35)
Insert into @x values ('suresh', 25)
Return
End
----------------------------------------------------
Select * from fun6 ()

----------------------------------------------------
Create function fun5 ()
Returns @tab table (ename varchar(20),eage tinyint )
As begin
Insert @tab select ename,eage from emp
Update @tab set eage=eage+10
Return
End

----------------------------------------------------

Select * from fun5 ()

Select * from EMP
----------------------------------------------------

Alter Multi Line Function

Alter function fun6 ()
Returns @tab table (ename varchar(20),eage tinyint )
As begin
Insert into @tab values ('ramu', 30)
Insert into @tab values ('raju', 40)
Insert into @tab values ('ramesh', 35)
Insert into @tab values ('suresh', 25)
Update @tab set eage=eage+10
Return
End

Select * from fun6 ()

Alter function fun5 ()
Returns @tab table (ename varchar(20),eage tinyint )
As begin
Insert @tab values (‘xyz’, 20)
Return
End
----------------------------------------------------

Select * from fun5()





Built-in Functions

ASCII Function

     Return the ASCII code value of the leftmost    character of a character expression.

Ex:

Select ASCII (‘a'), ASCII ('a')
Select ASCII ('APPLE'), ASCII ('apple')

Char Function

It is a string function that converts an int ASCII code to a character.

Select char (65), char (97)

Left Function

It returns the part of a character string startup at a specified number of character from the left.

Example:

Select Left ('manager', 6)

Right Function

It returns part a character string starting at a specified number of int_expr characters from the right.

Ex:

Select Right ('manager', 4)

LTrim Function

It returns a character expression after removing leading blanks.

Ex:

Select LTrim ('      this is a Sample String')

RTrim Function

It returns a character string after truncating all trailing blanks.

Ex:

Select RTrim (‘this is a Sample String    ')

Len Function

It returns the number of characters of a given string expression, excluding trailing blanks.

Ex:

Select Len ('Sample Data')

Lower Function

It returns a character expression after converting uppercase data to lower case.
Ex:

Select Lower ('Sample Data')

Upper Function

It returns a character with lowercase character data converted to uppercase.

Select Upper ('sample data')

Reverse Function

It returns the reverse of a character expression

Ex:

Select Reverse ('Sample Data')

Space Function

It returns a string of repeated spaces.
Ex:

Select 'Sample Data' +Space (5) + 'for testing'

Substring Function

It returns part of a character string, binary, text or image expression 

Ex:

Select Substring ('Sample', 1, 3)



MATHEMATICAL FUNCTIONS

Round Function

It returns a numeric expression, rounded to the specified length of precision

Ex:

Select Round (15.143,2)

Floor Function

It returns the largest integer length than or equal to the given numeric expression.

Ex:

Select Floor (123.45), Floor (-123.45)

Square Function

It returns the square of the given expression

Ex:

Select Square (2)

Sqrt Function

It returns the square root of the given expression 
Ex:

Select Sqrt (4)

Power Function

It returns the value of the given expression to the specified power.

Ex:

Select Power (2, 3)

DATE FUNCTIONS

Date Arithmetic:

Date +n           Returns the Date after ‘n’ days

Date –n           Returns the Date before ‘n’ days

GetDate Function

Returns the current system date and time in the Ms-SQL Server standard interval format for date, time Values.


Example:

Select GetDate ()

Select GetDate () +3, GetDate () –3

Day Function

It represents an integer representing the day datepart of the specified data.

Ex:

Select Day ('03/12/1998')

Month Function

It represents an integer that represents the monthpart of a specified date.

Ex:

Select Month ('03/12/1998')

Year Function

It returns integer that represents the year part of a specified Date.

Ex:

Select Year ('03/12/1998')


Trigger

A trigger is a special type of stored procedure that automatically takes effect when the data in a specified table is modified. Triggers are invoked in response to an INSERT, UPDATE, and DELETE statement.

Statement which causes the trigger fired is called trigger statements.

INSERT, UPDATE, DELETE are called trigger statements.

2 Types of triggers are there

  • After trigger
  • Instead of trigger

Ex - After trigger

Create table EMP
(
eno  tinyint,
ename varchar(20),
eage tinyint
)
-----------------------------------------------------------------------

Create trigger trig1
On emp
After insert
As begin
Print 'ONE RECORD IS INSERTED IN EMP'
End
-----------------------------------------------------------------------

Insert emp values (1,'ramesh',15)
-----------------------------------------------------------------------

Alter trigger trig1
On EMP
After delete
As begin
Print ' RECORD DELETED IN EMP'
End
-----------------------------------------------------------------------
Delete from EMP where eno=1
Drop trigger trig1

Ex -2

Create trigger suntrig
On EMP
After insert,delete,update
As begin
Declare @x varchar(10)
Select @x = datename ( weekday,getdate())
If @x = 'sunday'
   Begin
   Rollback transaction
   Print 'Sunday is Holiday’
   End
Else
   Print 'Transaction Completed successfully’
End
-----------------------------------------------------------------------

 Insert EMP values (1,'ramesh', 15)

Select * from EMP
-----------------------------------------------------------------------
Change the data and Insert Again

Ex - Instead of trigger

(No need of rollback)



Create trigger trig2
On EMP
Instead of insert, delete, update
As begin
   Print 'Do not Perform Transaction'
End
-----------------------------------------------------------------------

Insert EMP values (1,'ramesh', 15)

Select * from EMP





Structured Query Language

SQL is a standard that every database should follow. That means all the SQL commands should work in all databases. Based on the type of operations we are performed on the database, SQL is divided into the following types :

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Transaction Control Language (TCL)
  • Data Control Language(DCL)

1. Data Definition Language:

It is used to create object (ex. table), alter the structure of an object and also to delete the object from the server. All these commend will affect only the structure of the database Object. The commends contained by

These languages are:

CREATE - Used to create an object

ALTER        - Used to modify an existing object

DROP         - Used to deleted the object

TRUNCATE - Used to deleted only the data of an object


2. Data Manipulation Language (DML):

The DML commands are most frequently used SQL commends. They are used to query and manipulate existing objects like tables. They should affect only the data of an existing structure. The commands existing in this language are:

SELECT     - Used to query the object data

INSERT      - Used to enter the data into the object

UPDATE    - Used to modify existing objects data

DELETE    - Used to delete the data of an object


3. Transaction Control Language (TCL):

A transaction is a logical unit of work. All changes made to the database can be referred to as a transaction. Transaction changes can be controlled with the use of this language commands. They are:

COMMIT         - Used to make the changes permanent

ROLLBACK    - Used to undo the changes


4. Data Control Language (DCL):

In general to access an object which was created by another user, at first, we must get the permission from the owner of the object. To provide the permission or remove the permission, we use this language commands. They are:

GRANT      - Used to provide permission

REVOKE   - Used to remove the permission


Cursors

A cursor is a pointer to the result of a SELECT statement.
Ex – select * from EMP

* All records will be loaded into memory.
* No relation between table & Result set.
* In cursors we can select data from Result set, not from table.

Cursor Statements:

We can control the records of a result set with the use of these cursor statements.
They are:

DECLARE
OPEN
FETCH
CLOSE
DEALLOCATE
DECLARE Statement is used to provide cursor declarations such as name of the cursor, the select statement to which result set the cursor should point etc.,

OPEN Statement starts using cursor. It executes the select statement in the cursor and points the cursor to the first record of the result set.

FETCH statement is used to retrieve the data in the current location and navigate the cursor to the required position.

CLOSE Statement stops using the cursor. But the resources used by the cursor are still open.

DEALLOCATE Statement removes entire resources that are used by the cursor.

Ex:

Declare cur1 cursor
For select * from EMP
Open cur1


------------------------------------------------------------------

Fetch next from cur1 -- Working

 Fetch prior from cur1 –Not working (Default is Forward only)

------------------------------------------------------------------
Close cur1
Deal locate cur1

------------------------------------------------------------------

 Scroll (Every operation work here)

Ex:

Declare cur1 cursor
Scroll
For select * from EMP
Open cur1

Fetch next from cur1     -- -- Working

Fetch prior from cur1    -- -- Working

Close cur1
Deal locate cur1


Note: if we did any modification on original table, when the cursor is in open then that are not affected in cursor.

U has to close the cursor & open it again.

To avoid this problem we have to use dynamic cursors.

Ex:

Select * from EMP

Declare cur1 cursor
Scroll
For select * from EMP
Open cur1

Fetch next from cur1

Update EMP set eage=50 where eno=3

Fetch prior from cur1 -- we are not getting updated value
              
Close cur1
Deal locate cur1

------------------------------------------------------------------

Declare cur1 cursor
Scroll
For select * from EMP
Open cur1

Fetch next from cur1
                                -- Now we are getting updated value

Close cur1
Deal locate cur1

------------------------------------------------------------------

Ex - Dynamic cursor:

Declare cur1 cursor
Scroll
Dynamic
For select * from EMP
Open cur1

Fetch next from cur1

Update EMP set eage=60 where eno=3

Fetch prior from cur1 – changes are effected

Close cur1
Deal locate cur1
------------------------------------------------------------------

Instead of Manually Press F5 in fetch next from cur1
We can use While Loop until end of table

Ex:

Declare cur1 cursor
Scroll
Dynamic
For select * from EMP
Open cur1
---------------------------------------------

While (@@fetch status=0)
   Begin

   Fetch next from cur1
   End
-------------------------------------------

Close cur1
Deal locate cur1














Group by Example


sql_img_a
create table  emp
create table  emp
(
eno tinyint,
ename varchar(20),
dept  varchar(5),
esal  smallint
)
insert into emp values(5,'kishore','A',9000)
select * from  emp
select dept,sum(esal)as Totalsal from  emp   group by dept
Out put :
sql_img_b

0 comments :

Post a Comment