Transcript sqlserver_presentation

Leading Software Technologies
Chennai
CONTENTS














Introduction
SQL Server Introduction
Data Definition Language
Data Manipulation Language
Data Control Language
Constraints
Functions
Joins
Sub Queries
Views & Indexes
Stored Procedures
Triggers
Cursors
User-defined Data types
INTRODUCTION
INTRODUCTION

What is Database?

Basic Database Concepts

Introduction to DBMS.

Data Model.

Introduction to RDBMS.

DBMS Vs RDBMS
What is a Database?
•
•
•
A structured collection of related data
An filing cabinet, an address book, a telephone
directory, a timetable, etc.
In Access, your Database is your collection of
related tables
What is a Database?
Data vs. Information
•
Data – a collection of facts made up of text, numbers and dates:
Menaka
•
50000
5/22/82
Information - the meaning given to data in the way it is
interpreted:
Menaka is a Programmer whose annual salary is $50,000 and
whose date of birth is May 22, 1982.
Basic Database Concepts

Field
– A single item of data
common to all records

Record
– A collection of data
about an individual item

Table
– A set of related records
Name: Rahul
Name: Rahul
College: SSNA
Tel: 9942131251
Name: Rahul
College: SSNA
Tel: 9942131251
Basic Database Concepts
Example of Table :
Fields
Records
Name
Qualification
Phone
College
Rahul
MSC
9942131251
SSNA
Harris
MCA
9840945849
SRM
Priya
BE
9834756906
MIT
Data Base Management System (DBMS)
A set of generalized system software for creating and
manipulating large databases, whose interfaces provide a broad
range of languages to aid all users
Application
DBMS
Database
Data Model
•
Database model is the process of organizing the data into
related record types.
•
Types of Data models:
 Hierarchical
 Network
 Relational
 object oriented model
Data Model
Hierarchical Database
Data is organized into a tree-like structure, implying
a single upward link in each record to describe the nesting. A
record type can be owned by only one owner.
Network Database
In network databases, a record type can have
multiple owners.
Data Model
Relational Database models
Relational databases do not link records together
physically, but the design of the records must provide a common
field to allow for matching.
Often, the fields used for matching are indexed in
order to speed up the process
Data Model
Object Oriented Database
An "object oriented database" can be employed
when hierarchical, network and relational structures are
too restrictive.
Object oriented databases can easily handle
many-to-many relationships.
Introduction to RDBMS
•
RDBMS is a Relational Data Base Management
System Relational DBMS.
•
This adds the additional condition that the system
supports a tabular structure for the data, with enforced
relationships between the tables.
DBMS are for smaller organizations with small amount of data,
where security of the data is not of major concern and RDBMS are designed
to take care of large amounts of data and also the security of this data.
DBMS Vs. RDBMS
DBMS
RDBMS
1.Set of data and tools to manage
those data. - Will not support
RELATION SHIP between data. - Ex :
- Foxpro data files and earlier Ms
Access.
1.Same as DBMS - Will Support
RELATION SHIP between Tables. Ex : - ORACLE,SQL 2000,DB 2 ...
2.In DBMS only one user can access
the same database, at the same time
2.In RDBMS many users
simultaneously access the same
database
3.No relationship between tables
3. The main advantage of an RDBMS
is that it checks for referential
integrity (relationship between
related records using Foreign Keys).
You can set the constraints in an
RDMBS such that when a paricular
record is changed, related records
are updated/deleted automatically.
SQL SERVER BASICS
SQL SEVER BASICS

Introduction

Data Type

Working with Query Analyzer

SQL Components
SQL
• SQL stands for Structured Query Language
• SQL allows to access a database
• SQL is an ANSI standard computer language
• SQL can execute queries against a database
• SQL can retrieve data from a database
SQL
Sql used for…..
• SQL can insert new records in a database
• SQL can delete records from a database
• SQL can update records in a database
• SQL is easy to learn
• SQL is a standard computer language for accessing and
manipulating databases.
SQL
• SQL is a Standard - BUT....
• SQL is an ANSI (American National Standards Institute)
standard computer language for accessing and
manipulating database systems.
• SQL statements are used to retrieve and update data in a
database.
•
SQL works with database programs like MS Access, DB2,
Informix, MS SQL Server, Oracle, Sybase, etc.
Data Types
Binary data types
Special data types
Numeric data types
Date and Time data types
Text and image data types
Unicode Character data types
Integer data types
Character data types
Monetary data types
User-Defined data types
Data Types
CHARACTER DATA TYPES
Character data types are used to store any combination of letters, symbols, and
numbers. Enclose character data with quotation marks, when enter it.There are
two character data types:
1) CHAR(N)
2) VARCHAR(N)
// n Specifies the Length
Char(n) data type
• Store up to 8000 bytes of fixed-length character data.
Varchar(n) data type
• Store up to 8000 bytes of variable-length character data.
• Variable-length means that character data can contain less than n bytes,
and the storage size will be the actual length of the data entered.
• Use varchar data type instead of char data type, when you expect null
values or a variation in data size.
Data Types
DATE AND TIME DATA TYPES
There are two datetime data types:
DATETIME
SMALLDATETIME
Datetime
It is stored in 8 bytes of two 4-byte integers: 4 bytes for the
number of days before or after the base date of January 1, 1900, and 4
bytes for the number of milliseconds after midnight.
Smalldatetime
It is stored in 4 bytes of two 2-byte integers: 2 bytes for the
number of days after
the base date of January 1, 1900, and 2 bytes
for the number of minutes after midnight.
Data Types
NUMERIC DATATYPES
•DECIMAL[(P[, S])]
// Storage Size10^38 - 1 through - 10^38 - 1. ]
•NUMERIC[(P[, S])]
P - is a precision, that specify the maximum total number of decimal digits
that can be stored, both to the left and to the right of the decimal point. The
maximum precision is 28 digits.
S - is a scale, that specify the maximum number of decimal digits that can
be stored to the right of the decimal point, and it must be less than or equal
to the precision.
Data Types
NUMERIC DATATYPES (Cont.)
•FLOAT(N)
•REAL
Float[(n)] datatype
It is stored in 8 bytes and is used to hold positive or negative
floating-point numbers.It can store positive values from 2.23E-308 to
1.79E308 and negative values from -2.23E-308 to -1.79E308.
Real datatype
It is stored in 4 bytes and is used as float datatype to hold positive or
negative floating-point numbers. It can store positive values from
1.18E-38 to 3.40E38 and negative values from -1.18E-38 to -3.40E38.
Data Types
INTEGER DATATYPES
There are four integer data types:
•TINYINT
•SMALLINT
•INT
•BIGINT
TINYINT : It is stored in 1 byte and is used to hold integer values from 0 through
255.
SMALLINT : It is stored in 2 bytes and is used to hold integer values from -32768
through 32,767.
INT : It is stored in 4 bytes and is used to hold integer values from -2147483648
through 2147483647.
BIGINT : It is stored in 8 bytes and is used to hold integer values from 9223372036854775808
through 9223372036854775807
Data Types
MONETARY DATATYPES
Monetary datatypes are usually used to store monetary values.
There are two monetary datatypes:
•MONEY
•SMALLMONEY
MONEY It is stored in 8 bytes and is used to hold monetary values from 922337203685477.5808 through 922337203685477.5807.
SMALLMONEY It is stored in 4 bytes and is used to hold monetary values
from - 214748.3648 through 214748.3647
Data Types
SPECIAL DATATYPES
•BIT
•SQL_VARIANT
•TIMESTAMP
•UNIQUEIDENTIFIER
BIT :
It is usually used for true/false or yes/no types of data, because it holds
either 1 or 0. All integer values other than 1 or 0 are always interpreted as 1.
One bit column stores in 1 byte, but multiple bit types in a table can be
collected into bytes. Bit columns cannot be NULL and cannot have indexes on them.
SQL_VARIANT : It is used to store values of various SQL Server supported data
types, except text,ntext,timestamp, and sql_variant. The maximum length of
sql_variant datatype is 8016 bytes.
Store in one column of type sql_variant the rows of different data types,
for example int, char, and varchar values.
Data Types
SPECIAL DATATYPES (Cont.)
TIMESTAMP : It is stored in 8 bytes as binary(8) datatype. The timestamp value
is automatically updated every time a row containing a timestamp column is
inserted or updated.
Timestamp value is a monotonically increasing counter whose values
will always be unique within a database and can be selected by queried global
variable @@DBTS.
UNIQUEIDENTIFIER : It is a GUID (globally unique identifier). A GUID is a 16byte binary number that is guaranteed to be unique in the world.This datatype is
usually used in replication or as primary key to unique identify rows in a table.
Get the new uniqueidentifier value by calling the NEWID function.
Note
You should use IDENTITY property instead of uniqueidentifier,
if global uniqueness is not necessary, because the uniqueidentifier
values are long and more slowly generated.
Data Types
TEXT AND IMAGE DATATYPES
Text and image data are stored on the Text/Image pages. There are three datatypes
in this category:
•
•
•
TEXT
NTEXT
IMAGE
TEXT : It is a variable-length datatype that can hold up to 2147483647 characters.
This datatype is used when you want to store the character values with the total
length more than 8000 bytes.
NTEXT : It is a variable-length unicode datatype that can hold up to 1073741823
characters. This datatype is used when you want to store the variable-length
unicode data with the total length more than 4000 bytes.
IMAGE : It is a variable-length datatype that can hold up to 2147483647
bytes of binary data.This datatype is used when you want to store the
binary values with the total length more than 8000 bytes. It is also used to
store pictures.
Data Types
UNICODE CHARACTER DATATYPES
A column with unicode character datatype can store all of the characters that are
defined in the various character sets, not only the characters from the particular
character set, which was chosen during SQL Server Setup.
Unicode datatypes take twice as much storage space as non-Unicode datatypes.
The unicode character data, as well as character data, can be used to store any
combination of letters, symbols, and numbers. Enclose unicode character data with
quotation marks, when enter it.
There are two unicode character datatypes:
NCHAR[(N)]
NVARCHAR[(N)]
Data Types
BINARY DATA TYPES
Binary data is similar to hexadecimal data and consists of the
characters 0 through 9 and A through F, in groups of two characters
each.Specify 0x before binary value when input it.
There are two binary datatypes:
•BINARY[(N)]
//Specify the maximum byte length with n.
•VARBINARY[(N)]
BINARY[(N)]

Store up to 8000 bytes of fixed-length binary data.
VARBINARY[(N)]
•
Store up to 8000 bytes of variable-length binary data.
•
Variable-length means that binary data can contain less than n bytes,
and the storage size will be the
actual length of the data entered.
•
Use varbinary datatype instead of binary datatype, when you expect
null values or a variation in data size.
Working with Query Analyzer
To start SQL SERVER
Start
Programs
MicrosoftSQLSERVER
Enterprise Manager
Query Analyzer
Query analyzer
Working with Query Analyzer
Query Analyzer
Working with Query Analyzer
Server name
To log
Working with Query Analyzer
Create a new database named as
Ebidding
Working with Query Analyzer
Select the Query and Press F5 to
run the query
Working with Query Analyzer
Use command
• The USE command selects a database to use for future
processing.
Syntax
Use <databasename>
SQL Components
SQL
DDL
RDBMS Structure
Create/Delete DBs
Create/Delete Tables
Alter Tables
DML
Data I/O
Create Record
Read Record
Update Record
Delete Record
DCL
DBA Activities
Create Users
Delete Users
Grant privileges
Implement Access
Security
DATA DEFINITION LANGUAGE
DATA DEFINITION LANGUAGE

