Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Thursday, 20 July 2017

SQL is NULL AND is NOT NULL

0 comments

What is Null values:


SQL NULL is an undefined and incomparable value.
It is not equal to zero or space.
It will not occupy any memory.
It is represented by NULL keyword and displayed as space.
It is a standard value supported by every RDBMS tool defined by EF code.
It provides unique treatment for all types of data any athematic operation with null values gives null.

Is null/is not null:


Used to compare null values
Supports with all types of data

The following statement List the employees who are not having commission.

SELECT ename, sal, comm
  FROM emp
 WHERE comm IS NULL;

The following statement List the employees who are having commission.

SELECT ename, sal, comm
  FROM emp
 WHERE comm IS NOT NULL;

The following statement Assign job as executive if job is null.

UPDATE emp
   SET job = 'EXECUTIVE'
 WHERE job IS NULL;

The following statement Assign dept as 10 if deptno is null.

UPDATE emp
   SET hiredate = '19-NOV-2011'
 WHERE hiredate IS NULL;

The following statement removing a column where comm having 5000

UPDATE emp
   SET comm = NULL
 WHERE comm = 5000; 

SQL is NULL or empty


The following statement returns values where commission having NULL or EMPTY

SELECT *
  FROM emp
 WHERE (comm IS NULL OR comm = '');


SQL is NOT NULL or empty


The following statement returns values where commission having NOT NULL or EMPTY

SELECT *
  FROM emp
 WHERE (comm IS NOT NULL OR comm = '');


SQL is NULL or 0(zero)


The following statement returns values where commission having NULL or 0(ZEO)

SELECT *
  FROM emp
 WHERE (comm IS NULL OR comm =0);



You May Like                                             
                            SQL IN Operator
                            SQL Like Operator
                            SQL Between Operator                       



Continue reading >>

Wednesday, 19 July 2017

SQL Between Operator

0 comments

Between Operator use to pick the values with the specified range.
It will Supports with numbers and data values.
Between includes range limits in output.
Not between excludes range limits from output.


SQL Between Syntax


SELECT column1,column2...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;


Example

The following statement returns employee salary who having 10000 and 20000

SELECT *
  FROM emp
 WHERE sal BETWEEN 10000 AND 20000;


Example


The following statement update employee salary 35% who joined between
'1 -JAN-10' and '31-DEC-10'

UPDATE emp
SET Sal = sal + sal * .35
WHERE hiredate BETWEEN '1 -JAN-10' and '31-DEC-10';



SQL Not Between



SQL NOT Between Syntax




SELECT column1,column2...
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;



Example

The following statement returns employee salary who not having 10000 and 20000

SELECT *
  FROM emp
 WHERE sal NOT BETWEEN 10000 AND 20000;



Example


The following statement update employee salary 15% who not joined between
'1 -JAN-10' and '31-DEC-10'

UPDATE emp
SET Sal = sal + sal * .15
WHERE hiredate NOT BETWEEN '1 -JAN-10' and '31-DEC-10';



You May Like                                             
                            SQL IN Operator
                            SQL Like Operator                         
                            SQL is NULL AND is NOT NULL



Continue reading >>

Monday, 17 July 2017

SQL Like Operator

0 comments

SQL like used to search for a pattern in given input supports with character data only.

It uses 2 special character (Meta characters).
(i)                % --->represents zero or more characters.

(ii)             – (underscore) represents one character.



Syntax for SQL Like



SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;


Examples

The following statement List the employees whose name begins with ‘s’


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE 's%';
             

The following statement List the employees whose name ends with ’s’


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE '%s';

The following statement List the employees whose name ends with ’S’(Capital),’s’(Small)


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE '%s' OR ename LIKE '%S';

The following statement List the employees whose name begins and ends with ‘s’


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE 's%s';


The following statement List the employees whose name got 5 letters


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE '-----';


The following statement List the employees whose name got 2nd letter as ‘i’


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE '_i%';

The following statement List the employees whose name got ‘LL’


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE '%LL%';


Searching for Meta characters

Using escape option we can search for Meta characters in given input.

List the employees whose name got ‘_‘(underscore)


SELECT ename
  FROM emp
WHERE ename LIKE '%\_%' ESCAPE '\';

Output

ANIL_KUMAR
KIRAN_KUMAR
SANDEEP_REDDY

List the employees whose name got ‘%’ symbol


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE '%\%%' escape '\';

Output

ANIL%KUMAR
KIRAN%KUMAR
SANDEEP%REDDY

SQL NOT Like Operator





Syntax for SQL NOT Like



SELECT column1, column2, ...
FROM table_name
WHERE columnN NOT LIKE pattern;


Examples



The following statement List the employees whose name not begins with ‘s’


SELECT ename, sal, job
  FROM emp
 WHERE ename NOT LIKE 's%';
             

The following statement List the employees whose name not ends with ’s’


SELECT ename, sal, job
  FROM emp
 WHERE ename NOT LIKE '%s';

The following statement List the employees whose name not ends with ’S’(Capital),’s’(Small)


SELECT ename, sal, job
  FROM emp
 WHERE ename NOT LIKE '%s' OR ename NOT LIKE '%S';

The following statement List the employees whose name not begins and not ends with ‘s’


SELECT ename, sal, job
  FROM emp
WHERE ename  NOT LIKE 's%s';



You May Like                                             
                            SQL IN Operator                           
                            SQL Between Operator
                            SQL is NULL AND is NOT NULL    

Continue reading >>