Chapter 11 SQL Examples

Column Functions

-------------------------------------------------
p. 405  Min & Max Functions Examples

select min(credit_limit),
       max(credit_limit),
       max(first_name),
       max(last_name),
       max(hire_date)
from l_employees;

-------------------------------------------------
p. 407  Min & Max used with the where clause

select min(credit_limit),
       max(credit_limit),
       max(first_name),
       max(last_name),
       max(hire_date)
from l_employees
where employee_id between 202 and 206;

-------------------------------------------------
p. 409-410  Finding the rows with the Min/Max value

a) Method 1

select min(credit_limit)
from l_employees;

select employee_id,
       first_name,
       last_name,
       credit_limit
from l_employees
where credit_limit = 15.00
order by employee_id;

b) Method 2

select employee_id,
       first_name,
       last_name,
       credit_limit
from l_employees
where credit_limit = (select min(credit_limit)
                      from l_employees)
order by employee_id;

-------------------------------------------------
p. 412  Counting Rows excluding null values

select count(*),
       count(last_name),
       count(hire_date),
       count(manager_id)
from l_employees;

-------------------------------------------------
p. 414 Counting with Nulls

select count(col_2) as count_col,
       count(*) as count_rows,
       max(col_2) as max,
       min(col_2) as min,
       sum(col_2) as sum,
       avg(col_2) as avg
from sec1107;

-------------------------------------------------
p. 416  Counting distinct values in one column

a)
select distinct manager_id
into sec1108_step1
from l_employees;

b) 
select count(manager_id)
from sec1108_step1;

-------------------------------------------------
p. 419-420  Counting distinct values in 2 or more columns

a)
select distinct manager_id,
                credit_limit
into sec1109_manager_credit
from l_employees;

b)
select count(*)
from sec1109_manager_credit;

-------------------------------------------------
p. 421  Sum and Average

select sum(credit_limit),
       avg(credit_limit)
from l_employees;

-------------------------------------------------
p. 423  problem with Null and addition

select sum(col_2) + sum(col_3) as columns_added_first,
       sum(col_2 + col_3) as rows_added_first
from sec1111;

-------------------------------------------------
p. 425 Correct addition (Remember, 0 is optional in nz() function)

select sum(nz(col_2, 0)) + sum(nz(col_3, 0)) as columns_added_first,
       sum(nz(col_2, 0) + nz(col_3, 0)) as rows_added_first
from sec1111;

-------------------------------------------------
p. 429 - Estimating for Null values

select sum((price * quantity)
       + nz(tax, 0.07 * price * quantity)
       + nz(shipping, 0.12 * price * quantity)) as total_invoices
from sec1112_shipping;
 
-------------------------------------------------
p. 430  Count Null values

select count(*) as number_of_nulls
from l_employees
where manager_id is null;

-------------------------------------------------
p. 432  Couting distinct dates

a) 
select distinct format(date_entered, 'YYYY-MM-DD') as date_entered_2
into temp_date
from l_lunches;

b)
select count(date_entered_2)
from temp_date;
-------------------------------------------------

-------------------------------------------------

-------------------------------------------------

-------------------------------------------------

-------------------------------------------------


-------------------------------------------------

-------------------------------------------------

-------------------------------------------------

-------------------------------------------------