CREATE

ALETR

DROP
Data Definition Language (DDL)
The Data Definition Language (DDL) part of SQL permits
database tables to be created or deleted.
•
The most important DDL statements in SQL are:

CREATE TABLE - creates a new database table

ALTER TABLE - alters (changes) a database table

DROP TABLE - deletes a database table
DDL - CREATE
CREATE Table using Constraints
Syntax :
Create table <table name >(
column name1 data type ,
Table 1: Employee
column name2 data type
Eno
Ename
Dateofbirth
Salary
…….
)
Example :
Create table Employee (
Eno varchar(10),
Empname varchar(100),
Dateofbirth varchar(100),
Salary Numeric )
varchar(10)
varchar(100)
varchar(100)
int
DDL - ALTER
Modifies a table definition by altering, adding, or dropping columns and
constraints.
Table 1 : Employee
Syntax 1: Alter a table to add a new column
Eno
ALTER TABLE <table name >
EmpName
Dateofbirth
Salary
ADD column name1 data type
Example : Add “Age” column to Employee table
ALTER TABLE Employee
ADD age INT
Table 1: Employee
Eno
Ename
Dateofbirth
Salary
Age
Table 1 : Altered table
Employee
Eno
varchar(10)
varchar(100)
varchar(100)
int
int
EmpName
Dateofbirth
Salary
Age
DDL - ALTER
Syntax 1: Modify Existing Column
ALTER TABLE <table name >
ALTER column name1 data type
Example : Modify “DateofBirth” data type to
DATETIME
ALTER TABLE Employee
Table 1 : Employee
Table 1: Employee
Eno
Ename
Dateofbirth
Salary
varchar(10)
varchar(100)
varchar(100)
int
Table 1 : Altered table Employee
ALTER COLUMN DateofBirth DateTime
Table 1: Employee
Eno
Ename
varchar(10)
varchar(100)
Dateofbirth
DateTime
Salary
int
DDL - ALTER
Table 1 : Employee
Syntax : Alter table to drop column
ALTER TABLE <Table Name>
Eno
EmpName
Dateofbirth
Salary
Age
DROP COLUMN <Columnname>
Example: Remove “Age” from Employee Table
Table 1 : Altered table Employee
ALTER TABLE Employee
DROP COLUMN Age
Eno
EmpName
Dateofbirth
Salary
DDL - DROP
Removes a table definition and all data, indexes, triggers, constraints, and
permission specifications for that table.
Syntax :
DROP
TABLE <table name >
Example :
DROP TABLE Employee
Drop should destroy the values and structure of the table
DATA MANIPULATION LANGUAGE
DATA MANIPULATION LANGUAGE

INSERT

UPDATE

DELETE

SELECT
DML - Data Manipulation Language
• Data manipulation language (DML) statements
access and manipulate data in existing schema
objects.
DML Statements includes :




SELECT - extracts data from a database table
UPDATE - updates data in a database table
DELETE - deletes data from a database table
INSERT INTO - inserts new data into a database table
DML – INSERT
INSERT Statement is used to insert data into
Database table
Syntax : Simple Insert
INSERT INTO TableName
VALUES(Fieldvalue1,Field Value2 …)
Table 1: Employee
Eno
Ename
Dateofbirth
Salary
varchar(10)
varchar(100)
Datetime
int
Example :
INSERT INTO Employee VALUES
(‘LST/1001’,’Menaga’,’05/22/1982’,12000)
Note :
Varchar,Char and DateTime values
should be given with single quotes. (Eg) ‘Menaga’
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
DML – INSERT
Insert data with fewer values than columns
Syntax :
INSERT INTO
TableName(Field1,Field2…)
VALUES(Fieldvalue1,Field Value2 …)
Table 1: Employee
Eno
Ename
Dateofbirth
Salary
varchar(10)
varchar(100)
Datetime
int
Example :
INSERT INTO Employee(Eno,Ename)
VALUES(‘LST/1001’,’Menaga’)
Note : Insert All not null values.
Eno
EmpName
LST/1001
Menaga
Dateofbirth
Salary
DML – INSERT
Insert data from Other table
Syntax :
INSERT INTO
TableName(Field1,Field2…)
SELECT(Fieldvalue1,Field Value2 …)
Table 1: Employee
Eno
Ename
Dateofbirth
Salary
varchar(10)
varchar(100)
Datetime
int
Example :
INSERT INTO Employee(Eno,Ename)
SELECT Eno,Ename FROM OldEmp
Note : Insert All not null values.
Eno
EmpName
LST/1001
Menaga
Dateofbirth
Salary
DML – INSERT
Insert data from Some otherFile System(Eg.
Notepad,XML)
Syntax :
BULK INSERT database_name.dbo.table_name
FROM 'data_file'
WITH
( FIELDTERMINATOR ='field_terminator‘,
Example File Format is :
LST/1001 * Menaga * 05/22/1982 * 10000>
LST/1002 * Kavitha * 07/10/1982 * 12000>
ROWTERMINATOR = 'row_terminator' )
Example :
BULK INSERT Master.dbo.Employee
FROM ‘C://empdetails.txt’'
WITH
( FIELDTERMINATOR=‘*‘,
ROWTERMINATOR = ‘ > ' )
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
05/22/1982
10000
LST/1002
Kavitha
07/10/1982
12000
DML - UPDATE
UPDATE Statement is used to update data into
Database table
Syntax :
UPDATE <tablename> SET columname=value
WHERE conditon
1) Simple
Update
UPDATE Employee Set salary=20000
Table 1 : Employee
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
LST/1002
Kavitha
10/07/1982
15000
LST/1003
Shakthi
12/05/1985
12000
LST/1004
Karthik
15/09/1980
20000
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
20000
LST/1002
Kavitha
10/07/1982
20000
LST/1003
Shakthi
12/05/1985
20000
LST/1004
Karthik
15/09/1980
20000
2).More than 2 values with Condition
UPDATE employee SET
salary=50000,Empname=‘Preetha’
WHERE Eno=‘LST/1003’
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
20000
LST/1002
Kavitha
10/07/1982
20000
LST/1003
Preetha
12/05/1985
50000
LST/1004
Meena
15/09/1980
20000
DML - DELETE
DELETE Statement is used to delete data from
Database table
Table 1 : Employee
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
DELETE FROM <tablename>
LST/1002
Kavitha
10/07/1982
15000
WHERE conditon
LST/1003
Shakthi
12/05/1985
12000
LST/1004
Karthik
15/09/1980
20000
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
LST/1002
Kavitha
10/07/1982
15000
LST/1003
Shakthi
12/05/1985
12000
Syntax :
1) Delete the Employee with employeeno‘LST/1004’
DELETE FROM Employee
WHERE eno=‘LST/1004’
2.) Delete all records in Employee
DELETE FROM Employee
Delete values only not
structure of the table
DML - SELECT
SELECT is Used to retrieve the data from
the database Table
Syntax:
SELECT * From <tablename>
Table 1 : Employee
WHERE Condition
SELECT Field1,Field2.. FROM TableName
WHERE Condition
1) Display Employee details
SELECT * FROM Employee
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
LST/1002
Kavitha
10/07/1982
15000
LST/1003
Shakthi
12/05/1985
12000
LST/1004
Karthik
15/09/1980
20000
2) Display all the details of Employee no
‘LST/1001’
SELECT * FROM Employee WHERE
Eno=‘LST/10001’
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
DML - SELECT
SELECT USING ORDER BY
Arrange the Rows by Ascending or Descending
Syntax :
SELECT * FROM tablename
ORDER BY Fieldname ASC/DESC
* By default is is Ascending
Example :
SELECT * FROM Employee
ORDER BY Empname
SELECT * FROM Employee
ORDER BY Empname DESC
Table 1 : Employee
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
LST/1002
Kavitha
10/07/1982
15000
LST/1003
Sakthi
12/05/1985
12000
LST/1004
Karthik
15/09/1980
20000
ORDER BY ASC
Eno
EmpName
Dateofbirth
Salary
LST/1004
Karthik
15/09/1980
20000
LST/1002
Kavitha
10/07/1982
15000
LST/1001
Menaga
22/05/1982
12000
LST/1003
Sakthi
12/05/1985
12000
ORDER BY DESC
Eno
EmpName
Dateofbirth
Salary
LST/1003
Sakthi
12/05/1985
12000
LST/1001
Menaga
22/05/1982
12000
LST/1002
Kavitha
10/07/1982
15000
LST/1004
Karthik
15/09/1980
20000
DML - SELECT
SELECT DISTINCT
The DISTINCT keyword eliminates
duplicate rows from the results of a SELECT statement.
If DISTINCT is not specified, all rows are returned,
including duplicates.
Syntax :
SELECT DISTINCT( Field Name) FROM TableName
Table 1 : Employee
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
LST/1002
Kavitha
10/07/1982
15000
LST/1003
Karthik
12/05/1985
12000
LST/1004
Karthik
15/09/1980
20000
With out Distinct
EmpName
Menaga
Example :
SELECT Empname FROM Employee
Kavitha
Karthik
Karthik
Using Distinct
SELECT DISTINCT( Empname) FROM Employee
EmpName
Menaga
Kavitha
Karthik
DML - SELECT
SELECT USING LOGICAL OPERATORS
The Where Conditions may includes the
following logical operatos: AND , OR , NOT
AND - Both Conditions should be True.
OR - Both or Any one of the Condition should be True
NOT – If Condition is True then return False
If Condition is False then return True
Example :
SELECT * FROM Employee
WHERE Eno=‘LST/1001’ AND Salary=12000
SELECT * FROM TableName
WHERE Eno=‘LST/1001’ OR Salary=12000
SELECT * FROM TableName
WHERE NOT (Salary=12000)
Table 1 : Employee
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
LST/1002
Kavitha
10/07/1982
15000
LST/1003
Karthik
12/05/1985
12000
LST/1004
Karthik
15/09/1980
20000
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
LST/1003
Karthik
12/05/1985
12000
Eno
EmpName
Dateofbirth
Salary
LST/1002
Kavitha
10/07/1982
15000
LST/1004
Karthik
15/09/1980
20000
AND
OR
Not
DML - SELECT
SELECT USING BETWEEN
BETWEEN Specifies a range to test.
Syntax :
SELECT * FROM tablename
Table 1 : Employee
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
LST/1002
Kavitha
10/07/1982
15000
LST/1003
Sakthi
12/05/1985
12000
LST/1004
Karthik
15/09/1980
20000
WHERE Fieldname BETWEEN value1 AND value2
BETWEEN
Example :
Eno
EmpName
Dateofbirth
Salary
SELECT * FROM Employee
LST/1002
Kavitha
10/07/1982
15000
WHERE Salary BETWEEN 15000 AND 20000
LST/1004
Karthik
15/09/1980
20000
DML - SELECT
SELECT USING LIKE
Table 1 : Employee
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
LST/1002
Kavitha
10/07/1982
15000
LST/1003
Sakthi
12/05/1985
12000
LST/1004
Karthik
15/09/1980
20000
Eno
EmpName
Dateofbirth
Salary
SELECT * FROM Employee
LST/1002
Kavitha
10/07/1982
15000
WHERE EmpName LIKE ‘ K %’
LST/1004
Karthik
15/09/1980
20000
Determines whether or not a given character
string matches a specified pattern.
Syntax :
SELECT * FROM tablename
WHERE Fieldname LIKE ‘%Characterstring%’
% - Indicated any string before and after
Example : Select Employee details whose name
starts with ‘K’
LIKE
DML - SELECT
SELECT USING GROUP BY
Table 1 : Employee
Eno
EmpName
Dateofbirth
Salary
LST/1001
Menaga
22/05/1982
12000
Syntax :
LST/1002
Kavitha
10/07/1982
15000
SELECT [ALL | DISTINCT]
columnname1 [,columnname2] FROM
tablename1 [,tablename2] [WHERE
condition] [ and|or condition...]
[GROUP BY column-list] [HAVING
"conditions] [ORDER BY "column-list"
[ASC | DESC] ]
LST/1003
Sakthi
12/05/1985
12000
LST/1004
Karthik
15/09/1980
20000
•The GROUP BY clause is used to group the
output of the WHERE clause.
Using WHERE
15000
20000
Example :
SELECT SUM(salary) FROM Employee where
salary >12000 GROUP BY Eno
Example :
SELECT EmpName FROM EMPLOYEE
GROUP BY SALARY HAVING EMPNAME LIKE ‘k%'
Using Having
Kavitha
Karthik
DML - SELECT
UNION
•The UNION command is used to select related information
from two tables, much like the JOIN command. However, when
using the UNION command all selected columns need to be of
the same data type.
•With UNION, only distinct values are selected.
Syntax:
SQL SELECT Statement 1 UNION SQL SELECT Statement 2
UNION
1)Combining Two Tables
Table 1 : Employees_Chennai
SELECT E_Name FROM Employees_Chennai
UNION
SELECT E_Name FROM Employees_Banglore
Name
Employee_Id
Name
100
Sachin
101
Dravid
102
Ganguly
Sachin
Dravid
Ganguly
Table 2 :Employees_Banglore
Kumble
Prasad
Employee_Id
Name
Agarkar
100
Saachin
101
Kumble
102
Prasad
103
Agarkar
This command cannot be used to list all employees
in Chennai and Banglore. In the example above we
have two employees with equal names, and only one
of them is listed. The UNION command only selects
distinct values.
DML - SELECT
UNION ALL
The UNION ALL command is equal to the UNION command,
except that UNION ALL selects all values
Syntax
SQL Statement 1 UNION ALL SQL Statement 2
UNION ALL
1)Combining Two Tables
SELECT E_Name FROM Employees_Chennai
UNION ALL
SELECT E_Name FROM mployees_Banglore
Name
Table 1 : Employees_Chennai
Employee_Id
Name
100
Sachin
101
Dravid
102
Ganguly
Sachin
Sachin
Dravid
Table 2 :Employees_Banglore
Kumble
Prasad
Employee_Id
Name
Agarkar
100
Sachin
101
Kumble
102
Prasad
103
Agarkar
This command can be used to list all employees in
Chennai and Banglore. In the example above we
have two employees with equal names, and all of
them is listed.
DATA CONTROL LANGUAGE
Data Control Language
• Data Control Language is the segment of the SQL language
that allows you to work with user privileges for objects in
the database.
•
DCL uses the following two SQL commands to work with
objects in the database:
 GRANT: Gives authority for a user or group to access or
