Krishna Logo
CLICK HERE TO SEE THE LOCATIONS
Divied
Ria / Kumar @ 1-(877) 864-8462

 

Loading
Latest News
Home Navigation Divied
SQL/UNIX Navigation Divied SQL
Showing 1 - 10 of 20 Previous | 1 | 2 | Next
SQL
1

CLICK HERE TO DOWNLOAD THE FILE

 
DRL - SELECT 
DDL - CREATE, ALTER, TRUNCATE, RENAME, RECYCLEBIN, FLASHBACK, PURGE, DROP
 
SELECT * FROM EMP;
 
CREATE TABLE STUD (ROLLNUM NUMBER(10), NAME VARCHAR2(20), MARKS NUMBER(3,2));
 
SELECT * FROM STUD;
 
ALTER TABLE STUD ADD CITY VARCHAR2(10);
 
CREATE TABLE EMP_DUMMY AS (SELECT * FROM EMP);
 
SELECT * FROM EMP_DUMMY;
---------------------------------------------------
 
Truncate:
--------
it deletes the data in the table permanantly 
 
sytax:
 
truncate table table_name
 
truncate table EMP_DUMMY;
 
Rename:
------
 
It helps to change the name of the table or object
 
syntax:
 
rename old_name to new_name
 
rename EMP_DUMMY to EMP_NEW;
 
Drop:
-----
it is used to delete the table or object from the database
 
syntax:
 
drop table table_name
 
drop table emp_new;
 
Recyclebin:
-----------
 
show recyclebin;
 
flashback:
----------
helps restoring items from Recyclebin
 
Purge:
-----
deletes the items from the recyclebin
 
purge recyclebin
 
-------------------------------------------------
desc stud;
 
DML - Data manipulation Language
--------------------------------
 
Insert:
------
 
it helps to enter data into the table
 
syntax:
insert into table_name values (col1_val, col2_val, cal3_val ....);
 
desc stud;
 
Insert into stud values (1, 'Raj', 1, 'Herndon');
 
Insert into stud values (2, 'Ravi', 3.42, 'Manasas');
 
Insert into stud values (3, 'Ram', 4.00, 'DC');
 
insert into stud values (4, 'reston', 5.44, 'Tim');
 
insert into stud(marks, city, rollnum, name) values (4.57, 'Mclean', 5, 'Rob');
 
insert into stud(marks, rollnum, name) values (7.77, 6, 'Sam');
 
insert into stud values (7, 'Sim', null, ' ');
 
select * from stud;
 
create table emp_new as (select * from emp where 1=2);
 
select * from emp_new;
 
insert into emp_new (select * from emp); 
 
Update:
-------
it is used to modify the data in the existing table
 
syntax:
 
update table_name set condition where condition
 
select * from stud;
 
update stud set city = 'HERNDON';
 
update stud set name = 'Tim' where rollnum = 4;
 
Delete:
------
it is used to delete entire row from the table
 
syntax:
------
 
delete from table_name where condition
 
delete from stud where rollnum = 7;
 
select * from stud;
 
delete from stud where name = 'Rob';
 
Difference Between Truncate and Delete:
---------------------------------------
 
Truncate:
1. Data deleted from truncate is permanant
2. we cannot delete specific records
3. Truncated data cannot be retrieved
4. it is permanant transaction which directly effect on the database table
 
Delete:
1. it deletes the entire data this is not permanant deletion
2. We can delete specific rows
3. deleted data can be retrieved(restored)
4. it is temporary transaction which directly effect the database table
 
------------------------------
 
Alias -- empno - emp_num, ename - emp_Name, sal - salary
-----
 
select e.empno*56+89-9+57.5 as "sinsns sindis",
  e.ename as emp_name,
  e.sal as salary
from emp e;
 
select e.empno, e1.empno
from emp e, emp1 e1
 
select emp as e
 
DCL - Data control Language:
----------------------------
grant 
- it will give access to the objects or tables to a user
 
grant on to
 
and 
 
revoke 
- it will remove access to the objects or tables to a user
revoke on from
 
TCL - Transaction Control Language
----------------------------------
 
Commit - it will physically save data into the database which cant be deleted using rollback
Rollback -  it will be used to go back to the first created point - something like 'UNDO'
savepoint - It will be helpful to create multiple savepoints and can always roll back to a specific
            savepoint. - something like system restore points
 
COMMIT - 
-------
 
create table t1 (marks number(4));
 
select * from t1;
 
insert into t1 values(100);
 
insert into t1 values(200);
 
commit;
 
rollback;
 
savepoint s1;
 
insert into t1 values(300);
 
savepoint s2;
 
insert into t1 values(400);
 
savepoint s3;
 
insert into t1 values(500);
 
savepoint s4;
 
select * from t1;
 
rollback to s2;
 
rollback to s1;
 
drop table t1;
 
--------------------------------------------
 
Operators:
---------
 
Numerical Operators
Relational Operators
Logical Operators
 
Numerical Operators:
-------------------
 
 +, -, *, /
 
DUAL: 
----
It a dummy table from oracle, where you cant delete or add anything. This table has 1 row and 1 column
 
select 9 * 10 
from dual
 
select * from emp;
 
select empno as EMP_NUM, 
  ename as ENAME,
  sal + (sal/0.15) as Total_SAL
from emp;
 
Relational Operators:
--------------------
 
 >, <, >=, <=, !=, <>, any
 
 select * from emp;
 
 select * from emp where sal <> 2000;
 
 select * from emp where deptno = any(10,20);
 
 select * from emp where deptno = (10,20); wrong
 
 Logical Operators:
 -----------------
 
 AND, OR, NOT, IN, BETWEEN, LIKE(%)
 
 select * from emp where deptno = 10 or deptno = 20;
 
 select * from emp where job = 'CLERK' and sal < 1000;
 
 select * from emp where deptno in (10,20);
 
 select * from emp where deptno not in (10,20);
 
 select * from emp where job not in ('MANAGER','CLERK');
 
 select * from emp where job <> 'CLERK' and job <> 'MANAGER';
 
 select * from emp where sal between 1000 and 2000;
 
 select * from emp where sal >= 1000 and sal <= 2000;
 
LIKE(%)
 
% - percentile
_ - 1 character
 
select * from emp;
 
select * from emp where ename like ('_U%R');
 
--------------------------------------------------------
 
Functions:
----------
I. Single Row Functions
   A. Character Functions
   B. Nuemeric Functions
   C. Date Functions
   D. Conversion Functions
II. Group Functions
 
A. Character FUnctions
-----------------------
 
1. Length
 
- it gives number of characters in a string
- Gives the size of the string
 
syntax: length('string') or length(column_name)
 
select length('HELLO WORLD') from dual;
 
select length(ename) from emp;
 
select * from emp where length(ename) <= 4;
 
select * from emp where length(ename) > 4;
 
2. UPPER
 
- displays all the letter in the string or column in upper case(Capital Letters)
 
syntax: UPPER('string') or UPPER(column_name)
 
select upper('hello world') from dual;
 
select upper(ename) from emp;
 
