MS SQL SERVER
Microsoft SQL Server is a relational database management system developed by Microsoft. As a database, it is just a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network ( including the Internet ). There are at least a dozen different editions of Microsoft SQL Server aimed at different audiences and for different workloads (ranging from small applications that store and retrieve data on the same computer, to millions of users and computers that access huge amounts of data from the Internet at the same time).
Data Types
In a Database, each column, local
variable, expression, and parameter has a related data type. A data type is an
attribute that specifies the type of data that the object can hold: integer
data, character data, monetary data, data and time data, binary strings, and so
on.
Integer Types: To hold the
Integer values it provides with tinyint, smallint, int and bigint
data types with sizes 1, 2, 4 and 8 bytes respectively.
Boolean Type: To hold the
Boolean values it provides with bit data type that can take a value of
1, 0, or NULL.
Note: The string values TRUE and
FALSE can be converted to bit values: TRUE is converted to 1 and FALSE
is converted to 0.
Decimal Types: To hold the
decimal values it provides with the following types:
-decimal[
(p[ , s] )] and numeric[ (p[
, s] )]
p (precision)
The
maximum total number of decimal digits that can be stored, both to the left and
to the right of the decimal point. The precision must be a value from 1 through
the maximum precision of 38. The default precision is 18.
s (scale)
The
maximum number of decimal digits that can be stored to the right of the decimal
point. Scale must be a value from 0 through p. Scale can be specified
only if precision is specified. The default scale is 0.
Storage
sizes of Decimal and Numeric types vary, based on the precision.
Precision
|
Storage bytes
|
1 – 9
|
5
|
10-19
|
9
|
20-28
|
13
|
29-38
|
17
|
Note:
numeric is functionally equivalent to decimal.
-float [ ( n ) ] and real
-Approximate-number data types
for use with floating point numeric data. Floating point data is approximate;
therefore, not all values in the data type range can be represented exactly.
Where n is the number of bits that are used to store the mantissa of the
float number in scientific notation and, therefore, dictates the
precision and storage size. If n is specified, it must be a value
between 1 and 53. The default value of n is 53.
n value
|
Precision
|
Storage size
|
1-24
|
7 digits
|
4 bytes
|
25-53
|
15 digits
|
8 bytes
|
Monetary or Currency Types: To hold the Currency values it
provides with the following types which takes a scale of 4 by default:
money
|
-922,337,203,685,477.5808 to
922,337,203,685,477.5807
|
8 bytes
|
smallmoney
|
- 214,748.3648 to 214,748.3647
|
4 bytes
|
Date and Time Values: To hold the Date and Time values
of a day it provides with the following types:
Data type
|
Range
|
Accuracy
|
datetime
|
January 1, 1753, through December 31, 9999
|
3.33 milliseconds
|
smalldatetime
|
January 1, 1900, through June 6, 2079
|
1 minute
|
Values
with the datetime data type are stored internally by the Microsoft SQL
Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the
number of days before or after the base date: January 1, 1900. The base
date is the system reference date. The other 4 bytes store the time of day
represented as the number of milliseconds after midnight.
The
smalldatetime data type stores dates and times of day with less
precision than datetime. The Database Engine stores smalldatetime
values as two 2-byte integers. The first 2 bytes store the number of days after
January 1, 1900. The other 2 bytes store the number of minutes since midnight.
String
Values: To hold
the string values it provides with the following types:
char [ ( n ) ]
Fixed-length,
non-Unicode character data with a length of n bytes. n must be a
value from 1 through 8,000. The storage size is n bytes.
varchar [ ( n | max
) ]
Variable-length,
non-Unicode character data. n can be a value from 1 through 8,000. max
indicates that the maximum storage size is 2^31-1 bytes. The storage size is
the actual length of data entered + 2 bytes.
text
It
was equal to varchar(max) this data type will be removed in a future version of
Microsoft SQL Server. Avoid using these data types in new development work use
varchar(max) instead.
Unicode Data types for storing Multilingual Characters are
nchar, nvarchar and ntext where n stands for national.
nchar [ ( n ) ]
Fixed-length
Unicode character data of n characters. n must be a value from 1
through 4,000. The storage size is two times n bytes.
nvarchar [ ( n | max
) ]
Variable-length
Unicode character data. n can be a value from 1 through 4,000. max
indicates that the maximum storage size is 2^31-1 bytes. The storage size, in
bytes, is two times the number of characters entered + 2 bytes.
ntext
It
was equal to nvarchar(max) this data type will be removed in a future version
of Microsoft SQL Server. Avoid using these data types in new development work
use nvarchar(max) instead.
Binary Values: To hold the binary values likes
images, audio clips and video clips we use the following types.
binary [ ( n ) ]
Fixed-length
binary data with a length of n bytes, where n is a value from 1
through 8,000. The storage size is n bytes.
varbinary [ ( n | max)
]
Variable-length
binary data. n can be a value from 1 through 8,000. max indicates
that the maximum storage size is 2^31-1 bytes. The storage size is the actual
length of the data entered + 2 bytes.
Image
It
was equal to varbinary(max) this data type will be removed in a future version
of Microsoft SQL Server. Avoid using these data types in new development work
use varbinary(max) instead.
- Use char,
nchar, binary when the sizes of the column data entries are
consistent.
- Use varchar, nvarchar, varbinary when the sizes of the column data entries vary considerably.
- Use varchar(max), nvarchar(max), varbinary(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
Other Types: Apart from the above it provides
some additional types like -
timestamp: Is a data type that exposes
automatically generated, unique binary numbers within a database. The storage
size is 8 bytes. You can use the timestamp column of a row to easily
determine whether any value in the row has changed since the last time it was
read. If any change is made to the row, the timestamp value is updated. If no
change is made to the row, the timestamp value is the same as when it was
previously read.
Uniqueidentifier: Is a 16-byte GUID which is
initialized by using the newid() function or converting a string constant in
the form of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx which is used to guarantee
that rows are uniquely identified across multiple copies of the table.
Xml: Is the data type that stores XML
data. You can store xml instances in a column, or a variable of xml
type. The stored representation of xml data type instances cannot exceed
2 gigabytes (GB) in size.
Database:
A database is a place to store your application data. It can be a simple text file, xml file, binary file, MS Access database, SQL Server database, Oracle database etc. In other words, your database need not be SQL Server or Oracle - it can be just plain text files.
However, there are several key differences between storing data in text files and database server based databases. Text file based database storage may be good enough for small applications which deal with very less amount of data. Most of the applications developers followed this approach before database systems like FoxPro, MS Access, SQL Server etc became popular.
Data storage and retrieval become very efficient if you store very large volume of data in text files.The popular database systems in the market are SQL Server and Oracle along with few others. In the coming chapters, you will learn how the database systems evolved and how to use them.
SQL Server manages the objects in
a container known as Database, where we can have multiple databases present in
it, each database when created creates 2 files internally those or .mdf and
.ldf file.
Syntax for creating a database:
-CREATE
DATABASE <db_name>
-Database names must be unique
within an instance of SQL Server.
-Any Object name in sqlserver can
be of 1 through 128 characters
Tables:
-It is the object, which will
store the information in the database in the form of rows and columns.
Syntax for creating a Table:
-CREATE
TABLE <table_name>(
column_name1 <dtype> [width],
column_name1 <dtype> [width],
………………….
column_namen <dtype> [width])
-Table names must be unique
within the database.
-Column names must be unique
within the table.
-Every table can have maximum of
1024 and minimum of 1 column.
-CREATE
TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2))
Populating Data into Tables: after the table gets created to
populate the data into it we use
Insert Statement:
Syntax for Insert statement:
-INSERT
INTO <table_name> [(col1, col2, ……..coln)]
VALUES (val1, val2, …….valn)
Examples:
INSERT
INTO BANK VALUES (101, ‘VENKAT’, 4500)
-In this case we need to provide the values
for all the columns in the same order they are present in the table.
-String and Date values have to
be enclosed in single quotes.
INSERT
INTO BANK (CUSTID, CNAME, BAL) VALUES (102, ‘SUBASH’, 5600)
-This statement is same as above
statement.
-If we want to change the order
of columns while inserting:
INSERT
INTO BANK (CNAME, CUSTID, BAL) VALUES (‘SURESH’, 103, 6500)
-If we want to insert data only
into required columns then:
INSERT
INTO BANK (CUSTID, BAL) VALUES (104, 3600)
-In this case the columns into
which values are not supplied are filled with Null value.
-We can also insert Null’s
explicitly into the column in the following way:
INSERT INTO BANK VALUES (105, NULL,
5400)
Retrieving the data from Tables: if we want to retrieve the
information from the table use
Select Statement:
Basic Syntax for Select
statement:
SELECT
< * | COLLIST > FROM <TNAME> [CONDITIONS]
-
‘*’
Represents all the columns of the table in the same order.
-
COLLIST
is used for specifying the required no of columns and in required order.
-
CONDITIONS
are optional which can be used for retrieving the required rows.
-SELECT
* FROM BANK
-SELECT
CUSTID, CNAME, BAL FROM BANK
-SELECT
CNAME, BAL, CUSTID FROM BANK
-SELECT
CUSTID AS ACCNO, CNAME, BAL FROM BANK
-We can specify an alias name for
any required column while retrieving known as Column Alias.
-If we want to retrieve required
rows then we use a conditional statement where:
-SELECT
* FROM BANK WHERE CUSTID=104
-SELECT
CUSTID, BAL FROM BANK WHERE CNAME=’SURESH’
-SQL Server does not have any
case restrictions while writing the conditions.
Handling Null Values: The value NULL means the data
value for the column is unknown or not available, so we cannot use equality
condition while getting the data based on null values.
SELECT
* FROM EMP WHERE CNAME=NULL
-The above statement will not get
any result because no 2 null values can be compared so to get the data based on
Null values we should use the IS NULL operator as following:
SELECT
* FROM EMP WHERE CNAME IS NULL
Updating data present in the
tables: if we
want to Update the data existing in the table we use
Update Statement:
Syntax:
-UPDATE
<TNAME> SET <CNAME>=<VALUE> [, …..] [CONDITIONS]
Note: We can modify a single
column or multiple columns using the update statement all the rows that satisfy
the condition gets affected.
-UPDATE BANK SET CNAME=’RAMESH’
WHERE CUSTID=104
-UPDATE
BANK SET CNAME=’RAJESH’, BAL=3000 WHERE CUSTID=105
Deleting data present in the
tables: if we
want to delete rows of data present in the table we use
Delete Statement:
Syntax:
-DELETE
FROM <TNAME> [CONDITIONS]
-DELETE
FROM BANK WHERE CUSTID=105
-DELETE
FROM BANK
Constraints: used to enforce the integrity of
the data in the columns, SQL Server 2005 provides the following mechanisms to
enforce the integrity of the data in column:
-Not
Null
-Unique
-Primary
Key
-Check
-Default
-Foreign Key
Not Null: If it is imposed on a column that
column will not allow Null Values into it; this can be imposed on any no of
columns.
-CREATE
TABLE <table_name>(
column_name1 <dtype> [width] [Not Null],
column_name1 <dtype> [width] [Not Null],
………………….
column_namen <dtype> [width] [Not Null])
Recreating the bank table with
Not Null Constraint on it:
CREATE
TABLE Bank(Custid int Not Null, Cname varchar(50), Bal decimal(7,2) Not Null)
After creating this if we try to
insert a null value into the Custid or Bal columns it will restrict us:
INSERT
INTO Bank VALUES (NULL, ‘RAJU’, 3500)
INSERT
INTO Bank (CUSTID, CNAME) VALUES (101, ‘RAVI’)
The drawback with Not Null
Constraint is even if it restricts null values it will not restrict duplicate
values, if they has to be restricted we use the Unique Consraints.
Unique: If it is imposed on a column or
columns they will not allow duplicate Values into it.
Note: Unique, Primary Key, Check
and Foreign Key Constraints can be imposed in two different ways:
-Column
Level Definition
-Table
Level Definition
Column Level Definition: In this
case the constraint definition is immediately followed after the column
definition. The syntax is:
-CREATE
TABLE <table_name>(
column_name1 <dtype> [width] [
[Constraint <Name>] <Type> ],
column_name1 <dtype> [width] [
[Constraint <Name>] <Type> ],
………………….
column_namen <dtype> [width] [
[Constraint <Name>] <Type> ],
Recreating the bank table with
Unique Constraint on it:
CREATE
TABLE Bank(Custid int Unique, Cname varchar(50), Bal decimal(7,2) Not Null)
After creating this if we try to
insert a duplicate value into the Custid column it will restrict us:
INSERT
INTO Bank VALUES (101, ‘RAJU’, 3500)
INSERT
INTO Bank VALUES (101, ‘RAVI’, 4500)
Note: Internally Unique, Primary
Key, Check and Foreign Key Constraints are identified by using some unique name
which has to be given by us or else the system will give a name, so when we
violate these constraints it will show the name of the constraint in the error
message, by seeing which we require to identify on which column we are getting
the problem, but if the table is not created by us or we don’t remember the
structure of the table we cannot identify. So it is advised to give a name to
the constraint so that when it violates the error message shows the name of the
constraint using which we can easily identify where the violation has been
done.
While giving a name to the
Constraint they follow some conventions like:
<ColumnName_ConstraintType>
<TableName_ColumnName_ConstraintType>
Recreating the bank table with
Unique Constraint by giving a name to it:
CREATE
TABLE Bank(Custid int Constraint Cusid_UQ Unique, Cname varchar(50), Bal
decimal(7,2) Not Null)
Table Level Definition: In this
case the constraint definition is immediately followed after the column
definition. The syntax is:
-CREATE
TABLE <table_name>(
column_name1 <dtype> [width],
column_name1 <dtype> [width],
………………….
column_namen <dtype> [width],
[ [Constraint <Name>] <Type>
(<Collist>)],
…………………….)
Note: A Not Null Constraint
Cannot be defined table level.
CREATE
TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Not Null, Constraint
Cusid_UQ Unique(Custid))
-In this case because the
constraint is defined in the end of all the columns it cannot understand to
which column the constraint depends so we need to specify the column name
beside the constraint for identification.
-There will not be any difference
in execution whether the constraint is defined in table level or column level.
-When we define a constraint in
table level we can define composite constraint i.e. a single constraint on
multiple columns.
CREATE
TABLE BankDetails(CityCode varchar(10), BranchCode varchar(10), Constraint
CC_BC_UQ Unique(CityCode, BranchCode))
INSERT
INTO BankDetails Values(‘C1’, ‘B1’)
INSERT
INTO BankDetails Values(‘C1’, ‘B2’)
INSERT
INTO BankDetails Values(‘C1’, ‘B3’)
INSERT
INTO BankDetails Values(‘C2’, ‘B1’)
INSERT
INTO BankDetails Values(‘C2’, ‘B2’)
INSERT
INTO BankDetails Values(‘C2’, ‘B3’)
-In this case all the statements
are valid because a composite unique constraint checks the uniqueness on the
combination of columns, but not on a single column.
The drawback with Unique
Constraint is even if it restricts duplicate values it will allow a single null
value in to the column. If we want to restricted duplicate values as well as
null values we need to use Primary Key Constraint.
-While Creating a primary key
constraint we need to keep this in mind i.e. a table can contain only a single
primary key present on it which can be present on a single column or multiple
columns also.
Creating Primary Key in column
level:
CREATE
TABLE Bank(Custid int Constraint Cusid_PK Primary Key, Cname varchar(50), Bal
decimal(7,2) Not Null)
Creating Primary Key in table
level:
CREATE
TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Not Null, Constraint
Cusid_PK Primary Key(Custid))
Creating a Composite Primary Key
in table level:
CREATE
TABLE BankDetails(CityCode varchar(10), BranchCode varchar(10), Constraint
CC_BC_PK Primary Key(CityCode, BranchCode))
Check
Constraint: If we want to check the values present in a column
to be according to a specified value we use this constraint.
-If we
want to restrict the Bal in the bank table should be some specified values then
we can use the constraint as following:
CHECK (Bal>=1000) –> Checking
Bal should be greater than equal to 1000
CHECK (Bal BETWEEN 1000 AND 9999) -> Checking Bal
should be within the range of 1000 and 9999
CHECK (BAL IN (3000, 5000, 7000)) -> Checking the
Bal Should be within any of the three values only
Creating Check Constraint in
column level:
CREATE
TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Constraint Bal_CK
Check (Bal>=1000))
Creating Check Constraint in
table level:
CREATE
TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2), Constraint Bal_CK
Check(Bal BETWEEN 1000 AND 9999))
Default Value: The default value for any column
if a not null constraint is not present on it is “NULL”, which can be changed
by using the Default Clause while creating the table as following:
CREATE
TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Default 1000)
-In the above case if have not
specified any value to the Bal column while inserting then it takes 1000 as
default
INSERT
INTO Bank (Custid, Cname) VALUES (101, ‘Ravi’)
Identity
Function: Generally for any column if we want to insert only
unique values then we can hand over the task to the identity function, so that
it takes the responsibility of inserting a unique value in to the column as
following:
Colname
<dtype> [width] Identity [(Seed, Incr)]
Seed – It is the starting value for
the identity function.
Incr – It is the difference between
to subsequent values generated by the function.
-Both
of them are optional, if not specified 1 and 1 are taken as values.
-When
we use the identity function on a column we cannot explicitly insert any values
into the column using the insert statement.
-A
table can have only one identity column present in it.
-Generally
we use this on Primary Key Columns.
CREATE
TABLE Bank(Custid int identity(101, 1), Cname varchar(50), Bal decimal(7,2))
-In this case when we insert rows
into the table then it automatically generates a identity value starting from
101.
INSERT
INTO Bank (Cname, Bal) VALUES (‘Raju’, 3500)
Foreign
Key Constraint:
it is a column or combination of columns that is used to establish and enforce
a link between the data in two tables. In a foreign key reference, a link is
created between two tables when the column(s) in a table reference the
column(s) that hold the primary key of other table, which becomes a foreign key
in the first table.
For example, the Dept.Deptno table below has a link to the Emp.Deptno table because there is a logical relationship between Dept table and Emp table. The Deptno column in the Emp table matches the primary key column of the Dept table. The Deptno column in the Emp table is the foreign key to the Dept table. In this case the value that is going to be inserted into the Deptno column of the Emp table should be present in the Deptno column of the Dept table or should be a null values.
DEPT EMP
Deptno ___________________ Deptno
Dname Empno
Location Ename
Job
HireDate
Sal
Comm
Mgr
-
In
this case the Dept table is called as Parent table and Emp table is called as
Child table.
-
Dept.Deptno
is called as Reference Key column on which either Primary Key Constraint or
Unique Constraint has to be imposed.
-
Emp.Deptno
is called as Foreign Key column on which the Foreign Key Constraint has to be
imposed, with this only the link gets established between the 2 tables.
Create Table Dept (Deptno int Constraint Deptno_Pk
Primary Key, DName varchar(50), Loc varchar(50))
1.
Insert
into Dept values (10, 'Marketing', 'Mumbai')
2.
Insert
into Dept values (20, 'Sales', 'Chennai')
3.
Insert
into Dept values (30, 'Finance', 'Delhi')
4. Insert into Dept values (40,
'Production', 'Kolkota')
-Creating Foreign Key Constraint
in column level:
Create
table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate
datetime, Sal Money, Comm Money, Deptno int Constraint Deptno_Ref References
Dept (Deptno))
-Creating Foreign Key Constraint
in column level: while defining constraint in table level we need to explicitly
use the Foreign Key clause to specify the Foreign Key Column:
Create
table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate
datetime, Sal Money, Comm Money, Deptno int,
Constraint
Deptno_Ref Foreign Key (Deptno) References Dept (Deptno))
-Now
when we try to insert values into the Emp table the Deptno what we give
should be only the 4 values (10, 20, 30, 40) present in the dept table or a
null value, if we try to insert any other value the insert statement fails.
1.
Insert
into Emp Values (1001, 'Suresh', 'President', NULL, '01/01/78', 5000, NULL, 10)
2.
Insert
into Emp Values (1002, 'Ramesh', 'Manager', 1001, '01/01/78', 4000, NULL, 20)
3.
Insert
into Emp Values (1003, 'Ravi', 'Manager', 1001,
'01/01/78', 3500, NULL, 30)
4.
Insert
into Emp Values (1004, 'Vijay', 'Manager', 1001, '01/01/78', 4000, NULL, 40)
5.
Insert
into Emp Values (1005, 'Ajay', 'Salesman', 1003, '02/04/79', 3000, NULL, 50)
-In this case the first 4
statements gets executed but last statement fails because the Deptno given is
not present in the dept table.
The
Foreign Key constraint enforces referential integrity by guaranteeing that
changes cannot be made to data in the primary key table if those changes
invalidate the link to data in the foreign key table. If an attempt is made to
delete the row in a primary key table or to change a primary key value, the
action will fail when the deleted or changed primary key value corresponds to a
value in the FOREIGN KEY constraint of another table. To successfully change or
delete a row in a FOREIGN KEY constraint, you must first either delete the
foreign key data in the foreign key table or change the foreign key data in the
foreign key table, which links the foreign key to different primary key data.
By using
cascading referential integrity constraints, you can define the actions that
the SQL Server 2005 takes when a user tries to delete or update a key value in
the master table to which existing foreign keys point.
The REFERENCES clauses of
the CREATE TABLE statements support the ON DELETE and ON UPDATE clauses:
-ON DELETE <NO
ACTION | CASCADE | SET NULL | SET DEFAULT>
-ON UPDATE <NO
ACTION | CASCADE | SET NULL | SET DEFAULT>
NO ACTION is the default if ON
DELETE or ON UPDATE is not specified.
ON DELETE NO ACTION: Specifies
that if an attempt is made to delete a key value in the master table, which is
referenced by foreign keys in other tables, an error is raised and the DELETE
statement will not execute.
ON UPDATE NO ACTION: Specifies
that if an attempt is made to update a key value in the master table, which is
referenced by foreign keys in other tables, an error is raised and the UPDATE
statement will not execute.
When NO ACTION is specified we
can delete or update or delete rows in the master table if they are referenced
by child table rows, but we can perform those operations when we use CASCADE,
SET NULL AND SET DEFAULT CLAUSES:
ON DELETE CASCADE: Specifies that
if an attempt is made to delete a key value in the master table, which is
referenced by foreign keys in other tables, all rows that contain those foreign
keys in child table are also deleted.
ON UPDATE CASCADE: Specifies that
if an attempt is made to update a key value in the master table, which is
referenced by foreign keys in other tables, all the foreign key values will
also be updated to the new value specified for the key.
ON DELETE SET NULL: Specifies
that if an attempt is made to delete a key value in the master table, which is
referenced by foreign keys in other tables, all rows that contain those foreign
keys in child table are set to NULL, provided the foreign key column allows
NULL values into it.
ON UPDATE SET NULL: Specifies
that if an attempt is made to update a key value in the master table, which is
referenced by foreign keys in other tables, all rows that contain those foreign
keys in child table are set to NULL, provided the foreign key column allows
NULL values into it.
ON DELETE SET DEFAULT: Specifies
that if an attempt is made to delete a key value in the master table, which is
referenced by foreign keys in other tables, all rows that contain those foreign
keys in child table are set to default value. All foreign key columns of the
target table must have a default definition for this constraint to execute. If
there is no explicit default value set, NULL becomes the implicit default value
of the column.
ON UPDATE SET DEFAULT: Specifies
that if an attempt is made to update a key value in the master table, which is
referenced by foreign keys in other tables, all rows that contain those foreign
keys in child table are set to default value. All foreign key columns of the
target table must have a default definition for this constraint to execute. If
there is no explicit default value set, NULL becomes the implicit default value
of the column.
We can use any of the rules
beside the column as following:
Deptno int
Constraint Deptno_Ref References Dept (Deptno) on delete cascade on update
cascade
In the same way you can use any
rule there and also not mandatory to specify both the delete and update rule,
we can use any rule what we require.
Alter Command: After
creating a table if we want to make any modifications to the structure of the
table we use the Alter Command. Using alter command we can perform the
following:
-Increase/Decrease the width of a
column.
-Change the data type of a
column.
-Change Null to Not Null and Not
Null to Null
-Add a new column to the table.
-Drop an existing column from the
table.
-Add a constraint to a column of
the table.
-Drop an existing constraint
present on a column from the table.
-To perform the first 3
operations the syntax is:
ALTER TABLE
<TNAME> ALTER COLUMN <COLNAME> <DTYPE> [WIDTH] [NULL | NOT
NULL]
First create a table as
following:
CREATE
TABLE Students (SNO int, Sname varchar(50), Class int)
Increasing the width of a column:
ALTER
TABLE Students ALTER COLUMN Sname varchar(100)
Decreasing the width of a column:
ALTER
TABLE Students ALTER COLUMN Sname varchar(25)
Changing the data type of the
column:
ALTER
TABLE Students ALTER COLUMN Sname nvarchar(25)
Adding a Not Null Constraint:
ALTER
TABLE Students ALTER COLUMN Sname nvarchar(25) Not Null
Removing a Not Null Constraint:
ALTER
TABLE Students ALTER COLUMN Sname nvarchar(25) Null
Syntax to add a new column:
ALTER
TABLE <TNAME> ADD <COLNAME> <DTYPE> [<WIDTH>]
[ [CONSTRAINT
<CONS NAME>] <CONS TYPE> ]
Adding a column to the table with out any constraint:
ALTER TABLE Students ADD Fees Money
Adding a column to the table with
a constraint:
ALTER
TABLE Students ADD Sid int Constraint Sid_UQ UNIQUE
Syntax to drop an existing
column:
ALTER
TABLE <TNAME> DROP COLUMN <COLNAME>
Dropping the Sid Column:
ALTER TABLE Students DROP COLUMN Sid
Syntax to Add a Constraint:
ALTER TABLE
<TNAME> ADD [ CONSTRAINT <CON NAME> ] <CONS TYPE> (COLLIST)
Adding a check constraint on the
Fees column:
ALTER
TABLE Students
ADD Constraint Fees_CK Check (Fees>1500)
Adding a primary key constraint on the Sno column:
ALTER
TABLE Students ALTER COLUMN SNO INT NOT NULL
ALTER
TABLE Students ADD Constraint Sno_PK Primary Key(SNO)
Syntax to Drop a Constraint:
ALTER
TABLE <TNAME> DROP CONSTRAINT <CONS NAME>
Dropping the check constraint
present on the Fees column:
ALTER TABLE Students DROP Constraint Fees_CK
Drop Command: If we want
to destroy the existing tables present in the database we use the Drop Command.
Syntax: DROP TABLE
<TNAME>
Dropping the Students Table:
DROP TABLE Students
Truncate Command: Removes
all rows from a table. TRUNCATE TABLE is functionally the same as the DELETE
statement with no WHERE clause specified.
Syntax: TRUNCATE TABLE
<TNAME>
Truncating the EMP Table:
TRUNCATE TABLE Students
The difference between Truncate
and Delete is:
- Truncate table is faster in execution.
- Truncate will reset the identity function if present
on the table to initial value again which will not happen in delete.
FUNCTIONS: SQL Server 2005
provides built-in functions that can be used to perform certain operations.
Functions can be used or included in the following:
-
The select list of a query that uses a SELECT statement
to return a value.
-
A WHERE clauses search condition of a SELECT statement
to limit the rows that qualify for the query.
Syntax for executing a function:
SELECT <Fun
Name> ( [ <expressions> ] )
-The expression can be a constant
values or a name of a column.
Functions can be classified into
2 types:
-Single
Row Functions
-Group
Functions
A single row function executes
once for each row that is present in the table where as group functions take
multiple rows into consideration and returns a single value as output.
Single Row Function Categories:
-Mathematical
Functions
-String
Functions
-Date
and Time Functions
-System
Functions
Mathematical
Functions: These functions perform a
calculation, usually based on input values that are provided as arguments, and
return a numeric value; they take “n” as input where n is a numeric expression.
ABS (n): A mathematical function
that returns the absolute (positive) value of the specified numeric expression.
Select ABS(10) Ouput: 10
Select ABS(-10) Ouput: 10
CEILING (n): Returns the smallest
integer greater than, or equal to, the specified numeric expression.
SELECT
CEILING(15.6) OUTPUT: 16
SELECT
CEILING(15.6) OUTPUT: -15
CEILING (n): Returns the largest
integer less than or equal to the specified numeric expression.
SELECT
FLOOR(15.6) OUTPUT:
15
SELECT
FLOOR(15.6) OUTPUT:
-16
LOG (n): Returns the natural
logarithm of the specified expression, i.e. base-e
SELECT
LOG(10) OUTPUT:
2.30258509299405
LOG10 (n): Returns base-10
logarithm of the specified expression, i.e. base e
SELECT
LOG10(10) OUTPUT:
1
PI(): Returns the constant value
of PI.
SELECT
PI() OUTPUT:
3.14159265358979
POWER(n, m): Returns the value of
the specified expression n to the specified power m.
SELECT
POWER(10, 3) OUTPUT:
1000
RAND ( [SEED] ): Returns a random float value
from 0 through 1.
-
SEED: Is an integer expression that gives the seed
value. If seed is not specified, the Database Engine assigns a seed
value at random. For a specified seed value, the result returned is always the
same.
SELECT RAND() -Each time we execute we get a random
value.
SELECT RAND(100) -Each time we execute we get the
same value.
ROUND ( n , length
[ ,function ] ): Returns a numeric expression, rounded to the
specified length or precision.
SELECT
ROUND(156.567, 2) OUTPUT:
156.57
SELECT
ROUND(156.567, 1) OUTPUT:
156.6
SELECT
ROUND(156.567, 0) OUTPUT:
157
-If the seed is positive rounding
will be done after the decimal, if it is negative rounding will be done before
the decimal:
SELECT
ROUND(156.567, -1) OUTPUT:
160
SELECT
ROUND(156.567, -2) OUTPUT:
200
-If we specify the optional
parameter function that is an integer value we can decide to truncate the value
or round the value. If it is 0 (default) rounds the value and value greater
than 0 truncates the value.
SELECT
ROUND(156.567, 2, 1) OUTPUT:
156.56
SELECT
ROUND(156.567, -2, 1) OUTPUT:
100
SIGN(n): Returns the positive (+1), zero (0), or
negative (-1) sign of the specified expression.
-
If n<0 it returns -1
-
If n=0 it returns 0
-
If n>0 it returns 1
SELECT
SIGN(-100) OUTPUT:
-1
SELECT SIGN(0) OUTPUT:
0
SELECT SIGN(100) OUTPUT: 1
SQRT(n): Returns the square root
of the specified expression.
SELECT
SQRT(81) OUTPUT:
9
SELECT SQRT(30) OUTPUT:
5.47722557505166
SQUARE(n): Returns the square of
the specified expression.
SELECT
SQUARE(35) OUTPUT:
1225
-Apart from the above it provides
with trigonometric function like COS, COT, SIN, TAN, ACOS, ASIN, ATAN for which
we need to provide the degrees.
String Functions: These functions
perform an operation on a string input value and return a string or numeric
value.
ASCII(s): Returns the ASCII code
value of the leftmost character of the expression.
ASCII(‘A’) OUTPUT:
65
ASCII(‘BCD’) OUTPUT:
66
CHAR(n): Converts the given ASCII
code to a character.
CHAR(97) OUTPUT:
a
NCHAR(n): Returns the Unicode
character with the specified integer code ranging between 0 to 65, 535, as
defined by the Unicode standard.
CHAR(300) OUTPUT:
Ĭ
CHARINDEX(search exp, string
exp [ , start_location ] ): Returns the starting position of the
search exp in the string exp which can also be a column name.
CHARINDEX(‘O’,
‘HELLO WORLD’) OUTPUT: 5
-In this case it returns 5 as
output because it starts its search from the beginning of the string, we can
change it by using the start location optional parameter.
CHARINDEX(‘O’,
‘HELLO WORLD’, 6) OUTPUT: 8
-WAQ to get the details of
employees whose name contains the character ‘M’ in it.
Sol: SELECT * FROM EMP WHERE
CHARINDEX(‘M’, ENAME)>0
LEFT(s, n): Returns the
left part of the string with the specified number of characters.
SELECT
LEFT(‘HELLO’, 3) OUTPUT:
HEL
-WAQ to get the details of
employees whose name contains the first 2 characters as ‘VE’.
Sol: SELECT * FROM EMP WHERE
LEFT(ENAME, 2)=’VE’
RIGHT(s, n): Returns the
right part of the string with the specified number of characters.
SELECT
RIGHT(‘HELLO’, 3) OUTPUT:
LLO
-WAQ to get the details of
employees whose name ends with characters ‘TT’.
Sol: SELECT * FROM EMP WHERE
RIGHT(ENAME, 2)=’TT’
SUBSTRING(s, start,
length): Returns a part of a string from string s starting from start position,
where length is the no of chars to be picked.
SELECT
SUBSTRING(‘HELLO’, 1, 3) OUTPUT:
HEL
SELECT
SUBSTRING(‘HELLO’, 3, 3) OUTPUT:
LLO
SELECT
SUBSTRING(‘HELLO’, 2, 3) OUTPUT:
ELL
-WAQ to get the details of
employees whose names 3rd and 4th characters are ‘TI’.
Sol: SELECT * FROM EMP WHERE
RIGHT(LEFT(ENAME, 4), 2)=’TI’
Sol: SELECT * FROM EMP WHERE
SUBSTRING(ENAME, 3, 2)=’TI’
LEN(s): Returns the number of
characters of the specified string expression, excluding trailing blanks.
SELECT
LEN(‘HELLO’) OUTPUT:
5
SELECT
LEN(‘ HELLO’) OUTPUT: 8
-WAQ to get the details of
employees whose names was 5 characters in length
Sol: SELECT * FROM EMP WHERE
LEN(ENAME)=5
SELECT
LEN(‘HELLO ‘) OUTPUT: 5
LOWER(s): Returns a character
expression after converting the given character data to lowercase.
SELECT
LOWER(‘Hello’) OUTPUT:
hello
UPPER(s): Returns a character
expression after converting the given character data to uppercase.
SELECT
UPPER(‘Hello’) OUTPUT:
HELLO
LTRIM(s): Returns a character expression after it
removes leading blanks.
SELECT
LEN(LTRIM(‘ HELLO’)) OUTPUT: 5
SELECT 'HELLO ' + LTRIM(' WORLD') OUTPUT: HELLO WORLD
RTRIM(s): Returns a character expression after it
removes trailing blanks.
SELECT RTRIM('HELLO ') + ' WORLD' OUTPUT: HELLO WORLD
REPLACE(s1, s2, s3):
Replaces all occurrences of the s2 in s1 with s3.
SELECT
REPLACE(‘HELLO’, ‘L’, ‘X’) OUTPUT:
HEXXO
REPLICATE(s, n): Repeats the
expression ‘s’ for specified ‘n’ number of times.
SELECT
REPLICATE(‘HEL’, 2) OUTPUT:
HELHEL
REVERSE(s): Returns the reverse
of the given string ‘s’.
SELECT
REVERSE(‘HELLO’) OUTPUT:
OLLEH
SOUNDEX(s): Returns a
four-character (SOUNDEX) code to evaluate the similarity of two strings.
SOUNDEX converts an alphanumeric string to a four-character code to find
similar-sounding words or names. The first character of the code is the first
character of strings and the second through fourth characters of the code are
numbers.
SELECT
SOUNDEX ('Smith'), SOUNDEX ('Smyth')
-Generally we use then when we perform comparison of words,
which are sounded in the same way but have different spelling like color &
colour. Suppose in a table the ename of a person is smith we will get the
result even if the statement is written as following:
SELECT *
FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX(‘SMYTH’)
DIFFERENCE(S1, S2): Returns an integer value that
indicates the difference between the SOUNDEX values of two character
expressions. The return value ranges from 0 through 4: 0 indicates weak or no
similarity, and 4 indicates strong similarity or the same values.
SELECT
SOUNDEX(‘SMITH’), SOUNDEX('SMYTH'),
DIFFERENCE('SMITH','SMYTH')
SPACE(n): Returns a string with
specified ‘n’ number of repeated spaces.
SELECT
‘HELLO’ + SPACE(1) + ‘WORLD’ OUTPUT:
HELLO WORLD
STUFF(s, start, length,
replace_str): Replaces specified length of characters from specified starting
point with replace_str in the string ‘s’
SELECT
STUFF(‘ABXXCDXX’, 3, 3, ‘YY’) OUTPUT:
ABYYDXX
Date and Time Functions: The
following functions perform an operation on a date and time input value and
return a string, numeric, or date and time value.
GETDATE(): Returns the current
date and time of the server in SQL Server standard internal format.
SELECT
GETDATE()
DAY(date): Returns an integer
representing the DAY of the specified date, which has to be specified in
standard SQL Server date format ‘mm/dd/yy’.
SELECT
DAY(GETDATE())
SELECT
DAY(‘10/24/78’) OUTPUT:
24
MONTH(date): Returns an integer
representing the MONTH of the specified date, which has to be specified in
standard SQL Server date format ‘mm/dd/yy’.
SELECT
MONTH(GETDATE())
SELECT
MONTH(‘10/24/78’) OUTPUT:
10
YEAR(date): Returns an integer
representing the YEAR of the specified date, which has to be specified in
standard SQL Server date format ‘mm/dd/yy’.
SELECT
YEAR(GETDATE())
SELECT
YEAR(‘10/24/78’) OUTPUT:
1978
DATENAME(datepart, date): Returns
a character string representing the specified datepart of the specified date,
datepart is the parameter that specifies the part of the date to return. The
following table lists dateparts and abbreviations recognized by Sql Server:
Datepart
|
Abbreviations
|
year
|
yy, yyyy
|
quarter
|
qq, q
|
month
|
mm, m
|
dayofyear
|
dy, y
|
day
|
dd, d
|
week
|
wk, ww
|
weekday
|
dw
|
hour
|
hh
|
minute
|
mi, n
|
second
|
ss, s
|
millisecond
|
ms
|
SELECT DATENAME(mm,
‘10/24/78’) OUTPUT:
October
SELECT DATENAME(dd,
‘10/24/78’) OUTPUT: 10
DATEPART(datepart, date): This is
same as DATENAME function but the only difference is weekday (dw)
of DATEPART function returns a number that corresponds to the day of the week,
for example: Sunday = 1, Saturday = 7, where as in the case of DATENAME returns
the value in string format that is Sunday, Monday, … Saturday.
DATEADD(datepart, number, date):
Returns a new datetime value based on adding an interval to the
specified date, datepart is the value that has to be added and number is the
interval.
SELECT DATEADD(dd, 30, GETDATE())
–Adds 30 days to GETDATE().
SELECT DATEADD(mm, 16, GETDATE())
–Adds 16 months to GETDATE().
DATEDIFF(datepart, startdate,
enddate): Returns the difference between the start and end dates in the give
datepart format.
SELECT
DATEDIFF(yy, ‘10/24/78’, GETDATE())
GETUTCDATE()-Returns the datetime
value representing the current UTC time (Coordinated Universal Time or
Greenwich Mean Time).
SELECT
GETUTCDATE()
Conversion Functions: Explicitly
converts an expression of one data type to another. We has two conversion
functions CAST and CONVERT, both provide similar functionality.
Syntax for CAST:
CAST (
expression AS data_type [ (length ) ])
SELECT
CAST(10.6496 AS INT) OUTPUT:
10
SELECT CAST(10.3496847 AS money) OUTPUT:
10.3497
Syntax for CONVERT:
CONVERT ( data_type
[ ( length ) ] , expression [ , style ] )
SELECT
CONVERT(INT, 10.6496) OUTPUT:
10
SELECT
CONVERT(VARCHAR(50), GETDATE())
Style is an optional parameter
that can be used to specify a date format used to convert datetime or smalldatetime
data to character. When style is NULL, the result returned is also NULL.
Style can be used as following:
SELECT
CONVERT(VARCHAR(50), GETDATE(), 101)
SELECT
CONVERT(VARCHAR(50), GETDATE(), 102)
-Each style will give the output
of the date in a different format the default style it uses is 100. The style
values can be ranging between 100-114, 120, 121, 126, 127, 130 and 131 or 0 to
8, 10, 11, 12 and 14 in this case century part will not returned.
SELECT
CONVERT(VARCHAR(50), GETDATE(), 1)
System Functions:
ISNUMERIC( expression ):
Determines whether an expression is a valid numeric type. If it is numeric it
returns 1 else return 0.
SELECT
ISNUMERIC(100) OUTPUT:
1
SELECT ISNUMERIC(‘100’) OUTPUT: 1
SELECT
ISNUMERIC(‘100A’) OUTPUT:
0
ISDATE (expression): Determines
whether an input expression is a valid date or not. If it is a valid date it
returns 1 else return 0. Valid date in the sense the expression, which is
present in mm/dd/yy format.
SELECT ISDATE('12/21/98') OUTPUT: 1
SELECT ISDATE('21/12/98') OUTPUT: 0
ISNULL (expression1,
expression2): if expression1 is null then it returns expression2.
SELECT
ISNULL(100, 200) OUTPUT:
100
SELECT
ISNULL(NULL, 200) OUTPUT:
200
SELECT EMPNO,
ENAME, SAL, COMM, SAL + COMM AS [TOTAL SAL] FROM EMP
-In above case if any of the
value in the comm. Is null it returns null in the Total Sal because any
arithmetic operations performed on a null value results to null only at this
time the statement has to be written as following:
SELECT EMPNO,
ENAME, SAL, COMM, SAL + ISNULL(COMM, 0) AS [TOTAL SAL] FROM EMP
COALESCE (expression1,
expression2, …… expression n): Returns
the first not null expression in the list of expressions given, similar to
isnull but we can give multiple values here.
SELECT
COALESCE(NULL, 100, NULL, 200) OUTPUT:
100
SELECT EMPNO,
ENAME, SAL, COMM, SAL + COALESCE(COMM, 0) AS [TOTAL SAL] FROM EMP
DATALENGTH (expression) : Returns
the number of bytes used to represent any expression.
SELECT
DATALENGTH(100) OUTPUT: 4
SELECT
DATALENGTH(‘HELLO’) OUTPUT:
5
HOST_NAME(): Returns the name of
the workstation.
SELECT
HOST_NAME()
IDENT_CURRENT('table_name'):
Returns the last identity value generated for a specified table by the identity
function.
SELECT
IDENT_CURRENT(‘BANK’)
IDENT_SEED('table_name'): Returns
the seed value that was specified when the identity function in a table was
created.
SELECT
IDENT_SEED(‘BANK’)
IDENT_INCR('table_name'): Returns
the increment value that was specified when the identity function in a table
was created.
SELECT
IDENT_INCR(‘BANK’)
NEWID( ): Creates a unique value
of type uniqueidentifier.
SELECT
NEWID()
NULLIF(expression1, expression2):
Returns the first expression if the two expressions are not equivalent. If the
expressions are equivalent, returns a null value.
SELECT
NULLIF(100, 200) OUTPUT:
100
SELECT
NULLIF(100, 100) OUTPUT:
NULL
ROWCOUNT_BIG(): Returns the
number of rows affected by the last statement executed. If we use this after a
select statement it will return us the number of rows the select statement has
returned.
SELECT
* FROM EMP
SELECT
ROWCOUNT_BIG FROM EMP
APP_NAME(): Returns the name of
the application from where the statement is executed.
SELECT
APP_NAME()
CASE: Evaluates a list of
conditions and returns one of multiple possible result expressions. It has two
formats:
-The simple CASE function
compares an expression to a set of simple expressions to determine the result.
-The searched CASE function
evaluates a set of Boolean expressions to determine the result.
- Both formats support an
optional ELSE argument.
CASE
<expression>
WHEN when_expression THEN
result_expression
WHEN when_expression THEN
result_expression
…………………………
ELSE
else_result_expression
END
-In this case if the expression
matches with any of the when_expression it returns the corresponding
result_expression, if it does not match with any then it returns
else_result_exression.
SELECT
EMPNO, ENAME, SAL, JOB,
(CASE JOB
WHEN ‘PRESIDENT’
THEN ‘BIG BOSS’
WHEN ‘MANAGER’
THEN ‘BOSS’
WHEN ‘ANALYST’
THEN ‘SCIENTIST’
ELSE ‘EMPLOYEE’
END) AS COMMENTS
FROM EMP
SELECT
EMPNO, ENAME, JOB, SAL,
(CASE
SIGN(SAL-3000)
WHEN
1 THEN ‘ABOVE TARGET’
WHEN
0 THEN ‘ON TARGET’
WHEN
–1 THEN ‘BELOW TARGET’
END)
AS COMMENTS FROM EMP
-The above statement can be
written in one more way also by using the second format of the CASE function.
CASE
WHEN
condition THEN result_expression
WHEN
condition THEN result_expression
…………………………
ELSE
else_result_expression
END
SELECT
EMPNO, ENAME, JOB, SAL,
(CASE
WHEN
SAL>3000 THEN ‘ABOVE TARGET’
WHEN
SAL=3000 THEN ‘ON TARGET’
WHEN
SAL<3000 THEN ‘BELOW TARGET’
END)
AS COMMENTS FROM EMP
Set Operators:
COUNT(expression): Returns the
number of items in a group.
SELECT
COUNT(*) FROM EMP
SELECT
COUNT(*) FROM EMP WHERE DEPTNO=20
SELECT
COUNT(COMM) FROM EMP
COUNT_BIG(expression): COUNT_BIG
works like the COUNT function. The only difference between the two functions is
their return values. COUNT_BIG always returns a bigint data type value.
COUNT always returns an int data type value.
SELECT
COUNT_BIG(*) FROM EMP
SUM(expression): Returns the sum
of all the values. SUM can be used with numeric columns only. Null values are
ignored
SELECT
SUM(SAL) FROM EMP
AVG(expression): Returns the
average of the values in a group. Null values are ignored.
SELECT
AVG(SAL) FROM EMP
MAX(expression): Returns the
maximum value in the expression.
SELECT
MAX(SAL) FROM EMP
MIN(expression): Returns the
minimum value in the expression.
SELECT
MIN(SAL) FROM EMP
STDEV(expression): Returns the
statistical standard deviation of all values in the specified expression.
SELECT
STDEV(SAL) FROM EMP
VAR(expression): Returns the
statistical variance of all values in the specified expression.
SELECT
VAR(SAL) FROM EMP
Operators: An operator is a
symbol specifying an action that is performed on one or more expressions. The
lists the operator categories that SQL Server supports:
-Arithmetic
Operators
-Assignment
Operator
-Comparison
Operators
-Logical
Operators
-Concatenation
Operator
Arithmetic Operators: Arithmetic
operators perform mathematical operations on two expressions of one or more of
the data types of the numeric data type category. Those are:
+ - Addition
- - Subtraction
* - Multiplication
/ - Division
% - Modulo
Assignment Operators: The equal
sign (=) is the only assignment operator.
Comparison Operators: Comparison
operators test whether two expressions are the same. Comparison operators can
be used on all expressions except expressions of the text, ntext,
or image data types. Those are:
= - Equal
to
> - Greater
than
< - Less
than
>= - Greater
than or equal to
<= - Less
than or equal to
<> - not
equal to
!= - not
equal to
!< - not
less than
!> - not
greater than
Logical Operators: Logical
operators test for the truth of some condition. Logical operators, like
comparison operators, return a Boolean value of TRUE or FALSE.
Those are:
- ALL TRUE if all of a set of comparisons are TRUE
- AND TRUE if both Boolean expressions are TRUE
- ANY TRUE if any one of a set of comparisons are TRUE
- BETWEEN TRUE if the operand is within a range
- EXISTS TRUE if a subquery contains any rows
- IN TRUE if the operand is equal to one of a list of expressions.
- LIKE TRUE if the operand matches a pattern
- NOT Reverses the value of any other Boolean operator
- OR TRUE if either Boolean expression is TRUE
- SOME TRUE is some of a set of comparisons are TRUE
String Concatenation Operator:
The plus sign (+) is the string concatenation operator that enables string
concatenation.
-WAQ to find the details of employees whose job is CLERK.
Sol: SELECT * FROM EMP WHERE
JOB=’CLERK’
-WAQ to find the details of all
employees except SALESMAN.
Sol: SELECT * FROM EMP WHERE JOB
!= ‘SALESMAN’
(OR)
Sol: SELECT * FROM EMP WHERE JOB
<> ‘SALESMAN’
-WAQ to find the details of
employees who are earning more than 3000
Sol: SELECT * FROM EMP WHERE
SAL>3000
-WAQ to find the details of
employees who are earning less than 2500
Sol: SELECT * FROM EMP WHERE
SAL<2500
-WAQ to find the details of
employees who are earning with in a range of 2500 and 4000
Sol: SELECT * FROM EMP WHERE
SAL>=2500 AND SAL<=4000
Sol: SELECT * FROM EMP WHERE SAL
BETWEEN 2500 AND 4000
-Between operator is used for
specifying with a range of values to test.
-WAQ to find the details of
employees who are earning less than 1500 as well as more than 3500
Sol: SELECT * FROM EMP WHERE
SAL<1500 OR SAL>3500
Sol: SELECT * FROM EMP WHERE SAL
NOT BETWEEN 1500 AND 3500
-WAQ to find the details of
employees whose jobs are CLERK, MANAGER AND SALESMAN
Sol: SELECT * FROM EMP WHERE
JOB=’CLERK’ OR JOB=’MANAGER’ OR JOB=’SALESMAN’
Sol: SELECT * FROM EMP WHERE JOB
IN (’CLERK’, ’MANAGER’, ’SALESMAN’)
-In Operator Determines whether a
specified value matches any value in the list.
-WAQ to find the details of all
employees except PRESIDENT AND MANAGER.
Sol: SELECT * FROM EMP WHERE JOB
!= ’MANAGER’ AND JOB != ’PRESIDENT’
Sol: SELECT * FROM EMP WHERE JOB
NOT IN (’MANAGER’, ’PRESIDENT’)
-WAQ to find the details of
employees who name starts with character S.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘S%’
-Like Operator determines whether
a specific character string matches a specified pattern. A pattern can include
regular characters and wildcard characters. During pattern matching, regular
characters must exactly match the characters specified in the character string.
However, wildcard characters can be matched with arbitrary fragments of the
character string. Using wildcard characters makes the LIKE operator more
flexible than using the = and != string comparison operators.
%
- it represents any string of zero or more characters.
-WAQ to find the details of
employees whose name contains M in it.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘%M%’
-WAQ to find the details of
employees whose name is SMITH, when the spelling of the name is not known
exactly as SMITH OR SMYTH.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘SM_TH’
OR
Sol: SELECT * FROM EMP WHERE
SOUNDEX(ENAME)=SOUNDEX(‘SMYTH’)
_(underscore)
- it represents any single character.
WAQ to find the details of
employees whose name starts with a characters between A to S.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘[A-S]%’
[ ] – it
represents any single character within the specified range ([a-f]) or set
([abcdef]).
WAQ to find the details of
employees whose name starts with any of the character “ABCDE”.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘[ABCDE]%’
WAQ to find the details of
employees whose name starts with a characters not between A to S.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘[^A-S]%’
WAQ to find the details of
employees whose name starts with characters apart from “ABCDE”
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘[^ABCDE]%’
OR
Sol: SELECT * FROM EMP WHERE
ENAME NOT LIKE ‘[ABCDE]%’
WAQ to find the details of
employees whose job is CLERK and earning 3000.
Sol: SELECT * FROM EMP WHERE
JOB=’CLERK’ AND SAL=3000
WAQ to find the details of
employees whose job is MANAGER as well as earning more than 3000.
Sol: SELECT * FROM EMP WHERE
JOB=’MANAGER’ OR SAL>3000
WAQ to find the details of
employees whose salary is not equal to 3000.
Sol: SELECT * FROM EMP WHERE NOT
SAL=3000
Set Operators: Combines the
results of two or more queries into a single result set.
The following are basic rules for
combining the result sets of two queries by using SET Operators:
- The number and the order of the columns must be the same in all queries.
- The data types must be compatible.
UNION: Combines the results of
two or more queries into a single result set that includes all the rows that
belong to all queries in the union.
SELECT
JOB FROM EMP WHERE DEPTNO=10
UNION
SELECT
JOB FROM EMP WHERE DEPTNO=30
UNION ALL: These is same as UNION
but in this case duplicates will not be eliminated.
SELECT
JOB FROM EMP WHERE DEPTNO=10
UNION
ALL
SELECT
JOB FROM EMP WHERE DEPTNO=30
INTERSECT: Returns any distinct
values that are returned by both the query on the left and right sides of the
INTERSECT operand.
SELECT
JOB FROM EMP WHERE DEPTNO=10
INTERSECT
SELECT JOB FROM
EMP WHERE DEPTNO=30
EXCEPT: Returns any distinct values
from the query to the left of the EXCEPT operand that are not also returned
from the right query.
SELECT
JOB FROM EMP WHERE DEPTNO=10
EXCEPT
SELECT JOB FROM
EMP WHERE DEPTNO=30
CLAUSES: SQL Server provides with
the following clauses that can be used in the SELECT statements:
·
WHERE
·
GROUP BY
·
HAVING
·
ORDER BY
The complete syntax of the SELECT
statement looks as following:
SELECT
<select_list> FROM <tname>
[ WHERE
search_condition ]
[ GROUP BY
group_by_expression ]
[ HAVING
search_condition ]
[ ORDER BY
order_expression [ ASC | DESC ] ]
WHERE Clause: The WHERE clause is
a filter that defines the conditions each row in the source tables must meet to
qualify for the SELECT. Only rows that meet the conditions contribute data to
the result set. Data from rows that do not meet the conditions is not used.
SELECT
* FROM EMP WHERE JOB=’MANAGER’
SELECT
* FROM EMP WHERE DEPTNO=20
GROUP BY Clause: The GROUP BY
clause partitions the result set into groups based on the values in the columns
of the group_by_list. For example, the Emp table has 3 values in Deptno
column. A GROUP BY Deptno clause partitions the result set into 3
groups, one for each value of Deptno.
WAQ to find the highest salaries
for each department.
Sol: SELECT DEPTNO, MAX(SAL) FROM
EMP GROUP BY DEPTNO
WAQ to find the highest salaries
for each job.
Sol: SELECT JOB, MAX(SAL) FROM
EMP GROUP BY JOB
WAQ to find the highest salaries
for each department in it for each job.
Sol: SELECT DEPTNO, JOB, MAX(SAL)
FROM EMP GROUP BY DEPTNO, JOB
Note: While using the GROUP By
clause the select_list of the query should contain only the following:
-Group
Functions or Aggregate Functions
-Columns
used in the Group By Clause
-Constants.
WAQ to find the number of
employees working for each department.
Sol: SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO
WAQ to find the number of
employees working for each department only if the number is greater than 3.
Sol: SELECT DEPTNO, COUNT(*) FROM
EMP GROUP BY DEPTNO HAVING COUNT(*)>3
HAVING Clause: The HAVING clause
is an additional filter that is applied to the result set. Logically, the
HAVING clause filters rows from the intermediate result set built from applying
any FROM, WHERE, or GROUP BY clauses in the SELECT statement. HAVING clauses
are typically used with a GROUP BY clause.
WAQ to find the number of Clerk’s
working for each department.
Sol: SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=’CLERK’
GROUP BY DEPTNO
WAQ to find the number of Clerk’s
working for each department only if the count is greater than 1.
Sol: SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=’CLERK’
GROUP BY DEPTNO HAVING COUNT(*)>1
ORDER BY order_list[ ASC |
DESC ]
The ORDER BY clause defines the
order in which the rows in the result set are sorted. order_list
specifies the result set columns that make up the sort list. The ASC and DESC
keywords are used to specify if the rows are sorted in an ascending or
descending sequence.
SELECT *
FROM EMP ORDER BY SAL
SELECT *
FROM EMP ORDER BY SAL DESC
SELECT *
FROM EMP ORDER BY SAL, COMM
SUBQUERY: A subquery is a query that is nested
inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another
subquery. A subquery can be used anywhere an expression is allowed. In this
case first the inner query executes and basing upon the result generated by it
the outer query executes to generate the final output.
WAQ to find the details of
employees earning the highest salary.
Sol: SELECT * FROM EMP WHERE SAL=
(SELECT MAX(SAL) FROM EMP)
WAQ to find the details of
employees earning the second highest salary.
Sol: SELECT * FROM EMP WHERE SAL=
(SELECT MAX(SAL) FROM EMP
WHERE SAL<
(SELECT MAX(SAL) FROM EMP))
WAQ to find the details of
employees working in sales department.
Sol: SELECT * FROM EMP WHERE DEPTNO=
(SELECT DEPTNO FROM DEPT
WHERE DNAME=’SALES’)
WAQ to find the details of
employees working in Mumbai.
Sol: SELECT * FROM EMP WHERE DEPTNO=
(SELECT DEPTNO FROM DEPT
WHERE LOC=’MUMBAI’)
WAQ to find the details of
employees who are earning more than the highest salary of deptno 30
Sol: SELECT * FROM EMP WHERE SAL>
(SELECT
MAX(SAL) FROM EMP WHERE DEPTNO=30)
OR
SELECT
* FROM EMP WHERE SAL>
ALL (SELECT SAL FROM EMP WHERE
DEPTNO=30)
-In
this case we can use the ALL operator which will compare an expression
with set of values, where the expression has to satisfy the condition with all
the values.
WAQ to find the details of
employees who are earning less than the lowest salary of deptno 20
Sol: SELECT * FROM
EMP WHERE SAL<
(SELECT MIN(SAL) FROM EMP WHERE
DEPTNO=20)
OR
SELECT
* FROM EMP WHERE SAL<
ALL(SELECT SAL FROM EMP WHERE
DEPTNO=20)
WAQ to find the details of
employees who are earning less than the highest salary of deptno 10
Sol: SELECT * FROM
EMP WHERE SAL<
(SELECT MAX(SAL) FROM EMP WHERE
DEPTNO=10)
OR
SELECT
* FROM EMP WHERE SAL<
ANY(SELECT SAL FROM EMP WHERE
DEPTNO=10)
-In
the place of ANY we can use SOME operator also.
-
In this case we can use the ANY/SOME operatorS which will compare an
expression with set of values, where the expression has to satisfy the
condition with at least a single value.
WAQ to find the details of
employees who are earning the highest salary in each department.
SELECT
* FROM EMP WHERE SAL IN
(SELECT
MAX(SAL) FROM EMP GROUP BY DEPTNO)
WAQ to find the details of
seniors in each department.
SELECT
* FROM EMP WHERE HIREDATE IN
(SELECT
MIN(HIREDATE) FROM EMP GROUP BY DEPTNO)
Correlated Subqueries: Many
queries can be evaluated by executing the subquery once and substituting the
resulting value or values into the WHERE clause of the outer query. In queries
that include a correlated subquery (also known as a repeating subquery), the
subquery depends on the outer query for its values. This means that the
subquery is executed repeatedly, once for each row that might be selected by
the outer query.
WAQ to find the details of
employees earning the highest salary.
Sol: SELECT * FROM EMP E WHERE 0=
(SELECT COUNT(DISTINCT SAL)
FROM EMP WHERE SAL>E.SAL)
WAQ to find the details of
employees earning the second highest salary.
Sol: SELECT * FROM EMP E WHERE 1=
(SELECT COUNT(DISTINCT SAL)
FROM EMP WHERE SAL>E.SAL)
-In this case if we want the n th highest salary we need to
substitute n-1 value in the where condition of the outer query.
WAQ to find the details of
departments in which employees are working.
Subquery: SELECT * FROM DEPT
WHERE DEPTNO IN
(SELECT
DISTINCT DEPTNO FROM EMP)
Correlated Subquery: SELECT * FROM DEPT
WHERE EXISTS
(SELECT DEPTNO FROM EMP WHERE
EMP.DEPTNO=DEPT.DEPTNO)
-EXISTS is an operator which is
used to specifies a subquery to test for the existence of rows.
WAQ to find the details of
departments in which employees are not working.
Subquery: SELECT * FROM DEPT
WHERE DEPTNO NOT IN
(SELECT
DISTINCT DEPTNO FROM EMP)
Correlated Subquery: SELECT * FROM DEPT
WHERE NOT EXISTS
(SELECT DEPTNO FROM EMP WHERE
EMP.DEPTNO=DEPT.DEPTNO)
WAQ to find the details of
employees who have subordinates under them.
Subquery: SELECT * FROM EMP
WHERE EMPNO IN(
SELECT
DISTINCT MGR FROM EMP)
Correlated Subquery: SELECT * FROM EMP E WHERE EXISTS (
SELECT
* FROM EMP M WHERE E.EMPNO=M.MGR)
WAQ to find the details of
employees who doesn’t have any subordinates under them.
Subquery: SELECT * FROM EMP
WHERE EMPNO NOT IN(
SELECT
DISTINCT MGR FROM EMP)
Correlated Subquery: SELECT * FROM EMP E WHERE NOT EXISTS (
SELECT
* FROM EMP M WHERE E.EMPNO=M.MGR)
JOINS:
By using joins, you can retrieve data from two or more tables based on
logical relationships between the tables. Joins indicate how database should
use data from one table to select the rows in another table.
A join condition defines the way
two tables are related in a query by:
- Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
- Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.
Types of Joins:
- Equi-Joins
- Non Equi-Joins
- Self Joins
- Cartesian Joins
- Outer Joins
- Left Outer Join
- Right Outer Join
Equi-Joins: It returns the
specified columns from both the tables, and returns only the rows for which
there is an equal value in the join column.
SELECT
E.EMPNO,
E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO,
D.DNAME, D.LOC
FROM EMP E, DEPT
D
WHERE
E.DEPTNO=D.DEPTNO
-The above statement is known, as
old-style join statement, which will combine the tables basing on equality
condition i.e. the Deptno column in the Emp table, has to have an exact match
of Deptno in the Dept table, then these 2 rows combine and get retrieved. In
the new-style we call this as Inner Join where we write the statement as
following:
SELECT
E.EMPNO,
E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO,
D.DNAME, D.LOC
FROM EMP E INNER
JOIN DEPT D
ON
E.DEPTNO=D.DEPTNO
-In the same way if we want to combine
multiple table in old-style we write following:
SELECT
E.EMPNO,
E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO,
D.DNAME, D.LOC,
DD.DID,
DD.COMMENTS
FROM EMP E, DEPT
D, DEPTDETAILS DD
WHERE
E.DEPTNO=D.DEPTNO AND D.DEPTNO=DD.DEPTNO
-The same statement in the
new-style we write as following:
SELECT
E.EMPNO,
E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO,
D.DNAME, D.LOC,
DD.DID,
DD.COMMENTS
FROM EMP E INNER
JOIN DEPT D
ON
E.DEPTNO=D.DEPTNO
INNER JOIN
DEPTDETAILS DD
ON
D.DEPTNO=DD.DEPTNO
Non Equi-Joins: You can also join
values in two columns that are not equal. The same operators and predicates
used for equi-joins can be used for not-equi joins.
SELECT
E.EMPNO,
E.ENAME, E.SAL,
S.SALGRADE,
S.LOSAL, S.HISAL
FROM
EMP E, SALGRADE S
WHERE
E.SAL BETWEEN S.LOSAL AND S.HISAL
-We can write the above statement
using inner join in the new style as following:
SELECT
E.EMPNO,
E.ENAME, E.SAL,
S.SALGRADE,
S.LOSAL, S.HISAL
FROM
EMP E INNER JOIN SALGRADE S
ON E.SAL BETWEEN
S.LOSAL AND S.HISAL
Self Join: If a table has a
reflexive relationship in the database, you can join it to itself automatically
which is known as self join.
SELECT
DISTINCT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO
FROM EMP E, EMP M
WHERE E.EMPNO=M.MGR
-We can write the above statement
using inner join in the new style as following:
SELECT
DISTINCT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO
FROM EMP E INNER JOIN EMP M
ON E.EMPNO=M.MGR
Cartesian
Join: A Cartesian join that does not have a WHERE clause produces the
Cartesian product of the tables involved in the join. The size of a Cartesian product
result set is the number of rows in the first table multiplied by the number of
rows in the second table. This is also known as cross-join. However, if a WHERE
clause is added, the cross join behaves as an inner join.
SELECT
E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
-We can write the above statement
in the new style as following:
SELECT
E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO, D.DNAME, D.LOC
FROM EMP E CROSS JOIN DEPT D
Outer Join: By default, when we join
multiple tables using inner join what we get is the matching data from the
tables, if we want to include data rows in the result set that do not have a
match in the joined table, we can us outer join.
The old-style of outer joins have
been classified into 2 types as Left Outer Join and Right Outer Join.
We use Left Outer Join to get the
matching information plus unmatched information from left hand side table, in
the same way we use Right Outer Join to get the matching information plus
unmatched information from right hand side table.
Left hand side table and right
hand side tables are referred in the order we write in the from clause, first
table is LHS table and second table is RHS table.
SELECT
E.EMPNO,
E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO,
D.DNAME, D.LOC
FROM EMP E, DEPT
D
WHERE
E.DEPTNO=*D.DEPTNO
-In the above case we get the
matching information plus unmatched information from Dept table.
SELECT
E.EMPNO,
E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO,
D.DNAME, D.LOC
FROM EMP E, DEPT
D
WHERE E.DEPTNO*=D.DEPTNO
-In the above case we get the
matching information plus unmatched information from Emp table.
-Suppose we have unmatched
information in both the sides we cannot retrieve it at the same time to over
come this in the new-style of join statement they have introduced Full Outer
join. So the new-style supports use the following:
·
LEFT OUTER JOIN
·
RIGHT OUTER JOIN
·
FULL OUTER JOIN
-Use Left Outer Join to get the
unmatched information from left hand side table as following:
SELECT
E.EMPNO,
E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO,
D.DNAME, D.LOC
FROM EMP E LEFT
OUTER JOIN DEPT D
ON
E.DEPTNO=D.DEPTNO
-Use Right Outer Join to get the
unmatched information from right hand side table as following:
SELECT
E.EMPNO,
E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO,
D.DNAME, D.LOC
FROM EMP E RIGHT
OUTER JOIN DEPT D
ON
E.DEPTNO=D.DEPTNO
-Use Full Outer Join to get the
unmatched information from both the tables as following:
SELECT
E.EMPNO,
E.ENAME, E.SAL, E.DEPTNO,
D.DEPTNO,
D.DNAME, D.LOC
FROM EMP E FULL
OUTER JOIN DEPT D
ON
E.DEPTNO=D.DEPTNO
Finally concluding in the
new-style we have only 3 types of joins those are Inner Joins, Cross Joins and
Outer Joins in the place of Equi-Joins, Non Equi-Joins, Self Joins, Cartesian
Joins and Outer Joins which are present in the old-style.
Transactions
- A transaction is a single unit of work.
- If a transaction is successful, all of the data
modifications made during the transaction are committed and become a
permanent part of the database.
- If a transaction encounters errors and must be
canceled or rolled back, then all of the data modifications are erased.
SQL Server operates in the
following transaction modes:
- Autocommit transactions: Each individual statement is a transaction.
- Explicit transactions: Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
- Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
- By Default SQL Server uses AutoCommit Transactions i.e. after executing each statement it will automatically Commit it.
- If we want to use the Explicit Transactions before executing the statements we need to start with a Begin Transaction statement and then decide whether it has to be commited or rolledback, until the transaction ends the records gets locked.
- If we want to use the Implicit Transactions we should use the following Statement:
SET
IMPLICIT_TRANSACTIONS ON | OFF
- When ON, SET IMPLICIT_TRANSACTIONS sets the
connection into implicit transaction mode. When OFF, it returns the
connection to autocommit transaction mode.
- To Manage the Transactions we have the TCL (Transaction Control Language) with 3 commands in it Commit, Rollback and Save Transaction.
Commit: Marks the end of a
successful implicit or explicit transaction. COMMIT TRANSACTION makes all data
modifications performed since the start of the transaction a permanent part of
the database, frees the resources held by the transaction.
Begin
Transaction
DELETE
FROM EMP WHERE EMPNO=1015
COMMIT
Rollback: Rolls back an explicit
or implicit transaction to the beginning of the transaction, or to a savepoint
inside the transaction.
Begin
Transaction
DELETE
FROM EMP WHERE EMPNO=1014
ROLLBACK
Save Transaction: A user can set
a savepoint, or marker, within a transaction. The savepoint defines a location
to which a transaction can return if part of the transaction is conditionally
canceled. If a transaction is rolled back to a savepoint, it must proceed to
completion with more Transact-SQL statements if needed and a COMMIT TRANSACTION
statement, or it must be canceled altogether by rolling the transaction back to
its beginning. To cancel an entire transaction, use the ROLLBACK TRANSACTION
statements.
BEGIN TRANSACTION
UPDATE EMP SET SAL=5000 WHERE EMPNO=1001
SAVE TRANSACTION S1
UPDATE EMP SET SAL=5000 WHERE EMPNO=1002
SAVE TRANSACTION S2
UPDATE EMP SET SAL=5000 WHERE EMPNO=1003
ROLLBACK TRANSACTION S2 OR Rollback ROLLBACK TRANSACTION S1
COMMIT
-In the above case either the
last statement gets rolled back or the last 2 statements gets rolled back and
commits the rest.
Creating a table from an
existing table: We can create a table from an existing table maintain a
copy of the actual table before manipulating the data.
Syntax: SELECT < * |
<COLLIST > INTO <NEW TNAME> FROM <OLD TNAME> [CONDITIONS]
SELECT * INTO
NEW_EMP FROM EMP
-In this case it creates a table
NEW_EMP by copying all the rows and columns of the EMP table.
SELECT
EMPNO, ENAME, SAL, DEPTNO INTO TEST_EMP FROM EMP
-In this case it creates a table
TEST_EMP with only the specified columns from the EMP table.
SELECT * INTO
SALES_EMP FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=’SALES’)
-In this case it creates a table
SALES_EMP with only the information of sales department from the EMP table.
SELECT
* INTO DUMMY_EMP FROM EMP WHERE 1=2
-In this case it creates the
DUMMY_EMP table with out any data in it.
Copying data from one existing
table to another table: We can copy the data from one table into another
table by using a combination of insert and select statement as following:
Syntax:
INSERT INTO <TNAME> [ (COLLIST) ]
SELECT
< * | <COLLIST> FROM <TNAME> [CONDITIONS]
INSERT
INTO DUMMY_EMP SELECT * FROM EMP
-In this case all the rows of EMP
table is copied into DUMMY_EMP table.
INSERT
INTO DUMMY_EMP (EMPNO, ENAME, SAL, DEPTNO)
SELECT EMPNO,
ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=30
-In this case it copies only the
selected columns into the DUMMY_EMP table from the EMP table.
VIEWS
- A view can be thought of as either a virtual table or a stored query, like a real table, a view consists of a set of named columns and rows of data.
- Unless a view is indexed, its data is not stored in the database as a distinct object.
- What is stored in the database is a SELECT statement.
- The result set of the SELECT statement forms the virtual table returned by the view.
- A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.
- The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.
- A view acts as a filter on the underlying tables referenced in the view.
- The query that defines the view can be from one or more tables or from other views in the current or other databases.
- There are no restrictions on querying through views
and few restrictions on modifying data through them.
Syntax: CREATE VIEW <view_name>
[(column [,...n])]
[WITH
<view_attribute> [,...n]]
AS select_statement
[WITH CHECK OPTION]
Under the view_attribute
we have the following options:
[ENCRYPTION]
[SCHEMABINDING]
Types of Views:
- Simple Views
- Complex Views
Simple Views:
-
These Views as based upon a single table, which access
the data from the single table.
-
They contain a Sub Query which retrieves the data from
one base table.
CREATE VIEW
SIMPLE_VIEW
AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
-Once the view is created we can
access the data from it as if it was a table as following:
SELECT
* FROM SIMPLE_VIEW
SELECT EMPNO,
ENAME, SAL, SAL*12 AS [ANNUAL SAL], DEPTNO FROM SIMPLE_VIEW
SELECT DEPTNO,
MAX(SAL) FROM EMP GROUP BY DEPTNO
-We can also perform DML operations
on the Simple Views which will effect on the base table.
INSERT
INTO SIMPLE_VIEW VALUES(1234, ‘SANTOSH’, 4300, 20)
DELETE
FROM SIMPLE_VIEW WHERE DEPTNO=20
UPDATE
EMP SET SAL=5600 WHERE EMPNO=1001
-All the columns that are
referenced in the view can be modified through the view.
-We cannot perform insert
operations on the view if he view does not contain all the not null columns of
the base table.
Complex Views:
-
If the View is based on multiple tables it is a complex
view
-
If it is based on a single table with any of the
following:
o
Group By Clause
o
Having Clause
o
Group Functions
o
Distinct Function
o
Function Calls
CREATE VIEW
EMP_DEPT
AS
SELECT E.EMPNO,
E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E INNER
JOIN DEPT D
ON
E.DEPTNO=D.DEPTNO
CREATE
VIEW EMP_GRADE
AS
SELECT
E.EMPNO, E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL
FROM
EMP E INNER JOIN SALGRADE S
ON
E.SAL BETWEEN S.LOSAL AND S.HISAL
CREATE
VIEW EMP_MANAGERS
AS
SELECT E.ENAME + ' WORKS UNDER ' + M.ENAME
FROM EMP E INNER JOIN EMP M
ON E.MGR=M.EMPNO
CREATE
VIEW EMP_SALARIES
AS
SELECT EMPNO,
ENAME, DEPTNO, SAL AS MONTHLY, SAL*12 AS ANNUAL FROM EMP
CREATE VIEW
EMP_DESIGNATIONS
AS
SELECT JOB FROM
EMP WHERE DEPTNO=10
UNION
SELECT JOB FROM
EMP WHERE DEPTNO=20
UNION
SELECT JOB FROM
EMP WHERE DEPTNO=30
CREATE VIEW
EMP_MAX_SAL
AS
SELECT DEPTNO,
MAX(SAL) AS [HIGH SAL] FROM EMP GROUP BY DEPTNO
-If we want to perform
manipulations on the Complex Views we have the following restrictions:
- Any
modifications, including UPDATE, INSERT, and DELETE statements, must
reference columns from only one base table.
- The columns being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:
- An aggregate function
- A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators amount to a computation and are also not updatable.
- The columns being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.
-We can also classify views as
Updateable Views and Non Updateable Views:
-
A View, which allows manipulations on it, is known as
Updateable View.
-
A View, which will not allow manipulations on it, is
known as Non Updateable View.
With Check Option:
-
Forces all data modification statements executed
against the view to follow the criteria set within select statement.
-
The Clause specifies that DML operations are not
allowed on rows that the View cannot Select
-
When a row is modified through a view, the WITH CHECK
OPTION makes sure the data remains visible through the view after the
modification is committed.
CREATE VIEW
SALES_EMP
AS
SELECT EMPNO,
ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20
INSERT INTO
SALES_EMP VALUES(1050, ‘RAJU’, 3500, 30)
-The above insert statement
executes even if it does not satisfy the condition in the View, if this has to
be restricted the view has to be created by using With Check Option clause.
ALTER VIEW
SALES_EMP
AS
SELECT EMPNO,
ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20
WITH CHECK
OPTION
-If we want to make any
modifications to the existing view we can use the alter view statement.
View Attributes:
Encryption: After creating a view
if we want to see the view definition that can be found in the SYSCOMMENTS
System Table.
SELECT TEXT FROM
SYSCOMMENTS WHERE OBJECT_NAME(ID)=’SALES_EMP’
If we want to hide the definition
from other persons we can use the Encryption option while creating the view or
alter the view after creation to add the clause:
ALTER VIEW
SALES_EMP
WITH ENCRYPTION
AS
SELECT EMPNO,
ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20
WITH CHECK
OPTION
Schemabinding:
-
When SCHEMABINDING is specified, the base table or
tables cannot be modified in a way that would affect the view definition.
-
The view definition itself must first be modified or
dropped to remove dependencies on the table that is to be modified.
-
When you use SCHEMABINDING, the select statement must
include the two-part names (schema.object) of tables that are
referenced.
-
We need to specify the column names individual in the
select statement, cannot use “*” in the select statement.
-
All referenced objects must be in the same database.
-
Views or tables that participate in a view created with
the SCHEMABINDING clause cannot be dropped unless that view is dropped or
changed so that it no longer has schema binding.
CREATE VIEW
EMP_BIND
WITH
SCHEMABINDING
AS
SELECT EMNO,
ENAME, JOB, MGR FROM DBO.EMP
-After the view is created EMP
table cannot be dropped are the column referred in the views cannot be altered
using the alter command.
INDEXES
-
Like an index in a book, an index in a database lets
you quickly find specific information in a table or indexed view.
-
An index contains keys built from one or more columns
in the table, or view, and pointers that map to the storage location of the
specified data.
-
These keys are stored in a structure (B-tree) that
enables SQL Server to find the row or rows associated with the key values
quickly and efficiently.
-
We can significantly improve the performance of database
queries and applications by creating well-designed indexes to support your
queries.
-
Indexes can reduce the amount of data that must be read
to return the query result set.
-
Indexes can also enforce uniqueness on the rows in a
table, ensuring the data integrity of the table data.
Types of indexes:
Clustered:
-
Clustered indexes sort and store the data rows in the
table or view based on their key values.
-
These are the columns included in the index definition.
-
There can be only one clustered index per table,
because the data rows themselves can be sorted in only one order.
-
The only time the data rows in a table are stored in
sorted order is when the table contains a clustered index.
-
When a table has a clustered index, the table is called
a clustered table.
-
A table can have only 1 Clustered index on it, which
will be created when a primary key constraint is used in a table.
Nonclustered:
-
Nonclustered indexes have a structure separate from the
data rows.
-
A nonclustered index contains the nonclustered index
key values and each key value entry has a pointer to the data row that contains
the key value.
-
The pointer from an index row in a nonclustered index
to a data row is called a row locator.
-
If a table has no clustered index, its data rows are
stored in an unordered structure called a heap.
-
The structure of the row locator depends on whether the
data pages are stored in a heap or a clustered table.
-
For a heap, a row locator is a pointer to the row.
-
For a clustered table, the row locator is the clustered
index key.
-
A table can have 249 Nonclustered indexes on it, which
will be created whenever a unique constraint is used in the table.
How Indexes are used:
Well-designed
indexes can reduce disk I/O operations and consume fewer system resources therefore
improving query performance. Indexes can be helpful for a variety of queries
that contain SELECT, UPDATE, or DELETE statements. When this query is executed,
the query optimizer evaluates each available method for retrieving the data and
selects the most efficient method. The method may be a table scan, or may be
scanning one or more indexes if they exist.
When performing
a table scan, the query optimizer reads all the rows in the table, and extracts
the rows that meet the criteria of the query. A table scan generates many disk
I/O operations and can be resource intensive. However, a table scan could be
the most efficient method if, for example, the result set of the query is a
high percentage of rows from the table.
When the query
optimizer uses an index, it searches the index key columns, finds the storage
location of the rows needed by the query and extracts the matching rows from
that location. Generally, searching the index is much faster than searching the
table because unlike a table, an index frequently contains very few columns per
row and the rows are in sorted order.
The query
optimizer typically selects the most efficient method when executing queries.
However, if no indexes are available, the query optimizer must use a table
scan. Your task is to design and create indexes that are best suited to your
environment so that the query optimizer has a selection of efficient indexes
from which to select.
The following tasks make up
SQL Server recommended strategy for creating indexes:
- Design the index.
Index design is a critical task. Index design includes determining which columns to use, selecting the index type (for example, clustered or nonclustered), selecting appropriate index options, and determining filegroup or partition scheme placement. For more information, see Designing Indexes.
- Determine the best creation method. Indexes are
created in the following ways:
-
By defining a PRIMARY KEY or UNIQUE constraint on a
column by using CREATE TABLE or ALTER TABLE
-
The SQL Server 2005 Database Engine automatically
creates a unique index to enforce the uniqueness requirements of a PRIMARY KEY
or UNIQUE constraint. By default, a unique clustered index is created to
enforce a PRIMARY KEY constraint, unless a clustered index already exists on
the table, or you specify a unique nonclustered index. By default, a unique
nonclustered index is created to enforce a UNIQUE constraint unless a unique
clustered index is explicitly specified and a clustered index on the table does
not exist.
-
An index created as part of a PRIMARY KEY or
UNIQUE constraint is automatically given the same name as the constraint name.
-
By creating an index independent of a constraint by
using the CREATE INDEX statement, you must specify the name of the index,
table, and columns to which the index applies. Index options and index
location, filegroup or partition scheme, can also be specified. By default, a
nonclustered, nonunique index is created if the clustered or unique options are
not specified.
- Create the index:
-
Whether the index will be created on an empty table or
one that contains data is an important factor to consider. Creating an index on
an empty table has no performance implications at the time the index is created;
however, performance will be affected when data is added to the table.
-
Creating indexes on large tables should be planned
carefully so database performance is not hindered. The preferred way to create
indexes on large tables is to start with the clustered index and then build any
nonclustered indexes.
Syntax for creating a Index:
CREATE
[UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON
<table_name | view_name> (column [ASC | DESC] [,...n ])
CREATE
UNIQUE CLUSTERED INDEX ENO_IND ON EMP(EMPNO)
-In this case it creates a unique
clustered index on the empno column.
CREATE
INDEX ENAME_IND ON EMP(ENAME)
-In this case it creates a
non-unique non-clustered index on the ename column.
INDEXED
VIEWS
- An indexed view is a view that has been materialized,
this means it has been computed and stored.
- You index a view by creating a unique clustered index
on it.
- Indexed views dramatically improve the performance of
some types of queries.
- Indexed views work best for queries that aggregate
many rows.
- They are not well-suited for underlying data sets
that are frequently updated
Views are also known as virtual
tables. The result set of a standard view is not stored permanently in the
database. For a standard view, the overhead of dynamically building the result
set for each query that references a view can be significant for views that
involve complex processing of large numbers of rows, such as aggregating lots
of data, or joining many rows. If such views are frequently referenced in
queries, you can improve performance by creating a unique clustered index on
the view, which is know as Indexed View. When a unique clustered index is
created on a view, the result set is stored in the database just like a table
with a clustered index is stored.
Another benefit of creating an
index on a view is existing queries can benefit from the improved efficiency of
retrieving data from the indexed view without having to be recoded.
As modifications are made to the
data in the base tables, the data modifications are reflected in the data
stored in the indexed view. The requirement that the clustered index of the
view be unique improves the efficiency with which SQL Server can find the rows
in the index that are affected by any data modification.
If we want to create an Indexed
View we need to do the following:
- Create a View by using the with SchemaBinding Option.
- Create a Unique Clustered Index on the View
CREATE VIEW IND_VIEW
WITH SCHEMABINDING
AS
SELECT DEPTNO, SUM(ISNULL(SAL, 0)) AS [TOTAL SAL],
COUNT_BIG(*) AS [TOTAL RECORDS] FROM DBO.EMP GROUP BY DEPTNO
CREATE UNIQUE CLUSTERED
INDEX DEPTNO_IND ON IND_VIEW(DEPTNO)
-Once the index is created on the view it will
internally the store the information of the View physicially in a location, any
manupulation performed on the base table reflects to the the View also.
TSQL PROGRAMMING
- TSQL (Transact SQL) Programming is an Procedural Language Extension
to SQL which is known as PL/SQL in Oracle.
- It extends SQL by adding programming structures and subroutines
available in any high level language.
- It has syntax and rules that determine how programming statements
work together.
- We can control the program flow by using conditional statements like
IF and While loop
- Runtime Error Handling is provided using the try catch mechanism
- Reusability of the code is available by defining objects such as
Procedures and Functions.
- SQL Commands can be embedded inside the programs.
- Program Blocks can be of 2 types:
- Anonymous Blocks
- Sub-Program Blocks
Anonymous Blocks: They are unnamed block of code for
execution which can be written at a point where they are to be executed. They
can be written on a Query window and execute.
Sub-Program Blocks: These are nothing but named block
of code for execution, where the program blocks are given a name for
identification. These will be stored on the database which provides the
reusability of code.
Program Blocks like in any other language provides
option for variable declaration, program logic using conditional statements and
displaying the results to the user, in the same way we can define programs in
SQL Server also.
Declaring Variables: While declaring variables it has
to be preceded with @ symbol.
Syntax:
DECLARE @<var> [AS] <data_type> [,…n]
DECLARE @X INT
DECLARE @SAL AS MONEY
DECLARE @ENAME VARCHAR(50), @JOB VARCHAR(50)
Assinging Values to Variables: Values can be assigned
by using a SET statement.
Syntax:
SET @<var> = <value>
SET @X=100
SET @ENAME=’SCOTT’
Printing Values: If we want to print the values we can
use the Print statement.
Syntax:
Print @<var>
Print @X
Print @Ename
Conditional Statements:
If … Else If … Else: Imposes conditions on the execution of a
Transact-SQL statement. The Transact-SQL statement that follows an IF keyword
and its condition are executed if the condition is satisfied: the Boolean
expression returns TRUE. The optional ELSE keyword introduces another
Transact-SQL statement that is executed when the IF condition is not satisfied:
the Boolean expression returns FALSE.
IF
Boolean_expression
[
BEGIN ]
< sql_statement |
statement_block >
[ END ]
[ ELSE IF
Boolean_expression
[
BEGIN ]
< sql_statement |
statement_block >
[ END ]
ELSE
[
BEGIN ]
< sql_statement |
statement_block > ]
[ END ]
-If there are multiple statements
being enclosed between each block then we can put them under Begin and End
Statements.
DECLARE @WEEK INT
SET @WEEK=DATEPART(DW,
GETDATE())
IF @WEEK=1
PRINT 'SUNDAY'
ELSE IF @WEEK=2
PRINT 'MONDAY'
ELSE IF @WEEK=3
PRINT 'TUESDAY'
ELSE IF @WEEK=4
PRINT 'WEDNESDAY'
ELSE IF @WEEK=5
PRINT 'THURSDAY'
ELSE IF @WEEK=6
PRINT 'FRIDAY'
ELSE IF @WEEK=7
PRINT 'SATURDAY'
CASE FUNCTION: The case
function what we have discussed under the System Functions can also be used
here as following:
DECLARE @WEEK INT
SET @WEEK=DATEPART(DW,
GETDATE())
SELECT CASE @WEEK
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN
'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
-This can be written in the second style of the CASE
Statement also that has been discussed in the SQL as following:
DECLARE @WEEK INT
SET @WEEK=DATEPART(DW, GETDATE())
SELECT CASE
WHEN @WEEK=1 THEN 'SUNDAY'
WHEN @WEEK=2 THEN 'MONDAY'
WHEN @WEEK=3 THEN 'TUESDAY'
WHEN @WEEK=4 THEN 'WEDNESDAY'
WHEN @WEEK=5 THEN 'THURSDAY'
WHEN @WEEK=6 THEN 'FRIDAY'
WHEN @WEEK=7 THEN 'SATURDAY'
END
While Loop: Sets a
condition for the repeated execution of an SQL statement or statement block.
The statements are executed repeatedly as long as the specified condition is
true. The execution of statements in the WHILE loop can be controlled from
inside the loop with the BREAK and CONTINUE keywords.
WHILE
Boolean_expression
[
BEGIN ]
<
sql_statement | statement_block >
[ BREAK ]
<
sql_statement | statement_block >
[ CONTINUE ]
<
sql_statement | statement_block >
[ END ]
-If there are multiple statements
being enclosed then we can put them under Begin and End Statements.
BREAK: Causes an exit from the
innermost WHILE loop. Any statements that appear after the END keyword, marking
the end of the loop, are executed.
CONTINUE: Causes the WHILE loop
to restart, ignoring any statements after the CONTINUE keyword.
Program 1:
DECLARE @X INT
SET @X=0
WHILE @X<10
BEGIN
SET @X=@X+1
PRINT @X
END
Program
2:
DECLARE @X INT
SET @X=0
WHILE @X<10
BEGIN
SET @X=@X+1
IF @X=6 BREAK
PRINT @X
END
-In
this case the break statement brings the control out of the loop printing from
1 to 5.
Program
3:
DECLARE @X INT
SET @X=0
WHILE @X<10
BEGIN
SET @X=@X+1
IF @X=6 CONTINUE
PRINT @X
END
-In
this case the continue statement will skip the print statement when the value
of x is 6 so prints from 1 to 5 and 7 to 10.
Comments
in TSQL: Comments will be ignored will executing the program, they will
increase the readability and aids understanding of the program.
- Single Line Comments
(--)
- Multi Line Comments
(/* ….. */)
Assinging
values from columns into variables: Till now we were assigning static values to
the variables using the SET statement, but we can also assign values from a
column into the variables as following:
SELECT @<var>=<col_name>
[, ……n] FROM <table_name> [CONDITIONS]
SELECT @ENAME=ENAME FROM EMP WHERE
EMPNO=1001
-A
simple TSQL program which takes the Empno and prints the Name and Salary.
DECLARE @EMPNO INT, @ENAME
VARCHAR(50), @SAL MONEY
SET @EMPNO=1005
SELECT @ENAME=ENAME, @SAL=SAL FROM EMP WHERE
EMPNO=@EMPNO
PRINT @ENAME + ' EARNS ' + CAST(@SAL AS VARCHAR)
-A
Program which takes the Empno and increments the Salary of the person on the
following criteria:
If Job is President increment with
10%
If Job is Manager increment with 8%
If Job is Analyst increment with 6%
If Job is any thing other incrment
with 5%
DECLARE @EMPNO INT, @JOB VARCHAR(50)
SET @EMPNO=1005
SELECT @JOB=JOB FROM EMP WHERE EMPNO=@EMPNO
IF @JOB='PRESIDENT'
UPDATE EMP SET SAL = SAL + SAL * 0.1 WHERE
EMPNO=@EMPNO
ELSE IF @JOB='MANAGER'
UPDATE EMP SET SAL = SAL + SAL * 0.08 WHERE
EMPNO=@EMPNO
ELSE IF @JOB='ANALYST'
UPDATE EMP SET SAL = SAL + SAL * 0.06 WHERE
EMPNO=@EMPNO
ELSE
UPDATE EMP SET SAL = SAL + SAL * 0.05 WHERE
EMPNO=@EMPNO
-In the above case which empno has been provided for the
variable @EMPNO first it will check for the JOB of the employee and then it
will increment the salary on the specified criteria .
-The problem in the above case is we can increment only one
Employee Salary at a time but if we want to increase the Salary of Multiple
Emloyees at the same time it is not possible, as multiple rows cannot be
effected within the program to over come
this we use Cursors.
- Operations in a relational database
act on a complete set of rows. The set of rows returned by a SELECT statement
consists of all the rows that satisfy the conditions in the WHERE clause of the
statement. This complete set of rows returned by the statement is known as the
result set. Applications, especially interactive online applications, cannot
always work effectively with the entire result set as a unit. These
applications need a mechanism to work with one by one row at a time. Cursors
are an extension to result sets that provide that mechanism.
Cursor
extend result processing by:
- Allowing positioning at specific rows of the result set.
- Retrieving one row or block of rows from the current position in the result set.
- Supporting data modifications to the rows at the current position in the result set.
- Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.
- Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.
Cursor Process:
Transact-SQL Cursors follows a general process that is used with all SQL
Server cursors:
- Associate a cursor with the result set of a Transact-SQL statement, and define characteristics of the cursor, such as whether the rows in the cursor can be updated.
- Execute the Transact-SQL statement to populate the cursor.
- Retrieve the rows in the cursor you want to see. The operation to retrieve one row or one block of rows from a cursor is called a fetch. Performing a series of fetches to retrieve rows in either a forward or backward direction is called scrolling.
- Optionally, perform modification operations (update or delete) on the row at the current position in the cursor.
- Close the Cursor
The Cursor Process has the following steps involved in it:
·
Declare a Cursor
·
Open a Cursor
·
Fetch data from the
Cursor
·
Close the Cursor
·
De-allocate the
Cursor
Declaring a Cursor:
Defines the attributes of a Transact-SQL server cursor, such as its scrolling
behavior and the query used to build the result set on which the cursor
operates.
DECLARE
cursor_name CURSOR
[ LOCAL | GLOBAL
]
[ FORWARD_ONLY |
SCROLL ]
[ STATIC |
KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY |
SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [
OF column_name [ ,...n ] ] ]
LOCAL: Specifies that the scope
of the cursor is local to the program in which the cursor was created.
GLOBAL: Specifies that the scope
of the cursor is global to the connection. The cursor name can be referenced in
any program by the connection. The cursor is only implicitly deallocated at
disconnect.
If neither GLOBAL nor LOCAL is
specified, the default is taken as GLOBAL.
FORWARD_ONLY: Specifies that the
cursor can only be scrolled from the first to the last row. FETCH NEXT is the
only supported fetch option. When neither FORWARD_ONLY nor SCROLL is specified,
FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are
specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL.
SCROLL: Specifies that the cursor
can scroll from first to the last row as well as last to first row also. It
Supports 6 fetch methods like FETCH NEXT, FETCH PRIOR, FETCH FIRST, FETCH LAST,
FETCH ABSOLUTE n and FETCH RELATIVE n.
STATIC: Defines a cursor that
makes a temporary copy of the data to be used by the cursor. All requests to
the cursor are answered from this temporary table in tempdb; therefore,
modifications made to base tables are not reflected in the data returned by
fetches made to this cursor, and this cursor does not allow modifications
KEYSET: Specifies that the
membership and order of rows in the cursor are fixed when the cursor is opened.
The set of keys that uniquely identify the rows is built into a table in tempdb
known as the keyset. Changes to nonkey values in the base tables, either
made by the cursor or committed by other users, are visible as we scroll around
the cursor. Inserts made by other users are not visible. If a row is deleted,
an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key
values from outside the cursor resemble a delete of the old row followed by an
insert of the new row.
DYNAMIC: Defines a cursor that
reflects all data changes made to the rows in its result set as you scroll
around the cursor. The data values, order, and membership of the rows can
change on each fetch.
FAST_FORWARD: Specifies a
FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.
FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.
READ_ONLY: Prevents updates made
through this cursor. This option overrides the default capability of a cursor
to be updated.
SCROLL_LOCKS: Specifies that
positioned updates or deletes made through the cursor are guaranteed to
succeed. Microsoft SQL Server locks the rows as they are read into the cursor
to ensure their availability for later modifications. SCROLL_LOCKS cannot be
specified if FAST_FORWARD is also specified.
OPTIMISTIC: Specifies that
positioned updates or deletes made through the cursor do not succeed if the row
has been updated in the table since it was read into the cursor. SQL Server
does not lock rows as they are read into the cursor. It instead uses
comparisons of timestamp column values, or a checksum value if the table
has no timestamp column, to determine whether the row was modified after
it was read into the cursor. If the row was modified, the attempted positioned
update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also
specified.
TYPE_WARNING: Specifies that a
warning message is sent to the client if the cursor is implicitly converted
from the requested type to another.
FOR UPDATE [OF column name
[,...n]]: Defines updatable columns within the cursor. If OF column_name
[,...n] is supplied, only the columns listed allow modifications.
If UPDATE is specified without a column list, all columns can be updated,
unless the READ_ONLY concurrency option was specified.
Opening a Cursor: Opens a
Transact-SQL server cursor and populates the cursor by executing the
Transact-SQL statement specified on the DECLARE CURSOR.
Syntax: OPEN <cursor_name>
Fetching data from the Cursor:
Retrieves a specific row from a Transact-SQL server cursor into specified
variables.
Syntax:
FETCH [ NEXT | PRIOR | FIRST |
LAST | ABSOLUTE n | RELATIVE n ]
FROM
<cursor_name> INTO @variable_name [ ,...n ]
NEXT: Returns the result row
immediately following the current row and increments the current row to the row
returned. If FETCH NEXT is the first fetch against a cursor, it returns the
first row in the result set. NEXT is the default cursor fetch option.
PRIOR: Returns the result row
immediately preceding the current row, and decrements the current row to the
row returned. If FETCH PRIOR is the first fetch against a cursor, no row is
returned and the cursor is left positioned before the first row.
FIRST: Returns the first row in
the cursor and makes it the current row.
LAST: Returns the last row in the
cursor and makes it the current row.
ABSOLUTE n: If n is positive, it returns the specified
nth row from the front of the cursor. If n is negative, it returns the
specified nth row from the back of the cursor.
RELATIVE n: If n is positive, returns the row n rows
beyond the current row. If n is negative, returns the row n rows prior to the
current row.
If any of the used fetch
statement is successful it returns the status of it which will be stored in a
implicit variable @@FETCH_STATUS (this does not requires to be declared) which
can be any of the following values:
0 - The
FETCH statement was successful
-1 - The
FETCH statement failed or the row was beyond the result set
-2 - The
row fetched is missing
Closing a Cursor: Closes
an open cursor by releasing the current result set and freeing any cursor locks
held on the rows on which the cursor is positioned. CLOSE leaves the data
structures available for reopening, but fetches and positioned updates are not
allowed until the cursor is reopened. CLOSE must be issued on an open cursor; CLOSE
is not allowed on cursors that have only been declared or are already closed.
Syntax:
Close <cursor_name>
Deallocating a Cursor: Removes
a cursor reference. When the last cursor reference is deallocated, SQL Server
releases the data structures comprising the cursor.
Syntax: Deallocate <cursor_name>
Using a Simple Cursor:
DECLARE EMPCUR CURSOR FOR SELECT ENAME, SAL FROM EMP
DECLARE @ENAME VARCHAR(50), @SAL MONEY
OPEN EMPCUR
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'SALARY OF ' + @ENAME + ' IS ' + CAST(@SAL AS
VARCHAR)
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL
END
CLOSE EMPCUR
DEALLOCATE EMPCUR
Using a Cursor to Update all the rows of the Table:
This program will explain you how
we can update all the rows of the table basing on some conditions, similar to
the program we have written before discussing cursors but there only a single
row has been modified.
DECLARE EMPCUR CURSOR FOR
SELECT EMPNO, JOB FROM EMP
DECLARE @EMPNO INT, @JOB
VARCHAR(50)
OPEN EMPCUR
FETCH NEXT FROM EMPCUR INTO
@EMPNO, @JOB
WHILE @@FETCH_STATUS=0
BEGIN
IF @JOB='PRESIDENT'
UPDATE EMP SET SAL = SAL + SAL * 0.1 WHERE
EMPNO=@EMPNO
ELSE IF @JOB='MANAGER'
UPDATE EMP SET SAL = SAL + SAL * 0.08 WHERE
EMPNO=@EMPNO
ELSE IF @JOB='ANALYST'
UPDATE EMP SET SAL = SAL + SAL * 0.06 WHERE
EMPNO=@EMPNO
ELSE
UPDATE EMP SET SAL = SAL + SAL * 0.05 WHERE
EMPNO=@EMPNO
FETCH NEXT FROM EMPCUR INTO @EMPNO, @JOB
END
CLOSE EMPCUR
DEALLOCATE EMPCUR
Using a Global Cursor:
Program 1:
DECLARE EMPCUR CURSOR GLOBAL
FOR SELECT ENAME, SAL, COMM FROM EMP
DECLARE @ENAME VARCHAR(50), @SAL MONEY, @COMM MONEY,
@TOTSAL MONEY
OPEN EMPCUR
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM
WHILE @@FETCH_STATUS=0
BEGIN
SET @TOTSAL=@SAL + ISNULL(@COMM, 0)
PRINT @ENAME + ' EARNS ' + CAST(@TOTSAL AS VARCHAR) +
' EVERY MONTH'
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM
END
CLOSE EMPCUR
-In
the above case because it was a Global cursor we are not using any Deallocate
Cursor statement, now we use the same cursor in other programs with of declaring
it as following:
Program 2:
DECLARE @ENAME VARCHAR(50), @SAL MONEY, @COMM MONEY,
@ANNSAL MONEY
OPEN EMPCUR
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM
WHILE @@FETCH_STATUS=0
BEGIN
SET @ANNSAL=(@SAL + ISNULL(@COMM, 0)) * 12
PRINT @ENAME + ' EARNS ' + CAST(@ANNSAL AS VARCHAR) +
' EVERY YEAR'
FETCH NEXT FROM EMPCUR INTO @ENAME, @SAL, @COMM
END
CLOSE EMPCUR
-We don’t require to Deallocate
the Cursor any where it gets deallocated when we close the connection.
Using a Static Cursor:
DECLARE EMPCUR CURSOR
STATIC
FOR SELECT SAL FROM EMP WHERE EMPNO=1005
DECLARE @SAL MONEY
OPEN EMPCUR
UPDATE EMP SET SAL=6000 WHERE EMPNO=1005
FETCH NEXT FROM EMPCUR INTO @SAL
PRINT @SAL
CLOSE EMPCUR
DEALLOCATE EMPCUR
-In this case after opening the
cursor we have performed an update of Sal on the EMP table but still the cursor
contains the old value but not the new value, so it prints the old Salary value
only.
Using a Dynamic Cursor:
DECLARE EMPCUR CURSOR
DYNAMIC
FOR SELECT SAL FROM EMP WHERE EMPNO=1005
DECLARE @SAL MONEY
OPEN EMPCUR
UPDATE EMP SET SAL=4000 WHERE EMPNO=1005
FETCH NEXT FROM EMPCUR INTO @SAL
PRINT @SAL
CLOSE EMPCUR
DEALLOCATE EMPCUR
-In this case after opening the
cursor we have performed an update of Sal on the EMP table but the cursor
contains the new value but not the new value, so it prints the new Salary value
only.
Using Scroll Cursor:
DECLARE EMPCUR CURSOR
SCROLL
FOR SELECT EMPNO FROM EMP
DECLARE @EMPNO INT
OPEN EMPCUR
FETCH NEXT FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH LAST FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH PRIOR FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH FIRST FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH ABSOLUTE 12 FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH ABSOLUTE -10
FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH RELATIVE 3 FROM EMPCUR INTO @EMPNO
PRINT @EMPNO
FETCH RELATIVE -5 FROM
EMPCUR INTO @EMPNO
PRINT @EMPNO
CLOSE EMPCUR
DEALLOCATE EMPCUR
-As
we have declared the cursor as scroll all the Fetch methods can be used on it.
SUB-PROGRAMS
A
Sub-Program is a new block of code which can be reused. We have 2 types of
Sub-Programs in SQL Server:
1. Procedures
2. Functions
Procedures:
- A stored procedure is a saved collection of
Transact-SQL statements or a reference to a Microsoft .NET Framework
common language runtime (CLR) method that can take and return
user-supplied parameters.
- Procedures can be created for permanent use or for
temporary use within a session, local temporary procedure, or for
temporary use within all sessions, global temporary procedure.
- Stored procedures can also be created to run
automatically when an instance of SQL Server starts.
Syntax:
CREATE | ALTER PROCEDURE
<procedure_name>
[ ( @parameter1 <data_type>
[ = default ] [ OUT | OUTPUT ],
@parameter2 <data_type> [ =
default ] [ OUT | OUTPUT ],
…………………….
@parametern
<data_type> [ = default ] [ OUT | OUTPUT ] ) ]
[
WITH <procedure_options> ]
AS
BEGIN
<statements>
END
ALTER: Modifies a previously
created procedure that was created by executing the CREATE PROCEDURE statement.
ALTER PROCEDURE does not change permissions and does not affect any dependent
stored procedures or triggers.
Procedure Options: The Procedure
provide to options that can be used while creating the procedures. They are:
1. Encryption
2. Recompile
RECOMPILE: Indicates that the
Database Engine does not cache a plan for this procedure and the procedure is
compiled at run time. To instruct the Database Engine to discard plans for
individual queries inside a stored procedure, use the RECOMPILE query hint. Use
the RECOMPILE query hint when atypical or temporary values are used in only a
subset of queries that belong to the stored procedure.
Important: Because the SQL
Server 2005 query optimizer typically selects the best execution plan for a
query, we recommend that hints, including <query_hint>, be used only as a
last resort by experienced developers and database administrators.
ENCRYPTION: Indicates that SQL
Server will convert the original text of the CREATE PROCEDURE statement to an
obfuscated format. The output of the obfuscation is not directly visible in any
of the catalog views in SQL Server 2005. Users that have no access to system
tables or database files cannot retrieve the obfuscated text.
-Procedure
contains 2 parts in it: 1. Header 2. Body
-Header
part is the content above the AS keyword.
-Body
part is the content below the AS keyword.
Passing
Parameters to Procedures: As if we are passing parameters to functions in
languages, we can also pass parameters to Procedures. They are the means to
pass a value to the procedure or returns from a procedure.
Parameter
Modes: These will specify whether ther parameter is passed into the procedure
or returned out of the procedure. SQL Server supports to Parameter Modes:
·
IN MODE (DEFAULT)
·
OUT OR OUTPUT
MODE
IN
MODE: Passes a value into the procedure for execution, this is best suitable
for constants & expressions. The value of it can be changed with in the
program but cannot be returned. It is the default mode if nothing is specified
OUT
MODE: Passes a value back from the program after the execution of the
procedure.
The value of this option can be
returned to the calling EXECUTE statement. Use OUTPUT parameters to return
values to the caller of the procedure. text, ntext, and image
parameters cannot be used as OUTPUT parameters
Syntax
for executing the Procedure:
EXEC | EXECUTE [ [@parameter=]
<value> [OUTPUT] [DEFAULT] [,…n]
]
A
Simple Procedure:
CREATE PROCEDURE PROC1
AS
BEGIN
PRINT 'MY FIRST PROCEDURE'
END
-Executing the above procedure:
EXEC
PROC1 OR EXECUTE PROC1
A Procedure which accepts arguments:
ALTER PROCEDURE PROC2(@X INT, @Y
INT)
AS
BEGIN
DECLARE @Z INT
SET @Z=@X+@Y
PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z AS
VARCHAR)
END
-Executing the above procedure in
2 ways:
1. EXEC
PROC2 100, 50
2. EXEC PROC2 @X=100, @Y=50
A
Procedure with Default Values:
CREATE PROCEDURE PROC3(@X INT = 100,
@Y INT)
AS
BEGIN
DECLARE @Z INT
SET @Z=@X+@Y
PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z AS
VARCHAR)
END
-Executing the above procedure:
1. EXEC
PROC3 200, 25
2. EXEC PROC3 @X=200, @Y=25
3. EXEC PROC3 @X=DEFAULT, @Y=25
4. EXEC PROC3 @Y=25
-In
the 3rd and 4th case it uses the default value of 100 to
the varibale X which has been given while creating the procedure.
A
Procedure with OUTPUT Parameter:
CREATE PROCEDURE PROC4(@X INT, @Y INT, @Z INT OUTPUT)
AS
BEGIN
SET @Z=@X+@Y
END
- Executing
the above procedure:
DECLARE @A INT
EXECUTE PROC4 500, 250, @A OUTPUT
PRINT @A
-A
Procedure for Inserting values into the Emp Table:
CREATE PROCEDURE Insert_Emp(@Empno int, @Ename
varchar(50), @Sal money, @Deptno int)
As
Begin
INSERT INTO Emp (Empno, Ename, Sal, Deptno) VALUES
(@Empno, @Ename, @Sal, @deptno)
End
-
Executing the above Procedure:
EXEC Insert_Emp 1016, ‘Sudhakar’,
2500, 10
-A
Procedure for Inserting values into the Emp Table but with Validations:
-This is same as the previous one
but with the following validations present in it:
-Empno cannot be NULL
value.
-Empno cannot be
duplicated.
-Salary cannot be less
than 2500.
-Deptno should be present
in the Dept Table.
CREATE PROCEDURE Insert_Emp(@Empno int, @Ename
varchar(50), @Sal money, @Deptno int)
As
Begin
IF @Empno IS NULL
Begin
Print ‘Empno cannot be NULL’
Return
End
IF Exists(SELECT * FROM Emp WHERE Empno=@Empno)
Begin
Print ‘Empno cannot be Duplicated’
Return
End
IF @Sal<2500 Begin
Print ‘Salary cannot be less than 2500’
Return
End
IF Not Exists(SELECT * FROM Dept WHERE Deptno=@Deptno)
Begin
Print ‘Deptno not found in the Dept Table’
Return
End
INSERT INTO Emp (Empno, Ename, Sal, Deptno) VALUES
(@Empno, @Ename, @Sal, @deptno)
End
-A
Procedure which takes the Empno and returns the Provident Fund and Professional
Tax at 12% and 5% respectively on the Salary.
CREATE PROCEDURE Deductions(@Empno int, @PF money
OUTPUT, @PT money OUTPUT)
As
Begin
Declare @Sal Money
SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno
SET @PF=@Sal * 0.12
SET @PT=@Sal * 0.05
End
-Executing
the above Procedure:
Declare @VPF money, @VPT money
EXEC Deductions 1005, @VPF OUTPUT,
@VPT OUTPUT
Print @VPF
Print @VPT
-A
Procedure which takes the Empno and prints the Net Salary of the Employee.
CREATE PROCEDURE Net_Sal(@Empno int)
As
Begin
Declare @VSal money, @NSal money, @VPF money, @VPT
money
EXEC Deductions @Empno, @VPF OUTPUT, @VPT OUTPUT
SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno
SET @NSal = @VSal - @VPF - @VPT
Print ‘Net Salary of the Employee is: ‘ + Cast(@NSal
as Varchar)
End
-Executing
the above Procedure:
EXEC Net_Sal 1005
-A
Procedure which will Insert values into the Dept table by generating a unique
Deptno.
CREATE PROCEDURE Insert_Dept(@Dname
varchar(50), @Loc varchar(50))
As
Begin
Declare @Deptno int
Select @Deptno = ISNULL(MAX(Deptno),
0) + 10 FROM Dept
INSERT INTO Dept Values (@Deptno,
@Dname, @Loc)
End
-Executing
the above Procedure:
EXEC Insert_Dept ‘Research’,
‘Hyderabad’
-A
Procedure which is used from transferring amount from one account to the other
within the Bank table:
CREATE PROCEDURE Funds_Transfer(@SrcID int, @DestID
int, @Amt money)
As
Begin
UPDATE BANK SET Bal = Bal - @Amt WHERE CUSTID=@SrcID
UPDATE BANK SET Bal = Bal + @Amt WHERE CUSTID=@DestID
End
-Executing
the above Procedure:
EXEC Funds_Transfer 101, 102, 500
-
In the above case
if the SrcID or DestID are not present in the table then it will deduct the amount from the other or
add the amount from the other to avoid this we need to use transaction
management.
-
To manage the
transaction first we need to identify which statement is executed and which
failed for this we use the function @@ROWCOUNT.
-
@@ROWCOUNT returns the number of rows affected by the last
statement.
-Managing
Transactions in the Procedure:
CREATE PROCEDURE Funds_Transfer(@SrcID int, @DestID
int, @Amt money)
As
Begin
Declare @Count1 int, @Count2 int
Begin Transaction
UPDATE BANK SET Bal = Bal - @Amt WHERE CUSTID=@SrcID
Set @Count1=@@ROWCOUNT
UPDATE BANK SET Bal = Bal + @Amt WHERE CUSTID=@DestID
Set @Count2=@@ROWCOUNT]
IF @COUNT1=@COUNT2
Begin
COMMIT
PRINT
‘TRANSACTION COMMITED’
End
ELSE
Begin
ROLLBACK
PRINT
‘TRANSACTION ROLLED BACK’
End
End
Handling Errors in Procedures:
-
In SQL Server when
a error occurs, the statement that caused the error is terminated, but the
execution of the stored procedure or batch continues.
-
When stored procedures and batches are executed
within the scope of a TRY block, batch abort errors can be handled by the
TRY…CATCH construct.
-
Errors in Transact-SQL code can be processed using a
TRY…CATCH construct similar to the exception-handling features of the
languages.
-
A TRY…CATCH construct consists of two parts: a TRY
block and a CATCH block.
-
When an error condition is detected in a Transact-SQL
statement contained in a TRY block, control is passed to a CATCH block where it
can be processed.
-
After the CATCH block handles the exception, control is
then transferred to the first Transact-SQL statement that follows the END CATCH
statement.
-
If the END CATCH statement is the last statement in a
stored procedure or trigger, control is returned to the code that invoked the
stored procedure or trigger.
-
Transact-SQL statements in the TRY block following the
statement that generates an error will not get executed.
-
If there are no errors inside the TRY block, control
passes to the statement immediately after the associated END CATCH statement.
-
If the END CATCH statement is the last statement in a stored
procedure or trigger, control is passed to the statement that invoked the
stored procedure or trigger.
-
A TRY block starts with the BEGIN TRY statement and
ends with the END TRY statement.
-
One or more Transact-SQL statements can be specified
between the BEGIN TRY and END TRY statements.
-
A CATCH block must follow a TRY block immediately.
-
A CATCH block starts with the BEGIN CATCH statement and
ends with the END CATCH statement.
-
In Transact-SQL, each TRY block is associated with only
one CATCH block.
-A
Procedure which can cause Error:
CREATE PROCEDURE Div(@X int, @Y int)
As
Begin
Declare @Z int
SET @Z=0
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
END
-Executing
the above procedure:
EXEC DIVX 100, 20
EXEC DIVX 100, 0
-The
first execution will print the result of 5 but the second time execution will
raise an error because we cannot divide a number by zero, in this case still it
will try to print the result as 0, because even if the error is encountered it
will not stop the execution of the program, if we want to stop the execution of
the program when an error raises the code has to be written in the following
way:
CREATE PROCEDURE Div(@X int, @Y int)
As
Begin
Begin Try
Declare @Z INT
SET @Z=0
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
End Try
Begin Catch
Print Error_Message()
End Catch
End
-Executing
the above procedure:
EXEC DIVX 100, 20
EXEC DIVX 100, 0
-
Every error has 4
properties to it, they are:
o
Msg id
o
Msg str
o
Severity
o
State
For
Example try the following statement:
Print 100/0
-This will display the following error message:
Msg 8134, Level 16, State 1,
Divide by zero error encountered.
-In
this the Msg id is 8134, Msg str is “Divide by zero error encountered”,
Severity Level is 16 and State is 1.
Msg
id: ID of the message, which is unique across server. Message IDs less
than 50000 are system messages.
Msg str: Error message that has
to be displayed when the error raises.
Severity Level: Severity level
that is associated with the error. Severity levels can range between 0 and 25.
Severity levels from 20 through 25 are considered fatal. If a fatal severity
level is encountered, the client connection is terminated after receiving the
message, and the error is logged in the error and application logs.
State: Is an arbitrary integer
from 1 through 127. If the same user-defined error is raised at multiple
locations, using a unique state number for each location can help find which
section of code is raising the errors.
Raising Errors Manually:
We can also raise errors manually at some required situations. It is used to
return messages back to applications using the same format as a system error or
warning message generated by the SQL Server Database Engine. For raising an
error manually we use the Raiserror Statement.
It generates an error message and
initiates error processing for the session. RAISERROR can either reference a
user-defined message stored in the sys.messages catalog view or build a
message dynamically. The message is returned as a server error message to the
calling application or to an associated CATCH block of a TRY…CATCH construct.
RAISERROR can return either:
- A user-defined error message that has been created using the sp_addmessage system stored procedure.
- A message string specified in the RAISERROR statement.
RAISERROR can also:
- Assign a specific error number, severity, and state.
- Request that the error be logged in the Database Engine error log and the Microsoft Windows application log.
- Substitute argument values into the message text, much like the C language printf function.
Syntax: RAISERROR ( msg_id
| msg_str | @local_variable, severity, state
[, argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
msg_id: Is a user-defined error
message number stored in the sys.messages catalog view using sp_addmessage.
Error numbers for user-defined error messages should be greater than 50000.
When msg_id is not specified, RAISERROR raises an error message with an error
number of 50000.
msg_str: Is a user-defined
message with formatting similar to the printf function in the C standard
library. The error message can have a maximum of 2,047 characters. When msg_str
is specified, RAISERROR raises an error message with an error number of 50000.
msg_str is a string of characters
with optional embedded conversion specifications. Each conversion specification
defines how a value in the argument list is formatted and placed into a field
at the location of the conversion specification in msg_str. The parameters that
can be used in msg_str are:
d
or i Signed Integer
s String
u Unsigned
Integer
These type specifications are
based on the ones originally defined for the printf function in the C
standard library. The type specifications used in RAISERROR message strings map
to Transact-SQL data types, while the specifications used in printf map
to C language data types.
@local_variable: Is a variable of
any valid character data type that contains a string formatted in the same
manner as msg_str. @local_variable must be char or varchar.
Severity: Is the user-defined
severity level associated with this message. When using msg_id to raise a
user-defined message created using sp_addmessage, the severity specified
on RAISERROR overrides the severity specified in sp_addmessage.
Any user can specify severity
levels from 0 through 18. Members of the sysadmin fixed server role permissions
can only specify severity levels from 19 through 25, for which the WITH LOG
option is required.
State: Is an arbitrary integer
from 1 through 127. A negative value for state defaults to 1. The value 0 or
values larger than 127 generate an error.
Argument: Are the parameters used
in the substitution for variables defined in msg_str or the message
corresponding to msg_id. There can be 0 or more substitution parameters, but
the total number of substitution parameters cannot exceed 20. Each substitution
parameter can be a local variable or any of these data types: tinyint, smallint,
int, char, varchar, nchar, nvarchar, binary,
or varbinary. No other data types are supported.
Option: Is a custom option for
the error and can be one of the values in the following table.
1.
LOG: Logs the error in the error log and the
application log for the instance of the Microsoft SQL Server Database Engine.
Errors logged in the error log are currently limited to a maximum of 440 bytes.
Only a member of the sysadmin fixed server role can specify WITH LOG.
2.
NOWAIT: Sends messages immediately to the client.
3.
SETERROR: Sets the @@ERROR and ERROR_NUMBER values to
msg_id or 50000, regardless of the severity level.
The errors generated by RAISERROR
operate the same as errors generated by the Database Engine code. The values
specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE,
ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system
functions. When RAISERROR is run with a severity of 11 or higher in a TRY
block, it transfers control to the associated CATCH block.
The error is returned to the
caller if RAISERROR is run:
- Outside the scope of any TRY block.
- With a severity of 10 or lower in a TRY block.
- With a severity of 20 or higher that terminates the database connection.
CATCH blocks can use RAISERROR to
rethrow the error that invoked the CATCH block by using system functions such
as ERROR_NUMBER and ERROR_MESSAGE to retrieve the original error information.
@@ERROR is set to 0 by default for messages with a severity from 1 through 10.
-A procedure to divide 2 numbers
and will raise an error when the divisor is 1.
CREATE PROCEDURE Divx(@X
int, @Y int)
As
Begin
Declare @Z INT
SET @Z=0
IF @Y=1
RAISERROR ('CANNOT DIVIDE BY 1', 15, 1)
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
End
-Executing
the above procedure:
EXEC DIVX 100, 20
EXEC DIVX 100, 1
-In the above case the RAISERROR
statement raises the error but still next statements get executed. So if we
want to stop the execution on the same line the code has to be enclosed with in
the Try and Catch blocks.
CREATE PROCEDURE Divx(@X int, @Y int)
As
Begin
Begin Try
Declare @Z INT
SET @Z=0
IF @Y=1
RAISERROR ('CANNOT DIVIDE BY 1', 15, 1)
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
End Try
Begin Catch
PRINT ERROR_MESSAGE()
End Catch
End
-Executing
the above procedure:
EXEC DIVX 100, 1
-In the above case when the error
is raised the control transfers to the catch block and prints the error message
associated with the error.
-If we want to customize the
error message with formatting we can use the Raiserror statement as following:
RAISERROR ('CANNOT DIVIDE %d WITH %d', 15, 1, @X, @Y)
-In this case substituting the
value of variable @X at the first % d location and the @y at second % d
location it will generate the error message.
-We can also use the “WITH LOG”
option at the end of the string to write the error message into the SQL Server
Log File as following:
RAISERROR ('CANNOT DIVIDE %d WITH %d', 15, 1, @X, @Y)
WITH LOG
-After
running the procedure which will generate the error go and verify under the
following location in the Object Explorer of the Management Studio i.e. under the
Management node, SQL Server logs node, Current node click on it where we find
the error message.
Pre-defined Errors: All the
predefined error list of sql server can be found in the SYS.Messages Catalog
View. Query on the database with the following statement where we can view the
list of predefined errors:
-SELECT * FROM SYS.MESSAGES
-This will display the list of
errors with their error_id, severity level, error_msg and language_id.
-We can also insert our own user
defined error messages into it and use them when required, but because this is
a System Catalog View we cannot directly insert records into it, so SQL Server
provides a predefined Procedure SP_AddMessage which when called will insert the
record into the Catalog View.
SP_AddMessage: Stores a
new user-defined error message in an instance of the SQL Server Database
Engine. Messages stored using sp_addmessage can be viewed using the sys.messages
catalog view.
Syntax:
sp_addmessage [ @msgnum = ] msg_id ,
[ @severity = ] severity ,
[ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] 'with_log' ]
[ , [ @replace = ] 'replace' ]
[ @msgnum = ] msg_id: Is
the ID of the message. msg_id is int with a default of NULL. msg_id
for user-defined error messages can be an integer between 50,001 and
2,147,483,647. The combination of msg_id and language must be
unique; an error is returned if the ID already exists for the specified
language.
[ @severity = ] severity:
Is the severity level of the error. severity is smallint with a
default of NULL. Valid levels are from 1 through 25.
[ @msgtext = ] 'msg': Is
the text of the error message. msg is nvarchar(255) with a
default of NULL.
[ @lang = ] 'language': Is the language for
this message. Because multiple languages can be installed on the same server,
language specifies the language in which each message is written. When language
is omitted, the language is the default language for the session.
[ @with_log = ] {
'TRUE' | 'FALSE' ] }:
Is whether the message is to be written to the Windows application
log when it occurs. The @with_log is varchar(5)with a default of
FALSE. If TRUE, the error will be written in to the Windows application log. If
a message is written to the Windows application log, it is also written to the
Database Engine error log file.
[ @replace = ] 'replace': If specified
as the string replace, an existing error message is overwritten with new
message text and severity level. @replace is varchar(7) with a default of NULL.
This option must be specified if msg_id already exists. If you replace a U.S.
English message, the severity level is replaced for all messages in all other
languages that have the same msg_id.
EXEC
sp_addmessage 50001, 16, ‘Cannot Divide the Number by One’
-The above statement will insert
a record into the SYS.Messages System Catalog after it was inserted we can use
the raiseerror statement as following in our previous procedure:
Raiserror(50001,
16, 1)
-So when the error is raised the
corresponding error message is picked out from the Catalog View and displayed
to the user.
-Add Procedure, which will delete
a record from the dept table for the given deptno and will raise an error if
the deptno has any child records in the emp table.
CREATE
PROCEDURE Delete_Dept(@Deptno int)
As
Begin
IF
EXISTS(SELECT * FROM Emp WHERE Deptno=@Deptno)
Raiserror(‘Child
Records Found’, 15, 1)
ELSE
DELETE
FROM Dept WHERE Deptno=@Deptno
End
-After creating a Procedure at
any time if we want to view the content of it write the following statement:
SP_HELPTEXT
<procedure_name>
SP_HELPTEXT Delete_Dept
Creating a Procedure using
With Encryption Option:
CREATE PROCEDURE Delete_Dept(@Deptno int)
WITH
ENCRYPTION
As
Begin
IF
EXISTS(SELECT * FROM Emp WHERE Deptno=@Deptno)
Raiserror(‘Child
Records Found’, 15, 1)
ELSE
DELETE
FROM Dept WHERE Deptno=@Deptno
End
-If the Procedure is
created by using the With Encryption Option even if we use the SP_HELPTEXT also
we cannot view the content of it.
Functions:
- A Function is also
Stored Block of code similar to a Procedure.
- A Function is a Block
of Code which will return only a single value.
- A Function is not a
stand alone executable like a Procedure it can be executed in some other
context also.
- A Function can be used
in a Select Statement.
- Modifications to database tables, operations on
cursors that are not local to the function are examples of actions that
cannot be performed in a function.
- Try and Catch Statements cannot be used in the
Functions.
- A user-defined function takes zero or more input
parameters and returns either a scalar value or a table; a function can
have a maximum of 1024 input parameters.
- User-defined functions do not support output
parameters.
- When a parameter of the function has a default value,
the keyword DEFAULT must be specified when calling the function to get the
default value.
Functions are of 3 types:
1.
Scalar Functions
2.
Inline Table-valued
Functions
3.
Multistatement
Table-valued Functions
Scalar Functions: Functions are
scalar-valued if the RETURNS clause specifies one of the scalar data types.
Syntax: CREATE FUNCTION
<function_name>
( [ @parameter_name [ AS
] data_type [ = default ] [ ,...n ] ] )
RETURNS data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
function_options can be any of
these two:
- Encryption
- Schemabinding
ENCRYPTION: Indicates that the
Database Engine encrypts the catalog view columns that contain the text of the
CREATE FUNCTION statement.
SCHEMABINDING: Specifies that the
function is bound to the database objects that it references. The binding of
the function to the objects it references is removed only when one of the
following actions occurs:
- The function is dropped.
- The function is modified by using the ALTER statement with the SCHEMABINDING option not specified.
-A
Function that takes the Empno and Returns the total salary of the employee.
CREATE
FUNCTION GET_TSAL (@EMPNO INT)
RETURNS
MONEY
AS
BEGIN
DECLARE
@TSAL MONEY
SELECT @TSAL=SAL
+ ISNULL (COMM, 0) FROM EMP WHERE EMPNO=@EMPNO
RETURN @TSAL
END
Syntax for Calling a Scalar
Function:
SELECT
<owner>.<function_name>( <list of values> )
Calling the above function:
SELECT
DBO.GET_TSAL(1005)
Inline
Table-valued Functions: These functions can return a table as an output. In
inline table-valued functions, the TABLE return value is defined through a
single SELECT statement. Inline functions do not have associated return
variables.
Syntax: CREATE FUNCTION
<function_name>
( [ @parameter_name [ AS
] data_type [ = default ] [ ,...n ] ] )
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN [ ( ] select_stmt [ ) ]
END
-A function which takes the
deptno and returns the list of employees working in it by joining Emp and Dept
tables.
CREATE
FUNCTION GET_ED_DATA(@DEPTNO INT)
RETURNS TABLE
AS
RETURN (SELECT
E.EMPNO, ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC FROM EMP E INNER JOIN DEPT D ON
E.DEPTNO=D.DEPTNO WHERE E.DEPTNO=@DEPTNO)
Syntax for Calling a Table Valued
Functions:
SELECT
< * | <collist> FROM <function_name>( <list of values> )
Calling the above function:
SELECT
* FROM GET_ED_DATA(10)
SELECT
EMPNO, ENAME, DEPTNO, DNAME FROM GET_ED_DATA(20)
Multistatement
Table-valued Functions: These function are same as Inline Table-valued but the
body of this functions can contain multiple statement in it and the structure
of the table can be defined by us.
Syntax: CREATE FUNCTION
<function_name>
( [ @parameter_name [ AS
] data_type [ = default ] [ ,...n ] ] )
RETURNS @return_variable TABLE <
table_type_definition >
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
-A function which, takes the
Empno and calculates the Total Salary and Annual Salary of the employee and
returns them.
CREATE
FUNCTION GET_EMPDATA(@EMPNO INT)
RETURNS
@MYTABLE TABLE(TOTSAL MONEY, ANNSAL MONEY)
AS
BEGIN
DECLARE
@SAL MONEY, @COMM MONEY
DECLARE
@TSAL MONEY, @ASAL MONEY
SELECT @SAL=SAL,
@COMM=COMM FROM EMP WHERE EMPNO=@EMPNO
SET @TSAL=@SAL +
ISNULL(@COMM, 0)
SET @ASAL=(@SAL
+ ISNULL(@COMM, 0)) * 12
INSERT INTO @MYTABLE
VALUES(@TSAL, @ASAL)
RETURN
END
Calling the above function:
SELECT
* FROM GET_ EMPDATA(1005)
TRIGGERS
- Microsoft SQL Server 2005 provides two primary
mechanisms for enforcing business rules and data integrity: constraints
and triggers.
- A trigger is a special type of stored procedure that
automatically takes effect when a language event executes.
- SQL Server includes two general types of triggers:
DML triggers and DDL triggers.
- DDL triggers are new to SQL Server 2005. These triggers are invoked when a data definition language (DDL) event takes place in the server or database.
- DML triggers are invoked when a data manipulation language (DML) event takes place in the database. DML events include INSERT, UPDATE, or DELETE statements that modify data in a specified table or view.
- A DML trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.
DML triggers are useful in these
ways:
- They can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.
- They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with CHECK constraints.
- Unlike CHECK constraints, DML triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.
- They can evaluate the state of a table before and after a data modification and take actions based on that difference.
- Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.
Types of DML Triggers:
AFTER Triggers: AFTER
triggers are executed after the action of the INSERT, UPDATE, or DELETE
statement is performed. Specifying AFTER is the same as specifying FOR, which
is the only option available in earlier versions of Microsoft SQL Server. AFTER
triggers can be specified only on tables.
INSTEAD OF Triggers: INSTEAD
OF triggers are executed in place of the usual triggering action. INSTEAD OF
triggers can also be defined on views with one or more base tables, where they
can extend the types of updates a view can support.
A trigger is a special kind of
stored procedure that automatically executes when an event occurs in the
database server. DML triggers execute when a user tries to modify data through
a data manipulation language (DML) event. DML events are INSERT, UPDATE, or
DELETE statements on a table or view. DDL triggers execute in response to a
variety of data definition language (DDL) events. These are primarily CREATE,
ALTER, and DROP statements. DML and DDL triggers can be created in the SQL
Server 2005 Database Engine directly from Transact-SQL. SQL Server allows for
creating multiple triggers for any specific statement.
Syntax: CREATE TRIGGER
trigger_name
ON table
| view
[ WITH ENCRYPTION ]
FOR | AFTER | INSTEAD OF
[ INSERT ] [ , ] [ UPDATE ] [ , ]
[ DELETE ]
AS
BEGIN
sql_statements
END
table | view: Is the table or
view on which the DML trigger is executed and is sometimes referred to as the
trigger table or trigger view. A view can be referenced only by an INSTEAD OF
trigger.
WITH ENCRYPTION: Encrypts the
text of the CREATE TRIGGER statement.
AFTER: Specifies that the DML
trigger be fired only when all operations specified in the triggering SQL
statement have executed successfully. All referential cascade actions and
constraint checks also must succeed before this trigger fires. AFTER is the
default when FOR is the only keyword specified. AFTER triggers cannot be
defined on views.
INSTEAD OF: Specifies that the
DML trigger be executed instead of the triggering SQL statement, therefore,
overriding the actions of the triggering statements. At most, one INSTEAD OF
trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view.
INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK
OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an
updatable view WITH CHECK OPTION specified. The user must remove that option by
using ALTER VIEW before defining the INSTEAD OF trigger.
[ DELETE ] [ , ] [ INSERT
] [ , ] [ UPDATE ]: Specifies the data modification statements that
activate the DML trigger when it is tried against this table or view. At least
one option must be specified. Any combination of these options in any order is
allowed in the trigger definition. For INSTEAD OF triggers, the DELETE option
is not allowed on tables that have a referential relationship specifying a
cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables
that have a referential relationship specifying a cascade action ON UPDATE.
-A Trigger that will restrict the operations
to be performed before 9 A.M and after 5 P.M
CREATE TRIGGER EMP_TRG
ON EMP AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @DT INT
SET @DT=DATENAME(HH, GETDATE())
IF @DT NOT BETWEEN 9 AND 16
BEGIN
ROLLBACK
RAISERROR('CANNOT PERFORM DML OPERATIONS NOW', 15, 1)
END
END
-After the trigger is created try
to perform any DML Operations on the EMP table before 9 A.M and after 5 P.M the
Trigger will fire and restrict the operations.
- When we try to perform any DML Operation on a table when a trigger is present on it the values of the DML statement will be captured in the trigger inside 2 Magic Tables Inserted and Deleted that have the same structure of the table.
- The deleted table stores copies of the
affected rows during DELETE and UPDATE statements. During the execution of
a DELETE or UPDATE statement, rows are deleted from the trigger table and
transferred to the deleted table. The deleted table and the
trigger table ordinarily have no rows in common.
- The inserted table stores copies of the
affected rows during INSERT and UPDATE statements. During an insert or
update transaction, new rows are added at the same time to both the inserted
table and the trigger table. The rows in the inserted table are
copies of the new rows in the trigger table.
- An update transaction is similar to a delete
operation followed by an insert operation; the old rows are copied to the deleted
table first, and then the new rows are copied to the trigger table and to
the inserted table.
-A trigger that will convert the
DName and Loc into upper case when the user insert in lower case.
CREATE TRIGGER DEPT_CONVERT_TRG
ON DEPT AFTER INSERT
AS
BEGIN
DECLARE @DEPTNO INT
DECLARE @DNAME VARCHAR(50)
DECLARE @LOC VARCHAR(50)
SELECT @DEPTNO=DEPTNO, DNAME=@DNAME, @LOC=LOC FROM
INSERTED
UPDATE DEPT SET DNAME=UPPER(@DNAME), LOC=UPPER(@LOC)
WHERE DEPTNO=@DEPTNO
END
-To
test the trigger execute the following statement which will convert the data
into upper case in the table:
INSERT INTO DEPT VALUES(50, ‘research’ ,’hyderabad’)
-The
above trigger can be written in the following way also:
CREATE TRIGGER DEPT_CONVERT_TRG2
ON DEPT INSTEAD OF INSERT
AS
BEGIN
INSERT INTO DEPT
SELECT DEPTNO, UPPER(DNAME), UPPER(LOC) FROM INSERTED
END
-A
Trigger which will generate a unique Deptno when the user inserts a record into
the dept table only by specifying DName and Loc when a primary key constraint
is present on the Deptno column.
CREATE TRIGGER DEPT_GENERATE_TRG
ON DEPT INSTEAD OF INSERT
AS
BEGIN
DECLARE @DEPTNO INT
SELECT @DEPTNO=DEPTNO FROM INSERTED
IF @DEPTNO IS NULL
SELECT @DEPTNO=ISNULL(MAX(DEPTNO), 0) + 10 FROM DEPT
INSERT INTO DEPT SELECT @DEPTNO, DNAME, LOC FROM
INSERTED
END
-To
test the following Trigger execute the following statement:
INSERT INTO DEPT(DNAME, LOC)
VALUES('RESEARCH', 'HYDERABAD')
-A
program which will restrict the Delete operation if the Job of the person is
Manager.
ALTER TRIGGER EMP_DELETE_TRG
ON EMP AFTER DELETE
AS
BEGIN
DECLARE @JOB VARCHAR(50)
SELECT @JOB=JOB FROM DELETED
IF @JOB='MANAGER'
BEGIN
ROLLBACK
RAISERROR('CANNOT DELETE MANAGER FROM THE TABLE', 15,
1)
END
END
-To
test the following Trigger execute the following statement:
DELETE FROM EMP WHERE EMPNO=1002
A
Trigger which will restrict to update the Salary of the Employee if the New
Salary is less than the Old Salary.
CREATE TRIGGER EMP_UPDATE_TRG
ON EMP AFTER UPDATE
AS
BEGIN
DECLARE @OLDSAL MONEY
DECLARE @NEWSAL MONEY
SELECT @OLDSAL=SAL FROM DELETED
SELECT @NEWSAL=SAL FROM INSERTED
IF @OLDSAL > @NEWSAL
BEGIN
ROLLBACK
RAISERROR('NEW SAL CANNOT BE LESS THAN OLD SAL', 15,
1)
END
END
NESTED
TRIGGERS: Triggers can be nested to a maximum of 32 levels. If a trigger
changes a table on which there is another trigger, the second trigger is
activated and can then call a third trigger, and so on. To disable nested
triggers, set the nested triggers option of sp_configure to 0
(off). The default configuration allows for nested triggers.
-A Trigger which will fire when a record is inserted into the
Emp table which verifies whether the given Deptno is present in the Dept table
or not if not it will insert a record into it.
CREATE TRIGGER EMP_NESTED_TRG
ON EMP AFTER INSERT
AS
BEGIN
DECLARE @DEPTNO INT
SELECT @DEPTNO=DEPTNO FROM INSERTED
IF NOT EXISTS(SELECT * FROM DEPT WHERE DEPTNO=@DEPTNO)
INSERT INTO DEPT VALUES(@DEPTNO, NULL, NULL)
END
-A Trigger which will fire when a record is inserted into the
Dept table which verifies whether the given Deptno is present in the
DeptDetails table or not if not it will insert a record into it.
CREATE TRIGGER DEPT_NESTED_TRG
ON DEPT AFTER INSERT
AS
BEGIN
DECLARE @DEPTNO INT
DECLARE @DID INT
SELECT @DEPTNO=DEPTNO FROM INSERTED
IF NOT EXISTS(SELECT * FROM DEPTDETAILS WHERE
DEPTNO=@DEPTNO)
BEGIN
SELECT @DID=MAX(DID)+ 1 FROM DEPTDETAILS
INSERT INTO DEPTDETAILS VALUES(@DID, @DEPTNO, NULL)
END
END
-After creating the 2 Triggers if we try to insert a record
into the Emp table with a Deptno not present in the Dept table it will insert a
record into the Dept table which will internally check whether the Deptno is
present in the DeptDetails table or not and inserts a records into it if not
present.
Instead of Triggers on Complex Views which are not updatable:
CREATE VIEW
EMP_DEPT
AS
SELECT E.EMPNO,
E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC FROM EMP E INNER JOIN DEPT D ON
E.DEPTNO=D.DEPTNO
-After creating the view try to execute the following insert
which will fail because complex views are by default non updatable:
INSERT
INTO EMP_DEPT VALUES(1100, ‘RAJU’, 4500, 50, ‘IT’, ‘BANGLORE’)
-If
the above statement has to execute we need to define a Instead of trigger on
the view so that the View become updatable.
CREATE TRIGGER VIEW_INSERT_TRG
ON EMP_DEPT INSTEAD OF INSERT
AS
BEGIN
INSERT INTO EMP(EMPNO, ENAME, SAL, DEPTNO)
SELECT EMPNO, ENAME, SAL, DEPTNO FROM INSERTED
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
SELECT DEPTNO, DNAME, LOC FROM INSERTED
END
-In
the same way a trigger which will allow delete operations to be performed on
the View:
CREATE TRIGGER VIEW_DELETE_TRG
ON EMP_DEPT INSTEAD OF DELETE
AS
BEGIN
DECLARE @DEPTNO INT
DECLARE @COUNT INT
SELECT @DEPTNO=DEPTNO FROM DELETED
SELECT @COUNT=COUNT(*) FROM EMP WHERE DEPTNO=@DEPTNO
DELETE FROM EMP WHERE DEPTNO=@DEPTNO
IF @COUNT=1
DELETE FROM DEPT WHERE DEPTNO=@DEPTNO
END
SAMPLE TABLES
These are the samples tables that are used in the examples in
the above:
Dept Table with out Constraints:
Create Table
Dept(Deptno int, DName varchar(50), Loc varchar(50))
Dept Table with Constraints:
Create Table
Dept(Deptno int Constraint Deptno_Pk Primary Key, DName varchar(50), Loc
varchar(50))
Emp Table with out Constraints:
Create table
Emp(Empno int,Ename varchar(100),Job varchar(100),Mgr int,HireDate datetime,Sal
Money,Comm Money,Deptno int)
Emp Table with Constraints:
Create table
Emp(Empno int Constraint Empno_Pk Primary Key,Ename varchar(100),Job
varchar(100), Mgr int Constraint Mgr_Ref References Emp(Empno),HireDate
datetime,Sal Money Constraint Sal_Check Check (sal between 1500 and 7500),Comm
Money,Deptno int, Constraint Deptno_Ref Foreign Key (Deptno) References
Dept(Deptno))
DeptDetails Table with out Constraints:
Create table
DeptDetails (Did int, deptno int,
comments varchar(8000))
DeptDetails Table with Constraints:
Create table
DeptDetails (Did int primary key, deptno int references dept(deptno), comments
varchar(8000))
Salgrade Table:
Create Table
SalGrade(Grade int Constraint Grade_Pk Primary Key, LoSal Money, Hisal Money)
Data in Dept Table:
Insert into Dept values(10,
'Marketing', 'Mumbai')
Insert into Dept values(20, 'Sales', 'Chennai')
Insert into Dept values(30, 'Finance', 'Delhi')
Insert into Dept values(40, 'Production', 'Kolkota')
Data in Emp Table:
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1001, 'Scott', 'President', NULL, '01/01/78', 5000, NULL, 10)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1002, 'Clark', 'Manager',
1001, '01/01/78', 4000, NULL,
10)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1003, 'Smith', 'Manager',
1001, '01/01/78', 3500, 500, 20)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1004, 'Vijay', 'Manager',
1001, '01/01/78', 4000, NULL,
30)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1005, 'Ajay', 'Salesman',
1003, '02/04/79', 3000,
300, 20)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1006, 'Satish', 'Salesman',
1003, '02/08/78', 4000, 600, 20)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1007, 'Venkat', 'Salesman',
1003, '04/15/78', 3300, 0, 20)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1008, 'Vinod', 'Clerk',
1003, '01/15/78', 2400, NULL,
20)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1009, 'Suneel', 'Clerk',
1004, '05/12/83', 2000, NULL,
30)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1010, 'Srinivas', 'Analyst',
1004, '03/01/79', 3400, NULL, 30)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1011, 'Prakash', 'Analyst',
1004, '03/01/79', 3600, NULL, 30)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1012, 'Madan', 'Analyst',
1004, '01/09/81', 3100, NULL, 30)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1013, 'Ravi', 'Clerk',
1002, '01/06/78', 1800, NULL,
10)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1014, 'Raju', 'Clerk',
1005, '06/01/78', 2300, NULL,
20)
Insert into Emp (Empno, Ename, Job, Mgr, HireDate,
Sal, Comm, Deptno) Values(1015, 'Ramesh', 'Clerk',
1011, '08/22/78', 2500, NULL, 30)
Data
in DeptDetails Table:
Insert into DeptDetails values(1, 10, 'This department
is located in Mumbai and mainly involved in marketing')
Insert into DeptDetails values(2, 20, 'This department
is located in Chennai and mainly involved in Sales')
Insert into DeptDetails values(3, 30, 'This department
is located in Delhi and mainly involved in Finance')
Insert into DeptDetails values(4, 40, 'This department
is located in Kolkota and mainly involved in Production')
Data
in Salgrade Table:
Insert into SalGrade Values(1, 1300,
1800)
Insert into SalGrade values(2, 1800, 2700)
Insert into SalGrade values(3, 2700,
3500)
Insert into SalGrade values(4, 3500, 5000)
Insert into SalGrade values(5, 5000, 8000)
CODD RULES
For a system to qualify as a relational database management system (RDBMS),
that system must use its relational facilities (exclusively) to manage
the database.
Rule 1: The information rule:
All information in the database is to be represented in one and only one
way, namely by values in column positions within rows of tables.
Rule 2 : The guaranteed access rule:
All data must be accessible with no ambiguity. This rule is essentially a
restatement of the fundamental requirement for primary keys. It says that every
individual scalar value in the database must be logically addressable by
specifying the name of the containing table, the name of the containing column
and the primary key value of the containing row.
Rule 3: Systematic treatment of null values:
The DBMS must allow each field to remain null (or empty). Specifically, it
must support a representation of "missing information and inapplicable
information" that is systematic, distinct from all regular values (for
example, "distinct from zero or any other number," in the case of
numeric values), and independent of data type. It is also implied that such
representations must be manipulated by the DBMS in a systematic way.
Rule 4: Active online catalog based on the relational model:
The system must support an online, inline, relational catalog that is
accessible to authorized users by means of their regular query language. That
is, users must be able to access the database's structure (catalog) using the
same query language that they use to access the database's data.
Rule 5: The comprehensive data sublanguage rule:
The system must support at least one relational language that
(a) Has a linear syntax
(b) Can be used both interactively and within application programs,
(c) Supports data definition operations (including view definitions), data
manipulation operations (update as well as retrieval), security and integrity
constraints, and transaction management operations (begin, commit, and
rollback).
Rule 6: The view updating rule:
All views that are theoretically updatable must be updatable by the system.
Rule 7: High-level insert, update, and delete:
The system must support set-at-a-time insert, update, and delete operators.
This means that data can be retrieved from a relational database in sets
constructed of data from multiple rows and/or multiple tables. This rule states
that insert, update, and delete operations should be supported for any
retrievable set rather than just for a single row in a single table.
Rule 8: Physical data independence:
Changes to the physical level (how the data is stored, whether in arrays or
linked lists etc.) must not require a change to an application based on the
structure.
Rule 9: Logical data independence:
Changes to the logical level (tables, columns, rows, and so on) must not
require a change to an application based on the structure. Logical data
independence is more difficult to achieve than physical data independence.
Rule 10: Integrity independence:
Integrity constraints must be specified separately from application
programs and stored in the catalog. It must be possible to change such
constraints as and when appropriate without unnecessarily affecting existing
applications.
Rule 11: Distribution independence:
The distribution of portions of the database to various locations should be
invisible to users of the database. Existing applications should continue to
operate successfully :
(a) when a distributed version of the DBMS is first introduced; and
(b) when existing distributed data are redistributed around the system.
Rule 12: The nonsubversion rule:
If the system provides a low-level (record-at-a-time) interface, then that
interface cannot be used to subvert the system, for example, bypassing a
relational security or integrity constraint.
Rules of Data Normalization
1NF
|
Eliminate
Repeating Groups - Make a separate table for each set of related
attributes, and give each table a primary key.
|
2NF
|
Eliminate
Redundant Data - If an attribute depends on only part of a multi-valued
key, remove it to a separate table.
|
3NF
|
Eliminate
Columns Not Dependent On Key - If attributes do not contribute to a
description of the key, remove them to a separate table.
|
BCNF
|
Boyce-Codd
Normal Form - If there are non-trivial dependencies between candidate key
attributes, separate them out into distinct tables.
|
4NF
|
Isolate
Independent Multiple Relationships - No table may contain two or more 1:n
or n:m relationships that are not directly related.
|
5NF
|
Isolate
Semantically Related Multiple Relationships - There may be practical
constrains on information that justify separating logically related
many-to-many relationships.
|
ONF
|
Optimal
Normal Form - a model limited to only simple (elemental) facts, as
expressed in Object Role Model notation.
|
DKNF
|
Domain-Key
Normal Form - a model free from all modification anomalies.
|
1. Eliminate Repeating Groups
Moving the known databases into a
seperate table helps a lot. Separating the repeating groups of databases from
the member information results in first normal form. The MemberID in the
database table matches the primary key in the member table, providing a foreign
key for relating the two tables with a join operation. Now we can answer the
question by looking in the database table for "DB2" and getting the
list of members.
2. Eliminate Redundant Data
In the Database Table, the
primary key is made up of the MemberID and the DatabaseID. This makes sense for
other attributes like "Where Learned" and "Skill Level"
attributes, since they will be different for every member/database combination.
But the database name depends only on the DatabaseID. The same database name
will appear redundantly every time its associated ID appears in the Database
Table.
Suppose you want to reclassify a
database - give it a different DatabaseID. The change has to be made for every
member that lists that database! If you miss some, you'll have several members
with the same database under different IDs. This is an update anomaly.
Or suppose the last member
listing a particular database leaves the group. His records will be removed
from the system, and the database will not be stored anywhere! This is a delete
anomaly. To avoid these problems, we need second normal form.
To achieve this, separate the
attributes depending on both parts of the key from those depending only on the
DatabaseID. This results in two tables: "Database" which gives the
name for each DatabaseID, and "MemberDatabase" which lists the
databases for each member.
Now we can reclassify a database
in a single operation: look up the DatabaseID in the "Database" table
and change its name. The result will instantly be available throughout the
application.
The Member table satisfies first
normal form - it contains no repeating groups. It satisfies second normal form
- since it doesn't have a multivalued key. But the key is MemberID, and the
company name and location describe only a company, not a member. To achieve
third normal form, they must be moved into a separate table. Since they
describe a company, CompanyCode becomes the key of the new "Company"
table.
The motivation for this is the
same for second normal form: we want to avoid update and delete anomalies. For
example, suppose no members from the IBM were currently stored in the database.
With the previous design, there would be no record of its existence, even
though 20 past members were from IBM!
BCNF. Boyce-Codd Normal Form
Boyce-Codd Normal Form states
mathematically that:
A relation R is said to be in BCNF if whenever X -> A holds in R, and A is not in X, then X is a candidate key for R.
BCNF covers very specific situations where 3NF misses inter-dependencies between non-key (but candidate key) attributes. Typically, any relation that is in 3NF is also in BCNF. However, a 3NF relation won't be in BCNF if (a) there are multiple candidate keys, (b) the keys are composed of multiple attributes, and (c) there are common attributes between the keys.
A relation R is said to be in BCNF if whenever X -> A holds in R, and A is not in X, then X is a candidate key for R.
BCNF covers very specific situations where 3NF misses inter-dependencies between non-key (but candidate key) attributes. Typically, any relation that is in 3NF is also in BCNF. However, a 3NF relation won't be in BCNF if (a) there are multiple candidate keys, (b) the keys are composed of multiple attributes, and (c) there are common attributes between the keys.
Basically, a humorous way to
remember BCNF is that all functional dependencies are:
"The key, the whole key, and nothing but the key, so help me Codd."
4. Isolate Independent Multiple Relationships "The key, the whole key, and nothing but the key, so help me Codd."
This applies primarily to
key-only associative tables, and appears as a ternary relationship, but has incorrectly
merged 2 distinct, independent relationships.
The way this situation starts is
by a business request list the one shown below. This could be any 2 M:M
relationships from a single entity. For instance, a member could know many
software tools, and a software tool may be used by many members. Also, a member
could have recommended many books, and a book could be recommended by many
members.
NOTE! This is not to say
that ALL ternary associations are invalid. The above situation made it obvious
that Books and Software were independently linked to Members. If, however,
there were distinct links between all three, such that we would be stating that
"Bill recommends the ERWin Bible as a reference for ERWin", then
separating the relationship into two separate associations would be incorrect.
In that case, we would lose the distinct information about the 3-way
relationship.
5. Isolate Semantically Related Multiple
Relationships
This makes sense after the
discussion on Rule 4, and again we may be tempted to resolve the multiple M:M
relationships into a single association, which would now violate 5th normal
form. The ternary association looks identical to the one shown in the 4th
normal form example, and is also going to have trouble displaying the
information correctly. This time we would have even more trouble because we
can't show the relationships between books and software unless we have a member
to link to, or we have to add our favorite dummy member record to allow the
record in the association table.
The solution, as before, is to
ensure that all M:M relationships that are independent are resolved
independently, resulting in the model shown below. Now information about
members and books, members and software, and books and software are all stored
independently, even though they are all very much semantically related. It is
very tempting in many situations to combine the multiple M:M relationships
because they are so similar. Within complex business discussions, the lines can
become blurred and the correct solution not so obvious.
6. Optimal Normal Form
At this point, we have done all
we can with Entity-Relationship Diagrams (ERD). Most people will stop here
because this is usually pretty good. However, another modeling style called
Object Role Modeling (ORM) can display relationships that cannot be expressed
in ERD. Therefore there are more normal forms beyond 5th. With Optimal Normal
Form (OMF)
It is defined as a model limited to only simple (elemental) facts, as expressed in ORM.
7. Domain-Key Normal FormIt is defined as a model limited to only simple (elemental) facts, as expressed in ORM.
This level of normalization is
simply a model taken to the point where there are no opportunities for
modification anomalies.
· "if every constraint on the relation is
a logical consequence of the definition of keys and domains"
· Constraint "a rule governing static
values of attributes"
· Key "unique identifier of a tuple"
· Domain "description of an attribute’s
allowed values"
- A relation in DK/NF has no modification anomalies, and conversely.
- DK/NF is the ultimate normal form; there is no higher normal form related to modification anomalies
- Defn: A relation is in DK/NF if every constraint on the relation is a logical consequence of the definition of keys and domains.
- Constraint is any rule governing static values of attributes that is precise enough to be ascertained whether or not it is true
- E.g. edit rules, intra-relation and inter-relation constraints, functional and multi-valued dependencies.
- Not including constraints on changes in data values or time-dependent constraints.
- Key - the unique identifier of a tuple.
- Domain: physical and a logical description of an attributes allowed values.
- Physical description is the format of an attribute.
- Logical description is a further restriction of the values the domain is allowed
- Logical consequence: find a constraint on keys and/or domains which, if it is enforced, means that the desired constraint is also enforced.
- Bottom line on DK/NF: If every table has a single theme, then all functional dependencies will be logical consequences of keys. All data value constraints can them be expressed as domain constraints.
- Practical consequence: Since keys are enforced by the DBMS and domains are enforced by edit checks on data input, all modification anomalies can be avoided by just these two simple measures.
Download: Oracle DataBase 11g
No comments:
Post a Comment