nulls, dual

    

    NULL - accepts nulls in column where specified db constraint NULL

          (its by default)

  DROP TABLE emp_null;

  CREATE TABLE emp_null(empno NUMBER(4) NOT NULL, ename VARCHAR2(20));

 

  INSERT INTO emp_null(empno) VALUES(501);

 

SELECT * FROM emp_null;

 

  INSERT INTO emp_null VALUES(502,NULL);

 

    INSERT INTO emp_null VALUES(502,'NULL');

   

        INSERT INTO emp_null VALUES(NULL,'john'); -- will give error

 

  SELECT * FROM emp_null; 

SELECT 'ITAMERICA' NAME FROM EMP;

SELECT 'ITAMERICA' NAME FROM DUAL;

DUAL Table:

==========

is a dummy table that you can use to view literal values,

results from functions and calculations. This table is owned by SYS user

and can be accessed by all users in the database.

It contains one column, DUMMY, and one row with value X.

Dual table is used to return a value only once.

SELECT * FROM dual;

SELECT 'Welcome','itamerica' FROM DUAL;

SELECT 1,2,3,4,5,6,7,8,9 FROM emp;

SELECT empno, ename FROM emp9;

SELECT 'Welcome' FROM emp;

SELECT 'Welcome' FROM DUAL;

SELECT 'Welcome' Greeting FROM dual;

SELECT 538*764 FROM DUAL;

SELECT 538*764 FROM dept;

Concatenation Operator (||)

===========================

SELECT ename, job FROM emp;

SELECT ENAME, JOB, ENAME||JOB AS ename_job FROM EMP;

SELECT ename, SAL, ename||sal FROM emp;

SELECT ename, sal, ename||' '||sal FROM emp;

SELECT ename||' earning salary $'||sal AS emp_info FROM emp;

SELECT ENAME||' '||JOB FROM EMP;

SELECT ENAME||' is working as : '||job DESCRIPTION from emp;

SELECT 'ENAME'||JOB FROM EMP;

q(quote) operator

=================

SELECT 'father's day' FROM DUAL;

SELECT 'father''s day' FROM DUAL;

SELECT 'father'''''''''''''s day' FROM DUAL;

SELECT q'!father's day!' FROM DUAL;

SELECT q'!father'''''''s day   !' FROM DUAL;

SELECT q'[Welcome to Father'''''''''''''''!!!!!!!!!*%^s day]' FROM dual;

SELECT q'^  Welcome to Father'''''''''''''''!!!!!!!!!*%^s day  ]' FROM dual; -- gives error

SELECT q'! '   dfsdfsf53454545*)*)&&(&*090)*)*)ljlsjdflsdfd*)*)*)*   '!' FROM DUAL;

SELECT q'! dfsdfsf53454545*)*)&&(&*090)*)*)ljlsjdflsdfd*)!'*)*)*   '!' FROM DUAL; -- gives error

SELECT q'! dfsdfsf53454545*)*)&&(&*090)*)*)ljlsjdflsdfd*)'!*)*)*   !' FROM DUAL;

syntax:

q'<delimiter> string/character <delimiter>'

Note: for delimiter we can use any symbol, but both side symbol should be same.

OPERATORS:

----------

Relational Operators (<, <=, >, >=, !=, <>, ^= etc.,)

Arithmetic Operators (+, -, *, /)

Comparison Operator (=)

Logical Operators (And, OR, NOT)

IN, Between, LIKE, ANY, ALL

Rules of Priority:

------------------

1.  Arithmetic Operators(*, /, +, -)

    1. *, /

    2. +, -

2.  Concatenation Operator(||)

3.  Comparison Conditions(<, >, =)

4.  IS [NOT] NULL, LIKE, [NOT] IN

5.  [NOT] BETWEEN

6.  NOT EQUAL TO[<>]

7.  NOT logical condition

8.  AND logical condition

9.  OR  logical condition

Note: To override this default priority we can use parantheses().

Restricting data

================

SELECT * FROM emp;

SELECT * FROM emp WHERE deptno=10;

SELECT * FROM emp WHERE deptno IN 10;

SELECT * FROM emp WHERE deptno IN (10);

SELECT * FROM emp WHERE job='SALESMAN';

SELECT * FROM emp WHERE job='Salesman'; -- will not display anything

Note: In where clause we should not use column alias.

SELECT empno, ename, sal AS salary FROM emp WHERE salary=3000; -- gives error

Note: in where clause character literals are case sensitive

Note: in where clause all character literals and date literals should be

enclosed in single quotes.

IN WHERE clause date literals are format sensitive.

SELECT * FROM EMP WHERE DEPTNO=10;

default format of the date value is :DD-MON-RR.

SELECT * FROM emp WHERE hiredate > 01-JAN-82; -- will give error

SELECT * FROM emp WHERE hiredate > '01-jan-82';

SELECT * FROM emp WHERE hiredate > 'JAN-01-82';

Note: in where clause date literals are format sensitive.

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE SAL >= 3000;

SELECT EMPNO, ENAME, MGR, DEPTNO, SAL FROM EMP WHERE SAL=3000;

SELECT * FROM emp;

select ename, sal, comm, deptno from emp where comm<400;

SELECT * FROM EMP WHERE COMM = NULL;

Note: One null value is not equal to any other value or to any other null also.

SELECT * FROM emp WHERE comm IS NULL;

SELECT * FROM emp WHERE comm IS NOT NULL;

SELECT sal, comm FROM emp;

select sal, comm, sal+comm AS total_sal from emp;

Note: Whenever we do arithmetic operation with NULL value result will always

be NULL only.

SELECT * FROM DEPT WHERE DEPTNO != 10;

SELECT * FROM DEPT WHERE DEPTNO <> 10;

SELECT * FROM DEPT WHERE DEPTNO ^= 10;

select * FROM dept WHERE DEPTNO >20;

select * FROM dept WHERE DEPTNO >=30;