SQL Server (t-sql)

This Course will give In-depth

Information related to t-sql language
Author: Lokanatha Palle
Date: 12-Dec-2017
 sql server overview

data base engine

relational database
management system RDBMS DBE

Contains set of rules

understand only t-sql language

sql server is an invisible software which is

not having GUI. Hence we use SSMS as User
Master
client to see what is there in sql server specific
and for communicating with DBE
Sql Server Management
Studio
Creating a Data base

DBE

RDBMS

Command(s) Successful
Skillgun Palle Master
SSMS and SQL Server Communication

Execute RDBMS

DBE
T-SQL query

CREATE DATABASE
TRAINING

SSMS MASTER DB

TRAINING
SQL SERVER
 Data types in T-SQL
•Int •Binary • Timestamp
•Small int •Varbinary(max) • Table
•Bigint •Datetime • Real
•Tinyint •Date • Numeric
•Decimal •Time • Sql_variant
•Char •Smalldatetime • xml
•Nchar •datetime2
•Varchar •Money
•Nvarchar •Smallmoney
•Varchar(max) •bit
 Datatypes Size in bytes
• Tinyint 1 byte
• Smallint 2 bytes
• Int 4 bytes
• Bigint 8 bytes
• decimal Can store upto 38 digits all can be
after decimal point

• Char Can store max 8000 characters

• Varchar Can store upto 8000 characters

• Varchar(max) Can store upto 2^31 characters

• Bit 1 bit ( can store either 0 or1)

Note: usually used to store true
or false
 Difference b\w char and nchar

Char Nchar

• we can store up to 8000 chars • can store up to 8000

(can be English + special+ characters
numeric ) • we are allowed to store
(Globalized character+ English
+ special+ numbers)
 char varchar
• Fixed size datatype • Varying size datatype

• declare @c char(10); • declare @v varchar(10);

Set @c = ‘ABC’ Set @v = ‘ABC’;

A B C
A B C
 Table
• Tables are the combination of Rows and Columns
Table = Row+Column
• Rows = tuples
Table1
• Columns = Attributes
or properties
 Table creation syntax
Create table <table_name>
(
Column1 datatype1[(Size)],
Column2 datatype2,
Column3 datatype3,
……..
)
Note: As per industry standards table names and
column names must not be plurals.
 table creation sample
Req:create a student table to store students details
sid,name,class,dob

Int vc(40) vc(40) date

sid name class dob

student
 table creation assignment

• write code for implementing the following set of items?

• create a table with the name products and with column
names (pidint, pnamevarchar(40),costint,
manifacturer_namevarchar(40),
manifactured_datedate)
• the table must be created in PalleTraining DB (assuming
that the DB is already available in your sql server).
• insert the following data into products table.
1, lux, 34, HUL, dec-12-2017
2, locks, 1200, Godrej, Jan-11-2018
• write query for displaying data present in product table
 constraints Part1

• using constraints we can limit the data which

is coming into table columns.
• t-sql supports following constraints

not null default check

primary foreign
unique
key key
 Default Constraints
Default constraints are useful for inserting default values
when user does not supply any value
Create table student
(
Sid int, Default
banglore
Name varchar(40),
City varchar(40) default ‘banglore ‘
)

DBE
 NotNull Constraints
When you set the not null constraint to a specific column , that
column will not allow null value
NOT NULL

DBE




Not null constraint will not allow null values.
 Check Constraints
By using check constraints we can limit the Range of permissible values
into specific column

Req:Create a table with employee details eid,Name,Age_in_years(Age must be

between 18 to 60)

Do You think that DBE will accept this command?

18 - 60

DBE will not Accept this Command

 DBE

 Error
Whether DBE will accept this values?
 Primary Key
 primary key gives uniqueness to the tables rows
 only one primary key is allowed per table
 primary key will not allow null values and duplicate values

Lets see a sample

Primary Key

customer

Duplicate values are not allowed

DB
ERROR E
 unique
unique constraint gives uniqueness to the tables rows
any number of unique constraints are allowed per table
unique contraint will not allow duplicate values and
allows only one null value
Unique

Varchar(40) int Varchar(40)

Name Cell_No Product
Ram 9943300821 Toothbrush
mahesh NULL Soap

CUSTOMER

DBE
ERROR
 Composite primary key
When we apply primary key constraint on more than one column,
then it is called Composite primary key.
primary key

Varchar(40) date Varchar(40)

Name Dob Product
Ravi 10-8-2017 toothbrush
mahesh 10-8-2017 lux soap
Ravi 10-8-2018 lion dates
CUSTOMER

