SQL – Functions Single-Row & Aggregate

1. SQL – FunctionsSingle-Row & Aggregate CIS 310

2. Datatype Conversion Functions in ORACLE • Many datatypes are automatically (implicitly) converted when differing but compatible datatypes are used in an expression. • Explicit conversion is required primarily for conversion and display of date/time data to character form and conversion of character strings to date/time values. • There are 3 explicit conversion functions

3. TO_CHAR Formatting of Numbers TO_CHAR(number, ‘formatting chars.’) • , and . – literals • $ literal prints just in front of first printed numeric digit. • 9s – Replaced by numeric value, blank if leading 0 • 0s – Replaced by numeric values 0 printed if leading 0

4. Examples of TO_CHAR for numbers • To_char(18.75, ‘$990.00’) -> $18.75 • To_char(18.75, ‘$000.00) -> $018.75 • To_char(1275.5, ‘99,990.00’) -> 1,276.50

5. Commonly used Single Row Numeric Functionsin ORACLEThese Functions accept Numeric input and return Numeric values

6. Example of round with #s trunc w. dates • Round(Sale_Tot_Bill,0) –Rounds to nearest whole number • Round(Unit_Weight,1) – gives value to nearest tenth e.g. 2.5 • Trunc(Ord_date,‘MON’) – would display 1-Oct-2012 for a date of 15-Oct-2012

7. Arithmetic operations can be a part of the SQL statement similar to any programming language. Arithmetic operators include the plus sign, minus sign, the asterisk for multiplication, and the / for division. Addition and subtraction is done before multiplication and division. Parenthesis can be used to modify the order of the calculations. Examples Select prod_code, Standard_price - 5 from PRODUCT Gives a $5 discount on the Standard_Price in the display Five is not subtracted from the Standard_Price in the table Select prod_code, (Standardt_price + 5) / 2 from PRODUCT Adds 5 to the Standard_price and divides the sum by 2 Arithmetic Operations

8. Date Formatting • Dates are stored as a consolidated date/time value • They are internally stored as numeric values that increase by 1 for each day, and are fractionally incremented to represent the time of day. • E.g. if midnight today is 533,278, 8 AM is 533,278.333 and noon is 533,278.5 • By default dates are displayed as DD-MON-YY • E.g. 12-DEC-12 • Dates entered as character values formatted either as ‘DD-MON-YYYY’ or ‘DD-MON-YY’ are implicitly converted to dates for storage • E.g. ’12-DEC-2012’ or ‘12-DEC-12’

9. 0 Commonly Used Date Format Elements • Oracle provides a large number of date and numeric format elements which can be used in the fmt portion of the conversion functions. • Only a small subset of the most frequently used date format elements are presented here.

10. Altering the Format of Dates • For dates, the form of the to_char function is TO_CHAR(column_name , 'date/time formatting characters'). • For example: select ord_no, to_char(ord_date, 'mm-dd-yy') as date1, to_char(ord_date, 'Mon-yyyy') as date2 from sale; • Produces output like: ORD_NO DATE1 DATE2 ---------- -------- -------- 2901 10-23-10 Oct-2010 2902 10-23-10 Oct-2010 2913 10-24-10 Oct-2010

11. Date Formatting - Continued • For dates, the form of the to_char function is TO_CHAR(column_name , 'date/time formatting characters'). • For example: select Emp_Id, Start_Time, to_char(End_time, ‘HH:MI AM – DD-MON') as date1, to_char(End_Time, ‘HH24:MI – MM-DD-YY') as date2 from EMP_HOURS; • Produces output like: EMP_ID START_TIME DATE1 DATE2 --------- ------------------------- ----------------- ---------------- 57 10-DEC-12 05:00 PM – 10-DEC 17:00 – 12-10-12 17 10-DEC-12 04:03 PM – 10-DEC 16:03 – 12-10-12 28 10-DEC-12 04:55 PM – 10-DEC 16:55 – 12-10-12

12. Commonly Used Single Row Character Functionsin ORACLEThese functions return character values

13. Aggregate functions in SQL are operations that summarize a number of rows from a table, view, or join operation into one value. Examples include: sum, avg for average, count, min for minimum, and max for maximum, and stddev for standard deviation. Examples: Select min(Standard_price) from PRODUCT Obtains the lowest price for all products Select count(Standard_price) from PRODUCT Obtains a count of the number of rows with standard_price values (non NULL) in the PRODUCT table Select avg(unit_price) from PRODUCT Obtains the average of standard_price across all rows in the PRODUCT table (NULLs not included) AGGREGATE Function Concepts

14. The group by clause allows you to use group functions on a group of rows that have the same value for a specific attribute. Typical examples include: grouping data is by department, or gender, or date. Using the group by will give you a result (summary) for each distinct value of the group by attribute. If you did a group by on gender you would get a summary on male and a summary on female. If you did a group by on date you would get a summary for each date value that is in the database. Example: SELECT Prod_Category, Avg(standard_price), count(standard_price) FROM PRODUCT GROUP BY Prod_Category; The result of this statement will be the average Standard_Price and number of products in each product category. If you include an non aggregate attribute (like Prod_Category) in the column list and do not include it in a group by clause you will get an error because you would be combining aggregate and detailed data. Group-By Clause

15. You can select from the output (result rows) of a Group By operation the same way you can select rows in a Select operation. In the Select operation you used the Where clause. To apply selection criteria to group results you use a Having Clause. Example: SELECT Prod_Category, Avg(Standard_Price), count(Standard_Price) FROM PRODUCT Group By Prod_Category HAVING count(Standard_Price) > 2 This would display summary results for all product categories where there are more than two products in the category. Group By with Selection