Oracle SQL Builtin Functions Chapter 5 in Lab

Oracle SQL Built-in Functions Chapter 5 in Lab Reference

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. Built-in Functions 2

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 3

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 4

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 5

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 6

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 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) ---------------------- 90000 Built-in Functions 8

Number Functions SQRT sqrt(n) returns square root of n. Example: Select sqrt(salary) From employee where ssn=123456789; SQRT(SALARY) ------------------- 173. 20508 Built-in Functions 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; Example: UPPER(PRODUCT_NAME) ---------------------------- ROCO PENCIL FABER PEN ROCO PAD LOWER(PRODUCT_NAME) ---------------------------- Select LOWER(product_name) From product; Built-in Functions roco pencil faber pen roco pad 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_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 REPLACE Replace(char, str 1, str 2) Every occurrence of str 1 in char is replaced by str 2. 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 14

Date Functions Function Sysdate Built-in Functions Description Get current system date and time. Syntax INSERT INTO employee VALUES (…………, trunc(sysdate), ………. ); 15

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. 16