Arithmetic Operators

Operator Specifies
+ Addition
- Subtraction
* Multiplication
/ Division
() Enclosed operation

• Can be performed while viewing data table or while

manipulating table data with insert, update or delete
operation.
 Arithmetic Operators
 Example: Calculate new salary of staff after rise of 10%
and display it with staff name, old salary and new salary.

Select eid, ename,salary, ”currentsalary”,(salary+salary*0.1)

“new salary” from employee.

Example :Calculate the student marks after add 3 more

marks and display it with new and old marks

select s_id,s_name,s_city,marks "old marks",(marks+3)"new

marks" from student;
Relational Operators
Operator Specifies
= Equals
!= or <> Not equals
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to

It performs comparison among values.

It returns three values only; true, false, unknown
Example

Example: List those staff whose salary is greater than or

equal to 10,000.
select staff_name , staff_initial,
staff_salary from staff where staff_salary>=10000;

Example :list the student whose marks less than 20

or equal to 20.
Logical Operators
AND operator
• To combine two or more conditions in WHERE or
HAVING clause.
• All conditions must be true.
• Example: Find the staff who are from Rajkot and
Salary is greater than 10,000
Select staff_name from staff where
staff_salary>=10000 and staff_city='Rajkot';
Logical Operators
OR operator
• To combine two or more conditions in WHERE or
HAVING clause.
• Any one condition required true.
• Example: Find the staff who are from Rajkot or
Salary is greater than 10,000
select staff_name from staff where
staff_salary>=10000 or staff_address='Rajkot';
Logical Operators
 NOT operator
• It is used to negate the result of any condition or group of
conditions.
• Example : Find the list of whose joining is not there before
1-1-2011
select staff_name
from staff
where not (staff_hiredate>='1-1-2012');
Logical Operators
BETWEEN operator
• Used to select data that belong to some particular
range.
• Example : Find the list of staff whose joining is
between 2010 to 2012
select staff_name from staff where
staff_hiredate between '1-1-2010' and '12-31-
2012';
Logical Operators
• IN operator
• Used to select data that belongs to some particular set
of values.
• It is similar to ‘=‘
• ‘=‘ compares a single value with another single value
• ‘IN’ compare a single value with set of values
• Example : List out staff who are from Rajkot,
Jamnagar and Junagadh.
select staff_name from staff where staff_address in
('Jamnagar','Rajkot','Junagadh');
Character Operators
LIKE operator
• Select rows that contain values similar to a given
pattern.
• This is similar to ‘=‘
• ‘=‘ operator compares for exact matching Pattern
are as followed,
1. %(modulo) allows matching with any string having
any number of characters, including zero.
Display student having city name starting with ‘j’
select * from student where s_city like 'j%';
 Character Operators
2. _ (underscore) allows matching with any single
character.
Select student list whose name have first char as ‘j’
select * from student where s_name like 'j%_';
Example :- Display student whose name content of 8
characters
select * from student where s_name like '________';
Example :-Display student whose name have second
char as ‘u’
select * from student where s_name like '_u%';
Character Operators
• || operator (Concatenation operator):
• It combines two strings.
• It is useful while the displaying the output.
• Example :
select s_name||','||s_city from student;
Here output appears as single column.
Aggregate Function (group Function)
Function Description Query Output
Name
Max(Column Returns maximum values select max(marks) from 30
name) for a given column student;
Min(Column Returns minimum values select min(marks) from 21
name) for a given column student;

Sum(Column Returns sum of all values select sum(marks) from 98

name) for a given column student;
Avg(Column Returns average of all select avg(marks) from 24.5
name) values for a given column. student;
Count(*) Returns number of rows in select count(*) from 10
a table including duplicate student;
and null values
Count(colum Returns number of rows select count(marks) from 4
nname) where column does not student;
contain null values
Numeric Function

Function Description Query Output

Name
Abs(n) Returns absolute value of select abs(-15) from dual; 15
n
Power (m,n) Returns m raised to nth select power(3,2) from 9
power dual;
Round (n,m) Returns n rounded to m select round (15.19,1) from 15.2
places the right of the dual;
decimal point
Trunc (m,n) m truncated to n places to select trunc(15.196,2)from 15.19
the right of a decimal point dual;
Numeric Function
Function Description Query Output
Name
Sqrt(n) Returns square root of n select sqrt(25) from dual; 5
Exp(n) Returns e raised to the nth Select exp(1) from dual; 2.71…
power (e=2.17828123)
Mod (n,m) Returns remainder of n select mod(11,3) from 2
devided by m dual;
Ceil(n) and Returns smallest and select ceil(25.2) from dual; 26
Floor(n) greatest value Select floor(25.2) from 25
dual;
Log (b, n) Return the logarithm of Select log(10,5) from dual .6989700
the n in the base of b 04
Cos,sin,tan Returns trigonometric Select
cosine, sine and tangent cos(3.14),sin(3.14),tan(3.14)
values from dual
Character Function (SQL Function)

Function Description Query Output

Name
Length(str) Returns the number of select length(‘DSTC’) 4
character in ‘str’ from dual;
Lower(str) Converts the string to select lower(‘DSTC’) dstc
lower case from dual;
Upper(str) Converts the string to select upper(‘dstc’) from DSTC
upper case dual;
Initcap(str) Changes the first letter of a select initcap(‘dstc’) from Dstc
word in to capital dual;
Substr(str,pos Returns the part of string select substr(‘I love c program
,length) programming’,10,11) ming
from dual;
Ltrim(str,set) Remove all specified trim select tc
char from left side of the ltrim(‘DstcDiploma’,’Ds’ Diploma
string ) from dual;
Character Function
Function Description Query Output
Name
Rtrim(str,sest) Remove all specified trim select rtrim(‘Dstc Dstc Dipl
char from right side of the Diploma’,’oma’) from
string dual;
Replace(str,fr Looks for the str and select dstc
om_str,to_str) replace the string every replace('dstcdiploma','di computer
time it occurs ploma','computer') from
dual;
Lpad(str,n,str Return str,left paadded select lpad('dstc',10,'*') ******dstc
2) with str2 up to n length from dual;
Rpad(str,n,str Return str,right paadded select rpad('dstc',10,'*') dstc******
2) with str2 up to n length from dual;
Asscii Return to the asccii code of select ascii('a') from dual; 97
a char
Example
• select s_name ,length(s_name) from student;

• select s_name ,upper(s_name) from student;

• select s_name ,lower(s_name) from student;

• select s_name ,initcap(s_name) from student;

Miscellaneous Function
Function Description Query Output
Name
Uid Return total number of us select uid from dual; 5
are current ally logged in

User Return the user name of select user from dual; system
the user currently logged
in
GREATEST Returns the greatest values select 52,30
from the given expression greatest(48,52,17),greatest
(10,29,30) from dual;
LEAST Returns the smallest values select 10
from the given expression least(10,20,50,20,30) from
dual;
Vsize Return the storage size of Select 2,5
expression in sql. vsize(12),vsize(‘india’)
from dual;
Conversion Function

Function Description Query

Name

TO_CHAR Converts numeric and date Select

values to a character string to_char(123456,’09,99,999’) from
value. dual;
select to_char(sysdate, 'dd
month yyyy') from dual;

TO_DATE Converts a valid numeric and select to_date('2 february,

character value to date value 2012','dd month, yyyy') from
dual;

TO_NUMB Converts a character string to a select to_number(‘123456.89’)

ER number format from dual;
 Group By clause

Computer Civil
Engineer Engineer
Group of Engineers

Identifies Computer
Engineers, Civil
Engineers, Electrical
Engineers and
Mechanical Engineer
from these students Electrical Mechanical
Engineer Engineer
Group By clause
• It groups records based distinct values for specified
columns.
• In other words, it creates a group of distinct values
from available records.
• Syntax :
select column1,column2,aggregate function(argument)
from table name
group by column1,column2,…;
Example
• select s_city, sum(marks)"total marks" from student
group by s_city;

Output :-
S_CITY total marks
---------- -----------
junagadh 41
ahemdabad 74
rajkot 46
Example
• select s_branch, sum(marks)"total marks" from student
group by s_branch;

S_BRANCH total marks

---------- -----------
civil 50
mechanical 46
electrical 24
computer 41
 Having clause
Identifies Computer
Engineers using group
by clause

Computer
Engineers
Identifies the Students who
Group of Engineers are working in .NET
Technology only from the
result of group by clause

Having will
be use at that
time
Having clause
• When we want to put a filter data.
• It is used when we want to filters group based data
Difference
between where
with a specified condition.
and having clause
• It must appear within
?? an aggregate function
• Syntax :

Select column1,column2,aggregate
function(argument)
from tablename group by column1,column2,…
having condition;
 Having clause
Identifies Computer
Engineers using group
by clause

Computer
Engineers
Identifies the Students who
Group of Engineers are working in .NET
Technology only from the
result of group by clause

Having will
be use at that
time
 Example
• select s_branch, sum(marks)"total marks" from student group
by s_branch having s_branch='computer';

S_BRANCH total marks

---------- -----------
computer 41
select s_branch, sum(marks)"total marks" from student group by
s_branch having sum(marks)>40;
S_BRANCH total marks
---------- -----------
civil 50
mechanical 46
computer 41