Oracle SQL Built-in Functions - Database Principles

Oracle SQL Built-in Functions - Database Principles

Oracle SQL Built-in Functions Chapter 5 in Lab Reference Column Alias Names Example: Select name AS Employee From employee; EMPLOYEE ------------------------------- Jamil N.Samir Amani F.Zaki Jihan H.Walid Ramy S.Nabil Joyce A.Eman Ahmad V.Jabbar James B.Baher 7 rows selected. Built-in Functions 2

Column Alias Names When you want to include spaces or special characters in alias names, then enclose the alias name in double quotation marks. Example: Select name || ' has an id of ' || ssn "Important information" From employee; IMPORTANT INFORMATION --------------------------------------------------------------------------------- Jamil N.Samir has an id of 123456789 Amani F.Zaki has an id of 999887777 Jihan H.Walid has an id of 987654321 Ahmad V.Jabbar has an id of 987987987 James B.Baher has an id of 888665555 7 rows selected. Built-in Functions 3 Table Alias Names Example:

Select T.item_id, T.item_desc From item T; ITEM_ID ITEM_DESC --------------------------------------------------------- LA-101 NY-102 Built-in Functions Box, Small Bottle, Large 4 Number Functions ROUND The ROUND function rounds the value you want to modify. Example: Select product_name, product_price, ROUND(product_price,0)

From product; PRODUCT_NAME PRODUCT_PRICE ROUND(PRODUCT_PRICE,0) ---------------------------------------------------------------------------------------------------------------------------------------------- Roco Pencil FABER Pen Roco Pad Built-in Functions 3.95 5 2.2 4 5 2 5

Number Functions TRUNC The TRUNC function truncates precision from a number. Example: Select product_name, product_price, TRUNC(product_price,0) From product; PRODUCT_NAME PRODUCT_PRICE TRUNC(PRODUCT_PRICE,0) -------------------------------------------------------------------------------------------------------------------------------------------- Roco Pencil FABER Pen Roco Pad Built-in Functions 3.95 5 2.2

3 5 2 6 Number Functions MOD mod(m,n) Example: Select mod(salary,3) From employee where ssn=123456789; MOD(SALARY,3) ------------------------------------- 0 Built-in Functions 7 Number Functions POWER

power(m,n) number m raised to the power of n. Example: Select power(salary,2) From employee where ssn=123456789; POWER(SALARY,2) ------------------------------------------- 900000000 Built-in Functions 8 Number Functions SIGN & SQRT sign(n) if n=0 returns 0 if n>0 returns 1 if n<0 returns -1 sqrt(n) returns square root of n. Example:

Select sqrt(salary) From employee where ssn=123456789; Built-in Functions SQRT(SALARY) -------------------------------------- 173.20508 9 Text Functions UPPER, LOWER & INITCAP These three functions change the case of the text you give them. Example: Select UPPER(product_name) From product; UPPER(PRODUCT_NAME) -------------------------------------------------------- ROCO PENCIL Example:

Select LOWER(product_name) From product; FABER PEN ROCO PAD LOWER(PRODUCT_NAME) -------------------------------------------------------- roco pencil faber pen roco pad Built-in Functions 10 Text Functions UPPER, LOWER & INITCAP Example: Select INITCAP(product_name) From product; INITCAP(PRODUCT_NAME) ----------------------------------------------------------

Roco Pencil Faber Pen Roco Pad Built-in Functions 11 Text Functions LENGTH To determine the lengths of the data stored in a database column. Example: Select product_name, LENGTH(product_name) AS Name_Length From Product where LENGTH(product_name)>8; PRODUCT_NAME NAME_LENGTH --------------------------------------------------------------------------------- FABER Pen

Roco Pencil Built-in Functions 9 11 12 Text Functions SUBSTR To separate multiple bits of data into discrete segments. Example: Select SUBSTR(item_id,1,2) Location, SUBSTR(item_id,4,3) Number, Item_desc From item; LOCATION NUMBER ITEM_DESC --------------------------------------------------------------------------------------------- LA

NY Built-in Functions 101 102 Box, Small Bottle, Large 13 Text Functions INSTR Useful when you have substrings vary in length. This mean not only is the length of the first substring is unknown, but the starting position of the second substring can also vary. Example: Select item_desc, INSTR(item_desc, , , 1) From item; ITEM_DESC

INSTR(ITEM_DESC, , , 1) -------------------------------------------------------------------------------------------- Box, Small Bottle, Large Built-in Functions 4 7 14 Text Functions REPLACE Replace(char, str1, str2) Every occurrence of str1 in char is replaced by str2. Example: Select Replace(name,'Jamil','Sara') From employee; REPLACE(NAME,'JAMIL','SARA') -------------------------------------------------------------------