update a table, view, or procedure.
 REVOKE: Removes authority that has been previously
granted to a user or a group.
 DENY : Deny Permission for a user or group to access or
update a table, view, or procedure.
CONSTRAINTS
CONSTRAINTS

PRIMARY KEY

FOREIGN KEY

CHECK

DEFAULT

NULL
CONSTRAINTS
• Constraints define rules regarding the values allowed in
columns and are the standard mechanism for enforcing
integrity.Using constraints is preferred to using triggers,
rules, and defaults.
Classes of Constraints
1.Primary Key
2.Foreign Key
3.Unique
4.Check
5.Default
6.Not null
CONSTRAINTS
1 ) PRIMARY KEY
It is a constraint that identify the column or set of columns whose
values uniquely identify a row in a table.

No two rows in a table can have the same primary key value.

You cannot enter a NULL for any column in a primary key.
NULL is a special value in databases that represents an unknown
value, which is distinct from a blank or 0 value.

2 ) FOREIGN KEY
It is a constraint that identify the relationships between tables.A
foreign key in one table points to a candidate key in another table.
3 ) CHECK
It is a constraint that enforce domain integrity by limiting the
values that can be placed in a column. range are entered for the key.
CONSTRAINTS
4 ) DEFAULT
It is a Constraint that sets the default value which is
allowed for the column if value is not given
5 ) NOT NULL
It specifies that the column does not accept NULL values.
 UNIQUE