how many PK’s are created in the table

Only 1 PK for 2 columns.
DBE ERROR
 Difference between primary key and unique

Primary key Unique

• Only one primary key is • Any number of unique
allowed per table constraints are allowed/table
• Will not allow NULL values • Will allow one NULL value
• It will internally create • Unique will create non
clustered index.(we will clustered index.
understand later)
 Constraints assignment
Int varchar(40) int varchar(10) int varchar(40)
Eid Name salary bg age email

4 ravi 36000 O+ve 48 ravi@gmail

.com
6 suresh 38000 O+ve 56 null

Req:

[Link] column must not allow any duplicate or null values

[Link] column must not allow any null values
[Link] should have default value as o+ve
[Link] should have range from 18-60 years
[Link] column should allow only one null value and no duplicate values
 Normalization
• using normalization we can reduce the data
duplication or data redundancy.
• usually normalization process involves splitting a
single table into multiple tables.
• It is recommended to create a new table for storing
predictable repeating data .
• Ex: blood group names, state names / province
names in a country…….
• normalization is used for avoiding the
insert/update/delete anomaly or inconsistency
 Consider this student table for understanding normalization

• Assume this table consist of 5000 records

• Observe the table and tell me , is there any

data duplication (repetition of same data)

The state column data are duplicated , and

tell me is state column data are predictable?

Definitely the state column data are predictable data, because we have only
29 states in our country. All the 5000 students must belong to any 1 of 29 states.

Memory required for storing 1 state name is, Then for storing all the state names
how much memory is required ??
Very huge memory is required

How can we avoid this duplication, By splitting the single table into two tables
Now i have created 1 separate table for storing all the state names, and I have given a unique id
For each state

Now I will create a student table and in place of state column , I will give the state id.
UnNormalized table

We have reduced the data duplication by using the normalization technique

 Foreign key constraint
•Using Foreign key constraint we link / relate 2 or more tables.
•We can achieve referential integrity
When we are normalizing the tables there are chances for getting insert
and update anomaly

To eliminate this problem, we will be using Foreign key constraint

• Foreign key will allow null values
• Any number of Foreign key constraints can be created per table
• Any Fk column mapped to other column from any table must be defined with either
with primary key or unique constraints state
Syntax Primary key
Create table table_name
(
Col1 datatype Foreign key references table_name(column_name)
Col2 datatype
)
Example

How will you link the columns

ERROR student
 Normalization lab1

• Normalize the employee2 table use foreign

key
 Blood_group state

Lab 1 solution

Foreignkey

Foreignkey

employee2 New_employee2

Un-normalized employee table Normalized employee table

 Normalization Lab 2

• Normalize the student table and use foreign

key
 Wrong solution

F.K F.K F.K

 Wrong solution Why?

F.K

F.K F.K

Is it a valid data?

Assume we are trying to insert

DBE
Normalized student table Solution Un-normalized student table

student student

F.K F.K F.K

country

city state
 Normalization lab3
Req:Design Normalized Database

suresh kiran veena

DotNet Android DotNet
Batch1 Batch2 Batch1
18989 19000 19989
19989 20989 19989
Mahesh kumar
Bharath
android DotNet
UIDeveloper
Batch1 Batch2
Dotnet
19000 18000
9000
20989 19989
12989
 Normalization lab3-Solution
PK
student course

PK
F.K F.K

Batch
 Types Of t-sql Statements
• t-sql statements categorized into
• DML Statements(Insert, Update, Delete, Select *
into )
• DDL Statements (Create, Alter)
• DQL Statements (All Select statements except
select * into )
• TCL Statements (Commit, Rollback)
• DCL Statements (Grant, Revoke used only by
DBA’s not by DB Programmers)
 Generic Select Statement

SELECT select_list

[INTO new_table_name]

FROM table_list

[WHERE search_conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]

[ORDER BY order_list [ASC | DESC]]

 Select sample 1 employee

Display all employee Details.

Select * from employee

Select eid,fname,lname,age,salary,dept,doj from employee

DBE
 Ask students to take table data in last page of their note.

Employee
 Select sample-2
ERS
write a query for producing the following result set Fullname age
From employee table rajeevsukla 23
sowmyakumari 23
kishorekumar 27
Select fname+lname,age from employee abimanyubiswal 22

Select fname+lname as ‘Fullname’,age from employee

No column
Fullname
name age
rajeevsukla 23
sowmyakumari 23
kishorekumar 27
abimanyubiswal 22
DBE
 • Table must be created without any constraints
