1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

RDBMS Using Oracle

RDBMS Using Oracle

=> Concept of Sub-query
=> Sub-query Examples
=> Sub-query Practice
=> Use of GROUP BY clause
=> GROUP BY Examples
=> Date Arithmetic
=> Date Functions
=> To count the number of rows in emp table enter:
SQL> select count(*) from emp;
COUNT(*)
----------
14
The COUNT Function
The count function is used to count the rows
selected by the query.
To count the
employees who are
getting salary.
SQL> select count(sal) from emp;
COUNT(SAL)
----------
14
To count the number
of different jobs held
by employees in
department 10.
SQL> select count (DISTINCT JOB) from
emp where deptno = 10;
COUNT (DISTINCT JOB)
------------------
2
Group functions Vs Individual
Functions
=> A command
Select ename, avg(sal) from emp
is invalid. Because select ename will return multiple
records and avg(sal) will return one average result of all
salaries.
IF we will enter this kind of command SQL*plus will
display a message saying that ENAME is ?not a singlegroup
group function?.
SQL> select ename, avg(sal) from emp;
select ename, max(sal) from emp
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> select ename, max(sal) from emp;
select ename, max(sal) from emp
*
ERROR at line 1:
ORA-00937: not a single-group group function
Group function in a sub-query
=> Suppose we want to find out who makes the
highest salary in emp table. Since we cannot
write this SELECT ename, max(sal) from emp
=> In order to get this result we can put MAX(SAL)
function in sub query
Group function in a sub-query
=> To list the employee and job with the highest
salary enter:
SQL> select ENAME, JOB, SAL from emp
where sal = (select MAX(sal) from emp);
ENAME JOB SAL
--------------- -------------------- ----------
Alan CLERK 900
Sub Query
Sub Query can be used with any of the following
Operators includes = , != , <> , <= , >= , NOT IN and
IN, <operator> ANY , <operator> ALL etc
= != <> < > <= >= all are used with single
value output of a sub query, where as IN, NOT IN,
<operator> ANY , <operator> ALL keywords can be
used for single as well as for multi value output of a sub
query.
SUB QUERY PRACTICE:
Note:-
You have understood the concept of SUBQUERY if you can
solve following queries by your own.
=> List the employee name and job with the lowest
salary?
=> List the employee name who has salary more
then the average salary of all Managers from
EMP table?
=> How many of employees have salary more then
the SUM of Salaries of All CLERKS?
=> Select ename from emp where
sal>(select min(sal) from emp)
Select ename from emp where sal > (select avg(sal)
from emp where job = ?MANAGER?;
Select count (empno) from emp where sal> (select
sum(sal) from emp where job = ?CLERK?);
Use of GROUP BY
SQL> select ename, max(sal) from emp
*
ERROR at line 1:
ORA-00937: not a single-group group function
Use of GROUP BY
select deptno, sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
--------- ----------
10 2700
20 800
Suppose you want to find sum of salaries for each
department.
=> SQL> select job, avg(sal) from emp group by job;
JOB AVG(SAL)
-------------------- ----------
CLERK 625
MANAGER 800
Manager 200
Use of GROUP BY
HAVING clause
=> As we can select specific rows with a WHERE
clause, similarly we can select specfic groups
with a HEAVING clause.
=> HAVING clause will come after the GROUP
BY clause.
HAVING clause
=> To list the average salary for all job groups with more
then two employees
SQL> select job , count (*) , avg (sal) from emp
group by JOB
HAVING count(*) > 2;
JOB COUNT(*) AVG(SAL)
-------------------- ---------- ----------
CLERK 4 625
=> As an example, suppose we want to list the average
salary for all job groups with more then two employees.
=> Can you solve this (A difficult One)
LIST all departments from emp table with
at least two clerks?
SQL> select DEPTNO from EMP
where JOB = 'CLERK'
GROUP BY DEPTNO
HAVING count(*) >= 2;
DEPTNO
----------
10
10
Labeling GROUP Columns
SQL> select job, sum(sal) as "TOTAL SALARY"
from emp group by job;
JOB TOTAL SALARY
-------------------- ------------
CLERK 2500
MANAGER 800
Manager 200
DATE ARITHMETIC
We can perform arithmetic operations on date
fields, the operations you may use are
Date + Number
Date ? Number
Date ? Date
11
We can perform arithmetic operations on date fields, the
operations you may use are
Date + Number
(add a number of days in a date, producing a date)
Date ? Number
(Subtracts a number of days in a date, producing a date)
Date ? Date
(Subtract one date from another, producing a number)
SQL> Select sal, hiredate, hiredate + 365
as "New Hiredate" from emp
SAL HIREDATE New Hiredate
---------- --------- ---------
800 17-DEC-80 17-DEC-81
1600 20-FEB-81 20-FEB-82
1250 22-FEB-81 22-FEB-82
446.25 02-APR-81 02-APR-82
1250 28-SEP-81 28-SEP-82
Date + Number
Add a number of days in a date,
producing a date.
Try for
e.g.(hiredate ? 31)
Also try for
Date - Number
e.g.(hiredate ? 365)
And
e.g.(hiredate ? 180)
12
Date ? Date
Subtract one date from another, producing a
number.
Date ? Date return?s result in number of days.
SQL> select sysdate, hiredate, (sysdate - hiredate) from emp;
SYSDATE HIREDATE (SYSDATE-HIREDATE)
--------- --------- ------------------
23-NOV-02 17-DEC-80 8011.50647
23-NOV-02 20-FEB-81 7946.50647
23-NOV-02 22-FEB-81 7944.50647
23-NOV-02 02-APR-81 7905.50647
SQL> select sysdate, hiredate, (sysdate - hiredate) / 365 from emp;
SYSDATE HIREDATE (SYSDATE-HIREDATE)/365
--------- --------- --------- -------------
23-NOV-02 17-DEC-80 21.9493285
23-NOV-02 20-FEB-81 21.7712463
23-NOV-02 22-FEB-81 21.7657669
23-NOV-02 02-APR-81 21.6589176
Here you can also use
Number Functions
like ROUND, CEIL,
FLOOR etc?
13
ADD_MONTHS Function
=> ADD_MONTHS function is used to add any
number of months in a date.
=> ADD_MONTHS takes two arguments
=> a date
=> and a integer representing a number of month.
It returns a date after adding that number in months.
SQL>select hiredate, ADD_MONTHS (hiredate, 1)
from emp;
HIREDATE ADD_MONTH
--------- ---------
17-DEC-80 17-JAN-81
20-FEB-81 20-MAR-81
22-FEB-81 22-MAR-81
02-APR-81 02-MAY-81
28-SEP-81 28-OCT-81
01-MAY-81 01-JUN-81
Also try
ADD_MONTHS(hiredate, 4)
ADD_MONTHS(hiredate, 12)
?.
?.
14
GREATEST & LEAST
=> GREATEST Function is used to Find the Later
date from two given dates
=> LEAST Function is used to Find the Earlier date
from two given dates
For Example
SQL> select greatest('12-JAN-2002' , sysdate) from dual
GREATEST
-----------
14-NOV-03
SQL> select greatest('12-JAN-2002' , ?11-DEC-2003?) from dual
GREATEST
-----------
11-DEC-2003
SQL> select LEAST('12-JAN-2002' , ?23-NOV-01?) from dual
LEAST
-----------
23-NOV-01
Similarly you can use LEAST Function
We can also use GREATEST & LEAST functions For
two date columns e.g.
select GREATEST (?Hiredate' ,?any_date_column?)
from mytable;
15
COUNT the employees (From
EMP table) which were hired in
each year.
YEAR Employees
1980 1
1981 10
1987 2
Time
5 Mins
Select
TO_CHAR(HIREDATE, 'YYYY'), COUNT(*)
from emp
group by
TO_CHAR(HIREDATE, 'YYYY');
16
DATE Functions Contd?
MONTHS_BETWEEN
This function is used to find the difference in
number of months between two date columns
or two given dates.
For Example
contd..
MONTHS_BETWEEN contd..
SQL> select months_between('12-JAN-
2002','12-JAN-2001') from dual;
MONTHS_BETWEEN('12-JAN-2002','12-JAN-2001')
-------------------------------------------
12
17
MONTHS_BETWEEN
SQL> select sysdate, hiredate,
months_between(sysdate, hiredate) as Diff From emp;
SYSDATE HIREDATE DIFF
--------- --------- --------------------------------
01-Nov-03 17-DEC-80 276.50282
01-Nov-03 20-FEB-81 264.406045
01-Nov-03 22-FEB-81 264.341529
01-Nov-03 02-APR-81 262.986691
SQL> select hiredate, LAST_DAY(HIREDATE) as "LAST
DATE" from emp
HIREDATE LAST DATE
--------- ---------
17-DEC-80 31-DEC-80
20-FEB-81 28-FEB-81
22-FEB-81 28-FEB-81
02-APR-81 30-APR-81
LAST_DAY
This function is used to find out the last date of the month
for any given date. LAST_DAY (HIREDATE)
18
LAST_DAY
SQL> Select LAST_DAY('20-DEC-2002') as Result
from dual;
RESULT
---------
31-DEC-02
NEXT_DAY
This function is used to find the NEXT day
(given) of any date (given).
NEXT_DAY (HIREDATE, ?FRIDAY?)
contd..
19
NEXT_DAY contd..
SQL> Select hiredate,
NEXT_DAY(HIREDATE, 'FRIDAY')
as "NEXT DAY" from emp;
HIREDATE NEXT DAY
--------- ---------
17-DEC-80 19-DEC-80
20-FEB-81 27-FEB-81
22-FEB-81 27-FEB-81
02-APR-81 03-APR-81 NEXT DAY column
is showing the First
FRIDAY coming after
Given (hiredate) date.
NEXT_DAY contd..
SQL> select hiredate,
to_char(
NEXT_DAY(HIREDATE,'FRIDAY'),
'DY DD MM YY') as "NEXT DAY" from emp
HIREDATE NEXT DAY
--------- ------------
17-DEC-80 FRI 19 12 80
20-FEB-81 FRI 27 02 81
22-FEB-81 FRI 27 02 81
02-APR-81 FRI 03 04 81
Finding NEXT_DAY
and also displaying
DAY by using TO_CHAR
function.
20
NEXT_DAY
SQL> select
NEXT_DAY(?15-Nov-2004', ?THUSDAY')
as ?EID DAY? from dual;
EID DAY
---------
16-NOV-04

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy