Distinct,NVl,Decode Functions,group functions and joins

DISTINCT(Very important)



Using this keyword we can select the unique values
from the table.

Select distinct deptno from emp;

in the above query it willl display the unique deptno from the emp table

select distinct empno,deptno from emp;

In the above query it displays the unique combination of empno,deptno from the emp table.

------------------------------------------------------------------------------------------
DECODE:
Decode is used at the time of reporting purpose.
this will not effect the data in the data base.

This is used only to maipulate the data while retreving for
reporting purpose.

This atcs like a If else end if condition.


syntax: decode('column_name','value1','result1','value2','result2','result n');

The data type of the column name should match the result data type.
EX

Write a query to display emp jobs as if job is accounting
then display as sr accounting,if it is sales then display
as sr sales else display Null

  select empno
         ,ename
         ,job
       ,upper(job)
      ,decode(upper(job),'MANAGER','SRMANAGER','SALESMAN','SRSALESMAN')
    from emp
           
Write a query to display emp jobs as if job is accounting
then display as sr accounting,if it is sales then display
as srsales else display as no job.

  select empno
  ,ename
 ,upper(job)
 ,job
 ,decode(upper(job),'MANAGER','SRMANAGER','SALESMAN','SRSALESMAN','NO JOB')
 from emp;

Write a query to display emp jobs as if job is accounting
then display as sr accounting,if it is sales then display
as srsales  else display as job is.
             
Select empno
          ,ename
         ,upper(job)
         ,decode(upper(job),'MANAGER','SRMANAGER','SALESMAN','SRSALESMAN',job)
 from emp;


NVL ( expr1 , expr2 )
If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

NVL2 ( expr1 , expr2 , expr3 )
If expr1 is null, then NVL2 returns expr3. If expr1 is not null, then NVL2 returns expr2.

AGGREGATE FUNCTIONS.....

SUM      --returns the sum of the column value
MIN      –return the  minimum value
MAX     –return the  maximum value
COUNT –return the  no of rows.

sum --returns the sum of the column value....
      
select sum(sal)  from emp;

min  --- will return the min value of the column

select min(sal)  from emp;

max -- will return the max value

select max(sal)  from emp;

count(*) -- will return the total count of records on a table

select count(*)  from emp;

count(column_name) -- will return the total count of the records
                                      of a column which takes only not null values...

select count(comm)  from emp;


Group by clause:

whenever we use an aggregate function in the select clause and also select some other columns then we need to use group by clause...
EX:
select ename,min(sal)
 from emp
 group by ename;

select sum(sal),deptno
 from emp
 group by deptno;
select max(sal),deptno
 from emp
 group by deptno;

select sum(sal),deptno
 from emp
 group by deptno;

select count(*),deptno
 from emp
 group by deptno;

Queries:
--------

ex1: Print the depno 30 job wise salaries....

select sum(sal),job,deptno
from emp
where deptno = 30
group by job,deptno;



Ex2: job wise salaries to be displayed

select sum(sal),job from emp group by job


Ex3: Depno,jobwise salaries to be diplayed..

select sum(sal),job,deptno
from emp group by job,deptno

 Having clause:

If we want to use having clause then group by clause is must....

Having clause is used to filter the grouped data on aggregate function.


Order by Clause:
Using this clause we can do the order by desc or asec.. for the fetched data/...

Defualt order by is asec

EX1:

SELECT * FROM EMP ORDER BY sal ASEC;

SELECT * FROM EMP ORDER  BY sal DESC;







NOTE:  order of using the clauses:


WHERE-->GROUP BY--HAVING--ORDER BY...


EX: list sum of salaries for each deptno having that total sal > 16000
      

select sum(sal),deptno
from emp
group by deptno
having sum(sal) > 16000
order by deptno desc

WHERE-->GROUP BY--ORDER BY...

EX: list count of records for each deptno     order by deptno desc

select count(*),deptno
from emp
group by deptno
order by deptno desc

WHERE --> ORDER BY

EX: list all employee details order by sal descending


