�ݺ�ߣ

Using the NVL Function

ex1:- To calculate the annual compensation of all employees, you need to
multiply the monthly salary by 12 and then add the commission percentage
to the result

SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

.
.


Using the NVL2 Function

SELECT last_name, salary, commission_pct,
NVL2(commission_pct,'SAL+COMM', 'SAL') income
FROM employees WHERE department_id IN (50, 80);

.
.


Executing Single-Row Subqueries

ex1:- displays employees whose job ID is the same as that of employee
141 and whose salary is greater than that of employee 143.

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = ST_CLERK
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary > 2600
(SELECT salary
FROM employees
WHERE employee_id = 143);

The result in next slide


Executing Single-Row Subqueries

ex2:- Find the job with the lowest average salary.

SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary)=(SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);


Will This Statement Return Rows?

SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');

there is no employee named Haas. So the subquery returns no
rows
The outer query takes the results of the subquery (null) and uses
these results in its WHERE clause.
The outer query finds no employee with a job ID equal to null, and
so returns no rows


Executing multiple-Row Subqueries

ex3:- display the employees whose there salary equal to the min salary of
each department.

SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id);




ex4:- displays employees who are not IT programmers and whose salary
is less than that of any IT programmer.

SELECT employee_id, last_name, job_id, salary
FROM employees 9000, 6000, 4800,4200
WHERE salary < ANY
(SELECT distinct salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

=ANY is equivalent to IN.
>ANY means more than the minimum.
<ANY means less than the maximum



.
.



ex4:- displays employees who are not IT programmers and whose salary
is less than that of all IT programmer.

SELECT employee_id, last_name, job_id, salary
FROM employees 9000, 6000, 4800,4200
WHERE salary < ALL
(SELECT distinct salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

>ALL means more than the maximum
<ALL means less than the minimum


.
.

.
.


Create a query that displays the first eight characters of the employees�� last
names and indicates the amounts of their salaries with stars. Each star
signifies a thousand dollars. Sort the data in descending order of salary.
Label the column EMPLOYEES_AND_THEIR_SALARIES.


The function check_sal is written to check if the salary of a
particular employee is greater or less than the average salary of all
employees working in his department. The function returns TRUE if
the salary of the employee is greater than the average salary of
employees in his department else returns FALSE. The function
returns NULL if a NO_DATA_FOUND exception is thrown.


Observe that the function checks for the employee with the
employee ID 205. The function is hard coded to check for this
employee ID only. If you want to check for any other employees,
you will have to modify the function itself. You can solve this
problem by declaring the function such that it accepts an
argument. You can then pass the employee ID as parameter.


�ݺ�ߣ

Oracle examples

More Related Content

Oracle examples

Editor's Notes