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.
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.
No comments:
Post a Comment