1 / 31

SQL Training ORACLE SQL Functions

SQL Training ORACLE SQL Functions. Lesson Objectives. At the end of this section you will be able to:. Write SQL Statements using: Aggregate Functions (max, min, avg) Common CHAR and DATE functions. Aggregate Functions. Select – Aggregate Functions.

vanessabradley
Télécharger la présentation

SQL Training ORACLE SQL 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. SQL Training ORACLE SQL Functions

  2. Lesson Objectives At the end of this section you will be able to: • Write SQL Statements using: • Aggregate Functions (max, min, avg) • Common CHAR and DATE functions

  3. Aggregate Functions

  4. Select – Aggregate Functions The following aggregate functions can be applied to multiple values retrieved from a table. FunctionPurpose MIN Yields the minimum value in a column MAX Yields the maximum value in a column AVG Computes the average value for a column SUM Computes the total value for a column COUNT Lists the number of rows in a query, or lists the number of distinct column values

  5. Group By and Having

  6. Group By Show the number of components used to build ProductCode HG4000-01C (ProductID = 15) SELECT Product.ProductID, Product.ProductDescription, Sum(RequiredQTY) as NumComponents From Product, Manifest Where Product.ProductID= Manifest.ProductID and Product.ProductID = 15 Group By Product.ProductID, Product.ProductDescription; All fields in the SELECT clause that are not part of an aggregate function must be included in the GROUP BY clause.

  7. Group By Problem: How many vendors are in each country? Show by Country and Region. SELECT c.countryname, r.regionname, count(vendorid) as NbrVendors FROM country c, region r, province p, vendor v WHERE c.countryid = r.countryid and r.regionid = p.regionid and p.provinceid = v.provinceid GROUP BY ____________ ORDER BY 1,2 desc; ? What goes in the GROUP BY clause?

  8. Group By Problem: How many vendors are in each country? Show by Country and Region. SELECT c.countryname, r.regionname, count(vendorid) as NbrVendors FROM country c, region r, province p, vendor v WHERE c.countryid = r.countryid and r.regionid = p.regionid and p.provinceid = v.provinceid GROUP BY c.countryname, r.regionname ORDER BY 1,2 desc;

  9. Group By Problem: What is the average, min and max hourly rate by user location? SELECT userLocation, avg(hourlyRate) as Avg_Hourly_Rate, min(hourlyRate) as Min_Hourly_Rate, max(hourlyRate) as Max_Hourly_Rate FROMUser GROUP BY userLocation ORDER BY avg(hourlyRate);

  10. Group By and Having WHERE  Filters out Rows that don’t satisfy the search conditions. HAVING  Filters out Groups that don’t satisfy the search conditions. Problem: Retrieve all products containing less than 40 components. SELECT Product.PRODUCTID, Sum(RequiredQTY) AS NumComponents FROM Product, Manifest WHERE Product.ProductID = Manifest.ProductID GROUP BY Product.PRODUCTID HAVING Sum(RequiredQTY) < 40; • 6 rows selected.

  11. Group By and Having Problem: Which customers have more than 9 users? SELECT ct.customerTypeDescription as CustomerType, c.customerName as Customer, count(*) as NbrUsers FROM CustomerType ct, Customer c, Users u WHERE ct.customertypeid = c.customertypeid and c.customerid = u.customerid GROUP BY ______________________ HAVING ______________________ ORDER BY 1,3 desc; 12 Rows

  12. Useful Functions

  13. Arithmetic Expressions Problem: Multiply the vendor price by 1.05 where ComponentID < 10 and VendorID = 1 SELECT VendorID, ComponentID, VendorPrice, VendorPrice * 1.05 as NewPrice From VendorComponent Where VendorID =1 and ComponentID < 10; 9 rows selected.

  14. Select – Scalar Functions • A scalar row function returns a single result row for every row of a queried table or view. • Single row functions can appear in select lists (provided the SELECT statement does not contain a GROUP BY clause) and WHERE clauses. • Number Functions: Number functions accept numeric input and return numeric values. ROUND Syntax ROUND(n[,m]) Returns n rounded to m places right of the decimal point. If m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer. SELECT ComponentID, Avg(VendorPrice) As Avg, ROUND(Avg(VendorPrice)) As Round FROM VendorComponent Group By ComponentID Order By ComponentID; 898 rows selected . Note: not all rows are displayed in this report due to size constraints of this page.

  15. Some Useful Scalar Functions ABS Syntax ABS(n) Returns the absolute value of n CEILSyntax CEIL(n) Returns smallest integer greater than or equal to n. FLOORSyntax FLOOR(n) Returns largest integer equal to or less than n. ROUNDSyntax ROUND(n[,m]) Returns n rounded to m places right of the decimal point. TRUNCSyntax TRUNC(n[,m]) Returns n truncated to m decimal places.

  16. Some Useful Character Functions SUBSTRSyntax SUBSTR(char, m [,n]) Returns a portion of char, beginning at character m, n characters long. If m is 0, it is treated as 1. If m is positive, Oracle counts from the beginning of char to find the first character. If m is negative, Oracle counts backwards from the end of char. If n is omitted, Oracle returns all characters to the end of char. If n is less than 1, a null is returned. Select substr(VendorFirstName,1,1) || ', ' || VendorLastName from Vendor; UPPERSyntax UPPER(char) Returns char, with all letters uppercase. Select * from Vendor where Upper(City) = ‘BOSTON’;

  17. Some Useful Character Functions LENGTHSyntax LENGTH(char) Returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null. LOWERSyntax LOWER(char) Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2). LTRIMSyntax LTRIM(char [,set] Removes characters from the left of char, with all the leftmost characters that appear in set removed. Set defaults to a single blank. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result. REPLACESyntax REPLACE(char, search_string[,replacement_string]) Returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned.

  18. TO_CHAR Function The TO_CHAR function converts a number or date to a string. The syntax for the to_char function is: to_char(value,[format_mask]). The value field can be either a number or a date. Example – Numbers: to_char(1210.73,’9999.9’) would return ‘1210.7’ to_char(1210.73,’9999.99’) would return ‘1210.73’ to_char(1210.73,’$9,999.99’) would return ‘$1,210.73’ Example – Dates: to_char(actualEndDate,’mm/dd/yyyy’) would return ‘05/17/2009’ to_char(actualEndDate,’Month DD, YYYY’) would return ‘May 17, 2009’ to_char(actualEndDate,’YYYY’) would return ‘2009’ to_char(actualEndDate,’MM’) would return ‘05’ to_char(actualEndDate,’DD’) would return ‘17’ to_char(actualEndDate,’D’) would return ‘1’ or Sunday to_char(actualEndDate,’SSSSS’) would return ‘30370’

  19. Date Conversion Examples SELECTfactoryid, to_char(actualEndDate,'mm/dd/yyyy') as ShortDate, to_char(actualEndDate,'Month DD, YYYY') as LongDate, to_char(actualEndDate,'YYYY') as Year, to_char(actualEndDate,'MM') as Month, to_char(actualEndDate,'DD') as Day, to_char(actualEndDate,'D') as DayOfWeek, to_char(actualEndDate,'SSSSS') as SecondsAfterMidnight, cast(actualEndDate as TimeStamp) as Timestamp FROM factory WHEREto_char(actualEndDate,'MM') = 5 and to_char(actualEndDate,'DD') = 17 and to_char(actualEndDate,'YYYY') = 2009;

  20. Date Conversion Parameters ISO date example, 2006-W52-7 (or in compact form 2006W527) is the Sunday of the 52nd week of 2006.

  21. Working with Dates Problem: What incidents were opened Nov 1 and Nov 5, 2008? SELECT incidentId, openDate FROM Incident WHERE OpenDate Between'11/1/2008' and '11/5/2008';

  22. Working with Dates Problem: What incidents were opened between Nov 1 and Nov 5, 2008? SELECT incidentID, openDate FROM Incident WHERE openDate Between'1-Nov-2008' and '5-Nov-2008'; But what if the dates aren’t in this format? 53 Rows

  23. Working with Dates Problem: What incidents were opened between Nov 1 and Nov 5, 2008? SELECT incidentID, openDate FROM Incident WHERE openDate Betweento_date('11/1/2008', 'MM/DD/YYYY') andto_date('11/5/2008','MM/DD/YYYY'); In Oracle, the to_date function converts a string to a date 53 Rows

  24. Date Arithmetic Problem: How many minutes did Nielsen engineers work on incidents 1-10? SELECT i.incidentID, round(sum(ia.checkout - ia.checkin) * 1440) as actualTimeMinutes FROM IncidentAction ia, Incident i WHERE i.incidentID = ia.incidentID and i.incidentID between 1 and 10 GROUP BY i.incidentID; Note: Date1 – Date2 = DaysDiff

  25. Workshop

  26. Individual SQL Workshop 2 – Starter DB

  27. Problem 1 – Aggregate Functions Problem: List all the Regions in the United States (CountryID = 1). Count the number of Provinces in each Region. Order by RegionName. 8 rows selected.

  28. Problem 2 – Aggregate Functions Problem: For all Products whose ProductID less than or equal to 12, calculate the Cost of the Components, and their Margin Amount (Product Price – Component Cost). Order the Report by MarginAmt in descending sequence. Note: In the Manifest Table is a field named RequiredQty. This field contains the number of components required to build the homegen. For example, if the component was a wheel, then RequiredQty for the wheel component would equal 4. Also in the VendorComponent table is the field VendorCost. VendorCost contains price we pay the Vendor for each component. • 12 rows selected.

  29. Problem 3 – Select SUBSTR and Concatenation Problem: Select all vendors in the Vendor table where the city starts with the character ‘B’. In the report, concatenate the first character of the first name of the vendor with their last name. Order the report in ascending sequence on the VendorID column. 5 rows selected.

  30. Problem 4 – Select using GROUP BY and Column Functions Problem: Group all PowerRating(s) represented in the Product column. • Include a Count of the number of rows in each grouping. • Calculate the Average, Maximum, Minimum ProductPrice column. • Include Order the report in ascending sequence by PowerRating. Note: To arrive at the “proper” AVGPRICE, you will have to round your average. 7 rows selected.

  31. Problem 5 – Select using GROUP BY and HAVING Problem: Group all PowerRating(s) represented in the Product table. Then display a Count of the rows in the group, and calculate the Average, Maximum and Minimum ProductPrice column. • Only include in the report products that have a ProductDescription that contains the characters 'Butane'. • Only include in the report those groups that have greater than or equal to 3 rows • Only include in the report those groups that have an Average ProductPrice amount greater than or equal to 7000 dollars. • Order the report in ascending sequence by PowerRating. Note: To arrive at the “proper” AVGPRICE, you will have to round your average. 6 rows selected.

More Related