code: select employee_id, first_name, salary, commission_pct, salary+(salary*commission_pct) "Total Salary" from employees;

[Note: The problem with this query is that if the commission_pct value is null then the total salary will also be null. To avoid this we use this query below. Null is a junk value, so we cannot use arithmetic operator along with it. ]

code: select employee_id, first_name, salary, nvl(commission_pct,0), salary+(salary*nvl(commission_pct,0)) "Net Salary" from employees;

[Note: In this nvl function we convert null value of commission_pct to 0. nvl stands for null to value.]

code: select round (243.567,2), trunc (243.56712) from dual;

code: select count(*) from employees;

code: select count(*) from employees where department_id=50;

code: select department_id, count(*) from employees group by department_id;

[Note: As previously told we can't use aggregate function or single value return function with field name or column name unless we use another clause 'group by' along with the field name that we used with the aggregate function.]

code: select department_id, count(*) from employees where department_id <> 50 or department_id is null group by department_id;

code: select department_id, count(*) from employees where department_id != 50 or department_id is null group by department_id;

[Note: We can use not equals to like this <> or !=]

code: select department_id, max(salary) from employees group by department_id;

code: select department_id, count( * ) from employees group by department_id having count( * )<=5;

[Note: We are using having clause instead of where clause because. With where clause we cannot use single value return function, although we can use multiple value return function like upper, lower, length since they can return multiple value. But count, max, min, avg these functions we cannot use with where clause, so we are using having clause. It's important to note that if we are using having clause we need to use group by clause, because without it we cannot use having clause.]

code: select first_name, count( * ) from employees group by first_name having count( * )>1;

code: select max(salary) from employees;

code: select employee_id, first_name, salary, department_id from employees where salary=24000;

code: select employee_id, first_name, salary, department_id from employees where salary=( select max(salary) from employees);

[Note: The first part that will be executed will be the part that is within (). The outer part will take the value of inner part and execute.]

code: select employee_id, first_name, salary, department_id from employees where salary> (select avg(salary) from employees);

code: select employee_id, first_name, salary, department_id from employees where salary=(select max(salary) from employees where salary <(select max(salary) from employees));

[Note: The innermost query will find the highest salary then the second innermost query will select all salary that is just lower to the highest salary. Finally the outermost query will display all the employee details that has the second most highest salary. ]

[innermost query- "(select max(salary) from employees)"

second innermost query- "(select max(salary) from employees where salary <(select max(salary) from employees))"

outermost query- "select employee_id, first_name, salary, department_id from employees where salary=(select max(salary) from employees where salary <(select max(salary) from employees)); "]

code: select extract(year from hire_date), count(*) from employees group by extract(year from hire_date) order by extract(year from hire_date);

code: select extract(month from hire_date), count(*) from employees group by extract (month from hire_date) order by extract(month from hire_date);

code: select emplopyee_id, first_name, salary, department_id from employees where (department_id, salary) in (select department_id, max(salary) from employees group by department_id) order by department_id;