Week 4 - Lab Comparison Operators:: Operator Description Example

Week 4 - Lab
Comparison Operators:
Assume variable a holds 10 and variable b holds 20, then:
Operator
=
!=
<>
>
<
>=
<=
!<
!>
Description
Checks if the values of two
operands are equal or not, if yes
then condition becomes true.
operands are equal or not, if values
are not equal then condition
becomes true.
operands are equal or not, if values
are not equal then condition
becomes true.
Checks if the value of left operand
is greater than the value of right
operand, if yes then condition
becomes true.
is less than the value of right
becomes true.
is greater than or equal to the value
of right operand, if yes then
condition becomes true.
Example
(a = b) is not true.

is less than or equal to the value of
right operand, if yes then condition
becomes true.
is not less than the value of right
becomes true.
is not greater than the value of right
becomes true.
(a <= b) is true.
(a != b) is true.
(a <> b) is true.
(a > b) is not true.
(a < b) is true.
(a >= b) is not true.
(a !< b) is false.
(a !> b) is true.
Logical Operators:
Operator
AND
BETWEEN
EXISTS
IN
LIKE
Description
The AND operator allows the existence of multiple
conditions in an SQL statement's WHERE clause.
The BETWEEN operator is used to search for values
that are within a set of values, given the minimum value
and the maximum value.
The EXISTS operator is used to search for the
presence of a row in a specified table that meets
certain criteria.
The IN operator is used to compare a value to a list of
literal values that have been specified.
The LIKE operator is used to compare a value to
similar values using wildcard operators.

The NOT operator reverses the meaning of the logical
operator with which it is used. Eg: NOT EXISTS, NOT
BETWEEN, NOT IN, etc. This is a negate operator.
The OR operator is used to combine multiple
conditions in an SQL statement's WHERE clause.
The NULL operator is used to compare a value with a
NULL value.
NOT
OR
IS NULL
Syntax:
Consider the basic syntax of the SELECT statement as follows:
SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION|EXPRESSION];
AND/OR Clause:
Syntax:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
IN Condition:
It tests whether a value is present in a list of values or subquery.
Syntax:
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);
Eg: select * from student where student_name IN (John, Kevin);

Select * from student where student_name IN (select student_name from student where
dept=30));
BETWEEN Condition:
It tells whether the value of an expression is in an interval defined by two other expressions.
Syntax:
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
Eg: select * from student where age BETWEEN 15 AND 20;

LIKE Condition:
LIKE conditions specify a test involving pattern matching
An underscore (_) in the pattern matches exactly one character in the value.
A percent sign (%) in the pattern can match zero or more characters. The pattern '%' cannot match
a null.
Syntax:
FROM table_name
WHERE column_name LIKE { PATTERN };
Eg: SELECT salary FROM employees WHERE last_name LIKE 'R%';

Exercise:
create table dept(
deptno number(2,0),
dname varchar2(14),
loc
varchar2(13),
constraint pk_dept primary key (deptno));
create table emp(

empno
number(4,0),
ename
varchar2(10),
job
varchar2(9),
mgr
number(4,0),
hiredate date,
sal
number(7,2),
comm
number(7,2),
deptno
number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno)
);
insert into
values(10,
insert into
values(20,
insert into
values(30,
insert into
values(40,
dept
'ACCOUNTING', 'NEW YORK');
dept
'RESEARCH', 'DALLAS');
dept
'SALES', 'CHICAGO');
dept
'OPERATIONS', 'BOSTON');
insert into emp

values(
7839, 'KING', 'PRESIDENT', null,
to_date('17-11-1981','dd-mm-yyyy'),
5000, null, 10
);
insert into emp
values(
7698, 'BLAKE', 'MANAGER', 7839,
2850, null, 30
);
insert into emp
values(
7782, 'CLARK', 'MANAGER', 7839,
2450, null, 10
);
insert into emp
values(
7566, 'JONES', 'MANAGER', 7839,
2975, null, 20
);
insert into emp
values(
7788, 'SCOTT', 'ANALYST', 7566,
to_date('13-JUL-87','dd-mm-rr') - 85,
3000, null, 20
);
insert into emp
values(
7902, 'FORD', 'ANALYST', 7566,
3000, null, 20
);
insert into emp
values(
7369, 'SMITH', 'CLERK', 7902,
800, null, 20
);
insert into emp
values(
7499, 'ALLEN', 'SALESMAN', 7698,
1600, 300, 30
);
insert into emp
values(
7521, 'WARD', 'SALESMAN', 7698,
1250, 500, 30
);
insert into emp
values(
7654, 'MARTIN', 'SALESMAN', 7698,
1250, 1400, 30
);
insert into emp
values(
7844, 'TURNER', 'SALESMAN', 7698,
1500, 0, 30
);
insert into emp
values(
7876, 'ADAMS', 'CLERK', 7788,
to_date('13-JUL-87', 'dd-mm-rr') - 51,
1100, null, 20
);
insert into emp
values(
7900, 'JAMES', 'CLERK', 7698,
950, null, 30
);
insert into emp
values(
7934, 'MILLER', 'CLERK', 7782,
1300, null, 10
);
Queries for Practice:

Practice of select statement:
1. Display Firstname , Lastname together with Alias as Fullname from student table.
2. Display employee name , commission and salary+10*commission as Total from scott.emp
3. Select unique deptno with job from scott.emp.
4. Select all the columns from scott.emp where sal is greater than equal to 1500
5. select only employee name from scott.emp whose employee number is less than 7782;
6. Select employee name from scott.emp where employee number is not equal to 7782;
7. Select employee name from scott.emp whose hiredate is 17th dec 1980.
8.select ename,sal,comm from scott.emp where 1=2;
9. select ename,sal,comm from scott.emp where 2=2;
10. select employee name , salary , commission from scott.emp where employee number is
greater than 0
11. select ename,sal,comm from scott.emp where comm=comm;
12. Select employee name , salary and commission from scott.emp whose salary is less than
equal to commission
13. By using IN operator in the where condition , display employee name , salary and job from
scott.emp where jobs are in 'CLERK','SALESMAN','ANALYST'
14. By using between in where condition display employee name , salary from scott.emp where
salary is between 1000 and 1500
15. Display employeenumber , employee name from scott.emp where employee name starts with
letter M
16. Display employee name , salary , job from scott.emp where job is SALESMAN or
PRESIDENT and salary is greater than 1500

Week 4 - Lab Comparison Operators:: Operator Description Example

Uploaded by

Copyright:

Available Formats

Week 4 - Lab Comparison Operators:: Operator Description Example

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Week 4 - Lab Comparison Operators:: Operator Description Example

Uploaded by

Copyright:

Available Formats

Week 4 - Lab

Checks if the value of left operand

(a > b) is not true.

(a >= b) is not true.

similar values using wildcard operators.

Eg: select * from student where student_name IN (John, Kevin);

Eg: select * from student where age BETWEEN 15 AND 20;

Eg: SELECT salary FROM employees WHERE last_name LIKE 'R%';

constraint pk_dept primary key (deptno));

create table emp(

insert into emp

Queries for Practice:

You might also like