select * from emp where ename = upper('smith'); 
 
3. LOWER
 
- displays all the letters in the string or column to be in lower case 
 
syntax: LOWER('string') or LOWER(column_name)
 
select lower('HELLO WORLD') from dual;
 
select ename, lower(ename), job from emp;
 
4. INITCAP
 
- Displays the first letter in the string or column to be in Upper case
 
select initcap('HELLO WORLD') from dual;
 
5. SUBSTR
 
- This is called sub string. 
- is is used to display parts of a string. 
- it is used to retrieve number of characters from a string.
 
syntax: substr('string',m,n)
 
m = starting point
n = number of charcters
 
select * from emp;
 
select ename,substr(ename,3) from emp;
 
select ename,substr(ename,2,3) from emp;
 
select ename,substr(ename,-3,2) from emp;
 
6. INSTR 
 
- This is used for positioning the cursor.
- Moves the cursor to a specific point in the string searching for a character
 
syntax: instr('string','chr',m,n)
 
chr = search charcter
m = starting postion (1 OR -1)
n = number of occarance
 
select INSTR('ABC$ABC','$',1,1) from DUAL;
 
select SUBSTR('ABC$ABC',(INSTR('ABC$ABC','$',1,1)+1)) from DUAL;
 
7. LTRIM
 
- Trims unwanted spaces or charcters from left side of the String.
 
***WELCOME
 
select '***WELCOME' from dual;
 
select Ltrim(ltrim('***Wel*come**', '*'),'*') from dual;
 
eg: 
 
1. welcome
 
2.     welcome
 
select length('welcome') as A, Length('welcome      ') as B,
       length(ltrim(rtrim('welcome      ')))
from dual;
 
 
 
8. RTRIM
 
- Trims unwanted spaces or characters from right side of the string.
 
select RTRIM('***WELCOME****', '*') from dual;
 
select length('welcome'), length('  welcome      '), length(rtrim('  welcome      '))
from dual;
 
select Ltrim(RTRIM('***WELCOME****', '*'),'*') from dual;
 
9. TRIM
 
- removes spaces from right side and left side 
 
select trim('   WELCOME      ') from dual;
 
select trim('**WELCOME$$$') from dual;
 
10. LPAD
 
- Adds charaters to left side
 
11.RPAD
 
- Adds charaters to right side
 
1500.00
 
select lpad('1500.00',10,'$') from dual;
 
Raj_OLD
Ravi_OLD
Ram_OLD
---
Sam_NEW
Sim_NEW
Kom_NEW
 
with name as (
Select ('Raj') A from dual
union
select ('Ravi') from dual
union
select ('Ram') from dual)
select rpad(A,length(A)+4,'_OLD') from name;
 
12. Least
 
- displays the least values in the array selected
 
select least(1,2,4,5,6,7,8, 0.1, -10) from dual;
 
select least(empno,mgr,sal,deptno) from emp;
 
select e.*,least(empno,mgr,sal,deptno) from emp e;
 
13. greatest
 
- displays the greatest value in the array selected
 
select e.*,greatest(empno,mgr,sal,deptno) from emp e;
 
14. NVL
 
- Replaces the null values in a column to a known(or specified) value
 
syntax: NVL(COl_Name,0) => If Col_Name = NULL then 0 else it will display Col_Name value
 
select * from emp;
 
Net_Sal = Sal + Comm
 
display Empno, ename, job, deptno, sal, comm, Net_Sal from emp table for all the employees
 
select empno, ename, job, deptno, sal, comm, sal + NVL(comm,0) as Net_Sal from emp;
 
15. NVL2
 
syntax: 
NVL2(Col,m,n) => If Col is NOT NULL then 'm', If COl is NULL then 'n'
 
select comm, nvl2(comm, 1, 2) from emp;
 
display the employees who got commission
 
select *
from emp e
where nvl2(comm,1,2) = 1;
 
16. Concat
 
It helps to merge multiple strings
 
select concat(ename,job) from emp;
 
select ename||JOB||MGR from emp;
 
17. CASE
 
Syntax:
 
Case when then
when then
Else
END
 
select * from emp;
 
If Sal < 1000 then display 'Low_Sal'
If sal > 1000 and Sal < 2000 then display 'Mid_Sal'
For all other cases display 'Hi_Sal'
 
case sal < 1000 then 'Low_sal', when sal between 1000 and 2000 then 'Mid_sal' else 'Hi_sal' end;
 
select Empno, Ename, Sal,
  ( Case when sal < 1000 then 'Low_sal'
         when sal between 1000 and 2000 then 'Mid_sal'
 else 'Hi_sal' 
    End
  ) 
from emp;
 
select * from emp;
 
select Empno, Ename, Sal, JOB,
  ( Case     when job = 'ANALYST' then 'a'
             when job = 'CLERK' then 'C'
     when job = 'MANAGER' then 'm'
     when job = 'PRESIDENT' then 'P'
     when job = 'SALESMAN' and sal > 1000 then 'S'
    End
  ) as Short_Job
from emp;
 
Display details of all the employees, add another column in the output which should include
the Name of the DEPARTMENT that employee belongs too. Columns is DEPARTMENT
 
Conditions:
If the DEPTNO = 10 then he/she is from a IT DEPARTMENT
If the DEPTNO = 20 then he/she is from a SALES DEPARTMENT
If the DEPTNO = 30 then he/she is from a MARKETING DEPARTMENT
If the DEPTNO = 40 then he/she is from a HR DEPARTMENT
For all the other DEPTNOs display it as 'OTHER'
 
SELECT A.*, (CASE DEPTNO WHEN 10 THEN 'IT DEPARTMENT'
      WHEN 20 THEN 'SALES DEPARTMENT'
  WHEN 30 THEN 'MARKETING DEPARTMENT'
  WHEN 40 THEN 'HR DEPARTMENT'
  ELSE 'OTHER'
END) AS DEPARTMENT
FROM EMP A;
 
18. DECODE
 
- It also works same as if else condition
 
syntax: 
 
decode(col_name, conditon_1, result_1, condition_2, result_2, condition_3, result_3, result_4)
 
If job = Salesman then display Sales
   job = President then display Prsdnt
   job = Clerk then display Clk
   for all other jobs N/A
   
select empno, ename, job, 
  decode(job, 'SALESMAN','Sales', 'PRESIDENT', 'Prsdnt', 'CLERK', 'Clk', 'N/A') as job
from emp;
 
select * 
from emp
order by sal desc;
 
19. Rank ()
 
Marks Rank   Dense_rank   
------------------------------
100     1   1
80 2   2
80 2   2
75 4   3
75 4   3
75 4   3
65 7   4
 
syntax:
 
rank() over (order by hiredate)
 
select ename, hiredate, rank() over (order by hiredate) from emp;
 
select ename, sal, rank() over (order by sal), dense_rank() over (order by sal)  from emp;
 
 
20. dense_rank()
 
select ename, sal, rank() over (order by sal desc), dense_rank() over (order by sal desc) from emp;
 
select * from emp; 
 
