IN: group of values we have to search
NOT IN: opposite to in.
Between: it will search between specific condition.
not between: opposite to between.
like : it will search if and only similar to the condition.
not like: opposite to like
%,_ - >wildcard Character
% - > grp of charcters
_ - > Single Charcters
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(3)
NAME VARCHAR2(30)
DOB DATE
GRADE VARCHAR2(6)
LOCATION VARCHAR2(8)
SQL> select *from student where grade between 9.5 and 10;
SNO NAME DOB GRADE LOCATION
---------- ------------------------------ --------- ------ --------
102 Krupa 18-FEB-22 9.5 AP
104 Loki 20-APR-22 9.6 AP
105 Inusha 23-OCT-22 9.8 AP
SQL> select *from student where grade not between 9.5 and 10;
SNO NAME DOB GRADE LOCATION
---------- ------------------------------ --------- ------ --------
101 Vardhan 17-NOV-22 9 AP
103 Maha 19-MAR-22 8.6 AP
SQL> select *from student where grade>7 and grade<8;
no rows selected
SQL> select *from student where grade>7 and grade<9;
SNO NAME DOB GRADE LOCATION
---------- ------------------------------ --------- ------ --------
103 Maha 19-MAR-22 8.6 AP
SQL> select *from student where grade>7 and grade<8.6;
no rows selected
SQL> select *from student where name in ('Vardhan','Loki');
SNO NAME DOB GRADE LOCATION
---------- ------------------------------ --------- ------ --------
101 Vardhan 17-NOV-22 9 AP
104 Loki 20-APR-22 9.6 AP
SQL> select name,grade from student;
NAME GRADE
------------------------------ ------
Vardhan 9
Krupa 9.5
Maha 8.6
Loki 9.6
Inusha 9.8
SQL> select *from student where grade in ('9.5','9');
SNO NAME DOB GRADE LOCATION
---------- ------------------------------ --------- ------ --------
101 Vardhan 17-NOV-22 9 AP
102 Krupa 18-FEB-22 9.5 AP
SQL> select *from student;
SNO NAME DOB GRADE LOCATION
---------- ------------------------------ --------- ------ --------
101 Vardhan 17-NOV-22 9 AP
102 Krupa 18-FEB-22 9.5 AP
103 Maha 19-MAR-22 8.6 AP
104 Loki 20-APR-22 9.6 AP
105 Inusha 23-OCT-22 9.8 AP
SQL> select *from student where name like "%a";
select *from student where name like "%a"
*
ERROR at line 1:
ORA-00904: "%a": invalid identifier
SQL> select *from student where name like "A%";
select *from student where name like "A%"
*
ERROR at line 1:
ORA-00904: "A%": invalid identifier
SQL> select *from student where name like 'A%';
no rows selected
SQL> select *from student where location like 'A%';
SNO NAME DOB GRADE LOCATION
---------- ------------------------------ --------- ------ --------
101 Vardhan 17-NOV-22 9 AP
102 Krupa 18-FEB-22 9.5 AP
103 Maha 19-MAR-22 8.6 AP
104 Loki 20-APR-22 9.6 AP
105 Inusha 23-OCT-22 9.8 AP
SQL> select *from student where location like 'A_';
SNO NAME DOB GRADE LOCATION
---------- ------------------------------ --------- ------ --------
101 Vardhan 17-NOV-22 9 AP
102 Krupa 18-FEB-22 9.5 AP
103 Maha 19-MAR-22 8.6 AP
104 Loki 20-APR-22 9.6 AP
105 Inusha 23-OCT-22 9.8 AP
SQL> select *from student where NAME like 'A_';
no rows selected