It is a constraint that enforce the uniqueness of the
values in a set of columns. No two rows in the table are allowed
to have the same values for the columns in a UNIQUE
constraint. It accepts one null value
1.PRIMARY KEY CONSTRAINT
Create table table name (column name data type
prmary key
create table product (pcode int primary key ,pname
varchar(100))
Pcode
Pname
100
Rin
101
Surf
102
Ariel
103
Power
.
Create table tablename (Column name data type
foreign key references primarykey
tablename(columnname)
2.FOREIGN KEY CONSTRAINT
Table 1 : Product
Pcode
Pname
100
Rin
101
Surf
102
Ariel
103
Power
Table 2 : Order
Pcode
Orderid
100
Ord100
101
Ord101
Pcode
Orderid
101
Ord102
100
Ord100
102
Ord103
101
Ord101
101
Ord102
102
Ord103
create table orders (pcode int foreignkey references
product(pcode),orderid varchar(100))
insert into orders values(104,'ord105'
Note:Pcode 104 is not belongs to product
)
INSERT statement conflicted with COLUMN
FOREIGN KEY constraint
'FK__orders__pcode__76A18A26'. The
conflict occurred in database 'master', table
'product', column 'pcode'.
The statement has been terminated.
3.CHECK CONSTRAINT
Alter table product add price numeric check(price
>250)
Product
Pcode
Pname
Price
int
varchar(100)
numeric check (price >250)
Pcode
Pname
Price
100
Rin
300
101
Surf
null
102
Ariel
null
103
Power
null
4.NOT NULL CONSTRAINT
Table 1 : Product
Pcode
Pname
100
Rin
101
Surf
102
Ariel
103
Power
Table 2 : Ord
create table ord (pcode int not null,prodname
varchar(100) not null)
Ord
Pcode
Pname
int not null
varchar(100) not null
Pcode
Productname
100
Pears
101
Hamam
101
Liril
102
Cinthol
UNIQUE CONSTRAINT
Create table <tblname>(columnname unique)
create table orde (pcode int unique,prodname
varchar(100) )
Pcode
Orde
Pcode
Pname
Table 1 : Orde
Productname
int unique
varchar(100)
Pcode
Productname
100
Rin
101
Surf
102
Ariel
103
Power
Table 1 : Orde1
5.DEFAULT CONSTRAINT
Create table <tblname>(column name data type
default value)
create table ord1 (pcode int unique,prodname
varchar(100) default ‘surf’)
insert into ord1(pcode) values(105)
Pcode
Productname
100
Rin
101
Surf
102
Ariel
105
Surf
Sample Constraints contain all classes
Create table empnew(eno int PRIMARY KEY,ename varchar(100)
unique,salary numeric CHECK(salary >10000),designation
varchar(30) DEFAULT 'programmer', dob datetime NOT NULL)
Table :Empnew
Eno
Ename
Salary
Designation
Dob
int
varchar(100)
numeric
varchar(30)
datetime
eno
ename
salary
designaton
dob
100
Meena
25000
Designer
1982/05/02
101
Geetha
30000
programmer
1982/04/05
102
Kavitha
50000
Teamleader
1982/06/07
IDENTITY
Create table <tblname>(columnname data type
identity)
Table 1 : Order
Pcode
Prodname
create table order (pcode int
identity(1000,1),prodname varchar(100) )
Order
Pcode
Prodname
int identity(1000,1)
varchar(100)
insert into order(prodname) values('pears')
Table 1 : Order
insert into order(prodname) values('Margo')
Select * from order
Pcode
Prodname
1000
pears
1001
Margo
FUNCTIONS
FUNCTIONS

AGGREGATE FUNCTIONS

STRING FUNCTIONS

MATHEMATICAL FUNCTIONS

DATE FUNCTIONS
FUNCTIONS
AGGREGATE FUNCTIONS
•
Its used produce the result set of the select
statements in an effective way as like calculating and
manipulating the values.
Types
1. Count
2.
Sum
3.
Avg
4.
Max
5.
Min
FUNCTIONS
AGGREGATE FUNCTIONS
COUNT
Its used to count the total
number of records in the table
Ex:
Table 1:Employees
Eno
Ename
Salary
100
Praveen
50000
101
Preetha
30000
102
Aravinth
25000
103
Priya
30000
SELECT COUNT(eno) FROM employees
Output:
In the employees table its display the
total number of employees
• 4
FUNCTIONS
AGGREGATE FUNCTIONS
SUM
Its used to sum the total number
of records in the table
Ex:
Table 1:Employees
Eno
Ename
Salary
100
Praveen
50000
101
Preetha
30000
102
Aravinth
25000
103
Priya
30000
SELECT SUM(salary)FROM employees
Output:
In the employees table its display the
sum of employee salary employees
• 135000
FUNCTIONS
AGGREGATE FUNCTIONS
Avg
Table 1:Employees
Its used to calculate the average
value for the given records in the
table
Eno
Ename
Salary
100
Praveen
50000
101
Preetha
30000
102
Aravinth
25000
103
Priya
30000
Ex:
SELECT AVG(salary)FROM employees
Output:
In the employees table its display the
average salary of the employees
• 33750
FUNCTIONS
AGGREGATE FUNCTIONS
MAX
Its used display the maximum values of
records in the table
Ex:
Table 1:Employees
Eno
Ename
Salary
100
Praveen
50000
101
Preetha
30000
102
Aravinth
25000
103
Priya
30000
SELECT MAX(salary)FROM employees
Output:
In the employees table its display
maximum salary of the employee
• 50000
FUNCTIONS
AGGREGATE FUNCTIONS
MIN
Its used display the minimum values of
records in the table
Ex:
Table 1:Employees
Eno
Ename
Salary
100
Praveen
50000
101
Preetha
30000
102
Aravinth
25000
103
Priya
30000
SELECT MIN(salary)FROM employees
Output:
In the employees table its display
minimum salary of the employee
• 25000
FUNCTIONS
STRING FUNCTIONS
These scalar functions perform an operation on a string input
value and return a string or numeric value
ASCII
Returns the ASCII code value of the
leftmost character of a character
expression.
Syntax
Ex:
Select ASCII(‘A’)
ASCII ( character_expression )
Arguments
character_expression
Is an expression of the type char or
varchar.
Return Types : int
Output:
65
FUNCTIONS
STRING FUNCTIONS
CHAR
A string function that converts an int
ASCII code to a character.
Syntax
CHAR ( integer_expression )
Arguments
integer_expression
Is an integer from 0 through 255. NULL
is returned if the integer expression is
not in this range.
Return Types
char(1)
Ex:
select CHAR(65)
Output:
A
FUNCTIONS
STRING FUNCTIONS
LEN
Returns the number of characters,
rather than the number of bytes, of
the given string expression,
excluding trailing blanks.
Ex:
select len('praveen')
Syntax
LEN ( string_expression )
Arguments
string_expression
Is the string expression to be
evaluated.
Return Types : int
Output:
7
FUNCTIONS
STRING FUNCTIONS
SUBSTRING
Returns part of a character, binary, text, or
image expression
Syntax
SUBSTRING ( expression , start , length )
Arguments
expression
Is a character string, binary string, text, image, a
column, or an expression that includes a column.
Do not use expressions that include aggregate
functions.
start
Is an integer that specifies where the substring
begins.
length
Is an integer that specifies the length of the
substring (the number of characters or bytes to
return).
Ex:
select SUBSTRING('preetha',1,3)
Output:
pre
FUNCTIONS
STRING FUNCTIONS
REPLACE
Replaces all occurrences of the second given
string expression in the first string
expression with a third expression.
Syntax
Ex:
REPLACE ( 'string_expression1' ,
'string_expression2' , 'string_expression3'
)
SELECT
REPLACE('abcdefghicde','cde','xxx')
Arguments
'string_expression1‘ : Is the string expression to be
searched. string_expression1 can be of character or
binary data.
'string_expression2‘ : Is the string expression to try
to find. string_expression2 can be of character or
binary data.
'string_expression3‘ : Is the replacement string
expression string_expression3 can be of character or
binary data.
Output:
abxxxfghixxx
This example replaces the string
cde in abcdefghi with xxx.
FUNCTIONS
STRING FUNCTIONS
UPPER
Returns a character expression with
lowercase character data converted
to uppercase.
Syntax
UPPER ( character_expression )
Arguments
character_expression
Is an expression of character data.
character_expression can be a
constant, variable, or column of
either character or binary data.
Return Types
varchar
Ex:
Select UPPER(‘priya’)
Output:
PRIYA
FUNCTIONS
STRING FUNCTIONS
LOWER
Returns a character expression after
converting uppercase character data to
lowercase.
Syntax
LOWER ( character_expression )
Arguments
character_expression
Is an expression of character or binary
data. character_expression can be a
constant, variable, or column.
character_expression must be of a data
type that is implicitly convertible to
varchar. Otherwise, use CAST to explicitly
convert character_expression.
Return Types
varchar
Ex:
Select LOWER(‘PRIYA’)
Output:
priya
FUNCTIONS
MATHEMATICAL FUNCTIONS
These scalar functions perform a calculation, usually based
on input values provided as arguments, and return a
numeric value.
Arithmetic functions, such as ABS, CEILING, DEGREES,
FLOOR, POWER, RADIANS, and SIGN, return a value having
the same data type as the input value. Trigonometric and
other functions, including EXP, LOG, LOG10, SQUARE, and
SQRT, cast their input values to float and return a float
value.
FUNCTIONS
MATHEMATICAL FUNCTIONS
ABS
Returns the absolute, positive value of
the given numeric expression.
Syntax
ABS ( numeric_expression )
Arguments
numeric_expression
Is an expression of the exact numeric
or approximate numeric data type
category, except for the bit data type.
Return Types
Returns the same type as
numeric_expression
Examples
This example shows the effect of
the ABS function on three
different numbers.
SELECT ABS(-1.0), ABS(0.0),
ABS(1.0)
Output:
1.0, .0 ,1.0
FUNCTIONS
MATHEMATICAL FUNCTIONS
CEILING
Returns the smallest integer greater
than, or equal to, the given numeric
expression.
Syntax
CEILING ( numeric_expression )
Arguments
numeric_expression
Is an expression of the exact numeric or
approximate numeric data type category,
except for the bit data type.
Return Types
Returns the same type as
numeric_expression.
Examples
This example shows positive numeric,
negative, and zero values with the
CEILING function.
SELECT CEILING(123.45),
CEILING(-123.45), CEILING(0.0)
Output
124.00, -123.00, 0.00
FUNCTIONS
MATHEMATICAL FUNCTIONS
FLOOR
Returns the largest integer less than or equal to
the given numeric expression.
Syntax
FLOOR ( numeric_expression )
Arguments
numeric_expression
Is an expression of the exact numeric or
approximate numeric data type category, except
for the bit data type.
Return Types
Returns the same type as numeric_expression.
Examples:
This example shows positive numeric,
negative numeric values with the
FLOOR function.
SELECT FLOOR(123.45), FLOOR(123.45), FLOOR(123.45)
Output:
123 ,-124 ,123.0000
FUNCTIONS
MATHEMATICAL FUNCTIONS
POWER
Returns the value of the given expression to the
specified power.
Syntax
POWER ( numeric_expression , y )
Arguments
numeric_expression
Is an expression of the exact numeric or
approximate numeric data type category, except
for the bit data type.
y
Is the power to which to raise numeric_expression.
y can be an expression of the exact numeric or
approximate numeric data type category, except
for the bit data type.
Return Types
Same as numeric_expression.
Ex:
select POWER(2,2)
Output:
4
FUNCTIONS
MATHEMATICAL FUNCTIONS
ROUND
Returns a numeric expression, rounded to
the specified length or precision.
Syntax
Examples
This example shows two
expressions illustrating that with
the ROUND function the last digit
is always an estimate
SELECT ROUND(123.9994, 3),
ROUND(123.9995, 3)
ROUND ( numeric_expression , length [ ,
function ] )
Arguments
numeric_expression
Is an expression of the exact numeric or
approximate numeric data type category,
except for the bit data type.
length
Is the precision to which numeric_expression
is to be rounded.
Output:
123.9990,
124.0000
FUNCTIONS
MATHEMATICAL FUNCTIONS
SQUARE :
Returns the square of the given expression.
Syntax : SQUARE ( float_expression )
Arguments
float_expression : Is an expression of type float.
Ex:
select square(4)
Output:
16.0
Return Types : float
SQRT
Returns the square root of the given expression.
Syntax : SQRT ( float_expression )
Arguments
float_expression : Is an expression of type float.
Return Types : float
Ex:
select sqrt(4)
Output:
2.0
FUNCTIONS
DATE AND TIME FUNCTIONS
These scalar functions perform an operation on a date and
time input value and return a string, numeric, or date and
time value.

GETDATE

DAY

MONTH

YEAR

DATEADD

DATEDIFF
FUNCTIONS
DATE AND TIME FUNCTIONS
GETDATE
Ex:
Returns the current system date and time in
the SQL Server standard internal format for
datetime values.
Select GETDATE( )
Syntax : GETDATE ( )
Return Types : datetime
Remarks
Date functions can be used in the SELECT
statement select list or in the WHERE clause of
a query.
Output:
2007-05-26 18:00:56.153
FUNCTIONS
DATE AND TIME FUNCTIONS
DAY
Returns an integer representing the
day datepart of the specified date.
Syntax
Example:
Select DAY(’05/12/2007’)
: DAY ( date )
Arguments :
Date : Is an expression of type
datetime or smalldatetime.
Return Type : int
Output:
12
FUNCTIONS
DATE AND TIME FUNCTIONS
MONTH
Returns an integer that represents the
month part of a specified date.
Syntax : MONTH ( date )
Example:
Select MONTH(’05/12/2007’)
Arguments
Date : Is an expression returning a
datetime or smalldatetime value, or a
character string in a date format. Use
the datetime data type only for dates
after January 1, 1753.
Return Types : int
Output:
5
FUNCTIONS
DATE AND TIME FUNCTIONS
YEAR
Returns an integer that represents
the year part of a specified date.
Syntax : YEAR ( date )
Example:
Select YEAR(’05/12/2007’)
Arguments
Date : An expression of type
datetime or smalldatetime.
Return Types : int
Output:
2007
FUNCTIONS
DATE AND TIME FUNCTIONS
DATEADD
Returns a new datetime value based on adding an
interval to the specified date.
Syntax : DATEADD ( datepart , number, date )
number
Is the value used to increment datepart. If you
specify a value that is not an integer, the
fractional part of the value is discarded. For
example, if you specify day for datepart and1.75
for number, date is incremented by 1.
Select
DATEADD(DAY,10,’03/12/2007’)
Output:
2007/05/22
Select
DATEADD(MONTH,04,’03/12/2007’)
Output:
2007/07/22
Select
DATEADD(YEAR,02,’03/12/2007’)
Output:
2009/05/22
FUNCTIONS
DATE AND TIME FUNCTIONS
DATEDIFF
Returns the number of date and time
boundaries crossed between two specified dates.
Syntax
DATEDIFF ( datepart , startdate , enddate )
Arguments
datepart
Is the parameter that specifies on which part of
the date to calculate the difference
startdate
Is the beginning date for the calculation.
startdate is an expression that returns a
datetime or smalldatetime value, or a
character string in a date format.
enddate
Is the ending date for the calculation. enddate is
an expression that returns a datetime or
smalldatetime value, or a character string in a
date format.
Select
DATEDIFF(DAY,'07/05/1979','11/08/1983')
Output:
1587
Select
DATEDIFF(MONTH,'07/05/1979','11/08/1983')
Output:
52
Select
DATEDIFF(YEAR,'07/05/1979','11/08/1983')
Output:
4
JOINS
JOINS

INNER JOIN

OUTER JOIN

CROSS JOIN
JOINS
Sometimes
we have to select data from two or more tables to make the
result complete. Here we have to perform a join.
Joins can be categorized as:

INNER JOINS

OUTER JOINS

CROSS JOINS
JOINS
Table 1 : Employee
Display all the details of Employee no ‘LST/1001’
Eno
EmpName
Dateofbirth
Salary
SELECT E.Eno,E.Ename,E.DateofBirth,D.
DeptName,E.Salary FROM Employee E,Department
D,EMpDept ED
LST/1001
Menaga
22/05/1982
12000
LST/1002
Kavitha
10/07/1982
15000
LST/1003
Shakthi
12/05/1985
12000
WHERE E.Eno=ED.Eno AND ED.Deptno=D.Deptno AND
AND E.Eno=‘LST/10001’
LST/1004
Karthik
15/09/1980
20000
Table 2 : Department
Eno
EmpName
Dateofbirth
DeptName
Salary
LST/1001
Menaga
22/05/1982
Accounts
12000
DeptNo
DeptName
Shortname
100
Human Resourse
HR
101
Accounts
AC
102
Reaserch and Development
RD
Table 3 : EmpDept
DeptNo
ENO
100
LST/1003
101
LST/1001
101
LST/1004
102
LST/1002
Simple Join
1)Joining Two Tables
select *from Employees,Orders
Table 1 : Employees
Employee_ID
Name
Prod_ID
Product
Employee_ID
Employee_Id
Name
100
Sachin
1000
Bat
100
100
Sachin
101
Dravid
101
Dravid
1000
Bat
100
102
Ganguly
102
Ganguly
1000
Bat
100
100
Sachin
1003
Ball
100
Table 2 :Orders
101
Dravid
1003
Ball
100
Prod_Id
Product
Employee_Id
102
Ganguly
1003
Ball
100
1000
Bat
100
100
Sachin
1005
Glouse
101
1003
Ball
100
1005
Glouse
101
101
Darvid
1005
Glouse
101
102
Ganguly
1005
Glouse
101
JOINS
INNER JOINS
An inner join is a join in which the values in the columns being joined
are compared using a comparison operator.

Inner
joins (the typical join operation, which uses some comparison
operator like = or <>).
These
include equi- joins and natural joins.
Inner joins use a comparison operator to match rows from two tables
based on the values in common columns from each table.

Inner joins return rows only when there is at least one row from both
tables that matches the join condition. Inner joins eliminate the rows
that do not match with a row from the other table

JOINS
INNER JOINS
1)Joining Two Tables with specified condition
SELECT Employees.Name, Orders.Product
FROM Employees INNER JOIN Orders ON
Employees.Employee_ID=Orders.Employee_ID
Name
Product
Sachin
Bat
Table 1 : Employees
Employee_Id
Name
100
Sachin
101
Dravid
102
Ganguly
Table 2 :Orders
Sachin
Dravid
Ball
Glouse
The INNER JOIN returns all rows from both tables where
there is a match. If there are rows in Employees that do
not have matches in Orders, those rows will not be
listed.
Prod_Id
Product
Employee_Id
1000
Bat
100
1003
Ball
100
1005
Glouse
101
JOINS
OUTER JOINS
Outer
joins can be a left, a right, or full outer join. Outer joins are specified
with one of the following sets of keywords when they are specified in the
FROM clause:
LEFT JOIN or LEFT OUTER JOIN
The
result set of a left outer join includes all the rows from the left table
specified in the LEFT OUTER clause, not just the ones in which the joined
columns match.
When
a row in the left table has no matching rows in the right table, the
associated result set row contains null values for all select list columns
coming from the right table.
LEFT OUTER JOIN
1)Joining Two Tables with specified condition
Table 1 : Employees
SELECT Employees.Name, Orders.Product
FROM Employees
Employee_Id
Name
LEFT JOIN Orders
100
Sachin
101
Dravid
102
Ganguly
ON Employees.Employee_ID=Orders.Employee_ID
Name
Product
Sachin
Bat
Table 2 :Orders
Sachin
Ball
Prod_Id
Product
Employee_Id
Dravid
Glouse
1000
Bat
100
Ganguly
Null
1003
Ball
100
1005
Glouse
101
The LEFT JOIN returns all the rows from the first table
(Employees), even if there are no matches in the second
table (Orders). If there are rows in Employees that do not
have matches in Orders, those rows also will be listed.
JOINS
OUTER JOINS
RIGHT OUTER JOIN OR RIGHT JOIN
A right
All
outer join is the reverse of a left outer join.
rows from the right table are returned.
Null values are returned for the left table any time a right table
row has no matching row in the left table.