select ename, comm, rank() over (order by comm desc nulls last), 
              dense_rank() over (order by comm desc nulls last) 
from emp;
 
21. ROWNUM
 
select * from emp;
 
select e.*, rownum 
from emp e
order by sal;
 
select Empno, sal,
       rownum, row_number() over (order by sal desc nulls last) 
from emp;
 
22. ROW_NUMBER()
 
select e.*,  row_number() over (order by sal)
from emp e;
 
23. REPLACE 
 
- it used to replace existing string by New string 
 
syntax: replace('STRING', 'old_string', 'new_string')
 
select ('This is Bombay') from dual;
 
select replace('This is Bombay', 'Bombay', 'Mumbai') from dual;
 
24. TRANSLATE
 
- it is used to replace existing character by new character
 
select ('walcoma') from dual;
 
select translate('walcoma','a','e') from dual;
 
select translate('This was Krishna Training','w','i') from dual;
 
------------------------------------------------------------------
 
Neumeric Functions:
-------------------
 
25. abs
 
select abs(-100), abs(100) from dual;
 
26. sqrt
 
select sqrt(16), sqrt(100), sqrt(2) from dual;
 
27. Power
 
select power(3,2), power(4,3), power(2,4) from dual;
 
28. sign
 
select sign(-100), sign(789) from dual;
 
29. floor
 
select floor(129.99) from dual;
 
30. ceil
 
select ceil(129.34) from dual;
 
31. trunc
 
select trunc(129.34567,2) from dual;
 
32. round 
 
select round(129.34567,2) from dual;
 
33. mod
 
select mod(8,2) from dual;
 
select mod(9,2) from dual;
 
34. remainder
 
select remainder(8,2) from dual;
 
select remainder(9,2) from dual;
 
select mod(9,3) from dual;
 
select remainder(11,3) from dual;
 
Date Functions:
---------------
 
dd-mon-yy
 
08-feb-13
 
35. ADD_MONTHS
 
syntax: add_months(date, n) 
n -> no of months to add to the date
 
select sysdate, add_months(sysdate,4) from dual;
 
select empno, ename, hiredate, add_months(hiredate,2) from emp;
 
select add_months('08-feb-2013') from dual;
 
select sysdate, add_months(sysdate+10,3) from dual;
 
36. MONTHS_BETWEEN
 
it used to display the number of months between 2 dates
 
select empno, ename, hiredate, trunc(months_between(sysdate, hiredate)) from emp;
 
select empno, ename, hiredate, months_between(sysdate, '04-SEP-2010') from emp;
 
select sysdate from dual;
 
37. next_day
 
select sysdate, next_day(sysdate,'MONDAY') from dual;
 
38. last_day
 
select sysdate, last_day('01-feb-2012') from dual;
 
select hiredate, last_day(hiredate) from emp;
 
--------------------------------------------------------------
 
Conversion Functions:
--------------------
 
1. TO_CHAR - This helps to convert the data into Varchar2 format (Text Format)
2. TO_NUMBER - This help to convert the data into NUmber format 
3. TO_DATE - This converts the text into a date
 
1. TO_CHAR
 
select sysdate, to_char(sysdate, 'DY'), to_char(sysdate, 'DAY') from dual;
 
D - Day of the week
DD - day of the month
DDD - day of the year
DY - First 3 characters from the Day of the week (Sun-Mon)
Day - full name of the day
 
select hiredate, to_char(hiredate, 'DDth') from emp;
 
select hiredate, to_char(hiredate, 'DDsp') from emp;
 
select hiredate, to_char(hiredate, 'DDspth') from emp;
 
y - 2
yy - 12
yyy - 012
yyyy - 2012
 
w - week of the month (1-5)
ww - week of the year (1-52)
mm - month of the year (1-12)
mon - short month (Jan - Dec)
month - full month (January - DEcember)
 
select to_char(sysdate,'year') from dual;
 
select sysdate, to_char(sysdate,'HH') from dual;
 
select sysdate, to_char(sysdate,'MI') from dual;
 
select sysdate, to_char(sysdate,'SS') from dual;
 
select sysdate, current_timestamp from dual;
 
select empno, ename, to_char(hiredate, 'YYYY') from emp;
 
2. TO_NUMBER
 
it converts the text fields to number datatype
 
select to_number('10'), '10' from dual;
 
3. TO_DATE
 
It converts the text fields in to DATE datatype
 
to_date('date','date format')
 
select '02-feb-2012',to_date('02-feb-2012'), to_date('feb-02-2012','mon-dd-yyyy'),
       to_date('2012-feb-02','yyyy-mon-dd'),
  to_char(to_date('2012-feb-02','yyyy-mon-dd'),'yyyy/dd/mm')
 from dual;
 
select '02-feb-2012',to_date('02-feb-2012'), 'feb-02-2012', to_date('feb-02-2012','mon-dd-yyyy'),
  to_char(to_date('feb-02-2012','mon-dd-yyyy'),'mon-dd-yyyy'),
  to_char(to_date('feb-02-2012','mon-dd-yyyy'),'ddth-mon-yyyy')
from dual;
 
oracle default format: dd-mon-yyyy
                       mon-dd-yyyy
 
-----------------------------------------------------------
 
GROUP FUNCTIONS
---------------
 
max(), min(), sum(), avg(), count()
 
select max(sal), min(sal), sum(sal), avg(sal), count(*) from emp;
 
select deptno,ename, max(sal) 
from emp
group by deptno, ename;
 
select * from emp;
 
display the sum of salary of each job type
 
select job, sum(sal) 
from emp 
group by job;
 
-------------------------------------
 
Clauses:
--------
 
Select
Distinct
from
where
group by
order by
having 
 
Distinct:
 
- deletes the duplicate records and displays the unique records
 
select distinct deptno
from emp;
 
select distinct to_char(hiredate, 'yyyy')
from emp;
 
Having:
 
- It is used to filter the grouped data 
 
select deptno, max(sal), min(sal), count(empno)
from emp
where deptno between 10 and 20
group by deptno
having count(empno) >= 5;
 
select deptno, count(*)
from emp
group by deptno
having count(*) > 1;
 
ORDER BY
 
select * 
from emp
order by deptno, ename, sal;
 
select empno, ename, job, deptno
from emp
order by 4, 2;
 
Clauses Usage Order:
---------------------
 
select empno, max(sal)
from emp
where ename = 'SMITH'
group by empno;
 
select - from - where - group by - having - order by
 
or
 
select - from - where - having - group by - order by
 
-----------------------------------------------
 
Database Constraints:
--------------------
 
Primary Key
- It doesnt accept any duplicates and Null Values
- One table has only one primary key
 
Unique
- It doesnt accept any duplicates
- It can accept any number of NULL Values
- Any number of unique columns can be created in a table
 
Check
- It specifically searches for a specified values
 
Foreign Key
- It used to maintain the relationship between database tables
 
NOT NULL
- This database constraint will not allow NULLS values in the column
 
select * from emp;
 
drop table t3;
 
