1 / 20

Oracle SQL Built-in Functions

Oracle SQL Built-in Functions. Chapter 5 in Lab Reference. Column Alias Names. SELECT column_name AS alias_name FROM table_name;. Example: Select name AS Employee From employee;. EMPLOYEE ------------------------------- Jamil N.Samir Amani F.Zaki Jihan H.Walid

shilah
Télécharger la présentation

Oracle SQL Built-in Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle SQL Built-in Functions Chapter 5 in Lab Reference

  2. Column Alias Names SELECT column_name AS alias_name FROM table_name; Example: Select name AS Employee From employee; EMPLOYEE ------------------------------- JamilN.Samir AmaniF.Zaki JihanH.Walid RamyS.Nabil Joyce A.Eman Ahmad V.Jabbar James B.Baher 7 rows selected.

  3. Column Alias Names When you want to include spaces or special charactersin 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 --------------------------------------------------------------------------------- JamilN.Samirhas an id of 123456789 AmaniF.Zakihas an id of 999887777 JihanH.Walidhas an id of 987654321 Ahmad V.Jabbarhas an id of 987987987 James B.Baherhas an id of 888665555 7 rows selected.

  4. Table Alias Names Example: Select T.item_id, T.item_desc From item T; • item_iditem_desc • --------------------------------------------------------- • LA-101 Box, Small • NY-102 Bottle, Large

  5. Number FunctionsROUND SELECT ROUND(column_name,decimals) FROM table_name; The ROUND function rounds the value you want to modify. Example: Select product_name, product_price, ROUND(product_price,0) From product; • product_nameproduct_price ROUND(product_price,0) • ---------------------------------------------------------------------------------------------------------------------------------------------- • Roco Pencil 3.95 4 • FABER Pen 5 5 • Roco Pad 2.2 2

  6. Number FunctionsTRUNC The TRUNC function truncates precision from a number. Example: Select product_name, product_price, TRUNC(product_price,0) From product; • product_nameproduct_price TRUNC(product_price,0)-------------------------------------------------------------------------------------------------------------------------------------------- • Roco Pencil 3.95 3 • FABER Pen 5 5 • Roco Pad 2.2 2

  7. Number FunctionsMOD Returnsthe remainder. Example: Select mod(salary,3) From employee where ssn=123456789; MOD( dividend, divider ) MOD(SALARY,3) ------------------------------------- 0

  8. Number FunctionsPOWER 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

  9. Number FunctionsSIGN & 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; SQRT(SALARY) -------------------------------------- 173.20508

  10. Text FunctionsUPPER, LOWER & INITCAP These three functions change the case of the text you give them. Example: Select UPPER(product_name) From product; Example: Select LOWER(product_name) From product; • UPPER(product_name) • -------------------------------------------------------- • ROCO PENCIL • FABER PEN • ROCO PAD • LOWER(product_name) • -------------------------------------------------------- • roco pencil • faber pen • roco pad

  11. Text FunctionsUPPER, LOWER & INITCAP Example: Select INITCAP(product_name) From product; • INITCAP(product_name) • ---------------------------------------------------------- • Roco Pencil • Faber Pen Roco Pad

  12. Text FunctionsLENGTH 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_nameNAME_Length • --------------------------------------------------------------------------------- • FABER Pen 9 • Roco Pencil 11

  13. Text FunctionsSUBSTR SUBSTRING ( expression ,start , length ) 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 101 Box, Small NY 102 Bottle, Large

  14. Text FunctionsINSTR 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 4 Bottle, Large 7

  15. Text FunctionsREPLACE 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') • ------------------------------------------------------------------- • SaraN.Samir • AmaniF.Zaki • JihanH.Walid • RamyS.Nabil • Joyce A.Eman • Ahmad V.Jabbar • James B.Baher • 7 rows selected.

  16. Text Functions Concatenation operator || To concatenate column names with other column names or with literal characters. Example: Select name ||‘ has an id of ‘|| ssn From employee; • NAME||’HAS AN ID OF'||SSN • ------------------------------------------------------------------------------ • 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.

  17. Date Functions

  18. Data Conversion Functions

  19. 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).

  20. Null values Example: Select name, NVL(SUPERSSN, ‘333445555‘) From employee; NAME NVL(SUPERSSN, ‘333445555’) ------------------------------------------------------------------------------------------------- Jamil N.Samir 333445555 Amani F.Zaki 987654321 Jihan H.Walid 888665555 Ramy S.Nabil 333445555 Joyce A.Eman 333445555 Ahmad V.Jabbar 987654321 James B.Baher 333445555 7 rows selected.

More Related