RIGHT OUTER JOIN
1)Joining Two Tables
Table 1 : Employees
SELECT Employees.Name, Orders.Product FROM Employees
RIGHT JOIN Orders ON
Employees.Employee_ID=Orders.Employee_ID
Employee_Id
Name
100
Sachin
101
Dravid
102
Ganguly
Name
Product
Sachin
Bat
Sachin
Ball
Table 2 :Orders
Dravid
Glouse
Prod_Id
Product
Employee_Id
1000
Bat
100
1003
Ball
100
1005
Glouse
101
The RIGHT JOIN returns all the rows from the second table
(Orders), even if there are no matches in the first table
(Employees). If there had been any rows in Orders that did
not have matches in Employees, those rows also would
have been listed.
JOINS
OUTER JOINS
FULL OUTER JOIN
A full outer join returns all rows in both the left and right
tables.

Any time a row has no match in the other table, the select list
columns from the other table contain null values.

When there is a match between the tables, the entire result set
row contains data values from the base tables.

FULL OUTER JOIN
1)Joining Two Tables
Table 1 : Employees
SELECT Employees.Name, Orders.Product
FROM Employees
FULL Outer JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
Name
Product
Sachin
Bat
Sachin
Ball
Dravid
Glouse
Ganguly
Null
Employee_Id
Name
100
Sachin
101
Dravid
102
Ganguly
Table 2 :Orders
Prod_Id
Product
Employee_Id
1000
Bat
100
1003
Ball
100
1005
Glouse
101
JOINS
CROSS JOINS
Cross joins return all rows from the left table, each row
from the left table is combined with all rows from the right table.


Cross joins are also called Cartesian products.
CROSS JOIN
1)Joining Two Tables with Cross Join
select *from Employees cross join Orders
Table 1 : Employees
Employee_ID
Name
Prod_ID
Product
Employee_ID
Employee_Id
Name
100
Sachin
1000
Bat
100
100
Sachin
101
Dravid
102
Ganguly
101
Dravid
1000
Bat
100
102
Ganguly
1000
Bat
100
100
Sachin
1003
Ball
100
Table 2 :Orders
101
Dravid
1003
Ball
100
Prod_Id
Product
Employee_Id
102
Ganguly
1003
Ball
100
1000
Bat
100
1003
Ball
100
1005
Glouse
101
100
Sachin
1005
Glouse
101
101
Darvid
1005
Glouse
101
102
Ganguly
1005
Glouse
101
SUB QUERIES
SUB QUERIES


A SELECT statement nested inside another SELECT
statement is commonly referred to as a SUBQUERY
It is also known as a nested query.

A sub Query is used to return data that will be used in
the main query as a condition to further restrict the data to be
retrieved.

Sub Queries are used with the SELECT, INSERT,
UPDATE, and DELETE statements.

A sub query can be used in some cases in place of a
join operation by indirectly linking data between the tables
based on one or more conditions.
SUB QUERIES
Table:Stud
Syntax
Select * from <tblname>
where condition( select * from
<tblname>)
Example
select * from stud where sno
in(select sno from payment)
Sno
100
101
102
103
Sno
Sname
100
preetha
101
praveenkumar
102
Ramkumar
103
Reethu
104
Sathyakar
105
Susi
Table:Payment
Sno
Amount
Balance
100
7000
2000
101
7000
0
102
7000
1000
103
7000
3000
Result
SUB QUERIES
Two types of sub queries:
1.Simple sub query
2.Correlated Sub query
1.Simple sub query
The results of one query as the input for another is
called as simple sub query
2.Correlated Sub query
It depends upon the outer table.Each time the
inner query evaluated whenever the system finds the new row
in outer query.
SUB QUERIES
Operations involved in sub query

Basic comparisons:Sub Queries can be introduced
with one of the comparison operators (=, < >, >, > =, <, ! >, ! <,
or < =).

Quantified Comparisons(ANY,ALL and SOME)

IN,NOT IN Keyword

EXISTS ,NOT EXISTS Keyword
SUB QUERIES
Table:Stud
1.Sub Queries using operators
select * from stud where sno
=(select sno from payment where
balance=0)
Sno
Sname
100
preetha
101
praveenkumar
102
Ramkumar
103
Reethu
104
Sathyakar
105
Susi
Table:Payment
Result
Sno
101
Sname
praveenkumar
Sno
Amount
Balance
100
7000
2000
101
7000
0
102
7000
1000
103
7000
3000
SUB QUERIES
Table:Stud
2.Sub Queries using IN
Sno
Sname
Using IN the inner query
execute and return value.Based on the
inner query the outer query return
values
100
Preetha
101
Praveenkumar
102
Ramkumar
103
Reethu
104
Sathyakar
syntax
Select stmt IN ( subquery |
105
Susi
expression [ ,...n ] )
select * from stud where sno in(select
sno from payment where
balance>1000)
Result
Sno
Sname
100
preetha
103
Reethu
Table:Payment
Sno
Amount
Balance
100
7000
2000
101
7000
0
102
7000
1000
103
7000
3000
SUB QUERIES
Table:Stud
3.Sub Queries using NOT IN
Using NOT IN the inner
query execute and return value.The
outer query return values which is not
belong to Sub query
syntax
Select stmt NOT IN ( subquery |
expression [ ,...n ] )
select * from stud where sno NOT IN
(select sno from payment where
balance>1000)
Result
Sno
Sname
101
Praveenkumar
102
Ramkumar
Sno
Sname
100
Preetha
101
Praveenku
mar
102
Ramkumar
103
Reethu
104
Sathyakar
105
Susi
Table:Payment
Sno
Amount
Balance
100
7000
2000
101
7000
0
102
7000
1000
103
7000
3000
SUB QUERIES
4.Sub Queries using EXISTS
Table:Stud
Exists function check’s inner
query if it contains at least one row.
It returns a value of TRUE or FALSE.
syntax
Select stmt where EXSTS ( subquery |
expression [ ,...n ] )
select * from stud where exists (select *
from payment where
stud.sno=payment.sno)
Sno
Sname
100
Preetha
101
Sno
Sname
100
Preetha
101
Praveenku
mar
102
Ramkumar
103
Reethu
104
Sathyakar
105
Susi
Table:Payment
Sno
Amount
Balance
100
7000
2000
Praveenkumar
101
7000
0
102
Ramkumar
102
7000
1000
103
Reethu
103
7000
3000
SUB QUERIES
Table:Stud
5.Sub Queries using NOT EXISTS
It works just opposite to EXISTS
syntax
)
select * from stud where not exists
(select * from payment where
stud.sno=payment.sno)
Sno
Sname
104
Sathyakar
105
Sus
Sname
100
Preetha
101
Praveenkumar
102
Ramkumar
103
Reethu
104
Sathyakar
105
Susi
Table:Payment
Select stmt where NOT EXSTS (
subquery | expression [ ,...n ]
Sno
Sno
Amount
Balance
100
7000
2000
101
7000
0
102
7000
1000
103
7000
3000
SUB QUERIES
Table:Stud
5.Sub Queries using ANY
syntax
Select stmt where ANY (
subquery | expression [ ,...n
]
)
SELECT sname FROM stud WHERE
sno = any(SELECT sno FROM payment
where balance=0)
Sno
Sname
101
Praveenkumar
102
Ramkumar
Sno
Sname
100
Preetha
101
Praveenkumar
102
Ramkumar
103
Reethu
104
Sathyakar
105
Susi
Table:Payment
Sno
Amount
Balance
100
7000
2000
101
7000
0
102
7000
0
103
7000
3000
SUB QUERIES
Table:Stud
7.Sub Queries using ALL
syntax
Select stmt where ALL
(subquery | expression [ ,...n
]
)
SELECT * FROM stud WHERE sno <>
ALL (SELECT sno FROM payment)
Sno
Sname
104
Sathyakar
105
Susi
Sno
Sname
100
Preetha
101
Praveenku
mar
102
Ramkumar
103
Reethu
104
Sathyakar
105
Susi
Table:Payment
Sno
Amount
Balance
100
7000
2000
101
7000
0
102
7000
1000
103
7000
3000
SUB QUERIES
Table:Stud
8.UPDATE statement in Sub Queries
update stud set sno =sno+1
where sno IN(select sno from
payment where balance>1000)
Sno
Sname
100
Preetha
101
Praveenkumar
102
Ramkumar
103
Reethu
104
Sathyakar
105
Susi
Result
Table:Payment
Sno
Sname
101
Preetha
101
Praveenkumar
Sno
Amount
Balance
102
Ramkumar
100
7000
2000
104
Reethu
101
7000
0
104
Sathyakar
102
7000
1000
105
Susi
103
7000
3000
SUB QUERIES
Table:Stud
9.DELETE statement in Sub Queries
DELETE stud WHERE sno IN
(SELECT sno from payment where
balance=0)
Sno
Sname
100
Preetha
101
Praveen
102
Ramkumar
103
Reethu
104
Sathyakar
105
Susi
Result
Table:Payment
Sno
Sname
100
Preetha
103
Reethu
Sno
Amount
Balance
104
Sathyakar
100
7000
2000
105
Susi
101
7000
0
102
7000
0
103
7000
3000
VIEWS AND INDEXES
VIEWS
A view is a virtual table based on the result-set of a SELECT
statement
What is View?
 A view contains rows and columns, just like a real table. The
fields in a view are fields from one or more real tables in the
database
 We can add SQL functions, WHERE, and JOIN statements to
a view and present the data as if the data were coming from a
single table.
 The database design and structure will NOT be affected by
the functions, where, or join statements in a view
VIEWS
Syntax :
CREATE VIEW view_name AS SELECT column_name(s)
FROM table_name WHERE condition
The database does not store the view data! The database engine
recreates the data, using the view's SELECT statement, every time
a user queries a view
Using Views
•A view could be used from inside a query, a stored procedure, or
from inside another view. By adding functions, joins, etc., to a
view, it allows you to present exactly the data you want to the user
VIEWS
1)Creating Temporary table using view
Create view v1 as select *from
employees
Select *from v1
Employee_Id
Name
100
Sachin
101
Dravid
102
Ganguly
Now the employee information should be
temporarily stored to view v1
Table 1 : Employees
Employee_Id
Name
100
Sachin
101
Dravid
102
Ganguly
VIEWS
1)Update The view
Table 1 : Employees
update v1 set Name=Sourvav where
Employee_id=102
Employee_Id
Name
100
Sachin
101
Dravid
102
Sourvav
While creating view for single table it
should be update but creating view
from more than one table shouldn’t
updated
Employee_Id
Name
100
Sachin
101
Dravid
102
Ganguly
VIEWS
Creating view from more than one table
Create view v2 as select
Employees.Employee_Id,Employees.Name
,Product.Products from employees
Select *from v2
Employee_Id
Name
Table 1 : Employees
Employee_Id
Name
100
Sachin
101
Dravid
102
Ganguly
Products
Table 2 : Product
100
Sachin
Bat
101
Dravid
Ball
102
Ganguly
Glouse
Prod_Id
Products
Employee_
Id
1000
Bat
100
1003
Ball
100
1005
Glouse
101
INDEXES
• Indexes help us retrieve data from tables quicker.
• Indexes in SQL Server are similar to the indexes in books.
They help SQL Server retrieve the data quicker.
Indexes are of two types. Clustered indexes and nonclustered indexes.
INDEXES
CREATE INDEX
• We can creates an index for table or view.
• Only the table or view owner can create indexes on that
table.
• The owner of a table or view can create an index at any
time, whether or not there is data in the table. Indexes can
be created on tables or views in another database by
specifying a qualified database name.
INDEXES
•
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME"
(COLUMN_NAME)
•
Let's assume that we have the following table,
•
TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
•
and we want to create an index on the column
Last_Name, we would type in,
INDEXES
• CREATE INDEX IDX_CUSTOMER_LAST_NAME
on CUSTOMER (Last_Name)
• If we want to create an index on both City and Country, we
would type in,
• CREATE INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)
STORED PROCEDURES
STORED PROCEDURES

