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.
-------------------------------
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
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.
----------------------------------------
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
Dept_no and dept_name are functionally
dependent on emp_no however, department can be considered a separate entity.
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
create table emp
create table emp
(
eno tinyint,
ename varchar(20),
dept varchar(5),
esal smallint
)
(
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 :
0 comments :
Post a Comment