select *
from emp
order by sal desc;

WHERE --> GROUP BY

EX:
list min of sal for each deptno


select min(sal),deptno
from
group by deptno

WHERE -->GROUP BY--HAVING...

lsit max sal for each dept no hacing its sal >8000

select max(sal),deptno
from emp
group by deptno
having max(sal) > 8000


ORDER BY HAS SPEACIAL FEATURE WHERE WE CAN DO THE ORDER BY USING THECOLUMN NO OR ALIAS NAME

EX1: Query based on the column name

select ename name
          ,sal salary
          ,job "job designation"
from emp
order by sal desc

EX2: Query based on the alias name

select ename name
          ,sal salary
          ,job "job designation"
from emp
order by salary desc

EX3: Query based on the column number

select ename name
          ,sal salary
          ,job "job designation"
from emp
order by 2

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


TYPES OF SELECT STATEMENTS---------------------------------------------

1)Simple select statement

select * from emp;

2)where clause select statement

select * from emp where deptno = 30

3)using operators
    IN,NOT IN,LIKE,BETWEEN,OR,NOT bETWEEN,AND,IS NULL, IS NOT NULL

4)AGGREGATE FUNCTIONS

5)USING GROUP BY, HAVING,ORDER BY CLAUSES


ALIAS: It is the duplicate name assigned .

 They are given just adjacent to the column name or table name just by giving space

Table alias : Duplicate Name assigned to the table.
                      It should not contain any spaces and should start with character

Column Alias : Duplicate Name assigned to the column.

                    It can contain spaces. if it contains spaces then it should be
                     enclosed  in the " ".

NOTE:

When table alias is given then the selection of columns or condition written on a column will be represented by table alias. Column name

Ex:

  Select E.Ename Employee_name
            ,E.Sal       Salary
            ,E.comm   commission
            ,E.job       "Employee designation"
  from   EMP E
where  E.deptno = 30

In the above example E represents the table name
and Employee_name represents the column name.



6)JOINS:
 IF WE WANT TO EXTRACT THE DATA FROM MORE THEN ONE TABLE THEN WE GO FOR JOINS...


1)Equi Join:  equal operator is used...

It fetches the records from 2 tables only when the condition is true
from both the tables......

Fetch the emp no,ename,job,deptno from the emp table
and fetch the department name and loc for that deptno of the
emp table from the dept table.


SELECT  E.empno "Employee No"
       ,E.ename "Employee Name"
       ,E.job    JOB
               ,E.deptno Employee_deptno
               ,E.sal    Salary
               ,D.dname  "Department Name"
               ,D.loc    Location
FROM    EMP  E
       ,DEPT D
WHERE  D.deptno = E.deptno --Equi Join
ORDER BY E.deptno

Include the condition in above query as whose deptno is 30 and sal greater then 0

SELECT  E.empno "Employee No"
       ,E.ename "Employee Name"
       ,E.job    JOB
               ,E.deptno Employee_deptno
               ,E.sal    Salary
               ,D.dname  "Department Name"
               ,D.loc    Location
FROM    EMP  E
       ,DEPT D
WHERE  D.deptno = E.deptno
AND    E.deptno = 30
ANd    E.sal    > 0
ORDER BY E.deptno


Fetch empno,ename,sal,dname,loc for the Accouting department name

SELECT  E.empno "Employee No"
       ,E.ename "Employee Name"
       ,E.job    JOB
               ,E.deptno Employee_deptno
               ,E.sal    Salary
               ,D.dname  "Department Name"
               ,D.loc    Location
FROM    EMP  E
       ,DEPT D
WHERE  D.deptno = E.deptno
AND    upper(D.dname)  = 'ACCOUNTING'
ORDER BY E.deptno

2) Non-Equi join: other then equal operator is used...

select E.empno
      ,E.ename
              ,E.job
              ,E.sal
              ,d.deptno
              ,d.dname
              ,d.loc
from emp E
    ,dept d
where d.deptno = e.deptno  ---equi join
and e.sal > 1500;                        -- non equi join

