KAMAL SQL Material

  Character functions Ø   Select LOWER (ename) from emp; it displays letters in lowercase Ø   Select UPPER (ename) from emp; it displays letters in uppercase Ø   Select INITCAP (ename) from emp; it displays 1 st letter capital Ø   Select CONCAT (ename, job) from emp; it combines two columns Ø   Select SUBSTR (ename,2) from emp; it displays ename from 2 nd letter Ø   Select SUBSTR (ename,1,3) from emp; it displays ename from 1 st letter to 3 rd letter Ø   Select LENGTH (ename) from emp; Ø   Select ename, INSTR (ename, ‘S’, 2,1) from emp; it displays letter S count from 2 nd position of 1 st S in the string Ø   Select ename, INSTR (ename,’S’,1,2) from emp; it displays letter S count from 1 st position to 2 nd S in the string Ø   Select ename, INSTR (ename, ‘L’, -2,1) from emp; it displays letter L count from Last position of 1 st L in the string Ø   Select LPAD (ename,20.’@’) from emp; it disp...

  Is null, is not null Ø   Select * from emp where comm is null; Ø   Select * from emp where comm is not null; NVL function It   is used to convert null values to given values Ø   Select sal, comm, nvl (comm,0) comm1, sal+nvl (comm,0) net_amount from emp;   NVL2 function nvl(exp1, exp2, exp3) if exp1 is null it written exp3 , otherwise it written exp2. Ø   Select nvl2(comm,100,200) from emp;   ->Select NULLIF (100,200) from dual; it written result as 100 ->Select NULLIF (100,100) from dual; it written result as NULL COALESCE(exp1, exp2, exp3………) ->It written 1 st non null value in the result Ø   Select COALESCE (comm, empno, sal) from emp; Order by ->It can be last of sql statements Ø   Select * from emp order by sal;  for descending order Ø   Select * from emp order by sal desc; for descending order Group by ->It divided into groups , by using this all group function wil...

  Relational or Comparison operators Ø   <, >, =, <=, >= ,Not equal to (<>, !=, ^=)      Select * from emp where deptno=10;      Select * from emp where deptno<>10; this means not equal to 10.       Select * from emp where sal>1500; Logical operators Ø   AND, OR, NOT Ø   Select * from emp where job=’MANAGER’ AND deptno=10; Ø   Select * from emp where job=’MANAGER’ OR deptno=10; Ø   Select * from emp where NOT SAL=0; Between …. And , Not Between ….. And Ø   Select * from emp where sal between 0 and 1500; Ø   Select * from emp where sal not between 0 and 1500; IN , NOT IN Ø   Select * from emp where job in('PRESIDENT','CLERK'); Ø   Select * from emp where job not in('PRESIDENT','CLERK'); Like, Not Like Ø   ‘%’ represents any sequence of zero or more characters Ø   ‘_’ represents single char only in that position Ø   Sel...

  Table restrictions Ø   Table name must be unique in database Ø   Table name should start with letter and can be 1 to 30 characters long only Ø   Max 1000 column allowed in a table Ø   Table names not case sensitive Column restrictions Ø   Column name should start with letter and can be 1 to 30 characters long only Ø   Column name must be unique in Table   Data types in oracle 1.Character datatype 2.Number datatype 3.Date & Time datatype 4.Long, RAW, LongRAW datatypes   Applying comment on a table Ø   Comment on table emp is ‘Employee table’; Ø   Comment on column emp.ename is ‘Employee name’; Ø   To see comment on a table Ø   Select * from user_tab_comments where table_name=’EMP’; ·         Note: Table name should be in upper case ·         Instead of tab we use col for column level comments Ø   Dr...

  SIMPLE SQL NOTES Data Base - Collection of related records and store the data in the form of tables which consists of rows and columns. Creating user steps ->Alter session set “_oracle_script”=true; ->Create user Raj identified by 123456; ->Grant connect, resource to Raj; Change password of user ->Alter user Raj identified by 1234; Dropping user steps ->revoke connect, resource from Raj; ->Drop user Raj; Mainly SQL divided into 5 types 1. DDL (Data Definition language) These are implicit commands no need to commit – Create, Alter, Drop, Truncate,                Rename. 2. DML (Data Manipulation language) These are explicit commands so we must commit – Insert, Update, Delete. 3. TCL (Transaction control language) – Commit, Rollback, Savepoint. 4. DQL/DRL (Data query language/Data Retrieval language) – Select. 5. DCL (Data control language) – Grant, Revoke. ...