1 / 15

SQL – Functions Single-Row & Aggregate

SQL – Functions Single-Row & Aggregate. CIS 310. Datatype Conversion Functions in ORACLE. Many datatypes are automatically (implicitly) converted when differing but compatible datatypes are used in an expression.

hasana
Download Presentation

SQL – Functions Single-Row & Aggregate

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

More Related