An operator is a reserved word or
a character used primarily in an SQL statement's WHERE clause to perform
operation(s), such as comparisons and arithmetic operations.
NOTE: Dual is a dummy table, used for miscellanies operations/calculations
in SQL.
SQL Operators:
- Arithmetic operators (+, - , * , /):
SQL> select 5+6 from dual; // adds numbers
SQL> select 5-6 from dual; // subtracts numbers
SQL> select 5*6 from dual; // multiplies numbers
SQL> select 25/5 from dual; // divides numbers
- Comparison / Relational operators (=,
>, <, >=, <=, <>, !=):
SQL>select * from emp where empsal=5000; // displays
empsal matches 5000
SQL>select * from emp where empsal<5000; // displays
empsal values<5000
SQL>select * from emp where empsal>5000; // displays
empsal values>5000
SQL>select * from emp where empsal<=5000; // displays
empsal values<=5000
SQL>select * from emp where empsal>=5000; // displays
empsal values>=5000
SQL>select * from emp where empsal!=5000; // displays
empsal values!=5000
SQL>select * from emp where empsal<>5000; //
displays empsal values<>5000
- Logical operators ( AND, OR, NOT):
SQL>select * from student where empsal>5000 and
deptname=’sales’;
SQL>select * from student where empsal>5000 or
deptname=’sales’;
- Set Operators (
Ex: Assume tables Set1={1,2,3,4,5,6,7,8,9}
and Set2={1,2,3,4,5}
SQL> Select * from Set1 UNION Select * from Set2;
UNION ALL: It returns
rows from both the queries including the duplicates in the results
SQL> Select * from Set1 UNION ALL Select * from Set2;
INTERSECT: It returns
common rows from both the queries.
SQL> Select * from Set1 INTERSECT Select * from Set2;
MINUS: It returns rows
from first query that are not present in second query.
SQL> Select * from Set1 MINUS Select * from Set2;
SQL Star plus Operators:
BETWEEN … AND
(negation of it is NOT BETWEEN…AND): It is to be specified that inclusive
range of values. Ex: Range 10,20,30,40. Between 10 and 30=10, 20, 30.
SQL> Select
empname,empsal from emp where empsal between 1000 and 10000;
SQL> Select
empname,empsal from emp where empsal not between 1000 and 10000;
IN (negation of it is
NOT IN): This IN operator is to
test the list of values.
SQL> Select
empname,empsal from emp where empjob in (‘manager’);
SQL> Select
empname,empsal from emp where empjob not
in (‘manager’);
IS NULL ( negation of
it is IS NOT NULL): Is to test NULL values.
SQL> Select
empname,empsal from emp where empjob IS NULL;
SQL> Select
empname,empsal from emp where empjob IS NOT NULL;
LIKE (negation of it
is NOT LIKE): Is to search the matching character pattern.
SQL> Select
empname,empsal from emp where empname LIKE ‘S%’; //empname starts with ‘S’
SQL> Select
empname,empsal from emp where empname LIKE ‘%%\_%’ ESCAPE ‘\’;
//displays empname having ‘_’
SQL> Select
empname,empsal from emp where empname NOT LIKE ‘S%’;//empname except ‘S’
Functions in SQL:
Function: It takes arguments as input and returns result as
output.
Types of Functions:
(1) Single
row functions: It manipulates on every row of the table and returns
single result per each row.
Character Functions:
SQL> Select lower
(‘HASZONE’) from dual; // changes to lower case
SQL> Select upper
(‘haszone’) from dual; // changes to Upper case
SQL> Select Initcap
(‘haszone technologies’) from dual; // changes to sentence case
SQL> Select concat
(‘haszone’,’technologies’) from dual; // joints two words
SQL> Select length
(‘HASZONE’) from dual; // calculates number of characters
Number Functions:
SQL> Select
round(123.444,2) from dual; // rounds the number to two decimal nos
SQL> Select trunc(123.444,2)
from dual; // truncate decimal nos
SQL> Select mod(5,2)
from dual; // gives reminder
SQL> Select
pow(2,3) from dual; // gives power of 3 on 2=8
SQL> Select
sqrt(121) from dual; // gives sqrt of 121=11
SQL> Select
abs(-15) from dual; // removes signs and give abs value
Date Functions:
SQL> select months_between(’13-oct-10’,’13-jul-10’) from
emp; // gives of months between=3
SQL> select add_between(’13-oct-10’,3) from emp; // adds
months
SQL> select last_day(’13-oct-10’) from emp; // gives last
day of the month
SQL> select next_day(’13-oct-10’,’friday’) from emp; //
displays when is next Friday
Data Converstion Functions:
Here 9-number,$-dollar indicator,G-group indicator,D-decimal
indicator,L-local currency
To_Char:
SQL>to_char(123456,’$9G99G999D99’)
from dual;//converts numbers as decimal
SQL>to_char(sysdate,’d’)
from dual; // displays day in the week
SQL>to_char(sysdate,’dd’)
from dual; // displays date of the month
SQL>to_char(sysdate,’ddd’)
from dual; // displays day in the year
SQL>to_char(sysdate,’day’)
from dual; // displays week day
SQL>to_char(sysdate,’mm’)
from dual; // displays two digit month
SQL>to_char(sysdate,’month’)
from dual; // displays spelled month
SQL>to_char(sysdate,’yy’)
from dual; // displays last two digits of the year
SQL>to_char(sysdate,’yyyy’)
from dual; // displays year in digits.
SQL>to_char(sysdate,’year’)
from dual; // displays spelled year
(2) Group
row functions: It operates on group of rows and returns single
result for group of rows.
SQL> Select
sum(empsal) from emp; // calculates total salary of employees
SQL> Select avg(empsal) from emp; // calculates average
salary of employees
SQL> Select min(empsal) from emp; // calculates min
salary employee
SQL> Select max(empsal) from emp; // calculates max
salary employee
SQL> Select count(*) from emp; // calculates total number
of rows in emp table
No comments:
Post a Comment