Banner DATBASE Workshop-II

1
Banner DATBASE Workshop-II

• Girija Chavala
• Northwest Oracle/Banner DBA

2
Using Psuedo Names for columns

• some of the column names are not appropriate for
  column headings
• Can use alias for column headings
• SELECT NW_TAX_ID TAXID,
• NW_COMPANY_NAME NAME,
• NW_STREET_LINE1 STREET,
• NW_CITY CITY,
• NW_STATE STATE,
• NW_YEARLY_SALES SALES
• FROM NWVEND

3
Caution with Pseudo names

• if pseudo column name has more than one word,
  place the words in
• For example
• SELECT nw_tax_id ID,
• nw_yearly_sales Yearly Sales
• FROM nwvend

4
Concatenating fields in SELECT

• To join 2 column data, Use (pipe)
• No restriction on data types of the column
• Can join words or symbols with column data such
  as or
• Can join spaces between columns

5
Joining columns exercise

• Describe NWSSN
• Type in the following
• SELECT nw_ssn SSN,
• nw_first_name
• nw_last_name NAME
• FROM nwssn

6
String functions in SELECT

• To convert upper case data to mixed case, use
  function called INITCAP
• Changes the 1st letter of a word or words changes
  into Upper case
• Always use column to be converted in ()
• Example
• SELECT INITCAP(nw_company_name) FROM
• NWVEND

7
String function in SELECT

• To convert data to lower case or upper case
• For lower case
• SELECT LOWER(nw_street_line2) FROM
• NWVEND
• For UPPER case
• SELECT UPPER(nw_last_name) FROM
• NWSSN

8
SUBSTRING FUNCTION

• Clips out a piece of a string
• It is useful for displaying or searching piece of
  the data
• For example, some zip codes are stored as 54 and
  some may have only 5 digits
• SELECT SUBSTR(nw_zip,1,5) ZIP
• FROM NWVEND
• List last 4 characters of SSN
• SELECT SUBSTR(nw_ssn,5,4) LAST 4
• FROM nwssn

9
DATE FUNCTION

• ADD_MONTHS adds months to the date
• LAST_DAY -- brings last day in the month
• MONTHS_BETWEEN returns difference between the
  dates
• NEXT_DAY -- returns next day of the week you
  specified
• Adding days can add days to current date

10
EXERCISES FOR DATE FUNCTIONS

• SELECT nw_effective_date, ADD_MONTHS(nw_effective_
  date,2) FROM nwvend
• --------------------------------------------------
  ----------------
• SELECT LAST_DAY(nw_effective_date) FROM nwvend
• --------------------------------------------------
  ----------------
• SELECT nw_termination_date, nw_effective_date,
• months_between(nw_termination_date,nw_effective_da
  te)
• FROM nwvend
• --------------------------------------------------
  -----------------
• SELECT nw_effective_date,
• NEXT_DAY(nw_effective_date,FRIDAY)
• FROM nwvend
• --------------------------------------------------
  -------------------
• SELECT nw_effective_date,
• sysdate60 60 days from today
• FROM nwvend

11
SELECT with filters (search criteria)

• WHERE clause operators
• equal
• ltgt not equal
• ! not equal
• lt less than
• lt less than or equal to
• !lt not less than
• gt greater than
• gt greater than or equal to
• !gt not greater than
• BETWEEN between two specified values
• IS NULL is a null values
• IS NOT NULL is a not null value
• IN range of values
• LIKE used with wild card

12
SELECT with WHERE clause
Key word(s)
Search criteria
13
SELECT with WHERE clause

• When to use single quotes in WHERE clause
• no quotes necessary for number columns
• quotes are necessary for alphanumeric columns
• No quotes necessary for date fields
• Date fields are used with format date is stored
  with TO_DATE function

14
SELECT with WHERE clause

• SELECT nw_tax_id, nw_company_name
• FROM nwvend
• WHERE nw_state MO
• CAUTION alpha fields are case sensitive. WHERE
  clause looks for exact match

15
SELECT with WHERE clause

• SELECT nw_tax_id,
• nw_company_name,
• nw_yearly_sales
• FROM nwvend
• WHERE nw_yearly_sales gt 500000
• ORDER BY nw_yearly_sales DESC
• ------------------------------------------------
• SELECT nw_tax_id,
• nw_company_name,
• nw_effective_date,
• nw_termination_date
• FROM nwvend
• WHERE nw_termination_date is not null

16
SELECT with WHERE clause

• SELECT FROM nwvend
• WHERE nw_state IN (IA,NE,KS)
• -------------------------------------------------
• SELECT nw_company_name,
• nw_state
• FROM nwvend
• WHERE substr(nw_zip,1,5) ! 64468
• ! is same as ltgt

17
LIKE operator with wild card

• SELECT nw_company_name
• FROM nwvend
• WHERE nw_company_name like B
• ---------------------------------------------
• SELECT nw_company_name
• FROM nwvend
• WHERE nw_company_name like PRINT
• -----------------------------------------------
• SELECT nw_company_name
• FROM nwvend
• WHERE nw_company_name like DENNIS

18
USING WILD CARDS

• Powerful tool
• But processing take longer
• Dont over use
• Dont use wild card in the beginning of a string.
  It takes longer to search.
• placing wild card needs some planning

19
Mathematical calculations

• Mathematical operators
• add
• Subtract
• Multiplication
• / Division
• SELECT nw_company_name,
• nw_yearly_sales/12 MONTHLY SALES
• FROM nwvend
• CAUTION to display only 2 decimal values use
  round function.

20
MATHEMATICAL CALCULATIONS

• SELECT nw_company_name,
• ROUND(nw_yearly_sales/12,2) MONTHLY SALES
• FROM nwvend
• ------------------------------------------------
• Amount is rounded to decimal places.
• ------------------------------------------------
• SELECT nw_company_name,
• TRUNC(nw_yearly_sales/12) MONTHLY SALES
• FROM nwvend
• TRUNC function does not require any decimal
  places.