Note: Create this table in the last pages of your
Table : Patient note book as this table used for explaining all
topics in t-sql.
Pid int
Fnamevarchar(40)
Lnamevarchar(40)
Ageint
BgVarchar(40)
 Select Statement Lab-1

• Write a query for displaying all data present in

patient table ( using * )

• Write a query for displaying all columns data

present in patient table without using *

• Write a query for displaying all patients

fullnames, pid, age
 Select Statement Lab-2

• Write a query for displaying all patients full

names along with the ages by incrementing
all patients age by 2 years ( Sample ERS)
 Select Statement Lab-3
• write a select statement for displaying the following result
set?
 Order By Clause

• Used to Order data present in a table based on

One or more columns.
• Use ASC keyword for ascending order and DESC
for descending order.
• Default Order is ASC
• Syntax:
select_list order by c1 Asc/Desc,c2 Asc/Desc,..
Employees table
int Vc(40) Vc(40 int int Vc(40) date
)
Display employees in
ascending order of their fname.

Select * from employees order by fname Final Result set

DBE
Employees table
int Varchar(40) Varchar(40) int int Varchar(40) date

Display employee
in desc order of
their first name

Select * from employees order by fname desc FRS created

DBE
 Draw the final result set .
Employees table
int Varchar(40) Varchar(40) int int Varchar(40) date

Select * from employees order by lname,fname desc DBE

Modifies the query

Select * from employees order by lname asc,fname desc

Employees table
Select * from employees order by lname asc,fname desc
int Varchar(40) Varchar(40) int int Varchar(40) date

FRS
 Order By Lab-1
• Identify the output for the following query?
Select fname+lname as ‘full name’, age from patient
order by age
• Identify the output for the following query?
select fname, lname, bg from patient order by bg
desc
Note: ascii for + is 43 and for – is 45
• Identify the output for the following query?
select fname, lname, pid from patient order by
lname, fname desc
 Order By Lab-2

• Identify the result set for the following query?

select fname,age from patient order by
‘Hello’+fname+lname desc
• write a query for displaying all patients data in
the descending order of their ages?
 Distinct clause

• Distinct clause is used for eliminating

duplicate rows from result set
• Syntax: distinct single column name
or distinct column1,column2,….
or distinct *
Int varchar(40) varchar(40) int int varchar(40) date

EMPLOYEES TABLE
QUERY: select distinct * from employees DBE

DBE produces IRS without

considering distinct clause

INTERMEDIATE RESULT SET

FINAL RESULT SET

select distinct lname from employees DBE

DBE produces IRS without

considering distinct clause

Lname
sukla

kumari

Kumar

biswal

_singh

INTERMEDIATE RESULT SET Moh%anty

_kumari

FRS
 Distinct Lab-1

• Identify the result set for the following query?

• select distinct fname, age from patient
• Identify the result set for the following query?
• select distinct age, bg from patient
• Identify the result set for the following query?
• select distinct fname, age from patient order by
bg desc
 top clause
• top clause is used to fetch top n rows or first n
rows from a table.
• Syntax : select top n column from table_name
or select top n column1,column2,.. from
table_name
Here n can be any number
Display the first 5 records from employee table employee

Select top 5 * from employee DBE

Final result set

Display fname , lname and age column data for first 3 records from employee table

employee
Select top 3 fname,lname,age from employee DBE

FRS
IRS
Write a query to display the top 3 highest paid employee’s fullname and age

Select top 3 fname+lname as ‘fullname’ ,age from employee order by salary desc

IRS1

DBE

Final Result Set

IRS2
   Employee

select distinct top 6 fname,age from employee order by age desc

Draw the result set for this query

Final result set

 top clause Lab-1

• Identify the output for the following query?

• select top 3 fname, lname, age from patient
• Write a query for displaying top 3 patients
details in the descending order of their age?
• ERS:
 where clause

• where clause is used to filter the records

present in a table.
• where clause can be applied on select or
update or delete statements.
• syntax: where <condition>

For writing a condition, we have to understand the operators

 operators part-1

>, >=, <, <= ,!= ,<> , and, or, between,

not between ,In, not in, is null,
is not null, all, any
Conditional AND(&&),conditional OR(||) are not
supported in t-sql
Bitwise And(&),bitwise(|) are supported in t-sql
but we never use in real time applications
 operators part-2

while using in or not in we must use set of

values.
Ex: where col1 in (val1,val2,…)
while checking for null values we must use
either is null or is not null
while using between or not between we must
use min value first and max value later
Req: display employees details whose salary is greater
than 20000

