1 / 27

Advanced SELECT Queries

Advanced SELECT Queries. CS 146. Review: Retrieving Data From a Single Table. Syntax: Limitation: Retrieves "raw" data Note the default formats…. SELECT column1, column2, … FROM tablename WHERE search_condition(s). We can use the DBMS to manipulate retrieved data!.

brook
Download Presentation

Advanced SELECT Queries

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. Advanced SELECT Queries CS 146

  2. Review: Retrieving Data From a Single Table • Syntax: • Limitation: Retrieves "raw" data • Note the default formats… SELECT column1, column2, … FROM tablename WHERE search_condition(s)

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

  4. Suppressing Duplicate Outputs • Use the DISTINCT qualifier • Ensures that only distinct rows are returned SELECT DISTINCT cust_zip FROM candy_customer;

  5. Sorting Query Output • Use the ORDER BY clause: • Always appears as the last item in a SELECT query • SELECT custname • FROM customer • WHERE cust_type = 'P' • ORDER BY cust_name;

  6. 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_date • FROM candy_purchase • ORDER BY purch_date DESC

  7. Multiple Sort Keys • You can sort output by multiple keys • Only makes sense when first sort key has repeating values… • SELECT purch_id, purch_date • FROM candy_purchase • ORDER BY purch_date DESC, purch_id

  8. Formatting Number Output • Use the FORMAT function • Format: FORMAT(number, decimal_places) • SELECT purch_id, • FORMAT(pounds, 2) • FROM candy_purchase;

  9. Formatting Number Output as Currency • Use the CONCAT and FORMAT function • CONCAT joins two strings to create a single string • CONCAT('$', FORMAT(number, decimal_places)) • SELECT prod_id, • CONCAT('$', FORMAT(prod_cost, 2)) • FROM candy_product;

  10. Formatting Date Output • Use the DATE_FORMAT function • Format: DATE_FORMAT(date, 'format') • SELECT purch_id, • DATE_FORMAT(purch_date, '%b %e, %Y') • FROM candy_purchase; Predefined format specifiers

  11. Format Specifiers • %b – abbreviated month name • %e – day of the month, numeric, suppresses leading zeroes • %Y – year, numeric, 4 digits • More complete list of specifiers

  12. Formatting Character Output • MySQL has a variety of functions for manipulating strings

  13. Default Query Output • Column names are database field names • Calculated column names are the formula

  14. Column Aliases • Provide an alternate column name • What good are they? • You can use them in the ORDER BY clause • You can reference them in embedded programs • NOTE: alias only has to be in single quotes if it contains blank spaces SELECT LENGTH(cust_name) AS 'name length' FROM candy_customer ORDER BY 'name length';

  15. Performing 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 need

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

  17. Performing Arithmetic Operations on Number Data • Example: • SELECT prod_desc, prod_price - prod_cost • FROM candy_product;

  18. 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 + 2 • FROM candy_purchase; • SELECT purch_id, purch_date, purch_date - 2 • FROM candy_purchase;

  19. Performing Arithmetic Operations on Date Data • To calculate the number of days between two known dates, use DATEDIFF • SELECT purch_id, purch_date, delivery_date, • DATEDIFF(delivery_date, purch_date) • FROM candy_purchase;

  20. Retrieving the Current Date • Use the CURRENT_DATE() function • Function: code retrieving information that acts like a column in a SQL command • SELECT CURRENT_DATE();

  21. Another Date Calculation • Calculating someone’s age from their date of birth • SELECT (DATEDIFF(CURRENT_DATE(), • '1986-11-20')) / 365.25;

  22. SQL 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)

  23. SQL Group Functions • Examples • SELECT MAX(prod_cost), MIN(prod_cost), • AVG(prod_cost) • FROM candy_product; • SELECT COUNT(*) • FROM candy_customer;

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

  25. Using the GROUP BY Clause • Example: • SELECT purch_date, MAX(pounds), • MIN(pounds), AVG(pounds) • FROM candy_purchase • GROUP BY purch_date;

  26. SUM and Statistical Functions • SUM, AVG, MAX, MIN • Can only be used with NUMBER columns SUM(pounds) MAX(prod_cost) MIN(prod_cost) AVG(prod_cost)

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

More Related