Advanced SELECT Queries

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