DBE
Query: select * from employees where salary>20000
Req: display all employees details whose salary is
between 15000 and 25000

Query: select * from employees where salary

DBE
between 15000 and 25000
Req: display all employees details whose salary is less
than 15000 greater than 25000

Query: select * from employees where salary not

DBE
between 15000 and 25000
Req: display all employees details whose salary is
equal to 15000 or21000 or 36000

Query: select * from employees where salary

DBE
in(15000,21000,36000)
Req: display all employees details whose salary is not
equal to 15000 or21000 or 36000

Query: select * from employees where salary DBE

not in(15000,21000,36000)
Req: display all employees details whose salary is null

Query: select * from employees where salary DBE

is null
Req: display all employees details whose salary is not
null

Query: select * from employees where salary is not null DB

E
 where clause lab-1
• write a query for displaying all patients details whose
age is greater than 45?
select * from patient where age>45
• write a query for displaying all patients details whose
age is between 40 and 50 (write query in all possible
ways)?
Select * from patient where age between 40 and 50
Select * from patient where age>=40 and age<=50
• write a query for displaying all patients details whose
age is greater than 40 and whose bg is not o+ve?
• Select * from patient where age>40 bg!=‘o+ve’
 where clause lab-2
• write a query for displaying all patient details whose bg is not
null?
select * from patient where bg is not null
• write a query for displaying all patient details whose age is
equal to 42 or 36 or 60 (write query using all possible ways)?
select * from patient where age=42 or age=36 or age=60
select * from patient where age in (42,36,60)
• write a query for displaying all patient details whose age is not
equal to 42 and 36 and 60 (write query using all possible
ways)?
select * from patient where age!=42 or age!=36 or age!=60
select * from patient where age not in (42,36,60)
 where clause lab-3

• identify the result set for the following query?

select top 3 * from patient where age>40 order
by bg desc
(student must identify which portion of the query
is executed first, also write IRS and FRS)
 like clause & pattern matching
• using like clause we can find data which is matching to a
specific pattern.
• syntax : where column|expression|variable like ‘pattern’
• wildcard characters used in pattern matching
Wildcard character description
% 0 or more characters
_ Any single character
[] Any single character within a
specified range or in a set of
characters
^ Any single character not within the
specified range or in a set of
characters.
 employees

Requirement:Display employee details who having fname

starting with character ‘a’
DBE
Solution:select * from employees where fname like ‘a%’

FRS
 employees

Requirement:Display employee details who having fname

ending with character ‘a’
Solution:select * from employees where fname like ‘%a’
DBE FRS
 employees

Requirement:Display employee details who having fname

having character ‘a’ in second position DBE
Solution:select * from employees where fname like ‘_a%’
FRS
 employees

Requirement:Display employee details whose fname having

character ‘a’ anywhere in fname
DBE
Solution:select * from employees where fname like ‘%a%’
FRS
 employees

Requirement:Display employee details whose lname having character ‘a’

