SQL operators(AND, OR, LIKE, BETWEEN, IN)

BETWEEN and AND:

 

SQL> select *
  2  from employee
  3  where salary between 14000 and 15000;

FNAME                MINIT                LNAME
-------------------- -------------------- ----------------
SSN                  BDATE     ADDRESS              SEX
-------------------- --------- -------------------- ------
    SALARY SUPER_SSN                   DNO
---------- -------------------- ----------
Varun                ganesh               deva
0236                 30-OCT-98 sediyapu puttur      M
     15000 1230                          1

prjwal               k                    raj
4126                 13-DEC-93 jp nagara banglore   M
     14500 1240                          2

FNAME                MINIT                LNAME
-------------------- -------------------- ----------------
SSN                  BDATE     ADDRESS              SEX
-------------------- --------- -------------------- ------
    SALARY SUPER_SSN                   DNO
---------- -------------------- ----------

pranav               j                    naik
0662                 01-NOV-92 vijay nagar mysore   M
     14000 1250                          3

Thilak               t                    naik
7894                 30-AUG-96 beliyoorkatte puttur M

FNAME                MINIT                LNAME
-------------------- -------------------- ----------------
SSN                  BDATE     ADDRESS              SEX
-------------------- --------- -------------------- ------
    SALARY SUPER_SSN                   DNO
---------- -------------------- ----------
     15000 1260                          4

 

  1. Select employee detail with fname either varun or thilak

 

SQL> select *
  2  from employee
  3  where fname='Varun' or fname='Thilak';

FNAME                MINIT                LNAME
-------------------- -------------------- ---------------
SSN                  BDATE     ADDRESS              SEX
-------------------- --------- -------------------- -----
    SALARY SUPER_SSN                   DNO
---------- -------------------- ----------
Varun                ganesh               deva
0236                 30-OCT-98 sediyapu puttur      M
     15000 1230                          1

Thilak               t                    naik
7894                 30-AUG-96 beliyoorkatte puttur M
     15000 1260                          4

FNAME                MINIT                LNAME
-------------------- -------------------- ---------------
SSN                  BDATE     ADDRESS              SEX
-------------------- --------- -------------------- -----
    SALARY SUPER_SSN                   DNO
---------- -------------------- ----------



 

Retrieve a employee with lname containing with letters ‘a’.

 

SQL> select lname
 	 2  from employee
  	3  where lname like '_%a%';

LNAME
--------------------
deva
raj
naik
naik

 

2)Select all the employees who born between 1990 and 1999 including the year

 

SQL> select* 
  2  from employee
  3  where bdate like '__-___-9_';

FNAME                MINIT                LNAME
-------------------- -------------------- ----------------
SSN                  BDATE     ADDRESS              SEX
-------------------- --------- -------------------- ------
    SALARY SUPER_SSN                   DNO
---------- -------------------- ----------
Varun                ganesh               deva
0236                 30-OCT-98 sediyapu puttur      M
     15000 1230                          1

prjwal               k                    raj
4126                 13-DEC-93 jp nagara banglore   M
     14500 1240                          2

FNAME                MINIT                LNAME
-------------------- -------------------- ----------------
SSN                  BDATE     ADDRESS              SEX
-------------------- --------- -------------------- ------
    SALARY SUPER_SSN                   DNO
---------- -------------------- ----------

pranav               j                    naik
0662                 01-NOV-92 vijay nagar mysore   M
     14000 1250                          3

Thilak               t                    naik
7894                 30-AUG-96 beliyoorkatte puttur M

FNAME                MINIT                LNAME
-------------------- -------------------- ----------------
SSN                  BDATE     ADDRESS              SEX
-------------------- --------- -------------------- ------
    SALARY SUPER_SSN                   DNO
---------- -------------------- ----------
     15000 1260                          4

 

  1. Select the manager detail who started his work as manager in the year 1998

 

SQL> select * 
  2  from department
  3  where mgrstartdate like '__-___-98';

DNAME                       DNO     MGRSSN MGRSTARTD
-------------------- ---------- ---------- ---------
Accounts                      1       1234 30-OCT-98