Sara N.Samir Amani F.Zaki Jihan H.Walid Ramy S.Nabil Joyce A.Eman Ahmad V.Jabbar James B.Baher 7 rows selected. Built-in Functions 15 Text Functions Concatenation operator || To concatenate column names with other column names or with literal characters. Example: NAME||HAS AN ID OF'||SSN Select name || has an id of || ssn

From employee; ------------------------------------------------------------------------------ Jamil N.Samir has an id of 123456789 Amani F.Zaki has an id of 999887777 Jihan H.Walid has an id of 987654321 Ramy S.Nabil has an id of 666884444 Joyce A.Eman has an id of 453453453 Ahmad V.Jabbar has an id of 987987987 James B.Baher has an id of 888665555 7 rows selected. Built-in Functions 16 Date Functions Function Sysdate Add_months(d, n)

Description Syntax Get current system date and time. INSERT INTO employee VALUES (, trunc(sysdate),.); Adds n months to date d. ADD_MONTHS(starting_date, number_of_months) Difference in months MONTHS_BETWEEN(later_date, Months_between(f, s) between date f and earlier_date) date s. Built-in Functions

17 Data Conversion Functions Function Description To_char(input_value, format_code) Converts any data type to character data type. To_number(input_value, format_code) Converts a valid set of numeric character data to number data type. To_date(input_value, format_code) Built-in Functions Converts character data of the proper

format to date data type. 18 Null values Some columns may contain Null values. You can use the NVL function to display actual values instead of null values in a query result. NVL(column|expression, replacement_value) Replacement_value must be of the same data type of the column (if not use data conversion functions). Built-in Functions 19 Null values Example: Select name, NVL(SUPERSSN, 333445555)

From employee; NAME NVL(SUPERSSN, 333445555) ------------------------------------------------------------------------------------------------- Jamil N.Samir Amani F.Zaki Jihan H.Walid Ramy S.Nabil Joyce A.Eman Ahmad V.Jabbar James B.Baher 333445555 987654321 888665555 333445555 333445555 987654321 333445555

7 rows selected. Built-in Functions 20

Recently Viewed Presentations

  • Gender Criticism - Holman&#x27;s AP English IV

    Gender Criticism - Holman's AP English IV

    Gender Criticism. Advantages. Women have been somewhat underrepresented in the traditional canon; a feminist approach to literature helps redress this problem. Disadvantages. Feminist critics turn literary criticism into a political battlefield and overlook the merits of works they consider "patriarchal."
  • BIOGRAPHIES of PARTICIPANTS MANFRED AUSTER is Minister-Counsellor and

    BIOGRAPHIES of PARTICIPANTS MANFRED AUSTER is Minister-Counsellor and

    Her main interests lie in international and Canadian refugee and immigration policy, with an emphasis on international human rights, gender‑sensitivity and anti‑racism, and in NGO networking and advocacy. Janet has a BA (in Classics and Modern Languages) from the University...
  • Section 2 : The Impacts of energy insecurity

    Section 2 : The Impacts of energy insecurity

    Section 2 : The Impacts of energy insecurity Key ideas Energy pathways, between producers and consumers, are complex and show increasing levels of risk What do we mean by an 'energy pathway'? Look at the information on page 24 of...
  • FPGA-Optimised High-Quality Uniform Random Number Generators

    FPGA-Optimised High-Quality Uniform Random Number Generators

    School of Engineering University of Guelph FPGA-Optimised High-Quality Uniform Random Number Generators By David Barrie Thomas and Wayne Luk of the Imperial College, London, England
  • Benchmarking Your Portfolio January 13, 2011 By: David

    Benchmarking Your Portfolio January 13, 2011 By: David

    Provide money market-like liquidity with incremental returns to traditional Rule 2a-7 funds Capture money market return Manage liquidity and credit risks Reinvest cash flows timely (critical to achieving competitive return profile) Capture best duration risk-adjusted returns with the "Core" portfolio...
  • CDC Method - Cedar Grove High School

    CDC Method - Cedar Grove High School

    Data. Commentary. Jay Z went from being a drug dealer to being a business man. "I be the d-boy who infiltrated all the corporate dudes" No longer is he a small time drug dealer, now he is a multimillionaire businessman...
  • Kits for Kids Solution Fluency Project

    Kits for Kids Solution Fluency Project

    Solution Fluency Project. Presented by: Ruby, Kelsey, Lauren & Maya. Summarized Goal . Our plan is to create care packages that include necessities, and send them to kids who live in refugee camps. Our goal is to start by creating...
  • Water Safety - NASA

    Water Safety - NASA

    "Principle of redundancy of potential command - Effective action is achieved by an adequate concatenation of information. In amanagement structure, the potential to act effectively belongs to that subset of management that first acquires the proper information. In other words,...