anywhere but not in last position ( last char can be any
DBE
Solution:select * from employees where lname like ‘%a%_’

FRS
 employees

Requirement:Display employee details whose lname having

character ‘a’ in second last position DBE
Solution:select * from employees where lname like ‘%a_’

FRS
 employees

Requirement:Display employee details whose fname starting

with character a-k
Solution:select * from employees where fname like ‘[a-k]%’ DBE

FRS
 employees

Requirement:Display employee details whose fname not

starting with character a-k
Solution:select * from employees where fname like ‘[^a-k]%’ DBE

FRS
 employees

Requirement:Display employee details whose fname having

character ‘a’ anywhere in fname and data must displayed in
descending order of their salaries

Solution: select * from employees where fname like ‘%a%’

order by salary desc
DBE
employees

FRS created
employees

FRS created
 employees

Requirement:Display employee details whose fname are having

character ‘i’ in third position
Solution: select * from employees where fname like ’__i%’

DBE
employees

FRS created
 employees

Requirement:Display employee details whose fname are not

ending with character ‘a’
Solution: select * from employees where fname not like ’%a’
DBE

FRS
 Like clause Lab-1

• write a query for displaying all patient details

whose fname’s are starting with character m?
• write a query for displaying all patients full
names whose lnames are ending with i?
• write a query for displaying all patients details
whose lnames are having character a in the
second position from ending ? for ex.. kiran is
having character a in the second position
from ending
 Like clause Lab-2

• write a query for displaying all patient details

whose fnames are having character r in the 3rd
position from ending?
• write a query for displaying all patient details
whose fnames are having character i any
where ?
• write a query for displaying all patient details
whose fnames are starting with either a or b
or c or d or e or f characters?
GENERIC INSERT,UPDATE AND
DELETE STATEMENTS
USED TO MODIFY OUR TABLE DATA
syntax for generic insert statement
insert into table_name(c1,c2,c3….)
values(v1,v2,v3…)
note: insert statement will not contain where clause
syntax for generic update statement
update table_name set c1=v1,c2=v2, ….[where
condition]
note: update statement without where will update
the complete table data
syntax for generic delete statement
delete [from] table_name [where condition]
note: delete statement without where will delete all
data present in a table.
Req:insert a new record into employee table with
eid=9,fname=giri, lname=babu,age=27,salary
18000,dept=.net,doj=06-24-2014
Insert into employees(eid,fname,lname,age,salary,dept,doj)
values(9,’giri’,’babu’,27,18000,’.net’,’06-24-2014’) DBE

employees
FRS
Req:update employee table with fname to rice, lname to paul
whose eid is 9

update employees set fname=‘rice’,lname=‘paul’ where eid=9

DBE

FRS employees
Req: delete a record from employee table whose eid is 9

DBE
delete employees where eid=9

employees FRS
 Insert Update Delete Lab-1
• write a query for inserting the following patient
details into patient table?
patient id=10 fname=‘ahaha’ lname=‘kumar’
age=78 bg=‘o+ve’ ( write query using all possible
ways )
• write a query for inserting the following patient
details into patient table?
patient id=11 fname=‘silli’ lname=‘suresh’
age=81 bg is null ( write query using all possible
ways )
 Insert Update Delete Lab-2

• write a query for updating the 10th patient

(pid=10) with the following details?
fname=‘raja’ lname=‘raveender’ age=66
bg=‘o-ve’
• write a query for updating the 11th patient
(pid=11) with the following details?
fname=‘meena’ lname=‘kumari’
• write a query for deleting 10th and 11th
patients from patient table?
 delete
drop
truncate
 delete without where

• a delete statement without where clause will

delete complete table data.
• delete statement is a logged operation.
• delete operation is a reversible operation.
• delete operation is slow compared to truncate
operation.
 Patient table

Pid FName Lname age bg

1 Madhava reddy 45 O+ve Delete patient
2 Hari kiran 60 B-ve

DBE

Similar table will be created in .ldf

.ldf

Pid FName Lname age bg

 Truncate table

truncate table statement will delete all data

present in a table.
truncate statement is not a logged operation
and hence we can’t rollback this operation.
syntax: truncate table table_name
“truncate operation” is faster than “delete
without where clause”.
truncate statement can’t contain where clause
Patient Table Table structure will not
delete
P_Id FName LName age bg
1 Madhava Reddy 45 O+ve
4 Hari kiran 60 B-ve
3 Madhava kiran 52 O+ve
5 Veena kumari 42 Null Truncate table patient
6 K_iran kumar 39 B-ve
2 Abhinav bandra 45 O-ve
7 Mahes%h Nambotri 36 B+ve

.ldf
DBE
No similar table will be created in .ldf
 Drop table

• drop table operation will delete the table from

the database and also the related constraints
and indexes.
• syntax: drop table table_name
• drop is not a logged operation
Patient Table Table structure also
deleted
P_Id FName LName age bg
1 Madhava Reddy 45 O+ve
4 Hari kiran 60 B-ve
3 Madhava kiran 52 O+ve
5 Veena kumari 42 Null Drop table patient
6 K_iran kumar 39 B-ve
2 Abhinav bandra 45 O-ve
7 Mahes%h Nambotri 36 B+ve

.ldf
DBE
No similar table will be created in .ldf
 Functions

Functions

Built In
functions User defined
functions

aggregate
functions

date time table valued

scalar

Cast and
Convert
 aggregate functions
Note: we are not allowed to use aggregate functions in where clause
Note2: we are not allowed to use columns in
select list which are not linked with aggregate
Aggregate Functions functions when any column is linked with
aggregate function
Min

Max

Avg

Sum

Count
 employee

Select AVG(age) from employee

Select MIN(salary) as ‘min_sal’ from employee DBE

Select Max(salary) as ‘min_sal’ from employee

Select SUM(salary) as ‘tot_salary’ from employee
 employee






DBE
Select COUNT(*) as ‘result’ from employee

Select COUNT(*) as ‘result’ from employee where dept=‘.net’

 aggregate functions lab-1

• write a query/program for displaying youngest

patient age, eldest patient age, sum of all
patients age and average age of patients?
ERS:

• identify the output for the following query?

query: select fname from patient where
min(age)=age;
 aggregate functions lab-2

• write a program for displaying youngest and

eldest patients fnames?
ERS:

• identify the output for the following program?

select MIN(age) as 'min age', fname from
patient
 count function

• count function gives total number of matched

records count from an intermediate result set.
 count function lab-1

• identify the output for the following query?

 date time functions

Current Date
Time Functions datename datepart

GetDate()

day month year

Current_timestamp

dateadd datediff
GetUtcDate()
 Date time function

insert into employee values ( 11

13 ,'ram','kumar',23,17000,'.net',Getdate())
12 ,CURRENT_TIMESTAMP)
,getutcdate())
employee

