1 / 15

Computer Science 101 Web Access to Databases

Computer Science 101 Web Access to Databases. SQL – Extended Form. SQL - Aggregate Functions. Aggregate Functions Count Sum Max Min Avg Get birth date of oldest student SELECT MIN(Birthdate) FROM Students. SQL Query - Expanded Form.

luyu
Download Presentation

Computer Science 101 Web Access to Databases

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. Computer Science 101Web Access to Databases SQL – Extended Form

  2. SQL - Aggregate Functions • Aggregate Functions • Count • Sum • Max • Min • Avg • Get birth date of oldest student SELECT MIN(Birthdate) FROM Students

  3. SQL Query - Expanded Form • SELECT <attribute or function list>FROM <table list>[WHERE <conditions>][GROUP BY<grouping attributes>][HAVING <grouping condition>][ORDER BY <attribute list>]

  4. SQL - Expanded Form (cont.) • SELECT: attributes or functions retrieved in result (columns of result) • FROM: all tables needed to do query • WHERE: conditions for selecting rows

  5. SQL - Expanded Form (cont.) • GROUP BY: attributes for grouping - one row in result for each group value • HAVING: conditions for selecting group rows • ORDER BY: order for display, can specify ASC or DESC

  6. SQL - Group By • Note: It must be that all attributes listed in the Select clause are constant within each group specified by the Group By clause. • For example, we could not say SELECT LastName, ClassYear FROM Students GROUP BY ClassYearsince the LastName varies within a ClassYear • Usually, the GROUP BY clause is used with aggregate functions to get statistics on the groups.

  7. SQL - Group By examples • Actually, the SELECT and GROUP BY lists should be the same except for aggregate functions in the Select list • Give a table of counts by class

  8. SQL - Group By examples • Restrict to current students and sort by count (descending) and ClassYear. • Give a table of counts by class

  9. SQL - Group By examples (cont.) • Give a list of faculty names together with the number of their advisees.

  10. SQL - Group By examples (cont.) • Just current term

  11. SQL - Group By examples (cont.) • List of NFL teams and the number of students expressing interest in that team

  12. SQL - Having Clause • The Having clause plays a role for group rows much like the Where clause plays for select rows. i.e., the Having clause specifies criteria which must be met by a group row in order for it to be in the result table.

  13. SQL - Having Clause (cont.) • The fromclause gives a set of potential rows for the result – every combination of rows from the tables. • The whereclause gives a filter for these potential rows – which do we keep. • The select clause specifies the columns we want. • The group by clause “squashes” groups of rows into single group rows. • The havingclause gives a filter for these group rows.

  14. SQL - Having clause examples List of interests, their categories and number of students with the interest where there are at least ten students with the interest, arranged in order of popularity, most popular first.

  15. SQL - Having clause examples Just this term (3 or more):

More Related