create table t3 (sno number(10) primary key, name varchar2(10), 
                 gender varchar2(1), check (gender in ('m','f')));
 
insert into t3 values (1, 'raj', 'm');
 
insert into t3 values (2, 'Ravi','f');
 
insert into t3 values (3, 'Ram', 'M');
 
insert into t3 values (NULL, 'Ram');
 
select * from t3;
 
desc t3;
 
select * from emp;
 
select * from dept;
 
create table t4 (sno number(10) primary key, 
                 name varchar2(10) not null, 
                 gender varchar2(1), check (gender in ('m','f')), 
deptno number(5) constraint dept_fk references dept(deptno));
 
desc t4;
 
------------------------------------------------------------------------------
 
Data Merging:
------------
 
Vertical Merging: => Joins
 
Horizontal Merging: => Set Operators
 
 
SET OPERATORS:
--------------
 
UNION, UNION ALL, INTERSECT, MINUS
 
Rule 1:
-------
 
Number of columns from table 1 and table 2 should be same 
 
Rule 2:
------
 
The merging columns should have same datatype
 
UNION
-----
 
select * from emp;
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
 
select * from dept;
(DEPTNO, DNAME, LOC)
 
select empno, ename, job from emp
union
select deptno, dname, LOC from dept;
 
select * 
from emp
where deptno in (30) and comm is not null
union
select * 
from emp
where deptno = 20;
 
query_1 => 5 = 3
union
query_2 => 7 = 3
 
select * from emp
union
select * from emp
 
select * from emp
union all
select * from emp
 
select * from emp where deptno in (10,20)
intersect
select * from emp where deptno in (20,30);
 
select * from emp
minus
select * from emp;
 
create table EMP_TRGT as (select * from emp where deptno in (20,30));
 
select * from emp_trgt;
 
select * 
from emp
where deptno in (20,10)
minus
select * from emp_trgt;
 
select * from emp_trgt
minus
select * 
from emp
where deptno in (20,10);
 
---------------------------------
 
Joins:
 
Cross Join: Displays Cartesian Product of the rows from both the tables
  No conditions are applied on this join
 
select * 
from emp e, dept d;
 
Inner Join: Displays the matching records from both the tables
-----------
 
select *
from emp e, dept d
where e.deptno = d.deptno;
 
select d.*
from emp e inner join dept d on e.deptno = d.deptno;
 
select d.*
from dept d inner join emp e on e.deptno = d.deptno;
 
Equi Join:
---------
 
select * 
from emp e, dept d
where e.deptno = d.deptno and ;
 
Non-Equi Join:
--------------
 
select * from emp;
 
select * from salgrade;
 
select * 
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
 
select * 
from emp e, salgrade s, dept d
where e.sal between s.losal and s.hisal 
      and e.deptno = d.deptno;
 
Left Join or Left Outer Join: Displays all the matching records from both the tables and also 
       displays all the unmatched records from left table.
 All the unmatched records have the NULL values
 
select *
from emp e left outer join dept d on e.deptno = d.deptno;
 
select *
from dept d left outer join emp e on e.deptno = d.deptno;
 
select * 
from emp e, dept d
where e.deptno = d.deptno (+);
 
Right Join Or Right Outer Join: Displays all the matching records from both the tables and also 
       displays all the unmatched records from right table.
 All the unmatched records have the NULL values
 
select *
from emp e right outer join dept d on e.deptno = d.deptno;
 
select * 
from emp e, dept d
where e.deptno (+)= d.deptno;
 
Full Join Or Full Outer Join: Displays all the matching records from both the tables and also 
       displays all the unmatched records from both the tables.
 All the unmatched records have the NULL values
 
select *
from emp e Full Outer join dept d on e.deptno = d.deptno;
 
Self Join: Joining the same table
 
select * 
from emp e, emp e1
where e.empno = e1.empno;
 
select count(*)
from emp
where to_char(hiredate,'yyyy') in (1980,1981);
 
select e1.
from emp e, emp e1, emp e2
where e.empno = e1.empno and e.empno = e2.empno
      and (to_char(e.hiredate,'yyyy') = 1980
 or to_char(e1.hiredate,'yyyy') = 1981
 or to_char(e2.hiredate,'yyyy') = 1982)
 
emp, dept 
 
emp 
(empno, ename, job, sal, deptno)
 
dept
(deptno, dname, loc)
 
LOcation of an employee
 
Equi Join & Non Equi Join
 
Rules: 1.atleast you should have 2 tables
  2. you should have a common column or a matching column 
  
select * 
from emp e, dept d
where e.deptno = d.deptno;
 
emp 
(empno, ename, job, sal, deptno)
 
dept
(deptno, dname, loc)
 
salgrade
(grade, losal, hisal)
 
select * from salgrade;
 
select * from emp;
 
select * 
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
 
select * 
from emp e, salgrade s, dept d
where e.sal between s.losal and s.hisal
      and e.deptno = d.deptno;
 
---------------------
 
Inner Join:
----------
 
create table EMP_New1 as (select * from emp);
 
SELECT * FROM EMP_NEW1;
 
SELECT * FROM DEPT;
 
INsert into emp_new1 values (3123, 'RAJ', 'WAITER',NULL,NULL, 500, 1500, 60);
 
INsert into emp_new1 values (4126, 'RAVI', 'DRIVER',7839,NULL, 1000, 100, 70);
 
SELECT *
FROM EMP_NEW1 E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
 
SELECT * 
FROM EMP_NEW1 E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
 
LEFT OUTER JOIN:
 
SELECT * 
FROM EMP_NEW1 E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
 
SELECT *
FROM EMP_NEW1 E, DEPT D
WHERE E.DEPTNO = D.DEPTNO (+);
 
RIGHT OUTER JOIN:
 
SELECT * 
FROM EMP_NEW1 E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
 
SELECT *
FROM EMP_NEW1 E, DEPT D
WHERE E.DEPTNO (+) = D.DEPTNO;
 
FULL OUTER JOIN:
 
SELECT * 
FROM EMP_NEW1 E FULL OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
 
SELF JOIN:
 
SELECT * 
FROM EMP E, EMP E1, DEPT D
WHERE E.EMPNO = E1.EMPNO AND E1.DEPTNO = D.DEPTNO;
 
SELECT * 
FROM EMP E INNER JOIN EMP E1 ON E.EMPNO = E1.EMPNO
           INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
  INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
  
DESC EMP;
 
SELECT * 
FROM DEPT D LEFT OUTER JOIN EMP_NEW1 E ON E.DEPTNO = D.DEPTNO;
 
SELECT * 
FROM DEPT D, EMP_NEW1 E
WHERE D.DEPTNO = E.DEPTNO (+);
 
SUB QUERYS:
----------
 
Writing a query inside a query is Subquery
 
SELECT - Scalar Subquery 
FROM   - Inline View Subquery
WHERE  - Subquery
 
 
Scalar Subquery:
---------------
 
The output of the subquery should result in only one cell
 
select Max(sal)
from emp
group by deptno;
 
select e.*, (select min(sal)
             from emp) as max_sal
from emp e;
 
select e.empno, 
       e.ename, 
  e.job, 
  e.comm, 
  (select max(hiredate) 
        from emp
        where deptno = 10
        group by deptno) as Max_Date
from emp e;
 
select e.empno, 
       e.ename, 
  e.job, 
  e.comm, 
  (select dname from dept where deptno = 10) as Max_Date
from emp e;
 
Writing a subquery in Where:
----------------------------
 
select *
from emp e
where e.sal not IN (select max(sal) 
                 from emp
group by deptno); 
 
select max(sal) 
from emp
group by deptno;
 
 ;
 
BATCH 122- IN CLASS SQL STATEMENTS-FOR PRACTICE

CLICK HERE TO DOWNLOAD THE FILE   DRL - SELECT  DDL - CREATE, ALTER, TRUNCATE, RENAME, RECYCLEBIN, FLASHBACK, PURGE, DROP   SELECT * FROM EMP;   CREATE TABLE STUD (ROLLNUM NUMBER(10), NAME VARCHAR2(2...  


CLICK HERE TO TAKE YOU TO THE WEBSITE


3TOAD IS NOT ALLOWING YOU TO DELETE FILES ? DELETE THE FILE

 IF THE BACKSPACE KEY IS NOT WORKING IN TOAD PLEASE DELETE THE FILE SHOWIN BELLOW

 

C:\Program Files\Quest Software\Toad for Oracle\User Files\plsqlkeys.bin

 

 


4

1.      Data: are the raw facts and figures which can be recorded.

2.      Information : is the processed data.

3.      Database is a logical collection of related data.

4.      RDBMS - Relational DataBase Management System is a software which manages

5.      Relational Database. In RDBMS data will be stored in tables.

6.      Tables : are the collection of rows and columns.

7.      Row: Row is also known as record. One row contains different type of data

8.      (horizontally) for one object.

9.      Column: column contains same type of data but for different object(vertically).

10.   Key column: Any Column which contains unique value and no null value

11.   and which is useful to identify each row in a table uniquely,

12.   Non-key column: Other than the key column are the non-key columns, non-key columns

13.   can contain duplicate value and null value,

14.   foreign key column: Any column which is dependent on the key column, whose

15.   value should be same as the key column value.

16.   cell/field : intersection of a column and a row,

17.   null: when value is absent it is known as null value.

18.   null is not same as a zero or a blank space, Columns of any datatype can contain nulls

19.   metadata: data about data

20.   data dictionary: system table created and maintained by the RDBMS. Data Dictionary

21.   tables contains metadata. Metadata is useful to user as well as to the RDBMS also.

22.   Database schema: Collection of Objects

23.   Database user: To work in the database username and password is required

24.   In oracle, schema and user shares same name

25.   One user is associated with one schema only.

26.   SQL: Structured Query Language:

27.   ==============================

28.   SQL is a language useful to communicate with Relational Database Management

29.   System.

30.   Sub Languages in SQL

31.   Data Definition Language (DDL)

32.   Data Manipulation Language (DML)

33.   Transaction Control Language (TCL)

34.   Data Control Language (DCL)

35.   DDL (DATA DEFINITION LANGUAGE) STATEMENTS

36.   CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT

37.   DML (DATA MANIPULATION LANGUAGE) STATEMENTS

38.   INSERT, UPDATE, DELETE, SELECT, MERGE

39.   TCL (TRANSACTION CONTROL LANGUAGE) STATEMENTS

40.   COMMIT, ROLLBACK, SAVEPOINT

 

41.   DCL (DATA CONTROL LANGUAGE) STATEMENTS

42.   GRANT, REVOKE

43.   To execute the SQL statements we require the tools, below are some of the tools

44.   where we can execute sql statements.

45.   SQL*Plus -> command based tool comes integrated with oracle software.

46.   iSQLPlus -> browser based tool comes integrated with oracle software

47.   but discontinued after Oracle 10g.

48.   SQL Developer -> Graphical User Interface tool introduced in Oracle 10g but

49.   integrated with oracle software from 11g.

50.   TOAD -> Graphical User Interface tool, but it is a third party tool, is not an

51.   Oracle Corporation tool.

52.   SELECT: it is useful to read/retrieve the data from the database.

53.   Syntax:

54.   SELECT [Unique/distinct/all] column/expression [alias]

55.   FROM table;

56.   DISTINCT/Unique -> useful to eliminate duplicate rows

57.   ALL -> displays all values including duplicates(default)

58.   alias -> gives selected columns different headings.

59.   SELECT empno, ename FROM dept;  -- selecting specific columns

60.   SELECT * FROM emp;  -- selecting all columns

61.   SELECT dname, loc, dname FROM dept;

62.   SELECT ALL deptno FROM emp;

63.   SELECT deptno FROM emp;

64.   SELECT DISTINCT deptno FROM emp;

65.   SELECT DISTINCT COMM FROM EMP;

66.   SELECT deptno, job FROM emp;

67.   SELECT DISTINCT deptno, job FROM emp;

68.   SELECT job, mgr FROM emp;

69.   SELECT DISTINCT job, mgr FROM emp;

70.   SELECT UNIQUE job FROM emp;

71.   Column Alias

72.   ============

73.   Column Alias are the different names given to column heading in the output.

74.   SELECT empno, sal FROM emp;

75.   SELECT empno, sal AS salary FROM emp; --alias

76.   SELECT empno, sal salary FROM emp; --alias

77.   SELECT empno, sal "Monthly Salary" FROM emp; --alias

78.   select ename, sal*12 annual_salary from emp;

79.   SELECT ENAME SAL FROM EMP;

 

80.   Literals:

81.   =========

82.   Literal: are the Values is given by the user(not taken from database)

83.   Three types of literals

a.      Character literal - eg, 'John' and 'JOHN' both are different.

84.   literal values are case sensitive and should be enclosed in single quotes.

85.   SELECT 'employees number is:' FROM emp;

86.   DUAL -> it is a dummy table, it contains one column and one row.

87.   it is useful to display literal vlaues.

88.   it is not useful to display data from the table.

a.      Date literal - ' '-> are the date value given by the user and date values also

89.   should be enclosed in single quotes.

90.   eg, '21-Oct-13'

91.   select '21-oct-13' from dual;

92.   '09/06/2013' -> not accepted as date,

a.      Numeric literal - are the number data given by the user which is not

93.   taken from the database. it should not be enclosed in single quotes.

94.   eg,

95.   select 2000 from dual;

96.   In the above statement 2000 is numeric literal.

97.   100, 100.25, 1.2E5 -> are the examples of numeric literal.

 

98.   Datatypes

99.   =========

100.                    date - is a datatype to store date values

101.                    (01 jan 4712 B.C. to 31 Dec 9999 A.D.), Default format is DD-MON-RR.

102.                    number - is a datatype to store numeric values, it will allow both positive

103.                    value and negative value to store.

104.                    eg, +20000, -20000

105.                    eg, 2000.50

106.                    (precision -> 1 to 38 digits and scale -> -84 to 127)

107.                    salary NUMBER(8,2) -> 8 is precision and 2 is scale.

108.                    char -> is a fixed length character datatype and maximum size 2000 bytes.

109.                    varchar - > is a variable length character datatype and maximum size 2000 bytes.

110.                    varchar2 -> is a variable length character datatype and maximum size 4000 bytes.

111.                    create table stud1(name varchar(10), salary NUMBER(8,2));

112.                    DESC stud1;

113.                    if you enter any decimal value in NUMBER datatype,

114.                    for example: salary NUMBER(4)

115.                    In the salary column if i enter 4.2 or 9.8 -> will round and insert value,

116.                    but do not reject

117.                    number(3,2) -> allows upto 1 digit for integer and 2 digits for decimal

118.                    3 -> precision

119.                    2 -> scale

120.                    Precision range is 1 to 38

121.                    Scale range from -84 to 127

 

---- 21-10-2013 ------

 

122.                    DATABASE CONSTRAINTS

123.                    ====================

124.                    Constraints are the rules which can be enforced on the data.

125.                    Constraints are useful to store consistent data and maintain integrity.

126.                    Constraints are of five types:

127.                    NOT NULL

128.                    CHECK

129.                    UNIQUE

130.                    PRIMARY KEY

131.                    FOREIGN KEY

132.                    Constraints can defined at two levels 1. at column level and 2. table level

133.                    and NOT NULL contraint cannot be defined at table level.

a.      NOT NULL - It will not allow user to enter null values(user should enter

134.                    value mandatorily).

135.                    create table employee(empno number(4),ename varchar2(20) NOT NULL);

136.                    INSERT INTO employee(empno) VALUES(100, 'Aruna'); -- allow

137.                    INSERT INTO employee(empno) VALUES(100);   -- not allow

138.                    INSERT INTO employee(ename) VALUES('Kumar'); -- allow

 

a.      UNIQUE - no duplicates are accepted (we can have null(s) in unique column)

 

139.                    create table employee1(empno number(4),email varchar2(15) UNIQUE); -- column level

 

140.                    INSERT INTO employee1(empno, email) VALUES(100, 'skp@gmail.com');

141.                    INSERT INTO employee1(empno, email) VALUES(101, 'skp@gmail.com'); -- not allow

142.                    INSERT INTO employee1(empno, email) VALUES(100, 'akp@gmail.com'); -- allow

143.                    INSERT INTO employee1(empno, email) VALUES(102, NULL);

144.                    INSERT INTO employee1(empno, email) VALUES(102, NULL); -- allow

145.                    INSERT INTO employee1(empno, email) VALUES(102,' '); -- allow

146.                    INSERT INTO employee1(empno, email) VALUES(102,' '); -- not allow

147.                    INSERT INTO employee1(email) VALUES('a@yahoo.co.in');

 

148.                    Note: Two null values are never be same.

 

149.                    SELECT * FROM employee1;

 

150.                    SELECT * FROM user_cons_columns;

151.                    SELECT * FROM user_constraints;  

 

a.      22/10/2013

b.      example for defining unique constraint at table level

152.                    CREATE TABLE emp_new(empno NUMBER(4), ename VARCHAR2(20), sal NUMBER(8,2),

153.                    UNIQUE(empno, ename));

 

154.                    INSERT INTO emp_new(empno, ename, sal) VALUES(100, 'John', 2000);

155.                    INSERT INTO emp_new(empno, ename, sal) VALUES(101, 'John', 4000);

156.                    INSERT INTO emp_new(empno, ename, sal) VALUES(100, 'John', 6000); -- not allow

157.                    INSERT INTO emp_new(empno, ename, sal) VALUES(100, 'Sanjay', 2000);

158.                    INSERT INTO emp_new(empno, ename, sal) VALUES(NULL,'subash', 2000);

159.                    INSERT INTO emp_new(empno, ename, sal) VALUES(NULL,'subash', 5000);-- not allow

160.                    INSERT INTO emp_new(empno, ename, sal) VALUES(NULL,NULL, 5000);

161.                    INSERT INTO emp_new(empno, ename, sal) VALUES(NULL,NULL, 5000);-- allows

 

162.                    select * from emp_new;

 

163.                    create table emp13(empno number unique not null,

164.                    ename varchar2(20) not null,sal number(7,2) not null);

 

165.                    INSERT INTO emp13 VALUES(101, 'Abeiy', 50000.50);

166.                    INSERT INTO emp13 VALUES(101, 'xyz', 50000.50); -- not allow

167.                    INSERT INTO emp13 VALUES(NULL, 'abc', 60000.50); -- not allow

168.                    INSERT INTO emp13 VALUES(102, 'aaa', 50000.50);

 

a.      PRIMARY KEY = Unique + not null ( no null value, no duplicate value)

b.      only 1 primary key per table

 

169.                    create table emp_pk(empno number(4) primary key,

170.                    ename varchar2(20) not null);

 

171.                    INSERT INTO emp_pk VALUES(200, 'abc');

172.                    INSERT INTO emp_pk VALUES(200, 'xyz'); -- not allow

173.                    INSERT INTO emp_pk VALUES(NULL, 'abc'); -- not allow

174.                    INSERT INTO emp_pk VALUES(201, 'abc');

175.                    INSERT INTO emp_pk VALUES(202); -- not allow

 

 

a.      FOREIGN KEY - references primary key of same table or another table,

176.                    it can have values only from primary key column or it can have nulls, it can

177.                    have duplicate values also,but it should not have any value which is not there

178.                    in the primary key column,

 

179.                    CREATE TABLE dept1(deptno NUMBER(4) PRIMARY KEY, dname VARCHAR2(20));

180.                    INSERT INTO dept1 VALUES(10, 'Accounting');

181.                    INSERT INTO dept1 VALUES(20, 'Marketing

 

182.                    SELECT * FROM dept1;

 

183.                    CREATE TABLE emps(empno NUMBER(4), ename VARCHAr2(20),

184.                    deptno NUMBER(4) REFERENCES dept1(deptno));

 

185.                    INSERT INTO emps VALUES(100, 'skp', 10);

186.                    INSERT INTO emps VALUES(101, 'akp', 30); -- not allow

187.                    INSERT INTO emps VALUES(102, 'san', NULL); -- allow

188.                    INSERT INTO emps VALUES(103, 'ishu', 10); -- allow

 

189.                    select * from dept1;

190.                    select * from emps;

 

191.                    DELETE FROM dept1 WHERE deptno=10; -- not allow

192.                    DELETE FROM dept1 WHERE deptno=20;

 

193.                    two referential integrity it enforce when you define foreign key constraint.

194.                    we cannot insert row in child table when there is no related record is there

195.                    in parent table.

196.                    we cannot delete the row from parent table when there is a dependent row

197.                    in the child table.

 

a.      24/10/2013 ---

 

b.      ON DELETE CASCADE - when deleting parent record - delete all corresponding child records

c.      ON DELETE SET NULL - when deleting parent record - set child record value as null

 

198.                    CREATE TABLE parent_table(eno NUMBER(4) PRIMARY KEY, ename VARCHAR2(20));

199.                    INSERT INTO parent_table VALUES(100, 'John');

200.                    INSERT INTO parent_Table VALUES(101, 'Dilip');

 

201.                    CREATE TABLE child_Table1(eno NUMBER(4) REFERENCES parent_Table(eno) ON DELETE CASCADE,

202.                    salary NUMBER(8,2));

203.                    INSERT INTO child_Table1 VALUEs(100, 15000);

204.                    INSERT INTO child_table1 VALUES(101, 25000);

 

205.                    SELECT * FROM parent_Table;

206.                    SELECT * FROM child_Table1; 

 

207.                    DELETE FROM parent_Table WHERE eno=100;

 

208.                    SELECT * FROM parent_Table;

209.                    SELECT * FROM child_Table1;

 

210.                    CREATE TABLE child_table2(eno NUMBER(4), grade VARCHAR2(20),

                                                    i.     FOREIGN KEY(eno) REFERENCES parent_table(eno) ON DELETE CASCADE);

 

 

211.                    CREATE TABLE t1(sid NUMBER(4) PRIMARY KEY, sname VARCHAR2(20));

212.                    CREATE TABLE t3(sid NUMBER(4) REFERENCES t1(sid) ON DELETE SET NULL,

a.      marks NUMBER(4));

 

213.                    INSERT INTO t1 VALUES(100, 'abc');

214.                    INSERT INTO t1 VALUES(101, 'xyz');

 

215.                    INSERT INTO t3 VALUES(100, 95);

216.                    INSERT INTO t3 VALUES(101, 98);

 

217.                    SELECT * FROM t1;

218.                    SELECT * FROM t3;

 

219.                    DELETE FROM t1 WHERE sid=100;

 

220.                    SELECT * FROM t1;

221.                    SELECT * FROM t3;

 

a.      DEFAULT - can set default value

 

222.                    CREATE TABLE marks(sno NUMBER(4), marks NUMBER(4))

 

223.                    INSERT INTO marks(sno) VALUES(100);

 

224.                    SELECT * FROM marks;

 

225.                    CREATE TABLE marks1(sno NUMBER(4), marks NUMBER(4) DEFAULT 0)

 

226.                    INSERT INTO marks1(sno) VALUES(101);

 

227.                    SELECT * FROM marks1;

 

a.      CHECK - restricts values from a list

 

228.                    CREATE TABLE applicants(a_name VARCHAR2(20), a_age NUMBER(4) CHECK(a_age >=18)

229.                    CHECK(a_age <=60));

 

230.                    INSERT INTO applicants VALUES('san', 15); -- not allow

231.                    INSERT INTO applicants VALUES('san', 25); -- allow

 

232.                    SELECT * FROM applicants;

 

233.                    DUAL Table:

234.                    ==========

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

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

237.                    and can be accessed by all users in the database.

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

239.                    Dual table is used to return a value only once.

 

240.                    SELECT * FROM dual;

241.                    SELECT 'sql class' FROM DUAL;

242.                    SELECT 2000 * 3 FROM DUAL;

243.                    SELECT sal * 12 annual_salary FROM emp;

244.                    select 'Welcome' Greeting from dual;

 

245.                    ---- 25/10/2013 -------------

 

246.                    Concatenation Operator (||)

247.                    ===========================

 

248.                    It is useful to display columns data combindly or it is useful to display

249.                    column data with literal value combindly.

 

250.                    SELECT empno, ename FROM EMP;

251.                    SELECT empno||ename FROM emp;

252.                    SELECT empno||' is an employee number for the employee '||ename  "emp_info"

253.                    FROM emp;

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

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

256.                    select 'ENAME'||JOB FROM EMP;

 

257.                    q(quote) operator

258.                    =================

 

259.                    SELECT 'fathers day' Msg FROM dual;

260.                    SELECT 'father's day' Msg FROM DUAL; -- not work

261.                    SELECT 'father''s day''s''' Msg FROM DUAL;

 

262.                    SELECT q'[ '''''' Welcome to Father's day***%%%$$$$   ]' FROM dual;

 

263.                    Syntax of q operator:

264.                    q' '

265.                    eg,  SELECT q'! '''''sql training''''''' !'  AS text  FROM DUAL;

 

266.                    Comparison Operators (=, <, <=, >, >=, !=, <>, ^= etc.,) -> is useful to

267.                    compare the literal values in the column

268.                    Arithmetic Operators (+, -, *, /) - is useful to calculate the data.

269.                    Logical Operators (And, Or, NOT) - are useful to write multiple conditions.

270.                    IN, Between, LIKE, ANY, ALL - are useful to compare more than one value

271.                    or to compare the values in the column based on few characters

 

272.                    SELECT * FROM emp;

 

273.                    Restricting data

274.                    ================

 

275.                    SELECT * FROM EMP WHERE DEPTNO=10;

 

276.                    SELECT * FROM emp;

 

277.                    SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE SAL>=2000;

 

278.                    SELECT EMPNO, ENAME, job, DEPTNO, SAL FROM EMP WHERE job='SALESMAN';

 

279.                    SELECT * FROM emp;

 

280.                    SELECT EMPNO, ENAME, job, DEPTNO, SAL FROM EMP WHERE job='salesman';

 

281.                    select ename, sal, comm, deptno from emp where sal < 2000;

 

282.                    SELECT * FROM emp;

 

283.                    SELECT * FROM EMP WHERE COMM = NULL;  -- not work

 

284.                    SELECT * FROM emp WHERE comm = ' ' -- not work

 

285.                    SELECT * FROM emp WHERE comm IS null;

 

286.                    SELECT * FROM emp WHERE comm = 0;

 

287.                    SELECT * FROM emp WHERE comm < 0;

 

288.                    SELECT * FROM emp WHERE hiredate < '31-MAR-81';

 

289.                    SELECT * FROM emp WHERE hiredate < 'MAR-31-81'; /* not work coz data value

290.                    is not in a valid format, in where clause date value should be given in

291.                    valid format only */

 

292.                    select * from emp;

 

293.                    select * from emp where comm is not null;

 

a.      29/10/13 --

294.                    SELECT * FROM emp;

295.                    select ename, sal, comm, sal+comm total_sal, deptno from emp where sal<2000;

296.                    Note: when we do arithmetic with null values the output will be null only.

 

297.                    SELECT sal, comm, sal * comm  AS commission  FROM emp;

 

298.                    Logical Operators

299.                    =================

300.                    AND, OR, NOT -> this operators are useful to write multiple conditions in

301.                    the where clause.

 

302.                    SELECT * FROM emp;

303.                    SELECT * FROM EMP WHERE DEPTNO = 20 AND SAL <1400;

 

304.                    Note: When we write two conditions by using AND operator, both the condition

305.                    should be true then only it will display that row, if one condition is false,

306.                    or both the conditions are false or if one condition is null or both the condition is null

307.                    then it will not display that row.

 

308.                    SELECT * FROM emp;

 

309.                    SELECT * FROM EMP WHERE DEPTNO = 20 AND DEPTNO=10;

 

310.                    SELECT * FROM emp;

311.                    SELECT * FROM EMP WHERE DEPTNO = 20 AND DEPTNO!=10;

312.                    SELECT * FROM emp;

313.                    SELECT * FROM EMP WHERE DEPTNO = 20 AND COMM IS NULL;

 

314.                    SELECT * FROM EMP WHERE DEPTNO = 30 AND COMM IS NOT NULL;

 

315.                    OR - When you combine multiple conditions using OR operator then if any one

316.                    condition is true, and other conditions are false or null still it will display that

317.                    row.

 

318.                    SELECT * FROM EMP WHERE SAL=1250 OR COMM IS NOT NULL OR DEPTNO = 10;

 

319.                    SELECT * FROM emp;

 

320.                    select * from emp where job = 'MANAGER' OR JOB='SALESMAN';

 

321.                    IN operator : IN operator is useful to compare multiple values/set of value

322.                    from the same column

 

323.                    SELECT * FROM EMP WHERE JOB IN('MANAGER','SALESMAN');

 

324.                    SELECT * FROM emp WHERE hiredate IN('01-MAY-81','08-SEP-81','10-DEC-85');

 

325.                    SELECT * FROM EMP WHERE JOB NOT IN('MANAGER','SALESMAN');

 

326.                    =any is same as IN operator

327.                    SELECT * FROm emp WHERE job IN('MANAGER','SALESMAN')

328.                    SELECT * FROM EMP WHERE JOB =ANY('MANAGER','SALESMAN');

 

329.                    >any means greater than minimum

330.                    SELECT * FROM emp;

331.                    SELECT * FROM EMP WHERE SAL >ANY(1250,2450);

 

332.                    less than the maximum.

 

333.                    SELECT * FROM EMP WHERE SAL

 

334.                    >ALL means -> greater than the maximum

335.                    SELECT * FROM EMP WHERE SAL >ALL(1250,2450);

 

336.                   

337.                    SELECT * FROM EMP WHERE SAL

 

338.                    =ALL means multiple conditions with AND operator, it is not possible, it

339.                    will display no rows.

340.                    SELECT * FROM EMP WHERE SAL =ALL(1250,2450);

 

341.                    SELECT * FROM EMP WHERE JOB NOT IN ('CLERK','SALESMAN','ANALYST');

 

a.      30/10/2013 ---

 

342.                    *Evaluate Non-null expression first

 

343.                    AND - TRUTH TABLE

344.                    ------------------

345.                    TRUE AND TRUE = TRUE

346.                    TRUE AND FALSE = FALSE

347.                    FALSE AND FALSE = FALSE

348.                    TRUE AND NULL = NULL

349.                    FALSE AND NULL = FALSE

350.                    NULL AND TRUE = NULL

351.                    NULL AND FALSE = FALSE

 

352.                    OR - TRUTH TABLE

 

353.                    TRUE OR TRUE = TRUE

354.                    TRUE OR FALSE = TRUE

355.                    FALSE OR TRUE = TRUE

356.                    FALSE OR FALSE = FALSE

357.                    TRUE OR NULL = TRUE

358.                    FALSE OR NULL = NULL

 

359.                    Between Operator

360.                    ================

361.                    it is useful to compare the values in range.

362.                    SELECT * FROM emp;

363.                    SELECT ENAME, sal FROM EMP WHERE SAL BETWEEN 3000 AND 5000;

364.                    Note: Both the lower bound value and upper bound value will be included.

365.                    SELECT ename FROM emp WHERE sal>=3000 AND sal <=5000;

366.                    SELECT ENAME FROM EMP WHERE SAL not BETWEEN 3000 AND 5000;

367.                    SELECT empno, ename, hiredate, sal from emp where ENAME BETWEEN 'A' AND 'K';

368.                    SELECT * FROM emp;

369.                    SELECT empno, ename, hiredate, sal from emp

370.                    where HIREDATE BETWEEN '30-JUN-1980' AND '09-JUN-1981';

 

371.                    Like Operator

372.                    =============

373.                    is useful to do character patter searching. it is also known as wildcard

374.                    search.

 

375.                    To match pattern

376.                    %(PERCENTILE) -> Represents zero or more characters

377.                    _ (UNDERSCORE)-> Represents one (any) character;

 

378.                    SELECT * FROM emp;

379.                    SELECT * FROM EMP WHERE ENAME LIKE '%S';

380.                    SELECT * FROM EMP WHERE ENAME LIKE 'S%';

381.                    SELECT * FROM EMP WHERE ENAME LIKE '_A%';

382.                    SELECT * FROM EMP WHERE ENAME LIKE '%M%';

383.                    SELECT * FROM EMP WHERE JOB = 'S%'; -- percentile symbol works only with like operator

384.                    CREATE TABLE escape_demo(sno NUMBER(4), text VARCHAR2(20));

385.                    INSERT INTO escape_Demo VALUES(100, 'abc%def');

386.                    INSERT INTO escape_demo VALUES(101, 'abcdef');

387.                    SELECT * FROM escape_Demo;

 

388.                    SELECT sno, text FROm escape_Demo WHERE text LIKE '%/%%' ESCAPE '/'

389.                    SELECT sno, text FROm escape_Demo WHERE text LIKE '%1%%' ESCAPE '1'

390.                    SELECT sno, text FROm escape_Demo WHERE text LIKE '%a%%' ESCAPE 'a'

 

391.                    Sorting Data (Order by)

392.                    =======================

 

393.                    SELECT ename, deptno, sal FROM emp;

394.                    SELECT ename, deptno, sal FROM emp ORDER BY sal;

395.                    SELECT ename, deptno, sal FROM emp ORDER BY sal ASC;

396.                    SELECT ename, deptno, sal FROM emp ORDER BY sal DESC;

397.                    SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY ENAME;

398.                    SELECT hiredate FROM EMP ORDER BY hiredate;

399.                    SELECT hiredate FROM EMP ORDER BY hiredate DESC;

400.                    SELECT job, ENAME FROM EMP O SQL ONLINE CLASS NOTES - LATEST

1.      Data: are the raw facts and figures which can be recorded. 2.      Information : is the processed data. 3.      Database is a logical collection of related ...  


MAKE SURE THAT YOU DOWNLOAD THIS FILE IN VMWARE AND EXTRACT THE ZIP FILE IN THE VMWARE ONLY CLICK HERE TO DOWNLOAD SQL DEVELOPER


CLASS 1 TO CLASS 12 CLICK HERE TO DOWNLOAD   CLASS 12 TO THE END CLICK HERE TO DOWNLOAD THE CLASS FILE  


Data integrity refers to the validity of data, meaning data is consisten...  


  1) Understanding the business transformation rules fromt he e...  


Data redundancy means youre checking for duplication. In order to make t...  


EXAMPLE1 select sal, case when sal < 2000 then 'category 1...  


Showing 1 - 10 of 20 Previous | 1 | 2 | Next
Shadow Bottom
   
© 2005 - 2014 Krishna Training.