---------Session 31 ---------------
Query to display employee who joining on TUESDAY using to_char() FUNCTION
select ename from employee where to_char(hiredate , 'fmDAY')='TUESDAY';
select ename from emloyee where to_char(hiredate ,'D')='3';
Note : in oracle whenever we are using to_char() func DAY Format , internally oracle will aloocate 9 bytes maximum memory for DAY field and when we passed day less than 9 bytes then oracle will not return anything . To Overcome this we need to use FM (fill mode) whitin to_char() function.
to_char()
to_date()
Insert date into table.
================================
Note : in oracle whenever we are passing date string into oracle predefined date functions (add_months , last_day, next_day(),months_between(),
...)then we are not required to use to_date() function explicity because in this case orace internally uses implicit convertion but here passed parameter return values must match with default date format retuen values otherwise oracle returns an error.
Ex: (implicit conversion)
select last_day('18-DEC-2025' from dual);
same logic applied in insert also.
Inserting date into Oracle Date Type columns.
create table test (col1 date);
insert into test values(sysdate);
select * from test;
insert into test values('12-Decm-2005');
insert into test values(to_date('15/05/2002' ,'dd/mm/yyyy'));
Syntax :
columnname DATE
in oracle whenever we are insertin dates ito date datatype column then we are not require to use to_date()
func when passed date reyuren values match with default date format return values because in this case oracle internally uses implicit conversion whereas in all other cases we must use to_date() function explicitly in insert statment.
Ex:
insert into test values(to_date('15/05/2002' ,'dd/mm/yyyy'));
-----------------Session 32 ----------------------------------
v.v.v imp
ROUND() , TRUNC() func are used in Dates : ->
Date datatype contains data and time.
In oracle date datatype contains 2 parts these are
1) Date Part
2) Time Part
select to_char(hiredate,'dd-mm-yyyy HH:MM:SS' ) from employee;
Whenever we are using Round() or TRUNC() functions the date part can be changed based on time portion and also time portion is automatically set
to zero.
Whenever we are using Round() function then oracle server check Time portion. if time portion is >=12 noon then oracle will add 1 day into the datepart and also time portion is automatically converted into zero.
Whenever we are using Trunc() func then oracle returns same date part when time portion >=12 noon also and also here time portion is automatically converted into zeros.
Query to display employee who joining today
insert into employee (ename, hiredate) values('a' , sysdate);
select * from employee where horedate=sysdate;
No RECORD
In oracle whenever we are comparing date by using equality operator in where clause then oracle doesnot return proper result because whenever we are comparing dates by using equality operator then oracle server not only compares date part but also internally compares time part.
To overcome this problem if we want to retrieve proper result then we must set time portion to zeros by using Trunc() function.
select * from employee where trunc(hiredate)=trunc(sysdate);
Note : In this case we can also retrive data by using to_date() function without using trunc() function.
select * from employee
Note: In oracle whenever we are inserting sysdate into date datatype column then oracle internally automatically insert current time of the system whereas whenever we are inserting our own date by using to_date() without specifiying any time then oracle internally automatically
insert 0s (00:00:00) hour,min, sec.
Whenever we are inserting time without specifing any date by usnig to_date() then oracle automatically inserts first date of the current month.
------------- Session 33 ---------------
Note : in oracle by usin round(), trunc() func we can also display first date of the year, first date of the month by using following syntax.
Syntax :
round(date,'year')
round(date,'month')
trunc(date, 'year')
trunc(date, 'month')
select sysdate , round(sysdate+200,'year'),trunc(sysdate,'year') from dual;
14-03-25 01-01-26 01-01-25
day means first day of week.
round() will always prev or next based on date above 50% then next else prev.
Ex: Sysdate 17-09-2021
select trunc(sysdate, 'year') from dual;---01-Jan-2021
select trunc(sysdate, 'month') from dual;---01-Sep-2021
select trunc(sysdate, 'day') from dual;---12 Sep 2021 (prev sunday)
5 date func --sysdate, add_month(), last_day() , next_day() , months_between()
Next_day() : It returns next occurence day from the specified date based on the second parameter.
Syntax:
next_day(date,'day')
select next_day(sysdate,'Friday') from dual;
select next_day(sysdate,'wed') from dual;
Months_Between() : -- Returns number
syntax:
Months_between(date1,date2)
date1>date2 --> POSITIVE
date1<date2 -->Negative -- we can use abosule to convert negative to positive.
it reutrns number datatype. it returns number of months between given 2 dates here date1 must be greater than date2 oterwise this function returns negative sign.
If day of date1> day of date2 === > date1-date2/31
if day< date2 ===> (31-date1+date2)
select months_between('13-Dec-08','12-Mar-08') from dual ---9.03225
select round(months_between('13-Dec-08','12-Mar-08') ) from dual; ---9
select round(months_between(sysdate, hiredate)) from dual;
BEfore Group by we have to understand Group Functions.
Group Functions or Aggregate Functions :
========================================
Oracle having following func
1)max()
2)min()
3)sum()
4)avg()
5)count(*)
6)count(columnname)
Generally Group functions are operate over number of values in a table column and also returns single value.
max() :
max(1,2,3,4)-----wrong it take column name from table
It returns maximum value from a table column
select max(sal) from emp;
It will work on number, string , dates also.
Min():
it will also work in number, date , string.
Group func direclty wont work in where.
select * from table where sal=min(Sal)----wrong
Avg: it returns avg from number datatype column.
In avg will it count that ROWS.It ignores null values.
select avg(Sal) from emp;
select avg(nvl(comm)) from emp;
In oracle by defalut all group function ignores Null values except count(*). If we want to count Null values in group functionthen we must use NUL() func.
sum() : it returns total from number datatype column.
select sum(sal) from emp;
------------------ Session 34--------------
-------------------------------------------
Count(*) : It counts number of rows in table.
=========
select count(*) from table;
count(Columnname) : It counts number of not null values from table column.
==================
select count(*) from emp; ----14
select count(comm) from emp; ----4
Note: if we want to count number of distinct values from a table column then we can also use distinct caluse within count function.
select count(distinct deplno) from emp;-----3
Group by Clause:
=================
Group by caluse is used to divide similar data items into set of logical groups. i.e group by clause automatically reduces number of rows in each group.
This group by clause is used in select statement.
Syntax:
select columnname
from table
group by columnname;
Query to displaynumber of employee in each department from table.
select deptno , count(*) from emp group by deptno.
Query to display no.of employee in each job
select job ,count(*) from emp group by job;
First Rule:
Note : in all relational databases we can also use group by clause without using group functions.
Without using group fucn we cn use Group by.
select job from employee group by job;
Note: Other than group Function columns specified after select then those all columns must be specify after
group by clause otherwise oracle returns an error not a group by expression.
select ename , job, sum(sal) from emp group by ename,job;
select ename, job from emp group by ename , job;
--------------- Session 35 --------------
------------------------------------------
Group By Clause Execution :
===============================
Whenever we are submitting group by clause into oracle server then oracle firstselects specified columns from group by clause and then only it returns similar data items into set of groups from that columns and also it reduces number of rows in each group and then this result internally stored in result set table. Then only oracle server retreve select list specified columns from this result set table.
select deptno from emp group by deptno;
Execution Process:
STEP1: group by deptno;
deptno
10
20
30
10
20
10
20
30
Result set table.
10
20
30
STEP2:
select deptno from result table.
deptno
10
20
30
Note : in all relational databases wheenever we are try to display group functions along with aonther columns
then database servers returns an error to overcome this problem then we must use group by clause.
Step1 :
select sum(sal) from emp;
Step2:
select deptno, sum(sal) from emp;
Error : not a single group func
Solution:
select deptno, sum(sal) from emp group by deptno;
Group func works on all data other column work on row wise.
Query to display those departments having more than 3 employee by using group by
select deptno, count(*) from emp group by deptno where count(*)>3;--------Wrong
Solu :
select deptno, count(*) from emp group by deptno having count(*)>3;
Having CLause:
============= After group by clause we are not allowed to use where clause in place of this one ANSI/ISO sql provided another clause Having.
Generally if you want to restrict rows in a table then we are using where clause whereas if you want to restrict group after Group by clause then we must use having clause.
select deptno,sum(sal) from employee group by ename;-wrong
Generally we are not allowed to use group function in where clause whereas we can also use group functions in having clause.
Query to display those dept sum(sal)having 9000 by using group by clause.
select deptno, sum(sal) from emp group by deptno having sum(sal)>9000;
Query to dislay year and number of empl joining for year in which more than 1 emp washired from emp table using froup by clause.
select extract(year from hiredate) , count(*) from emp group by extract(year from hiredate)
having count(*)>1;
select to_char(hiredate,'yyyy') , count(*) from emp group by to_char(hiredate,'yyyy')
having count(*)>1;
Can we use alias name in group by caluse not even in where clause --- not possible
select to_char(hiredate,'yyyy') 'year' , count(*) from emp group by year ----wrong
having count(*)>1;
DOnot use * with group by clause.
----------------------------------------------------------------------------------------------------------
SQL
SESSION 38 Join Started
Join : are used to retrieve data from multiple tables.
In all relational databased when we are joining N tables then we must use N-1 Joining Conditins.
Oracle Server having following types of joins. These are
1) Equi Join or Inner Join
2) Non Equi Join
3) Self Join
4) Outer Join
These joins are also called as oracle 8i joins. Along with these joins Oracle 9i onwards
oracle also supports ANSI standard joins same like all relational databases. These joins are also called as 9i joins or ANSI joins.
9i joins or ANSI joins
=======================
1) Inner Join
2) LEft OUTER Join ---OUTER is optional here
3) RIGHT OUTER Join
4) FULL Outer Join
5) Natural Join
In Oracle we can retrieve data from multiple table without using Joining condition.
In that case oracle server internally uses default join and default join is CROSS Join (CARTESIAN PRODUCT)
select col1, col2 from table1 , table2.....;
EQUI JOIN :
============= Based on equality condition (by using equality operator). We are retrieving data from multiple table. Joining conditional columns must have same data type.
Syntax :
select col1, col2 -----Ex.ecuting 3
from table1 , table2 ----Executing 1
where table1.commoncol = table2.commoncolname; ----Executing 2
Ambiguity Error: It is recommened while fetching column use table name like tablename.colname.
In all relationaldb for improvePerformance of the joins then we can also use alias name within join querys.in join condition also use table alias name.
Non Equi Join :
================ Based on other than equality operator (< , <= , >, >=, between,...) we are retriving from multiple tables.
------- SESSION 40 ----------
----------------------------
Note : After Joining condition if you want to filter data then we must use AND operator with in 8i joins whereas in 9i joins we are using either AND operators or where clause.
select columns from emp , dept where emp.=dept.
and condition;
No comments:
Post a Comment