DBE
 DBE
Select datename(month,getdate())

Select datename(year,getdate())

Select datename(WEEKDAY,GETDATE()) as 'day'

Select datename(day,getdate())
 Date part function
declare @d1 date;
set @d1='03/14/1979'
select DATEPART(YEAR,@d1) DBE

declare @d2 date;

set @d2='03/14/1979'
select DATEPART(MONTH,@d2)

declare @d3 date;

set @d3='03/14/1979'
select DATEPART(DAY,@d3)
declare @d1 date;
set @d1='03/14/1979'
select Year(@d1)

declare @d2 date;

DBE
set @d2='03/14/1979'
select month(@d2)

declare @d3 date;

set @d3='03/14/1979'
select day(@d3)
 date add and date diff functions
• dateadd function used to add a specified number
of years/months/days/hours/minutes/seconds to
a specified date or date time value.
• syntax: dateadd(datepart,number,datevalue)
returns varchar
• datediff function used to find difference between
two date time values either in
days/months/years/hours/minutes/seconds etc.
• syntax: datediff(datepart,startdate,enddate)
returns int
 @dob
declare @d date
set @d='12/26/1979‘ 1979-12-26
1991-12-26
set @d=dateadd(year,12,@d)

@dob1
DBE 2018-06-12
2018-01-12
declare @d1 date
set @d1=GETDATE();
set @d1=dateadd(MONTH,5,@d1)

@dob2
declare @d2 date
set @d2=GETDATE(); 2018-01-27
2018-01-12
set @d1=dateadd(DAY,15,@d2)
 @d1
declare @d1 date
1979-03-14
declare @d2 date DBE
set @d1='03/14/1979'
set @d2='12/18/2017'
@d2
2017-12-18
declare @y int
set @y=DATEDIFF(YEAR,@d1,@d2)
@y
38
declare @m int
set @m=DATEDIFF(MONTH,@d1,@d2) @m
465
declare @d int
@d
set @d=DATEDIFF(DAY,@d1,@d2)
14159
 date time functions lab-1

• write a program for adding 8 years 3 months

and10 days to current date and display the
resultant date?
• write a program for displaying difference
between two dates in terms of total days
difference, total months difference and total
years difference.
 cast and convert functions
