Find employees with no commission?
select
employee_id, first_name, last_name,
commission_pct
from hr.employees
where commission_pct = 0
no data found
Find employees with no commission - fixed
select
employee_id, first_name, last_name,
commission_pct
from hr.employees
where commission_pct is null
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | COMMISSION_PCT | 100 | Steven | King | - | 101 | Neena | Kochhar | - | 102 | Lex | De Haan | - | 103 | Alexander | Hunold | - | 104 | Bruce | Ernst | - | 105 | David | Austin | - | 106 | Valli | Pataballa | - | 107 | Diana | Lorentz | - | 108 | Nancy | Greenberg | - | 109 | Daniel | Faviet | - |
---|
Find all employees that DON'T manage a department?
select * from hr.employees e
where e.employee_id not in (
select d.manager_id from hr.departments d
)
no data found
Count the employees
select count (*) from hr.employees
COUNT(*) | 107 |
---|
Find the managers
select distinct manager_id from hr.departments
MANAGER_ID | 108 | 200 | 203 | 204 | 121 | 145 | 103 | - | 201 | 114 | 100 | 205 |
---|
NOT IN equivalent
select * from hr.employees e
where e.employee_id <> null
and e.employee_id <> 108
and e.employee_id <> 200 --etc.
no data found
Find all employees that DON'T manage a department - fixed
select * from hr.employees e
where e.employee_id not in (
select d.manager_id from hr.departments d
where d.manager_id is not null
)
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 21-SEP-05 | AD_VP | 17000 | - | 100 | 90 | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 13-JAN-01 | AD_VP | 17000 | - | 100 | 90 | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 21-MAY-07 | IT_PROG | 6000 | - | 103 | 60 | 105 | David | Austin | DAUSTIN | 590.423.4569 | 25-JUN-05 | IT_PROG | 4800 | - | 103 | 60 | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 05-FEB-06 | IT_PROG | 4800 | - | 103 | 60 | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 07-FEB-07 | IT_PROG | 4200 | - | 103 | 60 | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 16-AUG-02 | FI_ACCOUNT | 9000 | - | 108 | 100 | 110 | John | Chen | JCHEN | 515.124.4269 | 28-SEP-05 | FI_ACCOUNT | 8200 | - | 108 | 100 | 111 | Ismael | Sciarra | ISCIARRA | 515.124.4369 | 30-SEP-05 | FI_ACCOUNT | 7700 | - | 108 | 100 | 112 | Jose Manuel | Urman | JMURMAN | 515.124.4469 | 07-MAR-06 | FI_ACCOUNT | 7800 | - | 108 | 100 |
---|
Find all employees that DON'T manage a department - fixed v2
select * from hr.employees e
where not exists (
select null from hr.departments d
where e.employee_id = d.manager_id
)
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 21-SEP-05 | AD_VP | 17000 | - | 100 | 90 | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 13-JAN-01 | AD_VP | 17000 | - | 100 | 90 | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 21-MAY-07 | IT_PROG | 6000 | - | 103 | 60 | 105 | David | Austin | DAUSTIN | 590.423.4569 | 25-JUN-05 | IT_PROG | 4800 | - | 103 | 60 | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 05-FEB-06 | IT_PROG | 4800 | - | 103 | 60 | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 07-FEB-07 | IT_PROG | 4200 | - | 103 | 60 | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 16-AUG-02 | FI_ACCOUNT | 9000 | - | 108 | 100 | 110 | John | Chen | JCHEN | 515.124.4269 | 28-SEP-05 | FI_ACCOUNT | 8200 | - | 108 | 100 | 111 | Ismael | Sciarra | ISCIARRA | 515.124.4369 | 30-SEP-05 | FI_ACCOUNT | 7700 | - | 108 | 100 | 112 | Jose Manuel | Urman | JMURMAN | 515.124.4469 | 07-MAR-06 | FI_ACCOUNT | 7800 | - | 108 | 100 |
---|
COUNT(*) vs COUNT ( column )
select
count (*) emp#,
count ( commission_pct ) commissioned_emp#
from hr.employees
EMP# | COMMISSIONED_EMP# | 107 | 35 |
---|
Find the indexed columns on HR.EMPLOYEES
select
index_name,
listagg ( column_name, ', ' )
within group ( order by column_position ) indexed_cols
from all_ind_columns
where index_owner = 'HR'
and table_name = 'EMPLOYEES'
group by index_name
INDEX_NAME | INDEXED_COLS | EMP_DEPARTMENT_IX | DEPARTMENT_ID | EMP_EMAIL_UK | EMP_EMP_ID_PK | EMPLOYEE_ID | EMP_JOB_IX | JOB_ID | EMP_MANAGER_IX | MANAGER_ID | EMP_NAME_IX | LAST_NAME, FIRST_NAME |
---|
select
count (*)
from hr.employees
COUNT(*) | 107 |
---|
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID))
PLAN_TABLE_OUTPUT | SQL_ID 5f9x9mpfsdfn9, child number 0 | ------------------------------------- | select count (*) from hr.employees | Plan hash value: 3580537945 | ------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Cost (%CPU)| Time | | ------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | | 1 (100)| | | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 | | ------------------------------------------------------------------------- |
---|
COUNT optional column with index
select
count ( manager_id )
from hr.employees
COUNT(MANAGER_ID) | 106 |
---|
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID))
PLAN_TABLE_OUTPUT | SQL_ID 3916j7nns3ntf, child number 0 | ------------------------------------- | select count ( manager_id ) from hr.employees | Plan hash value: 3393209858 | ----------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ----------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | | | 1 (100)| | | | 1 | SORT AGGREGATE | | 1 | 4 | | | | | 2 | INDEX FULL SCAN| EMP_MANAGER_IX | 107 | 428 | 1 (0)| 00:00:01 | | ----------------------------------------------------------------------------------- |
---|
COUNT optional column with no index
select
count ( commission_pct )
from hr.employees
COUNT(COMMISSION_PCT) | 35 |
---|
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID))
PLAN_TABLE_OUTPUT | SQL_ID 51gan22wzxvpz, child number 0 | ------------------------------------- | select count ( commission_pct ) from hr.employees | Plan hash value: 1756381138 | -------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | -------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | | | 3 (100)| | | | 1 | SORT AGGREGATE | | 1 | 2 | | | | | 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 214 | 3 (0)| 00:00:01 | | -------------------------------------------------------------------------------- |
---|
COUNT mandatory column with no index
select
count ( hire_date )
from hr.employees
COUNT(HIRE_DATE) | 107 |
---|
select *
from table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID))
PLAN_TABLE_OUTPUT | SQL_ID 7ur0ak243yvaf, child number 0 | ------------------------------------- | select count ( hire_date ) from hr.employees | Plan hash value: 3580537945 | ------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Cost (%CPU)| Time | | ------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | | 1 (100)| | | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 | | ------------------------------------------------------------------------- |
---|
NULL in aggregates
select
/* Are these the same? */
sum ( commission_pct ) total_pct,
sum ( commission_pct + null ) total_pct_plus_null,
/* or these? */
avg ( commission_pct ) avg_pct,
avg ( commission_pct + null ) avg_pct_plus_null
from hr.employees
TOTAL_PCT | TOTAL_PCT_PLUS_NULL | AVG_PCT | AVG_PCT_PLUS_NULL | 7.8 | - | .2228571428571428571428571428571428571429 | - |
---|
NULL + val = NULL
select 1 + null from dual
1+NULL | - |
---|
NULL string concatenation
select last_name || null
from hr.employees
fetch first 10 rows only
LAST_NAME||NULL | Abel | Ande | Atkinson | Austin | Baer | Baida | Banda | Bates | Bell | Bernstein |
---|
The empty string
select * from dual
where '' is null
DUMMY | X |
---|
create table employees
( employee_id number(6)
, first_name varchar2(20)
, last_name varchar2(25)
constraint emp_last_name_nn not null
, email varchar2(25)
constraint emp_email_nn not null
, phone_number varchar2(20)
, hire_date date
constraint emp_hire_date_nn not null
, job_id varchar2(10)
constraint emp_job_nn not null
, salary number(8,2)
, commission_pct number(2,2)
, manager_id number(6)
, department_id number(4)
, constraint emp_salary_min
check (salary > 0)
, constraint emp_email_uk
unique (email)
)
Table created.
declare
negative_salary number := -999;
begin
insert into employees (
employee_id, last_name, email, hire_date, job_id, department_id,
salary
) values (
-99, 'Test', 'test@test.com', sysdate, 'ST_CLERK', 10,
negative_salary
);
end;
ORA-02290: check constraint (SQL_NRHMCIYEFQQIIEANXUOVHAGDL.EMP_SALARY_MIN) violated ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-02290
select
employee_id, first_name, last_name,
salary
from employees
where employee_id = -99
no data found
select search_condition
from user_constraints
where constraint_name = 'EMP_SALARY_MIN'
SEARCH_CONDITION | salary > 0 |
---|
declare
null_salary number;
begin
insert into employees (
employee_id, last_name, email, hire_date, job_id, department_id,
salary
) values (
-99, 'Test', 'test@test.com', sysdate, 'ST_CLERK', 10,
null_salary
);
end;
Statement processed.
select
employee_id, first_name, last_name,
salary
from employees
where employee_id = -99
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | SALARY | -99 | - | Test | - |
---|
select
employee_id, first_name, last_name,
salary
from employees
where employee_id = -99
and salary > 0
no data found
rollback
Statement processed.
Sorting NULL ascending
select
employee_id, first_name, last_name,
commission_pct
from hr.employees
order by
commission_pct --nulls last
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | COMMISSION_PCT | 164 | Mattea | Marvins | .1 | 179 | Charles | Johnson | .1 | 173 | Sundita | Kumar | .1 | 167 | Amit | Banda | .1 | 166 | Sundar | Ande | .1 | 165 | David | Lee | .1 | 155 | Oliver | Tuvault | .15 | 172 | Elizabeth | Bates | .15 | 171 | William | Smith | .15 | 163 | Danielle | Greene | .15 |
---|
Sorting NULL descending
select
employee_id, first_name, last_name,
commission_pct
from hr.employees
order by
commission_pct desc --nulls first
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | COMMISSION_PCT | 100 | Steven | King | - | 101 | Neena | Kochhar | - | 102 | Lex | De Haan | - | 103 | Alexander | Hunold | - | 104 | Bruce | Ernst | - | 105 | David | Austin | - | 106 | Valli | Pataballa | - | 107 | Diana | Lorentz | - | 108 | Nancy | Greenberg | - | 109 | Daniel | Faviet | - |
---|
Control NULL position when sorting
select
employee_id, first_name, last_name,
commission_pct
from hr.employees
order by
commission_pct desc nulls last
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | COMMISSION_PCT | 145 | John | Russell | .4 | 156 | Janette | King | .35 | 158 | Allan | McEwen | .35 | 157 | Patrick | Sully | .35 | 146 | Karen | Partners | .3 | 160 | Louise | Doran | .3 | 159 | Lindsey | Smith | .3 | 150 | Peter | Tucker | .3 | 148 | Gerald | Cambrault | .3 | 147 | Alberto | Errazuriz | .3 |
---|
Outer joins
select
*
from hr.departments d
left join hr.employees e
using ( department_id )
order by employee_id desc
fetch first 10 rows only
DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | 120 | Treasury | - | 1700 | - | - | - | - | - | - | - | - | - | - | 210 | IT Support | - | 1700 | - | - | - | - | - | - | - | - | - | - | 200 | Operations | - | 1700 | - | - | - | - | - | - | - | - | - | - | 190 | Contracting | - | 1700 | - | - | - | - | - | - | - | - | - | - | 180 | Construction | - | 1700 | - | - | - | - | - | - | - | - | - | - | 170 | Manufacturing | - | 1700 | - | - | - | - | - | - | - | - | - | - | 160 | Benefits | - | 1700 | - | - | - | - | - | - | - | - | - | - | 150 | Shareholder Services | - | 1700 | - | - | - | - | - | - | - | - | - | - | 140 | Control And Credit | - | 1700 | - | - | - | - | - | - | - | - | - | - | 130 | Corporate Tax | - | 1700 | - | - | - | - | - | - | - | - | - | - |
---|
Outer join grouping
select
department_id, count (*) c
from hr.departments
left join hr.employees
using ( department_id )
group by department_id
order by c nulls first
fetch first 10 rows only
DEPARTMENT_ID | C | 10 | 1 | 180 | 1 | 170 | 1 | 160 | 1 | 40 | 1 | 140 | 1 | 130 | 1 | 120 | 1 | 70 | 1 | 150 | 1 |
---|
Outer join grouping - fixed
select
department_id, count ( e.employee_id ) c
from hr.departments d
left join hr.employees e
using ( department_id )
group by department_id
order by c nulls first
fetch first 10 rows only
DEPARTMENT_ID | C | 210 | 0 | 220 | 0 | 140 | 0 | 260 | 0 | 200 | 0 | 250 | 0 | 240 | 0 | 180 | 0 | 270 | 0 | 190 | 0 |
---|
Window functions
select
employee_id, first_name, last_name, salary,
lag ( salary ) over ( order by hire_date ) prev_sal,
lead ( salary ) over ( order by hire_date ) next_sal,
avg ( salary ) over (
order by hire_date
rows between 10 preceding and 1 preceding
) avg_prev_10_sals
from hr.employees
where department_id = 10
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | SALARY | PREV_SAL | NEXT_SAL | AVG_PREV_10_SALS | 200 | Jennifer | Whalen | 4400 | - | - | - |
---|
Window functions
select
employee_id, first_name, last_name, salary,
lag ( salary, 1, 0 ) over ( order by hire_date ) prev_sal,
lead ( salary, 1, 0 ) over ( order by hire_date ) next_sal,
avg ( salary ) over (
order by hire_date
rows between 10 preceding and 1 preceding
) avg_prev_10_sals
from hr.employees
where department_id = 10
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | SALARY | PREV_SAL | NEXT_SAL | AVG_PREV_10_SALS | 200 | Jennifer | Whalen | 4400 | 0 | 0 | - |
---|
Mapping NULL to a value
with rws as (
select
employee_id, first_name, last_name,
nvl ( commission_pct, 0 ) nvl_comm_pct,
coalesce ( commission_pct, 0 ) coalesce_comm_pct,
nvl2 ( commission_pct, commission_pct, 0 ) nvl2_comm_pct,
decode ( commission_pct, null, 0, commission_pct ) decode_comm_pct,
case
when commission_pct is null then 0
else commission_pct
end case_comm_pct
from hr.employees
)
select * from rws
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | NVL_COMM_PCT | COALESCE_COMM_PCT | NVL2_COMM_PCT | DECODE_COMM_PCT | CASE_COMM_PCT | 100 | Steven | King | 0 | 0 | 0 | 0 | 0 | 101 | Neena | Kochhar | 0 | 0 | 0 | 0 | 0 | 102 | Lex | De Haan | 0 | 0 | 0 | 0 | 0 | 103 | Alexander | Hunold | 0 | 0 | 0 | 0 | 0 | 104 | Bruce | Ernst | 0 | 0 | 0 | 0 | 0 | 105 | David | Austin | 0 | 0 | 0 | 0 | 0 | 106 | Valli | Pataballa | 0 | 0 | 0 | 0 | 0 | 107 | Diana | Lorentz | 0 | 0 | 0 | 0 | 0 | 108 | Nancy | Greenberg | 0 | 0 | 0 | 0 | 0 | 109 | Daniel | Faviet | 0 | 0 | 0 | 0 | 0 |
---|
First non-null in a list
select
employee_id, first_name, last_name,
commission_pct, manager_id, salary,
coalesce ( commission_pct, manager_id, salary ) coal
from hr.employees
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | COMMISSION_PCT | MANAGER_ID | SALARY | COAL | 100 | Steven | King | - | - | 24000 | 24000 | 101 | Neena | Kochhar | - | 100 | 17000 | 100 | 102 | Lex | De Haan | - | 100 | 17000 | 100 | 103 | Alexander | Hunold | - | 102 | 9000 | 102 | 104 | Bruce | Ernst | - | 103 | 6000 | 103 | 105 | David | Austin | - | 103 | 4800 | 103 | 106 | Valli | Pataballa | - | 103 | 4800 | 103 | 107 | Diana | Lorentz | - | 103 | 4200 | 103 | 108 | Nancy | Greenberg | - | 101 | 12008 | 101 | 109 | Daniel | Faviet | - | 108 | 9000 | 108 |
---|
COALESCE query performance
declare
emp_id number := 100;
begin
for rws in (
select * from hr.employees
where employee_id = coalesce ( to_number ( emp_id ), employee_id )
) loop
null;
end loop;
end;
Statement processed.
select *
from table(dbms_xplan.display_cursor('7d8nsu08g09fh'))
PLAN_TABLE_OUTPUT | SQL_ID 7d8nsu08g09fh, child number 0 | ------------------------------------- | SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = COALESCE ( TO_NUMBER ( | :B1 ), EMPLOYEE_ID ) | Plan hash value: 1445457117 | ------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | | | 3 (100)| | | |* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 | | ------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("EMPLOYEE_ID"=COALESCE(TO_NUMBER(TO_CHAR(:B1)),"EMPLOYEE_I | D")) |
---|
NVL optimization
declare
emp_id number := 100;
begin
for rws in (
select * from hr.employees
where employee_id = nvl ( emp_id, employee_id )
) loop
null;
end loop;
end;
Statement processed.
select *
from table(dbms_xplan.display_cursor('9vhyyc4c3cu8s'))
PLAN_TABLE_OUTPUT | SQL_ID 9vhyyc4c3cu8s, child number 0 | ------------------------------------- | SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = NVL ( :B1 , EMPLOYEE_ID ) | Plan hash value: 71496665 | -------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | -------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | | | 4 (100)| | | | 1 | VIEW | VW_ORE_B4851255 | 108 | 14364 | 4 (0)| 00:00:01 | | | 2 | UNION-ALL | | | | | | | |* 3 | FILTER | | | | | | | | 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 | | |* 5 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| | | |* 6 | FILTER | | | | | | | | 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 | | -------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - filter(:B1 IS NOT NULL) | 5 - access("EMPLOYEE_ID"=:B1) | 6 - filter(:B1 IS NULL) |
---|
COALESCE Short-circuiting
select coalesce ( 1, 1/0 ) from dual
COALESCE(1,1/0) | 1 |
---|
NVL argument evaluation
select nvl ( 1, 1/0 ) from dual
ORA-01476: divisor is equal to zeroMore Details: https://docs.oracle.com/error-help/db/ora-01476
Find all employees with less than 0.2% commission?
select
employee_id, first_name, last_name,
commission_pct
from hr.employees
where commission_pct < 0.2
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | COMMISSION_PCT | 155 | Oliver | Tuvault | .15 | 163 | Danielle | Greene | .15 | 164 | Mattea | Marvins | .1 | 165 | David | Lee | .1 | 166 | Sundar | Ande | .1 | 167 | Amit | Banda | .1 | 171 | William | Smith | .15 | 172 | Elizabeth | Bates | .15 | 173 | Sundita | Kumar | .1 | 178 | Kimberely | Grant | .15 | 179 | Charles | Johnson | .1 |
---|
Find all employees with less than 0.2% commission - fixed v1
select
employee_id, first_name, last_name,
commission_pct
from hr.employees
where ( commission_pct < 0.2 or commission_pct is null )
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | COMMISSION_PCT | 100 | Steven | King | - | 101 | Neena | Kochhar | - | 102 | Lex | De Haan | - | 103 | Alexander | Hunold | - | 104 | Bruce | Ernst | - | 105 | David | Austin | - | 106 | Valli | Pataballa | - | 107 | Diana | Lorentz | - | 108 | Nancy | Greenberg | - | 109 | Daniel | Faviet | - |
---|
Find all employees with less than 0.2% commission - fixed v2
select
employee_id, first_name, last_name,
commission_pct
from hr.employees
where nvl ( commission_pct, 0 ) < 0.2
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | COMMISSION_PCT | 100 | Steven | King | - | 101 | Neena | Kochhar | - | 102 | Lex | De Haan | - | 103 | Alexander | Hunold | - | 104 | Bruce | Ernst | - | 105 | David | Austin | - | 106 | Valli | Pataballa | - | 107 | Diana | Lorentz | - | 108 | Nancy | Greenberg | - | 109 | Daniel | Faviet | - |
---|
LNNVL
select
employee_id, first_name, last_name,
commission_pct
from hr.employees
where lnnvl ( commission_pct >= 0.2 )
fetch first 10 rows only
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | COMMISSION_PCT | 100 | Steven | King | - | 101 | Neena | Kochhar | - | 102 | Lex | De Haan | - | 103 | Alexander | Hunold | - | 104 | Bruce | Ernst | - | 105 | David | Austin | - | 106 | Valli | Pataballa | - | 107 | Diana | Lorentz | - | 108 | Nancy | Greenberg | - | 109 | Daniel | Faviet | - |
---|