--------------- Session 36 --------------
------------------------------------------
Invisible Group Function
Note : in all relational databases we cna also use invisible group functions within having clause because whenever we are using Group func in group by clause queries then database servers internally stores all other group functions results within database.
Query to display dpeart , sum(sal) having more than 3 employye in dept
select dept, sum(sal) from employee group by dept having count(emp) >3; ---here count is invisible group func
ORDER BY CLAUSE :
========================
Order by clause is used to sorting rows either in ascending or descending order. Along with order by clause we are using 2 optional clauses. These are
ASC
DESC
By default its Asc.
select * from table order by column [asc/desc];
Note : in order by clause we can also use alias name or expression.
select ename , sal , sal*12 annsalfrom emp order by annsal desc;
OR
select ename , sal , sal*12 annsalfrom emp order by sal*12 desc;
Order by number;
Note : in order by clause we can also use number in place of column names. But these number represents column position within select list.
select ename , sal from table order by 1;
1 means ename, sal means 2--
All databases Null value treated as highlest values.
NOte : We can also use Null value in order by clasue.In this case Null value treated as highest value if you want to control these Null value within Order by clause then oracle provided Null first / Nulls last clause along with Order by clause
Syntax :
select * from table
order by [asc/desc][nulls first/nulls last];
select ename , comm from emp order by comm desc;
select ename , comm from emp order by comm desc nulls last;
==================
------------------- SEssion 37 ----------------
---------------------------------------------
Note : We can also use more than oacle sorting data based on single colne column within order by clause but in this case orumn. that column must be left first column within order by clause. If that column having duplicate data then that group only sorted in second column.
select deptno, sal from emp order by deptno asc, sal desc;
Syntax of :
======================================
select columns from tbale
where condition
Group by column names
Having Conditon
order by columnname [asc/desc];
=======================================
select deptno , count(*)
from emp
where sal >1000
group by deptno
having count(*)>3
order by deptno;
Order of execution of an Select statement:
==========================================
Parsing
----> Syntax
----> Semantics
Execution
====
From
WHERE
Group BY
HAVING
SELECT
Distinct
ORDER BY
Rollup , Cube :
===============
Oracle 8i introduced rollup , cube clauses. These clauses are optional clauses used along with group by clause only. These clauses are used to calculate subtotal, granttotal automatically.
Syntax :
select col1,col2.... from table group by rollup(col1, col2...);
select col1,col2.... from table group by cube(col1, col2...);
Note : whenever group by clause having single colunmn then Rollup , Cube clauses returns same result.
in this case these clauses returns Grant Total Automatically.
select deptno, sum(sal) from emp group by rollup(deptno);
select deptno, sum(sal) from emp group by cube(deptno);
Generally rollup is used to calculate subtotal based on a single column at a time whereas if we want to calculate subtotal based on all grouping columns then we must use cube. In these two cases Grant total is automatically displayed.
Ex1:
select deptno, job , sum(sal) from emp group by rollup(deptno, job);
select deptno, job , sum(sal) from emp group by rollup(job,deptno);
Ex2:
select deptno,job , sum(sal) from emp group by cube(deptno , job) order by 1,2;
---------------------------Session 38 -------------
---------------------------------------------------
Number Functions :
=================
Number func are operate over number data.
1) abs() : - It is used to convert negative sign into +ve sign.
select abs(-50) from dual; ---50
2) Mod(m,n) : - it will gives reminder after m divide by n.
select mod(10,5) form dual; ---0
it is useful to show Even row , odd row.
3)Greatest (exp1, exp2......expn) , Least (exp1, exp2.....expn) : - Greatest return maximum value among given expression whereas LEast returns minimum value among given expression.
select greatest(3,2,4,6) from dual; -----6
InOracle SQL, the LEAST function returns the smallest value from a list of expressions, but if any of the arguments are NULL, the function will return NULL. To handle NULL values and get a non-NULL result, you can use NVL.
In Oracle SQL, the GREATEST function returns NULL if any of its arguments are NULL. To handle NULL values and get the greatest non-NULL value, use COALESCE or NVL.
Round(m,n) : -It rounds gived floated value number m based on n.
select round(1.7) from dual; ----2 (above 50%)
select round(1.23456,3) from dual; -------1.234
select round(1.23456,3) from dual; ---1.235
Execution:
Step1 :1.234 (here remainign is 56 which is above 50%(out od 100))
Step2 :1.234 added 1 in last 1.235
Round always check remaining number if remaining number is above 50% then automatically 1 added into the rounded number.
select round(1285.674,-1) from dual;
1285.674 ----1290
Execution :
step1 : 1280 ( 5 is replaced with 0.5 is above 50% out of 10 )
step 2 :
1280
+ 1
1290
select round(1485,-2) from dual ; -2 means before decimal 2 number will be zero and those 2 number above 50% then add 1 in 3rd NUMBER
output : 1500
Trunc(m,n) : = it truncates given floated value number m based on n it doesnot check remianing
number is above or below 50%.
select TRUNC(1.9) from dual ---1.9
select TRUNC(1.23456,3) from dual --- 1.234
Ceil() , Floor() :- These fucntions always returns integer.
Ceil return neartest greatest INTEGER
Floor returns neartest lowet integer
ceil(1.4) ---2
floor(1.8)---1
-- JOINS ---
Joins are used to retrivedata from multiple tables.
In all relational databases when we are joining n tables then we must use n-1 joining conditions.
Oracle server having following type of joins. These are
1)Equi join or Inner Joins
2)Non Equi Join
3)Self Join
4)Outer Join
THese joins are also known as ORacle 8i joins along with these joins oracle 9i onwards oracle also supports ANSI standart joins same like all relational databases. These joins are called as 9i joins or ANSI joins
9i or ANSI joins
1) Inner join
2) LEft outer join
3)Right outer join
4) Full outer join
5) Natural Join
--------------- Session 39 --------------
------------------------------------------
if there is no join mention then internally oracle use Cross join LIKE
select * from table1, tbale2...........it will be cross join.
In oracle we can also retreive data from multiple tables without using joining condition. In this case oracle internally uses default join. In Oracle default join is Cross join.
Cross Join internally works based on cartesian product.
Syntax :
select col1, col2...
from tbale1, table2...;
EQUI JOIN :
==========
Based on equallity condition ( by using equality operator )we are retriving data from multiple tables. Here joining conditional columns must belongs to same data type.
When tables having common columns then we can use EQUI Join.
Syntax:
select col1, col2...
from table1, table2....
where table1.col=table2.col; -------this is joining condition.
select ename , deprno from
emp, dept
where emp.deptno=dept.deptno;------throw ambigous column
In all relational database when we are try to display common columns in joins then database servers returns ambiguity error. To overcome this problem for avoiding ambiguity error then we must speicify table name along with commong cloumn name by dot operator.
Syntax:
Tablename.commonvcolumnname;
Note :
Generally for avoiding future ambiguity then we must specify tblaename along withevery column name by using dot operator within select list.
Using Alias Name:
=================
In all relational database for improve performance of the joins then we can also use alias name within join queries that is in this case first we must create alais name ithin from clause thenonly those aliasname must be specify in place of table name within seelct list nd also within joining condition.
Syntax:
from tablename aliasname1
These table alaisname are alsocalled as referrence names fot the tables. These table aliasname must be different names.
Ex :
select ename , d.deptno from emp e, dept d
where e.deptno=d.deptno;
Note:
In oracle EQUI JOIn always returns matching rows only.
NON EQUI JOIN :
==============
Non equi mean other than equal to like < , > ,
BAsed on other than equality operator( < , <= , >, >=, <> , between...)
we are retrving data from mulitple tables.
Ex :
select * from test1 , test2 where test1.deptno>=test2.deptno;
Execution :
=========
same one value from first table compare with all value from second table.
--------------- Session 40 --------------
------------------------------------------
Joins means comparing VALUES
Note :
In oracle whenever one table one column value lies between another table 2 columnsthen also we are retreiving data from those tables by using non equi join.
In this case this join also called as Between....And join.
Ex:
select * from emp , salgrade where sal between lowsal and hisal;
select * from emp, salgrade where sal>=losal and sal<=hisal;
EQUI Join Queries:
==================
1) query to display employee who are wokring in the location Chicago from emp and dept tables by using Equi Join
select ename, loc from employee e , dept d
where e.deptno=d.deptno
d.loc='CHICAGO';
Note:
After joining condiion if you want to filter data then we must use and operator within 8i joins whereas 9i joins we are using eithere AND operator or
we can use where clause also.
2) Query to diaplay dname , sum(sal) form emp, dept tables by using EQUI Join
select dname , sum(sal) from employee e, dept d
where e.deptno=d.deptno
group by dname;
select dname , sum(sal) from employee e, dept d
where e.deptno=d.deptno
group by dname
having sum(sa)>9000;
3) select dname , sum(sal) from employee e, dept d
where e.deptno=d.deptno
group by rollup(dname)
having sum(sa)>9000;
SELF Join :
===========
Joining a table to itself is called self join. Here joining conditional column must belongs to same data type.
Generally in all relational databases self joins are used in following scenarios
1) Compare one table one column any vlaue with all other values in same column.
2) Compare 2 different column values (same datatype) in same table.
Whenwver we are using self join then we must create table alias name within from clause. These alias name must be different names. These alias name internally behaves like exact tables when query execution time.
Syntax:
from tablename aliasname , tablename aliasname2
1) Compare one table one column any vlaue with all other values in same column.
Query to display employess who are getting same salary as scott sal from employee table by using SELF JOIN
select
from emp e1, emp e2
where e1.ename='SCOTT'
and e1.sal=e2.sal;
Query to display those doctors which are workginin same hospital wiht differetnt sepciality
from hospital h1 , hospital h3
where h1.hospitalname=h2.hostipalname
and h1.specilaity<>h2.speciality
order by h1.id;