A Stored Procedures is a group of Transact-SQL
statements compiled into a single execution plan
A precompiled collection of Transact-SQL
statements stored under a name and processed as a unit.

SQL Server supplies stored procedures for
managing SQL Server and displaying information about
databases and users.

SQL Server-supplied stored procedures are called
system stored procedures.

STORED PROCEDURES
Procedures return data in four ways:
Output
parameters, which can return either data (such as an
integer or character value) or a cursor variable (cursors are result
sets that can be retrieved one row at a time).
Return
codes, which are always an integer value.
A
result set for each SELECT statement contained in the stored
procedure or any other stored procedures called by the stored
procedure.
A
global cursor that can be referenced outside the stored
procedure.
STORED PROCEDURES
Characteristics
A
Precompiled transact Sql statements that
stored under a single name.
Compiled
Stored
once and executed more times
batch of Sql statements
Advantages:
It
allows modular programming
Security
Reduce Network Traffic
Compilation speed is much faster than statements
STORED PROCEDURES
CREATE PROCEDURE
Creates
a stored procedure, which is a saved collection of
Transact-SQL statements that can take and return usersupplied parameters.
Syntax
Create Procedure Procedure_name(@parameter data_type,…)
As
Begin
--------------------------
SQL Statements
-------------------------End
STORED PROCEDURES
Arguments
procedure_name
The name of the new stored procedure. Procedure names must
conform to the rules for identifiers and must be unique within the
database and its owner.
Rules for Regular Identifiers:
•The
first character must be one of the following:
A letter ( a - z or A – Z)
The underscore (_),
•Subsequent
characters can be:
Letters
Decimal numbers
•The
identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
•Embedded
spaces or special characters are not allowed.
STORED PROCEDURES
@parameter
One or more parameters can be declared in a CREATE PROCEDURE statement.
The value of each declared parameter must be supplied by the user when the procedure
is executed (unless a default for the parameter is defined).
A stored procedure can have a maximum of 2,100 parameters.
Specify a parameter name using an at sign (@) as the first character.
 The parameter name must conform to the rules for identifiers.
Parameters are local to the procedure; the same parameter names can be used in other
procedures.
data_type
It is an attribute that specifies the type of data (integer, character, money, and so on)
that the parameter can hold.
The cursor data type can be used only on OUTPUT parameters.
There is no limit on the maximum number of output parameters that can be of cursor
data type.
STORED PROCEDURES
OUTPUT
Indicates that the parameter is a return parameter.
The value of this option can be returned to EXEC[UTE].
Use OUTPUT parameters to return information to the calling
procedure.
Text, ntext, and image parameters can be used as OUTPUT
parameters.
STORED PROCEDURES
EXECUTE
Executes a scalar-valued, user-defined function, a system procedure,
a user-defined stored procedures, or an extended stored procedure.
Also supports the execution of a character string within a TransactSQL batch. St
Syntax
EXEC Procedure_name <values>
STORED PROCEDURES
Create Procedure
Table 1:Emp
INSERT:
Create Procedure emp_insert(
Eno
Ename
Eno
Ename
104
Praveen
@eno int,
@ename Varchar(10)) As
Begin
Insert into emp values(@eno,@ename)
End
Exec emp_insert 104,’Praveen’
STORED PROCEDURES
Update Procedure
Table 1:Emp
Update:
Create Procedure emp_Update(
@eno int,
Eno
Ename
104
Praveen
105
Kumar
@ename Varchar(10)) As
Begin
Update emp set ename=@ename where
eno=@eno
End
Exec emp_Update 105,’Naveen’
Eno
Ename
104
Praveen
105
Naveen
STORED PROCEDURES
Delete Procedure
Table 1:Emp
Update:
Create Procedure emp_Delete(@eno int)
As
Eno
Ename
104
Praveen
105
Naveen
106
Kumar
Begin
Delete from emp where eno=@eno
End
Exec emp_Delete 106
Eno
Ename
104
Praveen
105
Naveen
STORED PROCEDURES
Select Procedure
Table 1:Emp
Select:
Create Procedure emp_Select(@eno int)
Eno
Ename
As
104
Praveen
Begin
105
Kumar
Select * from emp where eno=@eno
End
Eno
Ename
104
Praveen
As
Eno
Ename
Begin
104
Praveen
Select * from emp
105
Kumar
Exec emp_Selects 104
Select:
Create Procedure emp_Select
End
Exec emp_Selects
STORED PROCEDURES
ALTER PROCEDURE
Alters a previously created procedure, created by executing the CREATE
PROCEDURE statement, without changing permissions and without
affecting any dependent stored procedures or triggers.
ALTER PROCEDURE is used to redefine the stored procedure.
Syntax:
Alter procedure <procedure name>
As
----Sql Statements
----
STORED PROCEDURES
Parameters
Parameters are used to exchange data between stored procedures and
the application or tool that called the stored procedure:
Input
Parameters
Output
Parameters
Input parameters allow the caller to pass a data value to the stored
procedure.
Output parameters allow the stored procedure to pass a data value or
a cursor variable back to the caller.
Every stored procedure returns an integer return code to the caller. If
the stored procedure does not explicitly set a value for the return code,
the return code is 0.
STORED PROCEDURES
Example
The following stored procedure shows the use of an input
parameters, an output parameters, and a return code:
-- Create a procedure that takes one input
-- and returns one output parameters and a return code.
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
@MaxQuantity INT OUTPUT
AS
SELECT FirstName, LastName, Title FROM Employees WHERE
EmployeeID = @EmployeeIDParm
SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]
STORED PROCEDURES
Find ‘n’th maximum salary using Output parameter
create procedure nmax(@n int,@max
numeric output)
Table 1:Emp
as
select @max=max(salary) from emp
Eno
Salary
while(@n>1)
100
4000
begin
101
7000
select @max=max(salary) from emp
where salary<@max
102
8000
103
6000
set @n=@n-1
end
Return @max
Execution Part:
declare @m numeric
Output : (Second Maximum)
exec nmax 2,@max=@m output
Maximum Value Is:7000
print ‘Maximum Value Is:’+@m
STORED PROCEDURES
DROP PROCEDURE
Removes
one or more stored procedures or groups from the
current database.
Syntax:
DROP PROCEDURE procedure_name
TRIGGERS
TRIGGERS
A trigger is a special type of stored procedure that is not
called directly by a user.
When the trigger is created, it is defined to execute when
a specific type of data modification is made against a specific
table or column.
A trigger is a database object that is attached to a table.
In many aspects it is similar to a stored procedure.

As a matter of fact, triggers are often referred to as a
"special kind of stored procedure."

The main difference between a trigger and a stored procedure
is that the former is attached to a table and is only fired when
an INSERT, UPDATE or DELETE occurs. You specify the
modification action(s) that fire the trigger when it is created.
TRIGGERS
Syntax:
Create trigger <trigger name> on <table name>
For insert/update/delete
As
Begin
----------------------Sql statements--
---------------------End
TRIGGERS
Checking salary condition using Insert Trigger
Create Trigger
create trigger sa on sals
for insert as
begin
declare @salary numeric
select @salary=salary from inserted
if(@salary<1000)
begin
rollback transaction
1.Table : Sals
Eno
Salary
100
5000
print 'Salary should be >1000'
End
end
Inserting values
insert into sals
values(100,5000)
Inserting salary as <1000
insert into sals values(100,5000)
Output
Salary should be >1000
TRIGGERS
Create table
create table sample(eno int,m1 int,m2
int,tot int)
Create trigger
1.The Trigger is used to insert the
values in table then the total
should be automatically updated
create trigger s on sample
for insert
as
update sample set tot=m1+m2
1.Table:sample
Eno
M1
M2
tot
TRIGGERS
Inserting values in table
insert into
sample(eno,m1,m2)values(101,90,90)
1.Table : Sample
Eno
M1
M2
tot
Inserting another value
insert into
sample(eno,m1,m2)values(102,80,80)
2.Table : Sample
Select the values from the table
Select *from sample
Here the total should be
automatically updated using
trigger
Eno
M1
M2
tot
101
90
90
180
102
80
80
160
TRIGGERS
Checking salary condition using Update Trigger
Create Trigger
1.Table : Sals
create trigger sa on sals
Eno
Salary
100
5000
for update as
begin
declare @salary numeric
select @salary=salary from inserted
if(@salary<1000)
begin
rollback transaction
print ‘ Cannot Update Salary should be >1000'
End
end
Updating salary as <1000
Update sals set salary=500 where eno=100
Output
Cannot Update Salary should be >1000'
TRIGGERS
1.Table:Employees
Delete Trigger
Transferring one table value to
other table using trigger
create trigger ds on employees
for delete as
Eno
Salary
100
5000
101
6000
102
8000
begin
insert into oldemp select * from deleted
2.Table: OldEmp
Eno
end
3.Table :OldEmp
Eno
Salary
100
5000
101
6000
102
8000
Salary
CURSORS
CURSORS

A select statement returns a complete result set
which contains all the rows that are qualified in the
select statement .

Some applications needs one row or a block of
row at a time.At that time we need cursor

