Lecture 09: Select Statement and Clauses - Western Colorado University

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

Lecture 09: Select Statement and Clauses CS 195 WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Select Statement Functions Clauses WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  SELECT Statement Basic SELECT select_colum_list [,] FROM table_name; WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  SELECT Statement Keywords SELECT select_colum_list [,] FROM table_name; SELECT and FROM are the keywords. Make up the basic select statement and are required Formatting Uppercase Each clause written on separate lines WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  SELECT Statement Columns SELECT select_colum_list [,] FROM table_name; Specify one or more columns from which you want to select data from separate by commas Order displayed is the order you specify your columns WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  SELECT Statement Table SELECT select_colum_list [,] FROM table_name; Specify the name of the table from which you want to select data from Always one table name WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  SELECT Statement Colon SELECT select_colum_list [,] FROM table_name; The semicolon ( ; ) is optional, it denotes the end of a statement If you have two or more statements, you need to use the semicolon to separate them WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  SELECT Statement Example WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  SELECT Statement Select All SELECT * FROM table_name; ( * ) shorthand for all columns “select star” “select all” Used for ad-hoc queries only 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  Drop Zoo Database Connect to Rady server See Zoo database has information WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Select Statement Functions Clauses WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  SELECT statement Count and Distinct You don’t have to use just column names in select You can use a count to get the count of the number of records COUNT(*) or COUNT(column) You can use distinct to get the distinct values from a table DISTINCT (column) COUNT(DISTINCT(column)) WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  GROUP BY Clause Count WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Drop Zoo Database How many animals are in distinct enclosures? WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Recap Select Statement Functions Clauses Where Order By Limit Group BY Having 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 sponsors that contribute to animal enrichment programs Find animals that are juvenile (2 – 6 years) Find how many are Females Find how many are Female or are Carnivore’s Find animals that have had offspring WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Select Statement Clauses Where Order By Limit Group BY Having 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 See all sponsors amount by date given (earliest to oldest) WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Select Statement Clauses Where Order By Limit Group BY Having 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 Find the most recent medical record created WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Select Statement Clauses Where Order By Limit Group BY Having 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 Find the total amount of KG per each feeding schedule type WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO  Outline Select Statement Clauses Where Order By Limit Group BY Having 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 average age grouped by gender Find which types of feeding have more than 20KG total WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO WESTERN COLORADO UNIVERSITY | GO.WESTERN.EDU | GUNNISON, CO