Chapter 9 Examples

p. 326 - Create a new table with price and price_increase added together

a) What happens if no name specified for new column
select l_foods.*,
       price + price_increase
into sec0902_foods_a
from l_foods;

b)
select l_foods.*,
       price + price_increase as new_price
into sec0902_foods_b
from l_foods;

c)
select f.*,
       price + price_increase as new_price
into sec0902_foods_c
from l_foods f;

------------------------------------------------------
p. 328  using a row function in the select clause

select menu_item,
       description,
       price + price_increase as new_price
from l_foods
where menu_item < 15
order by menu_item;

------------------------------------------------------
p. 330 rOW function in all clauses of select statement

select menu_item,
       description,
       price + price_increase as new_price
from l_foods
where (price + price_increase) > 2.00
order by (price + price_increase);

------------------------------------------------------
p. 332 Defining row function in two steps

a) define row function, then save query

select menu_item,
       description,
       price + price_increase as new_price
from l_foods;

b) Use saved view/query without havcing to repeat row function

select menu_item,
       description,
       new_price
from sec0905_step1_view
where new_price > 2.00
order by new_price;

------------------------------------------------------
p. 336 Testing a row function

a) using table 
select 3 * 4
from dual;

b) without using a table

select 3 * 4;

------------------------------------------------------
p. 334-335 Numeric Functions

a) Addition

select 3 + 5;

select 3 + null;

b) Subtraction

select 3 - 2;

select 3 - null;

c) Multiplication

select 3 * 2;

select 3 * null;

d) Division

select 10 / 3;

select 10 / null;

e) value raised to an exponent

select 5 ^ 2;

f) Square root

select sqr(25);

g) Integer division

select 20 \ 3;

select 20 \ null;

h) Remainder from integer division

select 10 mod 3;

i) positive/negtive

select sgn(-8);

j) Absolute value

select abs(-8);

k) Largest integer less than or equal to a value  (convert decimal to integer)

select int(3.5);

------------------------------------------------------
p. 337  Patterns in numeric functions

select n,
       n mod 3
from sec0908_test_numbers
order by n;

------------------------------------------------------
p. 340 - 341 Text functions

a) Concatenation

select 'sun' & 'flower';

select 'sun' + 'flower';

b) Substring

select mid('sunflower', 4, 3);

c) Replace part of a string with another

select replace ('ABCABC', 'AB', '1234');

D) Create a string of a particular length

select string(5, 'A');

e) Upper case conversion

select ucase('sunflower');

select StrConv('sunflower', 1);

f) Lowercase conversion

select lcase('SUNFLOWER');

select StrConv('SUNFLOWER', 2);

g) Capitalize first letter

select StrConv('sun flower', 3);

h) Trim blanks left

select ltrim('     hello world     ');

i) Trim blanks right

select rtrim('     hello world     ');

j) Trim blanks both left and right

select trim('     hello world     ');

k) Create a string of vblanks a specific length

select space(5);

l) Find the number of characters in a string

select len('sunflower');

m) Starting position of one string within another

select InStr('sunflower', 'low');

select InStr('sunflower', 'zzz');

------------------------------------------------------
p. 344 Combining to text columns

select employee_id,
       first_name & ' ' & last_name as full_name
from l_employees;

------------------------------------------------------
p. 347 Dividing full name into first name and last name

a) find the position of the blank between first name and last name & save query

select full_name, 
       InStr(full_name, ' ') as position_of_space
from sec0911_full_name;

b) using position of space from first query, divide firwt name from last name

select full_name,
       position_of_space,
       mid(full_name, 1, position_of_space - 1) as first_name,
       mid(full_name, position_of_space + 1) as last_name
from sec0911_step1_view;

------------------------------------------------------
p. 349  Formattring phone numbers

select employee_id,
       first_name,
       last_name,
       '(415) 643 - ' & phone_number as phone_number2
from l_employees; 

------------------------------------------------------
p. 351 - 353 Date Functions

a) Date plus days

select #20-jan-2015# + 3;

select DateAdd('d', 3, #01-20-2015#);

b) Date minus days

select #20-jan-2015# - 3;

select DateAdd('d', -3, #01-20-2015#);

c) Number of Days between two dates

select #23-jan-2015# - #20-jan-2015#;

select DateDiff('d', #01-20-2015#, #01-23-2015#);

d) Returns day of the month from date

select Day(#20-jan-2015#);

e) Returns month from date

select Month(#20-jan-2015#);  

f) Returns year from date

select Year(#20-jan-2015#);

g) Returns day of the week from date

select Weekday(#20-jan-2015#);

h) Creates a date from a list of values

select DateSerial(2015, 1, 20);

i) Sets time portion of dateto midnight

select DateValue(#20-jan-2015 5:00 pm#);

j) Add a specific number of months to a date

select DateAdd('m', 3, #21-jan-2025#);

k) Caluculates the number of months between two dates

select DateDiff('m', #21-jan-2025#, #21-apr-2025#);

------------------------------------------------------
p. 355 Example of a date function and math function

select first_name,
       last_name,
       hire_date,
       int((#01-jan-2011# - hire_date) / 30) as months_with_the_company
from l_employees;


select first_name,
       last_name,
       hire_date,
       int((now() - hire_date) / 30) as months_with_the_company
from l_employees;
------------------------------------------------------
p. 357 - showing dates with time

select lunch_id,
       lunch_date,
       employee_id,
       format(date_entered, 'DD-MMM-YYYY HH:NN AM/PM') as date_entered2
from l_lunches;

select lunch_id,
       lunch_date,
       employee_id,
       format(l.date_entered, 'DD-MMM-YYYY HH:NN AM/PM') as date_entered
from l_lunches l;

------------------------------------------------------
p. 357 - removing time from dates

select lunch_id,
       lunch_date,
       employee_id,
       format(DateValue(date_entered), 'DD-MMM-YYYY HH:NN AM/PM') as date_entered2
from l_lunches;

select lunch_id,
       lunch_date,
       employee_id,
       format(DateValue(l.date_entered), 'DD-MMM-YYYY HH:NN AM/PM') as date_entered
from l_lunches l;
------------------------------------------------------