OUTER JOIN

Right outer join
----------------------

When the (+) symbol is placed in the left side of the join it is called right outer join.

ex: table1.column_name(+) = table2.column_name
It shows the matched records  data from the table1  and table2.
and also it shows all records from table 2 though the records doesnot exists in the table1.

--Fetch all employee information and their department information.
  if the employee deptno doesnot exists in the dept table still show the employee information and
show the dept information as null

select E.empno
      ,E.ename
              ,E.job
              ,E.sal
              ,e.deptno emp_deptno
              ,d.deptno dept_deptno
              ,d.dname
              ,d.loc
from emp E
    ,dept d
where d.deptno(+) = e.deptno
ORDER BY E.deptno



left outer join
----------------
When the (+) symbol is placed in the right side of the join it is called left outer join.

ex: table1.column_name = table2.column_name(+)
It shows the matched records data from the table1  and table2.
and also it shows all records from table 1 though the records doesnot exists in the table2.






select E.empno
      ,E.ename
              ,E.job
              ,E.sal
              ,e.deptno emp_deptno
              ,d.deptno dept_deptno
              ,d.dname
              ,d.loc
from emp E
    ,dept d
where e.deptno = d.deptno(+)
ORDER BY E.deptno

Ex:
List all dept details and their employee details.
show the dept info even if no employees are assigned to that deptno and show their employee information as null.

select E.empno
      ,E.ename
              ,E.job
              ,E.sal
              ,e.deptno emp_deptno
              ,d.deptno dept_deptno
              ,d.dname
              ,d.loc
from emp E
    ,dept d
where e.deptno(+) = d.deptno
ORDER BY E.deptno

create table salgrade
(lowsal number
,highsal number
,grade char(1));


SELECT  E.empno "Employee No"
       ,E.ename "Employee Name"
       ,E.job    JOB
               ,E.deptno Employee_deptno
               ,E.sal    Salary
               ,D.dname  "Department Name"
               ,D.loc    Location
               ,S.grade
FROM    EMP      E
       ,DEPT     D
              ,SALGRADE S
WHERE  D.deptno = E.deptno
AND    E.sal between S.lowsal and S.highsal
ORDER BY s.grade 
Ex  create student_marks....

Table Name : XX_STD_RESULT
column names -- sno,total_marks ,result

101  -- 550  -- PASS
201 -123 --FAIL
301 -- 430 --PASS
401  --299 --fail
501--361 -- PASS
601 -- 480 --PASS
701 --399   -- PASS
801 --301  --PASS

create markinggrade table

Table Name : XX_MARK_GRADE
column names -- lmark,hmark,grade

100 - 300 --D
300 - 400 --C
400- 500 - --B
500 -- 600 A

Create student information table

Table_name   XX_STD_INFO
sno,sname,sex,age,address.

Create gender table
Code,name

M – Male
F  -- Female

List all student information and their total marks and grade.
show student marks,result,grade even if the student information doesnot exists
SELECT XSI.sno           "Student Number"
             ,XSI.sname        Student_Name
             ,XSI.sex             Gender
             ,XSI.age             Student_Age
             ,XSI.address      Address
                                     ,XSR.sno          result_sno
             ,XSR.result         Student_Result
             ,XSR.totalmarks  Total_marks
             ,XSG.grade         Grade
             ,g.name     “sex”
FROM   xx_std_info     XSI
            ,xx_std_results  XSR
            ,xx_std_grade  XSG
            , gender G
WHERE     XSR.sno=XSI.sno(+)
AND      XSR.totalmarks Between XSG.Lmarks AND XSG.Hmarks
And       g.code  = xsi.sex;



2)fetch the student information along with his result,marks and grade
whose result is PASS


3)fetch the student information along with his result, marks and grade
 whose marks are above 398

4)fetch the student information along with his result,marks and grade
whose  grade is A or B or c.

5)fetch the student information along with his result, marks and grade
whose  grade is  D or whose result is PASS.







Post a Comment