Grouping%20Data

1
Grouping Data
2
GROUP BY clause

• Groups results by column name
• used with aggregate functions
• must come first when used with ORDER BY

3
Syntax

• SELECT count(au_id), state, city FROM authors
• GROUP BY state, city
• column in GROUP BY must be in SELECT list
• There can be multiple columns in GROUP BY. Where
  their list order is their order of precedence.

4
Limitations

• There must be only 1 value returned for each
  GROUP BY field.
• Should only use column names
• Due to these limitations, many vendors provide
  report generators

5
Grouping and Nulls

• Nulls are assigned a group of there own since
  they are, in a sense, their own datatype.

6
HAVING clause

• Same as WHERE clause except it allows aggregate
  functions
• Often used with GROUP BY
• Must come after GROUP BY when both are used in a
  query.
• Unlike WHERE, all the fields in the HAVING list
  must also be in the SELECT list.

7
Syntax for HAVING

• Select type, count()from titlesGROUP BY
  typeHAVING count() gt 1
• Resultstype__ ____business 4mod_cook 2

8
HAVING

• Multiple conditions can be specified through the
  use of AND, OR, or NOT keywords.
• Difference with WHERE functionality is that WHERE
  eliminates rows before grouping and HAVING does
  so after grouping.

9
Another syntax example

• SELECT pub_id, sum(advance), avg(price)FROM
  titlesWHERE price gt 5GROUP BY pub_idHAVING
  sun(advance) gt 1500and avg(price) lt 20ORDER BY
  pub_id

10
About Security

• As is the case in most shared environments, the
  DBMS also must implement a security mechanism
  that allows the setting of permissions to data
  and actions pertaining to that data.
• This is required to ensure data security.

11
GRANT Command

• This grants a user or group of users permission
  to manipulate specified data in specified ways.
• GRANT ALL privilege_listON table_name
  view_name (col_list)TO PUBLIC user_list

12
REVOKE Command

• This revokes a granted permission issued by
  GRANT from a specified user.
• REVOKE ALL privilege_listON table_name
  view_name (col_list)FROM PUBLIC
  user_list
• Tip1) You GRANT TO and REVOKE FROM.2) The most
  recently issued statement supercedes all others.