cast function is usually used for data type conversions
cast syntax: cast (expression as data _type[(size)]
example:
declare @x int;
set @x=10;
declare @y varchar(40);
set @y=‘palle’;
declare @r varchar(40);
set @r=@x+@y;
-- this code will not work since we can’t concatenate int and
varchar
set @r=cast(@x as varchar(10)) +@y;
--this code will work and @r will get 10palle as its data
Example for convert:
declare @x int;
set @x=20;
declare @y varchar(40);
set @y=‘palle university’;
set @y=@x+@y;
-- this code will not work since we can’t
concatenate int and varchar
set @y=convert(varchar(10),@x) +@y;
--this code will work and @y will get its value as
20palle university
display doj in Indian style from employee
table
Query:Select doj convert(varchar(40),doj,103)
from employees
Output:
Doj Destination datatype

23-10-2011 Indian style:dd/mm/yyyy

21-06-2009

…………………..
 cast and convert function lab-1

• write a query for displaying the concatenated

result of all patient columns data as shown
below(must consider this as 2 assignments
one by using cast and one by using convert
function)?
• ERS:
 group by and having clauses

• using group by clause we can group data

present in a table based on one or more
columns.
• syntax: select
column_names_used_in_group_by_clause_or
_columns_linked_with_aggregate_functions
from table name group by
column1,column2,…..
Using group by clause we can group data based on one or
more columns
Syntax:
select c1 from <Table name>group by c1
NOTE:
All Columns specified in select list must be
present in group by clause

Select c1,c2 from t1 group by c1,c2 

Select c1,c2,c3 from t1 group by c1,c2 
Select c1,c2,Avg(c3) from t1 group by c1,c2 
select bg from patients group by bg FRS

DBE

patient










 having clause
having clause is used to filter records which are
produced by group by clause
as we cannot use aggregate function in where
clause in that place we will use having clause
select bg from patients group by bg having avg(age)>40 FRS


Avg(age)>40
DBE


Avg(age)>40
patient


Avg(age)>40


Avg(age)>40
 group by lab - 1

• select bg, count(*) as ‘count’ from patient

group by bg?
 group by lab-2

• what is the output for the following

query(must show all Intermediate groups
created by dbe)?
select * from patient group by bg
• what is the output for the following
query(must show all Intermediate groups
created by dbe)?
select bg from patient group by bg
 group by lab-3

• identify the output for the following

query(must show all Intermediate groups
created by dbe)?

• identify the output for the following two

queries(must show all intermediate groups
created by dbe where ever required)?
 group by lab-4

• write a Program for displaying all youngest

patients fullname for each blood group?
• ERS:
 group by with having lab-1

• identify the output for the following query?

 subqueries

• a subquery is a query which is usually written

inside insert/update/delete/select statement
• usually inner query or sub query must be a
select statement and outer query can be any
t-sql statement
• sub queries usually used for identifying
unknown value and the same will be
substituted into outer query
 sub queries

non correlated
sub query correlated sub
query
 Non-correlated sub query

• In a non-correlated subquery , the innermost

query is executed first.

Outer query1 (Inner query)

input DBE

Final Output
 non-correlated subquery samples.
• Display fullnames of employees whose bg is
same as fourth patient’s bg.
• Display all employees whose salary is greater
than ‘db’ departments average salary.
• Display all employees whose salary is between
Highest paid ‘.net’ dept employee’s salary and
least paid db dept employee’s salary.
 non correlated sub queries lab-1
• write a query for displaying all patient details
whose age is greater the age of third
patient(pid=3)
• write a query for displaying all patient details
whose bg is same as 6th patient’s bg.
• write a query for displaying all patient details
whose age is not same as 1st patients age and 3rd
patients age and 9th patient’s age
• find the output for the following query?
select * from patient where age=(select age from
patient where pid in (1,3,6))
 correlated sub query

• correlated sub query is a type of sub query

where the inner query depends on outer
query for its results.
 correlated sub query internals

Completely executed
Outer query1 (Inner query)

input DBE

Final Output
 correlated sub query sample
select t1.* from t100 t1 where 1=(select count(*) from t100 t2 where t1.c3<t2.c3)

Where 1 = 10
23 
DBE

When no values are greater than the current value,

then the current value is the highest value

Similarly, when there is only one value

Final greater
result set than the
current value, then the current value is second highest t100 t1

Display the record which is having second highest value

In c3 column
 Correlated sample 2

employee

Display second highest salary

select salary from employee e1 where 1=

(select count(*) from employee e2 where [Link]>[Link])
 Query execution
select salary from employee e1 where 1 =
(select count(*) from employee e2 where [Link]<[Link])

DBE
> [Link]
[Link]

FRS
 correlated sub query

• identify the output for the following query

(and also write detailed analysis for the
same)?
select p1.* from patient p1 where 3=( select
count([Link]) from patient p2 where
[Link]>[Link])
 Joins

• using joins we can fetch data from one or

more tables into a result set.
inner join
Inner joins
Left join

Outer join Right join

joins
Full join
Self join

Cross joins
 inner join

• In inner joins only the matched records(based on

condition) from left side table and right side
table are added to result set
• syntax:
_____left_table_name alias1 inner join/join
right_table_name alias2 on<condition>
 Inner join/ join sample

Patient1 p Bg b

select [Link],[Link],[Link] from patient1 p join bg b on p.bg_id=[Link] DBE

Final result set

ERS
 left outer join
In a left outer join , all the data from left side table will be
included in the result set ,And only the matched records from the
right side table is included to result set .

Wherever there are no match in the right side table , null values
are included in the result set

SYNTAX:

Left_table_name alias _name left outer join right_table_name alias_name on <condition>

 Profession pf Person p

Select pf.* , p.* from profession pf left outer join person p on [Link] = [Link]

Final result set

DBE

In left outer join, all left table data must be added to result set
 right outer join
In a right outer join , all the data from right side table will be
included in the result set ,And only the matched records from the
left side table is included to result set .

Wherever there are no match in the left side table , null values
are included in the result set

SYNTAX:

Left_table_name alias _name right outer join right_table_name alias_name on <condition>

 country c animal a

?? Bear will be added

to resultset or not
Req: Now I would like to display all animal names along with country names if any
matching countries are present
select [Link], [Link] from country c right join animal a on [Link]=[Link]

Final result set DBE

In case of full outer join all data from both the tables will be added to the result set ,
irrespective of the condition, Wherever there are no match , null values are included

Profession pf Person p

Wether these records

are add to resultset or not

Select pf.* , p.* from profession pf full outer join person p on [Link]=[Link]

DBE
 Joining 3 or more tables
Student s City c State sn

 
 
 

select [Link],c.c_name,sn.state_name,cn.country_name from
Country cn
student s join city c on s.city_id=[Link]
join state sn on c.state_id=sn.state_id 

join country cn on cn.country_id=[Link] 
FRS ERS

DBE
 cross join
A cross join with where clause will produce same
result as inner join
A cross join without where clause will produce the
cartesian products of the tables which are involved
in join
We must use cross join keyword for cross join
use where clause for specifying cross join condition
NOTE :
Must not use ON keyword for specifying cross join condition
profession person

 
 
 
  
 

Select p.*,pf.* from person p cross join profession pf
where [Link]=[Link]
DBE FRS
 Cross join without where clause
profession person

Select pf.*, p.* from person p cross join profession pf

 self join

• joining a table to itself called as self join

• we use inner join for self join ( no special
keyword for self join )
• we must use different alias names for same
table while comparing same table with itself
Emp_mgr e1 Emp_mgr e2

select [Link] as 'employee',[Link] as 'manager' from emp_mgr e1

join emp_mgr e2 on e1.mgr_id=[Link]
DBE
Req: display all employee and manager names , where the employee’s experience are
greater than their managers

Emp_mgr e1 Emp_mgr e2

select [Link] as 'employee',[Link] as 'manager' from emp_mgr e1

join emp_mgr e2 on e1.mgr_id=[Link] and [Link]>[Link]
T1 ask students to find output
T2

T1 T2
tables required for joins lab
 inner joins lab-1

• identify the output for the following queries?

 outer joins

• identify the output for the following queries?

 self joins lab-1

• write a query for finding all employee names

whose exp is greater than their managers exp?
• write a query for displaying all employee names
along with their manager names if any (the result
set must also contain kiran)
 stored procedures

• stored procedure is almost same as

function/method in normal programming
languages.
• a stored procedure is a compiled query (query
whose execution plan is cached)
• a sp can take 0 or more inputs and can return
0 or more out puts.
 Types of stored procedures
Input
 Output Input
 Output
sp
sp

Input
 No Output
sp

No Input  Output Output:

sp 1) Using select statements
2) Output parameters
3) Return statement
 No Output Note: we can return only integer values using
