Lecture 09: Select Statement and Clauses PDF

Lecture 09: Select Statement and Clauses CS 195 WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Clauses More Functions WESTERN COLORADO UNIVERSITY | GO.WEST...

Lecture 09: Select Statement and Clauses CS 195 WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Clauses More Functions WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  SELECT Statement Example WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  SELECT Statement Select All WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Clause **Select and From are the only Order required operations in a select statement SELEC FROM T WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Count and Distinct Count WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Clauses Where Order By Limit Group BY Having More Functions WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  WHERE clause Condition SELECT select_column_list FROM table_name WHERE search_condition; Combination of one or more expressions using logical operators Boolean expression that evaluates to TRUE, FALSE or UNKNOWN WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  WHERE clause Condition True values are returned WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  WHERE clause Operations Like Between In Null Not Comparisons And Or WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  WHERE clause LIKE expression LIKE pattern Matches the pattern Wildcard characters ( % ) - match any string of zero or more characters ( _ ) – match any single character WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  WHERE clause LIKE Q: List all payments that start with C WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  WHERE clause BETWEEN value BETWEEN low AND high Value in range or not Value can be: Number (4 AND 6) Expressions (column_name >= 9 AND column_name 3 or invoice total > $160 AND and OR 1. Does the WHERE statement on the entire table 2. OR/AND operations on the entire table (not just the subset from #1) 3. AND first (out of all the invoices) 4. Of the ones that have 0 payment take ones > 160 invoice total WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Q: List invoices that have a payment WHERE clause total of $0 with client ids > 3 or invoice total > $160 AND and OR 1. AND first (out of all the invoices) 2. Of the ones that have a payment of 0 take ones that have a client id of 3 or more OR invoice total more than 160 ***ALWAYS USE PARANTHESIS WITH CRITERIA THAT NEES TO BE CONSIDERED TOGETHER WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  WHERE Clause Order SELE WHER FROM CT E WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  WHERE clause Zoo Database Find animals that are juvenile (2 – 6 years) Find how many are Females Find how many are Female or are Carnivore’s WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Clauses Where Order By Limit Group BY Having More Functions WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  ORDER BY Clause Keyword SELECT select_list FROM table_name ORDER BY column1 [ASC| DESC], column2 [ASC| DESC], …; By default, SELECT statements returns rows in an unspecified order One or more columns that you want to sort by WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  ORDER BY Clause Clause ORDER BY column1 [ASC| DESC], column2 [ASC| DESC], …; Order ASC = Ascending order DESC = Descending order Default is ASC If multiple columns sorts the columns in order (column 1, then column 2, etc…) WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  ORDER BY Clause Q: List all items by Q: List all items by product id highest to product id and by unit Example lowest price within capacity WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  ORDER BY Clause Order SELEC WHER ORDE FROM T E R BY WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  ORDER BY Clause Zoo Database See all animals from oldest to youngest WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Clauses Where Order By Limit Group BY Having More Functions WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  LIMIT Clause Keyword SELECT select_list FROM table_name LIMIT row_count SELECT statement returns rows in unspecified order The returned rows are unpredictable To ensure the LIMIT clause returns an expected output, you should always use it with an ORDER BY clause WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  LIMIT Clause SELECT select_list Order By FROM table_name ORDER BY sort_expression LIMIT row_count LIMIT is always the last clause regardless of any other clauses added to the SELECT statement WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  LIMIT Clause Example WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  LIMIT Clause Gotcha’s Don’t use the Limit in the application Always use ORDER BY WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  LIMIT Clause Order SELEC WHER ORDE FROM LIMIT T E R BY WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  LIMIT Clause Zoo Database Find the top 5 sponsors amount contributed WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Clauses Where Order By Limit Group BY Having More Functions WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  GROUP BY clause SELECT select_list Condition FROM table_name WHERE search_condition GROUP BY column1, column2, …; Group rows into subgroups based on values of columns or expressions WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  GROUP BY Clause Clause GROUP BY column1, column2, …; Groups a set of rows into a set of summary rows by values of columns or expressions Returns one row for each group (reducing the number of rows in the result set) Often used with aggregate functions as the aggregate function that appears in SELECT provides the information of each group WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  GROUP BY Clause Group summary statistics You can apply some summary statistics to a column in the select SUM(column) – gets the sum of the column AVG(column) – gets the average of the column MAX(column) – gets the max value of the column MIN(column) – gets the min value of the column WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  GROUP BY Clause Example Q: Show product id and Q: Show total unit price for total unit price for each each product where product product id > 5 WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  GROUP BY Clause Q: Show the sum of unit SUM price per product WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  GROUP BY Clause Q: Show the sum of unit SUM price per product WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  GROUP BY Clause Order SELEC WHER GROU ORDE FROM LIMIT T E P BY R BY WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  GROUP BY Clause Zoo Database Find the animals average age grouped by gender WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Clauses Where Order By Limit Group BY Having More Functions WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  HAVING clause Condition SELECT select_list FROM table_name WHERE search_condition GROUP BY group_by_expression HAVING group_condition; Applies a filter condition to each group of rows WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  HAVING Clause Clause HAVING group_condition HAVING clause is often used with the GROUP BY clause to filter groups based on specified conditions Omitting GROUP BY, the HAVING behaves like the WHERE clause Evaluates each group returned by the GROUP BY clause. If the result is true, the result is included in the result set It applies filter condition to each group of rows (not each individual row like the WHERE) WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  HAVING Clause Q: Show total capacity (max_enrl) for Example each course over all terms, if the total capacity is greater than 60 WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  HAVING Clause Q: Show total unit price for each product if the Example total unit price is greater than 11 WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  HAVING Clause Q: Show total unit price for each product Example with product id greater than or equal to 5, if the total unit price is greater than 10 WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  HAVING Clause Order SELEC WHER GROU HAVIN ORDE FROM LIMIT T E P BY G R BY **Select and From are the only required operations in a select statement all the rest are optional WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  HAVING Clause Zoo Database Find the animals that have an average age of more than 10 by species WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Clauses More Functions WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Functions Overview CONCAT GROUP_CONCAT SUBSTRING COALESCE ROUND WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Functions CONCAT() Concatenate two or more strings into a single string. It takes multiple string arguments and combines them. CONCAT(string1, string2, ….) SELECT CONCAT(‘hello’, ‘ ‘, ‘world’) WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Functions GROUP_CONCAT() Function is commonly used to concatenate values from multiple rows within a group. Used with Group BY GROUP_CONCAT(column ORDER BY column SEPARATOR ‘,’) WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Functions GROUP_CONCAT() SELECT order_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ‘,’) FROM orders GROUP BY order_id; WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Functions SUBSTRING() Function extracts a substring from a string. You provide the string, the starting position, and the length of the substring. SUBSTRING(string, start_position, length) SELECT SUBSTRING('Hello World', 7, 5) WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Functions COALESCE() Function returns the first non-null expression in a list of expressions. It is often used to handle NULL values. COALESCE(expression1, expression2,..., val) expression1 is either an expression or column val is what you want to display if there is null WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Functions COALESCE() SELECT employee_id, first_name, COALESCE(middle_name, ‘’”) FROM employees; WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Functions ROUND() Function is used to round a numeric value to a specified number of decimal places. ROUND(number, decimal) SELECT ROUND(3.14159, 2) WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO