Advanced SELECT QueriesSlide 2We can use the DBMS to manipulate retrieved data!Suppressing Duplicate OutputsSlide 5Sorting Query OutputMultiple Sort KeysFormatting Character OutputDefault Query OutputPerforming Arithmetic Calculations in QueriesPerforming Arithmetic Operations on Number DataSlide 12Performing Arithmetic Operations on Date DataSlide 14Another Date CalculationSQL Group FunctionsSlide 17Using the GROUP BY ClauseSlide 19SUM and Statistical FunctionsCOUNT FunctionAdvanced SELECT QueriesCS 146Review: Retrieving Data From a Single TableSyntax:Limitation: Retrieves "raw" dataNote the default formats…SELECT column1, column2, …FROM tablenameWHERE search_condition(s)We can use the DBMS to manipulate retrieved data!Suppress duplicatesSortFormat characters, numbers, & datesPerform arithmetic operationsSummarize groups of dataWhy not just do it in your program?Suppressing Duplicate OutputsUse the DISTINCT qualifierEnsures that only distinct rows are returnedSELECT DISTINCT cust_zipFROM candy_customer;Sorting Query OutputUse the ORDER BY clause:Always appears as the last item in a SELECT querySELECT SELECT custnamecustnameFROM FROM customercustomerWHERE WHERE cust_type = 'P'cust_type = 'P'ORDER BY cust_nameORDER BY cust_name;;Sorting Query OutputDefault sort order is ascendingNumbers: smallest to largestCharacters: alphabeticalDates: oldest to newestTo force a descending sort order, add the DESC modifier:SELECT purch_id, purch_dateFROM candy_purchaseORDER BY purch_date DESCMultiple Sort KeysYou can sort output by multiple keysOnly makes sense when first sort key has repeating values…SELECT purch_id, purch_dateFROM candy_purchaseORDER BY purch_date DESC, purch_idFormatting Character OutputMySQL has a variety of functions for manipulating stringsFunction Description Example QueryCONCAT(<string1>, <string2>) Concatenates (joins) two stringsSELECT CONCAT(cust_addr, cust_zip)FROM candy_customer;UPPER(<string>), LOWER(<string>)Returns the string, with all characters converted to upper/lower caseSELECT UPPER(username)FROM candy_customer;LENGTH(<string>) Returns an integer representing the string lengthSELECT LENGTH(password)FROM candy_customer;LPAD(<string>, <number of characters to add>, <padding character>), RPAD(<string>, <total length of return value>, <padding character>)Returns the value of the string, with sufficient padding characters added to the left/right edge so return value equals total length specifiedSELECT LPAD(password, 8, '*')FROM candy_customer;Default Query OutputColumn names are database field namesCalculated column names are the formulaPerforming Arithmetic Calculations in QueriesApplications often perform arithmetic operations on retrieved dataYou can perform basic arithmetic operations on numbers and dates in a SQL query SELECT clauseRationale:DBMS makes it easy to perform the operationNetwork needs to transmit only the data you needPerforming Arithmetic Operations on Number DataOperators: +, -, *, /Order of evaluation:* / then + -To force a different order, use parenthesesOnly use on number dataPrices, quantities, etc.Performing Arithmetic Operations on Number DataExample:SELECT prod_desc, prod_price - prod_costFROM candy_product;Performing Arithmetic Operations on Date DataTo display a date that is a specific number of days after/before a stored date, add/subtract the number of days:SELECT purch_id, purch_date, purch_date + 2FROM candy_purchase;SELECT purch_id, purch_date, purch_date - 2FROM candy_purchase;Performing Arithmetic Operations on Date DataTo calculate the number of days between two known dates, you can use subtraction.SELECT purch_id, purch_date, delivery_date, delivery_date - purch_dateFROM candy_purchase;Another Date CalculationHow long since we delivered?SELECT (CURRENT_DATE – delivery_date) / 365.25FROM CANDY_PURCHASESQL Group FunctionsPerforms an operation on a field from a group of retrieved recordsAVG (average of all retrieved values)COUNT (number of records retrieved)MAX (maximum value retrieved)MIN (minimum value retrieved)SUM (sum of all retrieved values)SQL Group FunctionsExamplesSELECT MAX(prod_cost), MIN(prod_cost), AVG(prod_cost)FROM candy_product;SELECT COUNT(*)FROM candy_customer;Using the GROUP BY ClauseWhenever you use a group function:All of the columns in the select clause must be in a group functionorIf not, the column must be listed in a GROUP BY clauseUsing the GROUP BY ClauseExample:SELECT purch_date, MAX(pounds), MIN(pounds), AVG(pounds)FROM candy_purchaseGROUP BY purch_date;SUM and Statistical FunctionsSUM, AVG, MAX, MINCan only be used with NUMBER columnsSUM(pounds)MAX(prod_cost)MIN(prod_cost)AVG(prod_cost)COUNT FunctionDisplays the number of records that a query will retrieve Can be used on a column of any data typeForms:COUNT(*) – displays total number of records, regardless if the record has fields that contain NULL valuesCOUNT(fieldname) – displays the number of retrieved records in which the specified field is NOT