No Input return statement
sp
 SP Syntax
Syntax:
Create proc\procedure<proc name>
(
@vn datatype[(size)],
@vn datatype[(size)],
----------------
)
as
begin
------------- any conditional statements/ loops / variables….
T-sql statements(dql\ddl\dml\tcl)
-------------
end
Calling sp:
exec spname_____,________,______............
 Stored Procedure sample
• write a sp with the name getpatient to get all
patient details from table
output

Calling sp:
 Stored Procedure sample 2
• write a sp with the name insertemployee for inserting new
employee into employee table (the sp must take @eid, @fn,
@ln, @age,@sal,@dept and @doj as input parameters)

Calling sp:
 Stored Procedure Lab-1

• write a sp with the name insertpatient for

inserting new patient into patient table (the sp
must take @pid, @fn, @ln, @age and @bg as
input parameters)
 User defined function
• User defined function is similar to a stored
procedure
• User defined function contains set of compiled t-
sql statements (which is similar to stored
procedures)
• User defined functions supports only input
parameters but not output parameters
• An udf can’t contain any t-sql statements which
alters current state of data base.
• User defined function is not allowed to call any
stored procedure
 udf vs sp

SP UDF
Supports input & output parameters Supports only input parameters

Can write any type of sql queries Can’t write sql queries which modifies state of
db ( ex. insert/update/delete/create etc..)

Sp can call UDF Udf can’t call sp

 indexes
• using indexes we can quickly find the information from a table
or from an indexed view
Types of indexes

Clustered index Non-clustered index

clustered index
• In a clustered index , the actual table is stored in the leaf pages of b-tree
[binary tree]
• Only 1 clustered index is possible per table

syntax :-

create clustered index <index_name> on <table_name> (column1,column2..)