Cursor is a logical extension to result set that
fetches single row at a time
 Definition:

A data type for variables or stored
procedure OUTPUT parameters that contain a reference
to a cursor. Any variables created with the cursor data
type are null able.
CURSORS

Allow positioning of a specific row

It retrieves one row or a block of row at current position

Supporting data modification to rows at current position

Used in script,stored procedures and triggers
CURSORS
Steps in Cursors
 The steps followed in cursor
1.DECLARE
2.OPEN
3.FETCH
4.CLOSE
5.DEALLOCATE
CURSORS
1.DECLARE
Is used to declare the cursor
• Syntax
DECLARE cursor_name CURSOR
FOR select_statement
• cursor may be declared as
LOCAL | GLOBAL
FORWARD_ONLY | SCROLL
STATIC | KEYSET | DYNAMIC
| FAST_FORWARD
2.OPEN
Used to open the cursor
•Syntax
OPEN cursor_name
CURSORS
3.Fetching and Scrolling
•
•
The operation to retrieve a row from a cursor is called a fetch. These
are the fetch options:
FETCH FIRST Fetches the first row in the cursor.
•
FETCH NEXT Fetches the row after the last row fetched.
•
FETCH PRIOR Fetches the row before the last row fetched.
•
FETCH LAST Fetches the last row in the cursor.
•
FETCH ABSOLUTE n Fetches the nth row from the first row in the
cursor if n is a positive integer. If n is a negative integer, the row n rows
before the end of the cursor is fetched. If n is 0, no rows are fetched.
•
FETCH RELATIVE n Fetches the row n rows from the last row fetched. If
n is positive, the row n rows after the last row fetched is fetched.
CURSORS
Fetch Status
Returns the status of the last cursor FETCH statement issued
against any cursor currently opened by the connection.
Return
value
Description
0
FETCH statement was successful.
-1
FETCH statement failed or the
row was beyond the result set.
Row fetched is missing.
-2
CURSORS
4.CLOSE
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.
Syntax
CLOSE cursor_name
5.DEALLOCATE
Removes a cursor reference.
Syntax
DEALLOCATE cursor_name
CURSORS
Example for Cursor
DECLARE c1 CURSOR FOR
SELECT ename FROM emp
OPEN c1
FETCH NEXT FROM c1
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM c1
END
CLOSE c1
DEALLOCATE c1
Note: cursor fetch single row at
a time
// -- Check @@FETCH_STATUS
to see if there are any more rows
to fetch.
Table:Emp
Result
CURSORS
TYPES OF FETCHING
1.Use FETCH in a simple cursor
2.Use FETCH to store values in variables
3.Declare a SCROLL cursor and use the other FETCH options
CURSORS
TYPES OF FETCHING
1. Use FETCH in a simple cursor
This example declares a simple
Cursor for the rows in employee
table
DECLARE c1 CURSOR FOR
SELECT * FROM Emp
OPEN c1
FETCH NEXT FROM c1
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM c1
END
CLOSE c1
DEALLOCATE c1
RESULT
CURSORS
2.Use FETCH to store values in variables
•
Table:Emp
Here the output of the FETCH statements is
stored in local variables. The PRINT
statement combines the variables into a
single string and returns .
DECLARE @e varchar(40)
DECLARE c CURSOR FOR
SELECT ename FROM emp WHERE ename
LIKE 'p%'
OPEN c
RESULT
FETCH NEXT FROM c into @e
WHILE @@FETCH_STATUS = 0
BEGIN
pinky
PRINT @e
praveenkumar
FETCH NEXT FROM c into @e
preetha
END
CLOSE c
DEALLOCATE c
CURSORS
3. Declare a SCROLL cursor and use the
other FETCH options
•
This example creates a SCROLL cursor to
allow full scrolling capabilities through the
LAST, PRIOR, RELATIVE, and ABSOLUTE
options.
DECLARE c SCROLL CURSOR FOR
SELECT ename FROM emp
Orderby ename
FETCH LAST FROM c
FETCH PRIOR FROM c
FETCH FIRST FROM c
FETCH NEXT FROM c
FETCH ABSOLUTE 2 FROM c
FETCH RELATIVE 3 FROM c
FETCH RELATIVE -2 FROM c
CLOSE c
DEALLOCATE c
RESULT
CURSORS
Cursor used in update
DECLARE c1 CURSOR FOR
Select * from emp
OPEN c1
FETCH NEXT FROM c1
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH c1
update emp set eno=eno+1 where
current of c1
END
CLOSE c1
DEALLOCATE c1
Table :Emp
RESULT
CURSORS
Cursor used in delete
DECLARE c1 cursor for
Select * from emp where eno=101
OPENc1
FETCH c1
While (@@fetch_status=0)
Begin
FETCH c1
Delete from emp where current of
c1
End
CLOSE c1
DEALLOCATE c1
Table:Emp
Duplicate record deleted
CURSORS
Cursor for delete duplicate records
Create procedure noduplicate as
DECLARE @e int
DECLARE c1 cursor for
Select * from emp where eno=@e
OPEN c1
FETCH c1
While (@@fetch_status=0)
Begin
FETCH c1
Delete from emp where current of
c1
End
CLOSE c1
FETCH next from c1 into @e
DEALLOCATE c1
Table:Emp
Duplicate records deleted
Note:Duplicate records are not
mentioned cursor fetches each row
and delete duplicate wherever it
occurred
USER DEFINED DATA TYPES
USER DEFINED DATA TYPES
RULE
A database object that is bound to columns or user-defined data types, and
specifies which data values are acceptable in a column. CHECK constraints
provide the same functionality and are preferred because they are in the
SQL-92 standard.
A column or user-defined data type can have only one rule bound to it.
However, a column can have both a rule and one or more check constraints
associated with it. When this is true, all restrictions are evaluated.
USER DEFINED DATA TYPES
Syntax
CREATE RULE rule AS condition_expression
Arguments
rule
Is the name of the new rule. Rule names must conform to the rules for
identifiers. Specifying the rule owner name is optional.
condition_expression
Is the condition(s) defining the rule. A rule can be any expression valid in a
WHERE clause and can include such elements as arithmetic operators,
relational operators, and predicates (for example, IN, LIKE, BETWEEN).
A rule can be created only in the current database. After creating a rule,
execute sp_bindrule to bind the rule to a column or to a user-defined data
type.
Examples
CREATE RULE range_rule AS @range >= $1000
AND @range < $20000
USER DEFINED DATA TYPES
User-defined data types are based on the system data types in SQL Server.
User-defined data types can be used when several tables must store the same
type of data in a column and you must ensure that these columns have exactly
the same data type, length, and null ability.
 For example, a user-defined data type called postal_code could be created
