The Operators Supported by SQL*Plus are as under
1) Comparision Operators
2) Logical Operators
3) Arithmetic Operators
4) Operators used to negate conditionsThey are discussed in brief as under.
SQL COMPARISION OPERATORSComparision Operators as the name sujjests are used to test values in SQL statement.
The comparision operators are as under
* Equality (=)
* Non-Equality (<>)
* Greater-than (>)
* Less-than (<) * Greater-than or equal to (>=)
* Less-than or equal to (<=) All Operators Works according to their names.
For example:-
1)SQL> select * from emp
where
emp_name='RAHUL';
->It will select all details of employee whose name is rahul.
2) SQL> select emp_name from emp
where
emp_age > 50;
-> It will list names of those employee whose age is above 50 yrs.
3) SQL> select emp_name from emp
where
emp_job = 'ACCOUNTANT' OR emp_job= 'MUNIM';
->It will list names of those employee whose status is munim or accountant in the company.
->Note, if any of condition satisfies than the record will display as they are joint using or operator(discussed later).
SQL LOGICAL OPERATORS-> Logical operators are those operators that are use SQL keywords to make comparision instead of symbols.
->A Logical operator is used to combine the results of two conditions.
The Logical Operators are AND, OR and NOT.
They also covers
* LIKE
* BETWEEN
* IN
* IS NULL
* ALL and ANY
AND- AND operator display records only when both conditions are satisfied.
eg:
SQL>select * from emp
where
emp_age < emp_name="'SAJID';">The above eg shows rows for those employee whose age is less than 50 yrs and name is sajid
OR- OR operator display records on matching of any condition.
eg:
SQL>select * from emp
where
emp_age < emp_name="'SAJID';">The above eg shows rows for those employee whose age is less than 50 yrs or name is sajid.
NOT- explained under negate operators.
LIKE
->LIKE operator is used to search a character pattern, without knowing exact character value.
->The LIKE operator used wildcard operators to compare a value to similar values.
->There are two wildcards used in conjunction with LIKE operator.
( % ) The percent sign-The percent sign represents Zero, one
or multiple characters.
( _ ) The Underscore-The underscore represents single
number or characters.
->These symbols can be used in combinations.
eg:
1)SQL> select * from emp
where
emp_name LIKE 'A%';
->Finds any name that start with A and continues for any length.
2) SQL>select * from emp
where
emp_name LIKE '%A';
->Finds any name that ends with A.
3) SQL>select * from emp
where
emp_name LIKE '%A%';
->Finds any name that have A in any position.
4) SQL>select * from emp
where
emp_salary LIKE '3___5';
->Finds any value in a five digit number that starts with 3 and end with 5.
5) SQL>select * from emp
where
emp_salary LIKE '_5%5';
->Finds any values that have a 5 in the second position and ends with a 5.
BETWEEN->The BETWEEN operator is an extension of comparision operator as to make more user friendly.
->The BETWEEN operator is used to search for values whose minimum and maximum values are given. Maximum and minimum values are included as a part of search.
-> A Between operator can be used with both character and
numeric datatypes. However, one cannot mix the data types.
eg:
1)SQL>select * from emp
where
emp_salary BETWEEN 5000 AND 10000;
->It searches for those employee whose salary is lies between 5000 and 10000, including the values 5000 and 10000.
IN->The IN operator search the value from a predetermined list, if any of the value matches than row will be displayed.
eg:
1)SQL>select * from emp
where
emp_job
IN ('ACCOUNTANT','PROGRAMMER','OPERATOR');
->It will display all details of those employee whose status in company is either accountant, programmer or operator.Note IN operator is basically use to remove multiple OR operators. The above statement using OR operator will be
emp_job='ACCOUNTANT' OR emp_job='PROGRAMMER' OR emp_job='OPERATOR';
Note here emp_job is repeated three times which is not efficient way of generating query, so using IN operator we can reduce our burden and also make our statement more efficient.
2)SQL>select * from emp
where
emp_depart_no IN ('5','10','15','20');
->It will display details of those employee whose department number is either 5,10,15 or 20.
IS NULL->IS NULL operator is used to compare a value with a NULL value.
eg:
->Let search for the employee who haven't have their email-id
1) SQL> select * fromemp
where
email_id IS NULL;
->It will display records of those employee who haven't created their email-id.
NOTE:- If you tried NULL operator to use with equality operator it will not give proper result.consider above eg. if you modify above statement using equality operator it will display improper result.
email_id = NULL does not find a NULL value. You will be prompted with the message no rows selected even though there are rows with a NULL values.
2)SQL>select * from emp
where
emp_salary IS NULL;
->It will display records of those employee whose pay field is NULL. This is consider as invalid's records and should be deleted immediately.
ALL and ANY
ALL->The ALL operator compares a value to all values in another value set.
eg:
1)SQL>select * from emp
where
emp_salary > ALL
(select emp_salary from emp where emp_job = 'OPERATOR');
->It will compares salary value to see whether it is greater than all salaries of the employee whose status is operator.
2)SQL>select * from emp
where
emp_age <= ALL (select emp_age from emp where emp_state='CA'); ->It will compares age value to see whether it is less than or equal to all employee's age who is staying in CA.
ANY->The ANY operator compares a value to any values in another value set.
eg:
1)SQL>select * from emp
where
emp_salary > ANY
(select emp_salary from emp where emp_job = 'PROGRAMMER');
->It will compares salary value to see whether it is greater than ANY salaries of the employee whose status is programmer.
2)SQL>select * from emp
where
emp_age <= ANY (select emp_age from emp where emp_state='CA'); ->It will compares age value to see whether it is less than or equal to any employee's age who is staying in CA.
SQL ARITHMETIC OPERATORS
->The arithmetic operators are mentioned as under
1) Addition ( + )
2) Subtraction ( - )
3) Multiplication ( * )
4) Division ( / )They work same as we have been taught so far.
->Let consider usage of arithmetic operator by following result table It contains field like rno, stname, std, div, totsub, totmarks.
where rno is roll number, stname is student name, std is standard, div is division, totsub is total subjects and totmarks is total marks.
1) To find percentage of all student
SQL>select rno,stname,(totmarks / totsub) as percentage
from result;
->Here help of division operator is taken to get percentage.
CONCEPT OF ALIASES
Aliases - Aliases is a temporary name assign to the field created at
execution of statement or field already there in a table.
consider the above example, field is created at the execution of statement and the name percentage is aliases for it.
eg:
1)SQL>select ename as employee_name from emp;
->here field is already there in a table and temporary name is created to display field title more user friendly.
->Note aliases is not compulsory on a field it is used just to interface more user friendly with sql statements.
Now back to the discussion of arithmetic operators,
continue with the examples
2)SQL>select prodname as Product_Name,qty as Quantity,
price as Price_per _unit,(qty*price) as Amount
from product
where
prodname = 'SCREW';
->Here query contains aliases Product_Name for prodname, Quantity for qty, Price_per_unit for price and Amount for (qty*price)
->This query display product name, quantity, price and amount for records whose prodname is screw.
->Here Amount is a field which will be created at the time of execution. It uses the multiplication operator.
Similarly we use arithmetic operators according to our requirement but the only point to note is that while using multiple operators operators works according to the precedence.
* and / have higher priority than + and - operator, but we can make priority of + and - higher than * and / by adding parenthesis to the statement.
SQL NEGATE OPERATORS
The Negate Operators reverses the meaning of the logical operators with which it is used.
We have explained some NOT operators which are mentioned as under.
* NOT EQUAL ( <> ) or ( != ).
* NOT BETWEEN
* IS NOT NULL
* NOT LIKE
* NOT IN
NOT EQUAL->Not equal works exactly oposite to the equal operator.
eg:
1)SQL>select * from emp
where
emp_job <> 'ACCOUNTANT';
->Here all records are selected excluding those whose status is accountant in company.
2)SQL>select * from emp
where
emp_salary != 5000;
->Selects all records excluding those whose salary is 5000.
NOTE:- Operator ( <> ) and ( != ) works same.
NOT BETWEEN-> This operator is used negate the between operator.
eg:
1)SQL>select * from emp
where
emp_salary NOT BETWEEN 2000 and 3000.
->It will select all records excluding those whose salary is between 2000 and 3000, including 2000 and 3000.
IS NOT NULL->It will check whether the selected field is not empty(null).
eg:
1)SQL>select * from emp
where
email_id IS NOT NULL;
->Selects records of those employee who have their email-id.
NOT LIKE->The NOT LIKE operator used wildcard operators to compare a value that is not similar.
->It supports both wildcard character that are mentioned in like operators.
eg:
1)SQL> select * from emp
where
emp_name NOT LIKE 'A%';
->Finds any name that do not start with A and continues for any length.
2) SQL>select * from emp
where
emp_name NOT LIKE '%A';
->Finds any name that do not ends with A.
3) SQL>select * from emp
where
emp_salary NOT LIKE '1___0';
->Finds any value in a five digit number that do not starts with 1 and end with 0.
NOT IN->The NOT IN operator search the value that is not mentioned in a predetermined list, if any of the value matches than row will be displayed.
eg:
1)SQL>select * from emp
where
emp_job
NOT IN ('PROGRAMMER','OPERATOR');
->It will display all details of employee excluding those whose status in company is either programmer or operator.
2)SQL>select * from emp
where
emp_depart_no NOT IN ('5','10','15','20');
->It will display details of employee excluding those whose department number is either 5,10,15 or 20.
1) Comparision Operators
2) Logical Operators
3) Arithmetic Operators
4) Operators used to negate conditionsThey are discussed in brief as under.
SQL COMPARISION OPERATORSComparision Operators as the name sujjests are used to test values in SQL statement.
The comparision operators are as under
* Equality (=)
* Non-Equality (<>)
* Greater-than (>)
* Less-than (<) * Greater-than or equal to (>=)
* Less-than or equal to (<=) All Operators Works according to their names.
For example:-
1)SQL> select * from emp
where
emp_name='RAHUL';
->It will select all details of employee whose name is rahul.
2) SQL> select emp_name from emp
where
emp_age > 50;
-> It will list names of those employee whose age is above 50 yrs.
3) SQL> select emp_name from emp
where
emp_job = 'ACCOUNTANT' OR emp_job= 'MUNIM';
->It will list names of those employee whose status is munim or accountant in the company.
->Note, if any of condition satisfies than the record will display as they are joint using or operator(discussed later).
SQL LOGICAL OPERATORS-> Logical operators are those operators that are use SQL keywords to make comparision instead of symbols.
->A Logical operator is used to combine the results of two conditions.
The Logical Operators are AND, OR and NOT.
They also covers
* LIKE
* BETWEEN
* IN
* IS NULL
* ALL and ANY
AND- AND operator display records only when both conditions are satisfied.
eg:
SQL>select * from emp
where
emp_age < emp_name="'SAJID';">The above eg shows rows for those employee whose age is less than 50 yrs and name is sajid
OR- OR operator display records on matching of any condition.
eg:
SQL>select * from emp
where
emp_age < emp_name="'SAJID';">The above eg shows rows for those employee whose age is less than 50 yrs or name is sajid.
NOT- explained under negate operators.
LIKE
->LIKE operator is used to search a character pattern, without knowing exact character value.
->The LIKE operator used wildcard operators to compare a value to similar values.
->There are two wildcards used in conjunction with LIKE operator.
( % ) The percent sign-The percent sign represents Zero, one
or multiple characters.
( _ ) The Underscore-The underscore represents single
number or characters.
->These symbols can be used in combinations.
eg:
1)SQL> select * from emp
where
emp_name LIKE 'A%';
->Finds any name that start with A and continues for any length.
2) SQL>select * from emp
where
emp_name LIKE '%A';
->Finds any name that ends with A.
3) SQL>select * from emp
where
emp_name LIKE '%A%';
->Finds any name that have A in any position.
4) SQL>select * from emp
where
emp_salary LIKE '3___5';
->Finds any value in a five digit number that starts with 3 and end with 5.
5) SQL>select * from emp
where
emp_salary LIKE '_5%5';
->Finds any values that have a 5 in the second position and ends with a 5.
BETWEEN->The BETWEEN operator is an extension of comparision operator as to make more user friendly.
->The BETWEEN operator is used to search for values whose minimum and maximum values are given. Maximum and minimum values are included as a part of search.
-> A Between operator can be used with both character and
numeric datatypes. However, one cannot mix the data types.
eg:
1)SQL>select * from emp
where
emp_salary BETWEEN 5000 AND 10000;
->It searches for those employee whose salary is lies between 5000 and 10000, including the values 5000 and 10000.
IN->The IN operator search the value from a predetermined list, if any of the value matches than row will be displayed.
eg:
1)SQL>select * from emp
where
emp_job
IN ('ACCOUNTANT','PROGRAMMER','OPERATOR');
->It will display all details of those employee whose status in company is either accountant, programmer or operator.Note IN operator is basically use to remove multiple OR operators. The above statement using OR operator will be
emp_job='ACCOUNTANT' OR emp_job='PROGRAMMER' OR emp_job='OPERATOR';
Note here emp_job is repeated three times which is not efficient way of generating query, so using IN operator we can reduce our burden and also make our statement more efficient.
2)SQL>select * from emp
where
emp_depart_no IN ('5','10','15','20');
->It will display details of those employee whose department number is either 5,10,15 or 20.
IS NULL->IS NULL operator is used to compare a value with a NULL value.
eg:
->Let search for the employee who haven't have their email-id
1) SQL> select * fromemp
where
email_id IS NULL;
->It will display records of those employee who haven't created their email-id.
NOTE:- If you tried NULL operator to use with equality operator it will not give proper result.consider above eg. if you modify above statement using equality operator it will display improper result.
email_id = NULL does not find a NULL value. You will be prompted with the message no rows selected even though there are rows with a NULL values.
2)SQL>select * from emp
where
emp_salary IS NULL;
->It will display records of those employee whose pay field is NULL. This is consider as invalid's records and should be deleted immediately.
ALL and ANY
ALL->The ALL operator compares a value to all values in another value set.
eg:
1)SQL>select * from emp
where
emp_salary > ALL
(select emp_salary from emp where emp_job = 'OPERATOR');
->It will compares salary value to see whether it is greater than all salaries of the employee whose status is operator.
2)SQL>select * from emp
where
emp_age <= ALL (select emp_age from emp where emp_state='CA'); ->It will compares age value to see whether it is less than or equal to all employee's age who is staying in CA.
ANY->The ANY operator compares a value to any values in another value set.
eg:
1)SQL>select * from emp
where
emp_salary > ANY
(select emp_salary from emp where emp_job = 'PROGRAMMER');
->It will compares salary value to see whether it is greater than ANY salaries of the employee whose status is programmer.
2)SQL>select * from emp
where
emp_age <= ANY (select emp_age from emp where emp_state='CA'); ->It will compares age value to see whether it is less than or equal to any employee's age who is staying in CA.
SQL ARITHMETIC OPERATORS
->The arithmetic operators are mentioned as under
1) Addition ( + )
2) Subtraction ( - )
3) Multiplication ( * )
4) Division ( / )They work same as we have been taught so far.
->Let consider usage of arithmetic operator by following result table It contains field like rno, stname, std, div, totsub, totmarks.
where rno is roll number, stname is student name, std is standard, div is division, totsub is total subjects and totmarks is total marks.
1) To find percentage of all student
SQL>select rno,stname,(totmarks / totsub) as percentage
from result;
->Here help of division operator is taken to get percentage.
CONCEPT OF ALIASES
Aliases - Aliases is a temporary name assign to the field created at
execution of statement or field already there in a table.
consider the above example, field is created at the execution of statement and the name percentage is aliases for it.
eg:
1)SQL>select ename as employee_name from emp;
->here field is already there in a table and temporary name is created to display field title more user friendly.
->Note aliases is not compulsory on a field it is used just to interface more user friendly with sql statements.
Now back to the discussion of arithmetic operators,
continue with the examples
2)SQL>select prodname as Product_Name,qty as Quantity,
price as Price_per _unit,(qty*price) as Amount
from product
where
prodname = 'SCREW';
->Here query contains aliases Product_Name for prodname, Quantity for qty, Price_per_unit for price and Amount for (qty*price)
->This query display product name, quantity, price and amount for records whose prodname is screw.
->Here Amount is a field which will be created at the time of execution. It uses the multiplication operator.
Similarly we use arithmetic operators according to our requirement but the only point to note is that while using multiple operators operators works according to the precedence.
* and / have higher priority than + and - operator, but we can make priority of + and - higher than * and / by adding parenthesis to the statement.
SQL NEGATE OPERATORS
The Negate Operators reverses the meaning of the logical operators with which it is used.
We have explained some NOT operators which are mentioned as under.
* NOT EQUAL ( <> ) or ( != ).
* NOT BETWEEN
* IS NOT NULL
* NOT LIKE
* NOT IN
NOT EQUAL->Not equal works exactly oposite to the equal operator.
eg:
1)SQL>select * from emp
where
emp_job <> 'ACCOUNTANT';
->Here all records are selected excluding those whose status is accountant in company.
2)SQL>select * from emp
where
emp_salary != 5000;
->Selects all records excluding those whose salary is 5000.
NOTE:- Operator ( <> ) and ( != ) works same.
NOT BETWEEN-> This operator is used negate the between operator.
eg:
1)SQL>select * from emp
where
emp_salary NOT BETWEEN 2000 and 3000.
->It will select all records excluding those whose salary is between 2000 and 3000, including 2000 and 3000.
IS NOT NULL->It will check whether the selected field is not empty(null).
eg:
1)SQL>select * from emp
where
email_id IS NOT NULL;
->Selects records of those employee who have their email-id.
NOT LIKE->The NOT LIKE operator used wildcard operators to compare a value that is not similar.
->It supports both wildcard character that are mentioned in like operators.
eg:
1)SQL> select * from emp
where
emp_name NOT LIKE 'A%';
->Finds any name that do not start with A and continues for any length.
2) SQL>select * from emp
where
emp_name NOT LIKE '%A';
->Finds any name that do not ends with A.
3) SQL>select * from emp
where
emp_salary NOT LIKE '1___0';
->Finds any value in a five digit number that do not starts with 1 and end with 0.
NOT IN->The NOT IN operator search the value that is not mentioned in a predetermined list, if any of the value matches than row will be displayed.
eg:
1)SQL>select * from emp
where
emp_job
NOT IN ('PROGRAMMER','OPERATOR');
->It will display all details of employee excluding those whose status in company is either programmer or operator.
2)SQL>select * from emp
where
emp_depart_no NOT IN ('5','10','15','20');
->It will display details of employee excluding those whose department number is either 5,10,15 or 20.
0 comments:
Post a Comment