Chapter 10 - Specialized Row Functions

p. 366 - curentuser & now functions

select CurrentUser() as user,
       format (Now(),  'DDDD MMMM DD, YYYY HH:NN AM/PM') as date_time;

-----------------------------------------------------
p.  367 - Replacing nulls with other values - nz function, same data types

select pkey, 
       nz (num_col, 0) as num_col2,
       nz (text_col, 'ZILCH') as text_col2,
       nz (date_col, #01-JAN-1900#) as date_col2
from sec1003;
       
-----------------------------------------------------
p. p.  368 - Replacing nulls with other values - nz function, all text

select pkey, 
       nz (num_col, 'NO NUMBER') as num_col2,
       nz (text_col, 'NO TEXT') as text_col2,
       nz (date_col, 'NO DATE') as date_col2
from sec1003;

-----------------------------------------------------
p. 370 Problems with automatic datatype conversion

select pkey, 
       text_1,
       text_2,
       text_1 + text_2 as text_add,
       text_1 - text_2 as text_subtract,
       text_1 * text_2 as text_multiply,
       text_1 / text_2 as text_divide
from sec1004;

-----------------------------------------------------
p. 371 Correction to automatic datatype conversion

select pkey, 
       text_1,
       text_2,
       cint(text_1) + cint(text_2) as text_add,
       text_1 - text_2 as text_subtract,
       text_1 * text_2 as text_multiply,
       text_1 / text_2 as text_divide
from sec1004;

-----------------------------------------------------
p. 378 Creating number patterns

a)
select n, 
       3 * n as multiple_of_3
from numbers_0_to_99;

b)
select multiple_of_3
from sec1008_view
where multiple_of_3 between 50 and 250
order by multiple_of_3;

-----------------------------------------------------
p. 380 Creating complex number patterns

select n as prime_number
from numbers_0_to_99
where n > 10
      and not ((n mod 2) = 0)
      and not ((n mod 3) = 0)
      and not ((n mod 5) = 0)
      and not ((n mod 7) = 0)
order by n;

-----------------------------------------------------
p. 381 - 383 Listing all the days of one week

a) create table of constants

create table sec1010_constants
(begin_date datetime);

insert into sec1010_constants
values (#24-FEB-2010#);

b) Create a query/view containing start date & next 7 days

select cdate(begin_date + digit) as days
from numbers_0_to_9, 
     sec1010_constants
where digit < 7;

c) List days of the week formatted in 3 ways

select days, 
       format(days, 'DDD') as abbreviated_day,
       format(days, 'DDDD') as full_day
from sec1010_view
order by days;

-----------------------------------------------------
p. 384 - 387 

a) Create table of constants

create table sec1011_boundaries
(start_date     datetime,
 end_date       datetime);

insert into sec1011_boundaries
values (#01-MAR-2015#, #01-JUN-2015#);

b) Create a table with all the consecutive days between start date and end date

select n,
       cdate(start_date + n) as date_1
into sec1011_calendar
from numbers_0_to_99,
     sec1011_boundaries
where start_date + n < end_date;

c) Delete Sundays & set Saturdays to null to create a blank line

delete from sec1011_calendar
where format(date_1, 'DDD') = 'SUN';

update sec1011_calendar
    set date_1 = null
where format(date_1, 'DDD') = 'SAT';

d) Display the calendar

select format(date_1, 'DDDD') as day_of_the_week,
       format(date_1, 'MM/DD/YYYY') as work_day
From sec1011_calendar
order by n;

-----------------------------------------------------
p. 389 - Find out how old you are

select now() - #24-DEC-1958# as days_old;
-----------------------------------------------------
p. 390 - Find out when you'll be 10,000 days old

select #24-DEC-1958# + 10000 as celebration_day;

-----------------------------------------------------
p. 392 - creating row numbers

a) create a table with an order by clause

select price, 
       description
into sec1014_table
from l_foods
where price > 1.75
order by price, description;

b) Add a column of row numbers

alter table sec1014_table
add column line_number counter;

c) Select columns in desired order (or mov column in GUI)

select line_number,
       price,
       description
from sec1014_table;

-----------------------------------------------------
p. 394 - 396 - Solve an algebraic equation

a) Calculate the value of the function between 0 and 99

select n, 
       ((n*n*n*n) - 91 * (n*n*n) + 66 * (n*n) - 451 * n - 5913) as value_of_function
from numbers_0_to_99
order by n;

b) Generate the numbers between 90.00 and 90.99

select n,
       90 + (n/100) as m
from numbers_0_to_99
order by n;

c) Calculate the value of the function between 90.00 and 90.99

select m, 
      ((m*m*m*m) - 91 * (m*m*m) + 66 * (m*m) - 451 * m - 5913) as value_of_function
from sec1015_view
order by (M * 100);

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