based on the char data type.
When a user-defined data type is created, you must supply these parameters:
Name
System data type upon which the new data type is based
Syntax
sp_addtype [ @typename = ] type,[ , [ @nulltype = ] 'null_type' ]
USER DEFINED DATA TYPES
Arguments
[@typename =] type
Is the name of the user-defined data type. Data type names must follow the rules for
identifiers and must be unique in each database.
[@nulltype =] 'null_type‘
Indicates the way the user-defined data type handles null values. null_type is
varchar(8), with a default of NULL, and must be enclosed in single quotation marks
('NULL', 'NOT NULL', or 'NONULL'). If null_type is not explicitly defined by
sp_addtype, it is set to the current default nullability.
Examples with Not Null
EXEC sp_addtype gend, 'VARCHAR(6)',
'NOT NULL'
Examples With Null
EXEC sp_addtype birthday, datetime,
'NULL'
Used in tables
Create table emp(sno int,gender gend)
Used in Tables
Create table emp(sno int,dob birthday)
USER DEFINED DATA TYPES
Drop user-Defined Type
Syntax:
Examples
sp_droptype [ @typename = ] 'type'
This example drops the userdefined data type birthday.
Arguments
[@typename =] 'type'
Is the name of a user-defined data type that
you own. type is sysname, with no default.
Note This user-defined data type
must already exist or this example
returns an error message.
EXEC sp_droptype 'birthday'
Normalization
Normalization
•
•
•
•
•
•
•
•
•
The purpose of normailization
Functional Dependencies
The Process of Normalization
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)
The Purpose of Normalization
Normalization is a technique for producing a set of relations
with desirable properties, given the data requirements of an
enterprise.
The process of normalization is a formal method that
identifies relations based on their primary or candidate
keys and the functional dependencies among their
attributes.
Functional Dependencies
Functional dependency describes the relationship between
attributes in a relation.
For example, if A and B are attributes of relation R, and B is
functionally dependent on A ( denoted A B), if each value of
A is associated with exactly one value of B. ( A and B may each
consist of one or more attributes.)
B is functionally
A
B
dependent on A
Determinant
Refers to the attribute or group of attributes on the
left-hand side of the arrow of a functional dependency
Functional Dependencies (2)
Trival functional dependency means that the right-hand
side is a subset ( not necessarily a proper subset) of the lefthand side.
For example: (See Figure 1)
staffNo, sName  sName
staffNo, sName  staffNo
They do not provide any additional information about possible integrity
constraints on the values held by these attributes.
We are normally more interested in nontrivial dependencies because they
represent integrity constraints for the relation.
Functional Dependencies
Main characteristics of functional dependencies in normalization
• Have a one-to-one relationship between attribute(s) on the left- and
right- hand side of a dependency;
• hold for all time;
• are nontrivial.
Functional Dependencies
Identifying the primary key
Functional dependency is a property of the meaning or
semantics of the attributes in a relation. When a functional
dependency is present, the dependency is specified as a
constraint between the attributes.
An important integrity constraint to consider first is the
identification of candidate keys, one of which is selected to
be the primary key for the relation using functional dependency.
Functional Dependencies
Inference Rules
A set of all functional dependencies that are implied by a given
set of functional dependencies X is called closure of X, written
X+. A set of inference rule is needed to compute X+ from X.
Armstrong’s axioms
1.
2.
3.
4.
5.
6.
7.
Relfexivity:
If B is a subset of A, them A  B
Augmentation: If A  B, then A, C  B
Transitivity:
If A  B and B  C, then A C
Self-determination:
AA
Decomposition: If A  B,C then A  B and A C
Union:
If A  B and A  C, then A B,C
Composition:
If A  B and C  D, then A,C B,
Functional Dependencies
Minial Sets of Functional Dependencies
A set of functional dependencies X is minimal if it satisfies
the following condition:
• Every dependency in X has a single attribute on its
right-hand side
• We cannot replace any dependency A  B in X with
dependency C B, where C is a proper subset of A, and
still have a set of dependencies that is equivalent to X.
•
We cannot remove any dependency from X and still
have a set of dependencies that is equivalent to X.
Functional Dependencies
Example of A Minial Sets of Functional
Dependencies
A set of functional dependencies for the StaffBranch relation
satisfies the three conditions for producing a minimal set.
staffNo  sName
staffNo  position
staffNo  salary
staffNo  branchNo
staffNo  bAddress
branchNo  bAddress
branchNo, position  salary
bAddress, position  salary
The Process of Normalization
• Normalization is often executed as a series of steps. Each step
corresponds to a specific normal form that has known properties.
• As normalization proceeds, the relations become progressively
more restricted in format, and also less vulnerable to update
anomalies.
• For the relational data model, it is important to recognize that
it is only first normal form (1NF) that is critical in creating
relations. All the subsequent normal forms are optional.
First Normal Form (1NF)
Repeating group = (propertyNo, pAddress,
rentStart, rentFinish, rent, ownerNo, oName)
Unnormalized form (UNF)
A table that contains one or more repeating groups.
ClientNo
CR76
CR56
cName
John
kay
Aline
Stewart
propertyNo
pAddress
PG4
6 lawrence
St,Glasgow
rentStart
1-Jul-00
rentFinish
31-Aug-01
rent
350
ownerNo
oName
CO40
Tina
Murphy
5 Novar Dr,
Glasgow
1-Sep-02
1-Sep-02
450
CO93
Tony
Shaw
PG4
6 lawrence
St,Glasgow
1-Sep-99
10-Jun-00
350
CO40
Tina
Murphy
PG36
2 Manor Rd,
Glasgow
CO93
Tony
Shaw
PG16
5 Novar Dr,
Glasgow
CO93
Tony
Shaw
PG16
Figure 3 ClientRental unnormalized table
10-Oct-00
1-Nov-02
1-Dec-01
1-Aug-03
370
450
Definition of 1NF
First Normal Form is a relation in which the intersection of each
row and column contains one and only one value.
There are two approaches to removing repeating groups from
unnormalized tables:
1. Removes the repeating groups by entering appropriate data
in the empty columns of rows containing the repeating data.
2. Removes the repeating group by placing the repeating data,
along with a copy of the original key attribute(s), in a separate
relation. A primary key is identified for the new relation.
1NF ClientRental relation with the first
approach
The ClientRental relation is defined as follows,
With the first
approach,
we remove
the repeating
group
ClientRental
( clientNo,
propertyNo,
cName, pAddress,
rentStart,
rentFinish, rent,
(property
rented
details) by entering the appropriate client
ownerNo,
oName)
data into each row.
ClientNo
propertyNo
cName
pAddress
rentStart
rentFinish
rent
ownerNo
oName
CR76
PG4
John
Kay
6 lawrence
St,Glasgow
1-Jul-00
31-Aug-01
350
CO40
Tina
Murphy
CR76
PG16
John
Kay
5 Novar Dr,
Glasgow
1-Sep-02
1-Sep-02
450
CO93
Tony
Shaw
CR56
PG4
Aline
Stewart
6 lawrence
St,Glasgow
1-Sep-99
10-Jun-00
350
CO40
Tina
Murphy
PG36
Aline
Stewart
2 Manor Rd,
Glasgow
PG16
Aline
Stewart
5 Novar Dr,
Glasgow
CR56
CR56
10-Oct-00
1-Nov-02
1-Dec-01
1-Aug-03
Figure 4 1NF ClientRental relation with the first approach
370
450
CO93
Tony
Shaw
CO93
Tony
Shaw
1NF ClientRental relation with the second
approach
Client
(clientNo,
cName) the repeating group
With the second approach,
we remove
PropertyRentalOwner
(clientNo, propertyNo, pAddress, rentStart,
(property rented details)
by placing the repeating data along with
rentFinish, rent, ownerNo, oName)
aClientNo
copy ofcName
the original key attribute (clientNo) in a separte relation.
CR76
John Kay
CR56
Aline Stewart
ClientNo
propertyNo
CR76
PG4
CR76
PG16
CR56
PG4
CR56
PG36
CR56
PG16
pAddress
6 lawrence
St,Glasgow
5 Novar Dr,
Glasgow
6 lawrence
St,Glasgow
2 Manor Rd,
Glasgow
5 Novar Dr,
Glasgow
rentStart
rentFinish
rent
ownerNo
oName
1-Jul-00
31-Aug-01
350
CO40
Tina
Murphy
1-Sep-02
1-Sep-02
450
CO93
Tony
Shaw
1-Sep-99
10-Jun-00
350
CO40
Tina
Murphy
10-Oct-00
1-Dec-01
370
CO93
Tony
Shaw
1-Nov-02
1-Aug-03
450
CO93
Tony
Shaw
Figure 5 1NF ClientRental relation with the second approach
Full functional dependency
Full functional dependency indicates that if A and B are
attributes of a relation, B is fully functionally dependent on
A if B is functionally dependent on A, but not on any proper
subset of A.
A functional dependency AB is partially dependent if there
is some attributes that can be removed from A and the
dependency still holds.
Second Normal Form (2NF)
Second normal form (2NF) is a relation that is in first
normal form and every non-primary-key attribute is fully
functionally dependent on the primary key.
The normalization of 1NF relations to 2NF involves the
removal of partial dependencies. If a partial dependency
exists, we remove the function dependent attributes from
the relation by placing them in a new relation along with
a copy of their determinant.
2NF ClientRental relation
The ClientRental relation has the following functional
dependencies:
fd1
fd2
fd3
fd4
fd5
fd6
clientNo, propertyNo  rentStart, rentFinish
(Primary Key)
clientNo  cName
(Partial dependency)
propertyNo  pAddress, rent, ownerNo, oName (Partial dependency)
ownerNo  oName
(Transitive Dependency)
clientNo, rentStart  propertyNo, pAddress,
rentFinish, rent, ownerNo, oName
(Candidate key)
propertyNo, rentStart  clientNo, cName, rentFinish
(Candidate key)
2NF ClientRental relation
After
partial dependencies,
the creation of the three
Clientremoving the
(clientNo,
cName)
new
relations called
Client, Rental,
and PropertyOwner
Rental
(clientNo,
propertyNo,
rentStart, rentFinish)
PropertyOwner (propertyNo, pAddress, rent, ownerNo, oName)
Client
Rental
ClientNo
cName
ClientNo
propertyNo
rentStart
rentFinish
CR76
CR56
John Kay
Aline Stewart
CR76
PG4
1-Jul-00
31-Aug-01
CR76
CR56
CR56
CR56
PG16
PG4
PG36
PG16
1-Sep-02
1-Sep-99
10-Oct-00
1-Nov-02
1-Sep-02
10-Jun-00
1-Dec-01
1-Aug-03
PropertyOwner
propertyNo
pAddress
rent
ownerNo
oName
PG4
6 lawrence St,Glasgow
350
CO40
Tina Murphy
PG16
5 Novar Dr, Glasgow
450
CO93
Tony Shaw
PG36
2 Manor Rd, Glasgow
370
CO93
Tony Shaw
Figure 6 2NF ClientRental relation
Third Normal Form (3NF)
Transitive dependency
A condition where A, B, and C are attributes of a relation such that
if A  B and B  C, then C is transitively dependent on A via B
(provided that A is not functionally dependent on B or C).
Third normal form (3NF)
A relation that is in first and second normal form, and in which
no non-primary-key attribute is transitively dependent on the
primary key.
The normalization of 2NF relations to 3NF involves the removal
of transitive dependencies by placing the attribute(s) in a new
relation along with a copy of the determinant.
3NF ClientRental relation
The functional dependencies for the Client, Rental and
PropertyOwner relations are as follows:
Client
fd2
clientNo  cName
(Primary Key)
clientNo, propertyNo  rentStart, rentFinish
clientNo, rentStart  propertyNo, rentFinish
propertyNo, rentStart  clientNo, rentFinish
(Primary Key)
(Candidate key)
(Candidate key)
Rental
fd1
fd5
fd6
PropertyOwner
fd3
fd4
propertyNo  pAddress, rent, ownerNo, oName
(Primary Key)
ownerNo  oName
(Transitive Dependency)
3NF ClientRental relation
The resulting 3NF relations have the forms:
Client
Rental
PropertyOwner
Owner
(clientNo, cName)
(clientNo, propertyNo, rentStart, rentFinish)
(propertyNo, pAddress, rent, ownerNo)
(ownerNo, oName)
3NF ClientRental relation
Rental
Client
ClientNo
cName
ClientNo
propertyNo
rentStart
rentFinish
CR76
CR56
John Kay
Aline Stewart
CR76
PG4
1-Jul-00
31-Aug-01
CR76
CR56
CR56
CR56
PG16
PG4
PG36
PG16
1-Sep-02
1-Sep-99
10-Oct-00
1-Nov-02
1-Sep-02
10-Jun-00
1-Dec-01
1-Aug-03
PropertyOwner
Owner
propertyNo
pAddress
rent
ownerNo
ownerNo
oName
PG4
6 lawrence St,Glasgow
350
CO40
CO40
Tina Murphy
PG16
5 Novar Dr, Glasgow
450
CO93
CO93
Tony Shaw
PG36
2 Manor Rd, Glasgow
370
CO93
Figure 7 2NF ClientRental relation
Boyce-Codd Normal Form (BCNF)
Boyce-Codd normal form (BCNF)
A relation is in BCNF, if and only if, every determinant is a
candidate key.
The difference between 3NF and BCNF is that for a functional
dependency A  B, 3NF allows this dependency in a relation
if B is a primary-key attribute and A is not a candidate key,
whereas BCNF insists that for this dependency to remain in a
relation, A must be a candidate key.
Example of BCNF
fd1
fd2
fd3
fd4
clientNo, interviewDate  interviewTime, staffNo, roomNo
(Primary Key)
staffNo, interviewDate, interviewTime clientNo
(Candidate key)
roomNo, interviewDate, interviewTime  clientNo, staffNo
(Candidate key)
staffNo, interviewDate  roomNo
(not a candidate key)
As a consequece the ClientInterview relation may suffer from update anmalies.
For example, two tuples have to be updated if the roomNo need be changed for
staffNo SG5 on the 13-May-02.
ClientInterview
ClientNo
interviewDate
interviewTime
staffNo
roomNo
CR76
13-May-02
10.30
SG5
G101
CR76
CR74
CR56
13-May-02
13-May-02
1-Jul-02
12.00
12.00
10.30
SG5
SG37
SG5
G101
G102
G102
Figure 8 ClientInterview relation
Example of BCNF(2)
To transform the ClientInterview relation to BCNF, we must remove
the violating functional dependency by creating two new relations
called Interview and SatffRoom as shown below,
Interview (clientNo, interviewDate, interviewTime, staffNo)
StaffRoom(staffNo, interviewDate, roomNo)
Interview
ClientNo
interviewDate
interviewTime
staffNo
CR76
13-May-02
10.30
SG5
CR76
CR74
13-May-02
13-May-02
12.00
12.00
SG5
SG37
CR56
1-Jul-02
10.30
SG5
staffNo
interviewDate
roomNo
SG5
13-May-02
G101
SG37
SG5
13-May-02
1-Jul-02
G102
G102
StaffRoom
Figure 9 BCNF Interview and StaffRoom relations
Fourth Normal Form (4NF)
Multi-valued dependency (MVD)
represents a dependency between attributes (for example, A,
B and C) in a relation, such that for each value of A there is a
set of values for B and a set of value for C. However, the set of
values for B and C are independent of each other.
A multi-valued dependency can be further defined as being
trivial or nontrivial. A MVD A > B in relation R is defined
as being trivial if
• B is a subset of A
or
•AUB=R
A MVD is defined as being nontrivial if neither of the above two
conditions is satisfied.
Fourth Normal Form (4NF)
Fourth normal form (4NF)
A relation that is in Boyce-Codd normal form and contains
no nontrivial multi-valued dependencies.
Fifth Normal Form (5NF)
Lossless-join dependency
Fifth normal form (5NF)
A property of decomposition, which ensures that no spurious
A relation that has no join dependency.
tuples are generated when relations are reunited through a
natural join operation.
Join dependency
Describes a type of dependency. For example, for a relation
R with subsets of the attributes of R denoted as A, B, …, Z, a
relation R satisfies a join dependency if, and only if, every
legal value of R is equal to the join of its projections on A, B,
…, Z.